UNIT FOUR

Logical Database Design

✓ Database normalization is a series of steps followed to obtain a database design that allows for

consistent storage and efficient access of data in a relational database.

✓ These steps reduce data redundancy and the risk of data becoming inconsistent.

✓ It is the process of identifying the logical associations between data items and designing a database that will

represent such associations but without suffering the anomalies.

1. Insertion Anomalies

2. Deletion Anomalies

3. Modification/ Updating Anomalies

 Cont…

✓ Normalization may reduce system performance since data will be cross referenced from many tables.

✓ Thus, denormalization is sometimes used to improve performance.

Insertion anomalies

✓ Is a failure to place information about a new database entry into all the places in the database where

information about that new entry needs to be stored.

✓ Information about a new entry may need to be inserted into more than one place and, human fallibility.
 Cont…

Deletion anomalies

✓ A "deletion anomaly" is a failure to remove information about an existing database entry when it is
time to remove that entry.

✓ information about that old entry may need to be deleted from more than one place, and, human
fallibility being what it is, some of the needed additional deletions may be missed.
Modification/Updating anomalies
✓ A modification of a database involves changing some value of the attribute of a table.
 Cont…

EmpID FName LName SkillID Skill SkillType School SchoolAdd Skill

Level

12 Abebe Mekuria 2 SQL Database AAU Sidist_Kilo 5

16 Lemma Alemu 5 C++ Programming Unity Gerji 6

28 Chane Kebede 2 SQL Database AAU Sidist_Kilo 10

25 Abera Taye 6 VB6 Programming Helico Piazza 8

65 Almaz Belay 2 SQL Database Helico Piazza 9

24 Dereje Tamiru 8 Oracle Database Unity Gerji 5

51 Selam Belay 4 Prolog Programming Jimma Jimma City 8

94 Alem Kebede 3 Cisco Networking AAU Sidist_Kilo 7

18 Girma Dereje 1 IP Programming Jimma Jimma City 4

13 Yared Gizaw 7 Java Programming AAU Sidist_Kilo 6

 Cont…

Insertion Anomalies:
What if we have a new employee with a skill called Pascal? We can not decide weather Pascal is allowed as a
value for skill and we have no clue about the type of skill that Pascal should be categorized as. In addition, we are
required to record/insert values for the primary key column (i.e. we can’t insert value for skill column without
inserting values for EMPID column of employee).
Deletion Anomalies:
If employee with EMPID 16 is deleted then ever information about skill C++ and the type of skill is deleted from
the database. Then we will not have any information about C++ and its skill type.
Modification Anomalies:
What if the address for Helico is changed from Piazza to Mexico? We need to look for every occurrence of Helico and change
the value of SchoolAdd from Piazza to Mexico, which is prone to error.
 Cont…
Functional Dependency (FD)
✓ Functional Dependencies (FDs) will decide whether a database design is correct or not.
✓ Depicts the relationship between attributes.

Example
Dinner Type of Wine
Meat Red
Fish White
Cheese Rose

Since the type of Wine served depends on the type of Dinner, we say Wine is functionally dependent on Dinner. And this
can be expressed as:
Dinner → Wine
Dinner Type of Wine Type of Fork
Meat Red Meat fork

Fish White Fish fork

Dinner → Wine and Dinner → Fork Cheese Rose Cheese fork
 Cont…

Partial Dependency

