ITECH 1006 - Database Management Systems Report: ER Diagram & Schema

This report, prepared for the ITECH 1006 Database Management Systems course, presents a comprehensive analysis of database design principles. It begins with an Entity Relationship (ER) diagram illustrating the structure of a fitness center database, followed by an explanation of normalization techniques to minimize data redundancy and ensure data integrity, detailing the decomposition of relations up to the third normal form. The relational schema defines the structure of the tables, including attributes and data types, while the database schema outlines the structure of tables for Member, Course, Payment, Staff, Payment_Statement, Awards, Running_Course, MemberofClass, and Class. The report includes detailed table schemas specifying primary and foreign keys, data types, and purposes of each field within the database, and concludes with a bibliography of relevant sources.

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
Relational schema 5
Database schema 5
Bibliography 9

2ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
ER Diagram
Figure 1: Entity Relationship Diagram of TopFit
(Source: Created by author)
Normalization
The Normalization is an idea of decreasing repetition and also reliance of information to
sorted out a database outline. In other word, it can be characterizing as part huge connection into
littler connection and connection utilizing their useful reliance.
Be that as it may, here a few substances are in first ordinary shape and furthermore have
single segment of essential key. As per the TopFit rec center foundation ponder the above
substance relationship chart is made, where all tables are deteriorated up to third typical frame.

3ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Figure 2: Decomposing relations to eliminate transitive dependency
(Source: Created by author)
At initial a part is related with the class and staffs, thusly if part, class and staffs are
speaking to in one connection. This connection can be characterizing as first typical frame,
however when it isolated in three distinct relations and every one of them contains one essential
key than it could be a second ordinary shape. After that assessing the practical reliance among
these relations, if any transitive reliance found than that connection is disintegrated in third
ordinary frame.

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
Relational schema
MEMBER (Member_ID, NAME, address, email, phone_number, DOB, Notes, DOJ, DOL)
COURSE (CourseID, name, number_of_classes, course_description, maximum_student,
prerequisite_courseID)
PAYMENT (paymentID, bank_name, account_type, account_number, account_Name,
Members_ID)
STAFF (ID, name, email, phone_number, weekly_payment_rate, tax_number, address)
PAYMENT_STATEMENT (ID, PaidDate, AmountPaid, PaymentID, StaffID, MEMBERID)
AWARDS (AWARDID, name, description, date_awarded, note, award_ type, Member_ID)
RUNNING_COURSE (CODE, End_Date, Starting_Date, NOTES, COURSEID, StaffID)
MEMBEROFCLASS (ID, Member_ID, classID)
CLASS (classID, CourseID, DateTime, RoomNumber, Notes)
Database schema
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
account_number number Number of the account
account_Name varchar Name of the account
Foreign Key Members_ID number Member identification
number

6ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
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
date_awarded date date awarded of the member
note varchar note of the awards

7ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
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.
course_description varchar details of course.
maximum_student number maximum number of student
prerequisite_courseI
D
number unique Identification number of another course

8ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
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

9ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Bibliography
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., Bhandari, R., & Rogers, T. (2014). U.S. Patent Application No. 14/763,325.
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.