Below is given an example design for a supermarket chain database design. For reference, both an ER-diagram and relational model description are included. Your task is to write SQL instructions to (1) create  a database implementing such a design, (2) populating the database with data, and (3) creating SQL statements according to a list of queries.

The relational model is as follows. Primary key attributes are written surrounded by _underscores_ emulating an underline.

• Outlet(_outlet_name_, address_street, address_city).
• Customer(_customer_nr_, customer_name, address_street, address_city).
• Product(_product_id_, product_name, description, standard_price).
• Category(_category_id_, description).
• Product_Category(_product_id_, _category_id_). The attribute product_id references the PK of Product. The attribute category_id references the PK of Category. (Note: This relation represents a many-to-many relationship, i.e., a product can belong to several categories.)
• Purchase(_purchase_id_, date_time, outlet_name, customer_nr). The attribute outlet_name references the PK of Outlet. The attribute customer_nr references the PK of Customer.
• PurchaseItem(_purchase_id_, _product_id_, copies, price_per_copy). The attribute purchase_id references the PK of Purchase. The attribute product_id references the PK of Product. (Note: This relation represents a many-to-many relationship, i.e., each purchase could contain many different products.)
• Stock(_outlet_name_, _product_id_, copies). The attribute outlet_name references the PK of Outlet. The attribute product_id references the PK of Product. (Note: This relation represents a many-to-many relationship, since each product is sold in many outlets and each outlet sells many products.)

Your tasks are the following.

1. Create tables

Create  a file createdb.sql containing SQL instructions for creating tables corresponding to the relations given above. [20 marks]

2. Populate tables

Create  a file inserts.sql containing a sequence of SQL INSERT statements to populate your tables. Every table should have at least 5 rows, since this is the minimum number of rows expected for testing purposes. [20 marks]

Advice: Take care not to accidentally make your data too simple in format! For example, if you create  a database where every purchase contains only one purchaseitem, and every product is a part of only one purchase, then there is a large risk that your SQL queries in task 3 contain bugs that your data did not reveal.

3. Write queries

Define queries, in SQL, to obtain the following information over the database you created. Note that these should work with any data that can be present in the model, not just for the test data you used. The queries must be included as part of your submission in a file named queries.sql.

To begin with, create two useful views to simplify further processing:

1. Create a view named purchase_item_total that adds the derived attribute item_total to purchase_item, where item_total contains the total price paid by the customer for the purchase_item line. That is, purchase_item_total contains attributes (purchase_id, product_id, copies, price_per_copy, item_total)whereitem_totalis the product ofcopiesandprice_per_copy`. [10 marks]
2. Using purchase_item_total, create a second view called purchase_total with two attributes (purchase_id, total_paid) where total_paid is the sum of the item_total across all purchase item lines in the purchase. [10 marks]

In both of these views, you are allowed to add extra attributes if you find it appropriate to do so.

With the views created, define SQL queriy instructions for the following requests. Your queries may of course use the views you just created.

1. A list of all purchases made at a particular outlet (for example, 'Egham Main Street'), ordered by time, giving for each the time, the number of different product types purchased, and the total price paid by the customer. 
2. A list of all customers who have made a purchase to a total value of more than 100 pounds at the same occasion, giving for each the name, address, the average purchase value, and the maximum purchase value. 
3. A list of all products, giving for each the name, the total number of copies in stock across all outlets, and the total number of copies sold. Order the list by number of copies sold in decreasing order. [10 marks]
4. A list of all cities where the total sales of products from category 10 are larger than from category 11, as measured by the total price paid for them. Give for each the city name, the number of outlets in the city, the total sales value for products from category 10, and the total sales value for products from category 11. The list should not contain any repeated cities. [10 marks]

Submission format

The submission is to be a ZIP file containing three files: createdb.sql, inserts.sql and queries.sql, as above. All three files must be plain text files, in a form that can be **directly fed into psql*. (For example, the instruction i createdb.sql at a psql prompt should create  all tables in the design.)

• If you want to add comments, recall that the comment markers in SQL are -- (two minus signs) for a single-line comment, or /* ... */ for a multi-line comment.

 

  

Transcribed Image Text:

copies productID price what catID name Product PCat Category purchase ID costPerCopy description timestamp Purchase copies Stock description where. boughtBy name Outlet street address city customer nr name Customer street address city copies productID price what catID name Product PCat Category purchase ID costPerCopy description timestamp Purchase copies Stock description where. boughtBy name Outlet street address city customer nr name Customer street address city

Answer rating: 100% (QA)

Based on the information provided and the ERdiagram I will outline the SQL statements you need for creating tables populating them and writing queries Since the question provides enough details to gen... View the full answer