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).
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
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 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
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 $140
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 Advertising
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
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
-----
----
---
upto Task 28