Description
Assignment 2: Logical Database Design
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.
This is a group assignment for teams of about 3 members, and it is assumed that you will continue in your Assignment 1 group. You should inform the teaching assistant as soon as possible if you wish to change groups.
Please also keep an eye on your email and any announcements that may be made on Canvas.
Submission Details
The final submission of your relational database schema is due at 11pm on the 1st of May. You should submit the items for submission (detailed below) via Canvas.
Items for submission
Please submit your solution to Assignment 2, in the ’Assignment’ section of the unit’s Canvas site by the deadline, including EXACTLY TWO files:
- 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.
School of Computer Science
RelationA
RelationB
keyA
attrib1
fkey
1
keyB
attrib2
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:
- Fields in a tuple related to dates and times should always have values.
- All fields in a tuple relating to details about a name (eg: Menu Item Name, First Name, etc) should always have a value.
- 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.
- Customers must have a specified mobile number.
- The TimeDelivered time/date should always be after TimeReady.
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
3
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
If members of your group do not contribute sufficiently, you should alert the teaching assistant as soon as possible. The course instructor has the discretion to scale the group’s mark for each member as follows:
|
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)
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
Proficient (3 pts)
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
no further integrity
some integrity constraints
all necessary integrity constraints
Constraints
constraints given
such as CHECK or NOT NULL were defined, but either incorrectly or incomplete
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
