Database Management System Design

This assignment focuses on designing the schema for a database system used by a university. The schema should include tables for Students, Courses, Units, EnrolledUnits, Assessments, TutorAllocation, LabStaffAllocation, and relevant relationships between them. The design must ensure data integrity, efficiency, and support key functionalities such as student enrollment, course management, and assessment tracking.

Database Assignment
Student Name
Student Roll-Number
1

Table of Contents
S.No Title PageNo
1 Business Rules 3
2 Entities 3
3 Entities Characteristics 3
4 ER Diagram 4
5 Dependency Diagram 4
6 Table Structure 5
7 Database Creation 7
8 Table Creation 8
9 Data Insertion 9
10 Views 11
11 Procedures 12
2

Business Rules:
Western Melbourne University consists of 7 colleges Arts, Science, Engineering, Business, Law,
Education, and Information Technology. The WMU Enrolment database is developed to store
and maintain the information about the students enrolled in the college. The university intakes
students during the month of February and July. All colleges provide both Bachelor and Master
course. Most of the Bachelors take 3 years and master course takes 2 years to complete. Each
year the students will attend two semesters. Each semester the student takes 4 units. The database
maintains the information of the students enrolled. Some of the information includes name,
address, contact number, mobile number, email address, contact person information like name,
contact number and his/her relationship with the student. Each unit is identified with the unit
code and name of the unit and the semester in which the student can enroll for the unit. After a
student select units for the semester, fees to pay for the semester is sent to the student. The units
are taught by on lecturer and one or more tutors. The staff information include their id, name,
email address and contact number are maintained in the database. Each unit will have one or
more labs with each lab accommodating at most 30 students. The staff allocation to the unit is
done and is maintained in the database. The information includes unit to teach, class type, time
starts, time ends, room location. Each unit undertaken by the student requires finishing some
assessment tasks like lab exercises, tests, assignments, reports, in-class activities, practical
demonstrations, and examinations. The assessment marks of the student are maintained in the
database and grades are provided to the student using the rules:
• N – under 50
• P – 50 to 59
• C – 60 to 69
• D – 70 to 79
• HD – 80 or above
Entities:
The list of entities in the database includes Student, Staff, Unit, StudentFees, EnrolledUnits,
Assessment, Course, TutorAllocation and LabStaffAllocation
Entities Characteristics:
The attributes of each of the entities are:
Student entity has StudentID, EnrolledAt, CollegeName, DegreeType, FullName, Address,
LocalNumber, MobileNumber, EmailAddress, ContactPersonName, ContactPersonNumber ,
ContactPersonRelationship, PrevQualificationType, PrevInstitute and PrevYearOfCompletion as
attributes
3

The Staff entity has StaffID, StaffName, ContactNumber and EmailAddress as attributes
The StudentFees entity has StudentID, Year, Semester and Fees as attributes.
The Unit entity has UnitCode, UnitName and OfferedSemester as attributes
The EnrolledUnits entity has StudentID, UnitCode, TotalMarks, Grade as attributes.
The Assessment entity has StudentID, UnitCode, AssessmentTask, Marks as attributes.
The Course entity has CourseID, DegreeType, College, Year, Semester, UnitCode, LecturerID,
ClassType, TimeStarts, TimeEnd, RoomLocation as attributes.
The TutorAllocation entity has CourseID, TutorID as attributes.
The LabStaffAllocation entity maintains the CourseID, LabCount, TimeStart , TimeEnd,
LabCapacity, StaffID as attributes.
ER Diagram:
Dependency Diagram:
Student:
StudentID  EnrolledAt, CollegeName, DegreeType, FullName, Address, LocalNumber,
MobileNumber, EmailAddress, ContactPersonName, ContactPersonNumber,
ContactPersonRelationship, PrevQualificationType, PrevInstitute and PrevYearOfCompletion
Staff:
StaffIDStaffName, ContactNumber and EmailAddress as attributes
StudentFees:
StudentID, Year, Semester Fees
Unit:
4

UnitCode  UnitName and OfferedSemester
EnrolledUnits:
StudentID, UnitCode  TotalMarks, Grade
Assessment:
StudentID, UnitCode, AssessmentTask Marks
Course:
CourseID  DegreeType, College, Year, Semester, UnitCode, LecturerID, ClassType,
TimeStarts, TimeEnd, RoomLocation
TutorAllocation:
CourseID, TutorID
LabStaffAllocation:
CourseID, LabCount  TimeStart , TimeEnd, LabCapacity, StaffID
Table Structure:
Student
Attribute Name Data Type Key
StudentID int Primary Key
EnrolledAt varchar(20)
CollegeName varchar(20)
DegreeType varchar(20)
FullName varchar(20)
Address varchar(20)
LocalNumber int(10)
MobileNumber int(10)
EmailAddress varchar(20)
ContactPersonName varchar(20)
ContactPersonNumber int(10)
ContactPersonRelationship varchar(20)
PrevQualificationType varchar(20)
PrevInstitute varchar(20)
PrevYearOfCompletion int
Staff
Attribute Name Data Type Key
5

