Database Design and SQL Queries for Mindful Body Fitness

Desklib provides solved assignments and past papers to help students succeed.

Data and Information Management

Contents
Introduction:..........................................................................................................................................3
1. Information Requirements............................................................................................................4
a. Identify all entities.....................................................................................................................4
b. User views as tables...................................................................................................................4
c. Relationships.............................................................................................................................6
d. Functional Dependences...........................................................................................................8
e. Normalise tables:.......................................................................................................................9
2. ER Diagram..................................................................................................................................11
3. Model Building.............................................................................................................................12
4. Report Generation.......................................................................................................................16
Conclusion:..........................................................................................................................................18
References:..........................................................................................................................................18
List of images:
Figure 1: Coaches tables........................................................................................................................5
Figure 2; Customer table.......................................................................................................................5
Figure 3: Exercise Log table...................................................................................................................5
Figure 4: Program Table........................................................................................................................6
Figure 5: ERD.........................................................................................................................................7
Figure 6: ER-Diagram.............................................................................................................................7
Figure 7: ERD.......................................................................................................................................11
Figure 8: Entity-relationship Diagram..................................................................................................12
Figure 9: Datasheet view of the coaches table....................................................................................12
Figure 10: Design view of the coaches tabel........................................................................................13
Figure 11: Datasheet view of the Customers table..............................................................................14
Figure 12: Design view of the customers table....................................................................................14
Figure 13: Datasheet view of the exercise log table............................................................................15
Figure 14: Design view of the Exercise log table..................................................................................15
Figure 15: Datasheet view of the Programs table................................................................................16
Figure 16: Design view of the program table.......................................................................................16
Figure 17: Coach SQL Query................................................................................................................17
Figure 18: Output of Coach SQL Query................................................................................................17
Figure 19: Customer SQL Query...........................................................................................................17
Figure 20: Output of Customer Query.................................................................................................17
Figure 21: Exercise Logs SQL Query.....................................................................................................17
Figure 22: Output of Exercise Logs Query............................................................................................17
Figure 23: Program SQL Query............................................................................................................18
Figure 24: Output of Program Query...................................................................................................18

Introduction:
Data management is a sub-category of information management that used to manage different
kinds of data. The goal of the current assignment is to develop data models with database system on
MS access for the selected organization. Select the company name as, Mindful Body Fitness, to
create a database on MS access with creating different entities and their attributes. Identify different
business rules in the assignment with functional dependencies and normalization in different forms.
Represent the structure of the MS access database with different SQL queries and relationship
diagram.

1. Information Requirements
Gather the information from different sources to fulfil the user requirement including entities and all
the attributes. Provide the images of tables view of the MS access database with the relationship
between different entities of the database.
a. Identify all entities
Entities in the database management system are known as the group of similar entities that have a
group of attributes in the database tables. These attributes define as primary key and foreign key in
the database. Each entity has one primary key and can have a different foreign key. The entities of
the database are:
 Exercise logs
 Programs
 Coaches
 Customers
Fundamental principles off the networking are used to make strong a network. Internet protocol is
helpful for the implementation of a network. Network design is important to measure the
performance of the company as well to provide security. Here are some basic characteristics or
principles that required in the good network as:
 Performance: observe the performance of a network at each step by using different
techniques as well as with the user’s response.
 Reliability: a network should be reliable for the support of the applications with the zero-
downtime for applications of critical applications.
 Scalability: the network design should be scalable that is helpful in the growth of the
network and it increases the bandwidth of the network.
 Running costs: the running cost of the network should be low as there are different
technologies for the tracking of the applications [1].
b. User views as tables
Represent all the entities in the form of tables that includes different attributes with their define
datatype. It each required to define the datatype of each attribute and each entity has one primary
key as:

Figure 1: Coaches tables
Provide the coaches table in the above image with defining different attributes in which Coa_ID is
the primary key as represent in the image.
Figure 2; Customer table
Provide the above image of a customer table with defining different attributes and primary key as
Cust_ID.
Figure 3: Exercise Log table

The exercise log table includes the primary key name as, Exe_ID with different attributes of the
entity.
Figure 4: Program Table
The program table used to represent the entity with different attributes of the tables and the
primary key name as P_Num.
c. Relationships
ERD (entity – relationship diagram) is used to represent the structural diagram of the database. ERD
used different symbols to represent the information like, rectangle for the entity, attributes define
with the ovals and relationships or actions define with diamond shapes. The relationship has
different types,
 One to one
 One to many
 Many to many
 Many to one
The concept of the primary key and foreign key defines as the combination of attributes as:
 Primary key: it is the unique key and defines as one key in one attribute.
 Foreign key: it is the associated key that used to an as unique key in one entity key and as a
foreign key in the different attribute.

Figure 5: ERD
The above diagram used to present the relationship between different entities of that database
created on MS Access.
Figure 6: ER-Diagram
Create the relationship diagram using different entities and attributes as the above image. Use the
tool ERDPlus to create the diagram and represent different terms with different symbols.

d. Functional Dependences
Define the relationship between two different attributes with using their primary key and foreign key
of a table is known as functional dependencies [2]. Select a table from the current database and
define the functional dependences on it as:
Coach table:
Coa_ID First Name
Coa ID Last Name
Coa ID DOB
Coa ID Address
Coa ID ContactNo.
Coa ID UserName
Coa ID Password
Coa ID P-Num

e. Normalise tables:
Procedure to organise the data into the database is known as normalization. It used to reduce the
redundancy from the different set of databases and eliminate the unwanted characteristic like
update and insertion. This process used to divide the larger table into a small table. the
normalisation has three different types as, 1NF, 2NF and 3NF [3].
1NF:
Coaches Exercise logs
Coa_ID Exe_ID
First Name Exe_Date
Last Name Exe_Category
DOB Exe_sub-cat
Address Time Spent
Contacto Calories Burnt
UserName Cust_Id
Password Programs
P_Num P_Num
Customers P_title
Cust_ID S_Date
Cust_Number F_Date
First Name Price
Last Name Cust_Id
Address Coach_Id
P_Num
P_Title
Payment
Coa_ID
2NF:
Coaches Exercise logs
Coa_ID Exe_ID
First Name Exe_Date
Last Name Exe_Category

DOB Exe_sub-cat
Address Time Spent
ContactNo Calories Burnt
UserName Cust_Id
Password Programs
P_Num P_Num
Customers P_title
Cust_ID S_Date
Cust_Number F_Date
First Name Price
Last Name Cust_Id
Address Coach_Id
P_Num
P_Title
Payment
Coa_ID
3NF:
Coaches Exercise logs
Coa_ID Exe_ID
First Name Exe_Date
Last Name Exe_Category
DOB Exe_sub-cat
Address Time Spent
ContactNo Calories Burnt
UserName Cust_Id
Password Programs
P_Num P_Num
Customers P_title
Cust_ID S_Date
Cust_Number F_Date
First Name Price
Last Name Cust_Id

Address Coach_Id
P_Num
P_Title
Payment
Coa_ID
2. ER Diagram
Create the ERD by using different entities and attributes as below:
Figure 7: ERD