Coursework

STAGE 1 – Relational Database

Here is the Final EAR Diagram expected:

UNIVERSITY

Course
Course: VARCHAR
Course Name: CHAR
Module: VARCHAR
Student
URI: VARCHAR
Student Number: INTEGER
First Name: CHAR
Last Name: CHAR
Course: VARCHAR
Year of Entry: INTEGER
Module
Module: VARCHAR
Module Name: CHAR

Contact Details
URI: VARCHAR
Telephone Number: INTEGER
Email Address: CHAR
Street Number: INTEGER
Street Name: CHAR
City: CHAR
County: CHAR
Postcode: VARCHAR

Step 1
Design a relational database using to model students with the EAR diagram * Student
URI: VARCHAR
Student Number: INTEGER
First Name: CHAR
Last Name: CHAR
Course: VARCHAR
Year of Entry: INTEGER
EAR Diagram expected for this step:
Module
Module: VARCHAR
Module Name: CHAR
Course
Course: VARCHAR
Course Name: CHAR
Module: VARCHAR

* I will explain the transformation of this EAR Diagram into relations :

1NF: All attributes values must be atomic and single. The columns must not be repeated.

The attribute Student Name is not atomic. So I have to replace Student Name by First Name and Last Name.

I get this table:

Student URI | Student Number | First Name | Last Name | Course | Year of Entry |

I choose URI as the primary key because URI is used to identify the person.

* To get the course details, we have to design a table Course Details and use the course attribute as a foreign key.

Course Details Course | Course Name | Module |

Course is the primary key of this table and a foreign key from Student

* A module is related to his name.

Module Details Module | Module Name |

Module is the primary key and a foreign key from Course Details

* We can deduce this referential Integrity Diagram :

Student

URI | Student Number | First Name | Last Name | Course | Year of Entry |

Course | Course Name | Module | Course Details

Module | Module Name | Module Details