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.
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
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?
