Programming Assignment Unit 3
a) Define normalization in the context of database management
systems (DBMS). Explain why normalization is essential for
database design.
Normalization is the process of organizing data in a database to reduce redundancy and improve
data integrity. In database management systems (DBMS), normalization involves dividing large
tables into smaller, related tables and ensuring that relationships between the tables are
properly established. This process helps minimize the chances of data anomalies such as
insertion, update, and deletion anomalies that can arise when redundant data is present.
Normalization is essential for database design because it ensures that the data is stored in an
efficient, logical manner. By reducing redundancy, it also decreases storage requirements and
improves query performance. Furthermore, normalization helps maintain consistency by
ensuring that each piece of data is stored only once, preventing data discrepancies and
improving overall data integrity.
b) Normalize this relation into First Normal Form (1NF), Second
Normal Form (2NF), Third Normal Form (3NF) and Boyce-Codd
Normal Form (BCNF).
Given the unnormalized relation representing a library database:
Books (Book_ID, Title, Author, Genre, Publisher, Publication_Year, ISBN, Price)
1. First Normal Form (1NF)
A relation is in First Normal Form (1NF) if it contains only atomic (indivisible) values. In the given
relation, we assume that each attribute has a single value (atomic) and that there are no
repeating groups.
The relation is already in 1NF since each column contains only atomic values. However, we will
still list the following attributes:
Books (Book_ID, Title, Author, Genre, Publisher, Publication_Year, ISBN, Price)
2. Second Normal Form (2NF)
A relation is in Second Normal Form (2NF) if it is in 1NF and all non-key attributes are fully
dependent on the primary key. To achieve 2NF, we need to ensure that partial dependencies
(i.e., when a non-key attribute depends only on part of a composite primary key) are removed.
In the current relation, Book_ID is the primary key, and all attributes depend fully on it. There
are no partial dependencies, so the relation is already in 2NF.
The relation remains:
Books (Book_ID, Title, Author, Genre, Publisher, Publication_Year, ISBN, Price)
3. Third Normal Form (3NF)
A relation is in Third Normal Form (3NF) if it is in 2NF and no transitive dependencies exist,
meaning that non-key attributes are not dependent on other non-key attributes.
In the original relation, we can see that Publisher and Genre are dependent on the Book_ID, but
there could be a transitive dependency where Genre determines Publisher. To remove this
dependency, we would separate the Publisher and Genre into different tables and link them to
the main Books table via foreign keys.
After applying 3NF, we get the following tables:
Books (Book_ID, Title, Author, Genre, Publication_Year, ISBN, Price)
Genres (Genre_ID, Genre, Publisher)
Publishers (Publisher_ID, Publisher)
4. Boyce-Codd Normal Form (BCNF)
A relation is in Boyce-Codd Normal Form (BCNF) if it is in 3NF and for every functional
dependency (X → Y), X is a superkey. In the relation after 3NF, we may still have a situation
where Genre determines Publisher, but Genre is not a superkey. Therefore, to achieve BCNF, we
need to separate the tables further so that each determinant is a superkey.
The resulting BCNF relations are:
Books (Book_ID, Title, Author, Genre_ID, Publication_Year, ISBN, Price)
Genres (Genre_ID, Genre)
Publishers (Publisher_ID, Publisher)
c) For each normalization step (1NF, 2NF, 3NF, BCNF) performed in
Part (b), explain the specific functional dependencies present in the
original relation and how they are addressed through
normalization.
1. **1NF**: In the original relation, we assumed all attributes are atomic. There are no
repeating groups or multi-valued attributes. Hence, no functional dependencies need to be
addressed for 1NF.
2. **2NF**: Since the primary key is Book_ID and all non-key attributes depend fully on it, no
partial dependencies exist. Therefore, the relation is already in 2NF, and no changes are
required.
3. **3NF**: In the original relation, Genre determines Publisher, creating a transitive
dependency. We address this by separating Publisher and Genre into their own tables, with
Genre_ID and Publisher_ID as foreign keys in the Books table.
4. **BCNF**: In 3NF, we still have the functional dependency Genre → Publisher, where Genre
is not a superkey. To address this, we further decompose the relation to ensure that each
determinant is a superkey, leading to the final BCNF structure.
d) Discuss the advantages and potential drawbacks of achieving higher
normal forms (3NF and BCNF) compared to lower normal forms (1NF and
2NF) in terms of database design, querying efficiency, and data integrity.
Advantages of Higher Normal Forms (3NF and BCNF):
- **Data Integrity**: Higher normal forms reduce data redundancy and prevent anomalies
during insertion, deletion, or updates.- **Storage Efficiency**: Less duplication of data, resulting
in more efficient use of storage space.- **Consistency**: Ensures that each piece of data is
stored in one place, reducing the risk of data inconsistency.
Potential Drawbacks of Higher Normal Forms (3NF and BCNF):
- **Complex Queries**: The normalization process can lead to more complex queries with
multiple joins.- **Performance Issues**: While normalization reduces redundancy, it might slow
down read operations due to the need for joins between tables.- **Development Time**:
Achieving higher normal forms may require more time to design and implement the schema.