Database Assignment | Database Management Assignment

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