09-16-2008, Tuesday E-R Diagrams and Normalization

1. CS8630 Database Administration Dr. Mario Guimaraes 09-16-2008, TuesdayE-R Diagrams and Normalization • Class • Will • Start • Momentarily…

2. E-R Introduction • Database design tool proposed by Peter Chen (1976). The real world is seen represented as entities, relationships and attributes. Example:

3. Example of an E-R Diagram

4. Components of E-R • Entity types • Relationship types • Attributes • Cardinality (minimum and maximum)

5. Many Different Notations

6. Example of Entity • Entity type • Group of objects with same properties, identified by enterprise as having an independent existence. • Entity occurrence or Entity Instance • Uniquely identifiable object of an entity type. • As shown in the right of the screen, an entity can have a physical existence or a conceptual existence. What would be typical entities of an inventory system ?

7. Relationships • Relationship type • Set of meaningful associations among entity types. • Relationship occurrence • Uniquely identifiable association, which includes one occurrence from each participating entity type.

8. Degree of Relationship • Degree of a Relationship • Number of participating entities in relationship. • Relationship of degree: • one is unary; • two is binary; • three is ternary; Most is Relationships are of _______ degree

9. Binary Relationships • The most common degree for relationships is binary. • Binary relationships are generally referred to as being: • one-to-one (1:1) • one-to-many (1:*) • many-to-many (*:*)

10. Staff Manages Branch

11. Staff & PropertyForRent

12. Staff Manages Branch Newspaper & propertyForRent

13. Multiplicity of Ternary Registers Relationship

14. Special Entity/Attribbutes • Other important terminology weak entity multi-valued attribute

15. Generalization/Specialization (Ch.12) • Superclass • An entity type that includes one or more distinct subgroupings of its occurrences. • Subclass • A distinct subgrouping of occurrences of an entity type.

16. Characteristics of Subclass • Superclass/subclass relationship is one-to-one (1,1:0,1). • Superclass may contain overlapping or distinct (disjoint) subclasses. • Not all members of a superclass need be a member of a subclass (Partial Specialization). Summary: subclass may be overlapping or disjoing, partial or total.

17. Example of Entity w/Subclasses

18. Subclass in Relationship

19. Normalization • Normalization may be used as an alternative or as a supplement to E-R Diagrams. All non-key attributes of a table must be dependent on THE KEY, THE WHOLE KEY, and NOTHING BUT THE KEY. Normalization: helps maintenance (update, insert , delete). Not intended to speed up queries.

20. Data Normalization • Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data. • The process of decomposing relations with anomalies to produce smaller, well-structured relations. • Primary Objective: Reduce Redundancy,Reduce nulls, • Improve “modify” activities: • insert, • update, • delete, • but not read • Price: degraded query, display, reporting

21. Functional Dependency and Keys • Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute. • Candidate Key: Each non-key field is functionally dependent on every candidate key. • Examples: • Ssn determines name, • Ssn determines salary • Salary does not determine ssn

22. Steps in Normalization

23. Normalization – most used • Four most commonly used normal forms are first (1NF), second (2NF) and third (3NF) normal forms, and Boyce–Codd normal form (BCNF). • Based on functional dependencies among the attributes of a relation. • A relation can be normalized to a specific form to prevent possible occurrence of update anomalies.

24. First Normal Form • No multi-valued attributes. • Every attribute value is atomic. • Why are the following tables not in 1NF Employee (ssn, Name, Salary, Address, ListOfSkills) Department (Did, Dname, ssn)

25. Second Normal Form • 1NF and every non-key attribute is fully functionally dependent on the primary key. • Every non-key attribute must be defined by the entire key, not by only part of the key. • No partial functional dependencies. Assuming that we have a composite PK (LicensePlate, OwnerSSN) for the Vechicle Table below, why is the table not in 2NF ? Vehicle (LicensePlate, Brand, Model, PurchasePrice, Year, OwnerSSN, OwnerName)

26. Third Normal Form & BCNF • 2NF and no transitive dependencies (functional dependency between non-key attributes = BCNF) Why are the following tables not in 3NF or BCNF ? • Why is Employee [ssn, name, salary, did, dname] • Customer

27. 3NF & BCNF • It is very rare for a Table to be in 3NF and not be in BCNF (violation of BCNF). • Given a Relation R with attributes A, B and C where A and B are together the composite PK, IF A, B -> C and C -> B THEN R is in 3NF and is not in BCNF Example: Student, course -> Instructor Instructor -> Course

28. Steps in Normalization • 1NF: a table, without multivalued attributes • if not, then decompose • 2NF: 1NF and every non-key attribute is fully functionally dependent on the primary key • if not, then decompose • 3NF: 2NF and no transitive dependencies • if not, then decompose • GENERAL: • Each table should describe a single theme • Modification anomalies are minimized Hint: THE KEY, THE WHOLE KEY AND NOTHING BUT THE KEY

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

30. 4NF • Dependency between attributes (for example, A, B, and C) in a relation, such that for each value of A there is a set of values for B and a set of values for C. However, set of values for B and C are independent of each other.

31. End of Lecture End Of Today’s Lecture.