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

To develop a data models , map and implement a database system in a standard development environment to gain understanding of data models development and implementation.

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

Exercise (ExerciseID, ExerciseDate, Category, SubCategory, TimeSpent, CaloriesBurnt)
ProgramExercise (PNum, ExerciseID)
1 F) ER Diagram
[1]
2) MS-Access Database
4 | P a g e

Coach Table
Customer Table
Program Table
CustomerProgram Table
ProgramCoach Table
5 | P a g e

Exercise Table
ProgramExercise Table
3) SQL Queries
Query 1
SELECT Coach.CoachID, Coach.FirstName & ' ' & Coach.LastName as CoachName,
ProgramCoach.PNum, Program.Title as ProjectTitle FROM Program INNER JOIN (Coach
INNER JOIN ProgramCoach ON Coach.CoachID = ProgramCoach.CoachID) ON
Program.PNum = ProgramCoach.PNum;;
6 | P a g e

Query 2
SELECT Customer.Cust_Number, Customer.FirstName & ' ' & Customer.LastName as
CustomerName, CustomerProgram.PNum, CustomerProgram.StartDate,
CustomerProgram.EndDate FROM Customer INNER JOIN CustomerProgram ON
Customer.Cust_Number = CustomerProgram.Cust_Number;
Query 3
SELECT Program.PNum, Program.Title, Exercise.ExerciseID, Exercise.Category,
Exercise.SubCategory FROM Exercise INNER JOIN (Program INNER JOIN
ProgramExercise ON Program.PNum = ProgramExercise.PNum) ON Exercise.ExerciseID =
ProgramExercise.ExerciseID;
7 | P a g e

References
[1] Oracle. (2000). Drawing the Entity Relationship Diagram [online].
http://docs.oracle.com/cd/A87860_01/doc/java.817/a81358/05_dev1.htm /
[2] Janalta Interactive Inc. (2015). Functional Dependency [online].
http://www.techopedia.com/definition/19504/functional-dependency
8 | P a g e