T1 2019 MN405: Data Model Development and Implementation Report

This report presents a comprehensive analysis of data modeling and database implementation, focusing on the development of a database system. The assignment includes the creation of database entities (Coach, Customer, Program, etc.) and their corresponding attributes, alongside the specification of business rules governing the relationships between these entities. The report details the tables and attributes, functional dependencies, and normalization processes (1NF, 2NF, and 3NF) applied to ensure data integrity and efficiency. An ER diagram visually represents the database structure. Furthermore, the report includes the implementation of the database in MS-Access and provides SQL queries designed to retrieve and manipulate data from the created tables. References to relevant resources are also provided.

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