As your continuous assessment for this module, you are required to design and develop a database using MySQL.
There are two project deliverables:
This document serves as the design and implementation plan for the database. It is at this stage that the database should be modelled and associated documentation should be developed. This report should have the following structure:
Title Page: This should contain the name of the report, student number and student name, date and module.
Table of Contents: This should include major sections and page numbers.
Business Description: The business description should include a description of the business, along with its major functions. The benefits of implementing a database for the chosen business should be highlighted.
Enhanced ER diagram: The data model should be sufficiently complex, including different types of relationships/associations (e.g. 1:1, 1:M, M:M and recursive relationships, used where relevant) and at least one example of a supertype/subtype relationship.
Normalised Tables (3NF sufficient): You should list the tables that will be created from the EER diagram above. Remember to include all the appropriate foreign keys and tables created by association entities.
Table Design: This is a detailed description of each table. It should incorporate the data shown in the sample below. This section should also identify the relevant integrity constraints and keys required for database table. It would be advisable to indicate where the use of indexes would be appropriate. You should justify your choice of index. Remember to fill in all columns for all fields.
Table name: Student
Field | Type | Size | Null/Not Null | Default | Constraints | Index | Description |
---|---|---|---|---|---|---|---|
StdNo | char | 8 | Not Null | Unique | PK | Unique student number | |
Name | char | 20 | Surname followed by first name | ||||
Address | char | 100 | College address | ||||
Mature | char | 1 | N | Y or N | Yes if student is greater than 24 years old in year 1. |
The design document should be submitted in an electronic format through Moodle by Friday 30th August.
In this phase all previous documentation will be used in order to implement the actual database in the MySQL environment. The student is expected to submit two SQL scripts:
You will also submit a brief report which documents:
The two SQL scripts and the report should be submitted in an electronic format through Moodle by Friday 30th August.