ACS - 4904 Some Review Questions March 2007
Consider the following star schema. The fact table records each enrollment by a student in a course. The date the student registered and the date the student paid are maintained. The amount the student paid is recorded along with the currency the student paid with (the university now lets you pay in American, Canadian or other funds).
Enroll is a fact table:
Rows in Enroll :
1; 1; 1; 1; 2; 13; 1; 300
1; 2; 5; 1; 2; 13; 1; 350
1; 2; 7; 1; 2; 14; 1; 300
1; 1; 1; 2; 2; 14; 1; 315
The Professor dimension has the following structure:
Sample rows in Professor:
1; 7; Mary; Adams; American; 51,000
2; 8; Barry; Smith; American; 55,000
3; 9; April; McDuff; Canadian; 62,000
4; 10; Ron; Ross; Canadian; 77,000
5; 11; Ma; Margy; American; 85,000
Offering is a dimension with the following structure:
Sample rows in Offering :
13; 4904; 92; Applied Computer Science; Data Warehousing; 3; 001; Fall2004
14; 2914; 92; Applied Computer Science; Database; 3; 001; Winter2005
15; 2914; 92; Applied Computer Science; Database; 3; 002; Winter2005
33; 2914; 92; Applied Computer Science; Database; 3; 001; Fall2004
The Student dimension has the following structure:
Sample rows in Student:
1; 1238764; John; Smith; Canadian; Winnipeg; MB; Canada; Jan 17 1990; Aug 1 1990; Aug 15 2000
5; 1122334; Pat; Stevens; American; Winnipeg; MB; Canada; Jan 18 1990; Aug 1 1990; Aug 15 2000
7; 1111777; John; Doe; American; Winnipeg; MB; Canada; Jan 18 1990; Aug 1 1990; Aug 15 2000
9; 1111777; Pete; Peters; Canadian; Winnipeg; MB; Canada;Jan 18 1990; Aug 1 1990; Aug 15 2000
The currency dimension has the following structure:
Sample rows in Currency
1; Cndn Dollar; Canada
2; US Dollar; United States
The Date dimension
Sample rows in Date:
0; Unknown; ; ; ; ; unknown ; unknown ; 0
1; Monday; 1/17/1990; 1; January; 17; 1990; Winter; 1
2; Tuesday; 1/18/1990; 1; January; 18; 1990; Winter; 1
50; Wednesday; 2/19/1990; 2; February; 19; 1990; Winter; 1
101; Tuesday;8/1/1990; 8; August 1, 1990; Summer; 2
3759; Monday; 8/15/2000; 8; August 15, 2000; Summer; 32
The grade dimension:
Rows in grade are:
0; unknown
1; Excellent; A; 4.0
2; Superior; B; 3.0
3; Excellent; A+; 4.5
- Consider the SQL query.
SELECT COUNT(*)
FROM enroll INNER JOIN student
ON (enroll.studentSK = student.studentSK)
WHERE student.citizenship = ‘Canadian’
a) Describe a bitmap join index based on citizenship that would facilitate execution of the query.
b) Given the sample data, illustrate the contents of the join index.
- Consider an aggregate schema where Professor, DateRegistered, and Grade are lost dimensions; DatePaid is shrunken to Term (see description of Date dimension). Offering is collapsed to department. The metric in the aggregate fact table is the sum of amountPaid. Each row of the aggregate fact table holds the total amount paid for a particular student, term, department, and currency.
a) Illustrate the aggregate schema (give the ERD) and specify the attributes of the physical fact table.
b) Consider the following query submitted by an end-user
SELECT currencyName, sum(amountPaid)
FROM (((( enroll
INNER JOIN currency ON (enroll.currencySK = currency.currencySK) )
INNER JOIN student ON (enroll.studentSK = student.studentSK) )
INNER JOIN datepaid ON (enroll.datepaidSK = datepaid.dateSK) )
WHERE student.citizenship = ‘canadian’
AND department = ‘applied computer science’
AND currency.country = ‘canada’
AND datepaid.year = 2000
GROUP BY currencyName
Translate the query from the base schema to the aggregate schema.
c) Illustrate an SQL statement that uses the base schema to list the rows which appear in the aggregate fact table (Note that such a statement could be used to build the aggregate).
1