|Lesson 6||Normalization and Codd's 12 criteria|
|Objective||Explain how Achieving Third Normal Form adheres to Codd's 12 Criteria.|
Achieving Third Normal Form and Its Compliance with Codd's 12 Criteria
Introduction to Third Normal Form
Third Normal Form (3NF) is a database schema design principle that enhances data integrity and minimizes redundancy. By achieving 3NF, database designers can improve the overall efficiency and organization of a relational database management system. This article will explore how adhering to Third Normal Form aligns with Codd's 12 criteria for relational databases.
Codd's 12 Criteria: A Brief Overview
The 12 criteria proposed by Dr. Edgar F. Codd serve as a guideline for evaluating and ensuring the efficiency of relational database management systems. These criteria focus on aspects such as data integrity, logical data independence, and support for transaction processing. By adhering to these rules, a database management system can deliver optimal performance, scalability, and maintainability.
Third Normal Form and Its Relation to Codd's Criteria
- Information Rule: The Information Rule states that all data in a relational database should be represented as values within tables. Third Normal Form promotes this criterion by eliminating transitive dependencies, ensuring that each non-key attribute is directly dependent on the primary key. This reduces redundancy and guarantees accurate representation of data within tables.
- Guaranteed Access Rule: By adhering to 3NF, a database schema ensures that all data can be accessed using the combination of a table name, primary key, and attribute name. This aligns with the Guaranteed Access Rule, which requires that every data item be uniquely addressable.
- Systematic Treatment of Null Values: 3NF contributes to the systematic treatment of null values by minimizing the chances of null values ppearing in a table. Since 3NF eliminates transitive dependencies and ensures proper organization of attributes, null values are less likely to occur.
- Dynamic Online Catalog Based on the Relational Model: Achieving Third Normal Form aids in creatingdynamic online catalog based on the relational model. By ensuring that the schema is well-structured, database designers can more easily manage metadata and make changes to the schema as needed.
- Comprehensive Data Sublanguage Rule: Third Normal Form, as a part of the relational model, inherently supports a comprehensive data sublanguage. This language includes features for defining, querying, and updating data, enabling efficient management of the database.
- View Updating Rule: The View Updating Rule states that any view that is theoretically updatable should be updatable by the system. Adhering to 3NF simplifies view updating, as it ensures that the underlying base tables have a well-structured schema and are free from anomalies that could complicate updates.
- High-Level Insert, Update, and Delete: By achieving Third Normal Form, a database schema supports high-level insert, update, and delete operations. This is because 3NF eliminates redundancies, reducing the likelihood of update anomalies and ensuring the integrity of the data.
- Physical Data Independence: Physical data independence is the capacity to change the physical storage or access methods without affecting the application logic. While 3NF primarily focuses on the logical organization of data, achieving Third Normal Form can indirectly support physical data independence by facilitating the maintenance of a well-structured schema.
- Logical Data Independence: Logical data independence refers to the ability to change the logical schema without impacting the application programs. Adhering to Third Normal Form contributes to logical data independence by providing a well-structured, efficient schema that can be modified without causing disruptions.
- Integrity Independence: Third Normal Form supports integrity independence by ensuring that data dependencies are appropriately captured n the schema. This means that any constraints, such as primary and foreign keys, can be maintained independently of the application logic.
- Distribution Independence: Distribution independence implies that users are unaware of how the data is physically distributed across multiple locations. Although achieving Third Normal Form primarily impacts the logical organization of data, it indirectly contributes to distribution indeence by providing a well-structured schema that can be effectively partitioned and distributed across various storage systems.
- Non-Subversion Rule: The Non-Subversion Rule states that if the system supports a low-level language, it should not be used to bypass integrity rules or constraints expressed in the high-level language. Adhering to Third Normal Form bolsters the Non-Subversion Rule by ensuring that all data dependencies are captured correctly in the schema. Consequently, the likelihood of violating integrity constraints is minimized, even when using a low-level
Adheres to Codd's 12 Criteria for Normalization
E.F. Codd, the theoretician who laid the groundwork for relational databases, in an article published in the October 1985 edition of Computerworld,
described 12 rules of fully relational database
The theory went relatively unchallenged, but his comparisons of existing database systems ruffled feathers because, according to Codd's criteria, some were marketed as relational when they were not.
Most of Codd's 12 criteria establish rules for how the RDBMS handles things internally and only peripherally influence how users design databases to be managed by the RDBMS. Designing a database in third normal form makes it possible for the RDBMS to follow Codd's rules. There are four rules that stand out.
Four Important Rules
Slogan to Remember Codd's Criteria
If you are having trouble remembering which requirements go with which normal form, there is a slogan you can use to keep them in order.
Codd Slogan for Normalization:
The key, the whole key, and nothing but the key, so help me Codd.
- “The key” : Tables may not contain repeating groups, which prevent a table from having a primary key. (1NF)
- “the whole key”: Every attribute must be functionally dependent on the entire primary key. (2NF)
- “and nothing but the key,” : There may not be any transitive dependencies, which involve a non-key field.(3NF)
- “ so help me, Codd.” : The theorist who gave us these rules.
Codds Normalization Basis
The slogan is based on the oath witnesses take in courts in the United States, which requires them to
tell "the truth, the whole truth, and nothing but the truth, so help me God."
The next lesson discusses normalization beyond 3NF.
Normalization - Exercise
Before you move to the next lesson, click the Exercise link below to reinforce your understanding of normalization rules.
Normalization - Exercise