Example of ERD

Example of GRD

Please answer all of question 1, while i, ii, are diagrams which u have to make. And the rest is a SQL Script

Q1. Basic database design and queries (6 marks) Argos is a small home product store. Argos wants to create a simple database to keep track of its products prices, customers and their sale orders. For simplicity, products will be recorded as a product which stores its name and price, each customer will be recorded for the name and address, and each sale order will be recorded for its date, all the products included in the order and whether the order has been paid yet. i. Design a minimum database (a database of only essential attributes) to fulfil this purpose, and draw the ER diagram for your design. Indicate on the ER diagram the primary keys and the relationship multiplicities. By minimum we mean that you don't need to add anything that is not explicitly stated in the requirements, unless it's one of your artificially generated keys. For instance, you don't need to include a telephone number or email address for the entity corresponding to a customer. (1 mark) ii. Draw the Global Relation Diagram (GRD) corresponding to the ER diagram in the above, indicating all the primary keys and foreign keys. The GRD should be in a similar form to Figure 17.9 (page 554 or 516 for edition 5) of the textbook. Note, all the attributes should be kept in this diagram too. (1 mark) iii. Write an SQL script (of statements) that generates all the tables for your designed database from above. (1 mark) iv. Write an SQL script to insert sufficient records into your tables. Each table should contain no less than 3 records. At least 2 sale orders should each contain 2 or more products of the ordered merchandise. Screenshots are required for the records of all the tables. (0.5 marks) v. For a given sale order (number), write an SQL statement to list all the product names and their corresponding prices for the sale order (screenshot required). (0.5 marks) vi. List all the sale orders by their order number, date, and the name and the address of the customer who places that order (screenshot required). (0.5 marks) vii. Write an SQL statement that lists the sale order (number) and the total price of the corresponding order, for all the orders in the database (screenshot required). 00.5 marks) viii. Extend the database so that a customer can opt for a home delivery of their ordered product. The home delivery fee will be charged according to the postcode of the customer address. In other words, each delivery to an address of the same postcode will incur the same delivery cost. Draw a separate ERD and a separate GRD for this extended database. (1 mark) rigure 0.s th alagram tor tne stamlient user views ot ureamnome witn primary keys aadea. 554 Chapter 17 Methodology-Logical Database Design for the Relational Model