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:

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.