Fundamentals of Database systems

Chapter: four
Logical Database Design
 Outlines
 Logical Database Design
 Normalization
 Functional Dependencies
 Steps of Normalization

2
 Logical Database Design
 The process of constructing a model of the information
used in an enterprise based on a specific data model
Converting ER Diagram to Relational Tables
 Three basic rules to convert ER into tables or
relations:
 For a relationship with One-to-One Cardinality
All the attributes are merged into a single table.

3
 Cont.…
 For a relationship with One-to-Many Cardinality:
– Post the primary key or candidate key from the
“one” side as a foreign key attribute to the “many”
side.

 For a relationship with Many-to-Many Cardinality

– Create a new table (which is the associative entity)
and post primary key or candidate key from each
entity as attributes in the new table along with
some additional attributes 4
 Normalization
 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
update anomalies.
 Update anomalies are the type of problems that could
occur in insufficiently normalized table
 The purpose of normalization is to reduce the
chances for anomalies to occur in a database.
5
 Update Anomalies

 An insertion anomaly 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.

 A deletion anomaly is a failure to remove information about an

existing database entry when it is time to remove that entry.

 A modification anomaly is a failure to change information

about an existing database entry in all places in the database

6
 Cont.…
Example of problems related with Anomalies
 Deletion Anomalies: If employee with ID 16 is deleted
– we will not have any information about C++ and its skill type.
 Insertion Anomalies: What if we have a new employee with a skill called Pascal?
– Pascal should be categorized as what??
 Modification Anomalies: What if the address for Helico is changed from Piazza
to Mexico? Modify in all locations
– prone to errors

7
 Functional Dependencies
 We say an attribute B has a functional dependency on
another attribute A if for any two records, which have the
same value for A, then the values for B in these two records
must be the same.
 We illustrate this as:
AB
 B is functionally dependent on A.
 A determines B, or B is a function of A
 A is Determinant

8
 Cont.…
 Example

Since both Wine type and Fork type are determined by the Dinner
type, we say
Wine is functionally dependent on Dinner and Fork is functionally
dependent on Dinner.

Dinner Wine
Dinner Fork
9
 Cont.…
Partial Dependency
 Let {A,B} is the Primary Key and C is non key attribute.
Then if {A,B}C and BC or AC
Then C is partially functionally dependent on {A,B}
 Example: StudentID ProjectNo StudentName ProjectName
S01 P01 Abebe SMIS
S02 P02 Mamo Geo Location

 StudentName can be determined by StudentID which is

partial dependent on pks
 ProjectName can be determined by ProjectNo, which is
partial dependent on pks
10
 Cont.…
Full Dependency
 Let {A,B} is the Primary Key and C is non key attribute
Then if {A,B}C and BC and AC does not hold
Then C Fully functionally dependent on {A,B}
 Example: empID projectID Days(spent on the project)
E099 001 320
E056 002 190

• The subset {empID, projectID} can determine the

{Days} spent on the project by employees
11
 Cont.…
Transitive Dependency
If A functionally governs B, AND
If B functionally governs C
THEN A functionally governs C
 {(AB) AND (BC)} ==> AC provided that
B /A and C /A
 Example: If Mr X is a Human, and if every Human is
an Animal, then Mr X must be an Animal.

12
 Steps of Normalization
First Normal Form (1NF)
 Requires that all column values in a table are atomic
 We have two ways of achieving this:
1. Putting each repeating group into a separate table and
connecting them with a primary key-foreign key
relationship
2. Moving these repeating groups to a new row by
repeating the common attributes. If so then Find the key
with which you can find all data
13
 Cont.…
 A table (relation) is in 1NF If
 There are no duplicated rows in the table. Unique
identifier
 Each cell is single-valued (i.e., there are no repeating
groups).
 Entries in a column (attribute, field) are of the same
kind.

14
Example: Unnormalized

FIRST NORMAL FORM (1NF)

15
 Cont.…
Second Normal form 2NF
 A table (relation) is in 2NF If
It is in 1NF and
 If all non-key attributes are dependent on the entire
primary key(i.e. no partial dependency)
 Example for 2NF:

16
 Cont.…
 It is in 1NF, there is no any repeating groups or attributes with
multi-valued property.

 To convert it to a 2NF we need to remove all partial

dependencies of non key attributes on part of the primary key.
{EmpID, ProjNo}EmpName, ProjName, ProjLoc, ProjFund, ProjMangID, Incentive

 But in addition to this we have the following dependencies

FD1: {EmpID}EmpName
FD2: {ProjNo}ProjName, ProjLoc, ProjFund, ProjMangID
FD3: {EmpID, ProjNo}Incentive
17
 Cont.…
 Some non key attributes are partially dependent on some
part of the primary key(FD1 and FD2)

 Each Functional Dependencies, with their dependent

attributes should be moved to a new relation where the
Determinant will be the Primary Key for each.

18
 Cont.…
Third Normal Form (3NF)
 Eliminate Columns Dependent on another non-Primary Key
 This level avoids update and delete anomalies.

 A Table (Relation) is in 3NF If

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

19
 Cont.…
 Example : Assumption: Students of same batch (same year) live in one
building or dormitory

 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 cannot determine StudID and
 Dormitary cannot determine StudID
 Then transitively StudIDDormitary
20
 Cont.…
 To convert it to a 3NF we need to remove all transitive
dependencies of non key attributes on another non-key
attribute.
 The non-primary key attributes, dependent on each other will
be moved to another table and linked with the main table using
Candidate Key- Foreign Key relationship.

21