Normalization

Question 1

When designing a database, at which step do you look at normalization?

Answer 1

In the logical step as this is where you pay more attention to the attributes

Question 2

What is normalization aiming to minimize

Answer 2

Update and deletion inefficiencies and anomalies.

Removing data redundancy

Question 3

Data normalization is a progressive process; what are the steps?

Answer 3

1. The steps are called normal forms.
2. Each step increases the efficiency of the database.
3. To be in the third normal form the database has to conform to 1st and 2nd normal form.

Question 4

What is the overall goal of normalization?

Answer 4

To find the minimum number of attributes necessary for a relation that still satisfies the requirements.

Group logical attributes in relations (tables),

Minimize update, delete anomalies

Question 5

what is the main purpose of normalization

Answer 5

minimize the redundancy and remove Insert, Update and Delete Anomaly.

It divides larger tables to smaller tables and links them using relationships.

Question 6

What is insertion anomalies?

Answer 6

when data is inserted wrong, for example, more information is added to a column than there should be.

Question 7

What is a deletion anomaly?

Answer 7

deletion anomalies occur whenever deleting a row inadvertently causes other data to be deleted

Question 8

when does update anomalies occur?

Answer 8

when updating data has to happen in multiple rows to change one single fact

Question 9

What is a functional dependency?

Answer 9

When an attribute (B) is dependent on another attribute (A) - then attribute A can identify B.

e.g. attribute A (student_id) -> B (student_name).
Note: if you have B then you can’t be certain about A.

In words: student_name is functional dependent on studfent_id.

Question 10

How can you identify functional dependencies?

Answer 10

Look at which attributes can be used to uniquely identify a row.

Question 11

What is a candidate key

Answer 11

A candidate key is an attribute that can be used as a primary key, but is not - it can uniquely identify the row.

Question 12

What is a partial dependency?

Answer 12

A partial dependency is when you have an attribute that is functionally dependent on a candidate key

Question 13

What are the requirements of 1st NF? (Normal form)

Answer 13

1. Values in the columns must be atomic

2. No multi-valued or composite attributed

Question 14

What is the 2nd Normal Form?

Answer 14

When all non-primary keys are fully dependent on the primary key.

This means that you should remove partial dependencies. (attributes dependent on a candidate attribute)

Question 15

How can you remove partial dependencies?

Answer 15

You take the dependent attribute and the candidate key it is dependent on and moves it into a new table.

Question 16

What is a transitive dependency?

Answer 16

When you have a relation with the attributes A, B and C.

And C is dependent on B that is dependent on A. Then C is transitively dependent on A

Question 17

What is 3NF?

Answer 17

A table is in 3rd NF when all attributes are mutually independent but at the same time are fully dependent on the primary key.

Question 18

What is the disadvantage of normalizing the database a lot?

Answer 18

The more normalized data is, the more complex the queries need to be to retrieve data.

Inner joins are often needed and they are computationally expensive

Question 19

What is denormalisation?

Answer 19

reversing normal forms to optimize the queries for example.

Question 20

When do you want a denormalised database?

Answer 20

When you need to do reads often. Its used a lot in data warehouses or reporting tables.

Question 21

Are there any other ways to denormalise other than just reversing normal forms?

Answer 21

Yes, several;
separate active and inactive data.
separate heavily and lightly accessed fields.
making pre-joined tables