1 / 32

09-16-2009, Wednesday E-R Diagrams and Normalization

CS8630 Database Administration Dr. Mario Guimaraes. 09-16-2009, Wednesday E-R Diagrams and Normalization. Class Will Start Momentarily…. Database Design. Obtain Requirements 2) Draw ER then Map to Tables or 2) Define Functional Dependencies then Normalize. E-R Introduction.

evangeline-dillard
Télécharger la présentation

09-16-2009, Wednesday E-R Diagrams and Normalization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. Database Design • Obtain Requirements 2) Draw ER then Map to Tables or 2) Define Functional Dependencies then Normalize

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

  4. Example of an E-R Diagram

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

  6. Many Different Notations

  7. 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 ?

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

  9. 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

  10. 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 (*:*)

  11. Staff Manages Branch

  12. Staff & PropertyForRent

  13. Staff Manages Branch Newspaper & propertyForRent

  14. Multiplicity of Ternary Registers Relationship

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

  16. 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.

  17. 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.

  18. Example of Entity w/Subclasses

  19. Subclass in Relationship

  20. 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.

  21. 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

  22. 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

  23. Steps in Normalization

  24. 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.

  25. 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)

  26. 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)

  27. 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

  28. 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

  29. 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

  30. 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.

  31. 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.

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

More Related