NIT1201 WMU Enrollment DB

Introduction

The objective of this assignment is for you to put into practice the many different skills that you are learning in this unit into a single cohesive database project.

You will be designing a database to meet a specific organisational need. To do this you will work through the various stages of database design, including identifying user requirements, developing an understanding of the entities required and the relationships between them, as well as identifying the business rules associated with the processes that are driving the need for the database. You will then develop appropriate data models and design and implement the database. You will demonstrate that your database implementation is viable through a series of queries and updates on the database.

Scenario

Western Melbourne University (WMU) requests you to work as a database designer to provide a database solution as part of its course enrolment system. The WMU Enrolment database (WMUED) is expected to store and maintain enrolment activities and records for all students in seven colleges: Arts, Science, Engineering, Business, Law, Education, and Information Technology.

WMU accepts two intakes every year, one occurs in February and the other is in July. All colleges provide a number of Bachelor and Master courses. The Bachelor course normally take three years to complete for full-time students, except Engineering Bachelor courses require four years and Science Bachelor courses need five years. All Master courses take students two years to finish all requirements. Every year, students will have two semesters. Each semester, a full-time student with 100% study load needs to take four units. That is, for a three-year Bachelor course, students will complete 24 units; for a four-year Bachelor course, students will complete 32 units; and for a five-year Bachelor course will complete 40 units. A master course requires students to complete 16 units in total.

When a new student is coming to WMU, some basic information will be collected and stored, such as full name, full home address, contact local number, mobile number, a personal email address, emergency contact person information (name, contact number, relationship). Students also need to provide previous education records, including qualification type, institute, and year of completion.

Once a student enrol into a course in February, he or she needs to select at most four units (including part-time study load) for Semester 1and another four units for Semester two. Students are allowed to change their units four weeks before every semester starts. If a student enrols in July, then he or she only needs to select four units for Semester 2. Re-enrolment for both semesters will be opened next year. Each unit only is only offered in one semester, which means Unit X is offered in Semester 1, but not in Semester 2; and Unit Y is offered in Semester 2, then it will not be offered in Semester 1. Once students successfully enrol into the required units, he or she will be given a list of the enrolled units and fees to pay for the coming semester. The payment is requested for one semester, up to four units at most. Each unit will charge from $2500 to $4000 accordingly.

The units are recorded by unit code and unit name. A unit code is assigned to a particular unit only. Same rule is applied on course code as well. A unit will be taught by one lecturer and one or more tutors. The lecturer can work as a tutor too. According to the number of the enrolled students, one or more labs will be offered in one unit. Each lab will be taken by a tutor. One tutor can take more than one lab. Each lab has a capability to have at most 30 students. The teaching allocation of all lecturers and tutors will be saved in each semester. Their basic information, their class allocation information (including unit to teach, class type, time starts, time ends, room location, etc.) are collected as well.

All students will take lecture at the same time, but labs may not start for the same time. According to different units, a lecture lasts for one to two hours; and a lab takes from one, two or three hours.

Each unit requires students to finish a number of assessment tasks, including lab exercises, tests, assignments, reports, in-class activities, practical demonstrations, and examinations. The results of all the assessments will be recorded. Grades will be granted based on the final results using the following rules:

• N under 50
• P 50 to 59
• C 60 to 69
• D 70 to 79
• HD 80 or above

All students will receive a report of their results of the enrolled units three weeks after the examination. In the report, the student information, course information, unit information, the final marks of each unit and corresponding grades will be sent to students via their university email and SMS to their mobile phones. A unit review report will be available for lecturers to access to evaluate student performance.

Your task

You have been commissioned to develop a database system that is capable of growing as WMUED does.

The database needs to keep a record of:

• All student basic information
• All staff basic information
• All student enrolment information
• All teaching allocation
• All assessments and results
• Timetable for all units in both semesters

Further, it should be possible to generate a report on:

• Course enrolment including number of students who enrol newly or continuously
• Unit enrolment including number of students who enrol newly or repeat
• Teaching staff allocation including staff information, class type, time and room location
• Assessment results of individual students for all enrolled units
• Student performance of a particular unit including all assessment results and final marks, sorted based on grades and surnames

Steps you need to take to develop your database application

1. Complete the analysis and design of your database application

• a. List the business rules for your system (do not get distracted by red herrings in the scenario!).
• b. Identify the entities and relationships in your system.
• c. Identify the characteristics of the entities in your system.
• d. Develop an ER diagram to model your system.
• e. Develop table structures from the ER model.
• f. Conduct a dependency analysis of the table structures and normalize your tables where appropriate, to at least 3NF.
• g. Create a data dictionary for your database.

2. Implement your project

• a. Create a database that hosts your application data
• b. Create tables in your database. These must be consistent with your design.
• c. Populate all tables with sample data (at least 5 entries in each)
• d. Create the required views, stored procedures etc. to meet the requirements of your system

You need to be able to demonstrate that your database application meets the requirements detailed in the scenario as well as be consistent with the model you have developed.