Normalization

Developinga database can be a long and tedious process. In the course ofcreating a database an IT specialist is assured that he/she mightface some problems. Such problems include data redundancy, insertionanomaly, update anomaly, and deletion anomaly. The most convenientway to handle these anomalies is by using the normalization process. is a process that is used to avoid deletion anomaly,update anomaly, insertion anomaly and data redundancy. helps the database to be more efficient and free from major errors.

Processof

Toensure that a database is correctly normalized there is a procedurethat should be followed by using normal forms. There are many typesof normal forms which are First normal form(1NF), Second Normalform(2NF) and Third Normal Form(3NF). If a database is to benormalized, the IT expert must ensure that the database follows therules that are in each form. Each form has a different rule, and youcan only complete one form then go to the next. For example, it is amust that the database satisfies the standards in the first normalform to start on the second normal formal and to start on the thirdnormal form it must meet the standards in the second normal form.

FirstNormal Form

Theonly rule that applies in this form is that a column is not allowedto hold multiple values.The column should only contain a singlevalue. For example, in our bookstore database, it ensures that thereis no case where multiple values are found in one column. If a columnhas multiple values, the best way to make sure that the tablesatisfies the 1NF is by creating a new column for each single value.

SecondNormal Form

Onlytwo rules apply in the second normal form. The table must be in the1NF, and there should be no non-prime attribute that is reliant onthe apt subset of any candidate key of the table. In the bookstoredatabase, the status inventory table and the sales transaction listtable will have to be split to fit the requirements of 2NF.A newtable will be created and it will contain the fields Producer_ID,Product_Name, and Type_of_Item.The new table will be called ProductDetails.

ThirdNormal Form

Fora database to be in 3NF, two rules must be adhered to. The databaseshould satisfy the rules in 2NF and Transitive functional dependencyon non-prime attributes on any super key must be eliminated. Thebookstore database is compliant with the 3NF rules. Hence, nomodifications will be made.

SQLStatements of the Tables

ProductDetails Table

CREATETABLE ProductDetails

(

Producer_IDint,

Product_Namevarchar(50),

Type_of_Itemvarchar(50)

)

InventoryTable SQL statements

CREATETABLE InventoryTable

(

Type_of_Itemvarchar(50),

Unit_pricedouble,

Cost_of_itemdouble,

Sold_by varchar(50),

Sold_to varchar(50),

Selling_PriceDouble

Date_C Date

)

SalesTransaction Table Statements

CREATETABLE SalesTransaction

(

Type_of_Itemvarchar(50),

Unit_pricedouble,

Cost_of_itemdouble,

Date_C Date

)

Screenshotsof Database

Conclusion

Itis clear that normalization helps in organizing the database to avoiderrors. A normalized database is more efficient and simpler tounderstand as compared to a database that is denormalized.

References

Snodgrass,R. T. (2000).&nbspDevelopingtime-oriented database applications in SQL.Morgan Kaufmann Publishers,.

Cattell,R. (2011). Scalable SQL and NoSQL data stores.&nbspAcmSigmod Record,&nbsp39(4),12-27.

Xin,R. S., Rosen, J., Zaharia, M., Franklin, M. J., Shenker, S., &ampStoica, I. (2013, June). Shark: SQL and rich analytics at scale.In&nbspProceedingsof the 2013 ACM SIGMOD International Conference on Management ofdata&nbsp(pp.13-24). ACM.