Consider the following relation for students enrolled in courses, with Relationa

Question

Consider the following relation for students enrolled in courses, with Relational Notation Sample data for this relation follows: Based on the sample data provided, Identify all possible functional dependencies. Consider both single and multiple attributes. Do not consider attributes R nor E as possible determinants. Identify all possible candidate keys, and choose a primary key. Modify the Relational Notation to show your primary key selection. Place this relation in the highest possible Normal Form. Explain each step as you go along, include referential integrity constraints where applicable.

Explanation / Answer

i. Functional dependencies:

C -> Cn

P-> Pn

(Sy,C,P) -> (Cn,Pn,R,E)

ii. Candiadate key is the key used to identify any row uniquely in the relation.

Here, Course ID is also repeating in SemesterYear, ProfessorID is repeating in SemesterYear.

Possible candidate keys:

a. (C,P): In the sample data combination of CourseID and Professor ID is not repeating

b. (Sy,C,P): In the sample data combination of SemesterYear, CourseID and Professor ID is not repeating

But (Sy,C,P) should be primary key as CourseID and Professor ID may repeat across semester so this is the ideal Primary key.

Relational notation: C_ROLL(Sy,C,P,Cn,Pn,R,E)

iii.

1NF: As per First Normal Form, no two Rows of data must contain repeating group of information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row.

Current relation is in First normal form, no modifications required.

2NF: As per the Second Normal Form there must not be any partial dependency of any column on primary key.

That means, if any column in the relation can be uniquely identified using partial primary key.

e.g., CourseName can be identified using CourseId and same for ProfessorName column.

Hence, this relation fails 2NF.

Splitting the table to follow 2NF

Table 1: Course

Table 2: Professor

Table 3: C_ROLL

3NF: As per third normal form, there should be no transitive functional dependency in the relation. i.e., no non-prime attribute is determined by another non-prime attribute and relation should be in 2NF.

Relation abides those rules, hence, its in 3NF.

CourseID CourseName C-001 Databases C-009 Calculus I C-101 English 101 C-241 Statistics

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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