Student ID –. Date -. Assignment 2. Data and Informatio

Student ID –
Date -
Assignment 2
Data and Information Management
T3, 2018
Module Tutor –
Date-
1 | P a g e

Report
1 A) Database Entities
1. Coach: This entity will store information related to Coaches.
2. Customer: This entity will store information related to Customers.
3. Program: This entity will store information related to Programs.
4. CustomerProgram: This entity will store information related to customers and
enrolled programs.
5. ProgramCoach: This entity will store information related to programs and their
coaches.
6. Exercise: This entity will store information related to exercises.
7. ProgramExercise: This entity will store information related to programs and
exercises.
1 B) Tables and Attributes
Coach (CoachID, FirstName, LastName, DOB, Address, ContactNo, UserName, Password)
Customer (Cust_Number, FirstName, LastName, Phone, Address)
Program (PNum, Title, StartDate, FinishDate, Price)
CustomerProgram (Cust_Number, PNum, StartDate, EndDate)
ProgramCoach (CoachID, PNum)
Exercise (ExerciseID, ExerciseDate, Category, SubCategory, TimeSpent, CaloriesBurnt)
ProgramExercise (PNum, ExerciseID)
1 C) Business Rules
Following business rules are used in the database design-
- A coach can work for only one program.
- A program may have any number of coaches.
- A customer may enrol into one program only at a time.
- A program may contain any number of exercises.
2 | P a g e

1 D) Functional Dependency
CoachID  FirstName, LastName, DOB, Address, ContactNo, UserName, Password
Cust_Number  FirstName, LastName, Phone, Address
PNum  Title, StartDate, FinishDate, Price
CoachID  PNum
ExerciseID  ExerciseDate, Category, SubCategory, TimeSpent, CaloriesBurnt [2]
1 E) Normalise Tables
First Normal Form
The first normal form is obtained by applying following rules-
- Delete the repeated group from tables and make new tables.
- Set primary key in all newly created tables.
Second Normal Form
The second normal form is obtained by applying following rules-
- Make sure that all tables are in first normal form.
- All fields depend upon the primary key only.
Third Normal Form
The third normal form is obtained by applying following rules-
- Make sure that all tables are in second normal form.
- Delete all transitive dependencies.
Coach (CoachID, FirstName, LastName, DOB, Address, ContactNo, UserName, Password)
Customer (Cust_Number, FirstName, LastName, Phone, Address)
Program (PNum, Title, StartDate, FinishDate, Price)
CustomerProgram (Cust_Number, PNum, StartDate, EndDate)
ProgramCoach (CoachID, PNum)
3 | P a g e