Tutorial 4

• Before the Tutorial
• Questions
  • Candidate Keys and Highest Normal Form #1
  • Candidate Keys and Highest Normal Form #2
  • Functional Dependencies

Before the Tutorial

Revise the material from Lecture 16, Lecture 17 and Lecture 18.

[Top]

Questions

Candidate Keys and Highest Normal Form #1

Below are five relations and their associated minimal FD diagrams.

For each relation:

1. Write the minimal FD list which corresponds to the given minimal FD diagram.

2. Find the candidate key(s) of the relation.

3. List the prime and non-prime attribute(s) of the relation.

4. Determine the highest normal form (1NF, 2NF, 3NF, BCNF) of the relation.

First Relation

Second Relation

Third Relation

SUBURB (Suburb-name, State, Population, Postcode)

Fourth Relation

Fifth Relation

[Top]

Candidate Keys and Normal Forms #2

Below are six relations, each with its associated minimal FD list.

For each relation, state:

1. its candidate key(s)

2. its highest normal form (1NF, 2NF, 3NF, or BCNF)

Use the COMP2400/6240 convention for designating candidate keys.

1.       R1 (A, B, C)
   
         A → B
         C → B
         

2.       R2 (D, E, F, G)
   
         D → E
         E → F
         

3.       R3 (H, I, J)
   
         H → I
         H → J
         I → H
         

4.       R4 (K, L, M, N)
   
         K, L → M
         K, L → N
         M → L
         

5.       R5 (t, a, n, g, o)
   
         t → a
         t → n
         t → g
         t → o
         n → t
         

6.       R6 (x, y, z)
   
         The minimal FD list is empty
         

[Top]

Functional Dependencies

 STUDENTID COURSECODE       YEAR        SEM       MARK GRADE
---------- ---------- ---------- ---------- ---------- ------
   2010001 COMP1100         2005          1         67 CR
   2010001 COMP1110         2005          2
   2010001 COMP2400         2005          2
   2010001 MATH1003         2005          1         81 HD
   2010035 MATH1003         2004          1         55 P
   2010035 COMP1100         2004          1         61 CR
   2010035 COMP1110         2004          2         58 P
   2010035 COMP2400         2005          2
   2010035 INFS2004         2005          2
   2010035 BUSN1001         2004          1         66 CR
   2010018 MATH1003         2004          1         79 D
   2010052 BUSN1001         2003          2         44 N
   2010052 COMP1100         2003          1            NCN
   2010052 BUSN1001         2005          1         53 P
   2010052 COMP1100         2005          1         67 CR
   2010069 COMP1100         2002          1         78 D
   2010069 COMP3100         2004          3         70 D
   2010069 COMP2400         2004          2         76 D

Below are seven functional dependencies (FDs) among the attributes/columns in the table above.

For each FD:

1. State whether the FD is Consistent or Inconsistent with the sample data in the table.

2. If the FD is Consistent, briefly explain the semantics of the FD.

   If the FD is Inconsistent, give one example of the inconsistency.

1. STUDENTID → COURSECODE

2. COURSECODE → SEM

3. MARK → GRADE

4. COURSECODE, YEAR → MARK

5. STUDENTID, YEAR → GRADE

6. STUDENTID, COURSECODE, YEAR, SEM → MARK

7. STUDENTID, COURSECODE, YEAR, SEM → GRADE

[Top]