Database Design Normalisation 1 John Wordsworth Department of

Database Design Normalisation (1) John Wordsworth Department of Computer Science The University of Reading J. B. Wordsworth@rdg. ac. uk Room 129, Ext 6544 April 2002 2 CS 3 X 1

Lecture objectives Explain the benefits of normalisation. Describe and recognise common anomalies in table designs. Explain the concept of functional dependency among attributes in a table. Describe the characteristics of tables in 1 NF and 2 NF. Perform normalisation to 2 NF. April 2002 2 CS 3 X 2

Why normalise? • To control the degree of redundancy in a database design – control, not necessarily eliminate • To ensure that all information has an independent place in the database schema – eliminate anomalies of various kinds April 2002 2 CS 3 X 3

Anomalies • Delete anomaly – delete last tutee of JAO, no way of retaining JAO in table • Insertion anomaly – new student; can’t insert record for new student unless tutor already exists • Update anomaly – change of LPhone; all records must be altered April 2002 2 CS 3 X 4

Repeating groups This table contains a “repeating group”: the columns SName, SHall, SDegree. It is the attributes that repeat, not their values. This table has two rows; each cell in the R/H 3 columns has a composite value. April 2002 2 CS 3 X 5

First normal form (1 NF) In first normal form every cell in the table has one and only one value. Transform to 1 NF by eliminating repeating groups What is a suitable primary key for this relation? April 2002 2 CS 3 X 6

Another 1 NF table April 2002 2 CS 3 X 7

Functional dependency • Consider two attributes A, B in a relation, R • The value of A determines the value of B – for each value of A there is one and only one value of B – A B; ( A determines B); ( B is FD on A) – A is said to be a determinant • E. g. A UNIT CODE; B UNIT TITLE – Note: may be two different units with the same title – A B does not imply B A April 2002 2 CS 3 X 8

Dependency diagram Primary Key LName LPhone SDegree SName SHall UCode April 2002 UTitle 2 CS 3 X 9

Full and partial dependency • Note that although the primary key is composite, some attributes are functionally dependent on only part of it. • E. g. LName is FD on SName • This is called partial dependency. • In second normal form we eliminate partial dependencies. April 2002 2 CS 3 X 10

Second normal form (2 NF) Primary Key LName LPhone SDegree SName SHall UCode UTitle Registration (SName, UCode); Unit (UCode, UTitle); Student (SName, LPhone, SDegree, SHall) April 2002 2 CS 3 X 11

The 2 NF relations Registration Units April 2002 2 CS 3 X 12

The 2 NF relations (contd. ) Student April 2002 2 CS 3 X 13

Key points Normalisation is an operation on a table to remove anomalies. Tables can exhibit functional dependencies between attributes. Tables in 1 NF have no repeating groups. Tables in 2 NF have no partial dependencies. April 2002 2 CS 3 X 14