Database Design For Course Management System

Entities

Staying updated with the changing trends of the day is a sign of positive progress. Maintaining manual records has become outdated. The present project has been designed to keep in line with the current digitalized era.

Prior to the creation of the database, the entities have been categorized and the respective connections between them has been traced. The entities that have been interlinked should be in normalized form without repetition of data. Third Normalization Form has been used in designing this project

During the formulation of the database, difficulty was felt in the class registration of students. The process of class allotment to the staff members and the respective class arrangement for the particular course subject was found to be complicated.

Entity: COURSETYPE

Each COURSETYPE belong to one or more course

Entity: SUBJECT

Each SUBJECT refers any one of the major TEXTBOOK

Each SUBJECT has zero or three SUPPTEXTBOOK

Entity: TEXTBOOK

Each TEXTBOOK belong to zero or more SUBJECT

Each TEXTBOOK belong to zero or three SUPPTEXTBOOK

Entity: COURSE

Each COURSE has any one of the COURSETYPE

Each COURSE belong to any one of the DEPARTMENT

Each COURSE has zero or more COURSESUBJECT

Each COURSE has zero or more COURSEOFFERED by the ABCE Branch

Entity: DEPARTMENT

Each DEPARTMENT belong to zero or more number of Course

Each DEPARTMENT has one or more SUBJECT

Entity: RANK

Each RANK belong to one or more FACULTYSTAFF

Entity: ADDRESS

Each ADDRESS belong to one or more ABCEBRANCH

Each ADDRESS belong to one or more STAFF

Each ADDRESS belong to one or more STUDENT

Entity: ABCEBRANCH

Each ABCEBRANCH branch employees to zero or more STAFF

Each ABCEBRANCH branch locates any one of the ADDRESS

Each ABCEBRANCH branch managed by any one of the STAFF

Each ABCEBRANCH branch offers zero or more COURSE

Each ABCEBRANCH branch has to zero or more EQUIPMENT

Each ABCEBRANCH branch allocates to zero or two SEMESTER per year

Each ABCEBRANCH branch transfers to one or more STAFFTRANSFER

Entity: STAFF

Each STAFF works any one of the ABCEBRANCH

Each STAFF manages zero or one ABCEBRANCH

Each STAFF branch locates any one of the ADDRESS

Each STAFF branch may stay temporarily any one of the ADDRESS

Each STAFF may be one of FACULTYSTAFF

Each STAFF may be one of ADMINSTAFF

Each STAFF may be one of OFFICESTAFF

Each STAFF may hire zero or more EQUIPMENT

Entity: FACULTYSTAFF

Each FACULTYSTAFF belong to the any one of the STAFF

ERD

Each FACULTYSTAFF belong to the any one of the RANK

Each FACULTYSTAFF handles one or more FACULTYCLASS

Each FACULTYSTAFF marks zero or more REPORT card to the any one of the STUDENT

Each FACULTYSTAFF may be advisory to zero or 30 number of STUDENT

Entity: OFFICESTAFF

Each OFFICESTAFF belong to the any one of the STAFF

Each OFFICESTAFF generated zero or more REPORT for the student

Entity: ADMINSTAFF

Each ADMINSTAFF belong to the any one of the STAFF

Each ADMINSTAFF designed zero or two SEMESTER per year

Entity: CLASS

Each CLASS belong to the any one of the SEMESTER

Each CLASS handled by zero or more FACULTYSTAFF

Each CLASS needs zero or more PREREQSUBJECT

Each CLASS has zero or more SUBJECT

Entity: SEMESTER

Each SEMESTER contacted by any one of the ABCEBRANCH

Each SEMESTER designed by any one of the ADMINSTAFF

Each SEMESTER has zero or more CLASS

Each SEMESTER has zero or more CLASS

Entity: STUDENT

Each STUDENT stays any one of the ADDRESS

Each STUDENT guided by any one of the FACULTYSTAFF

Each STUDENT may register zero or more CLASSREGISTRATION

Each STUDENT may join zero or more STUDENTCOURSE

Each STUDENT may register zero or more ALTCLASSREGISTRATION

EACH STUDENT may hire zero or more EQUIPMENT

Entity: CLASSREGISTRATION

Each CLASSREGISTRATION may register one of the STUDENT

Each CLASSREGISTRATION belong to one of the CLASSSUBJECT

Each CLASSREGISTRATION has zero or more REPORT

Entity: ALTCLASSREGISTRATION

Each ALTCLASSREGISTRATION may register one of the STUDENT

Each ALTCLASSREGISTRATION belong to one of the CLASSSUBJECT

Each ALTCLASSREGISTRATION has zero or more REPORT

