Download Normal Forms-Database System Concepts-Lecture 17 Slides-Computer Science and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Lecture 15 of 42
Wednesday. 27 September 2006
William H. Hsu Department of Computing and Information Sciences, KSU
KSOL course page: http://snipurl.com/va Course web site: http://www.kddresearch.org/Courses/Fall-2006/CIS Instructor home page: http://www.cis.ksu.edu/~bhsu
Reading for Next Class: First half of Chapter 7, Silberschatz et al. , 5 th^ edition
Normal Forms
Notes: E-R and Exam 1 Review
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Relationship Sets with Attributes:
Review
Relationship Sets with Attributes:
Review
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Weak Entity Sets:
Review
Weak Entity Sets:
Review
z An entity set that does not have a primary key is referred to as a weak entity set. z The existence of a weak entity set depends on the existence of a identifying entity set it must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set Identifying relationship depicted using a double diamond z The discriminator ( or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set. z The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set’s discriminator.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Weak Entity Sets (Cont.)Weak Entity Sets (Cont.)
z We depict a weak entity set by double rectangles. z We underline the discriminator of a weak entity set with a dashed line. z payment_number – discriminator of the payment entity set z Primary key for payment – ( loan_number, payment_number )
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Extended E-R Features: Specialization Extended E-R Features: Specialization
z Top-down design process; we designate subgroupings within an entity set that are distinctive from other entities in the set. z These subgroupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set. z Depicted by a triangle component labeled ISA (E.g. customer “is a” person ). z Attribute inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Specialization ExampleSpecialization Example
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Extended ER Features: Generalization Extended ER Features: Generalization
z A bottom-up design process – combine a number of entity sets that share the same features into a higher-level entity set. z Specialization and generalization are simple inversions of each other; they are represented in an E-R diagram in the same way. z The terms specialization and generalization are used interchangeably.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Specialization and Generalization (Cont.) Specialization and Generalization (Cont.)
z Can have multiple specializations of an entity set based on different features. z E.g. permanent_employee vs. temporary_employee , in addition to officer vs. secretary vs. teller z Each particular employee would be a member of one of permanent_employee or temporary_employee , and also a member of one of officer , secretary , or teller z The ISA relationship also referred to as superclass - subclass relationship
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Aggregation Aggregation
Consider the ternary relationship works_on , which we saw earlier Suppose we want to record managers for tasks performed by an employee at a branch
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Aggregation (Cont.) Aggregation (Cont.)
z Relationship sets works_on and manages represent overlapping information Every manages relationship corresponds to a works_on relationship However, some works_on relationships may not correspond to any manages relationships Ö So we can’t discard the works_on relationship z Eliminate this redundancy via aggregation Treat relationship as an abstract entity Allows relationships between relationships Abstraction of relationship into new entity z Without introducing redundancy, the following diagram represents: An employee works on a particular job at a particular branch An employee, branch, job combination may have an associated manager
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
E-R Diagram With AggregationE-R Diagram With Aggregation
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
E-R Design DecisionsE-R Design Decisions
z The use of an attribute or entity set to represent an object. z Whether a real-world concept is best expressed by an entity set or a relationship set. z The use of a ternary relationship versus a pair of binary relationships. z The use of a strong or weak entity set. z The use of specialization/generalization – contributes to modularity in the design. z The use of aggregation – can treat the aggregate entity set as a single unit without concern for the details of its internal structure.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Summary of Symbols (Cont.) Summary of Symbols (Cont.)
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Reduction to Relation Schemas Reduction to Relation Schemas
z Primary keys allow entity sets and relationship sets to be expressed uniformly as relation schemas that represent the contents of the database. z A database which conforms to an E-R diagram can be represented by a collection of schemas. z For each entity set and relationship set there is a unique schema that is assigned the name of the corresponding entity set or relationship set. z Each schema has a number of columns (generally corresponding to attributes), which have unique names.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Representing Entity Sets as SchemasRepresenting Entity Sets as Schemas
z A strong entity set reduces to a schema with the same
attributes.
z A weak entity set becomes a table that includes a column
for the primary key of the identifying strong entity set
payment =
( loan_number, payment_number, payment_date,
payment_amount )
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Representing Relationship Sets as SchemasRepresenting Relationship Sets as Schemas
z A many-to-many relationship set is represented as a
schema with attributes for the primary keys of the two
participating entity sets, and any descriptive attributes
of the relationship set.
z Example: schema for relationship set borrower
borrower = ( customer_id, loan_number )
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
UML UML
z UML : Unified Modeling Language z UML has many components to graphically model different aspects of an entire software system z UML Class Diagrams correspond to E-R Diagram, but several differences.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Summary of UML Class Diagram NotationSummary of UML Class Diagram Notation
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
UML Class Diagrams (Cont.)UML Class Diagrams (Cont.)
z Entity sets are shown as boxes, and attributes are shown within the box, rather than as separate ellipses in E-R diagrams. z Binary relationship sets are represented in UML by just drawing a line connecting the entity sets. The relationship set name is written adjacent to the line. z The role played by an entity set in a relationship set may also be specified by writing the role name on the line, adjacent to the entity set. z The relationship set name may alternatively be written in a box, along with attributes of the relationship set, and the box is connected, using a dotted line, to the line depicting the relationship set. z Non-binary relationships drawn using diamonds, just as in ER diagrams
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
UML Class Diagram Notation (Cont.)UML Class Diagram Notation (Cont.)
*Note reversal of position in cardinality constraint depiction *Generalization can use merged or separate arrows independent of disjoint/overlapping
overlapping
disjoint
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Combine Schemas? Combine Schemas?
z Suppose we combine borrow and loan to get bor_loan = ( customer_id , loan_number , amount ) z Result is possible repetition of information (L-100 in example below)
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
A Combined Schema Without RepetitionA Combined Schema Without Repetition
z Consider combining loan_branch and loan loan_amt_br = ( loan_number , amount , branch_name ) z No repetition (as suggested by example below)
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
What About Smaller Schemas?What About Smaller Schemas?
z Suppose we had started with bor_loan. How would we know to split up ( decompose ) it into borrower and loan? z Write a rule “if there were a schema ( loan_number, amount ), then loan_number would be a candidate key” z Denote as a functional dependency : loan_number → amount z In bor_loan , because loan_number is not a candidate key, the amount of a loan may have to be repeated. This indicates the need to decompose bor_loan. z Not all decompositions are good. Suppose we decompose employee into employee1 = ( employee_id , employee_name ) employee2 = ( employee_name , telephone_number , start_date ) z The next slide shows how we lose information -- we cannot reconstruct the original employee relation -- and so, this is a lossy decomposition.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
A Lossy Decomposition A Lossy Decomposition
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Goal — Devise a Theory for the FollowingGoal — Devise a Theory for the Following
z Decide whether a particular relation R is in “good” form. z In the case that a relation R is not in “good” form, decompose it into a set of relations { R 1 , R 2 , ..., R (^) n } such that each relation is in good form the decomposition is a lossless-join decomposition z Our theory is based on: functional dependencies multivalued dependencies
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Functional Dependencies Functional Dependencies
z Constraints on the set of legal relations. z Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. z A functional dependency is a generalization of the notion of a key.
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Functional Dependencies (Cont.)Functional Dependencies (Cont.)
z Let R be a relation schema
α ⊆ R and β ⊆ R
z The functional dependency
holds on R if and only if for any legal relations r (R), whenever any two tuples t 1 and t 2 of r agree on the attributes α, they
also agree on the attributes β. That is,
t 1 [α] = t 2 [α] ⇒ t 1 [ β ] = t 2 [ β ]
z Example: Consider r (A ,B ) with the following instance of r.
z On this instance, A → B does NOT hold, but B → A does hold.
1 4 1 5 3 7
Computing & Information Sciences CIS 560: Database System Concepts Wednesday, 27 Sep 2006 Kansas State University
Functional Dependencies (Cont.)Functional Dependencies (Cont.)
z K is a superkey for relation schema R if and only if K → R z K is a candidate key for R if and only if K → R , and for no α ⊂ K, α → R z Functional dependencies allow us to express constraints that cannot be expressed using superkeys. Consider the schema: bor_loan = ( customer_id, loan_number, amount ). We expect this functional dependency to hold: loan_number → amount but would not expect the following to hold: amount → customer_name
