logo

Database Schema Design

   

Added on  2020-02-23

11 Pages1283 Words188 Views
 | 
 | 
 | 
Running Head: ITECH 1006 - DATABASE MANAGEMENT SYSTEMSITECH 1006 - Database Management Systems[Name of the student][Name of the university]
Database Schema Design_1

ITECH 1006 - DATABASE MANAGEMENT SYSTEMS1Table of ContentsER Diagram2Normalization3Data dictionary4Database schema6Bibliography10
Database Schema Design_2

ITECH 1006 - DATABASE MANAGEMENT SYSTEMS2ER DiagramFigure 1: Entity Relationship Diagram of TopFit(Source: Created by author)Assumption:One staff can have many running courses.One staff can make payment statement for many members.A course may have one or more running classes.A class may have more than one members.A member can have one or more members.
Database Schema Design_3

ITECH 1006 - DATABASE MANAGEMENT SYSTEMS3Every member has a payment statement for each class.One member may have zero or more award.Every payment has one payment statement.NormalizationCourse (courseID, name, numberClassees, courseDescription, prerequisiteCourseID,runningCourse, StartingDate, EndDate, notes)Staff (ID, name, phoneNumber, address, email, taxNumber, jobTitle,weeklyPaymentRate)The relations shown above all are in 3NF, except course. The course relation is in 2NF due to transitive dependency CourseID -> runningCourseexists in the course relation. Therefore, the only depended variable is runningCourse. To provideredundancy free relation this relation must be decomposed in 3rd normal form. Classes (classID, CourseID, DateTime, RoomNumber, Notes, MemberID)Members (MemberID, name, address, email, phoneNumber, DOB, Notes, DOJ, DOL)The above relation is in 3NF, but classes relation is not in 3rd NF.Therefore, the relation class have transitive dependency courseID -> MemberID exists inthe class relation. Therefore, the only dependent variable is MemberID. To provide properintegrity constraints for that relation it need to decomposed into two relations. Such asmemberOFclass (id, memberID, classID).
Database Schema Design_4

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents