Relational Database Examples

An Example of a relational database and Normalisation

1. Relational Database Exercises
John Cutajar

2. Databases 2
First Normal Form
Which rule does this table violate?
Change the following table in 1NF

3. Databases 3
First Normal Form
No repeating groups.As an example, it might be
tempting to make an invoice table with columns for
the first, second, and third line item (see above).
This violates the first normal form, and would result
in large rows, wasted space (where an invoice had
less than the maximum number of line items), and
*horrible* SQL statements with a separate join for
each repetition of the column. First form
normalization requires you make a separate line item
table, with it's own key (in this case the combination
of invoice number and line number)

4. Databases 4
From 1NF to 2NF
Why not in 2NF: Now convert to 2NF

5. Databases 5
Second Normal Form
Each column must depend on the *entire*
primary key.As an example, the customer
information could be put in the line item table
(previous slide). The trouble with that is that the
customer goes with the invoice, not with each line on
the invoice. Putting customer information in the line
item table will cause redundant data, with it's inherant
overhead and difficult modifications. Second form
normalization requires you place the customer
information in the invoice table.

6. Databases 6
From 2NF to 3NF
Why not in 3NF: Now convert to 3NF

7. Databases 7
Third Normal Form
Each column must depend on *directly* on the
primary key.As an example, the customer address
could go in the invoice table (previous slide), but this
would cause data redundancy if several invoices were
for the same customer. It would also cause an
update nightmare when the customer changes his
address, and would require extensive programming
to insert the address every time an existing customer
gets a new invoice. Third form normalization requires
the customer address go in a separate customer
table with its own key (customer), with only the
customer identifier in the invoice table.

8. Databases 8
From 2NF to 3NF
Now in 3NF

9. Databases 9
First Normal Form
Which rule does
this table violate?
Change the
following table in
1NF
Members List
1John CutajarAccess, DB2, FoxPro
2Paul BorgdBase, Clipper
3Mary Hanks
4Joe Doe DB2, Oracle
5Tim Rice Oracle, Sybase
6Paul SimonInformix
7Ned Blue
8Sam Red Access, MySQL
9Tina Skoss

10. Databases 10
From 1NF to 2NF
Now to 2NF
Members Table
MID Member
1 John Cutajar
2 Paul Borg
3 Mary Hanks
4 Joe Doe
5 Tim Rice
6 Paul Simon
7 Ned Blue
8 Sam Red
9 Tina Skoss
Database Table
MIDDIDDatabase
1 1 Access
1 2 DB2
1 3 FoxPro
2 4 dBase
2 5 Clipper
4 2 DB2
4 6 Oracle
5 6 Oracle
5 7 Sybase
6 8 Informix
8 1 Access
8 2 MySQL

11. Databases 11
From 2NF to 3NF
Now in 3NF
Members Table
MID Member
1 John Cutajar
2 Paul Borg
3 Mary Hanks
4 Joe Doe
5 Tim Rice
6 Paul Simon
7 Ned Blue
8 Sam Red
9 Tina Skoss
Database
Table
MIDDID
1 1
1 2
1 3
2 4
2 5
4 2
4 6
5 6
5 7
6 8
8 1
8 9
Database Table
DIDDatabase
1 Access
2 DB2
3 FoxPro
4 dBase
5 Clipper
6 Oracle
7 Sybase
8 Informix
9 MySQL