Normalization: 1NF, 2NF, 3NF
Discussing data anomalies, functional dependencies, and normalization process: the first normal form, second normal form, third normal form
Table of Contents
· Introduction
· Data Redundancy and Anomalies
∘ Insertion Anomalies
∘ Deletion Anomalies
∘ Update Anomalies
· Normalization Process
∘ 1NF (First Normal Form)
∘ 2NF (Second Normal Form)
∘ 3NF (Third Normal Form)
· Summary
· References
Introduction
The design process is one of the stages in the database life cycle after the data requirements analysis phase is complete. There are two approaches to database design, namely:
- The top-down design uses the entity-relationship (ER) model. The procedure identifies the entities, followed by the relationships between entities and cardinalities or multiplicities. Each entity — may also be a relationship — is equipped with attributes, primary key, and foreign keys (if any).
- The bottom-up design does the process of normalization. The method identifies attributes and then groups them into data sets to form relations.
The two approaches complement each other. The design process begins with conceptual data modeling using the ER model and continues to the logical database design stage. In the early ER model, you normalize entities or relationships that have data redundancy. The normalization results modify the initial ER model to obtain a better final ER model. In the logical database design stage, you normalize the relations — mapping results from ER model — which may also have data redundancy.
Data redundancy in a relation causes many problems. In addition to wasting storage space, three main problems arise, namely when: (1) adding new data, (2) modifying, and (3) deleting existing data. These three problems, commonly called data anomalies, are addressed by normalization.
“A relation is a table with columns and rows.” (Connolly & Begg, 2015:152)
“… E.F. Codd, used the term relation as a synonym for table.”
(Coronel et.al., 2020:71)
“… in the relational model the term relation is used to refer to a table.” (Silberschatz et.al., 2020:40)
The purpose of normalization is to group attributes into relations to minimize data redundancy. Codd (1970, 1971) proposed the concept of normalization by introducing 1NF (first normal form), followed by 2NF (second normal form), and 3NF (third normal form). Together with Raymond F. Boyce, Codd (1974) defined BCNF ( Boyce-Codd normal form). Fagin (1977) introduced 4NF (fourth normal form) followed by 5NF (fifth normal form) in 1979. Date, Darwen, and Lorentzos (2002) defined 6NF (sixth normal form).
In practice, the normalization process to 3NF is good enough to reduce data redundancy. A relation that fulfills 3NF, in most cases, usually satisfies BCNF and higher normal form. According to Coronel and Morris (2020), some exceptional cases, such as statistical research, may require the normalization of 4NF or higher. Higher normal forms usually increase join-operations, which reduces performance without any added value in reducing data redundancy.
Data Redundancy and Anomalies
Not only wasting storage space, but data redundancy also causes data anomalies. There are three types of data anomalies: (1) insertion anomalies, (2) deletion anomalies, and (3) update anomalies.
For example, see the BranchEmployee relation in Figure 1(a). There is data redundancy since repetitions of the same value on the last three columns: BranchNo, BranchAddress, and BranchPhone. After normalization, we obtained Employee and Branch relation. See Figure 1(b) dan 1(c). Both are free from data anomalies problems because there is no data redundancy.
(b), (c) Relation without data redundancy
Insertion Anomalies
An insertion anomaly occurs when you add new data to a relation. See the BranchEmployee relation in Figure 1(a). Suppose a new employee is placed in Branch B01, then into the BranchEmployee relation, detailed data of the B01 branch (BranchNo, BranchAddress, BranchPhone) must also enter with the risk of data inconsistency. On the other hand, in the Employee relation in Figure 1(b), it is simple to enter employee data without entering branch details.
Suppose there is a new branch with no employee yet. In that case, it’s impossible to enter the detailed data of the new branch since the EmpId as the primary key can not be null. In contrast, see Figure 1(c), you can add data of the new branch into the Branch relation without issue.
Deletion Anomalies
A deletion anomaly occurs when any data is deleted. See the BranchEmployee relation in Figure 1(a). Suppose you delete an employee with EmpId 19009. In this case, you also delete branch detail data B03. On the other hand, see Figure 1(b), deleting employees with EmpId 19009 in the Employee relation does not remove detailed data of branch B03.
Update Anomalies
Update anomaly occurs when any data is updated. See the BranchEmployee relation in Figure 1(a). Suppose branch B03 changes its address and its telephone number. In the EmployeeBranch relation, you must repeatedly update all existing B03 branches. It can lead to data inconsistency. On the other hand, see Figure 1(c); it is only once to modify the data of the Branch B03.
Normalization Process
As an example of the case of the normalization process, see the example of a shopping receipt in Figure 2. The table in Figure 3, which contains data of the shopping receipt, is a UNF (unnormalized form) table as it does not meet the characteristics of a relation. One of its characteristics states that each cell of the relation must be single-valued. The cells in the Qty, ItemId, ItemName, UnitPrice, and Amount attributes have two values. The table has repeating groups.
Unnormalized Form (UNF)
A table that contains one or more repeating groups.
(Connolly & Begg, 2015:466)
1NF (First Normal Form)
A relation that satisfies 1NF is a relation in which each row and column intersection contains one and only one value. Let’s modify the UNF relation in Figure 3 in such a way as to be two rows. See Figure 4. Notice that each row and column intersection contains one and only one value. It means that the relation has met the 1NF requirements. The scheme is as follows.
ShoppingReceipt (ReceiptNo, Date, CustName, Address, HpNo, Qty, Unit, ItemCode, ItemName, UnitPrice, Amount, Total)
The last two attributes are derived attributes so that you can ignore them (may not be stored in the database). The relation schema is as follows:
Get M. Ramadhan’s stories in your inbox
Join Medium for free to get updates from this writer.
ShoppingReceipt (ReceiptNo, Date, CustName, Address, HpNo, Qty, Unit, ItemCode, ItemName, UnitPrice)
2NF (Second Normal Form)
A 2NF is a relation that satisfies 1NF and every non-primary key attribute is fully functionally dependent on the primary key. So, there are two things related to 2NF: primary key and full functional dependency.
Functional Dependency
Describes the relationship between attributes in a relation. For example, if A and B are attributes of relation R, B is functionally dependent on A (denoted A → B), if each value of A is associated with exactly one value of B. A and B may each consist of one or more attributes. (Connolly & Begg, 2015:457)Determinant
Refers to the attribute, or group of attributes, on the left-hand side of the arrow of a functional dependency. (Connolly & Begg, 2015:457)
For example, student Name is functionally dependent on StudentId, StudentId→Name; for one StudentId value, only one Name is obtained. But not the other way around, StudentId has no functional dependency on Name; several students with different StudentId values can own the same Name.
Full functional dependency
Indicates that if A and B are attributes of a relation, B is fully functionally dependent on A if B is functionally dependent on A, but not on any proper subset of A. (Connolly & Begg, 2015:459)
Since the requirements for a 2NF relation are related to the primary key, it is necessary to determine the primary key of a 1NF relation. No single attribute qualifies as a primary key. Therefore, the combination of two attributes, namely ItemCode+ReceiptNo, is taken as the composite primary key so that the relation schema becomes:
Since 2NF relates to the primary key, it is necessary to determine the primary key of a 1NF relation. No single attribute qualifies as a primary key. Therefore, the combination of two attributes, namely ItemCode+ReceiptNo, is taken as the composite primary key so that the relation schema becomes:
ShoppingReceipt (ItemCode, ReceiptNo, Date, CustName, Address, HpNo, Qty, Unit, ItemName, UnitPrice)
Furthermore, each non-primary key attribute is checked whether it is fully functionally dependent on the primary key or only on the part of the primary key.
See Figure 5. Two groups of attributes have a functional dependency on the part of the primary key (partial dependency). First, the Unit and ItemName have the functional dependency on ItemCode only.
ItemCode → Unit, ItemName
Second, the Date, CustName, Address, and HpNo have functional dependency only on ReceiptNo. It means that the ShoppingReceipt relation does not meet 2NF yet.
ReceiptNo → Date, CustName, Address, HpNo
Let’s remove the two groups of attributes with partial functional dependencies into different relations to obtain three 2NF relations: Item, CustomerReceipt, and ReceiptDetail. See Figure 6. All non-primary key attributes in the three relations are fully functionally dependent on the primary key.
3NF (Third Normal Form)
A relation that fulfills 3NF is a relation in 1NF and 2NF and in which no non-primary-key attribute is transitively dependent on the primary key.
Transitive dependency
A condition where A, B, and C are attributes of a relation such that if A → B and B → C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C). (Connolly & Begg, 2015:460)
In other words, a transitive dependency is a functional dependency between non-primary key attributes. The non-primary key attribute in a 2NF relation must be checked whether it has a transitive dependency or not. See Figure 6. The schema of the three 2NF relations is as follows.
Item (ItemCode, Unit, ItemName)
ReceiptDetail (ItemCode, ReceiptNo, Qty, UnitPrice)
CustomerReceipt (ReceiptNo, Date, CustName, Address, HpNo)
In the Item relation, there is no dependency between the Unit and the ItemName. Likewise, in the ReceiptDetail relation, there is no dependency between the Qty and the UnitPrice. It means that they both meet 3NF because they do not have a transitive dependency. In the CustomerReceipt relation, HpNo value is unique; there is transitive dependency as follows:
ReceiptNo → HpNo
HpNo → CustName, Address
Thus, the CustomerReceipt relation does not meet 3NF. See Figure 7.
Further, let’s remove the attributes with transitive dependency from CustomerReceipt into a different relation. We obtain two 3NF relations: Receipt and Customer. See Figure 8.
So, the final result of the normalization process is four 3NF relations: (1) Customer, (2) Receipt, (3) ReceiptDetail, and (4) Item. Figure 9 shows the relationship between relations through the foreign keys with a dotted underline referring to the primary keys with a continuous underline.
Summary
Apart from being a waste of storage space, relationships that have data redundancy pose three main problems, namely when: (1) adding new data, (2) updating, and (3) deleting existing data. These three problems are commonly called data anomalies.
We have discussed normalizing relations up to 3NF to deal with data anomalies. Overall, Figure 10 shows the sequence of processes.
Thanks for reading! If you’d like to support my writing journey, you can treat me to a coffee here. Every bit of support is deeply appreciated!
References
Codd, E. F. (1970, June). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6), pp. 377–387. doi:10.1145/362384.362685
Connolly, Thomas M., & Begg, Carolyn E. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management (6th Edition). Essex, England: Pearson Education.
