Continuous Assessment

As your continuous assessment for this module, you are required to design and develop a database using MySQL.

There are two project deliverables:

  1. Design Document (25%): due Friday 30th August
  2. MySQL Implementation (25%): due Friday 30th August

Design Document (25%)

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:

  1. Title Page: This should contain the name of the report, student number and student name, date and module.

  2. Table of Contents: This should include major sections and page numbers.

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

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

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

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

Sample table specification

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.

Submitting your assignment

The design document should be submitted in an electronic format through Moodle by Friday 30th August.

SQL Implementation (25%)

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:

  • One that creates the database, users and tables, grants appropriate privileges to the users and populates the tables with records; basically everything required to recreate the entire database on another computer.
  • Another script that contains the list of frequently used queries for the database.

You will also submit a brief report which documents:

  • The process you took to implement the database, any problems you encountered and how you solved them
  • An explanation of the frequently used queries
  • An explanation of the privileges granted to users

Submitting your assignment

The two SQL scripts and the report should be submitted in an electronic format through Moodle by Friday 30th August.