Star Schema Model for Victoria University - Explanatory Write-up and Diagram

Victoria University’s IT director would like to keep track of computer lab usage. Lab usage is measured by the number of students using a particular laboratory at a time and duration. Measuring usage is important because it is used for budgeting, billing and planning purposes. A star schema model needs to be developed to keep track of the lab usage statistics.

The main requirements for the schema are to:

Show the total number of users and total time per laboratory by different time periods (day, month, semester, and year)

Recording usage (number and duration) by time periods, a student’s course, a student’s gender and the school and faculty a student belongs to..

Course Details CourseNO, CousreName
Student Details StudentNO, StudentName, Gender
School Details SchoolNO, SchoolName
Faculty Details FacultyNO, FacultyName
Laboratory Details RoomNO, Campus, Location
A student belongs to one course and a course can have many students enrolled in it.
A course belongs to one school and a school may run many courses.
A school belongs to one faculty and a faculty may consist of many schools
Usage is measured by two factors, number and duration. Number represents a count of the students using a laboratory and duration keeps track of the time (minutes) a student uses the facilities.
Currently when a student uses a laboratory, the RoomNO, StudentNO, date, and length of duration is recorded.
Instructions:
From the information provided above students are required to develop a star schema that would satisfy the requirements indicated. An explanation of the derived model is also required.

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

First of all, there is not only one solution to this problem. The resulting star schema can have many shapes, according the decisions involving the tables.
We need a facts table, where to group attributes that are relevant for the metrics we are looking (this case the lab usage). Then we need to establish more dimension tables. For each dimension table we have to set a primary key on corresponding Id column, but not anyhow. These primary keys must be related to one of the columns from the facts table. This will have the primary key composed by each primary key from dimension tables. Besides these considerations,...