Relational Database Systems (COMP 1005)

Objective: This is an individual assignment aimed to give the student exposure in understanding, designing, building and analyzing database systems for a given real-time based scenario and be able to conduct optimal strategies for efficient management of databases.

Intended Learning Outcomes covered:

1. Design the logical structure of a database using Entity-Relationship diagram.

2. Apply normalization techniques to reduce redundancy in a database.

Task 1:

- General overview of initial understanding of solutions to task 2 and task 3

- What you will do with the given tasks: task 2, task 3 and the dates (timeline) by when they will be completed

Task 2: Scenario:

- Identification of Literature Resources

The company in the given scenario is organized into departments. Each of the department has an identification number and an employee who manages the department. When the

employee is being assigned as the manager, the department wants to keep a record of the date on which the manager takes charge of the department.

A department may be situated at different locations. The department also supports a number of projects. A Project has a unique number, title, and a single location.

The company also records the details of all the employees. The details include the employee identity, name, address, salary, date of birth and gender. An employee is assigned to only one department. However, the employee may work for several projects. When the employee is being assigned to the project the company wants to keep a record of the hours per week an employee works on each project. Each employee will also have a supervisor (also an employee) for each project.

An employee is also having a health insurance for all the dependents of the family. For this a record of all the dependents needs to be maintained. The details of the dependents include dependent name, birth date, relationship to the employee, gender.

a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, attributes of each entity including primary key, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design.

b) Analyze the above given scenario and discuss about the possible structure of the relations (the number and name of the columns) and the referential integrity constraints to be implemented in the above design.

Task 3:

a) Normalize the below given Invoice to First Normal Form, Second Normal Form and Third Normal Form.

b) Discuss how the data consistency can be maintained during normalization. Support your answer with reference to the above Question.

Task 4:

Be ready for a written viva to demonstrate your knowledge with the different concepts used in preparing the assignment. Schedule for the written viva will be announced in the class and on Moodle/MEC mail. Marks for task 2 and task 3 will depend on the satisfactory pass for the demonstration in task 4.