Data Modeling and Normalization

Question 1

Why do we normalize our data?

Answer 1

We use normalization to formally validate our relational model, it helps us reduce redundancy and thereby anomalies in our data. Anomalies can be of either the insertion type, deletion type or modification type. Either one can be avoided by normalizing our relational model.

Question 2

What are the different degrees of normalization?

Answer 2

• 0th normal form which is unstructured data of any kind
• 1st normal form where all values are atomic, rows are uniquely identifiable and all values in one column are of the same type
• 2nd normal form is only relevant when we work with a compound/composite key. It is 1st normal form where all attributes(columns) in a relation(table) are functionally dependent on the whole primary key.
• 3rd normal form is second normal form, but without any transitive dependencies, meaning all non-key values depend directly on the primary key.
• 3.5 normal form or Boyce Codd Normal Form is 3rd normal form where there are no functional dependencies on anything that isn’t a candidate key. “The data must depend on the key, the whole key and nothing but the key So help me Codd”

The rest we don’t do.

Question 3

What level do we strive to reach ?

Answer 3

We strive to reach the 3rd normal form, and afterwards we often denormalize, to make it fit the specific needs and logic of the domain in which we are working.

Question 4

What are the different ways to model inheritance in a database?

Answer 4

There are 3 ways of doing generalization:
1. Each class is mapped to a table, where both superclasses and subclasses are given tables.

2. “Pull-down” - Each subclass is mapped to a table, with the superclasses attributes in each.
3. “Pull-up” - All attributes of the different subclasses are pulled up into the superclass and only one table is made

Question 5

How do we model 1-to-1, 1-to-many and many-to-many relations using a database?

Answer 5

1-to-1 one of the relations contains the primary key of the other relation as a foreign key

1-to-many the one-side of the association is referenced as a foreign key in the many-side of the association

many-to-many a join-table is created with the primary keys of both relations as foreign keys, the foreign keys become a composite primary key in the join-table