You are given a task to develop a relational schema for a set of classes at UI.

Question

You are given a task to develop a relational schema for a set of classes at UI. Each class has a CourseNo, CourseTitle, and multiple sections. Different courses use the same set of section numbers (SectionNo). Given a CourseNo and a SectionNo, the corresponding class room number (RoomNo) is determined. Each room has a fixed capacity.  

For each of the following relations, convert the short text statement to a graphical representation, underline the primary key attribute(s), diagram the functional dependencies, and indicate whether each functional dependency is a partial, transitive, or full dependency. Then, indicate in what normal form each relation is. If the relation is not in 3NF, decompose it into a set of 3NF relations using graphical representation, and indicate the PK/FK and referential integrity constraints of the resulting tables (but no need to show functional dependencies). a. CLASS(CourseNo, CourseTitle, SectionNo) b. CLASS(CourseNo, SectionNo, RoomNo) c. CLASS(CourseNo, SectionNo, RoomNo, RoomCapacity)

Explanation / Answer

a)CLASS(CourseNo,CourseTitle,SectionNo):

Functional Dependency: CourseNo->SectionNo

This is a Fully functional dependency.

3 NF: A relation is in 3 NF if for every relation R, X->A, if either of condition holds

i)X is a superkey for R.

ii)A is primary attribute, i.e A is proper subset of any candidate key for R.

Since CourseNo is a superkey for this relation Class. Therefore, the relation is in 3 NF.

(b) CLASS(CourseNo, SectionNo, RoomNo):

FD: CourseNo SectionNo->RoomNo

This is a partial dependency.Since CourseNo is partially dependent on RoomNo and SectionNo is also partially depedent on RoomnNo.

The relation is in 3 NF. Since, CourseNo SectionNo is a superkry for the relation.

(c) CLASS(CourseNo, SectionNo, RoomNo, RoomCapacity)
FD: CourseNo SectionNo->RoomNo

RoomNo->RoomCapacity

This is a transitive dependency. As using above two FD's we can deduct CourseNo SectionNo->RoomCapacity.

Following is not in 3 NF.

3 NF Decomposition:

CLASS(CourseNo, SectionNo, RoomNo, RoomCapacity) : R1(CourseNo,SectionNo),R2(RoomNo,RoomCapacity)

Now R1 and R2 are individually in 3 NF.

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.