CSC8017 Database Systems Coursework 1
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.
Deadline for submission: INSERT DATE October, 14:30 on NESS
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/good- academic-practice/plagiarism/
Problem Statement:
You work as a database developer for a city council. The Environmental Services department wants you to help them construct a database for their Trade Waste Collection Scheme. Customers (who are businesses in the city) sign up for the scheme, receive the appropriate bins they request and when a bin needs emptying, they call the department to book a collection. Customers pay once for the bins and then a charge per collection.
For simplicity, you can assume each customer is unique (so two branches of the same restaurant chain would count as two separate customers). You can also assume multiple customers will never share a bin and that all charges are in whole pounds.
Each customer has a unique ID, a name, a contact e-mail and the address their bins are located at. It is also important to store their account balance (i.e. how much they currently owe the council). All bins have a unique ID number, a max weight of rubbish it can accommodate, a date of last maintenance and a category. The categories are important to prevent rubbish becoming mixed up. Examples of categories include glass, plastic, general waste and paper.
A collection is planned to empty a number of different bins in a particular order (simply a number starting at 1 for the first bin emptied). How this order is calculated is not important. What is important is to store a unique reference for each collection, a date and a start time. A collection may require multiple different staff members to move and empty the bins into a lorry. However, each collection only involves a single lorry. The only information needed about staff members is their staff ID number, name and mobile number, whereas the only information about lorries needed is the unique registration number, manufacturer and max load capacity.
Each collection is then delivered to one of three local disposal points (a tip and two recycling centres). The department wishes you to store the name and address of these disposal points, as well as a phone number. For simplicity, you may assume each of the three disposal points has a different name.
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. (45 marks).
Important Note: DBeaver can draw E-R diagrams, but only of implemented solutions. A design diagram needs to be drawn before you implement the solution (in Task 2), so
a DBeaver-generated diagram will score 0 marks for Task 1.
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/5.
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 seehttp://www.take-a-screenshot.org/ . Please note that you do not need to show any INSERT statements you did for adding data.
(5 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.