Assignment

MITS4003 Assignment 3
MITS4003
Database Systems
Assignment 3
Case Study
March 2020
MITS5507 Assignment 3
Copyright © 2015-2018 VIT, All Rights Reserved. 2
Case Study (Group Assignment) – 30% (Due Week 12)
Objectives(s)
This assessment item relates to the unit learning outcomes as in the unit descriptor. This
assessment is designed to improve students’ skills to analyze organizational database
requirements, develop a data model to reflect the organization’s business rules and use data
manipulation language to manage database. This assessment covers the following LOs.

LO1. Synthesize user requirements/inputs and analyse the matching data processing needs,
demonstrating adaptability to changing circumstances.
Develop an enterprise data model that reflects the organization’s fundamental business
rules; refine the conceptual data model, including all entities, relationships, attributes,
and business rules.
Derive a physical design from the logical design taking into account application, hardware,
LO2.
LO3.

operating system, and data communications networks requirements. further use of data
manipulation language to query, update, and manage a database

LO4. Identify functional dependencies, referential integrity, data integrity and security
requirements; Further integrate and merge physical design by applying normalization
techniques.
Design and build a database system using the knowledge acquired in the unit as well as
LO5.

through further research on recent trends to demonstrate competence in various
advanced tasks with regard to modelling, designing, and implementing a DBMS including
Data warehousing, Data Management, DB Security.
Note: This is a group assignment and each group can have maximum 4 students. Students need to inform
lecturer about their groups by
week 5
Assignment Requirements and Deliverables:
Part A (Designing and Modelling) – 10%
Part A requires you to analyze the data requirements for the business model of Prestige
Automobile Rental
(Scenario contained within this document) and design a reasonable
database to meet their data requirements. In this section you need to perform the following
tasks
Develop an Entity Relationship Diagram
Make a Relational Schema (including Primary, Foreign Keys and all attributes).
Provide Supplementary Design requirements (data attribute information)
MITS5507 Assignment 3
Copyright © 2015-2018 VIT, All Rights Reserved. 3
Discuss physical design and any assumptions made during design
Part B – 10%
Part B is a SQL implementation of your design developed in Part A. Your code must work on
MySQL database and have the following features
SQL code required:
CREATE TABLE statements for all tables in your ERD including integrity constraints.
INSERT INTO statements for populating the database
o You must at least enter 5 customer details.
o You must at least put 10 vehicle details
o Create at least three rentals with different customers
o Data may need to be inserted in a particular order to comply with integrity
constraints.
SELECT statement that will return all vehicle that have not been rented
o The details to be included (Vehicle ID, type, registration number,
year/make/model, vin number)
SELECT statement that will produce the total amount paid per customer
o The details to be included (customerID, customer name (first and last
combined), phone number and total amount
CREATE FUNCTION that will calculate the age of customers
CREATE TRIGGER statements:
o Update the number of vehicle when a rental is taken out (i.e. decrease by 1)
o Update the number of vehicle when a rental is returned (i.e. increase by 1)
o Add demerit point for every day the rental was overdue to the customer’s
record (Can be calculated when renal is returned)
Part C (Research and Discussion) – 10%
Part C requires you to do research on the following topics.
Discuss what is normalization including the normal form of each entity in your design
and why that is optimal. Also discuss how normalization was achieved for each entity.
Your entities must be in 3NF unless there is a compelling reason provided to keep a
relation in 2NF.
Discuss the database security and control measures required in the above case study.
Also discuss the commands used by SQL server to enforce those security controls.
Elaborate the data integrity problems you encounter during concurrent user accesses
and technique of sharing and recovery.

MITS5507 Assignment 3
Copyright © 2015-2018 VIT, All Rights Reserved. 4
Submission Guidelines
All submissions are to be submitted through turn-it-in. Drop-boxes linked to turn-it-in will be set
up in the Unit of Study Moodle account. Assignments not submitted through these drop-boxes
will not be considered.
Submissions must be made by the due date and time (which will be in the session detailed above)
and determined by your Unit coordinator. Submissions made after the due date and time will be
penalized at the rate of 10% per day (including weekend days).
The turn-it-in similarity score will be used in determining the level if any of plagiarism.
Turn-it-in
will check conference websites, Journal articles, the Web and your own class member
submissions for plagiarism
. You can see your turn-it-in similarity score when you submit your
assignment to the appropriate drop-box. If this is a concern you will have a chance to change
your assignment and re-submit.
However, re-submission is only allowed prior to the submission
due date and time
. After the due date and time have elapsed you cannot make re-submissions
and you will have to live with the similarity score as there will be no chance for changing. Thus,
plan early and submit early to take advantage of this feature. You can make multiple submissions,
but please remember we only see the last submission, and the date and time you submitted will
be taken from that submission.
For this assignment you need to submit following three files
Part A- Submit a word file including you assumptions and constraints, ER Diagram,
relational schema and discussion about attributes and physical design.
Part B- Submit all your code in .sql file. Before submitting make sure all the
commands works in SQL.
Part C- Submit a word file answering the question of Part C.
Important: It is very important to put all these files in a one folder and zip the folder. Please
name the zip folder as “Assignment3_GroupX_YY.zip” (where
X is your group number and YY
are student IDs).
MITS5507 Assignment 3
Copyright © 2015-2018 VIT, All Rights Reserved. 5
Case Study:
Prestige Automobile Rental (PAR) is a vehicle rental company that rents old vehicles
to the public. PAR has been using manual methods for keeping track of their
customers and their rentals. However, the company would now like to go online and
allow customers to search the available vehicles and see their rental history.
For the first time, when the customer rents a vehicle from PAR their details (name,
address, phone number, driving license number and credit card number) are
recorded. The date they become a customer is also stored. There are certain types of
vehicles which are classified as heavy vehicles. It is a policy of PAR that these vehicles
are only rented to customers who have no demerit points on their driving license.
The information about the vehicle (type, registration number, year/make/model, vin
number, distance travelled and current condition) are also stored. Each vehicle has a
unique ID. The customer can search for their desired vehicle and can see if it is
available.
All rentals are for 7 days. Rental charges are based on the type of vehicle (Car (C) or
Heavy Duty (HD)). Rental must be paid for on collection. Customers can rent up to 2
vehicles at a time. Each rentalID is for a single vehicle for one customer. (The rentalID
is an auto number). When the rental is taken out the date of checkout is recorded
along with calculated due date (7 days from checkout date). When the rental is
returned, the date is recorded in the returned date.
PAR would like to store ‘demerit’ points for the renter in order to identify bad renters.
These demerit points are accumulated at a rate of one point per day a rental is
overdue. PAR will cancel the membership of the customers who have too many
demerit points.

MITS5507 Assignment 3
Copyright © 2015-2018 VIT, All Rights Reserved. 6
THIS PAGE INTENTIONALLY LEFT BLANK
MITS4003 Assignment 3
Group Participation Form. (Database Systems Group Assignment)
This form is to be completed by the group and returned to your tutor/lecturer as soon as possible.
We, the undersigned, agree to contribute individually and as a team to complete the Group Assignment for MITS4003 Database Systems in the
time specified. (It should be noted that failure to participate in a group may result in a fail for the assignment component of the subject.)
Group membership:
Surname First name Student ID Date Signature
1. ______________________ ___________________ __________ ___/___/___ _______________________
2. ______________________ ___________________ __________ ___/___/___ _______________________
3. ______________________ ___________________ __________ ___/___/____ _______________________
4. ______________________ ___________________ __________ ___/___/____ _______________________
* All members in the team will receive the same mark for an assignment, unless there are extenuating circumstances whereby an individual’s
mark has to be altered by the tutor/lecturer, or if the peer group assessment warrants it.
** Team members should contact their tutor/lecturer immediately if problems arise within the team that may cause completion of an assignment
to be severely delayed, or the quality of the submission to be substantially lowered.
*** No additions or deletions from Teams allowed unless agreed to by your Instructor