4.10 Fundamentals of DB (Normalisation)

Question 1

What is Normalisation

Answer 1

The process which helps to organise and structure a database in a way which improves efficiency and integrity

Question 2

What advantages does Data Normalisation bring

Answer 2

• No redundancy, making size smaller and so more money efficient
• One change cascades across related record making modification easier
• Less data to search through makes the process of the query faster
• Improves Data Integrity

Question 3

What are the disadvantages of normalisation

Answer 3

• If the data is mad too atomic, fields may become unmeaningful
• More tables are made than in 0NF
• A more complex database is made meaning that querying data is also more complex and potentially slower

Question 4

What is meant by a dependency

Answer 4

A value that varies in line with another value

Question 5

What are the conditions for 0NF

Answer 5

• Non-Atomic data
• Repeating groups of data

Question 6

What are the conditions for 1NF

Answer 6

• No repeating geoups of attributes (2 of the same attribute)
• Data is atomic
• Each record must have a primary key

Question 7

Process of 0NF –> 1NF

Answer 7

• Separate repeating attributes into separate records.
• Separate non-atomic values into separate attributes
• Declare a primary key

Question 8

What is meant by a partial key dependency

Answer 8

Where the value of a data field relies entirely on part of the primary key where the primary key is a composite key

Question 9

What are the conditions for 2NF

Answer 9

• Must already be in 1st Normal Form
• The must be no Partial Key dependencies

Question 10

How do you identify and fix partial key dependencies for n partial dependencies

Answer 10

• First ask for each non-key attribute “if the primary key changes does the attribute value change
• Make n new tables where for each new table the primary key is the part of the primary key that one of the field relied on.

Question 11

What is meant by a non-key dependency

Answer 11

Where an attribute is determined by another attribute in a table that is not the primary key

Question 12

What are the conditions for 3NF
a

Answer 12

• Must already be in second normal form
• Must have no non-key dependencies

Question 13

How do you find non-key dependencies
a

Answer 13

Ask yourself:
“Is it ever possible for two records to have the same value in this field?”.

Question 14

How do you fix non-key dependencies for n non-key dependencies

Answer 14

• Make n new tables, and for each one you should contain the non key dependency attributes