MITS4003
Database Systems
Assignment 1
Due Date: August 20th, 2023
Semester 2 2023
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.
1. Synthesize user requirements/inputs and analyse the matching data processing needs, demonstrating adaptability to changing circumstances.
2. 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.
3. 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.
1. 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).
2. 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.
3. 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).
4. Membership Information: The system shall record information on AL members, including type of membership (Platinum/Gold/Silver), and when the membership will expire.
Assignment Requirements and Deliverables:
Part A - 10% (Due in week 5)
Submitted as a MS Word Document
Task 1: (Weightage 20%)
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: (Weightage 60%)
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: (Weightage 20%)
Derive the physical design having detail of entities, primary and foreign keys, detail of all the attributes.
Marking Guide:
Marking Guide: Unacceptable Acceptable Good Excellent
Task 1
(Weightage 20%) .
1.1 Entities
(Weightage 10%) Does not adequately have all required Entities in ER diagram Partially have all required Entities in ER diagram Most of the required
Entities are there in ER diagram All Entities are there in ER diagram
1.2 Attributes
(Weightage 5%) Attributes not identified or incorrectly identified. A few of the required attributes for each of the required entities are identified. Most of the required attributes for each of the required entities are identified. All the required attributes for each of the required entities are identified.
1.3 Unique
Identifiers
(Weightage 5%) Unique identifier not identified or incorrectly identified. Unique identifier for a few of the required entities is identified. Unique identifier for most of the required entities is identified Unique identifier for all of the required entities is identified
Task 2
(Weightage 60%)
2.1 Entities (Weightage 10%) Does not adequately demonstrate required entities in an ER diagram. A few of the required
entities are demonstrated correctly in an ER diagram. Most of the required
entities are demonstrated correctly in an ER diagram. All the required entities are demonstrated correctly in an ER diagram.
2.2 Attributes (Weightage 10%) Does not adequately demonstrate required attributes in an ER diagram. A few of the required attributes for each of the required entities are demonstrated correctly in an ER diagram. Most of the required attributes for each of the required entities are demonstrated correctly in an ER diagram. All the required attributes for each of the required entities are demonstrated correctly in an ER diagram.
2.3 Unique
Identifier
(Weightage 10%) Does not adequately demonstrate required Unique identifier in an ER diagram. Unique identifier for a few of the required
entities is
demonstrated correctly in an ER diagram Unique identifier for most of the required
entities is
demonstrated correctly in an ER diagram. Unique identifier for all of the required
entities is
demonstrated correctly in an ER diagram.
2.4 Relationships
(Weightage 30%) Does not adequately
identify and demonstrate relationships among entities in an ER diagram. A few of the required relationships among entities are identified and correctly demonstrated in an ER diagram. Most of the required relationships among entities are identified and correctly demonstrated in an ER diagram. All of the required relationships among entities are identified and correctly demonstrated in an ER diagram.
Task 3
(Weightage 20%)
3.1 Tables
(Entities)
(Weightage 5%) Relational schema does not adequately cover the required tables, and key and non-key attributes. Relational schema covers a few of the required tables, and key and non-key attributes for those tables. Relational schema covers most of the required tables, and key and non-key attributes for those tables. Relational schema covers all required tables, and key and non-key attributes for those tables.
3.2 Foreign Keys
(Weightage 10%) Relational schema does not adequately cover foreign keys. Relational schema covers required foreign keys in a few of the required tables. Relational schema covers required foreign keys in most of the required tables. Relational schema covers required foreign keys in all required tables.
3.3 Column (attributes) information (Weightage 5%) Does not adequately cover information about data columns. Provide required detail information for all data columns of a few of the tables. Provide required detail information for all data columns of most of the tables. Provide required detail information for all data columns of all required tables.