Consider the Department and Department Chair Attributes in the Course Dimension

Selected Answers to Review Questions 4904 April 1, 2008

Note that whenever you are asked to write a SQL statement you may choose to specify the FROM clause using a picture, as would be given in the SQL Server or MS Access query design interfaces. These questions refer to tables/schemas in the appendix.

Using the Faculty dimension and the gender attribute, illustrate a bitmapped index for Faculty.

Female: 1100011

Male:0011100

Using the Registration star schema, the Faculty dimension, and the gender attribute illustrate a bitmapped join index. Note that the StudentSK column of the Registration fact table is renamed FacultySK in the appendix

Female:0001

Male:1110

The University would like to report on grade point averages obtained by students according to various ranges for the Winter 2008 term. They would like to see a count of the number of students for each range. Currently the ranges they use are:

BandSK / Description / LowerValue / UpperValue
1 / Low [0, 2.0) / 0 / 2.0
2 / Average [2.0, 3.0) / 2.0 / 3.0
3 / Superior [3.0, 4.0) / 3.0 / 4.0
4 / Excellent [4.0, 4.5] / 4.0 / 4.5

Give the SQL statement that generates data for the report.

Using the base schema(IGNORE this question)

Using the Student Performance schema

Select description, count(*)

From Performance P, BandTable, Term T

Where gpa >LowerValue and gpa <= UpperValue

And P.termSK=T.termSK

And termDesc = “Winter08”

Group by description

The Registration schema has a grain defined as one row per student per section registered for. Of course, the dimensions attached to the fact table do not alter the grain. Pay particular attention to the TimeInterval dimension; this has one row for each time interval that is used in the time tabling of courses at the university. It is assumed that each time interval is made of an integral number of time slices. A time sllice is a 15 minute period; there are exactly (4x24=) 96 of these in a day, 6:00 to 6:15, 6:15 to 6:30, … . So.. there is a many to many relationship between registration facts and time slices. Design a bridge table to accommodate this (Give the ERD and table structure). Using 92.4904/3 and92.2941/3, illustrate some rows of the fact table, bridge table, and …

TimeInterval
tiSK / startTime / endTime
1 / 8:00 / 9:00
…
20 / 11:30 / 1:00
TimeSlice
tsSK / startTime / endTime
1 / 6:00 / 6:15
…
23 / 11:30 / 11:45
24 / 11:45 / 12:00
25 / 12:00 / 12:15
26 / 12:15 / 12:30
27 / 12:30 / 12:45
28 / 12:45 / 1:00

Bridge Table:

timeIntervalSK / timeSliceSK
20 / 23
20 / 24
20 / 25
20 / 26
20 / 27
20 / 28

Write SQL statements to

Generate a list of departments and courses offered for each term (department name, course title, term desc)

Select distinct deptName, CourseTitle, termDesc

From ClassPerformance P, Section S, Term T, Dept D

Where join criteria…

Order by termDesc, deptName, CourseTitle

Generate a list of instructors and the number of students they have taught each term (instructor name and a count)

Select LastName, count(*), termDesc

From Registration P, Faculty F, Term T

Where P.termSK=T.termSK and P.facultySK=F.facultySK

Group by termDesc, LastName

Generate a list of students and the number of credit hours they have registered for each term (student name and a sum)

Select StudentName, sum(creditHours), termDesc

From Registration P, Term T, Student S

Where join criteria …

Group by termDesc, studentName, studentID

Students should not be registered for any courses that have conflicts according to the days/times they are offered, but it seems to happen. List any students who have conflicts.

Select studentSK, studentName

From Student Inner Join Registration On … Inner Join Timeinterval On … Inner Join Bridge On … Inner Join Timeslice On …

Group by studentSK, studentName, termSK, timeSlice

Having count(*) > 1

Regarding the aggregate fact tables:
Give the SQL statement that creates the Performance fact table … using the Registration schema

Select studentSK, termSK, avg(gradePoint)

From Registration

Group by studentSK, termSK

Give the SQL statement that creates the Utilization fact table … using the Registration schema

Select deptSK, timeSliceSK, roomSK, termSK, sum(regCount)

From Performance inner join TimeInterval on …

inner join Bridge ON … inner join TimeSlice ON …

Group by deptSK, timeSliceSK, roomSK, termSK

The information analyst tells us we need to allow for team teaching – where several instructors are teaching the same section of a course. In these cases, where 2 or more instructors teach a section, there is a weighting factor for each indicating their contribution to the effort. For one section these factors add to 1. Modify the schema to account for this.

FacultyBridge
GroupSK / facultySK / weight
1 / 5 / 50
1 / 6 / 50
2 / 5 / 40
2 / 6 / 40
2 / 7 / 20

