Lecture Notes Data Modeling and the EntityRelationship Model

Lecture Notes Data Modeling and the Entity-Relationship Model CS 475 - Spring 2002

Outline of Lecture • Overview of Database Modeling • Entity-Relationship Model – Constructs – Relationships – Constraints • Entity-Relationship Diagram • Chapter 11, Sec. 11. 1 - 11. 6 CS 475 - Spring 2002 2

Database Modeling • Analysis of what information the database must hold • Relationships among components of that information • Database schema is specified in one of several languages or notations suitable for expressing designs • Next, design is committed to a form which can be input to a DBMS DB takes Object-Oriented on physical existence DBMS ODL, UML, etc. • Design notation in this course is Entity Ideas Relational Relationship (E/R) model Relations DBMS E/R CS 475 - Spring 2002 3

Entity-Relationship Model Principal building blocks: – Entity: Real-world object, concept, or event – Entity type/set: Collection of entities of the same type (same properties); sets don’t have to be disjoint – Attribute: Values describing some property of an entity • simple vs. composite • single-valued vs. multi-valued • stored vs. derived • Null – Relationship: Association between two or more entity sets – Relationship type/set: Collection of relationships of the same type CS 475 - Spring 2002 4

Examples - “Movie Database” • Entity: – Star • Attributes: – – Name: “Harrison Ford” (simple, single-valued, stored) Address: “ 123 Main Str. , LA, CA” (complex) Birthdate: “ 1 -1 -50” (simple) Age: 50 (could be derived from Birthdate and current date) • Entity Type: – Stars (Name, Address, Birthdate, Age) • Entity Occurrence – a uniquely identifiable object of an entity type CS 475 - Spring 2002 5

Entity Type in ERD STAR Represented by a rectangle with the entity name in it CS 475 - Spring 2002 6

Attributes • Attribute domain - set of allowable values • Simple or Composite surname simple address composite (street, city, state) • Single valued / multi-valued course_number contact_info - (phone, e-mail, cell, etc) • derived - may be generated from a set of attribute values, possibly from different entities. CS 475 - Spring 2002 7

Relationships • Define associations among entities • Grouped together into relationships sets (types) • Example: – Relationship type: Stars-in between Movies and Stars • Can be visualized as a table • Each entry is a relationship occurrence CS 475 - Spring 2002 Movies Stars Total Recall A. Schwarzenegger Total Recall Sharon Stone Basic Instinct Sharon Stone Relationship Type Relationships 8

ERD Relationship Stars in Movie Represented by a diamond shape containing the relationship name CS 475 - Spring 2002 9

Relationships Cont’d • Degree of a relationship type is the number of participating entity types – binary, ternary, n-ary • Example: – Entities: Stars, Movies, Studios – Ternary Relationship Set: Contracts • Each entity type that participates in a relationship type plays a role in the relationship – Marked by (optional) name – Entity type may play multiple roles in a relationship type – Ex. : Relationship type Sequel-of between Movies CS 475 - Spring 2002 and itself (Movies) 10

Relationships Cont’d • Relationships can have attributes • Example: Relationship type Contracts between Stars and Studios for Movies • Might want to record salary associated with each contract • Not necessary to place attributes on relationships; instead, can invent new entity type whose entities have the attributes ascribed to relationship CS 475 - Spring 2002 11

Constraints on Relationships • Limit the possible combinations of entities that participate in the relationship type – Derived from real-world scenario which is being modeled • Multiplicity (Cardinality ratio) and participation constraints • Cardinality ratio specifies the number of relationship occurrences an entity can participate in – 1: 1, 1: N, N: 1, N: M CS 475 - Spring 2002 12

Mapping Cardinalities • Studio-Owns between Studio and Movies is 1: N • Stars-in between Stars and Movies is M: N • Teaches between Professors and Students is M: N • Manages between staff and Branch is 1: 1 CS 475 - Spring 2002 13

Relationship Constraints Cont’d Existence Dependency • Existence of some entity x from entity type X depends on existence of some entity y from entity type Y – x is said to be existence dependent on y – if y is deleted, so is x • Example: – Entity type Loans and Payments with relationship Loan-payment (1: N from Loans to Payments) – Payment entities are existence dependent on loan entities CS 475 - Spring 2002 14

Strong and Weak Entities • Strong Entity type An entity type that is not existence-dependent on some other entity type. • Weak Entity type an entity type that is existence-dependent on some other entity type – two rectangles CS 475 - Spring 2002 15

Relationship Constraints Cont’d Participation constraint • Participation of an entity type E in relationship type R: total or partial – Mandatory (Total) if every entity in E participates in at least one relationship in R – Optional (Partial) if only some entities in E participate in R – Represented by the min value in a min-max pair • In previous example, participation of Payment entities in the relationship type Loan-payments is total CS 475 - Spring 2002 16

ER Diagrams - Notation. . . Entity Set Role Name Composite Attribute Relationship Set 1: n Attribute E 1 Multivalued E 1 R E 2 N Total participation of E 2 in R 1: N Relationship between E 1 and E 2 in R E 2 Derived CS 475 - Spring 2002 17