you What should have learned after this lecture ...
what the normal forms are
The following anomalies can occur:
+ insertion anomaly: What do we do with students who do not attend a lecture?
+ update anomaly: If a student reaches the next semester, we must ensure that in
all tuples containing information about the student the semester number is
changed accordingly.
+ deletion anomaly: What happens if a student drops his/her only lecture?
Solution of these problems is relatively simple: decompose the relation in several
subrelations which each fulfil the 2NF. Split StudentsLecture in the two relations
attend(reg-id, id) and students(reg-id, name, sem). Both relations satisfy the 2NF.
Moreover, they represent a lossless decomposition.
remarks:
no description of a decomposition algorithm which splits a given relation schema R
into several 2NF relation schemas R1, ..., Rn here, because always 3NF is the goal
(low importance of 2NF)
violation of 2NF only with composite keys
conclusion: The 2NF eliminates the partial FDs between key and non-key
attributes
7.5 Third Normal Form
Definition
A relation schema R with associated FDs F is in third normal form (3NF), if, and
only if, it is in 2NF and for each FD A B F at least one of the following conditions
holds:
B A, i.e., the FD A B is trivial.
A is superkey of R.
B is (part of) some candidate key of R.
These conditions exclude non-trivial FDs between non-key attributes. That is,
transitive dependencies of the type A B and B C, where A is candidate key, B is
no candidate key and C contains at least one non-key attribute is forbidden.
The last condition is rather unintuitive but helps to ensure that every schema has a
dependency-preserving decomposition into 3NF.
Example
relation lecture(id, title, pers-id, room)
Relation is not in 3NF because the FD pers-id room exists, and pers-id is not a key
and room is not (part of) a candidate key.
Possible anomalies:
Information about a professor and his/her room are not available without assignment of a lecture.
update anomaly: Change of the room number of a professor requires a change for
each course with the same professor.
deletion If anomaly: a professor does not hold a class any more, all information
about the professor and his/her room is removed from the database.
solution: Splitting of the schema lecture into the two schemas lecture(id, title, pers-id)
and Prof(pers-id, room).
conclusion: The 3NF eliminates the dependencies from non-key attributes.
3NF synthesis algorithm
goal: decomposition of a relation schema R with the FDs F into relation schemas R1,
..., Rn so that the following three criteria are fulfilled:
R1, ..., Rn is a lossless decomposition of R.
The decomposition preserves the FDs.
The schemas R1, ..., Rn each fulfil the 3NF.
synthesis algorithm for computing the decomposition on the basis of F:
step 1: determine a canonical cover Fc for F
(i.e., left reduction of the FDs, right reduction of the remaining FDs, removal of
FDs of the form A , union rule for identical left sides)
step 2: for each FD A B Fc :
+ create a relation schema RA := A B
+ assign the FDs FA = {C D Fc | C D RA} to RA
step 3: If all schemas RA created in step 2 do not contain a candidate key of the
original schema R, additionally create a relation with the schema RK = K and FK =
where K is a candidate key of R.
step 4: Eliminate schemas RA that are contained in another schema RA .
The result is not uniquely defined, since a set of FDs can have more than one canonical cover. In some cases the result of the algorithm depends on the order in which it
considers the dependencies in Fc.
Example for decomposition algorithm
relation schema ProfAddr(pers-id, name, rank, room, city, street, zipcode, area-code,
state, government)
assumptions:
A city denotes the residence of a professor.
Government is the party of the president.
City names are unique within a state.
The zipcode does not change within a
street.
Cities and streets lie completely in the
single states.
A professor has exactly one office that
he does not share.
rank
name
pers-id
room
zipcode
street
city
state
areacode
government
{pers-id} and {room} are candidate keys of the relation ProfAddr. The relation is not in
3NF since, e.g., the FD {city, state} {area-code} violates the 3NF.