Normalization

Normalization

• We will take a look at – First Normal Form– Second Normal Form– Third Normal Form

• There are also– Boyce-Codd, Fourth and Fifth normal forms

• In most cases the first three normal forms are enough

normalization

• Why normalization– Avoid redundancy– Minimize risk for inconsistent data– Make it easier to maintain the database– The database takes less space– Avoid Anomalies

• When inserting, updating or deleting data

Normalization

• A table must always have a primary key with unique values for each row.

• An unnormalized table– Table that contains one or more repeating

data groups.

• First Normal Form (1NF)• A relation in which the intersection of each row

and column contains one and only one value.

First Normal Form (1NF)

• Typically a DB Relation (table) should:– A table must have a primary key with unique

values for each row, i.e uniquly indetifies each row in the table.

– Each intersection (cell) in the table contains only one value

– An attribute (column) should not be present more than once.

Table student, unnormalized

studentnr name class Studentcounsellor subject grade subject grade subject grade

16 peter B2 Jensen Danish 7 Math 8 IT 7

24 Ulla A3 MadsenEconomic 6

31 Niels B2 JensenEconomic 8

43 Lena A5 Andersen English 6 IT 6

44 Fie B2 Jensen Math 9

45 Lars A1 Nielsen English 8 Math 7 IT 8

Table Student, 1NF

studentnr name class Studentcounsellor subject mark

16 peter B2 Jensen Danish 7

16 peter B2 Jensen Math 8

16 peter B2 Jensen IT 7

24 Ulla A3 Madsen Economic 6

31 Niels B2 Jensen Economic 8

43 Lena A5 Andersen English 6

43 Lena A5 Andersen IT 6

44 Fie B2 Jensen Math 9

45 Lars A1 Nielsen English 8

45 Lars A1 Nielsen Math 7

45 Lars A1 Nielsen IT 8

Second Normal Form

• Second normal form (2NF)– A relation that is in 1NF and every non-primary-key

attribute is fully functionally dependent on the primary key.

• This means:– The table is in 1NF– No attribute (column) which is not part of primary key,

may depend on part of the primary key.– (Can only be a problem in tables with PK consisting of

several attributes)

Udlaan table, 1NF

Laan: lending (library)BogID: Book IDForfatter: WriterForlag: Publisher

2NF, two solution alternatives

Problem normalization to 1NF

Afdeling table, problem normalization to 2NF

Third Normal Form

• Third normal form (3NF)– A relation that is in 2NF and in which no non-primary-

key attribute is transitively dependent on the primary key.

• This means:– The table is in 2NF– No attribute (column) may depend on any other

column which is not the primary key.

Kunde table, think how to normalize to 3NF