2. Conceptual Modeling using the Entity …

1 ECS-165A WQ 11152. Conceptual Modeling using theEntity- relationship ModelContents Basic concepts: entities and Entity types, attributes and keys,relationships and relationship types Entity - relationship schema (aka ER diagram) Constraints on relationship types Design choices Enhanced Entity - relationship model features Steps in designing an ER schema Translation of an ER schema to tablesDept. of Computer ScienceUC Davis2. Entity - relationship ModelECS-165A WQ 1116 What does Conceptual Design include?Ideas High-leveldesign Relationaldatabaseschema RelationalDBMS Entity - relationship model is used in the Conceptual design ofa database (+ Conceptual level, Conceptual schema) Designisindependentofallphysicalconsider ations(DBMS, OS.)

2 Questions that are addressed during Conceptual design: What are the entities and relationships of interest (mini-world)? What information about entities and relationships amongentities needs to be stored in the database? What are the constraints (or business rules) that (must)hold for the entities and relationships? A database schema in the ER model can be representedpictorially( Entity -Relationsh ip diagram)Dept. of Computer ScienceUC Davis2. Entity - relationship ModelECS-165A WQ 1117 Entity Types, Entity Sets, Attributes and Keys Entity :real-world object or thing with an independentexistence and which is distinguishable from other are a person, car, customer, product, gene, booketc.

3 Attributes:an Entity is represented by a set of attributes(its descriptive properties), , name, age, salary, price values that describe each Entity become a major partof the data eventually stored in a database. With each attribute adomainis associated, , a set ofpermitted values for an domains areinteger,string,date, etc. Entity Type:Collection of entities that all have the sameattributes, , persons, cars, customers etc. Entity Set:Collection of entities of a particular Entity type atany point in time; Entity set is typically referred to using thesame name as Entity of Computer ScienceUC Davis2.

4 Entity - relationship ModelECS-165A WQ 1118 Key attributes of an Entity Type Entities of an Entity type need to be distinguishable. Asuperkeyof an Entity type is a set of one or more attributeswhose values uniquely determine each Entity in an Entity set. Acandidate keyof an Entity type is a minimal (in terms ofnumber of attributes) superkey. For an Entity type, several candidate keys may exist. Duringconceptual design, one of the candidate keys is selected to betheprimary keyof the Entity of Computer ScienceUC Davis2. Entity - relationship ModelECS-165A WQ 1119 Relationships, relationship Types, and relationship Sets relationship (instance):association among two or moreentities, , customer Smith orders product PC42 relationship Type:collection of similar relationshipsAnn-ary relationship typeRlinksnentity typesE1.

5 , relationship in a relationship setRof a relationship typeinvolves entitiese1 E1,..,en EnR {(e1,..,en)|e1 E1,..,en En}where(e1,..,en)is a relationship . Degree of a relationship :refers to the number of Entity typesthat participate in the relationship type (binary, ternary, .. ). Roles:The same Entity type can participate more than oncein a relationship labels clarify semantics of a relationship , , the way inwhich an Entity participates in a relationship .;recursive of Computer ScienceUC Davis2. Entity - relationship ModelECS-165A WQ 1120 relationship Attributes:A relationship type can haveattributes describing properties of a relationship .

6 Customer Smith ordered product PC42 on January 11,2005, for $2345 .These are attributes that cannot be associated withparticipating entities only, , they make only sense in thecontext of a relationship . Note that a relationship does not have key attributes! Theidentification of a particular relationship in a relationship setoccurs through the keys of participating of Computer ScienceUC Davis2. Entity - relationship ModelECS-165A WQ 1121 Example of an Entity - relationship DiagramoffersPriceSAddressChainCAddressC USTOMERS ordersAccountPRODUCTSSUPPLIERSQ uantityFNameLNameProdnameSNameCustomers- Suppliers-Products Entity - relationship Diagram Rectanglesrepresent Entity types Ellipsesrepresent attributes Diamondsrepresent relationship types Lineslink attributes to Entity types and Entity types torelationship types Primary keyattributes are underlined Empty Circleat the end of a line linking an attribute toan Entity type represents an optional (null)

7 Attribute (notmentioned in textbook)Not in the above diagram, but later in examples: Double Ellipsesrepresent multi-valued attributesDept. of Computer ScienceUC Davis2. Entity - relationship ModelECS-165A WQ 1122 Constraints on relationship TypesLimit the number of possible combinations of entities thatmay participate in a relationship set. There are two types ofconstraints:cardinality ratioandparticipation constraintsVery useful concept in describing binary relationship types. Forbinary relationships, the cardinality ratio must be one of thefollowing types: Many-To-Many(default)EMPLOYEES works inDEPARTMENTSM eaning: An employee can work in many departments ( 0),and a department can have several employees Many-To-OneDEPARTMENTS works inEMPLOYEESM eaning: An employee can work in at most one department( 1), and a department can have several of Computer ScienceUC Davis2.

8 Entity - relationship ModelECS-165A WQ 1123 One-To-ManyDEPARTMENTS works inEMPLOYEESM eaning: An employee can work in many departments ( 0),but a department can have at most one employee. One-To-OneDEPARTMENTS works inEMPLOYEESM eaning: An employee can work in at most one department,and a department can have at most one of Computer ScienceUC Davis2. Entity - relationship ModelECS-165A WQ 1124 Constraints on relationship Types (cont.)A many-one relationship type (and the counterpart one-many) isalso often called afunctional ratio of a relationship can affect the placement of arelationship attribute.

9 , in case of a many-one relationshiptype, one can place a relationship attribute at a participatingentity constraint:specifies whether the existence of anentitye Edepends on being related to another Entity via therelationship typeR. total: each entitye Emust participate in a relationship ,it cannot exist without that participation (total participationaka existence dependency).EMPLOYEES works inDEPARTMENTS partial: default; each entitye Ecan participate in arelationshipDept. of Computer ScienceUC Davis2. Entity - relationship ModelECS-165A WQ 1125 Instead of a cardinality ratio or participation constraint, moreprecisecardinality limits(akadegree constraintsin textbook) canbe associated with relationship types:(min , max )E111(min , max )22E2 REach entitye1 E1must participate in relationship setRatleastmin1and at mostmax1times (analogous fore2 E2).

10 Frequently used cardinalitiesRelationship(min1,max1)(min 2,max2)pictorial notationmany-to-many(0, )(0, )many-to-one(0,1)(0, )one-to-one(0,1)(0,1)Dept. of Computer ScienceUC Davis2. Entity - relationship ModelECS-165A WQ 1126 Design Choices for ER Conceptual DesignIt is possible to define entities and their relationships in a numberof different ways (in the same model!). Should a real-world concept be modeled as an Entity type,attribute, or relationship type?Is Address an attribute or an Entity type? Decision dependsupon the use one wants to make of address information. If oneis interested in the structure, , (City, Street, Zip-Code),Address must be modeled as an Entity type (or as acomplexattribute).