Swinburne INF10004 Database, Analysis and Design 代写
100%原创包过,高质代写&免费提供Turnitin报告--24小时客服QQ&微信：120591129
Swinburne INF10004 Database, Analysis and Design 代写
Page 1 of 13
Swinburne University of Technology
Foundation and Pathways
INF10004 Database, Analysis and Design
Assignment 1
This assignment must be completed in your Groups
Due Date/Time: 5pm Thursday 6 April, 2017
Marks: 10% of your total marks for this subject.
Submission Requirements
One .zip file that contains
o A copy of the SQL text file named ASS1_999999_SQL.TXT that contains all of the
SQL statements required for all tasks of this assignment (where 999999 is your
student id)
o A complete copy of all output generated by ISQL JR as a result of executing your
script file pasted into an DOCX file named ASS1_999999_OUTPUT.DOCX Use the
entire output that appears beneath the Execute button in ISQL JR.
(Do not copy and paste only some lines of text.)
(You may submit a .pdf file rather than a .docx file if you wish)
Your script must work with Oracle iSQL Junior
You must submit your assignment via Blackboard submission link on or before the due date.
You must save a copy of your submitted zip file on Drive H:
This assignment is marked out of 100.
Section 1 40 marks
Section 2 10 marks
Section 3 20 marks
Section 4 10 marks
Section 5 20 marks
Page 2 of 13
1. Section 1: (40 marks)
A database analyst has developed the following ER Diagram:
Download and edit the script file named ASS1_999999_SQL.TXT
Add your Student ID(s) and Student name the first lines of the script file.
Warning : Do not use the & symbol anywhere in the script. It will cause many problems for you.
1.1. Relationships
Write the English sentences that best describe the ERD above.
Place the text in the specified location in the file: ASS2_999999_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
Page 3 of 13
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
varchar2(30)
cgender, spgender varchar2(1)
qtysold, qtydelivered number(4)
saleprice, currentprice number(6,2)
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 attempts 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.
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 attempts 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.
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.
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 DELIVERYADDRESS SALES PERSON
41 NULL 23
51 NULL 23
Customer 2 Orders:
42 NULL 21
43 NULL 23
49 NULL 24
Customer 3 Orders:
44 1 John St Hawthorn 26
48 NULL 26
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
Customer 4 Orders:
45 1254 Dunstall Rd Coorparoo 22
47 727 Hudson Rd Glenorchy 26
50 517 Franklin St Dowerin 22
Customer 6 Orders:
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 constraint
clause in your Create Table statement.
Add these statements to the appropriate location within the script file.
Customer 5 Orders:
Customer 9 Orders:
1.11. Insert Order 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
Order 42
Order 43
Order 44
Order 45
Order 46
Order 47
Order 48
Order 49
Order 50
Order 51
66 NULL 29
67 NULL 26
PRODID QTYSOLD QTYDELIVERED SALEPRICE
31 2 0 750
36 1 0 170
33 1 1 500
35 6 5 220
31 1 0 760
34 1 0 1100
36 1 0 180
36 2 2 175
37 2 2 380
38 2 2 90
39 1 0 600
35 1 0 200
36 2 0 175
38 1 0 85
34 1 1 1200
38 1 1 90
31 2 0 750
36 1 0 180
35 10 10 190
Page 6 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
Order 52
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: (10 marks)
Add each statement to the appropriate location within the script file.
2.1. Queries
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
PRODID QTYSOLD QTYDELIVERED SALEPRICE
40 2 2 200
PRODID QTYSOLD QTYDELIVERED SALEPRICE
36 10 10 175
Page 7 of 13
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
3. Section 3: (20 marks)
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.
Page 8 of 13
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
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 9 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
Swinburne INF10004 Database, Analysis and Design 代写
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
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.
Page 10 of 13
4. Section 4: (10 marks)
Write these SQL SELECT statements.
Add each statement to the appropriate location within the script file.
4.1.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.1.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.1.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.
Page 11 of 13
4.1.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.1.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
Page 12 of 13
5. Section 4: (20 marks)
Write these SQL SELECT statements.
Add each statement to the appropriate location within the script file.
5.1.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.1.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.1.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.1.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.1.5. Same as above, but only show products where total sales are in the
range 2 to 10 (inclusive)
5.1.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.1.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.1.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.
Swinburne INF10004 Database, Analysis and Design 代写
