CS8630 Database Administration Dr. Mario Guimaraes 09-16-2009, WednesdayE-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 • Database design tool proposed by Peter Chen (1976). The real world is seen represented as entities, relationships and attributes. Example:
Components of E-R • Entity types • Relationship types • Attributes • Cardinality (minimum and maximum)
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 ?
Relationships • Relationship type • Set of meaningful associations among entity types. • Relationship occurrence • Uniquely identifiable association, which includes one occurrence from each participating entity type.
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
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 (*:*)
Staff Manages Branch Newspaper & propertyForRent
Special Entity/Attribbutes • Other important terminology weak entity multi-valued attribute
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.
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.
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.
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
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
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.
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)
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)
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
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
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
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.
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.
End of Lecture End Of Today’s Lecture.