CS 8630 Database Administration Dr Guimaraes 09 16 2008 Tuesday E R Diagrams and Normalization CS8630 Database Administration Dr Mario Guimaraes Class Will Start Momentarily E R Introduction CS 8630 Database Administration Dr Guimaraes Database design tool proposed by Peter Chen 1976 The real world is seen represented as entities relationships and attributes Example Example of an E R Diagram CS 8630 Database Administration Dr Guimaraes Components of E R CS 8630 Database Administration Dr Guimaraes Entity types Relationship types Attributes Cardinality minimum and maximum Many Different Notations CS 8630 Database Administration Dr Guimaraes Example of Entity CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes Staff PropertyForRent CS 8630 Database Administration Dr Guimaraes Newspaper propertyForRent Staff Manages Branch CS 8630 Database Administration Dr Guimaraes CS 8630 Database Administration Dr Guimaraes Multiplicity of Ternary Registers Relationship Special Entity Attribbutes CS 8630 Database Administration Dr Guimaraes Other important terminology weak entity multi valued attribute Generalization Specialization Ch 12 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 Example of Entity w Subclasses CS 8630 Database Administration Dr Guimaraes Subclass in Relationship CS 8630 Database Administration Dr Guimaraes Normalization CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 Steps in Normalization CS 8630 Database Administration Dr Guimaraes Normalization most used CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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 CS 8630 Database Administration Dr Guimaraes 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