Database Design For TopFit Gym – Entities, Attributes, And Business Rules

The following assumptions were made.

• Every payment made by a member is either daily, weekly or monthly and is done for a certain course that the member is taking.
• A member can get many awards from the same course although they may be for different reasons or achievements.
• Payments done by a member are done only using the bank and the bank details are provided by the member.
• A course type can have more than prerequisite.
• A member can enroll in more than one course.

The following entities and their attributes were derived from the entity relationship diagram;

MEMBERS (memberID,name,address,phone,dob, notes,emContactID,joinigDate,leavingDate)

COURSE_TYPES (courseTypeID,name, classesNumber, maxStudentsNO)

COURSES (courseID, courseTypeID, staffID, starting_date, end_date)

STAFF (staffID, name, phone, address, email, taxNO, jobTitle, payRate)

COURSE_PREREQUISITES (typeCourseID, prerequisiteID)

PAYMENT (paymentID, courseID, bankID, memberID, date, methodID)

PAYMENT_METHOD (methodID, type, days)

BANK_DETAILS (bankID, name, account_type, accountNO, accountName)

MEMBER_EMERGENCY_CONTACTS (emContactID, name , phone)

MEMBER_COURSES (memberID, courseID)

CLASS (classID, staffID, dateTime, courseID, room, notes)

MEMBER_CLASS (memberID, classID)

COURSE_NOTES (noteID, courseID, notes)

MEBER_AWARDS (memberAwardID, memberID, date, notes, courseID)

AWARDS (awardID, name, description, competencies)

3.1 Justification for normalization

The key attribute is the underlined attribute.

• MEMBERS (memberID,name,address,phone,dob, notes,emContactID,joinigDate,leavingDate)

The relation is in 3NF because the following conditions are true;

• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• COURSE_TYPES (courseTypeID,name, classesNumber, maxStudentsNO)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• COURSES (courseID, courseTypeID, staffID, starting_date, end_date)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• STAFF (staffID, name, phone, address, email, taxNO, jobTitle, payRate)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• COURSE_PREREQUISITES (typeCourseID, prerequisiteID)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• PAYMENT (paymentID, courseID, bankID, memberID, date, methodID)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• PAYMENT_METHOD (methodID, type, days)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• BANK_DETAILS (bankID, name, account_type, accountNO, accountName)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• MEMBER_EMERGENCY_CONTACTS (emContactID, name , phone)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• CLASS (classID, staffID, dateTime, courseID, room, notes)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• COURSE_NOTES (noteID, courseID, notes)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• MEBER_AWARDS (memberAwardID, memberID, date, notes, courseID)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute
• AWARDS (awardID, name, description, competencies)
• The relation is already in 1NF because all repeating groups have been eliminated.
• The relation is already in 2NF because all partial dependencies have been eliminated thus all non-candidate keys are dependent on the candidate key
• The relation in in 3NF because all transitive dependencies have been eliminated and thus all non-key attributes are determined by the key attribute

All the relations are in 3NF except the following relations

• MEMBER_CLASS
• MEMBER_COURSES

The two relations are not in 3NF because there exists transitive dependency in the relations. For MEMBER_CLASS there exists transitive dependency between memberID and classID attributes which are combined to form the key attribute. For MEMBER_COURSES there exists transitive dependencies between the memberID and the courseID attributes which are combined to form the key attribute thus the two relations are not in 3NF but are in 2NF. The relations have been left in 2NF to enhance query efficiency.

4 Conversion of ER diagram to relational schema

5 References

Baral, S. (2014, August 21). Solved MCQ on Database Normalization set-1. Retrieved August 10, 2017, from https://www.siteforinfotech.com/2014/08/solved-mcq-on-database-normalization.html

Silberschatz, A., Korth, H. F., & Sudarshan, S. (1986). Database System Concepts. McGraw-Hill Education.

Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd Edition ed.). New York: McGraw-Hill Education.

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order