Database Administration Lecture No 2

1. Database AdministrationLecture No 2 Muhammad Abrar muhammadabrar78@yahoo.com

2. Hppt://sites.google.com/site/bcskardan

3. Normalization • We are familiar with the following facts about the database • relations have no duplicate tuples, • tuples have no ordering • each element in the relation is atomic • Relations that satisfy these basic requirements may still have some undesirable properties i.e. • Data Redundancy, anomalies etc. • Today we will study about these problems and how to eliminate these undesirable properties

4. Normalization • It is a stepwise technique that study the relation and decompose it into two are more than two smaller relations if there are some problems in the relation. • Normalization steps are called Normal Forms • Each normal form name is associated with the sequence in which we perform the specific step i.e. 1NF, 2NF, 3NF, 4NF etc • Normalization is based on the concept of Functional Dependency

5. Functional Dependency • Functional Dependencies • The most important concept in relational databases • Definition • An attribute ‘B’ is said to be functionally dependent on attribute ‘A’, if for each value of attribute ‘A’ only one value form attribute ‘B’ is returned.

6. projects of emp no 123? • Name of pnumber 1? • Hour for Emp no 123? • Name for emp no 124? • Hour for emp 123 and project no 2? • Location of Project No 3? {1,2,3} {Analysis} {23,20,15} {Saleem} {20} {Hirat}

7. Functional Dependency (Cont’d) • Rule • A functional dependency denoted by X  Y, between two set of attribute X and Y that are subset of a relation R is that • For any two tuples (rows) t1 and t2 in R that have t1[X] = t2[X], we must have t1[Y] = t2[Y] • This mean that the value of Y attribute of tuple depends on the value of X attributes • OR • The value of X attribute of a tuple functionaly (or uniquely) determine the values of Y attributes • A relation is said to be semantically correct if all tuple follows the functional dependency rule

8. Functional Dependency (cont’d) • Functional dependency is a property of semantic of the relation • It shows that how the attribute are related to each other

9. Functional Dependency (cont’d) • We represent the previous functional dependency as follow • EMP NO  ENAME • PNUMBER  { PNAME, PLOCATION} • {EMP NO, PNUMBER}  HOURS

10. Normalization • Proposed by E F Codd in 1972 • It take a relation and process it through a series of tests to clarify whether it satisfy certain normal forms or not. • Initially Codd proposed 3 normal forms • These Normal forms are based on functional dependency among the attributes of relation

11. Normalization (cont’d) • Definition • It is a process of analyzing the given relation based on their FDs and PK to achieve the desirable properties of • 1. Minimizing redundancy • 2. Insertion, Deletion and Update Anomalies • A series of normal form tests that can carried out on individual relation so that the relational database can be normalized to any degree

12. Normalization (cont’d) • Normalization consist of the following normal forms that are based on Functional Dependency • First Normal Form – 1NF • Second Normal Form – 2NF • Third Normal Form – 3NF • Boyce – Codd Normal Form - BCNF • The following or based on multivalued and Join Dependency • Forth Normal Form (Multivalued Dependency) – 4NF • Fifth Normal Form (Join dependency) – 5NF

13. Steps in Normalization

14. First Normal Form – 1NF • A relation is said to be in 1NF if every attribute has atomic value • A relation is said to be in 1NF if it has no mutivalued attributes

15. Relation with mutivalued information – not in 1NF Not a relation at all

16. Relation with No mutivalued information and unique rows - in 1nf • Now this is relation • Each relation is by default in 1NF

17. Anomalies in this Table • Insertion–if new product is ordered for order 1007 of existing customer, customer data must be re-entered, causing duplication • Deletion–if we delete the Dining Table from Order 1006, we lose information concerning this item's finish and price • Update–changing the price of product ID 4 requires update in several records • Why do these anomalies exist? • Because there are multiple themes (relations) in one relation. This results in duplication and an unnecessary dependency between the entities

18. Second Normal Form • If the relation is in 1NF and every non-key attribute is fully functionally dependent on the ENTIRE primary key. • If the relation is 1st NF and there is no partial functional dependency • Every non-key attribute must be defined by the entire key, not by only part of the key • No partial functional dependencies

19. Functional dependency diagram for INVOICE Order_ID  Order_Date, Customer_ID, Customer_Name, Customer_Address Customer_ID  Customer_Name, Customer_Address Product_ID  Product_Description, Product_Finish, Unit_Price Order_ID, Product_ID  Order_Quantity Therefore, NOT in 2nd Normal Form

20. Removing partial dependencies Getting it into Second Normal Form Partial dependencies are removed, but there are still transitive dependencies

21. Third Normal Form • If the relation is in 2NF there is no transitive dependencies (functional dependencies on non-primary-key attributes) • Note: This is called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third • Solution: Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table

22. Removing partial dependencies Getting it into Third Normal Form Transitive dependencies are removed

23. Relation • Definition: A relation is a named, two-dimensional table of data • Table consists of rows (records) and columns (attribute or field) • Requirements for a table to qualify as a relation: • It must have a unique name • Every attribute value must be atomic (not multivalued) • Every row must be unique (can’t have two rows with exactly the same values for all their fields) • Attributes (columns) in tables must have unique names • The order of the columns must be irrelevant • The order of the rows must be irrelevant NOTE: all relations are in 1st Normal form

24. Normalize the following Table

25. Normalize the following Table

26. Normalize the following Table

27. Normalize the following Table