12CSC Database Normalisation

What characteristic does NOT mean that data is in 1NF?

• It is all in one table. 
• There is redundancy. 
• There is an assigned primary key. 
• There are duplicate names. 

What does Normalisation allow a database designer to do?

• Design their database efficiently, removing the chance of redundancy and inaccuracies. 
• Remove additional records from the tables. 
• Add as much data as they like. 
• Draw an ERD. 

To be in 1NF: 

• Each field contains the smallest meaningful value
• There are multiple values in the single field
• There are no duplicate records
• A primary key is assigned

To be in 2NF: 

• Each non-key field relates to the primary key
• There are multiple entries in the same field
• There are no foreign keys
• All numbers are in decimal form

To be in 2NF:

• Any field not relating to the PK is placed in a separate table
• The records all relate easily
• Each table has a foreign key
• There are no redundancies

To be in 3NF:

• Records do not depend on anything other than the table's PK
• The PK is not added
• The FKs are limited to one only
• The tables all relate to one another

Tables in 2NF but not 3NF contain...

• Insert, update and delete anomalies
• No extra FKs
• No redundancy
• Access to more data 

Advantage of 3NF?

• It eliminates redundant data which in turn saves space and reduces manipulation anomalies. 
• It gives the data status. 
• It allows the tables to be queried in SQL. 
• It prevents any new updates. 

Which one of these do you want to avoid in good database design?

• Redundancy!
• Giant squid!
• Third normal form!
• Data!

A foreign key

• Links to a primary key in another table. 
• Links to another foreign key. 
• Links to another database system. 
• Should always reference a unique value in the same table.