Question
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.
Solution Preview
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,...