CSC8017语言编程代做、Database程序设计代写、SQL程序代写

日期：2020-11-12 11:17

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.