Entity: LOCATION

Each LOCATION has zero or more CLASSSUBJECT

Entity: TIMESLOT

Each TIMESLOT has zero or more CLASSSUBJECT

Entity: CLASSSUBJECT

Each CLASSSUBJECT belong to any one of the TIMESLOT

Each CLASSSUBJECT belong to any one of the LOCATION

Entity: EQUIPMENT

Each EQUIPMENT purchases to any one of the ABCEBRANCH

Each EQUIPMENT may hire zero or more STUDENT

Each EQUIPMENT may hire zero or more STAFF

Entity: STAFFHIRE

Each STAFFHIRE belong to any one of the STAFF

Each STAFFHIRE belong to any one of the EQUIPMENT

Entity: STUDENTHIRE

Each STUDENTHIRE belong to any one of the STUDENT

Each STUDENTHIRE belong to any one of the EQUIPMENT

ERD

Data Dictionary

Entity Name	Attribute Name	Field Type	Description	Constraints
CourseType	CourseTypeID	Number	Course Type ID	PK
	CourseTypeName	VARCHAR(30)	Name of the Course Type
Department	DepartmentID	Number	Department ID	PK
	DepartmentName	VARCHAR(30)	Name of the Department
TextBook	ISBN	VARCHAR(15)	ISBN Number	PK
	BookTitle	VARCHAR(45)	Title of the Book
Location	LocationID	Number	Location ID	PK
	LocationName	VARCHAR(30)	Name of the Location
TimeSlot	TimeSlotID	Number	Time Slot ID	PK
	Day	VARCHAR(10)	Name of the Day
	StartTime	TIMESTAMP	Starting Time
	EndTime	TIMESTAMP	Ending Time
Address	AddressID	Number	Address ID	PK
	StreetNumber	VARCHAR(10)	Street Number
	StreetName	VARCHAR(30)	Name of the Street
	City	VARCHAR(30)	City Name
	State	VARCHAR(3)	State Name in three character format
	Zip	Number	Zip Code
ABCEBranch	BranchID	Number	Branch ID	PK
	BranchName	VARCHAR(30)	Name of the Branch
	AddressID	Number		FK (Address)
	ManagerNumber	VARCHAR(10)	Faculty Staff	FK (FacultyStaff)
