1
Individual Assignment
In this assignment you are required to:
Design; implement a solution to a business problem based on the case study
given.
Implement the solution in Oracle.
Document the solution as set out in the assignment requirements.
Submit a CD containing the softcopy of both your documentation and
application.
o Your documentation file.
o Script file(s) that you used to create and populate the sample data.
o Script files(s) that answer all the questions in part 4(b).
Submit hardcopy of your documentation file.
Due Date:
BACHELOR OF INFORMATION TECHNOLOGY (HONS)
ADVANCED DATABASE SYSTEMS
CIT4144-TCS4424 (1702) Assignment 2.docx
CIT4144-TCS4424 (1702) Assignment 2.docx
2
Case Study.
VFR Renting is a nationwide vehicle rent company that provides vehicles for customers to
rent. A rent agreement between the company and the customer set out the terms of the rent
such as the rent period, rent charges and the restrictions of use.
Vehicles can be either cars or vans. Both have attributes make, model, year made, license
plate no, cost, odometer, maximum speed and number of passengers. Information specific to
car is car class (standard, luxury, executive) whereas for mini vans are no of seaters.
Rental charge for car depends on the car model (Vios, MyVi, City and etc.) and rental period
(daily, weekly, monthly) whereas rental charge for van depends on model (Hiace, Quest,
Odyssey and etc.) and rental period same as car.
VFR Renting has one depot and one “rent point” in every major city. Vehicles are stored at
a depot unless they are on rent to customers.
A rent point is a place where a customer collects and returns their rent vehicle. One rent
point has access to several depots and each depot may supply vehicles to many rent points.
Employees of VFR Renting work at either a depot or at a rent point. Depot employees are
mainly employed preparing vehicles for rent which may involve repairing or maintaining
vehicles. Rent point employees are mainly office staff involved with sales and marketing.
Depot employees do not move but rent point employees frequently move from one rent point
to another as demand for workers varies.
Note: You need to justify any additional assumption that you introduce.
1) Develop an E/R diagram using Chen notation that you learn in the course for the case
study described above. Make sure to indicate all cardinality constraints specified above.
The E/R diagram should not contain redundant entity sets, relationships, or attributes.
Also, use relationships whenever appropriate. You will be expected to use advanced
modeling techniques (EER diagram)
Note: If you need to make any assumptions, include them in your answer.
(25 marks)
2) Map the ER or EER diagram you created in part 1) into a set of relational tables at least in
3NF clearing showing the primary keys, foreign keys and attribute/column names
assigned to each relation. You will be expected to justify any mapping issues by
showing all the steps..
(25 marks)
CIT4144-TCS4424 (1702) Assignment 2.docx
3
3) Implement the relational tables for your answer in part 2) using Oracle (write SQL
statements to create the relations). You will be expected to demonstrate appropriate
constraints (entity, referential, column/domain constraints and tables, entity integrity,
tables, referential integrity, appropriate test data (note: please design your test data
based on the queries asked below (see part 4).
Please insert at least 5 rows of relevant data into parent tables and at least 10 rows of data
into the child tables.
You are required to describe the structure and show the contents of all relational tables.
(25 marks)
4) Create query to answer the following questions. .You must be able to explain the queries
and justify the approach taken if the need arises. Marks will be reduced where you cannot
explain how they have arrived at their solutions.
(25 marks – 5 marks each)
(a). List the details of customers (Name, Address) who last name start with ‘A’ and
first name end with ‘e’.
(b). List pair of cars that are of the same model (display both the license plate no and
model)
(c). List those employees who have moved their employment within the company.
The output must include the Employee Name, Starting Date, Leaving Date, Depot,
Rent point, Job Title.
(d). List the License plat no, Make, Model, Date of last service, Year Made, Odometer
reading for each van held at all the depots.
(e). For each rent point, list the number of vehicles that are currently on rent.
.
(Total 25 marks – 5 marks each)
CIT4144-TCS4424 (1702) Assignment 2.docx
4
Marking Scheme
80 and above 70 and above 60 and above 50 and above Less than 50 Marks/Remarks
ERM (25%) The ERM model will
be without error.
Advanced data
modelling features
will be used.
The ERM model
will be
implementable and
errors and/or
omissions will be
minor. Advanced
data modelling
features will be
used.
The ERM model
will be
implementable
although with some
errors and/or
omissions.
The ERM model
will be
implementable
although with errors
and/or omissions.
The ERM model
will not be
implementable or
will not cover major
aspects of the
scenario.
Relational
Schema
(25%)
The relational
schema, dependency
diagram, integrity
constraints and
justification for
mapping are of high
standard.
The relational
schema, dependency
diagram, integrity
constraints and
justification for
mapping attempted
and satisfactory.
The relational
schema, dependency
diagram, integrity
constraints and
justification for
mapping attempted
with some minor
errors and omissions
The relational
schema, dependency
diagram, integrity
constraints and
justification for
mapping attempted
with some errors
and omissions
The relational
schema, dependency
diagram, integrity
constraints and
justification for
mapping attempted
with major error and
and/or omissions
are likely
Database
(25%)
The database will be
implemented to a
high standard of
functionality and will
be appropriately
normalized.
The database will
function and will be
appropriately
normalized although
with some minor
errors and/or
omissions.
The database will
function and will be
appropriately
normalized although
with some errors
and/or omissions.
The database will
function although
with some errors
and/or omissions.
The database may
have limited
functionality and
major errors and/or
omissions are likely
Queries
(25%)
Queries will have
been attempted and
all or most queries
will function
Queries will have
been attempted and
most queries will
function.
Queries will have
been attempted and
the majority of
queries will
function.
Queries will have
been attempted and
some queries will
function.
Queries may not
have been attempted
or very limited
queries.