Database normalization is a common practice for maintaining data separation in a certain schema to prevent unauthorized data access, insertion and deletion, inconsistent updates, and cancellation anomalies. In addition to improving performance, it prevents data duplication and redundancy. Also, it aids in preventing the creation of superfluous tables. By distinguishing their linkages, identifying necessary tables, and segmenting within those tables, it prevents underlying processes from duplicating or removing information. Normalization is important for efficient database management since it helps a designer see the entire picture based on the functional dependencies of elements and achieve core functionalities without any duplication, undesired deletion and presence irregularities. It highlights only required information and arrange for their successful entry. It reduces stockpiling of prerequisites in its way to control data access and standard upkeeping. A normalized database ensures reliability and integrity of information stored in a database (Robert John Flynn, 1999).
Database Normalization
Database normalization requires careful investigation of required elements and data to be stored in tables. It focuses on keeping database tables to the minimal and develop relations across different tables using keys that are of common interest between them.
1NF
These normal forms if defined in the relation. According to Chen, Liu, Meyer, and Gotterbarn, (2007, March, p.223), it specifies that all properties or fields in a table should have atomic domains whereby the values of these fields are indivisible. For instance if the entity person has the property name, it should be expanded into first name and last name. The main purpose of 1NF normalization is
Eliminate redundant fields from a table
Establish separate relation for groups of attributes with related data and primary attribute
2NF
In order to explain the second normal form, the following words are important to know; prime attribute and non-prime attribute. Prime attributes are entity properties that are part of the prime key whereas non-prime attributes are the entity properties that are functionality dependent on prime key (Dongare, Dhabe, & Deshmukh, 2011). Second normal form specifies the concept or removing duplicate data in a table. Conditions for 2NF are;
All 1NF requirements are met
Removal of fields that belong to other new tables
Create relationship with the new tables
3NF
The third normal form further creates more efficiency from the second normal form. The conditions for 3NF;
All 2NF requirements are met
Fields that are not dependent on the primary key
Adolph Case
The Adolph case database has fully fulfilled the requirements of 1NF. This is because none of the fields in any table are composite attributes. This necessitates the evaluation of the tables to see if they meet requirements of the 2NF (Arenas, 2009). Analysis of the employee table necessitates the creation of a zip table as address attribute is a prime key. Therefore, the new ZIP table created will contain address as the primary key, state, city and zip code. Below is the entity relation diagram representing the new normalized tables and relationships;
Fig. ERD
ACID
Database transaction is a unit of a program that contains a task. A database transaction must adhere to four properties namely; Atomic, Consistent, Isolated and Durable (ACID). These properties ensure that the database is complete, it is accurate and it has data integrity (Deng, Frankl, & Chen, 2003, October, p.187). Atomic property defines that a transaction in the database is a single minimal unit whose processes are either entirely executed or not executed at all. Consistency property defines that no transaction in the database should have any adverse effect on the database data. Therefore, a transaction should not affect the consistency of data. Durability of the database defines the property that the database should be able to hold any updates to the system, even in the event of system failure.
In conclusion, normalization has been implemented in the Adolph database as seen in the new database schema used to design the tables. Normalization is important as it helps reduce data redundancy is a database. On the other hand, ACID represent transaction properties that are important in ensuring that the database is complete, accurate, and has data integrity.
Why Normalization
The main importance of a database is to refrain from information redundancy and abnormalities in database manipulation. Information redundancy may cause system performance degradation and reduce the efficiency of a database. Abnormalities in database functionalities include unwanted deletion of data, unwanted update of data and other unwanted operations.
A normalized database ensures reliability of data and enhanced performance. Furthermore, it ensures no data duplicate exists. A normalized database is much easier to handle and use. Without normalization, it is common to have a inconsistent relationship across multiple tables, which may result in improper query output.
REFERENCES
Arenas, M. (2009). Boyce-Codd Normal Form. In Encyclopedia of Database Systems (pp.
264-265). Springer US.
Chen, T. X., Liu, S. S., Meyer, M. D., & Gotterbarn, D. (2007, March). An introduction to
functional independency in relational database normalization. In Proceedings of the
45th annual southeast regional conference (pp. 221-225). ACM.
DBMS Normalization. (2017). www.tutorialspoint.com. Retrieved 8 October 2017, from
https://www.tutorialspoint.com/dbms/database_normalization.htm
Deng, Y., Frankl, P., & Chen, Z. (2003, October). Testing database transaction concurrency.
In Automated Software Engineering, 2003. Proceedings. 18th IEEE International
Conference on (pp. 184-193). IEEE.
Dongare, Y. V., Dhabe, P. S., & Deshmukh, S. V. (2011). RDBNorma:-A semi-automated
tool for relational database schema normalization up to third normal form. arXiv
preprint arXiv:1103.0633.
Robert John Flynn, R. A. (1999). A Quarter-century of Normalization and Social Role Valorization: Evolution and Impact.
