HS2021 Database Design and Use Individual Assignment questions and answers tutorial

Posted on 20th Aug 2019 08:49:17 in Questions and Answers

Submission Requirements  Create a text file that contains all of the statements required for all tasks of this assignment.  Your scripts must work with Oracle Live SQL  You must submit your assignment via Blackboard submission link by the due date. (Note: Late submissions will attract a penalty)  In week 8 Lab, you may request to demonstrate your assignment to your tutor. Make sure that you have a soft copy of the script with you in the lab. 1. Section 1 A database analyst has developed the following ER Diagram:

Each product is assigned to one or more managers. It is the responsibility of a manager to perform a quality check once a week on each product that they have been assigned to. For each quality check performed by a manager, the week no and score (a value between 1 and 3) is recorded. 3.1.Drop Tables Write SQL DROP statements that will drop all the additional tables. Add these statements to section 1.2 of the script file. 3.2. Create Tables Write SQL CREATE TABLE statements to create all additional the tables. Add these statements to the appropriate location within the script file. Note:  All tables must have primary keys.  All tables must have appropriate foreign key constraints.  Each foreign key column must have identical column name, data type and size of the primary key that it refers to Page 10 of 13  Add any NOT NULL constraints as dictated by the ERD  Choose your own appropriate column data types and sizes 3.3. Insert Managers Write SQL INSERT statements that add the data shown to the MANAGER table. Add these statements to the appropriate location within the script file. ID Firstname Surname 101 Bob Starkie 102 Shirley Strachan 103 Greg Macainch 3.4. Insert Allocations Write SQL INSERT statements for the ALLOCATION table to assign products to managers. Add these statements to the appropriate location within the script file. Product ID ManagerID 31 101 32 102 32 103 33 103 34 103 35 102 36 101 36 102 3.5. PK Error Write these SQL INSERT statements that attempt to add the data shown to the ALLOCATION table. These statements must fail. If they don't fail, there is a problem with your Primary Key constraint clause in your Create Table statement. Add these statements to the appropriate location within the script file. Product ID ManagerID 35 102 36 101 Page 11 of 13 3.6. Insert Quality Checks Write SQL INSERT statements for the QUALITYCHECK table to record scores awarded by managers. Add these statements to the appropriate location within the script file. Product ID ManagerID Week Number Score 31 101 1 3 31 101 2 2 31 101 3 3 32 102 1 1 32 102 2 2 32 102 3 1 32 103 1 2 32 103 2 1 32 103 3 1 33 103 1 1 33 103 2 3 33 103 3 3 3.7. PK Error Write these SQL INSERT statements that attempt to add the data shown to the QUALITYCHECK table. These statements must fail. If they don't fail, there is a problem with your Primary Key constraint clause in your Create Table statement. Add these statements to the appropriate location within the script file. Product ID ManagerID Week Number Score 31 101 3 3 32 102 1 1 3.8. FK Error Write these SQL INSERT statements that attempt to add the data shown to the QUALITYCHECK table. These statements must fail. If they don't fail, there is a problem with your Foreign Key constraint clause in your Create Table statement. Add these statements to the appropriate location within the script file. Product ID ManagerID Week Number Score 31 101 1 2 Page 12 of 13 34 101 1 3 36 103 2 2 31 102 3 3 32 101 1 1 3.9. List all rows in additional Tables Write three SQL statements that will list all of the rows from all additional tables in ascending primary key sequence. Add these statements to the appropriate location within the script file. 4. Section 4: Write these SQL SELECT statements. Add each statement to the appropriate location within the script file. 4.1. List the order id, product id, product name, current price, sale price and the price difference for all products that appear in the ORDERLINE table. Only list rows where the price difference is greater than 10% of the product's current selling price. List in ascending price difference sequence. 4.2. Use a Union clause to list the names of all customers, salespersons and managers in surname / firstname sequence. Show the role of each person. 4.3. Use a UNION clause to list each order id and address. The list must be in ascending Order ID sequence. If the Shop Order delivery address is NULL, then you must display the customer's billing address instead of the delivery address. 4.4. List the order id, customer id, firstname & surname, product id & name for all rows in the ORDERLINE table. List in ascending customer id / order id / product id sequence. 4.5. List the product id, name and current price of all products that have a selling price greater the average current price of all products. List in ascending current price sequence 5. Section 5: 5.1. For each row in the SHOPORDER table, show the customer id & surname and order id. Ensure that every customer is the CUSTOMER table is listed – even if they have not created any shop orders. List in ascending customer id / order id sequence. 5.2. Count the number of shop orders for each customer. Ensure that every customer is the CUSTOMER table is listed – even if they have not created any shop orders. List in ascending customer id. 5.3. List order id, customer id, firstname & surname, product id and quantity sold for all rows in the ORDERLINE table. List in ascending customer id / order id / product id sequence. Page 13 of 13 5.4. List the product id, product name and total quantity sold for all products that appear in the ORDERLINE table. List in ascending total sequence / product id. 5.5. Same as above, but only show products where total sales are in the range 2 to 10 (inclusive) 5.6. Same as above, but only show products where total sales are greater than 1 that contain the word 'Lamp' or 'Table' in the product name. 5.7. List the average score in the QUALITY CHECK table for each product. Only show results if the average score is greater than or equal to 2.0. List an ascending average score sequence. 5.8. List the total orders for products with an average quality check score greater than or equal to 2.0. Do not 'hard code' product ids 33 and 31 in your query as obviously data values will change over time