Note that Registration does not have a Faculty FK, rather it will have an attribute that references GroupSK in Faculty Bridge

Appendix

Consider the following Registration star schema. The fact table has one record for each course that a student has completed, and so each fact record is related to one student, one section, one term; these three attributes uniquely identify rows in Registration.

Note that we are assuming that any course is offered during the same time interval on all days that is offered.

The StudentPerformance star schema is an aggregate obtained from Registration. It has one row per student per term.

The RoomUtilization star schema contains data that indicates how each department utilizes rooms over a term. It is an aggregate obtained from Registration. regCount is the count of the number of students who took a courseoffered by the department scheduled for a room. Note that TimeSlice is a dimension and so the grain is down to the 15 minute time period.

The ClassPerformance star schema contains data that indicates how each section per term performs. It is an aggregate obtained from Registration:

Some sample values are:

Term
termSK / termNo / termDesc / academicYear / calendarYear
1 / 1 / Fall98 / 1998-1999 / 1998
2 / 2 / Winter99 / 1998-1999 / 1999
3 / 3 / Spring99 / 1998-1999 / 1999
4 / 1 / Fall99 / 1999-2000 / 1999
5 / 2 / Winter00 / 1999-2000 / 2000
6 / 3 / Spring00 / 1999-2000 / 2000
7 / 1 / Fall07 / 2007-2008 / 2007
8 / 2 / Winter08 / 2007-2008 / 2008
9 / 3 / Spring08 / 2007-2008 / 2008
Student
studentSK / gender / StudentID / StudentName
1 / M / 444 / Edwards
2 / F / 435 / Jones
3 / F / 245 / Ferbey
4 / M / 321 / Johnson
Faculty
fSK / LastName / Dept / Title / Gender
1 / James / Sociology / Professor / Female
2 / Small / Psychology / Associate Professor / Female
3 / Ross / Computing / Assistant Professor / Male
4 / Margy / Computing / Assistant Professor / Male
5 / Yu / Computing / Assistant Professor / Male
6 / Chan / Computing / Professor / Female
7 / Hargood / Psychology / Associate Professor / Female
Section

sectionSK

/ CourseNum / CourseTitle / SectionNo
-1 / Unknown
1 / 2914 / Relational Database Application Development / 2
2 / 4904 / Data Warehouse / 1
6 / 2941 / Unix / 2
5 / 2255 / Sociology 101 / 1
3 / 2255 / Sociology 101 / 1
Registration
FacultySK / SectionSK / TermSK / … / CreditHours / Grade / GradePt
3 / 2 / 8 / 3 / A / 4
5 / 1 / 8 / 3 / B / 3
3 / 6 / 8 / 6 / A / 4
1 / 3 / 1 / 3 / A+ / 4.5
Department

deptSK

/ DeptNum / deptName / DeptChair / Faculty
4 / Unknown / Unknown
1 / 92 / Computing / Margy / Arts & Science
6 / 91 / Computing / Margy / Arts & Science
5 / 44 / Religious Studies / Smith / Theology
3 / 55 / Sociology / James / Arts & Science
TimeSlice
tsSK / startTime / endTime
1 / 6:00 / 6:15
2 / 6:15 / 6:30
3 / 6:30 / 6:45
4 / 6:45 / 7:00
5 / 7:00 / 7:15
Room
roomSK / capacity / room / building
1 / 60 / 3D01 / Duckworth
2 / 20 / 3D02 / Duckworth
3 / 25 / 3D03 / Duckworth
4 / 40 / 3D04 / Duckworth
5 / 12 / 3D05 / Duckworth
6 / 20 / 3D06 / Duckworth
7 / 6 / 3D07 / Duckworth
TimeInterval
tiSK / startTime / endTime
1 / 8:00 / 9:00
2 / 8:30 / 9:30
3 / 8:30 / 10:00
4 / 9:00 / 10:00
5 / 9:30 / 10:30
6 / 9:30 / 11:00
Days
dSK / days / numDays / dayCode
1 / M / 1 / 1
2 / T / 1 / 2
3 / W / 1 / 4
4 / R / 1 / 8
5 / MW / 2 / 5
6 / MWF / 3 / 21
TR / 2 / 10
Sample courses/sections in the Winter 2008 term for UofW
Section / Term / Instructor / Slot / Day / Time / Room
92.2914/3 Relational Database Application Development
2 / 2007T2/W / McFadyen / MW / 11:30-12:45 / 3D03
92.2941/3 Unix
2 / 2007T2/W / Liao / 9 / TR / 11:30-12:45 / 3D04
92.4904/3 Data Warehousing
1 / 2007T2/W / McFadyen / 9 / TR / 11:30-12:45 / 3D03