Ch 7 Normalization-Part 1.ppt

Ch 7: Normalization-Part 1 Much of the material presented in these slides was developed by Dr. Ramon Lawrence at the University of Iowa Normalization Normalization is a technique for producing relations with desirable properties. Normalization decomposes relations into smaller relations that contain less redundancy. This decomposition requires that no information is lost and reconstruction of the original relations from the smaller relations must be possible. Normalization is a bottom-up design technique for producing relations. It pre-dates ER modeling and was developed by Codd in 1972 and extended by others over the years. Normalization can be used after ER modeling or independently. Normalization may be especially useful for databases that have already been designed without using formal techniques. Normalization Motivation The purpose of normalization is to develop good relational schemas that minimize redundancies and update anomalies. Redundancy occurs when the same data value is stored more than once in a relation. Redundancy wastes space and reduces performance. Update anomalies are problems that arise when trying to insert, delete, or update tuples and are often caused by redundancy. The goal of normalization is to produce a set of relational schemas R1, R2, …, Rm from a set of attributes A1, A2, … ,An. Imagine that the attributes are originally all in one big relation R= {A1, A2, .., An} which we will call the Universal Relation. Normalization divides this relation into R1, R2, …, Rm. The Power of NormalizationExample Relations The Power of NormalizationUniversal Relation Universal(eno, pno, resp, hours, ename, bdate, title, salary, supereno, dno, dname, mgreno, pname, budget) What are some of the problems with the Universal Relation? Normalization will allow us to get back to the starting relations. Update Anomalies There are three major types of update anomalies: Insertion Anomalies - Insertion of a tuple into the relation either requires inserti