Normalization

1. Chapter 4 Normalization

3. Purpose of Normalization Normalization • A technique for producing a set of relations with desirable properties, given the data requirements of an enterprise. • The process of normalization is a formal method that identifies relations based on their primary or candidate keys and on the functional dependencies among their attributes. • Normalization supports a number of tests , which can be applied to relations so that a relational schema can be normalized to a specific form to prevent the possible occurrence of update anomalies.

4. First Normal Form Relation We saw before that a relation should have the following properties: • A relation in a database has a unique name. • Each cell of the relation contains exactly one atomic value. • Each attribute has a distinct name within a relation. • The values of an attribute are all from the same domain. • The order of the attributes has no significance. • Each tuple is distinct; there are no duplicate tuples. • The order of tuples has no significance, theoretically. In that case we say that the relation is in FIRST NORMAL FORM (1NF).

5. Un-normalized form A table that contains repeating Groups First Normal Form A relation in which the inter- section of each row and column contains one and only one value Normalization Process: 1NF

6. Sample Database : 1NF First Constraints Primary Key : S# , P# A city has a specific status A Supplier Is located in one City

7. Update Anomalies • Insertion Anomalies • each time we insert a new part for a supplier we have to repeat status and city • we cannot insert a new supplier before he supplies a part • Deletion Anomaly • If we delete the fact that S3 supplies P2 we delete the row and we do not know anymore that he is located in Paris • Modification Anomalies • If S1 moves from London to Berlin we have to modify 6 rows • If the status of London changes we have to modify 9 rows in order to avoid inconsistency

8. Functional Dependencies Functional Dependency • Describes the relationship between attributes in a relation • If A and B are attributes of relation R, B is functionally dependent on A , if each value of A in R is associated with exactly one value of B in R • notation A  B • Functional dependency diagram Determinant • The determinant of a functional dependency refers to the attribute or group of attributes on the starting point of the arrow B is functionally dependent on A A B

9. Functional Dependencies in FIRST S# Status QTY P# City

10. Sample Database : 1NF First Constraints Primary Key : S# , P# A city has a specific status A Supplier Is located in one City

11. Lossless-join and Dependency Preservation Properties • Two important properties of decomposition • Lossless-join property enables us to find any instance of the original relation from corresponding instances in the smaller relations. • Dependency preservation property enables us to enforce a constraint on the original relation by enforcing some constraint on each of the smaller relations.

12. Full Functional Dependency 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 a proper subset of A. e.g. First.(S# , Status ) First.City First. S# First.City In fact full functional dependence is a more important concept than functional dependence

13. Normalization Process: 2NF A relation is in second normal form if it is in first normal form and every non-primary-key attribute is fully functional dependent on the primary key. Second SP The reduction consists of a suitable projection Supplier 5 is inserted If in a 1NF relation the primary has only one attribute, the relation is also in 2NF

14. Functional Dependencies in SP and Second S# Elimination of non-fully functional dependencies QTY P# S# Status City

15. Normalization Process: 3NF A relation in third normal form is a relation that is in first and second normal form , and in which no non-primary-key attribute is transitively dependent on the primary key. SC CS

16. Functional Dependencies in SP , SC and CS S# QTY P# Elimination of transitive dependencies S# City Status City

17. Inter-relational Dependency S# QTY P# The three relations are also in 3NF but there is an inter-relational dependency S# City Bad decomposition Status S#

18. Normalization Process: BCNF A relation is in Boyce-Codd normal form if and only if every determinant is a candidate key. • This definition doesn’t refers to other normal forms. • BCNF is stronger than 3NF If a relation is in third normal form , violation of the BCNF is quite rare. It may only happen under the specific conditions that the relation : • contains two or more composite candidate keys • which overlap and share at least one attribute in common • this attribute is fully dependent on the primary key

19. Boyce-Codd Normal Form (BCNF) • Violation of BCNF may occur in a relation that • contains two (or more) composite keys • which overlap and share at least one attribute in common.

20. Multivalued Dependency In R(A,B,C) the multivalued dependency R.A R.B holds in R , if and only if the set of B-values matching a given pair (A,C) is independent of the C-value. • Multivalued dependencies always go together in pairs • notation R.A R.B R.C • functional dependency is a special case of multivalued dependency • example CTX

21. Normalization Process: 4NF Normalized CTX.Course CTX.Teacher CTX.Course CTX.Text Omit multivalued dependencies CTX 4NF