Database and Design Assignment

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 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.

- End of assignment -