Develop an enterprise data model

Assignment Help Other Subject

Reference no: EM133746284

Database Systems

Objectives

This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to improve students' skills to analyze organization database requirements, develop a data model to reflect the organization's business rules. This assessment covers the following LOs.

Synthesize user requirements/inputs and analyse the matching data processing needs, demonstrating adaptability to changing circumstances.

Develop an enterprise data model that reflects the organization's fundamental business rules; refine the conceptual data model, including all entities, relationships, attributes, and business rules.

Derive a physical design from the logical design taking into account application, hardware, operating system, and data communications networks requirements; further use of data manipulation language to query, update, and manage a database

Case Study

This assignment concerns a liquor shop chain in Sydney, called the A-one liquor (AL). The objective of this assignment is to develop a database system that will be used to centrally store and manage all relevant information for the branches of AL.

The information to be stored include information on different branches of AL (e.g., Bankstown, Hornsby, etc.), types of drinks they sell (beers, wines, cedars, etc.), staff they employ (Retail Assistants, Shelving Assistants, etc.), AL Members (AL Loyalty Card holders). The basic requirements gathered from the stake holders is presented in the following four points. As typically the case, these requirements are often underspecified. Use your judgment in interpreting them when required and keep a note of the assumptions you made.

Branch Information: The AL System shall keep information on each branch including its name and address, and the number of employees who work there. The system shall also contain information on which days (Mon-Sun) the branch is open, and opening hours. It will also keep information on opening hours (e.g., Mon-Fri 10:00AM-5:30PM; Sat 9:00AM-9:00PM; Sun Closed).

Product Information: The system shall contain relevant information on products of different types at the "item level", such as: (wine/beer/spirit/...), packaging info (can/bottle/...), volume (e.g., 375ml X 6 pack), price, and brand (e.g., Tooheys Old Dark Ale), as well as current stock level.

Staff Information: The system shall record information on staff members who work at different branches of AL. This will include their roles, type of employment (e.g., permanent, casual), salary (annual or hourly depending on permanent or casual), as well as who they report to (i.e., supervisors).

Membership Information: The system shall record information on AL members, including type of membership (Platinum/Gold/Silver), and when the membership will expire.

Task 1:
Investigate and identify data requirement for the given study by providing list of required entities, list of attributes in each of these entities, and the unique identifier for each entity.

Task 2:
Develop an Entity-Relationships Diagram for the given scenario (It must contain entities and unique identifiers in terms of keys, relationships between the entities including relationships constraints, and the attributes).

Task 3:
Derive the physical design having detail of entities, primary and foreign keys, detail of all the attributes.

Create a database and CREATE TABLE statements for all tables in your ERD (Part A) including primary and foreign keys.

INSERT INTO statements for populating the database

Insert five rows of (made-up) data into each table. Make sure that the data you enter in these tables should be sufficient to return at least one row for each query in Task 3. AL should hold at least 5 bottles of Penfold Grange 2010 in some branch or other.
Select Statements

List the branches (ID) of MA that have in stock at least 5 bottles of Penfold Grange 2010.

SELECT statement to generate a list of all email addresses of members whose card will expire in the month after the coming month. Thus, for instance, if the query is run in November 2121, it will list the emails of all members whose membership will expire in January 2122. The emails should be ordered by Branch ID, then by expiry date, and then by the email address, all in ascending order.

Reference no: EM133746284

Questions Cloud

Role of the mesolimbic dopamine pathway : Describe the role of the mesolimbic dopamine pathway in the development or continuation of impulsive/compulsive behaviors.

Recently diagnosed with migraines : A nurse is providing health education to a client who has been recently diagnosed with migraines. What should the nurse include in the teaching?

Client regarding administration of sumatriptan : A nurse is educating a client regarding the administration of sumatriptan. Which client statement requires intervention by the nurse?

The client is suffering from migraine headache : The client is suffering from a migraine headache. Discard the medication and syringe. Give all of it to the client so as not to waste the drug.

Develop an enterprise data model : PAED4401 Research Conduct and Ethics, The University of Western Australia - Develop an enterprise data model that reflects the organization's fundamental

Client has taken naproxen sodium for migraine headache : A client has taken naproxen sodium for a migraine headache and now reports an upset stomach. What is the best information the nurse could give to the client?

Explain recent global warming : Explain recent global warming. However, Soon failed to disclose this as a conflict of interest in most of his scientific papers

WGU Evidence Leveling Tool : The Evidence-Based Innovation Plan Template to appraise 5 relevant peer-reviewed scholarly sources using the WGU Evidence Leveling Tool located in Supporting

How the theoretical model or treatment described : Article Review, you must discuss how the theoretical model or treatment described in the article compares to the information discussed in your textbook.

User Account

All Pages