L11 - Normalisation

Question 1

What is normalisation?

Answer 1

• Technique for producing a set of tables with minimal redundancy
  
  • Which supports the data requirements of an org
• Important to reduce duplication
  
  • Less file space taken
  • Decreases update anomalies occurring resulting in data loss
• Normal forms applied at logical design stage
  
  • After ER models have been mapped to initial set of tables

Question 2

What are updat anomalies

Answer 2

• Inconsistencies that arise
  
  • When use attempts to update a table containing redundant data
  • Through insertion, deletion or modification
• Making these changes will break the data integrity

Question 3

HOw do you resolve / prevent update anomalies?

Answer 3

Question 4

What are the normal forms?

Answer 4

• Normalisation requires a series of tests
  
  • Checking if table satisfies or violates the rules of a given normal form
  • 1NF, 2NF, 3NF

Question 5

What is the first step in normalisation?

Answer 5

1NF - First Normal Form

• Most critical only required NF
• Enforces atomic cells
  
  • Where the data comes from the correct domain
• No duplicate rows
  
  • Valid PK needed

Question 6

What is tyhe second step in nomralisation?

Answer 6

2NF - Second Normal Form

• Required only when PK is made up of >1 Column (Composite keys)
• Forces there to be only full functional dependencies
  
  • Where all PK column values determine the value of a non-PK column
  • A→B
• Therefore we must remove all partially dependent columns to a new table and give that a new PK

Question 7

What is the third step in normalisation?

Answer 7

• Ensures the PK uniquely determines all other values in a record
• Transitive functional dependencies = fail 3NF
  
  • A→B but in turn B→C
  • Non-PK column if functionally dependent on another Non-PK Column
• So we create a new table where those attributes rely on a new PK exclusively