1 / 13

Database Normalisation

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).

crystal
Télécharger la présentation

Database Normalisation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Normalisation The good, the bad and the gotchea

  2. 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.

  3. 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

  4. If this is bad… what is good?

  5. What are the advantages of this?

  6. 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:

  7. But this only works when I have…

  8. What if I have a mailing address and a residential address? Common solution is to have multiple columns

  9. A solution, but wrong

  10. Atomisation – removing repetition with Primary Keys

  11. 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)

  12. 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.

  13. Atomization and many to many relationships What's the link between atomisation and the link table?

More Related