Normalization

Normalization
Student’s Name
Institutional Affiliation

Assignment2: Normalization
Abstract
Normalization is a term that is used in reference to databases and is used to refer to the process by which data in databases is organized in such a way that data redundancy is eliminated or greatly reduced. The process of normalization also ensures that data integrity in databases is improved. The process of normalization can be a basic one or it can take complex forms. In this regard, normalization can take several forms; First Normal Form, Second Normal Form, The third Normal Form and BCNF (Boyce and Codd Normal Form). While the First Normal form (usually abbreviated as 1NF) is the most basic with only two dimensions, rows and columns, the BCNF form is the most complex and handles anomalies that cannot be handled in the third normal form. Data normalization ensures that retrieval of data is easy and, therefore, when querying databases, precision of the data retrieved can be guaranteed (Aleyksandr, 2012). This paper will discuss normalization, look at its practical applications as well as how normalization affects organizations in the real world.
Describe the steps that you would use in order to convert database tables to the First Normal Form, the Second Normal Form, and the Third Normal Form.
First normal form: involves having a table whose attribute doesn’t contain multiple values. For instance, take the table below;
Student Name Registration Number Address
John L3457 1234-456
Tom L5674 4566-987
7685-123
Dan L6795 4789-064
Fig1: a tabled example not satisfying 1NF.

From the table above, it is that the address attribute holds multiple values for Tom. To convert this to 1NF, the record for Tom is duplicate with each address holding their own unique row i.e. (Singh).
Student Name Registration Number Address
John L3457 1234-456
Tom L5674 4566-987
Tom L5674 7685-123
Dan L6795 4789-064
Fig2: table converted to 1NF.
Second normal form: involves having a table that satisfies 1NF and also there should no dependency of any primary key attribute. For instance, consider the table below;
Student Name Course number Account number
John L3457 1234-456
Tom L5674 4566-987
Fred L5674 4566-987
Dan L6795 4789-064
Fig3: a tabled example not satisfying 2NF
From the above table, it is evident that one course number relates two values in the account attribute. This can be solved by having two unique tables i.e. (Singh).
Course number Account number
L3457 1234-456
L5674 4566-987
L6795 4789-064
Fig4: first table that fits 2NF
Course number Student Name
L3457 John
L5674 Tom
L5674 Fred
L6795 Dan
Fig5: second table that fits 2NF
Third normal form; involves a table that satisfies 2NF and must be fully dependent on the primary key i.e. no transitive dependencies. For instance, consider the table below;
Student Name Registration Number ZIP City District
John L3457 1234-456 Agra Dayal
Tom L5674 4566-987 Chennai Mumbai
Tom L8947 7685-123 Pauri Bhagwan
Dan L6795 4789-064 Gwaliori Ratan
Fig6: a tabled example not satisfying 3NF.
From the above, it is evident that the city and district attribute have transitive attributes for the ZIP attribute. This can be easily converted to 3NF in two tables i.e. (Singh).
Student Name Registration Number ZIP
John L3457 1234-456
Tom L5674 4566-987
Tom L8947 7685-123
Dan L6795 4789-064
Fig7: first table satisfying 3NF.
ZIP City District
1234-456 Agra Dayal
4566-987 Chennai Mumbai
7685-123 Pauri Bhagwan
4789-064 Gwaliori Ratan
Fig8: the second table satisfying 3NF.
Provide one (1) example that is relevant to a college environment that illustrates reasons for converting database tables to the First, Second, and Third Normal Forms.
The table below shows a typical record for college students, their registration and address attributes. The address attribute store more than one instance for the row holding Tom’s details. This provides need to convert the table to 1NF for it to be better (Aleyksandr, 2012).
Student Name Registration Number Address
John L3457 1234-456
Tom L5674 4566-987
7685-123
Dan L6795 4789-064
Fig9: tabled 1NF example.
The table below shows a record for college students, course number and account number. From this it is evident that there exists partial dependencies between the course number and the account number. This should therefore be converted to 2NF to eliminate thess partial dependencies (Aleyksandr, 2012).
Student Name Course number Account number
John L3457 1234-456
Tom L5674 4566-987
Fred L5674 4566-987
Dan L6795 4789-064
Fig10: tabled 2NF example
The table below is a record for college students, registration number, Zip, city, and district. This shows that there exist transitive dependency for city and district on ZIP. Eliminating this transitive dependency will convert this table to 3NF (Aleyksandr, 2012).
Student Name Registration Number ZIP City District
John L3457 1234-456 Agra Dayal
Tom L5674 4566-987 Chennai Mumbai
Tom L8947 7685-123 Pauri Bhagwan
Dan L6795 4789-064 Gwaliori Ratan
Fig11: tabled 3NF example.
Explain typical situations when denormalizing a table is acceptable. Provide one (1) example of denormalizing a database table to justify your response.
Denormalization is a technique used on previously normalized tables with the sole purpose of (Drkusic, 2016):
Valid values that exist upon creation of the table, thus maintain the tables history.
To improve query performance by allowing scripts to search for the needed instance in a single whole database.
Speed up reports; this allows the production of analytic statistics on given table when they exist as a whole.
An instance can be from the normalized 3NF above, when searching for a given students record city and district, it would faster using a denormalized table than having to choose the best table that the query will work for.
Explain the significant manner in which business rules impact both database normalization and the decision to denormalize database tables.
Business rules has major effects on database normalization and its corresponding decisions that denormalization of such databases. This is because denormalization would involve creating new applications around the normalized database. The application creation process is a very tedious prospect that should ensure that the integrity properties of the normalized database is still maintained even upon denormalization. This means that every phases of the denornamlization process must be properly and effectively tested in order to guarantee the safety of data properties converted (Chrisholm, 2004).
References
Singh, C. (n.d.). BeginnersBook – A Complete Beginner’s Guide. Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database. Retrieved November 5, 2016, from http://beginnersbook.com/2015/05/normalization-in-dbms/Aleyksandr, J. (2012, August 15). Share and Discover Knowledge on LinkedIn SlideShare. Database design & Normalization (1NF, 2NF, 3NF). Retrieved November 5, 2016, from http://www.slideshare.net/jagaarj/database-design-normalizationDrkusic, E. (2016, March 17). Vertabelo – Design Your Database Online. Denormalization: When, Why, and How.Retrieved November 5, 2016, from http://www.vertabelo.com/blog/technical-articles/denormalization-when-why-and-howChrisholm, M. (2004, January 30). Information Management | IT Business News. The Normalized Foundation of Business Rules | Information Management Online. Retrieved November 5, 2016, from http://www.information-management.com/news/columns/-8076-1.html