Description

Maximum mark: 50

This coursework is worth 50% of the total assessment for this module.

Aims:

To assess your ability to:

• Design a relational database, expressing that design in an entity-relationship diagram.
• Implement the design in MySQL.

Learning Outcomes:

• Design a database from a problem statement.
• Implement a database designed with an E-R diagram.

All work will be checked for plagiarism. DO NOT copy or alter other people’s work and submit it as your own.

For further information, please see: https://www.ncl.ac.uk/academic-skills-kit/goodacademic-practice/plagiarism/

Problem Statement:

Some sites may appear on multiple themed tours, e.g. a building that a famous scientist and a famous musician lived in may appear on the science themed tours and the music themed tours. The main information required about each site is its postcode (which you can assume is unique), name and a background/overview to the site. Each tour uses one bus provided by one of several local bus companies. You don’t need to store information about the buses or drivers, but bus company names and phone numbers are required. Each tour can have a number of customers and for simplicity you can assume there will always be enough capacity on a tour. Basic contact details are required for all customers. There are no limits to the number of tours a customer can travel on. If a customer is buying more than one ticket for a tour, you only need to store the details of the customer who is making the booking. Finally, there are two other groups of people you need to store information about. Each tour is led by one guide (though a guide might do lots of tours over the season) and you need to store their employee ID, name and phone number. You also need to store the names, job titles and phone numbers of senior staff at each of the sites. There may be multiple senior staff members at each site, but you can assume each staff member works at only one venue.

Tasks:

1) Draw an entity-relationship diagram for the above scenario. You must explain any assumptions that you make during the design process and use the same design of diagram (UML) as the lecture notes. (40 marks).

2) Implement your design in MySQL. Populate your database tables with data of your choice (this does not need to be a huge amount of data, maybe 5-10 rows for each base table). If you make any changes to your original design from Task 1, these must be explained. Include the following TWO items:

i) SQL statements you used to create your tables in your answer document (plus a description of any changes made from Task 1). This MUST be pasted into your answer document and MUST NOT be a screenshot. Screenshots of SQL will score 0/10.
ii) A screenshot of each table’s contents (i.e. the result of doing SELECT * from each table). Make it clear in your answer document which table each screenshot refers to. If you are unsure of how to take a screenshot, please see http://www.take-a-screenshot.org/ Please note that you do not need to show any INSERT statements you did for adding data.

(10 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.