The University of Texas
CS 327e
Fall 2001
Lab 5
Due: 2001-11-19 Monday 8.30 a.m. by email to TA.
Purpose:
a)
Get
familiar with SQL queries in star schema.
For this lab, develop the 3 SQL queries in the attached lab5.mdb
file (same as problem 1.g in chapter 13, pg. 617 of text):
a) Show the total number of users by different time periods in
each semester.
Some sample (possibly incomplete) output is shown below.
|
SEMESTER_ID |
TIME_ID |
SumOfTOTAL |
TIME_DESCRIPTION |
SEMESTER_DESCRIPTION |
|
FA95 |
2 |
7987 |
Afternoon |
Fall 1995 |
|
FA95 |
1 |
5713 |
Morning |
Fall 1995 |
|
FA95 |
3 |
3183 |
Night |
Fall 1995 |
|
SP95 |
2 |
8085 |
Afternoon |
Spring 1995 |
|
SP95 |
1 |
7550 |
Morning |
Spring 1995 |
|
SP95 |
3 |
3571 |
Night |
Spring 1995 |
b) Show usage numbers by time period, by major, and by student
classification.
Some sample (possibly incomplete) output is shown below.
|
TIME_ID |
MAJOR_CODE |
CLASS_ID |
SumOfTOTAL |
MAJOR_NAME |
CLASS_DESCRIPTION |
TIME_DESCRIPTION |
|
1 |
ACIN |
MS |
228 |
Accounting/Information Systems |
Master Student |
Morning |
|
1 |
ACTG |
FR |
52 |
Accounting |
Freshman |
Morning |
|
1 |
ACTG |
JR |
289 |
Accounting |
Junior |
Morning |
|
1 |
ACTG |
SO |
147 |
Accounting |
Sophomore |
Morning |
|
1 |
ACTG |
SR |
816 |
Accounting |
Senior |
Morning |
|
1 |
ADM3 |
JR |
10 |
Administrative Business |
Junior |
Morning |
c) Compare usage for different major and different semesters.
Some sample (possibly incomplete) output is shown below.
|
MAJOR_CODE |
SEMESTER_ID |
MAJOR_NAME |
SEMESTER_DESCRIPTION |
SumOfTOTAL |
|
ACIN |
FA95 |
Accounting/Information Systems |
Fall 1995 |
537 |
|
ACIN |
SP95 |
Accounting/Information Systems |
Spring 1995 |
420 |
|
ACTG |
FA95 |
Accounting |
Fall 1995 |
1592 |
|
ACTG |
SP95 |
Accounting |
Spring 1995 |
1419 |
|
ADM3 |
FA95 |
Administrative Business |
Fall 1995 |
21 |
For the above problems, assume the following star schema:
Submit:
a)
Rename
the attached file to lab5xxxx.mdb, where “xxxx” is the last 4 digits of your
SSN. After storing the queries, compress the database + zip it up, and send it
as an attachment in an email to the TA.