The University of Texas

CS 327e

Fall 2004

Lab 3

Due: 2004-10-04 Monday 8.30 a.m. in class. For this Lab, no late assignments will be accepted.

PURPOSE:

The purpose of this lab is to learn about Normalization and good database design.

IMPLEMENTATION:

For this lab, consider solving ALL of the following:

A) Consider a relation that involves STUDENT CLASS GRADE data. Specifically, consider the relation

STUDENT_CLASS_GRADE (CourseNumber, CourseDesc, CourseFee, ClassUniqueNumber, ClassTime, ClassMaxStudentsAllowed, ClassRoom, LetterGrade, StudentEID, StudentFirstName, StudentLastName, ProfessorFirstName, ProfessorLastName, ProfessorEID, DepartmentName, DepartmentSchool).

Where PK = (StudentEID, ClassUniqueNumber)

As you might guess, this will result in a highly denormalized relational data. Your job is to normalize the above relation.

You can assume the following:

1) This is information for 1 semester (say Fall 2004) in a university environment like The University of Texas.

2) A Course may have 1 or more sections (or classes); each section belongs to only 1 course. Each section has a unique number that is unique across all courses for this semester.

3) A professor may teach 1 or more (up to 3) sections, but a section is taught by exactly 1 professor.

4) Domain values for LetterGrade are {A, B, C, D, F, Q, I}

5) A student must be enrolled in a minimum of 3 different courses, with a maximum of 7.

6) StudentEID uniquely identifies each student; ProfessorEID uniquely identifies each professor; ClassUniqueNumber uniquely identifies each class; CourseNumber uniquely identifies each course.

7) Examples of CourseNumber are {CS301K, PH301, CS302, CS303E, CS327E etc.}

8) Examples of ClassUniqueNumber are {53635, 53640, 53650, 53655 etc.}

9) A Department can have 1 or more students and 1 or more professors. Each professor (or student) belongs to exactly 1 department.

10) Assume that there are at least 50,000 students and 500 professors in the university.

11) Each School (or College) can have 1 or more departments, but a department always belongs to a school (College). As an example, the department of computer sciences belongs to the college of natural sciences.

For this relation,

A) Identify all functional dependencies (FD’s). (HINT: I can see at least 5 Functional Dependencies).

B) Break this relation down into relation(s) that are in 2^nd Normal Form (NF). Clearly state the FD’s and primary key for each of these relations.

C) Break the above relation(s) into 3^rd NF. Clearly state the FD’s and primary key for each of these relations.

D) For the relations in C), clearly state the referential integrity constraints (FK-PK relationships).

In all of the above, CLEARLY STATE ANY ADDITIONAL ASSUMPTIONS THAT YOU MIGHT HAVE MADE.

B) For the relations in 3^rdNF above, draw the ER diagram. Clearly label all PK, FK, entity names, relationship names, attribute names, and the referential integrity actions (e.g. delete cascade etc.).

In all of the above, CLEARLY STATE ANY ADDITIONAL ASSUMPTIONS THAT YOU MIGHT HAVE MADE.

SUBMISSION:

Please submit a paper copy in class by the Due Date/Time.

Make sure you have written the following on your paper submission:

· First Name, Last Name

· Course Name

· Lab #

· EID (optional)

MAKE SURE YOU STAPLE THE PAGES BEFORE SUBMISSION.

Consult the class web page for

- Late submission policy

- Academic Dishonesty Policy

Last Updated: 9/24/2004 4:45:42 PM