Database System For Western Melbourne University: Analysis And Design

Entities

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

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.

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:

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
StaffID	int	Primary Key
StaffName	varchar(20)
ContactNumber	int(10)
EmailAddress	varchar(20)

Entities Characteristics

 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

 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; 

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));

Data Insertion:

insert into Student values(1, ‘February’, ‘Arts’, ‘Bachelor’, ‘John Martin’, ’12 Walter St NJ’, 4323424234,432342342, ‘[email protected]‘, ‘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, ‘[email protected]‘, ‘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, ‘[email protected]‘, ‘William’, ‘6453342423’, ‘Father’ , ‘School’, ‘Canada Public School’, 2017);

insert into Student values(4, ‘July’, ‘Education’, ‘Masters’, ‘JamesWilliam’, ‘2  Fling St NJ’, 435345434, 52234242, ‘[email protected]‘, ‘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, ‘[email protected]‘, ‘Kingsy Luther’, ‘63243253’, ‘Sister’ , ‘Bachelor’, ‘JFK Public School’, 2015);

insert into Staff Values(1, ‘Rose William’,442342232 ,’[email protected]‘);

insert into Staff Values(2, ‘James Luther’,343286786 ,’[email protected]‘);

insert into Staff Values(3, ‘Martin Luther’,454657665,’[email protected]‘);

insert into Staff Values(4, ‘Tom Harry’,452335457,’[email protected]‘);

insert into Staff Values(5, ‘John King’,567575756,’[email protected]‘);

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);

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);

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);