Description

COMP9120 Database Management Systems

Assignment 2: Logical Database Design

Group assignment (15%)
Introduction
This assignment is about the logical database design for an ER diagram included in this assignment description. The objectives are to gain practical experience in relational database schema creation, including integrity constraints, based upon a given entity-relationship (ER) diagram.
Submission Details
Items for submission
• Firstly, you should submit a sql file (.sql file suffix) containing all DDL statements necessary to fully instantiate a working database for the ER diagram, and DML statements to populate each relation. Your file should run without errors in PostgreSQL 9.5. You can annotate your statements using ‘–‘ at the start of lines for comment. You should group your statements for ease of reading (e.g., by keeping all table constraints within the relevant CREATE TABLE statement rather than declaring them externally, if possible).
• Secondly, you should submit a pdf file (.pdf file suffix) including the relational model (RM) diagram that provides a visual model of your database schema. The figure below summarises the syntax to use for the RM diagram.

1
COMP9120 Assignment 2
Design Brief: Relational Database Schema for a Restaurant Home Delivery System
Your task is to create a relational database schema for the entity-relationship diagram that is shown on page 3. In particular, your solution should include:
• Tables and attributes with suitable data types to capture all information in the model (please use the same names as in the ER diagram for naming tables and attributes);
• Appropriate PRIMARY KEY, UNIQUE, FOREIGN KEY constraints for all tables;
• Correct foreign key specifications including ON DELETE clauses where suitable;
• Appropriate additional integrity constraints expressed by means of NOT NULL, or CHECK clauses;
• INSERT statements to populate each relation with at least one record, to demonstrate a database instance consistent with the ER model.
Additional details
In addition to the model shown in the ER diagram, the following details apply:
1. Fields in a tuple related to dates and times should always have values.
2. All fields in a tuple relating to details about a name (eg: Menu Item Name, First Name, etc) should always have a value.
3. The total charge of an order, the quantity and charge for an order item, and the price for a menu item should always have values.
4. Customers must have a specified mobile number.
Escaping PostgreSQL keywords in DDL
If you need to escape PostgreSQL keywords like “Table”, you will need to use double quotes.
E.g. CREATE TABLE “Table” (…);
Q&A
Q: How to draw the link from foreign key in a table to its referenced candidate key in another table if the foreign key contains more than one attributes?
A: You should draw it in a similar way to the following RM diagram that has been used in Week 4’s homework (specifically, see the Sell table).

2

COMP9120 Assignment 2
Marking
This assignment is worth 15% of your final grade for the unit of study.
Your group’s submission will be marked according to the attached rubric.
Group member participation
Level of contribution Proportion of final grade received
No participation. 0%
Full understanding of the submitted work. 50%
Minor contributor to the group’s submission. 75%
Major contributor to the group’s submission. 100%
Marking Rubric
Your submissions will be marked according to the following rubric, with a maximum possible score of 15 points.
Novice (0 pts) Competent (1.5 pts) Proficient (3 pts)
Relational
Mapping less than competent schema of the given scenario all main entities and relationship mapped correctly to relations, with reasonable choice of data type for most attributes the core model was very well mapped to a relational schema and good choice of data types for all attributes
Key Constraints no key constraints captured at all some primary and foreign keys were defined, but either incorrectly or incomplete all necessary primary keys and foreign keys are given including useful ON DELETE clauses
Semantic Constraints no further integrity constraints given some integrity constraints such as CHECK or NOT NULL were defined, but either incorrectly or incomplete all necessary integrity constraints for the model were given
Example Data no example data given or yielded multiple errors some table examples missing or generated an error database fully populated with a consistent set of data
RM Diagram no RM diagram submitted RM diagram does not exactly match the relational schema created by the submitted sql file RM diagram exactly matches the relational schema created by the submitted sql file (note that:
semantic constraints and example data are not required in the RM diagram).

4