(115 marks)

2. Completed major steps in the physical database design:

Note: If your code cannot be run through successfully by the marker (including executing all the

submitted code as a whole, and re-running the code to start the whole code again by clicking

"Execute"), you will get zero marks for section 2. So it is strongly suggested that you try your code

with a different account and on a different computer to make sure it will work at the marker's end.

(Note: to meet this requirement, it is essential that the codes for dropping all tables are included and

work. If the database cannot be created by clicking "Execute", or if the database cannot be re-created by

clicking "Execute" again, zero (0) marks will be given for section 2.)

2.1 Write SQL scripts that create the normalised Numberone Pizza database, including all necessary

tables with the right parameters such as primary key, foreign key, and default value.

+ Right tables according to right normalised tables (for each unimplemented table-1) (25)

+ Right Foreign Keys, all with referential actions (0.5 for each FK. If an FK has not had the

definition "on Update ... on Delete...", -0.25. At least 5 FKs should use "ON UPDATE

CASCADE, ON DELETE CASCADE, otherwise -10)

+ Right attributes & data types

(15)

(2)

(50)

+ Right Primary key

+ Have at least 2 Not Nulls in the database

+ Have at least 3 defaults in the database

2.2 Write SQL statements satisfying the transaction requirements including the following

2.2.1 Input proper data (as you consider legitimate) of at least five rows for every table (25)

2.2.2 Implement the following queries

(40)

Note: if the void output is shown, no mark for each query.

Q.1 For an instore staff with id number xxx, print his/her first name, last name, and hourly payment

rate. (8)

(65)

Q.2 List all the shift details of a delivery staff with first name xxx and last name ttt between date yyy

and zzz. (8)

Q.3 List all the order details of the orders that are made by a walk-in customer with first name xxx and

last name ttt between date yyy and zzz. (8)

Q.4 List the names of the menuitems that are ordered in the current year. Note the current year is the

current year that is decided by the system. (8)

Q.5 List the name(s) of the ingredient(s) that was/were supplied by the supplier with supplier ID xxx

on date yyy. (8)