Programming Assignment Unit 3: Database Normalization

Database Normalization of a Library Database

(a) Definition of Normalization and Its Importance

Database normalization is the process of organizing data in a relational database to

minimize redundancy and improve data integrity by decomposing large relations into

smaller, well-structured relations based on functional dependencies (Vidhya et al.,

2016). The primary objective of normalization is to ensure that each fact is stored

only once, thereby reducing duplicate data and preventing inconsistencies.

Normalization is essential in database design because it eliminates insertion, update,

and deletion anomalies. Insertion anomalies occur when new information cannot be

added without unnecessary data. Update anomalies arise when the same information

must be modified in multiple places, increasing the risk of inconsistency. Deletion

anomalies occur when removing one record unintentionally deletes other valuable

information. By applying normalization rules, database designers create databases that

are easier to maintain, more reliable, and capable of preserving data accuracy

throughout their lifecycle (Vidhya et al., 2016).

The given unnormalized relation is:

Books (Book_ID, Title, Author, Genre, Publisher, Publication_Year, ISBN,

Price)

(b) Normalization Process

A relation is in First Normal Form (1NF) when every attribute contains only atomic

(indivisible) values and there are no repeating groups.

The given relation already satisfies 1NF because every attribute stores a single value.

Books

 Book_ID (PK)

 Title

 Author

 Genre

 Publisher

 Publication_Year

 ISBN

 Price

Second Normal Form (2NF)

A relation is in Second Normal Form (2NF) when it is already in 1NF and every non-

key attribute is fully functionally dependent on the entire primary key.

Since Book_ID is a single-attribute primary key, partial dependencies cannot exist.

Therefore, the relation already satisfies 2NF.

 Book_ID (PK)

 Title

 Author

 Genre

 Publisher

 Publication_Year

 ISBN

 Price

Third Normal Form (3NF)

A relation is in Third Normal Form (3NF) when it is in 2NF and contains no transitive

dependencies.

Publisher information can be separated into its own relation to eliminate redundancy.

Books

 Book_ID (PK)

 Title

 Author

 Genre

 Publication_Year

 ISBN

 Price
  Publisher_ID (FK)

Publishers

 Publisher_ID (PK)

 Publisher

This decomposition ensures that publisher information is stored only once and

referenced through a foreign key.

Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF) requires that every determinant in a functional

dependency be a candidate key.

In this relation:

 Book_ID uniquely identifies each book.

 ISBN also uniquely identifies each book because every published book has a

unique ISBN.

Since both determinants are candidate keys, the normalized relations satisfy BCNF.

(c) Functional Dependencies

The following functional dependencies exist in the original relation:

Price

Every Book_ID uniquely identifies all information about a book.

2. ISBN → Book_ID, Title, Author, Genre, Publisher, Publication_Year,

Price

ISBN is also unique and determines all remaining attributes.

3. Publisher_ID → Publisher (after decomposition)

Each Publisher_ID uniquely identifies one publisher.

During normalization, these dependencies are addressed as follows:

 1NF ensures all attribute values are atomic and removes repeating groups.

 2NF removes partial dependencies. Because the primary key consists of only one

attribute (Book_ID), no partial dependency exists.

 3NF removes transitive dependencies by separating publisher information into a

dedicated Publishers table. This prevents redundant storage of publisher names

and eliminates update anomalies.

 BCNF strengthens the design by ensuring every determinant is a candidate key.

Since both Book_ID and ISBN uniquely identify each book, the resulting

relations comply with BCNF.

Higher normal forms such as Third Normal Form (3NF) and Boyce-Codd Normal

Form (BCNF) provide significant benefits for database design. They eliminate

redundant data, reduce storage requirements, and prevent insertion, update, and

deletion anomalies. This improves data integrity and consistency because each piece

of information is stored in only one location. Well-normalized databases are also

easier to maintain, as modifications need to be performed only once, reducing the

likelihood of errors (Vidhya et al., 2016).

However, higher normalization also has disadvantages. Splitting data into multiple

related tables increases the number of joins required when retrieving information.

Complex join operations may reduce query performance, particularly in databases

containing millions of records or handling large transaction volumes. In contrast,

lower normal forms such as 1NF and 2NF generally require fewer joins and may offer

faster query execution. However, they retain more redundant data and are more

vulnerable to data anomalies and inconsistencies.

Therefore, database designers must balance normalization and performance according

to the application's requirements. In most business applications, achieving 3NF or

BCNF provides the best compromise between efficient storage, maintainability, and

reliable data integrity.

Elmasri, R., & Navathe, S. B. (2016). Fundamentals of database systems (7th ed.).

Pearson.

Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database system concepts (7th

ed.). McGraw-Hill Education.

Vidhya, V., Jeyaram, G., & Ishwarya, K. (2016). Database management systems.

Alpha Science International.