Lecture IV: Normalization

Question 1

How is the act of normalization often done?

Answer 1

The act of normalization is often done through a series of tests on one ore more tables. This is to determine if they comply or violate the rules for a given normal form.

Question 2

What are the two ways to perform normalization?

Answer 2

There are two ways to perform normalization: the first is a bottom-up database design methodology while the second is a top-down development process.

Question 3

How does the bottom-up database design metodology work?

Answer 3

(Man skippar conceptual design(erd osv) och går direkt till logical design)

In the bottom-up approach, we analyze the relationships between attributes to create tables that represent entities and their relationships.

• In other words, we design the database at the table level. There is no ER-diagram design phase.

Normalization is then used to “clean up” poorly structured tables.

Question 4

How does the top-down database design metodology work?

Answer 4

In top-down, ER modeling is used to create a model that represents, entities, relationships and attributes..

• The ER model is then translated into tables. When this is done, we normalize these tables to see if the tables are designed correctly.

Question 5

What is one thing normalization reduce the risk of?

Answer 5

Normalization reduces the risk of anomalies

Question 6

What is the aim of normalization?

Answer 6

The aim of normalization is to free the database from the aforementioned anomalies.

Question 7

What does normalization ensure?

Answer 7

Normalization ensures that when a new value is introduced in a relationship, it has minimal effect on the database and therefore also minimal effect on the program that uses the database.

Question 8

How does First Normal Form (1NF) work?

Answer 8

First Normal Form (1NF)

• Atomic columns
• A table in which the interesection of every column and record contains only one value.
• It is the only normal form that is critical for creating relational database tables, but to avoid update anomalies it is recommended to proceed through to 3NF.
• A column that contains more than one entry per record must be removed to create a new table where the duplicate valuesa re flattened to become new primary keys.
• 1NF also require that the table has a primary key.

Question 9

what is a foreign key?

Answer 9

A foreign key is a column or columns that are used to create a link between two tables.

Question 10

What is the table that contains the foreign key called?

And what is the table that contains the condidate key called?

Answer 10

The table that contains the foreign key is called the referencing table and the table that contains the candidate key is called the referenced table.

Question 11

Why is it generally requied that the foreign key be the same as a candidate key in any row in the referenced table?

Answer 11

Since the purpose of a foreign key is to identify a particular row in the referenced table, it is generally required that the foreign key be the same as a candidate key in any row in the referenced table.

Question 12

How does Second Normal Form(2NF) work?

Answer 12

Second Normal Form (2NF) applies to tables with composite primary keys, I.e a primary key composed of two or more columns.

Question 13

What does Functional Dependency describe?

Answer 13

Functional Dependency describes the relationship between columns in a table and indicates how columns relate to one another.

Question 14

Consider a table with columns a and b, where column a determines column b. (which is written as a -> b).

What does that mean?

Answer 14

That means that fi we know the value for a, the value of “b” will always be the same for every record where “a” has this particular value.

However, even if we know the value of “b”, “a” can have several different values.

Question 15

What are some consequences of Partial dependency?

Answer 15

That consequence of partial dependance is that it creates problems. More specifically redudant data. In our case in the columns name and position which can lead to update anomalies.

• For example, to rename ‘Ellen Layman’, we need to update two rows in our table.
• If we only update one row, the databse will be inconsistent.

Question 16

What is the formal definition of Second Normal Form (2NF)?

Answer 16

The formal definition of 2NF is a table that is in 1NF, and every column, that is not a part of the primary key, is fully functionally dependant on the primary key.

Question 17

What is Full Functional Dependency?

Answer 17

If “a” and “b” are columns in a table, “b” is fully determined by “a” if “b” is not determined by a subset of “a”.

Question 18

What is Partial Dependency?

Answer 18

If “a” and “b” are columns in a table and -

If “b” is determined by a subset of “a”, this is referred to as a partial dependency.

Question 19

How do you know if a table is in Third Normal Form (3NF)?

Answer 19

A table is in 3NF if no column is transitively dependent on the primary key.

Question 20

What does Transitive dependency describe? and how does it look like?

Answer 20

• Describes a relationship between columns “a”, “b” and “c”.
• If “a” determines “b” (a->b) and “b” determines c (b->c), then “c” is transitively dependant on “a” via “b” (provided that “b” or “c” does not determine “a”)