Question

Suppose a university wants to build a Student Record data warehouse with the following information: student, major, course, department, grade. Also, the school would like to calculate student GPA, major GPA etc. Using the idea of the snowflake schema diagram:

1. Write 4 possible Dimensions, example : X(a,b,c,d) ..etc

2. Write a possible fact table including possible measures

Answer 1

In a data warehouse multi-dimensional schemas are specified using:

• Dimension table: It consists of tuples of attributes of the dimension.

• Fact table: Each tuple is a recorded fact. This fact contains some measured or observed variable (s) and identifies it with pointers to dimension tables. The fact table contains the data, and the dimensions to identify each tuple in the data.

Snowflake Schema: It is a multidimensional schema, in which the dimensional tables are organized into a hierarchy by normalizing them.

1. In a student record database dimensions are:

• Student (name, address, age, major, birth place ...),

• Department (dname, deptid, college, head ...),

• Course (cname, cno, credit, semester, year, instructor ...),

• Grade (semester, year, marks, ...)

2. The dimension tables should be linked to the fact table. So the possible measures in a fact table are

1. total number of students appearing for a course measured as count(*)

2. gpa of a student measured as avg(marks).