Staff	StaffID	VARCHAR(10)	Staff Unique ID	PK
	StaffName	VARCHAR(45)	Name of the Staff
	PermanentAddressID	Number	Permanent Address ID	FK (Address)
	TempAddressID	Number	Temporary/Local Address ID	FK (Address
	ContactNumber	VARCHAR(15)	Contact Phone Number
	Email	VARCHAR(45)	Email Address
	BranchID	Number	Currently Working Branch ID	FK (ABCEBranch)
Rank	RankID	Number	Rank Level ID	PK
	RankName	VARCHAR(30)	Rank Level
FacultyStaff	FacultyID	VARCHAR(10)	Faculty Staff ID	PK, FK (Staff)
	RankID	Number		FK (Rank)
OfficeStaff	OfficeStaffID	Number		PK, FK (Staff)
AdminStaff	AdminID	Number		PK, FK (Admin)
StaffTransfer	BranchID	Number		PK, FK(ABCEBranch)
	StaffID	Number		PK, FK (Staff)
	JoinDate	DATE	Join date of the branch	PK
	TransferDate	DATE	Transfer Date
Course	CourseCode	VARCHAR(6)	Course Code	PK
	CourseName	VARCHAR(45)	Name of the Course
	CourseTypeID	Number		FK (CourseType)
	DepartmentID	Number		FK (Department)
CourseOffered	BranchID	Number		PK, FK(ABCEBranch)
	CourseCode	VARCHAR(6)		PK, FK (Course)
	CourseFee	Number (9,2)	Course Fee
Subject	SubjectCode	VARCHAR(3)	Subject Code	PK
	SubjectName	VARCHAR(45)	Name of the Subject
	SubjectTitle	VARCHAR(45)	Title of the Subject
	Description	VARCHAR(200)	Description of the subject
	ISBN	VARCHAR(15)		FK (TextBook)
SuppTextBook	SubjectCode	VARCHAR(3)		PK, FK (Subject)
	ISBN	VARCHAR(15)		PK, FK (TextBook)
CourseSubject	CourseCode	VARCHAR(6)		PK, FK (Course)
	SubjectCode	VARCHAR(3)		PK, FK (Subject)
	CreditOffered	Number
DepartSubject	DepartmentID	Number		PK, FK (Department)
	SubjectCode	VARCHAR(3)		PK, FK (Subject)
Equipment	EquipmentID	Number	Equipment ID	PK
	EquipmentName	VARCHAR(45)	Name of the Equipment
	EquipDescription	VARCHAR(200)	Description of the equipment
	PurchaseDate	DATE	Purchase Date
	PurchaseAmount	Number(9,2)	Purchase Amount
	HireRate	Number(9,2)	Hire Rate per day
	BranchID	Number		FK(ABCEBranch)
	Status	VARCHAR(30)	Is it available, rent,  damage or else
Semester	SemesterCode	VARCHAR(8)	Semester Code	PK
	StudyPeriodBegin	DATE	Starting Semester Date
	StudyPeriodEnds	DATE	Ending date of the semester
	FinalExamBegins	DATE	Exam starting date
	FinalExamEnds	Date	Suggested Exam ending date
	LastWithdrawDate	DATE	Date of the last with draw of the semester
	BranchID	Number		FK (ABCEBranch)
	AdminID	Number	Designed by	FK (AdminStaff)
StaffHire	StaffHireID	Number	Equipment Hire ID of the Staff	PK
	HireDate	Date	Hire Date
	ReturnDate	DATE	Return Date
	StaffID	Number		FK (Staff)
	EquipmentID	Number		FK (Equipment)
Student	StudentID	VARCHAR(10)	Student Unique ID	PK
	FirstName	VARCHAR(25)	First Name
	LastName	VARCHAR(25)	Last Name
	Gender	VARCHAR(1)	M or F
	DOB	DATE	Date of Birth
	AddressID	Number		FK (Address)
	Phone	VARCHAR(15)
	AdvisorID	Number		FK (FacultyStaff)
StudentHire	StudentHireID	Number	Equipment Hire ID of the Student	PK
	HireDate	Date	Hire Date
	ReturnDate	DATE	Return Date
	StudentID	VARCHAR(10)		FK (Student)
	EquipmentID	Number		FK (Equipment)
StudentCourse	StudentID	VARCHAR(10)		PK, FK (Student)
	BranchID	Number		PK, FK (CourseOffered)
	CourseCode	Number		PK, FK (CourseOffered)
	JoinDate	Date
	OverallGrade	VARCHAR(3)
	SemesterCode	VARCHAR(8)		FK (Semester)
	OverallGPA	Number
	OverallTotalPoints	Number
Class	ClassID	Number	Class Unique ID	PK
	SemesterCode	VARCHAR(8)		FK (Semester)
	MaxEnrolment	Number	Maximum Enrollment
ClassSubject	ClassID	Number		PK, FK (Class)
	CourseCode	VARCHAR(6)		PK, FK (Course)
	SubjectCode	VARCHAR(3)
	TimeSlotID	Number		FK (TimeSlot)
	LocationID	Number		FK (Location)
ClassRegistration	RegNo	Number	Registration Number	PK
	RegDate	Date	Registration Date
	StudentID	VARCHAR(10)		FK (Student)
	ClassID	Number		FK (ClassSubject)
	SubjectCode	VARCHAR(3)		FK (ClassSubject)
	CourseCode	VARCHAR(6)		FK (ClassSubject)
AltClassRegistration	RegNo	Number	Alternate Registration Number	PK
	RegDate	Date	Registration Date
	StudentID	VARCHAR(10)		FK (Student)
	ClassID	Number		FK (ClassSubject)
	SubjectCode	VARCHAR(3)		FK (ClassSubject)
	CourseCode	VARCHAR(6)		FK (ClassSubject)
Report	ReportID	Number		PK
	ReportDate	Date
	RegNo	Number		FK (ClassRegistration)
	Total	Number
	TotalWithCredit	Number
	GPA	Number
	TotalPoints	Number
	FacultyID	Number	Marked faculty ID	FK (FacultyStaff)
	OfficeStaffID	Number	Report generated office staff ID	FK (OfficeStaff)
ReportSubject	ReportID	Number		PK, FK (Report)
	CourseCode	VARCHAR(6)		PK, FK (CourseSubject)
	SubjectCode	VARCHAR(3)		PK, FK (CourseSubject)
	CreditEarned	Number	Credit earned for the particular subject
	Grade	VARCHAR(3)

Final form of ERD assures ease and comfort to the end user and enables them to design the relational database without any interruption

List of Assumption

1. Report Card are generated by Office staff

Office Staff only handle/maintain the student report card

1. Each Student Course maintained the overall total, GPA and Grade

After selecting the course by the student, the above mentioned fields are set to zero. It will update at the end of the course

• Though the project is strong in many aspects. It also has some minor defects.
• There is no provision for student fee history.
• There is no record for Student attendance.
• There are no particular about discussion/feedback of the subject matters through student panel