normalizations

• New lesson editor

Database Normalization

Dr. Firas Hammoodi

contents

• Normalization

• Data Redundancy

• Functional Dependencies

• Normalization Process

Normalization aims to identify a suitable set of relations that support the data requirements of an enterprise.

suitable set:

- Each table represents a single subject.

- There are no data items stored unnecessarily in many tables

- Every table has no anomalies related to inserts, updates, or deletions anomalies.

Relational database design aims to reduce the opportunities for data inconsistencies, minimize storage space, and minimize costs.

Data Redundancy

B008

Insert

Delete

Update

StaffBranch

Functional Dependencies

staffNo → sName, position, salary, branchNo.

A staff member's staffNo uniquely determines their sName, position, salary, and branchNo.

branchNo → bAddress.

Each branch is uniquely identified by its branchNo, which determines its bAddress.

• Functional dependencies describe relationships between attributes in a relational schema

Partial dependency

• A partial dependency exists when a non-prime attribute (not part of the candidate key) depends on a part of a composite primary key, not the entire one.

• Let's assume a composite primary key: {staffNo, branchNo}

branchNo → bAddress.

staffNo → sName, position, salary.

Partial Dependency

Transitive Dependency

• A transitive dependency exists when a non-prime attribute depends on another non-prime attribute, which depends on the primary key.
  Formally,

if A → B and B → C, then A → C.

• Assuming staffNo is the primary key:

staffNo → branchNo

branchNo → bAddress

staffNo → bAddress

transitive dependency

Normalization Process

• Normalization is a technique for analyzing relations based on their primary key (or candidate keys) and functional dependencies

• Three normal forms were initially proposed:

First Normal Form (1NF),

Second Normal Form (2NF),

The third Normal Form (3NF), Boyce Codd Normal Form (BCNF)

• Higher normal forms such as :

Fourth Normal Form (4NF),

Fifth Normal Form (5NF),

Sixth Normal Form (6NF).

Normalization Process

ClientRental

• A table contains one or more repeating groups. Remove its.

First Normal Form FN1

• Each row is unique

{clientNo, propertyNo, ownerNo}.

Normalization Process

Composite Primary Key:

Second Normal Form (2NF)

The composite key clientNo and propertyNo.

Partial Dependencies:

clientNo → cName

propertyNo → pAddress, rent, ownerNo, oName

{clientNo, propertyNo} → rentStart, rentFinish

Client Table

PropertyOwner Table

Rental Table

Normalization Process

Third Normal Form (3NF)

Client

clientNo → cName (Primary key)

Rental

clientNo, propertyNo → rentStart, rentFinish (Primary key)

clientNo, rentStart → propertyNo, rentFinish (Candidate key)

propertyNo, rentstart → clientNo, rentFinish (Candidate key)

PropertyOwner

propertyNo → pAddress, rent, ownerNo, oName (Primary key)

3NF removes transitive dependencies by placing the attribute(s) in a new relation with a copy of the determinant, removing any existing transitive dependency.

ownerNo → oName (Transitive dependency)

Normalization Process

Third Normal Form (3NF)

Owner Table

PropertyForRent Table

Client Table

Rental Table

Normalization Process

ClientRental

Client

Rental

PropertyOwner

PropertyForRent

Owner

1NF A table contains one or more repeating groups. Remove its. And Each row is unique

2NF appears to be a composite key and Partial Dependencies

3NF removes transitive dependencies by placing the attribute(s) in a new relation with a copy of the determinant, removing any existing transitive dependency.

3NF

Thank you