Week 6 - Data Normalisation

Question 1

What is Normalization

Answer 1

A methodology to enforce data integrity by construction

Question 2

Why use normalization

Answer 2

Helps to:
- Preserve functional dependencies
- Avoid data redundancy and insertion/deletion anomalies
- Reduce the need for restructuring the schema
- Increase the life span of programs

Question 3

What is an insertion anomaly in database design?

Answer 3

An insertion anomaly occurs when it is difficult to add data to a database due to the presence of redundant or incomplete information. For example, adding a new entity may require additional unrelated data to be inserted.

Question 4

Can you give a practical example of an insertion anomaly?

Answer 4

In a database where customer orders and products are stored together, if a new customer is added who has not yet made an order, you would still need to insert a dummy order just to store the customer’s information, causing redundancy.

Question 5

What is a deletion anomaly in database design?

Answer 5

A deletion anomaly occurs when removing data from a database causes unintended loss of other important data. For example, deleting a record could inadvertently remove details about other entities that are tied to it.

Question 6

Can you give a practical example of a deletion anomaly?

Answer 6

In a database where employee and department information is stored together, if an employee is deleted, all the department information associated with that employee might also be removed, even if other employees in the department still exist.

Question 7

What is a superkey?

Answer 7

A set of one or more columns that uniquely identify each row in your table. It should be functional (meaning if you know the values in the superkey columns, you can determine exactly one set of values for all the other columns)

Question 8

What is a candidate key?

Answer 8

A minimal superkey. It is the smallest possible set of columns that can uniquely identify each row in the relation. If you remove any column, it stops being a superkey.

Question 9

Who was Edgar Frank “Ted” Codd

Answer 9

• British computer scientist
• Father of relational model for databases
• Turing award 1981

Question 10

What does 1NF ensure?

Answer 10

That data is organized in a way that avoids hierarchical structures (e.g. tables inside tables)

Question 11

A table is in 1NF if?

Answer 11

Each field in a table should contain a single, non-divisible value, and the table must represent a valid relationship between sets of values, containing no duplicates rows.

Question 12

What are partial dependencies?

Answer 12

When a non prime attribute depends on only a part of a composite candidate key.

Question 13

What is a prime attribute?

Answer 13

An attribute that is part of a candidate key

Question 14

What is a non prime attribute?

Answer 14

An attribute that is not part of any candidate key

Question 15

A table is in 2NF if?

Answer 15

• It is in 1NF
• No functional dependencies (where a non-prime attribute depends on only part of a composite key)

Question 16

What does 2NF ensure?

Answer 16

That non-prime atributes depend only on the entire candidate key, not just part of it.

Question 17

A table is in 3NF if?

Answer 17

• It is in 2NF
• It has no transitive dependencies

Question 18

How dou you eliminate Transitive Dependencies

Answer 18

By ensuring every non-prime attribute is functionally dependent only on a candidate key.
By ensuring functional dependencies satisfy one of the following:
- The determinant (left-hand side of the dependency) is a superkey.
- The dependent attribute (right-hand side) is a prime attribute.