SQL database assessment task

Assessment 3
SQL
CSE2DCX Database Fundamentals on the Cloud2 © 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/academicintegrity/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 paperbased 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 value4 © 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 PostcodeEmail DOB
1001 Ken Adams 5 Shipton creek2064 abc@email.com 1988-04-12
1002 Ryu 1 Oakwood 3004 def@email.com 1990-01-18
1003 Dhal Sim 8 Ivory lane 5000 ghi@email.com 1991-03-07
1004 Elvis Honda 2 Bathhouse 2000 jkl@email.com 1993-04-17
1005 Mike Balrog 9 Club road 3174 mno@email.com 1996-06-21
1006 Martin Bison 12 Show
Ground
4000 pqr@email.com 1990-07-12
1007 Chun Li 6 Market street 2000 stu@email.com 1984-09-18© Didasko 2019. All rights reserved. 5
1008 Zan Gif 7 Mill View 3010 vwx@email.com 1998-02-02
1009 Sagar 11 Temple
wood
3064 yz@email.com 1986-03-01
1010 Warren Gates 3 Wind
Crescent
2000 123@email.com 1984-09-18
1011 Jeff Buffet 9 Chariot point 3010 456@email.com 1997-02-02
1012 Steve Bezos 11 Riverview 3063 789@email.com 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 $1406 © 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 (Price * number of days). Number of days can be calculated
by using pickup and return dates. [2 marks]
Task 20
Write a query to display all information about the Customer who made the highest
transaction. It is expected that the newly created amount due column be used to
identify the highest transaction. [2 marks]
Task 21
Write a query to display the number of transactions for each type of car. The
output should have a separate row for each type of car such as sedan, hatch, ute
etc. [2 marks]
Task 22
Write a query to identify emails addresses that are incorrect (missing @). Provide a
screenshot of the output. [2 marks]
Task 23
Write a query to display the day on which the CEO joined the company. Hint: There
are functions that can be used to display day of week for dates.
[2 marks]
Task 24
Write a query to display information about the current user (This is the user you have
logged in as) Hint: Data dictionary. [2 marks]
Task 25© Didasko 2019. All rights reserved. 9
Write a query to display all information about the manager who manages the
greatest number of employees. Hint: The manager column in the employee table
contains eID (employee ID) for Managers. [2 marks]
Task 26
Write a query to display each employees’ fName, lName, and commission.
Commission is calculated as 2.5% of the transaction value. Hint: Have a look at the
transaction table and you will find the employee who was responsible for each
transaction. [3 marks]
Task 27
This task requires you to allocate shifts to employees. You can manually decide which
employee works which shift as long as you follow the following rules:
1. No employee will work more than 38 hours
2. Each shift will have a manager or CEO.
How this is implemented is up to you. One approach may be to add new columns to
the existing shift and employee tables, or you could instead create a new table all
together. Hint: This is a typical “many to many” relationship as one shift can have
many employees and each employee can be part of multiple shifts.
Note: You can write up to three queries to complete this task (the fewer the better
though). [4 marks]
Task 28
Write a query to display the employees who will be working today. You should
use a built-in function to identify which day of the week it is today. Use this day
to extract information form the timetable (tTask 27).
[3 marks]
Submitting your assignment
When you have completed your answers, submit the assessment on the Learning
Portal. You should submit the following:
• Submit your answers in a document called xxx_cse2dcx_assessment3.docx
(where xxx is your student number) for your answers for Tasks 1-28.
Assessment Marking Criteria10 © Didasko 2020. All rights reserved. 10
Task 1: Creating the
Customer table.
Marks are awarded
for (a) syntax; (b)
primary key; (c)
fields/types/sizes;
and (d) constraints.
Solution
was not
correct. 0
marks
Solution
was correct
for 1
aspect.
0.25 marks
Solution
was
correct for
2 aspects.
0.5 marks
Solution
was
correct for
3 aspects.
0.75
marks
Solution
was fully
correct. 1
mark
Task 2: Creating the
Transaction table.
Marks are awarded
for (a) syntax; (b)
primary key; (c)
fields/types/sizes;
and (d) constraints.
Solution
was not
correct. 0
marks
Solution
was correct
for 1
aspect.
0.25 marks
Solution
was
correct for
2 aspects.
0.5 marks
Solution
was
correct for
3 aspects.
0.75
marks
Solution
was fully
correct. 1
mark
Task 3: Creating the
Car table. Marks are
awarded for (a)
syntax; (b) primary
key; (c)
fields/types/sizes;
and (d) constraints.
Solution
was not
correct. 0
marks
Solution
was correct
for 1
aspect.
0.25 marks
Solution
was
correct for
2 aspects.
0.5 marks
Solution
was
correct for
3 aspects.
0.75
marks
Solution
was fully
correct. 1
mark
Task 4: Creating the
Employee table.
Marks are awarded
for (a) syntax; (b)
primary key; (c)
fields/types/sizes;
and (d) constraints.
Solution
was not
correct. 0
marks
Solution
was correct
for 1
aspect.
0.25 marks
Solution
was
correct for
2 aspects.
0.5 marks
Solution
was
correct for
3 aspects.
0.75
marks
Solution
was fully
correct. 1
mark
Task 5: Creating the
Shift table. Marks
are awarded for (a)
syntax; (b) primary
key; (c)
fields/types/sizes;
and (d) constraints.
Solution
was not
correct. 0
marks
Solution
was correct
for 1
aspect.
0.25 marks
Solution
was
correct for
2 aspects.
0.5 marks
Solution
was
correct for
3 aspects.
0.75
marks
Solution
was fully
correct. 1
mark
Task 6: Customer data. Customer data was not
added to the table. 0 marks
Customer data was
successfully added to the
table. 1 point© Didasko 2019. All rights reserved. 11
Task 7: Transaction data. Transaction data was not
added to the table. 0 marks
Transaction data was
successfully added to the
table. 1 point
Task 8: Car data Car data was not added to
the table. 0 marks
Car data was successfully
added to the table. 1 point
Task 9: Employee data. Transaction data was not
added to the table. 0 marks
Transaction data was
successfully added to the
table. 1 point
Task 10: Shift data Car data was not added to
the table. 0 marks
Car data was successfully
added to the table. 1 point
Task 11: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 12: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 13: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 14: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 15: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 16: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 17: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 18: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.12 © Didasko 2020. All rights reserved. 12
Task 19: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 20: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 21: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 22: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 23: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 24: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 25: Marks are
awarded for
table/field names (1);
and condition (1).
Solution was not
correct. 0 marks
Solution had 1
error. 1 mark
Solution was
correct for all
aspects. 2 marks.
Task 26: Marks are
awarded for
table/field names (1);
and condition (2).
Solution was not
correct. 0 marks
Solution had
multiple
errors. 1 mark
Solution had
1 error. 2
marks
Solution was
correct for all
aspects. 3
marks.
Task 27: Marks are
awarded for
table/field names (2);
and condition (2).
Solution
was not
correct.
0 marks
Solution
had
multiple
errors.
1 mark
Solution was
correct for
half of the
aspects. 2
marks
Solution had
1 error. 3
marks
Solution was
correct for all
aspects. 4
marks.
Task 28: Marks are
awarded for
table/field names (1);
and condition (2).
Solution was not
correct. 0 marks
Solution had
multiple
errors. 1 mark
Solution had
1 error. 2
marks
Solution was
correct for all
aspects. 3
marks
Order Now

Calculate a fair price for your paper

Such a cheap price for your free time and healthy sleep

1650 words
-
-
Place an order within a couple of minutes.
Get guaranteed assistance and 100% confidentiality.
Total price: $78
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?