hi I just need the answer for 4.3 and 5.8
Page 1 of 13 HS2021 Database Design and Use Individual Assignment Version 1 Assignment Value: 15% of your final mark Due Date/Time: End of week 7 11:59PM (See Blackboard for submission deadline) 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: Page 2 of 13 Create a script file named ASS1_SQL.TXT Add your Student ID and Student name the first lines of the script file. 1.1. Relationships Write the English sentences that best describe the ERD above. Place the text in the specified location in the file: ASS1_SQL.TXT Prefix each line with comment symbols -- (two hyphens) E.g.: --ONE Employee MUST belong to ONE Branch --ONE Branch MAY employ to MANY Employees --ONE Branch MUST belong to ONE Organisation -- ONE Organisation MAY employ to MANY Branches 1.2.Drop Tables Write SQL DROP statements that will drop all the tables. Add these statements to the appropriate location within the script file. 1.3. Create Tables Write SQL CREATE TABLE statements to create all 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 Add any NOT NULL constraints as dictated by the ERD The following columns data types and sizes must be used custid, prodid, ordid, spid number(4) cfirstname, csurname, billingaddress, deliveryaddress, prodname, spfirstname, spsurname varchar(30) cgender, spgender varchar(1) qtysold, qtydelivered number(4) saleprice, currentprice number(6,2) Page 3 of 13 The following constraints must be used Type Details Check Gender values must be M or F Check Prices must be in the range 0 to 5000 Check Quantities must be in the range 0 - 99 Foreign Key All foreign keys must have named constraints 1.4. Insert Customers Write SQL INSERT statements that add the data shown to the CUSTOMER table. Add these statements to the appropriate location within the script file. ID Name Gender Bill Address 1 Casey Cartwright F 1 High St Kew 2 Evan Chambers M 8 Red St Rye 3 Calvin Owens M 7 Long Rd Lara 4 Frannie Morgan F 9 Down Pde Upwey 5 Cappie Jones M 6 Mist St Toorak 6 Dana Stockwell F 2 Tree St Epping 7 Ash Howard F 4 Elm Ave Elwood 1.5. Check constraint error Write SQL INSERT statements that attempt to add the data shown to the CUSTOMER table. This statement must fail due to check constraints. Add these statements to the appropriate location within the script file. ID Name Gender Bill Address 8 Milton Hastings X 3 Blue St, Kew Page 4 of 13 1.6. Insert Products Write SQL INSERT statements that add the data shown to the PRODUCT table. Add these statements to the appropriate location within the script file. ID Name Price 31 Lounge Chair 799 32 Study Lamp 150 33 Large Desk 550 34 Hallway Table 1200 35 Kitchen Stool 220 36 Lamp Stand (Tall) 189 37 Zzz King Size Single Bed 400 38 Bedside Lamp 99 39 Coffee Table 650 1.7. Check constraint error Write SQL INSERT statements that attempt to add the data shown to the PRODUCT table. This statement must fail due to check constraints. Add these statements to the appropriate location within the script file. ID Name Price 40 Cupboard -99 41 Bookcase 8765 1.8. Insert Salespersons Write SQL INSERT statements that add the data shown to the SALESPERSON table. Add these statements to the appropriate location within the script file. ID Name Gender 21 Serena Van der Woodsen F 22 Dan Humphrey M 23 Blair Waldorf F 24 Chuck Bass M 25 Lily Van der Woodsen F 26 Nate Archibald M Page 5 of 13 1.9. Insert Shop Orders Write SQL INSERT statements that add the data shown to the SHOPORDER table. Add these statements to the appropriate location within the script file. Customer 1 Orders: ORDID DELIVERADDRESS SALESPERSON 41 NULL 23 51 NULL 23 Customer 2 Orders: ORDID DELIVERADDRESS SALESPERSON 42 NULL 21 43 NULL 23 49 NULL 24 Customer 3 Orders: ORDID DELIVERADDRESS SALESPERSON 44 1 John St Hawthorn 26 48 NULL 26 Customer 4 Orders: ORDID DELIVERADDRESS SALESPERSON 45 1254 Dunstall Rd Coorparoo 22 47 727 Hudson Rd Glenorchy 26 50 517 Franklin St Dowerin 22 Customer 6 Orders: ORDID DELIVERADDRESS SALESPERSON 46 NULL 21 1.10. FK Error Write these SQL INSERT statements that attempts to add the data shown to the SHOPORDER table. These statements must fail. If they don't fail, there is a problem with your Foreign Key Page 6 of 13 constraint clause in your Create Table statement. Add these statements to the appropriate location within the script file. Customer 5 Orders: ORDID DELIVERADDRESS SALESPERSON 66 NULL 29 Customer 9 Orders: ORDID DELIVERADDRESS SALESPERSON 67 NULL 26 1.11. Insert Oder Lines Write SQL INSERT statements that add the data shown to the ORDERLINE table. Add these statements to the appropriate location within the script file. Order 41: PRODID QTYSOLD QTYDELIVERED SALESPRICE 31 2 0 750 36 1 0 170 Order 42: PRODID QTYSOLD QTYDELIVERED SALESPRICE 33 1 1 500 Order 43: PRODID QTYSOLD QTYDELIVERED SALESPRICE 35 6 5 220 Order 44: PRODID QTYSOLD QTYDELIVERED SALESPRICE 31 1 0 760 34 1 0 1100 36 1 0 180 Page 7 of 13 Order 45: PRODID QTYSOLD QTYDELIVERED SALESPRICE 36 2 2 175 Order 46: PRODID QTYSOLD QTYDELIVERED SALESPRICE 37 2 2 380 38 2 2 90 Order 47: PRODID QTYSOLD QTYDELIVERED SALESPRICE 39 1 0 600 35 1 0 200 36 2 0 175 38 1 0 85 Order 48: PRODID QTYSOLD QTYDELIVERED SALESPRICE 34 1 1 1200 Order 49: PRODID QTYSOLD QTYDELIVERED SALESPRICE 38 1 1 90 Order 50: PRODID QTYSOLD QTYDELIVERED SALESPRICE 31 2 0 750 36 1 0 180 Order 51: PRODID QTYSOLD QTYDELIVERED SALESPRICE 35 10 10 190 Page 8 of 13 1.12. FK errors Write these SQL INSERT statements that attempt to add the data shown to the ORDERLINE 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. Order 49: PRODID QTYSOLD QTYDELIVERED SALESPRICE 40 2 2 200 Order 52: PRODID QTYSOLD QTYDELIVERED SALESPRICE 36 10 10 175 1.13. List rows in all Tables Write five SQL statements that will list all of the rows in all tables in ascending primary key sequence. Add these statements to the appropriate location within the script file. 2. Section 2 Add each statement to the appropriate location within the script file. 2.1.1 Count the total number of orders in the SHOPORDER table. 2.1.2 Count the total number of orders that have been made by female customers. 2.1.3 Count the total number of orders that have been made by each gender of customer. 2.1.4 List the order id, customer id, firstname & surname for all shop orders where the customer is female. List in ascending customer id / order id sequence 2.1.5 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 9 of 13 3. Section 3 The database analyst has modified the existing 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