Assessment 3 SQL CSE2DCX Database Fundamentals on the Cloud 2 © Didasko 2020. All rights reserved. 2 Before you begin Objectives This is an individual assignment. Students are not permitted to work in...

Assessment 3 SQL CSE2DCX Database Fundamentals on the Cloud 2 © Didasko 2020. All rights reserved. 2 Before you begin Objectives This is an individual assignment. Students are not permitted to work in a group when writing this assignment. Copying and Plagiarism This is an individual assignment. Students are not permitted to work in a group when writing this assignment. Plagiarism is the submission of another person’s work in a manner that gives the impression that the work is their own. La Trobe University treats plagiarism seriously. When detected, penalties are strictly imposed. Further information can be found on http://www.latrobe.edu.au/students/academic- integrity/explanation/plagiarism Submission Guidelines Your assignment submission should be typed, not written/drawn by hand. Submit the electronic copy of your assignment through the subject LMS. Submission after the deadline will incur a penalty of 5% of the available assignment mark per day capped at 5 days. No assignment will be accepted after 5 days. If you have encountered difficulties that lead to late submission or no submission, you should apply for special consideration. © Didasko 2019. All rights reserved. 3 Background The Antiquity Car Rentals company is stuck in the past. For years the organisation has depended on manual information recording procedures to store information about their customers, sales staff and cars. The rental car company maintains these different types of information in separate paper- based files. As you can imagine, retrieving a required record from a massive file is both difficult and time consuming, as is extracting information for the development of reportsApart from these problems storing many paper-based files requires a significant amount of storage space. To overcome these issues Antiquity Car Rentals has decided to finally take advantage of modern technology to create an application that will allow them to perform these functions in a far more efficient manner. Your organisation has been hired by Antiquity to assist with the digitization of their records. You have been tasked to handle the initial work on the database systems, which will consist of constructing tables, populating data and running SQL queries. It has been decided that the initial database will contain 5 tables. • CUSTOMER • TRANSACTION • CAR • EMPLOYEE • SHIFT The list of attributes and data for the CUSTOMER, TRANSACTION, CAR, EMPLOYEE, and SHIFT tables are given in Tables 1, 2, 3, 4, and 5 respectively (see tasks 6, 7, 8, 9 and 10). Login to the AWS RDS SQL Server database using the credentials (username and password) that you have created earlier. If you are unable to log on, please do not proceed any further and contact the academic team for advice. Instructions: Provide a screenshot of the output for all tasks. Failing to do so may result in your assessor being unable to award marks for that task. Note that it is recommended that you carefully consider the order in which queries are run to ensure foreign keys are created correctly. You will not receive marks for queries that contain hardcoded values for instance, the highest value car in the database may currently be a car with an ID of 1005. If a query asks you to identify the highest value car it would not be expected that the clause “Where Car = 1005” would be used, as the value of ‘1005’ is hardcoded (and furthermore the actual ‘highest value’ car in the database could change in the future) Instead it would be expected that the query should retrieve the correct record without needing to directly refer to any specific value 4 © Didasko 2020. All rights reserved. 4 Tasks Task 1 Login to the SQL Server and create the CUSTOMER table using SQL statements (refer Table 1 below). Write the finalised SQL statements that you used to create the table. [1 mark] Task 2 Write the finalised SQL statements that you used to create the table. Provide a screenshot of the output. [1 mark] Task 3 Write the finalised SQL statements that you used to create the table. [1 mark] Task 4 Write the finalised SQL statements that you used to create the table. [1 mark] Task 5 Write the finalised SQL statements that you used to create the table. [1 mark] Task 6 Write a query to insert the following data to populate the CUSTOMER table. Make any reasonable modifications so that the data matches your field names and data types and conform with integrity constraints. Table 1: CUSTOMER Table cID fName lName Address Postcode Email DOB 1001 Ken Adams 5 Shipton creek 2064 [email protected] 1988-04-12 1002 Ryu 1 Oakwood 3004 [email protected] 1990-01-18 1003 Dhal Sim 8 Ivory lane 5000 [email protected] 1991-03-07 1004 Elvis Honda 2 Bathhouse 2000 [email protected] 1993-04-17 1005 Mike Balrog 9 Club road 3174 [email protected] 1996-06-21 1006 Martin Bison 12 Show Ground 4000 [email protected] 1990-07-12 1007 Chun Li 6 Market street 2000 [email protected] 1984-09-18 © Didasko 2019. All rights reserved. 5 1008 Zan Gif 7 Mill View 3010 [email protected] 1998-02-02 1009 Sagar 11 Temple wood 3064 [email protected] 1986-03-01 1010 Warren Gates 3 Wind Crescent 2000 [email protected] 1984-09-18 1011 Jeff Buffet 9 Chariot point 3010 [email protected] 1997-02-02 1012 Steve Bezos 11 Riverview 3063 [email protected] 1986-03-01 1013 Bill Jobs 15 Apple court 2000 10@emailcom 1996-06-21 1014 Mansa Musa 13 Timbuktu 3172 A1email.com 1999-07-12 [1 mark] Task 7 Write a query to insert the following data to populate the TRANSACTION table. Make any reasonable modifications so that the data matches your field names and data types and conform with integrity constraints. Table 2: TRANSACTION Table tID cID carID eID tDate PickupDate ReturnDate 1001 1005 1001 105 2018-04-12 2018-04-22 2018-04-26 1002 1006 1004 104 2018-04-18 2018-04-28 2018-04-29 1003 1003 1005 104 2018-04-12 2018-04-21 2018-04-22 1004 1004 1001 105 2018-04-17 2018-04-21 2018-04-27 1005 1008 1006 106 2018-04-21 2018-04-23 2018-04-25 1006 1001 1001 106 2018-04-21 2018-04-29 2018-05-21 [1 mark] Task 8 Write a query to insert the following data to populate the CAR table. Make any reasonable modifications so that the data matches your field names and data types. Table 3: CAR Table CarID Make Model Type Year Price 1001 Toyota Camry Sedan 2018 $150 1002 VW Passat Sedan 2016 $160 1003 Mazda CX5 C-SUV 2017 $190 1004 Ford Focus Hatch 2019 $140 mailto:[email protected] 6 © Didasko 2020. All rights reserved. 6 1005 Toyota HiLux Ute 2018 $250 1006 Kia Carnival Minivan 2017 $200 [1 mark] Task 9 Write a query to insert the following data to populate the EMPLOYEE table. Make any reasonable modifications so that the data matches your field names and data types and conform with integrity constraints. Table 4: EMPLOYEE Table Eid fName lName Job Manager Hired 101 Adam Smith Manager 102 1998-04-12 102 Marry Jane CEO 1997-01-18 103 Eliza Rynd Manager 102 1999-03-07 104 Imran Khan Sales 101 2003-04-17 105 Wally Ham Sales 101 2006-06-21 106 Jack Hobbs Sales 101 2000-07-12 107 Don Bradman Advertisin g 101 2014-09-18 108 Nawab Pat Accounts 103 2008-02-02 109 Jack Odumbe Accounts 103 2016-03-01 [1 mark] Task 10 Write a query to insert the following data to populate the SHIFT table. Make any reasonable modifications so that the data matches your field names and data types. Table 5: SHIFT Table sID Day startTime endTime 1 Monday 0900 1700 2 Tuesday 0900 1700 3 Wednesday 0900 1700 4 Thursday 0900 1700 5 Friday 0900 1700 6 Friday 1200 2000 7 Saturday 0900 1700 © Didasko 2019. All rights reserved. 7 8 Saturday 1200 2000 9 Sunday 1000 1600 [1 mark] Task 11 Write a query to display all details of Customers who do not have their last name recorded in the database. [2 marks] Task 12 Write a query to display all details of Customers who live in the Sydney CBD (postcode 2000). [2 marks] Task 13 Write a query to display all details of transactions that were made at least one week before the pickup date. [2 marks] Task 14 Write a query to display all information regarding all tables that are present in the current database. The output should contain all database tables, including those which were created by the system. (Hint: Use consider using data dictionary). [2 marks] Task 15 Create a view called ‘Young_Customers’, that displays all information for Customers who are less than 25 years of age. [2 marks] Task 16 Write a stored procedure called ‘Tax_Due’. The procedure should require a transaction id as input and display the tax for that transaction. Tax is calculated as 10% of the transaction value (transaction value = car price * number of days). Execute the procedure for transaction 1002. [2 marks] Task 17 Write a trigger called ‘TransactionInfo’. The trigger should run whenever a new row is added or an existing row is updated in the Transaction table. It should display the transaction id, car make, car model, pickup and return dates. 8 © Didasko 2020. All rights reserved. 8 A query should be written to test the trigger. [2 marks] Task 18 Write a query to add a new column ‘Amount_Due’ to the TRANSACTION table. This column will be used to represent the total amount due for a Transaction. [2 marks] Task 19 The newly added Amount_Due column in the TRANSACTION table should currently contain only NULL values. Write a query to assign values to this column. The amount due will be calculated as