CS157A Lecture 4 ER Model 2Weak Entity SetsWeak Entity Sets (Cont.)Slide 4More Weak Entity Set ExamplesSpecializationSpecialization ExampleGeneralizationSpecialization and Generalization (Contd.)Design Constraints on a Specialization/GeneralizationDesign Constraints on aSpecialization/Generalization (Contd.)AggregationAggregation (Cont.)E-R Diagram With AggregationE-R Design DecisionsE-R Diagram for a Banking EnterpriseHow about doing another ER design interactively on the board?Summary of Symbols Used in E-R NotationSummary of Symbols (Cont.)Alternative E-R NotationsUMLSummary of UML Class Diagram NotationUML Class Diagrams (Contd.)UML Class Diagram Notation (Cont.)Slide 25Reduction of an E-R Schema to TablesRepresenting Entity Sets as TablesComposite and Multivalued AttributesRepresenting Weak Entity SetsRepresenting Relationship Sets as TablesRedundancy of TablesRedundancy of Tables (Cont.)Representing Specialization as TablesRepresenting Specialization as Tables (Cont.)Relations Corresponding to AggregationRelations Corresponding to Aggregation (Cont.)E-R Diagram for Exercise 2.10E-R Diagram for Exercise 2.15E-R Diagram for Exercise 2.22Slide 40Existence DependenciesCS157A Lecture 4CS157A Lecture 4ER Model 2ER Model 2Prof. Sin-Min LeeDepartment of Computer ScienceSan Jose State University©Silberschatz, Korth and Sudarshan2.2Database System ConceptsWeak Entity SetsWeak Entity SetsAn entity set that does not have a primary key is referred to as a weak entity set.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 setIdentifying relationship depicted using a double diamondThe 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.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.©Silberschatz, Korth and Sudarshan2.3Database System ConceptsWeak Entity Sets (Cont.)Weak Entity Sets (Cont.)We depict a weak entity set by double rectangles.We underline the discriminator of a weak entity set with a dashed line.payment-number – discriminator of the payment entity set Primary key for payment – (loan-number, payment-number)©Silberschatz, Korth and Sudarshan2.4Database System ConceptsWeak Entity Sets (Cont.)Weak Entity Sets (Cont.)Note: the primary key of the strong entity set is not explicitly stored with the weak entity set, since it is implicit in the identifying relationship.If loan-number were explicitly stored, payment could be made a strong entity, but then the relationship between payment and loan would be duplicated by an implicit relationship defined by the attribute loan-number common to payment and loan©Silberschatz, Korth and Sudarshan2.5Database System ConceptsMore Weak Entity Set ExamplesMore Weak Entity Set ExamplesIn a university, a course is a strong entity and a course-offering can be modeled as a weak entityThe discriminator of course-offering would be semester (including year) and section-number (if there is more than one section)If we model course-offering as a strong entity we would model course-number as an attribute. Then the relationship with course would be implicit in the course-number attribute©Silberschatz, Korth and Sudarshan2.6Database System ConceptsSpecializationSpecializationTop-down design process; we designate subgroupings within an entity set that are distinctive from other entities in the set.These subgroupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set.Depicted by a triangle component labeled ISA (E.g. customer “is a” person).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.©Silberschatz, Korth and Sudarshan2.7Database System ConceptsSpecialization ExampleSpecialization Example©Silberschatz, Korth and Sudarshan2.8Database System ConceptsGeneralizationGeneralizationA bottom-up design process – combine a number of entity sets that share the same features into a higher-level entity set.Specialization and generalization are simple inversions of each other; they are represented in an E-R diagram in the same way.The terms specialization and generalization are used interchangeably.©Silberschatz, Korth and Sudarshan2.9Database System ConceptsSpecialization and Generalization Specialization and Generalization (Contd.)(Contd.)Can have multiple specializations of an entity set based on different features. E.g. permanent-employee vs. temporary-employee, in addition to officer vs. secretary vs. tellerEach particular employee would be a member of one of permanent-employee or temporary-employee, and also a member of one of officer, secretary, or tellerThe ISA relationship also referred to as superclass - subclass relationship©Silberschatz, Korth and Sudarshan2.10Database System ConceptsDesign Constraints on a Design Constraints on a Specialization/GeneralizationSpecialization/GeneralizationConstraint on which entities can be members of a given lower-level entity set.condition-definedE.g. all customers over 65 years are members of senior-citizen entity set; senior-citizen ISA person.user-definedConstraint on whether or not entities may belong to more than one lower-level entity set within a single generalization.Disjointan entity can belong to only one lower-level entity setNoted in E-R diagram by writing disjoint next to the ISA triangleOverlappingan entity can belong to more than one lower-level entity set©Silberschatz, Korth and Sudarshan2.11Database System ConceptsDesign Constraints on Design Constraints on aSpecialization/Generalization (Contd.)aSpecialization/Generalization (Contd.)Completeness constraint -- specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization.total : an entity must belong to one of the lower-level entity setspartial: an entity need not belong to one of the lower-level entity sets©Silberschatz, Korth and
View Full Document