Star schema for student database

Star Schema Question

The Scenario
Music University has asked you to help them to design their student performance data mart. Your requirements elicitation with the clients has determined that they are interested in answering questions such as:

· Number of students in each unit in each year

· Number of students who have come to Music from various other institutions (e.g., how many students from Singapore Polytechnic, Republic etc) and which Music courses they have enrolled in.

· Number of students in each unit offering (i.e., ICT394 in Semester 1 2018, is a different unit offering to ICT394 in Semester 2 2018) by year.

· Grade distributions of students in units by year

· Grade distributions of students in units by offering and year

· Grade distributions by Lecturer by unit and offering

· Grade distributions by Course

· Grade distributions by School

· Grade distributions by Previous Institution (e.g., Results for students in IT courses from SP, etc).

There are a number of data sources from which you will need to draw your data. Below are the sources and the tables within them that are/may be of interest to you. These are discussed below (Primary Key, Foreign Key):

Source 1: Course Handbook

The Course Handbook is a FileMaker Pro Database. It contains data regarding all courses, units and offerings of units that are offered by Music University. A course is made up of units, and a unit will have at least one offering each year.:

COURSE (CourseCode, Version, CourseName, SchoolName)

UNIT (UnitCode, CourseCode, Version, UnitName)

UNIT_OFFERING (OfferingNumber, UnitCode, Year, TeachingPeriod)

OFFERING_COORDINATOR (StaffID, UnitOfferingNumber)

Source 2: Student Information System

The Student Information System has its data stored in a relational DBMS (Oracle) at present.

STUDENT (StudentID, StudentName, DateOfBirth)

ENROLMENT (EnrolNumber, StudentID, UnitOfferingNumber, Grade)

Source 3: Human Resources System

The HR System is a proprietary system that is owned by the HR Department.

STAFF_MEMBER(StaffNumber, StaffName, SchoolCode)

SCHOOL(SchoolCode, SchoolTitle)

Source 4: Credit Database

The Credit Database records where students have studied prior to attending Music, and the amount of credit they receive toward their Music degree. The data are stored in a relational DBMS (MySQL) that was developed as a student project at Music.

STUDENT (StudentNumber, Student Name)

PREVIOUS_INSTITUTION (Institution_Code, Institution_Name, Country)

COURSE (Institution_Code, Course_Code, Course_Name)

PREVIOUS_STUDIES (StudentNumber, Institution_Code, Course_Code, Credit_Points)

TO DO:

TASK 1 (): Based on the list of questions the client wants answered (see above), discuss what you see as being the most appropriate level of granularity for your data warehouse. Your discussion will need to explain why you have made this choice, and why the alternatives have been discarded. This should take not more than one (1) page.

TASK 2 (): Design a Star Schema that will support the analyses as listed above.