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 代写