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.
|
INVOICE
|
|
ATLAS ANIMAL HOSPITAL DATE: JAN 13/20017
|
|
INVOICE NUMBER: 001
|
|
PET OWNER'S NAME: MOHAMMED ALI
|
|
ADDRESS: 33 AL KHUWAIR
|
|
WAY NO.3389
|
|
CONTACT NUMBER: 57586868
|
|
|
|
PET NAME
|
BREED
|
HEIGHT
|
WEIGHT
|
AGE
|
PROCEDURE
|
AMOUNT
|
|
ROVER
|
GERMAN SHEPHERD
|
3 feet
|
27
|
10
|
RABIES VACCINATION
|
30
|
|
MORRIS
|
BULL DOG
|
2 feet
|
25
|
4
|
RABIES VACCINATION
|
24
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
TOTAL
|
54
|
|
|
|
|
|
|
TAX (8%)
|
4.32
|
|
|
|
|
|
AMOUNT OWING
|
58.32
|
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.