Lab 1

# 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.

Lab5.zip

Home Page