COMP23111 Databases Systems
Coursework - Database Design & Implementation
Introduction
The coursework for COMP23111 is a singular piece split into two parts each with their own sub-
mission format and deadline. Please ensure you read these instructions carefully to ensure you are
confident in what needs to be submitted for each milestone.
The goal for Milestone 1 is for you to demonstrate the technique of deriving a conceptual model in
the form of an (enhanced) entity-relationship (EER) diagram from a data requirements specifica-
tion. The outcome of this step is a conceptual model from which a logical model of the data can then be
derived. Then you should normalise the data and create a relational schema ready for implementation.
Milestone 2 is all about your implementation and SQL skills. You will be creating the database
based on the designs you produced in Milestone 1, inserting some data and producing queries based
on the tasks outlined in Milestone 2. You will also be required to implement a simple front end which
your database must connect to. The ability to connect a database to a front end is a fundamental
skill needed by all database engineers. Download a suitable MySQL server for your operating system.
This will make the task easier in the long run. You may use the provided version of PhPMyAdmin
(web.cs.manchester.ac.uk), details of this can be found in Lab 04.
Contents
1 Milestone 1 - Database Design 2
2 Milestone 2 - Implementation 4
3 Material 7
4 FAQs 9
1
Chapter 1
Milestone 1 - Database Design
ERD
The first part of Milestone 1 is to analyse the Material section below. In there you will find all of the
information about our COMP23111 company “Kilburnazon”. You need to take all the information
provided and design an ERD capturing all of the information. Your ERD must be in Crow’s Foot
notation, other notations will not be accepted. Examples of suitable ERD’s can be found in Lab 02’s
Solutions.
You should draw your finished diagram neatly and clearly so that it looks professional and is easy
to read. You can do this with pen and paper, or there are many free tools available (there’s no need
to pay for a drawing tool for this). I suggest you try Lucidchart.com, draw.io or Visual Paradigm
Online. Or you can of course use any other drawing tool you like. Top tip: work out your diagram
with pen and paper first, and only draw it neatly once you’ve got it worked out. Otherwise you can
waste a lot of time. The logic should come first; make it look nice later.
Normalisation & Relational Schema
It is important that you limit the amount of data redundancy and avoid insert, update and delete
anomalies when you come to implement your database (see milestone 2 for implementation details).
Therefore, you should use the normalisation techniques that you have learnt and demonstrate your
understanding of 1NF, 2NF and 3NF by following the rules at each stage. Normalising all the infor-
mation you have captured, and your design should divide larger tables into smaller tables and link the
tables using relationships. This will strengthen your design, remove redundant data, and ensure data
is stored in the most logical way possible to help avoid insert, update and delete anomalies.
When reporting your design, you should use the following notation:
Relation_Name (Attribute_1, Attribute_2, ... Attribute N)
FK Attribute_Name → Relation_Name (Attribute_Name)
ON DELETE CONSTRAINT, ON UPDATE CONSTRAINT
The attribute(s) that form the primary key should be underlined (not the foreign keys)
The relations should be named appropriately and represent the information being stored (as
should the attributes)
2
? Any foreign keys should be identified under the relation with their associated table and attribute,
and the delete and update constraints.
Report Format
For Milestone 1 you are to submit a report following the general structure outlined below. However,
this is just for guidance, and you should structure your report as appropriate with headings and
subheadings.
Coverpage (your name, title of assessment, module code, date)
Table of Contents
ERD
– Introduction to section
– Your ERD
– A short report (Max 500 words) outlining your design choices
Normalisation
– Introduction to section
– Your relations in 3NF
– A short report (Max 500 words) outlining your design choices and at which normalisation
stage
Relational Schema
– Introduction to section
– Your Schema
– A short report (Max 500 words) outlining your design choices
Submission
Submit a single PDF to the appropriate place on Blackboard,
23111-Cwk1-S-Database Design.
Deadline:
18:00 on Friday the 11th November
Assessment Type:
This activity is subject to summative assessments regulations, therefore your submission will be
marked and you will receive the associated feedback. The marks you obtain count for up to 20% of
your overall mark for this unit.
Page 3 of 10
Chapter 2
Milestone 2 - Implementation
Database Creation
Implement your design being mindful of the data types for your fields and any referential actions for
foreign keys to update or delete data from related tables. Insert some data into your database through
a series of INSERT MySQL commands. We have provided randomly generated data in employees.csv
which you can use in your implementation, you may need to adjust column names and formats to
suit your design. For this part you will only be querying a small portion of the overall database
and therefore do not need to populate each individual table but each table must be created with the
appropriate constraints.
The Front End
Using PHP and MySQL create an application and front-end for your database. The application should:
1. Allow a user to add a new employee to the system, the details for the employee are:
emp id: 55-3623151
name: Malissia Osgardby
address: 29416 Grover Alley
salary: ￡17424.03
dob: 26/12/1989
nin: it152291r
department: Driver
emergency name: Marcie Prattington
emergency relationship: Mother
emergency phone: 07297 230 400
2. An employee has been promoted and therefore has a new salary and their emergency contact’s
phone number has also changed. Please use your system to update the details of Employee
Id: 07-4517183 so that their new salary is ￡33,027.23 and their emergency contacts new phone
number is 07231 462 728.
3. Employee 71-7374760 has left the company so please delete their record and any associated data.
4
4. Display all employees who are a Driver and their emergency contact is their Father. The output
should display the employees name, their department, the emergency relationship and the name
of their manager.
5. The CEO want to send out Birthday Cards to each employee. Create a stored procedure that
displays all employees whose birthday is in the current calendar month.
6. The CEO thinks it would be wise to keep a log of all contract terminations. Therefore, create
a trigger so that whenever an employee is deleted, a record of the employee id that left the
company, the current date, the current time and employee id of the person who deleted the
record are logged in an auditing table.
Screencast Format
For Milestone 2 you are to submit a screencast following the structure outlined below. We have
provided duration timestamps for each of the objectives that requires demonstrating, however, these
are indicative and demonstrating some objectives may take longer than others. It is important that you
follow the structure and that your screencast does not exceed 10 minutes in duration – any deviation
from the structure and time limit will result in lost marks.
00:00 - 02:00 Discuss your CREATE and INSERT statements, highlighting any significant complex-
ities in your system (i.e., tell us how you implemented the backend).
02:00 - 04:00 Provide an overview of your front end system and what the features are and how
they work.
04:00 - 05:00 Front End 1. Demonstrate adding the new employee to the system. If you have
used any client-side and/or server-side validation techniques explain these. You should also show
your updated table(s) in the database.
05:00 - 06:00 Front End 2. Demonstrate updating employee 07-4517183’s record to include the
new data. You should also show your updated table(s) in the database.
06:00 - 07:00 Front End 3. Demonstrate deleting employee 71-7374760 from the system. You
should also show your updated table(s) in the database.
07:00 - 08:00 Front End 4. Demonstrate your systems ability to display all the data required for
Front End task 4
08:00 - 09:00 Front End 5. Demonstrate the functionality of your stored procedure running in
your system. You should also show the back end functionality of this.
09:00 - 10:00 Front End 6. Demonstrate the functionality of your trigger running in your system.
You should also show the back end functionality of this.
Submission
Submit a .zip folder containing the below to the appropriate place on Blackboard,
23111-Cwk2-S-Implementation.
A backup of your database (its structure, procedures, triggers and test data)
Page 5 of 10
Your html & php (and other) files required to build your front end
your screencast which should be an .mp4
Deadline:
18:00 on Friday the 2nd December
Assessment Type:
This activity is subject to summative assessments regulations, therefore your submission will be
marked and you will receive the associated feedback. The marks you obtain count for up to 30% of
your overall mark for this unit.
Page 6 of 10
Chapter 3
Material
The information below gives the data definition and manipulation requirements for the system. This
is deliberately not written with super-precise wording. When working on real projects, initial drafts
of requirements are rarely complete and unambiguous. If you find any ambiguities, omissions, or
imprecision’s here, make a note of the issue, make your own decision about what to do, and justify
this decision in your report.
A new delivery company, “Kilburnazon” has just been established and you are the lead database
engineer tasked with designing and implementing the database.
Your task is to design the database and implement the database which models the structure of
the company. The following specification has been acquired through a requirements interview with
the companies CEO, Elon Bazos. Since the language is not precise, you may need to make some
assumptions and decisions as you go.
Kilburnazon Design Specification:
Kilburnazon’s staff are organised into departments including Management, HR, Drivers and Pack-
agers. Each of these departments has a name, number and an employee who manages it. We
should also note the numbers of employees in each department and the department’s head office
location.
Each employee in Kilburnazon has an employee number which stays with them for the dura-
tion of their employment. We must also know their name, home address, salary, date of birth and
national insurance number (NIN). Every employee is assigned to a single department and has a
manager they report to, the manager will be from the management department. Each employee
will also have an emergency contact on record, we need to know their name, relationship to the
employee and phone number.
Every company building is situated in one of the areas of the UK, each area has a unique
name. All drivers and packagers report to the warehouses in their area whereas managers and HR
staff work in a single office. Each warehouse is contained within a specific area, has a unique id,
location, size and purpose. The company has two main offices, one in Manchester and the other
in London, these are named after their location.
7
A manager may oversee the work of multiple employees from multiple departments. For drivers
we must also keep track of the number of hours a week they are working. The company owns lots
of vehicles each one has a unique ID, name and area it operates in. Routes for vehicles always
contain a starting and ending location with the possibility for multiple stops in the middle. Each
route has a unique name given to them (e.g. red-002). The time a vehicle arrives at each location
must also be. A driver will be assigned to a vehicle from their area each day and then allocated a
delivery route.
Every time a customer places an order we capture their full name, email address, postal lo-
cation and the products that have been ordered. We then receive this information in their order
forms. Each order can contain multiple products with varying quantities. Each order also has an
id and purchase date.
We must keep track of our full product list at all times. Each product has a unique id, name,
description, price and quantity left in the warehouses.
Finally, HR deal with any complaints received, these must all be logged with their unique
complaint number, date of the complaint, passenger complaints name and the reason for the
complaint. This complaint will then be allocated to a member of the HR team to deal with.
Page 8 of 10
Chapter 4
FAQs
Could I develop the applications with modern technologies like NodeJS, Flask, Java Spring, GoLang,
etc.
No frameworks are to be used. The assessment scripts are designed to test you on certain
aspects on the course and any restrictions built into them is intentional. You can, however, use
Bootstrap framework for the front-end if you wish.
Are any marks awarded for validation of data?
It is imperative that data is entered correctly into a database. Validation should be used to
reduce the number of errors during the process of data input.
What types of validation should I use?
I would suggest you look at the different validation techniques, any data captured by the user
should be validated, as a guide we would expect:
– Unique Values, a question last year was asked “if a user tries to register with the same
username as an existing user, should this be accounted for?”.
The reply: We would expect some validation here that will inform the user that the user-
name is already taken and give the user the option to try another username.
– Type validation, is the data entered the correct type? For example, if the input is a surname,
you would not expect numeric digits.
– Non-Null Values – if there are constraints of NOT NULL for any fields in the database,
then validation should ensure the user enters a value for any fields that have this constraint.
– Range Check is often used for numeric fields.
– Restricted Choice is used when the user can only enter a choice from a valid list of choices,
an obvious example might be asking the user which quiz they wish to compete. They user
only be able to enter a quiz that exists.
– Format is often the most useful validation check and has many applications. You will
have seen examples such as checking for valid postcodes, email addresses, course codes
etc. A format check is essentially checking a string matches a specific pattern and often is
implemented using regular expression.
9
Should I use client-side or server-side validation?
HTML 5 offers nice features for client-side validation (such as regular expression), anything that
HTML 5 doesn’t provide can normally be provided by JavaScript. However, you should not
assume the user is using HTML5 or JavaScript, so server-side validation can also be used for
these situations.
Milestone 2 asks for a backup of the database. Is this backup the .sql file that we get when we
export the database on phpMyAdmin?
Yes, you need to include the database file. This can be achieved in phpMyAdmin using the
export feature, or using the mysql command mysqldump.
Wanted to ask if the 3NF form and relational schema should be the same as the one which we
will use to implement the database in the application. By this I mean, if we want to change/add
something into the database for the app, that we did not deduce from the information source, should
we also change the 3NF and Relational Schema.
When you implement the database, it will be different from the relational scheme since the
information source does not capture everything.
Could you please clarify if we will be marked on the appearance of the website, or you will mark
the functionality of the website?
The user interface does not have to look amazing. We are interested in the interaction with the
database; however, why not apply a little CSS? There is no credit given for an attempt to make
the interface look better – focus your efforts on the functionality, make it look nicer if you have
time.
End of Assessment