Zvi’s changes, if any, are marked in green, they are not copyrighted by the authors, and the authors are not responsible for themChapter 2: Entity-Relationship ModelPurpose of the ER Model and Basic ConceptsEntity SetsEntity Sets customer and loanAttributesComposite AttributesRelationship Sets!Relationship Sets (Cont.)Degree of a Relationship SetMapping CardinalitiesBinary Relationships and Their FunctionalityBinary Relationships and ... (Cont.)!Binary Relationships and ... (Cont.)Slide 15Slide 16Mapping Cardinalities affect ER DesignE-R DiagramsE-R Diagram With Composite, Multivalued, and Derived AttributesRelationship Sets with AttributesRolesCardinality ConstraintsOne-To-Many RelationshipMany-To-One RelationshipsMany-To-Many RelationshipParticipation of an Entity Set in a Relationship SetAlternative Notation for Cardinality LimitsKeysCandidate Keys!Keys for Relationship SetsE-R Diagram with a Ternary RelationshipBinary Vs. Non-Binary RelationshipsConverting Non-Binary Relationships to Binary FormDesign Issues (matters of taste)SpecializationSpecialization ExampleGeneralizationSpecialization and Generalization (Contd.)Design Constraints on a Specialization/GeneralizationDesign Constraints on a Specialization/Generalization (Contd.)AggregationAggregation (Cont.)E-R Diagram With AggregationE-R Diagram for a Banking Enterprise!From ER Diagram to Relations (optional)Slide 46Reduction of an E-R Schema to TablesRepresenting Entity Sets as TablesComposite and Multivalued AttributesRepresenting Relationship Sets as TablesRedundancy of TablesRepresenting Specialization as TablesRepresenting Specialization as Tables (Cont.)Slide 54Zvi’s changes, if any, are marked in Zvi’s changes, if any, are marked in green, they are not copyrighted by the green, they are not copyrighted by the authors, and the authors are not authors, and the authors are not responsible for themresponsible for them©Silberschatz, Korth and Sudarshan2.2Database System ConceptsChapter 2: Entity-Relationship ModelChapter 2: Entity-Relationship ModelEntity SetsRelationship SetsDesign Issues Mapping Constraints KeysE-R DiagramExtended E-R FeaturesDesign of an E-R Database SchemaReduction of an E-R Schema to Tables©Silberschatz, Korth and Sudarshan2.3Database System ConceptsPurpose of the ER Model and Basic Purpose of the ER Model and Basic ConceptsConceptsThe purpose of the Entity -- Relationship model, referred further to, as ER model is to provide a common, informal, and convenient model for communication between users and the DBA for the purpose of modeling the data's structure.This will be a preliminary stage towards defining the database by means of a formal modal, such as the relational model.The ER model, frequently employs ER diagrams, which are visual, pictorial descriptions of the relations between various data items.There are 3 basic concepts appearing in the original ER model, which has since been extended. •We will start with the simple version of the ER model, and then extend it further.©Zvi M. Kedem©Silberschatz, Korth and Sudarshan2.4Database System ConceptsEntity SetsEntity SetsA database can be modeled as:•a collection of entities,•relationship among entities.An entity is an object that exists and is distinguishable from other objects.Example: specific person, company, event, plantEntities have attributesExample: people have names and addressesAn entity set is a set of entities of the same type that share the same properties.Example: set of all persons, companies, trees, holidaysNote, that we could have 2 different “related” entity sets: (1) airplanes in Boeing catalog, (2) airplanes in an airport©Silberschatz, Korth and Sudarshan2.5Database System ConceptsEntity Sets Entity Sets customercustomer and and loanloancustomer-id customer- customer- customer- loan- amount name street city number©Silberschatz, Korth and Sudarshan2.6Database System ConceptsAttributesAttributesAn entity frequently has attributes (though in general cannot be represented by the set of its attributes), that is descriptive properties possessed by all members of an entity set.Example, where entities “can be described by attributes”: customer = (customer-id, customer-name, customer-street, customer-city)loan = (loan-number, amount)Domain – the set of permitted values for each attribute Attribute types:•Simple and composite attributes.•Single-valued and multi-valued attributes•E.g. multivalued attribute: phone-numbers•Derived attributes•Can be computed from other attributes•E.g. age, given date of birth©Silberschatz, Korth and Sudarshan2.7Database System ConceptsComposite AttributesComposite Attributes©Silberschatz, Korth and Sudarshan2.8Database System ConceptsRelationship SetsRelationship SetsA relationship is an association among several entities, but as we shall see later, some of the several entities could be the same entityExample:Hayes depositor A-102customer entity relationship set account entityA relationship set is a mathematical relation among n  2 entities, each taken from entity sets{(e1, e2, … en) | e1  E1, e2  E2, …, en  En}where (e1, e2, …, en) is a relationship•Example: (Hayes, A-102)  depositor©Silberschatz, Korth and Sudarshan2.9Database System Concepts!!Relationship Sets (Cont.)Relationship Sets (Cont.)An attribute can also be property of a relationship set.For instance, the depositor relationship set between entity sets customer and account may have the attribute access-dateNote that in general, the relationship does not need to be total©Silberschatz, Korth and Sudarshan2.10Database System ConceptsDegree of a Relationship SetDegree of a Relationship SetRefers to number of entity sets that participate in a relationship set.Relationship sets that involve two entity sets are binary (or degree two). Relationship sets may involve more than two entity sets. •E.g. Suppose employees of a bank may have jobs (responsibilities) at multiple branches, with different jobs at different branches. Then there is a ternary relationship set between entity sets employee, job and branch©Silberschatz, Korth and Sudarshan2.11Database System ConceptsMapping CardinalitiesMapping CardinalitiesExpress the number of entities to which another entity can be associated via a relationship set.Most useful in describing