ITECH 1006 - Database Management Systems Project for [University Name]

This document presents a comprehensive database management systems project. It begins with an ER diagram illustrating the relationships within the TopFit gym database. The project then delves into normalization, explaining how to reduce data redundancy and dependencies by breaking down large relations into smaller ones, up to the 3rd normal form. The document includes figures to visualize the decomposition process. A detailed data dictionary follows, providing a tabular format of relations, primary and foreign keys, and field names. The database schema is defined, outlining each attribute's purpose and data type for each entity. The document concludes with a bibliography of relevant sources used in the project.

Running Head: ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
ITECH 1006 - Database Management Systems
[Name of the student]
[Name of the university]

1ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Table of Contents
ER Diagram 2
Normalization 2
Data dictionary 5
Database schema 7
Bibliography 11

2ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
ER Diagram
Figure 1: Entity Relationship Diagram of TopFit
(Source: Created by author)
Normalization
The normalization is a concept of reducing redundancy as well as dependency of data to
organized a database design. In other word, it can be define as splitting large relation into smaller
relation and link using their functional dependency.
However, here some entities are in 1st normal form and also have single column of
primary key. According to the TopFit gym background study the above entity relationship
diagram is created, where all tables are decomposed up to 3rd normal form.

3ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Figure 2: Decomposing relations to eliminate transitive dependency
(Source: Created by author)
At first a member is associated with the class and staffs, therefore if member, class and
staffs are representing in one relation. This relation can be defining as 1st normal form, but when
it separated in three different relations as well as all of them contains one primary key than it
could be a 2nd normal form. After that evaluating the functional dependency among these
relations, if any transitive dependency found than that relation is decomposed in 3rd normal form.

4ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Figure 3: Decomposing relations to eliminate transitive dependency
(Source: Created by author)
Figure 4: Decomposing relations to eliminate transitive dependency
(Source: Created by author)

5ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Data dictionary
The list of relations is given underneath in tabular format, where primary key is defining by
underline and foreign key is represented in italics front style.
Name of Table payments
Primary / Foreign Key Field Name
Primary key paymentID
bank_name
account_type
account_number
account_Name
Foreign Key Members_ID
Name of Table staff
Primary / Foreign Key Field Name
Primary key ID
name
email
phone_number
weekly_payment_rate
tax_number
address
Name of Table Payment_statement
Primary / Foreign Key Field Name
Primary key ID
PaidDate
AmountPaid
Foreign Key PaymentID
Foreign Key StaffID
Foreign Key MEMBERID
Name of Table awards

6ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Primary / Foreign Key Field Name
Primary key AWARDID
name
description
date_awarded
note
award_ type
Foreign Key Member_ID
Name of Table Running_Course
Primary / Foreign Key Field Name
Primary key CODE
End_Date
Starting_Date
NOTES
Foreign Key COURSEID
Foreign Key StaffID
Name of Table Members
Primary / Foreign Key Field Name
Primary key Member_ID
NAME
address
email
phone_number
DOB
Notes
DOJ
DOL
Name of Table Course

7ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Primary / Foreign Key Field Name
Primary key CourseID
name
number_of_classes
course_description
maximum_student
prerequisite_courseID
Name of Table MembersOFclass
Primary / Foreign Key FIELD NAME
Primary key ID
Foreign Key Member_ID
Foreign Key classID
Name of Table classes
Primary / Foreign Key FIELD NAME
Primary key classID
Foreign Key CourseID
DateTime
RoomNumber
Notes
Database schema
The database schema is defining for showing each of attributes purpose in an entity as
well as their type also mentioned, which define that this field can accept this type of data as an
input. The database schema is as follows-
Name of Table payments
Primary / Foreign Key Field Name Data
Type Purpose
Primary key paymentID number Unique identification number
bank_name varchar Name of the bank
account_type varchar Type of the account

8ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
account_number number Number of the account
account_Name varchar Name of the account
Foreign Key Members_ID number
Member identification
number
Name of Table staff
Primary / Foreign
Key Field Name Data
Type Purpose
Primary key ID number
Unique indentification
number
name varchar name of the staff
email varchar email of the staff
phone_number number phone number of the staff
weekly_payment_rat
e double
weekly payment rate of the
staff
tax_number number tax number of the staff
address varchar address of the staff
Name of Table Payment_statemen
t
Primary / Foreign
Key Field Name Data
Type Purpose
Primary key ID number
Unique identification number of payment
statement
PaidDate date date of payment
AmountPaid double total amount paid
Foreign Key PaymentID number Unique identification number of payment
Foreign Key StaffID number Unique identification number of staff
Foreign Key MEMBERID number Unique identification number of member
Name of Table awards
Primary / Foreign
Key Field Name Data
Type Purpose
Primary key AWARDID number unique identification number for awards
name varchar name of the awards
description varchar description of the awards

9ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
date_awarded date date awarded of the member
note varchar note of the awards
award_ type varchar award type of the awards
Foreign Key Member_ID number unique ID of the member
Name of Table Running_Course
Primary / Foreign
Key Field Name Data
Type Purpose
Primary key CODE number unique ID for running course
End_Date date end date of course
Starting_Date date start date of course
NOTES varchar special notes
Foreign Key COURSEID number unique ID for existing course
Foreign Key StaffID number unique ID of the staff
Name of Table Members
Primary / Foreign
Key Field Name Data
Type Purpose
Primary key Member_ID Number Unique identification number
NAME varchar name of the member
address varchar address of the member
email varchar email of the member
phone_number Number phone number of the member
DOB date DOB of the member
Notes varchar Notes of the member
DOJ date DOJ of the member
DOL date DOL of the member
Name of Table Course
Primary / Foreign
Key Field Name Data
Type Purpose
Primary key CourseID number unique Identification number of course
name varchar name of the course
number_of_classes number Total number of classes.

10ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
course_description varchar details of course.
maximum_student number maximum number of student
prerequisite_courseI
D number unique Identification number of another course
Name of Table MembersOFclass
Primary / Foreign
Key FIELD NAME Data
Type Purpose
Primary key ID number unique identification number of course.
Foreign Key Member_ID number Identification number of member
Foreign Key classID number Identification number of member
Name of Table classes
Primary / Foreign
Key FIELD NAME Data
Type Purpose
Primary key classID number unique identification number of class.
Foreign Key CourseID number Identification number of course.
DateTime date date and time of class
RoomNumber number room identification number
Notes varchar important note for class

11ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Bibliography
Arasu, A., Babcock, B., Babu, S., Cieslewicz, J., Datar, M., Ito, K., ... & Widom, J. (2016).
Stream: The stanford data stream management system. In Data Stream Management (pp. 317-
336). Springer Berlin Heidelberg.
Bester, K., Chandler, A. T., Shewell, M. A., & Yates, S. J. (2016). U.S. Patent No. 9,378,254.
Washington, DC: U.S. Patent and Trademark Office.
Clifford, P., & Robinson, M. (2014). U.S. Patent Application No. 14/786,728.
Clifford, P., Bhandari, R., & Rogers, T. (2014). U.S. Patent Application No. 14/763,325.
Krishnamurthy, S., Thombre, N., Conway, N., Li, W. H., & Hoyer, M. (2014). U.S. Patent No.
8,745,070. Washington, DC: U.S. Patent and Trademark Office.
Nadkarni, U. (2013). U.S. Patent No. 8,554,754. Washington, DC: U.S. Patent and Trademark
Office.
Reddy, T. B., Thomas, A. D., Stamatis, D., Bertsch, J., Isbandi, M., Jansson, J., ... & Kyrpides,
N. C. (2014). The Genomes OnLine Database (GOLD) v. 5: a metadata management system
based on a four level (meta) genome project classification. Nucleic acids research, 43(D1),
D1099-D1106.
Sim, K. S., Chong, S. S., Tso, C. P., Nia, M. E., Chong, A. K., & Abbas, S. F. (2014).
Computerized database management system for breast cancer patients. SpringerPlus, 3(1), 268.
Starkey, J. A. (2013). U.S. Patent No. 8,504,523. Washington, DC: U.S. Patent and Trademark
Office.