Lecture 5

Question 1

3 types of anomalies:

Answer 1

Update (inconsistency)

Deletion

Insertion

Question 2

summary of 1NF, 2NF, 3NF

Answer 2

1NF - contains no repeating data elements

2NF - 1NF and contains no partial-functional dependencies

• only full functional dependency
  
  • A and B are attributes of a relation,
  • B is fully dependent on A if B is functionally dependent on A but not on any proper subset of A
• every non-primary-key-attribute is fully functionally depedent on the primary key

3NF - 2NF and contains no transitive dependencies

• no non-primary-key attribute is transitively dependent on the primary key
• i.e. non-key attributes are mutually independent
  
  • Elements which are mutually dependent are separated off into another table with the independent part as the key

Question 3

1NF to 2NF

Answer 3

• Identify the primary key in the 1NF relation
• Identify the functional dependencies in the relation
  
  • Elements which are not fully dependent are separated off into another table with the relevant part of the key
  • If partial dependencies exist on the primary key remove them by placing then in a new relation along with a copy of their determinant

Question 4

transitive dependency

Answer 4

A —> B and B —> C,
so A —> C (transitive)

(Provided that A is not functionally dependent on B or C).