Normalisation Flashcards
What is normalisation?
A process to come up with the best possible design for a relational database.
What are the rules of Normalisation (0NF)?
- No data is unnecessarily duplicated. (Held in more than one table).
- Data is consistent throughout the database (a customer is not recorded with multiple addresses). This rule occurs due to rule 1.
- Structure of the table is flexible enough to allow as many or as few items as required to be entered.
- Structure must allow complex queries to be made.
Define 1st Normal Form:
A table with no repeating attribute or group of attribute is in 1NF.
eg Product ID: 123, CompID: 123, ABC, @£$ is not in 1NF
How are repeating attributes represented?
With a line over the top of them.
How is 1NF implemented?
Usually with a many to many relationship. (A link table exists between the two tables).
Define 2nd Normal Form:
A table in 1NF and with no partial dependencies is in 2NF.
What is a partial dependency?
Where one or more attributes depends on only part of a composite primary key.
eg EventName is dependant on EventID but not Year. Since EventID and Year are a composite key, this is a partial dependancy.
How may a partial dependency be resolved?
By generating a new table.
Define 3rd Normal Form:
A table in 2NF and with no ‘non key’ dependencies is in 3rd Form.
What is a non key dependency?
Where the value of one attribute is determined by another attribute that is not part of a key.
What is a good way of summarising 3NF?
All attributes are dependent on the key, the whole key and nothing but the key.
What are some of the advantages to normalisation?
- Has no data redundancy (data that appears more than once). This could cause data inconsistencies.
- Maintenance is easier.
- Produces smaller tables with fewer fields, so reduces storage space and makes searching/sorting much quicker.
- Accidental deletions are prevented as a ‘one’ side of a ‘one to many’ relationship cant be deleted.
