Project Specification:

1. Part A: Entity Relationship Diagram

1. Your first task is to study the Case study requirements and gather as much knowledge to list all the main Entities and attributes.

2. Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.

Your entity relation diagram that models your database design should:

i. Include all entities, relationships (including names) and attributes.

ii. Identify primary and foreign keys.

iii. Include cardinality/ multiplicity and show using crow’s feet or UML notation.

iv. Include participation (optional / mandatory) symbols if applicable.

The ERD should be created as part of a Microsoft Word document. Hand-drawn diagrams will not be accepted. It is recommended that you complete your ERD using Draw.io, Visio or try www.gliffy.com (Search for ERD gliffy to get started).

2. Part B: Normalisation

1. Normalise all entities and relationships in optimal normal form. Provide the normalised schema and discuss the level of normalisation achieved for each relation. You goal should be normalise all entities in 3NF. Provide the reasons for any relation that is not maintained in 3NF.

3. Part C: Relational schema and MySQL database

1. Create relational schema that matches ERD and normalised schemas you created in Part A and B. The schema should include suitable data validation, integrity and referential integrity constraints, for example, types and length of attributes, primary and foreign keys etc. Make sure you follow standards, naming conventions and notation. 

2. Using MySQL, you are required to develop a demonstration prototype system (database) that stores data for AMC stores, employees, products, suppliers, patrons and orders. Create a new database called AMCDB. Create tables according to your relational schema created in step 1.

Follow a standard naming convention for table names and also field names. Avoid using spaces and any special characters in table and field names. Use underscore_case or use camelCase to separate parts of a name.

You are required to be consistent in tables’ name, fields’ name, data types, field size and key constraints with relational schema created in step 2.

3. Add at least three records into each table.

Data Link