130 likes | 159 Views
Database Normalisation. The good, the bad and the gotchea. A definition. A relational database is made of multiple tables; each table visually resembles a small spreadsheet (but there are no cells with calculations).
E N D
Database Normalisation The good, the bad and the gotchea
A definition • A relational database is made of multiple tables; each table visually resembles a small spreadsheet (but there are no cells with calculations). • Each column will have the same kind of information (name, address, etc.) for each row. • each row will have the specific information for one real-world entity (person). • A key column in each table will provide information on how it relates to other tables.
1st Normal Form • Each field should only be designed to hold one and only one piece of information. • This is often called the atomisation of data
If this is bad… what is good?
Print an alphabetical list of people by last name Send a form letter that says, “Dear Ben,” in the salutation but address the envelope to “Mr. Goren,” etc Similarly, you can’t put the envelopes in ZIP code order, which will save significantly on bulk mail postage rates You also can’t get a list of everybody who lives in Mesa but not Tempe. In the good example, all those are possible. In the bad example, it’s impossible to:
What if I have a mailing address and a residential address? Common solution is to have multiple columns
2nd NF – remove partial key dependencies Consider a library System… • Book( AccessionNo, Title, Author Forename, ISBN, Dewy Classification, Genre, ) • Loan( AccessionNo, BorrowerID, DateofLoan, BorrowerName, BorrowerForm) • This above makes for needless repetition when loaning books – can you see how? Better: • Book( AccessionNo, Title, Author Forename, ISBN, Dewy Classification, Genre, ) • Loan( AccessionNo, BorrowerID, DateofLoan) • Borrower( BorrowerID, BorrowerName, BorrowerForm)
3rd NF – remove primary key non dependencies • Basic Rule: if a field is not directly related to the PK (or all of the CK) then it’s in the wrong table.
Atomization and many to many relationships What's the link between atomisation and the link table?