Database Schema Design

This assignment presents a detailed database schema design for a hypothetical system related to course management. It includes multiple tables such as 'MembersOFclass', 'classes', and 'courses' with well-defined field names, data types, and relationships established through primary and foreign keys. The description aims to illustrate how to structure a relational database effectively.

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 3
Data dictionary 4
Database schema 6
Bibliography 10

2ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
ER Diagram
Figure 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.

3ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
 Every member has a payment statement for each class.
 One member may have zero or more award.
 Every payment has one payment statement.
Normalization
Course (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 -> runningCourse
exists in the course relation. Therefore, the only depended variable is runningCourse. To provide
redundancy 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 in
the class relation. Therefore, the only dependent variable is MemberID. To provide proper
integrity constraints for that relation it need to decomposed into two relations. Such as
memberOFclass (id, memberID, classID).

4ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Data dictionary
Name of Table staff
Primary / Foreign Key Field Name
Primary key ID
name
email
phone_number
weeklypaymentrate
taxnumber
address
Name of Table awards
Primary / Foreign Key Field Name
Primary key AWARDID
name
description
dateawarded
note
award type
Foreign Key MemberID
Name of Table RunningCourse
Primary / Foreign Key Field Name
Primary key CODE
EndDate
StartingDate
NOTES
Foreign Key COURSEID
Foreign Key StaffID

5ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Name of Table payments
Primary / Foreign Key Field Name
Primary key paymentID
bankname
accounttype
accountnumber
accountName
Foreign Key MembersID
Name of Table Members
Primary / Foreign Key Field Name
Primary key MemberID
NAME
address
email
phonenumber
DOB
Notes
DOJ
DOL
Name of Table Paymentstatement
Primary / Foreign Key Field Name
Primary key ID
PaidDate
AmountPaid
Foreign Key PaymentID
Foreign Key StaffID
Foreign Key MEMBERID
Name of Table Course
Primary / Foreign Key Field Name
Primary key CourseID
name

6ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
numberofclasses
coursedescription
maximumstudent
prerequisitecourseID
Name of Table MembersOFclass
Primary / Foreign Key FIELD NAME
Primary key ID
Foreign Key MemberID
Foreign Key classID
Name of Table classes
Primary / Foreign Key FIELD NAME
Primary key classID
Foreign Key CourseID
DateTime
RoomNumber
Notes
Database schema
Name of Table payments
Primary / Foreign Key Field Name Data Type Purpose
Primary key paymentID number Primary key of payments
bankname varchar Name of the bank
accounttype varchar Type of the account
accountnumber number Number of the account
accountName varchar Name of the account
Foreign Key MembersID number Member identification number

7ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Name of Table staff
Primary / Foreign Key Field Name Data Type Purpose
Primary key ID number Primary key of staff
name varchar name of the staff
email varchar email of the staff
phonenumber number phone number of the staff
weeklypaymentrate double weekly payment rate of the staff
taxnumber number tax number of the staff
address varchar address of the staff
Name of Table Paymentstatemen
t
Primary / Foreign
Key
Field Name Data
Type
Purpose
Primary key ID number Primary key of Paymentstatement
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 Primary key of awards
name varchar name of the awards
description varchar description of the awards
dateawarded date date awarded of the member
note varchar note of the awards
award type varchar award type of the awards
Foreign Key MemberID number unique ID of the member

8ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Name of Table RunningCourse
Primary / Foreign Key Field Name Data
Type
Purpose
Primary key CODE number Primary key of RunningCourse
EndDate date end date of course
StartingDate 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 MemberID Number Primary key of members
NAME varchar name of the member
address varchar address of the member
email varchar email of the member
phonenumber 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 Primary key of course
name varchar name of the course
numberofclasses number Total number of classes.
coursedescription varchar details of course.
maximumstudent number maximum number of student
prerequisitecourseI
D
number unique Identification number of another
course
Name of Table MembersOFclass

9ITECH 1006 - DATABASE MANAGEMENT SYSTEMS
Primary / Foreign
Key
FIELD NAME Data
Type
Purpose
Primary key ID number Primary key of MembersOFclass
Foreign Key MemberID 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 Primary key of classes
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

10ITECH 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.
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.
Starkey, J. A. (2013). U.S. Patent No. 8,504,523. Washington, DC: U.S. Patent and Trademark
Office.
Weinberg, P. N., & Cherny, E. (2016). U.S. Patent No. 9,495,475. Washington, DC: U.S. Patent
and Trademark Office.