StaffID int Primary Key
StaffName varchar(20)
ContactNumber int(10)
EmailAddress varchar(20)
StudentFees
Attribute Name Data Type Key
StudentID int Primary Key, Foreign Key
Student(StudentID)
Year int Primary Key
Semester int Primary Key
Fees int
Unit
Attribute Name Data Type Key
UnitCode int Primary Key
UnitName varchar(20)
OfferedSemester int
EnrolledUnits
Attribute Name Data Type Key
StudentID int Primary Key, Foreign Key
Student(StudentID)
Year int Primary Key
Semester int Primary Key
UnitCode int Primary Key, Foreign Key
Unit(UnitCode)
TotalMarks int
Grade varchar(2)
Assessment
Attribute Name Data Type Key
StudentID int Primary Key, Foreign Key
Student(StudentID)
UnitCode int Primary Key, Foreign Key
Unit(UnitCode)
AssessmentTask varchar(20) Primary Key
Marks int
6

Course
Attribute Name Data Type Key
CourseID int Primary Key
DegreeType varchar(20)
College varchar(20)
Year int
Semester int
UnitCode int Foreign Key Unit(UnitCode)
LecturerID Int
ClassType varchar(20)
TimeStarts varchar(20)
TimeEnd varchar(20)
RoomLocation int
TutorAllocation
Attribute Name Data Type Key
CourseID int Primary Key, Foreign Key
Course(CourseID)
TutorID int Primary Key, Foreign Key
Staff(StaffID)
LabStaffAllocation
Attribute Name Data Type Key
CourseID int Primary Key
LabCount int Primary Key
TimeStart int
TimeEnd int
LabCapacity int
StaffID int Foreign Key Staff(StaffID)
Database Creation:
create database WMUED;
use WMUED;
7

Table Creation:
Create table Student( StudentID int PRIMARY KEY, EnrolledAt varchar(20), CollegeName
varchar(20), DegreeType varchar(20), FullName varchar(20), Address varchar(20),
LocalNumber int(10), MobileNumber int(10), EmailAddress varchar(30), ContactPersonName
varchar(20), ContactPersonNumber int(10), ContactPersonRelationship varchar(20),
PrevQualificationType varchar(20), PrevInstitute varchar(20), PrevYearOfCompletion int);
Create table Staff( StaffID int, StaffName varchar(20), ContactNumber int(10), EmailAddress
varchar(20),PRIMARY KEY(StaffID));
Create table StudentFees(StudentID int , Year int, Semester int, Fees int, PRIMARY
KEY(StudentID, Year, Semester), CONSTRAINT StudentFees_StudentIdFK FOREIGN
KEY(StudentID) references Student(StudentID));
Create table Unit( UnitCode int, UnitName varchar(20), OfferedSemester int,PRIMARY
KEY(UnitCode));
Create table EnrolledUnits(StudentID int, Year int, Semester int, UnitCode int, TotalMarks int,
Grade varchar(2), primary key(StudentID, Year, Semester, UnitCode), CONSTRAINT
EnrolledUnits_StudentIdFK FOREIGN KEY(StudentID) references
Student(StudentID),CONSTRAINT EnrolledUnits_UnitCodeFK FOREIGN KEY(UnitCode)
references Unit( UnitCode));
Create table Assessment( StudentID int, UnitCode int, AssessmentTask varchar(20), Marks int,
Primary key(StudentID, UnitCode, AssessmentTask), CONSTRAINT Assessment_StudentIdFK
FOREIGN KEY(StudentID) references Student (StudentID), CONSTRAINT
Assessment_UnitCodeFK FOREIGN KEY(UnitCode) references Unit( UnitCode));
Create table Course(CourseID int Primary key, DegreeType varchar(20), College varchar(20),
Year int, Semester int, UnitCode int, LecturerID int, ClassType varchar(20), TimeStarts
varchar(20), TimeEnd varchar(20), RoomLocation int, CONSTRAINT Course_UnitCodeFK
FOREIGN KEY(UnitCode) references Unit( UnitCode));
Create table TutorAllocation( CourseID int, TutorID int, Primary key(CourseID, TutorID),
CONSTRAINT TutorAllocation_CourseIDFK FOREIGN KEY(CourseID) references
Course(CourseID));
Create table LabStaffAllocation( CourseID int, LabCount int, TimeStart varchar(20), TimeEnd
varchar(20), LabCapacity int, StaffID int, Primary key(CourseID, LabCount), CONSTRAINT
LabStaffAllocation_CourseIDFK FOREIGN KEY (CourseID) references Course(CourseID),
CONSTRAINT LabStaffAllocation_StaffIDFK FOREIGN KEY(StaffID) references
Staff(StaffID));
8

Data Insertion:
insert into Student values(1, 'February', 'Arts', 'Bachelor', 'John Martin', '12 Walter St NJ',
4323424234,432342342, '[e-mail]', 'Mary Martin', '64535353', 'Mother' , 'School',
'NJ Public School', 2016);
insert into Student values(2, 'July', 'Science', 'Bachelor', 'Martin Luther', '1 King St Mexico',
45345344,654563453, '[e-mail]', 'Luther William', '342342342', 'Brother' ,
'School', 'Mexico Public School', 2015);
insert into Student values(3, 'February', 'Engineering', 'Bachelor', 'Rose William', '10 Fight Rd
Canada', 342342342,45353535, '[e-mail]', 'William', '6453342423', 'Father' ,
'School', 'Canada Public School', 2017);
insert into Student values(4, 'July', 'Education', 'Masters', 'JamesWilliam', '2 Fling St NJ',
435345434, 52234242, '[e-mail]', 'WilliamMartin', '643424353', 'Brother' ,
'Bachelor', 'NJ Public College', 2016);
insert into Student values(5, 'July', 'Information Technology', 'Masters', 'Martin King', '8 Walter
St JFK', 4323232,4323424342, '[e-mail]', 'Kingsy Luther', '63243253', 'Sister' ,
'Bachelor', 'JFK Public School', 2015);
insert into Staff Values(1, 'Rose William',442342232 ,'[e-mail]');
insert into Staff Values(2, 'James Luther',343286786 ,'[e-mail]');
insert into Staff Values(3, 'Martin Luther',454657665,'[e-mail]');
insert into Staff Values(4, 'Tom Harry',452335457,'[e-mail]');
insert into Staff Values(5, 'John King',567575756,'[e-mail]');
insert into StudentFees Values(1, 1, 1, 100);
insert into StudentFees Values(2, 1, 1, 250);
insert into StudentFees Values(3, 1, 1, 300);
insert into StudentFees Values(4, 1, 1, 150);
insert into StudentFees Values(5, 1, 1, 250);
9

insert into Unit values(1, 'Basics of Computer',1);
insert into Unit values(2, 'US History',1);
insert into Unit values(3, 'British',2);
insert into Unit values(4, 'Human Body',2);
insert into Unit values(5, 'Human Science',1);
insert into EnrolledUnits values (1,1,1,1,60,'C');
insert into EnrolledUnits values (2,1,1,2,48,'U');
insert into EnrolledUnits values (3,1,1,5,55,'P');
insert into EnrolledUnits values (4,1,2,3,72,'D');
insert into EnrolledUnits values (5,1,2,4,83,'HD');
insert into Assessment values(1,1,'lab exercises',60);
insert into Assessment values(2,2,'reports',50);
insert into Assessment values(3,5,'in-class activities',72);
insert into Assessment values(4,3,'assignments',60);
insert into Assessment values(5,4,'tests',85);
insert into Course values(1,'Bachelor','Education',1,1,1,1,'Normal','8 AM','3 PM',12);
insert into Course values(2,'Bachelor','Science',2,2,2,3,'Lab','9 AM','9 PM',1);
insert into Course values(3,'Master','Arts',3,1,5,2,'Normal','8 AM','1:30 PM',5);
insert into Course values(4,'Bachelor','Engineering',1,2,4,4,'Practical','8:30 AM','10 AM',8);
insert into Course values(5,'Master','Information Technology',2,1,3,5,'Normal','10 AM','2:30
PM',2);
10

insert into TutorAllocation values (1,2);
insert into TutorAllocation values (1,3);
insert into TutorAllocation values (2,1);
insert into TutorAllocation values (3,4);
insert into TutorAllocation values (4,5);
insert into LabStaffAllocation values (1,2, '7 AM', '3 PM',30,2);
insert into LabStaffAllocation values (1,1, '9 AM', '12PM',25,3);
insert into LabStaffAllocation values (2,1, '8:30 AM', '2 PM',13,1);
insert into LabStaffAllocation values (3,1, '12 PM', '1:45 PM',30,4);
insert into LabStaffAllocation values (5,1, '1 PM', '2:30 PM',23,5);
Views:
Create view StudentList as Select StudentID, FullName, Address, CollegeName, DegreeType
from Student;
select * from StudentList;
Output:
11

Procedures:
create procedure UnitsList()
select * from Unit;
call UnitsList();
Output:
12

References:
1. https://blog.udemy.com/xampp-tutorial/
13