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.