relational database examples
DESCRIPTION
An Example of a relational database and NormalisationTRANSCRIPT
![Page 1: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/1.jpg)
Relational Database Exercises
John Cutajar
![Page 2: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/2.jpg)
Databases 2
First Normal Form
Which rule does this table violate?
Change the following table in 1NF
![Page 3: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/3.jpg)
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)
![Page 4: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/4.jpg)
Databases 4
From 1NF to 2NF
Why not in 2NF: Now convert to 2NF
![Page 5: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/5.jpg)
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.
![Page 6: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/6.jpg)
Databases 6
From 2NF to 3NF
Why not in 3NF: Now convert to 3NF
![Page 7: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/7.jpg)
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.
![Page 8: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/8.jpg)
Databases 8
From 2NF to 3NF
Now in 3NF
![Page 9: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/9.jpg)
Databases 9
First Normal Form
Which rule does this table violate?
Change the following table in 1NF
Members List
1 John Cutajar Access, DB2, FoxPro
2 Paul Borg dBase, Clipper
3 Mary Hanks
4 Joe Doe DB2, Oracle
5 Tim Rice Oracle, Sybase
6 Paul Simon Informix
7 Ned Blue
8 Sam Red Access, MySQL
9 Tina Skoss
![Page 10: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/10.jpg)
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
MID DID Database
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
![Page 11: Relational Database Examples](https://reader035.vdocuments.us/reader035/viewer/2022081821/548fbd84b479597e6a8b5129/html5/thumbnails/11.jpg)
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
MID DID
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
DID Database
1 Access
2 DB2
3 FoxPro
4 dBase
5 Clipper
6 Oracle
7 Sybase
8 Informix
9 MySQL