2017_18
EBU5502 Database Coursework Specifications
Statement
Design and implement a relational database for The EasyDrive School of Motoring
(descriptions of the system can be found at the end of the document). Build a Java interface
to the database suitable for the database application.
Tasks
This includes setting up a database schema and providing access methods to this in the form.
of queries and views. The steps for doing this include:
Creating a conceptual schema in the form. of an ER diagram.
Deriving a relational schema from the ER diagram.
Validating the relations using normalization technique.
Implementing this schema using SQL.
Populating the database with a set of typical data. The data should be significant but
manageable.
Defining SQL queries which could be used in the Java interface.
Design and implementation of the Java interface, using JDBC to connect the database.
Teams
This coursework is to be done in groups of 3 students. You should form. the teams yourselves
via QMPlus, by Tuesday, 8 Nov 2017).
Deliverables
Submission by Thursday, 14th Dec 2017
Each group should submit a report containing (one report by one group member per group
on QMPlus):
Description of all assumptions you have made in your design (3 mark)
A conceptual schema (ER diagram) (3 mark)
The relational database schema for your database. You should also identify all primary
and foreign keys in your design. You should validate your relations using normalization,
and explain why they are in 4th Normal Form. (2 marks)
The sample test data. This test data should be carefully designed in order to test that
your queries will work under any conditions. (1 marks)
A set of SQL queries for sample queries (listed in Sample Queries), with results of the
output you obtain when you run these queries. (2 marks)
Each group should also submit a zip file of all the code for your application.
2017_18
Demonstration
A demonstration will be arranged in teaching week 4 (w/c 11th December 2017). Details of
the time and venue will be announced later in the course. Students will demonstrate to the
TA and lecturer their working system and answer questions. (4 marks)
Assessment
The total coursework counts for 15% of the final mark of the module.
Peer Assessment
Each student should submit a peer assessment form, give marks to the other two group
members on a number criteria. The form. can be found on QMplus page.
Notes
Use MySQL as the back end Database.
The coursework should be delivered in a form. so that the tables can be easily created,
data inserted and queries and views run easily by the person marking the work.
You can create your tables using individual CREATE TABLE statements, but it is
recommended that you put all of these into one script. file, so that it is easier to
reconstruct your entire schema of tables should you find data or tables have been
corrupted.
The focus of the coursework is designing and implementing database and query
functions, not GUI design. Your coursework will not be marked on the aesthetics of
the interface, no matter how pretty it is.
The report should adhere to technical report writing style, with clear English and
logical structure.
The EasyDrive School of Motoring Case Study
The EasyDrive School of Motoring was established in Glasgow in 1992. Since then, the School
has grown steadily and now has several offices in most of the main cities of Scotland. However,
the School is now so large that more and more administrative staffs are being employed to
cope with the ever-increasing amount of paperwork. Furthermore, the communication and
sharing of information between offices, even in the same city, is poor. The Director of the
School, Dave MacLeod, feels that too many mistakes are being made and that the success of
the School will be short-lived if he does not do something to remedy the situation. He knows
that a database could help in part to solve the problem and has approached you and your
team to help in creating a database system to support the running of the EasyDrive School of
Motoring. The Director has provided the following brief description of how the EasyDrive
School of Motoring operates.
Data Requirements
Each office has a Manager (who tends to also be a Senior Instructor), several Senior Instructors,
Instructors, and administrative staff. The Manager is responsible for the day-to-day running
of the office. Clients must first register at an office and this requires that they complete an
application form, which records their personal details. Before the first lesson, a client is
2017_18
requested to attend an interview with an Instructor to assess the needs of the client and to
ensure that the client holds a valid provisional driving license. A client is free to ask or a
particular Instructor or to request that an Instructor be changed at any stage throughout the
process of learning to drive. After the interview, the first lesson is booked. A client may request
individual lessons or book a block of lessons for a reduced fee. An individual lesson is for one
hour, which begins and ends at the office. A lesson is with a particular Instructor in a particular
car at a given time. Lessons can start as early as 8am and as late as 8pm. After each lesson,
the Instructor records the progress made by the client and notes the mileage used during the
lesson. The School has a pool of cars, which are adapted for the purposes of teaching. Each
Instructor is allocated to a particular car. As well as teaching, the Instructors are free to use
the cars for personal use. The cars are inspected at regular intervals for faults. Once ready, a
client applies for a driving test date.
To obtain a full driving license the client must pass both the practical and theoretical parts of
the test. It is the responsibility of the Instructor to ensure that the client is best prepared for
all parts of the test. The Instructor is not responsible for testing the client and is not in the car
during the test but should be available to drop off and pick up the client before and after the
test at the Testing Centre. If a client fails to pass, the Instructor must record the reasons for
the failure.
Query Transactions (Sample)
The Director has provided some examples of typical queries that the database system for
the EasyDrive School of Motoring must support.
(a) The names and the telephone numbers of the Managers of each office.
(b) The full address of all offices in Glasgow.
(c) The names of all female Instructors based in the Glasgow, Bearsden office.
(d) The total number of staff at each office.
(e) The total number of clients (past and present) in each city.
(f ) The timetable of appointments for a given Instructor next week.
(g) The details of interviews conducted by a given Instructor.
(h) The total number of female and male clients (past and present) in the Glasgow, Bearsden
office.
(i) The numbers and name of staff who are Instructors and over 55 years old.
( j) The registration number of cars that have had no faults found.
(k) The registration number of the cars used by Instructors at the Glasgow, Bearsden office.
(l) The names of clients who passed the driving test in January 2000.
(m) The names of clients who have sat the driving test more than three times and have still
not passed.
(n) The average number of miles driven during a one hour lesson.
(o) The number of administrative staff located at each office.