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.

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)

• 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.

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.  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)

• 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.

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.