CSC8017 Database Systems Coursework 2
This piece of coursework is worth 50% of the total assessment for this module. You
are expected to make use of MySQL to complete this coursework. Submit
a single answer document to NESS containing your answers to this
coursework. NESS will accept .doc/.docx and .pdf files.
Aims:
To assess your ability to:
• Construct SQL queries over a database implementation.
• Decide whether a table is in the first three normal forms or not.
Learning Outcomes:
• To be able to formulate SQL queries.
• To be able to normalise a database table.
Deadline for submission: Friday 13th November 2020, 14:30. Submit via NESS.
Problem statement:
The transport department of a city council would like to implement a database for bus
users indicating where bus routes go, how frequently they run and how to contact the
relevant bus operator.
The council has identified some data items they would like to be recorded and also
have provided some sample data (this can be found at the very end of this
assignment).
For the bus operators, the council simply want to store their name (unique) and some
basic contact details such as address, telephone and e-mail. The council are
concerned that someone might accidentally delete a current operator or route and
wish to ensure that such an operator or route cannot be easily deleted.
For bus stops, they wish to store each stop’s unique reference number (or ID) and a
description of that stop’s location, e.g. “Railway Station” . They also want to store
some information about facilities near to each of the bus stops, both a short
description and a full description.
For the bus routes, they wish to store the route number (unique), the starting and
destination bus stops and the frequency – the number of buses per hour. Note that
the inclusion of both start and end bus stops makes the relationship between stop and
route a 2-many relationship (just like a 1-many but with a bus stop participating
exactly twice). They would also like to know which bus operators work each route.
Some routes are shared between multiple operators with each operator working a
proportion of the journeys on that route. A proportion of 50 for a route and an operator
indicates that the operator operates 50% of all journeys on that route and a proportion
of 100 indicates that all journeys on that route are provided by that operator. The
council only wish to store the information described in this spec and do not wish to
store anything additional.
The council has developed an entity-relationship (E-R) diagram and some CREATE
TABLE statements have been written. However, the database has not been
implemented yet.
You may assume the above diagram is complete and will not change.
At the end of this document in the Appendices is the data set and the CREATE TABLE
statements needed to implement the above diagram.
Tasks:
i) Run all of the CREATE TABLE statements from Appendix A on MySQL to get your
database established. You MUST NOT change any of the table structures, data types
or keys. Populate your database with the data in Appendix B.
0 marks – this is preparatory work
ii) Construct the SQL for the following and show screenshots of the results for each
query. If you are unsure of how to take a screenshot, please see http://www.take-ascreenshot.org/
a) List the route number and frequency of all routes operated by Diamond
Buses. (1 mark)
b) What is the location of the bus stop with the highest stop ID? (2 marks)
c) List the name and phone number of the bus operators serving the Durham
Estate. (4 marks)
d) How many bus journeys per hour are operated by Blue Belle? Hint: Think
about how to work this out and then look back at the slides on arithmetic in
SQL queries. (4 marks)
e) What is the location (description) of the bus stops that have no facilities?
(3 marks)
f) For all bus stops which have facilities, list the stop ID and the number of
facilities that stop has. (2 marks)
g) A cycling club has members who cycle to bus stops with bike racks and
leave their bicycles in the racks. However, the club secretary wants to email
all bus operators who serve stops with bike racks to ask if folding
bikes are allowed on their buses. Provide a list ordered alphabetically of
the e-mail addresses the secretary should contact. You should make the
list as simple as possible and include no other information in your answer.
(5 marks)
h) A bus driver working for Bond Brothers needs to withdraw some cash
during their break. At which bus stops served by Bond Brothers could they
do this easily? You should give the answer as briefly as possible but
ensure that your answer includes both the ID and description of the
relevant bus stops. (5 marks)
i) How many bus stops serve as the starting point for a bus route but have
no routes which terminate there? (4 marks)
j) What is the name of the bus operator that serves more than four different
bus stops and how many stops does it serve? (These questions can be
answered in one single query) (5 marks)
35 Marks
iii) Imagine that each transport operator wishes to incorporate contact details for their
customer service agents. Each operator has one or more customer service agent. A
developer has suggested adding the attributes “Agent name”, “Agent phone number”
and “Agent Email” for each agent to the “Operator” entity.
The suggested modifications will result in unnormalised data. Explain the following:
a) How the suggested changes would break First Normal Form.
b) What changes would you make to the ensure the modifications would conform
to First Normal Form.
c) Would your changes conform to Second and Third Normal Form? Explain your
answer.
15 Marks
What to submit: A single Word or PDF document containing your answers to the
above tasks. You should submit your work electronically through NESS.
All work will be checked for plagiarism. DO NOT copy or alter other people’s
work and submit it as your own.
This is the end of the assignment but there are two appendices on the following pages
that contain the CREATE TABLE statements and the data that you need.
Appendix A
This appendix contains the CREATE TABLE statements needed to get the above
scenario working. You should hopefully be able to paste straight into MySQL or
DBeaver.
Please do NOT change the table names, column names or any of the keys. You
should also not add in any additional tables to those shown here.
These are the basic tables needed for the strong entities. Route has two foreign keys,
one each relating to the start and destination of the route – both of these refer to the
ID from Bus Stop, preventing someone from trying to have a route start at a bus stop
which doesn’t exist:
CREATE TABLE Operator (name VARCHAR(50), street VARCHAR(50),
town VARCHAR(20), postcode VARCHAR(10), email VARCHAR(30),
phone VARCHAR(15), PRIMARY KEY (name));
CREATE TABLE BusStop (ID INT, description VARCHAR(50), PRIMARY
KEY (ID));
CREATE TABLE Route (number VARCHAR(3), frequency INT, start INT,
destination INT, PRIMARY KEY (number), FOREIGN KEY (start)
REFERENCES BusStop (ID), FOREIGN KEY (destination) REFERENCES
BusStop (ID));
CREATE TABLE Facilities (facility VARCHAR(50), fullDescription
VARCHAR(250), PRIMARY KEY (facility));
These are the linking tables needed for the two many-to-many relationships.
Link table Operates needed for the relationship from operator to route:
CREATE TABLE Operates (routeNumber VARCHAR(3), operatorName
VARCHAR(50), proportion INT, PRIMARY KEY
(routeNumber,operatorName), FOREIGN KEY (routeNumber)
REFERENCES Route (number), FOREIGN KEY (operatorName)
REFERENCES Operator (name));
Link table NearTo needed for the relationship from bus stop to facilities:
CREATE TABLE NearTo (ID INT, facility VARCHAR(50), PRIMARY KEY
(ID,facility), FOREIGN KEY (ID) REFERENCES BusStop (ID), FOREIGN
KEY (facility) REFERENCES Facilities (facility));
Appendix B
(Note: this sample data is entirely fictitious!!)
Bus operators:
Venture Travel, Venture House, Consett, DH8 8SV, e-mail: info@venturetravel.co.uk, phone
01207 222 145.
OK Travel, Bondgate, Durham, DH2 2BC, e-mail: passengerservices@ok.com, phone 0191
301 3012.
Lockeys, The Garage, Durham, DH1 1AB, e-mail: contact@lockeysbuses.co.uk, phone 0191
340 1934.
Bond Brothers, Coronation Terrace, Durham, DH2 3AG, e-mail: jeff.bond@bondbuses.com,
phone 0191 333 1234.
Diamond Buses, Diamond Buildings, Newcastle, NE2 5JH, e-mail:
info@diamondbuses.co.uk, phone 0191 267 8937.
Blue Belle, Lane End Garage, Durham, DH3 8BD, e-mail bill@bluebelletravel.co.uk, phone
0191 366 9147.
Stephensons, North Road Depot, Durham, DH1 2CD, e-mail
bus@stephensonsofdurham.co.uk, phone 0191 311 4384.
Facilities:
Bike rack (Bicycle rack within 200m of the bus stop).
Cashpoint (Cashpoint within 200m of the bus stop).
Toilet (Public toilet within 400m of the bus stop).
Bus Stops:
1015: Quayside. Has toilet and bike rack.
1023: Ferry terminal. Has toilet and cashpoint.
1500: City Centre. Has toilet and cashpoint.
5061: Village green.
6700: Airport. Has toilet, cashpoint and bike rack.
7628: Shopping Centre. Has toilet, cashpoint and bike rack.
8000: Durham Estate.
9015: Railway Station. Has toilet, cashpoint and bike rack.
9016: Railway Station. Has toilet, cashpoint and bike rack.
9022: Park Gates.
Bus Routes:
Route 1: Railway Station (Stop 9015) to Park Gates, 2 per hour, operated entirely by
Venture Travel.
Route 16: Shopping Centre to City Centre, 6 per hour, operated entirely by Diamond Buses.
Route 16a: Shopping Centre to Park Gates, 2 per hour, operated entirely by Diamond
Buses.
Route 21: Ferry Terminal to Quayside, 4 per hour, operated entirely by Bond Brothers.
Route 22: Ferry Terminal to City Centre, 1 per hour, operated entirely by Bond Brothers.
Route 24: Durham Estate to City Centre, 4 per hour operated equally by Lockeys, OK
Travel, Blue Belle and Stephensons.
Route 30: Quayside to City Centre, 4 per hour, operated entirely by Bond Brothers.
Route 38: Village Green to City Centre, 1 per hour operated entirely by Stephensons.
Route 64: Railway station (Stop 9015) to Shopping Centre, 6 per hour, operated entirely by
Lockeys.
Route 66: Village Green to Shopping Centre, 1 per hour operated entirely by Blue Belle.
Route 88: Railway Station (Stop 9016) to Quayside, 2 per hour, operated entirely by Venture
Travel.
Route 100: Airport to City Centre, 4 per hour, operated entirely by OK Travel.
Route 111: Airport to Railway Station (Stop 9016), 4 per hour, operated equally by Venture
Travel and OK Travel.