If we have composite primary key and if an attribute which is not a member of all the primary key (i.e.

is dependent on some part of the primary key then that attribute is partially functionally dependent on

the primary key.

Let {A, B} is the Composite Primary Key and C is non key attribute.

Then if it should be {A, B}→C but B→C or A→C , Then C is partially functionally dependent on {A, B}.
 Cont…

Full Dependency

If an attribute which is not a member of the primary key is not dependent on some part of the primary

key but the whole key (if we have composite primary key) then that attribute is fully functionally

dependent on the primary key.

Let {A, B} is the Composite Primary Key and C is non key attribute.

Then if {A, B}→C and B→C and A→C , Then C Fully functionally dependent on {A, B}.
 Cont…

Transitive Dependency
In mathematics and logic, a transitive relationship is a relationship of the following form: "If A implies B, and if
also B implies C, then A implies C."
Example:
If Mr. X is a Human, and if every Human is an Animal, then Mr. X must be an Animal.

Generalized way of describing transitive dependency is that:

If A functionally governs B, AND
If B functionally governs C
THEN A functionally governs C.
Provided that neither C nor B determines A i.e. (B /→ A and C /→ A)

In the normal notation:

{(A→B) AND (B→C)} ==> A→C provided that B /→ A and C /→ A
 Process of normalization (1NF, 2NF, 3NF)

First Normal Form (1NF)

✓ Requires that all column values in a table are atomic (e.g., a number is an atomic value, while a list or

a set is not).

✓ It states that an attribute of a table cannot hold multiple values. It must hold only single-valued attribute.

✓ First normal form disallows the multi-valued attribute, composite attribute, and their combinations.

✓ There are no duplicated rows in the table.

✓ Each cell is single-valued (i.e., there are no repeating groups).

✓ Entries in a column (attribute, field) are of the same kind.

 Conti…

Example for First Normal Form (1NF): Consider the following unnormalized database table.
EmpID FirstName LastName Skill SkillType School SchoolAdd SkillLevel

12 Abebe Mekuria SQL, Database, AAU, Sidist_Kilo 5

VB6 Programming Helico Piazza 8
16 Lemma Alemu C++ Programming Unity Gerji 6
IP Programming Jimma Jimma City 4
28 Chane Kebede SQL Database AAU Sidist_Kilo 10
65 Almaz Belay SQL Database Helico Piazza 9
Prolog Programming Jimma Jimma City 8
Java Programming AAU Sidist_Kilo 6

24 Dereje Tamiru Oracle Database Unity Gerji 5

94 Alem Kebede Cisco Networking AAU Sidist_Kilo 7

 Conti…

First normal form (1NF)

✓ Remove all repeating groups.
✓ A relation will be 1NF if it contains an atomic value.
✓ Moving this repeating groups to a new row by repeating the common attributes. If so then find the key with
which you can find all data.
EmpID FirstName LastName SkillID Skill SkillType School SchoolAdd SkillLevel
12 Abebe Mekuria 1 SQL Database AAU Sidist_Kilo 5
12 Abebe Mekuria 3 VB6 Programming Helico Piasa 8
16 Lemma Alemu 2 C++ Programming Unity Gerji 6
16 Lemma Alemu 7 IP Programming Jimma Jimma City 4
28 Chane Kebede 1 SQL Database AAU Sidist_Kilo 10
65 Almaz Belay 1 SQL Database Helico Piasa 9
65 Almaz Belay 5 Prolog Programming Jimma Jimma City 8
65 Almaz Belay 8 Java Programming AAU Sidist_Kilo 6
24 Dereje Tamiru 4 Oracle Database Unity Gerji 5
94 Alem Kebede 6 Cisco Networking AAU Sidist_Kilo 7
 Conti…

Second Normal form (2NF)

o It is in 1NF and
o If all non-key attributes are dependent on the entire primary key i.e. if the primary key is composite key.
➢ No partial dependency.

✓ In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset
of a candidate key. That's why it violates the rule for 2NF.
✓ To convert the given table into 2NF, we decompose it into two tables:
Conti…
 Conti…

Third Normal form (3NF)

o It is in 2NF and
o There are no transitive dependencies between a primary key and non-primary key attributes.

StudID Stud_F_Name Stud_L_Name Dep’t Year Dormitary

125/97 Abebe Mekuria Info Sc 1 401

654/95 Lemma Alemu Geog 3 403

842/95 Chane Kebede CompSc 3 403

165/97 Alem Kebede InfoSc 1 401

985/95 Almaz Belay Geog 3 403

This schema is in its 2NF since the primary key is a single attribute.
Let’s take StudID, Year and Dormitary and see the dependencies.

StudID→Year AND Year→Dormitary

And Year can not determine StudID and Dormitary cannot determine StudID. Then transitively StudID→Dormitary.
 Conti…

Student Dorm
StudID Stud F_Name Stud L_Name Dep’t Year Year Dormitary
125/97 Abebe Mekuria InfoSc 1 1 401

654/95 Lemma Alemu Geog 3 3 403

842/95 Chane Kebede CompSc 3

165/97 Alem Kebede InfoSc 1

985/95 Almaz Belay Geog 3

Normalization up to 3NF could be the following:

1. No repeating or redundancy: No repeating fields in the table.
2. The fields depend upon the key: The table should solely depend on the key.
3. The whole key: No partial dependency. (No dependency on part of primary key).
4. And nothing but the key: No transitive dependency.
 Conti…

Advantages of Normalization

✓ Normalization helps to minimize data redundancy.

✓ Greater overall database organization.

✓ Data consistency within the database.

✓ Much more flexible database design.

Disadvantages of Normalization

✓ You cannot start building the database before knowing what the user needs.

✓ The performance degrades when normalizing the relations to higher normal forms, i.e., 4NF, 5NF.

✓ It is very time-consuming and difficult to normalize relations of a higher degree.

✓ Careless decomposition may lead to a bad database design, leading to serious problems.