ITECH 1006 Database Management System Assignment 1 Report Solution

This report presents a comprehensive solution to a database management system assignment. The report includes an ER diagram illustrating the relationships between entities such as courses, staff, members, and payments. Assumptions are clearly stated, followed by the normalization process, ensuring data integrity and efficiency. The normalization process involves breaking down the data into various tables (COURSE, STAFF, MEMBER, CLASS etc.) and defining primary and foreign keys. The report then details the conversion of the ER diagram into a relational schema, specifying field types and descriptions for each table. Tables include COURSETYPE, COURSEPREREQUISITE, STAFF, COURSE, CLASS, AWARDS, MEMBER, MEMBERCOURSES, MEMBERCLASSES, MEMBERAWARD, PAYMENT, AUTOMATICMONTHLY, SINGLEDAY, and MULTIPLEPASS. Each table's fields, data types, and primary/foreign key relationships are meticulously defined, providing a complete database design. The report references sources such as Brombarg and Cinergix Pty Ltd to support the design choices.

Student ID –
Date -
ITECH 1006 - Database Management System
Assignment 1
Module Tutor –
1 | P a g e

Report
Contents
Report.........................................................................................................................................2
ER Diagram................................................................................................................................3
Assumptions...............................................................................................................................4
Normalization.............................................................................................................................4
Conversion of E-R diagram to relational schema......................................................................5
References..................................................................................................................................7
2 | P a g e

ER Diagram
(Cinergix Pty Ltd. 2011)
3 | P a g e

Assumptions
1. A course type can have only one type of pre requisite.
2. The bank detail for the Monthly payment will be saved whether it is regular or
automatic.
3. There may be many courses of a course type.
4. A member may join many courses and take any number classes.
5. Each course type will have a name.
6. Each course of specific course type will also have a name.
Normalization
COURSETYPE (CourseType, CourseNane, NumberOfClassess, Description,
MaximumStudentNumbers)
COURSEPREREQUISITE (CourseType, PreRequisite)
STAFF (StaffID, Name, Phone, Address, Email, TaxNumber, JobTitle, WeekRate)
COURSE (CourseID, CourseName, CourseType, SupervisorStaffID, StartDate, EndDate,
Notes)
CLASS (ClassID, StaffID, ClassDateTime, CourseID, RoomNumber, Notes)
AWARDS (AwardID, Name, Description)
MEMBER (MemberID, MemberName, Address, Email, Phone, DOB, SpecialReuirements,
EmergencyContactName, EmergencyContactNumber, JoiningDate, LeavingDate)
MEMBERCOURSES (MemberID, CourseID)
MEMBERCLASSES (MemberID, ClassID)
MEMBERAWARD (MemberID, AwardID, CourseID, AwardDate, SpecialNotes)
PAYMENT (PaymentMode, PaymentDate, PaidAmount, MemberID)
AUTOMATICMONTHLY (PaymentMode, SuccessFailStatus, BankName, AccountType,
AccountNumber, AccountName)
SINGLEDAY (PaymentMode, PaymentMethod)
MULTIPLEPASS (PaymentMode, PaymentMethod, NumberOfPass)
All relations above are in 3 NF.
(Brombarg P. n.d.)
4 | P a g e

Conversion of E-R diagram to relational schema
Table Field Type Description
COURSETYPE CourseType VARCHAR(50) Primary Key
CourseNane VARCHAR(50)
NumberOfClassess INTEGER
Description VARCHAR(500)
MaximumStudentNumbers INTEGER
COURSEPREREQUIS
ITE
CourseType VARCHAR(50) Primary Key
Foreign Key
References to
CourseType
(CourseType)
PreRequisite VARCHAR(50)
STAFF StaffID INTEGER Primary Key
Name VARCHAR(50)
Phone VARCHAR(20)
Address VARCHAR(100)
Email VARCHAR(50)
TaxNumber VARCHAR(20)
JobTitle VARCHAR(50)
WeekRate DECIMAL(5,2)
COURSE CourseID INTEGER Primary Key
CourseName VARCHAR(50)
CourseType VARCHAR(50) Foreign Key
References to
CourseType
(CourseType)
SupervisorStaffID INTEGER Foreign Key
References to Staff
(StaffID)
StartDate DATETIME
EndDate DATETIME
Notes VARCHAR(500)
CLASS ClassID INTEGER Primary Key
StaffID INTEGER Foreign Key
References to Staff
(StaffID)
ClassDateTime DATETIME
CourseID INTEGER Foreign Key
References to Course
(CourseID)
RoomNumber VARCHAR(10)
Notes VARCHAR(500)
AWARDS AwardID INTEGER Primary Key
Name VARCHAR(50)
Description VARCHAR(500)
MEMBER MemberID INTEGER Primary Key
MemberName VARCHAR(50)
Address VARCHAR(50)
Email VARCHAR(50)
Phone VARCHAR(20)
DOB DATETIME
SpecialRequirements VARCHAR(200)
EmergencyContactName VARCHAR(50)
5 | P a g e

EmergencyContactNumber VARCHAR(20)
JoiningDate DATETIME
LeavingDate DATETIME
MEMBERCOURSES MemberID INTEGER Primary Key
Foreign Key
References to Member
(MemberID)
CourseID INTEGER Primary Key
Foreign Key
References to Course
(CourseID)
MEMBERCLASSES MemberID INTEGER Primary Key
Foreign Key
References to Member
(MemberID)
ClassID INTEGER Primary Key
Foreign Key
References to Class
(ClassID)
MEMBERAWARD MemberID INTEGER Primary Key
Foreign Key
References to Member
(MemberID)
AwardID INTEGER Primary Key
Foreign Key
References to Award
(AwardID)
CourseID INTEGER Foreign Key
References to Course
(CourseID)
AwardDate DATETIME
SpecialNotes VARCHAR(200)
PAYMENT PaymentMode VARCHAR(20) Primary Key
PaymentDate DATETIME
PaidAmount DECIMAL(5,2)
MemberID INTEGER Foreign Key
References to Member
(MemberID)
AUTOMATICMONT
HLY
PaymentMode VARCHAR(20) Primary Key
SuccessFailStatus Bit
BankName VARCHAR(50)
AccountType VARCHAR(50)
AccountNumber VARCHAR(20)
AccountName VARCHAR(50)
SINGLEDAY PaymentMode VARCHAR(20) Primary Key
PaymentMethod VARCHAR(50)
MULTIPLEPASS PaymentMode VARCHAR(20) Primary Key
PaymentMethod VARCHAR(50)
NumberOfPass INTEGER
6 | P a g e

References
Brombarg P. (n.d.), SQL SERVER Database Normalization Basics for Developers, Retrieved
from
http://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-
developers.aspx
Cinergix Pty Ltd. (2011), Ultimate Guide to ER Diagrams, Retrieved from
http://creately.com/blog/diagrams/er-diagrams-tutorial/
7 | P a g e