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
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, Third Normal Form and Its Relation to Codd's Criteria
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.
- 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.
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."
Ted's 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.
Codd produced these rules as part of a personal campaign to prevent his vision of the relational database being destroyed, as database vendors scrambled in the early 1980s to repackage existing products with a relational overlay. Rule 12 was particularly designed to counter such a positioning. Even if such re-packaged non-relational products eventually gave way to SQL DBMS, no popular relational database management systems are actually relational, be it by Codd's twelve rules or by the more formal definitions in his papers, in his books by its coworkers and successors, Christopher J. Date, David McGoveran and Fabian Pascal. Only less known DBMSs like Sybase strive to comply .
The only commercial example, as of December 2010, is Dataphor. Some rules are controversial, especially rule three, because of the debate on three-valued logic.
Three-Valued Logic and Third Normal Form: Exploring the Connection
This paper investigates the relationship between 'Three-valued logic' and 'Third Normal Form' in the context of database design and management. While these concepts are not directly related, both share a common theme in the quest for data consistency and accuracy. We will discuss their distinct roles and implications, delving into the importance of each concept in maintaining data integrity within relational databases.
The field of database management has given rise to various techniques and models aimed at optimizing the storage and retrieval of data. Among these, the concepts of 'Three-valued logic' and 'Third Normal Form' have garnered significant attention. Despite being distinct in nature and application, both share a common goal in ensuring data consistency and accuracy.
Three-valued logic : An Overview
Three-valued logic (3VL) is a non-classical logic system that extends the traditional Boolean logic with a third truth value, typically represented as 'unknown' or 'null.' In classical Boolean logic, propositions can be evaluated as either true or false. However, in real-world scenarios, especially in database management, situations may arise where the truth value of a proposition cannot be determined. In such cases, three-valued logic provides a more nuanced approach, facilitating the representation of incomplete or missing information.
In logic, a three-valued logic (also trinary logic, trivalent, ternary, or trilean, sometimes abbreviated 3VL) is any of several many-valued logic systems in which there are three truth values indicating true, false and some indeterminate third value. This is contrasted with the more commonly known bivalent logics (such as classical sentential or Boolean logic) which provide only for true and false. Conceptual form and basic ideas were initially created by Jan Lukasiewicz and C. I. Lewis.
These were then re-formulated by Grigore Moisil in an axiomatic algebraic form, and also extended to n-valued logics in 1945.
Third Normal Form: Definition and Importance
Third Normal Form (3NF) is a database normalization technique aimed at eliminating data redundancy and ensuring data consistency in relational databases. This form, introduced by Edgar F. Codd in 1971, builds upon the principles of First and Second Normal Forms. A table is considered to be in 3NF if it meets the following criteria:
- It is in Second Normal Form (2NF).
- There are no transitive functional dependencies between non-prime attributes.
In simpler terms, 3NF ensures that all non-key attributes are fully dependent on the primary key, and there are no hidden relationships between non-key attributes that could lead to redundancy or inconsistency.
Exploring the Connection
Upon examining the concepts of three-valued logic and third normal form, it becomes evident that these ideas are not directly related. However, they share a thematic connection in their pursuit of data consistency and accuracy.
Three-valued logic is particularly useful in database management when dealing with incomplete or uncertain data. By allowing the representation of 'unknown' or 'null' values, 3VL enables databases to store and process information that may be subject to change or further clarification. This contributes to the maintenance of data consistency and the accurate representation of real-world situations.
On the other hand, Third Normal Form focuses on eliminating redundancy and ensuring data consistency through the normalization process. By enforcing strict rules on the structure and relationships within a database, 3NF minimizes the chances of data anomalies and inaccuracies. Consequently, adherence to 3NF guarantees a higher degree of data integrity in relational databases.
While 'Three-valued logic' and 'Third Normal Form' are not directly related concepts, they share a common goal in promoting data consistency and accuracy. Three-valued logic provides a means to represent and process incomplete or uncertain information, whereas Third Normal Form eliminates redundancy and ensures data consistency through normalization. Both concepts play crucial roles in maintaining data integrity within relational databases, highlighting their significance in the broader context of database design and management.
The next lesson discusses normalization beyond 3NF.
DeNormalization Theory - Exercise