115 marks 2 completed major steps in the physical database design note
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
+ 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
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
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)
*The amount will be in form of wallet points that you can redeem to pay upto 10% of the price for any assignment. **Use of solution provided by us for unfair practice like cheating will result in action from our end which may include permanent termination of the defaulter’s account.Disclaimer:The website contains certain images which are not owned by the company/ website. Such images are used for indicative purposes only and is a third-party content. All credits go to its rightful owner including its copyright owner. It is also clarified that the use of any photograph on the website including the use of any photograph of any educational institute/ university is not intended to suggest any association, relationship, or sponsorship whatsoever between the company and the said educational institute/ university. Any such use is for representative purposes only and all intellectual property rights belong to the respective owners.