4 Chapter 4 Entity Relationship ER Modeling Database

4 Chapter 4 Entity Relationship (ER) Modeling Database Systems: Design, Implementation, and Management, Seventh Edition, Rob and Coronel Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel

4 The Entity Relationship (ER) Model • ER model forms the basis of an ER diagram • ERD represents conceptual database as viewed by end user • ERDs depict database’s main components: – Entities – Attributes – Relationships Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 3

4 Entities • Refers to entity set and not to single entity occurrence • Corresponds to table and not to row in relational environment • In both Chen and Crow’s Foot models, entity is represented by rectangle containing entity’s name • Entity name, a noun, is usually written in capital letters Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 4

4 Attributes • Characteristics of entities • Sometimes referred to as properties • In Chen model, attributes are represented by ovals and are connected to entity rectangle with a line • Each oval contains the name of attribute it represents • In Crow’s Foot model, attributes are written in attribute box below entity rectangle Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 5

4 Attributes (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 6

4 Primary Keys • Underlined in the ERD • Key attributes are also underlined in frequently used table structure shorthand Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 8

The Entity Relationship (E-R) Model 4 • Classes of Attributes – A simple attribute cannot be subdivided. • Examples: Age, Sex, and Marital status – A composite attribute can be further subdivided to yield additional attributes. • Examples: – ADDRESS Street, City, State, Zip – PHONE NUMBER Area code, Exchange number – NAME last, first, Middle Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 11

4 The Attributes of the STUDENT Entity First Initial Last Name STU ID No longer a Figure STUDENT Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 12

The Entity Relationship (E-R) Model 4 • Classes of Attributes – A single-valued attribute can have only a single value. • Examples: – A person can have only one social security number. – A manufactured part can have only one serial number. – A student can only have one GPA – Multivalued attributes can have many values. • Examples: – A person may have several college degrees. – A household may have several phones with different numbers – Cars can have multiple colors • Multivalued attributes are shown by a double line connecting to the entity. Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 13

4 Multivalued Attributes (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 14

The Entity Relationship (E-R) Model 4 • Multivalued Attribute in Relational DBMS – relational DBMS cannot implement multivalued attributes. – Possible courses of action for the designer • create several new attributes, one for each of the original multivalued attribute’s components (Figure 4. 4). • Create a new entity composed of the original multivalued attribute’s components (Figure 4. 5). Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 15

4 Resolving Multivalued Attribute Problems (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 17

The Entity Relationship (E-R) Model 4 – A derived attribute is not physically stored within the database; instead, it is derived (calculated) by using an algorithm. • Example: AGE can be derived from the data of birth and the current date. – Could be stored in DB if you understand the trade-off and decide it is worth it Figure 4. 6 A Derived Attribute Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 18

4 Derived Attributes (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 19

The Entity Relationship (E-R) Model 4 • Cardinality – Cardinality expresses the specific number of entity occurrences associated with one occurrence of the related entity. Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 22 No longer a Figure: Connectivity and Cardinality in an ERD

4 Connectivity and Cardinality (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 23

4 Existence Dependence • Existence dependence – Exist in database only when it is associated with another related entity occurrence – Means FK cannot be null • Existence independence – Entity can exist apart from one or more related entities – Sometimes refers to such an entity as strong or regular entity Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 24

4 Relationship Strength • Weak (non-identifying) relationships – Exists if PK of related entity does not contain PK component of parent entity – (related PK is only the FK) • Strong (Identifying) Relationships – Exists when PK of related entity contains PK component of parent entity Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 25

4 Weak (Non-Identifying) Relationships Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 26

Weak (Non-Identifying) Relationships (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 4 27

4 Strong (Identifying) Relationships Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 28

The Entity Relationship (E-R) Model 4 • Weak Entities – A weak entity is an entity that • Is existence-dependent and • Has a primary key that is partially or totally derived from the parent entity in the relationship. • Hence the weak entity is the dependent entity involved in a strong (identifying) relationship – The existence of a weak entity is indicated by a double rectangle. (in Chen notation) – The weak entity inherits all or part of its primary key from its strong counterpart. Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 29

4 Weak Entities (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 30

4 Relationship Participation • Optional participation – One entity occurrence does not require corresponding entity occurrence in particular relationship • Mandatory participation – One entity occurrence requires corresponding entity occurrence in particular relationship Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 32

4 Relationship Participation (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 33

4 Relationship Participation (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 34

4 Relationship Participation (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 35

4 Relationship Participation (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 36

4 Relationship Degree • Indicates number of entities or participants associated with a relationship • Unary relationship – Association is maintained within single entity • Binary relationship – Two entities are associated • Ternary relationship – Three entities are associated Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 37

Three Degrees of Relationships Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 4 38

4 Relationship Degree (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 39

4 Relationship Degree (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 40

Recursive Relationships (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 4 42

4 Recursive Relationships (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 43

4 Recursive Relationships (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 44

4 Recursive Relationships (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 47

4 Composite Entities • Also known as bridge entities • Composed of primary keys of each of the entities to be connected • May also contain additional attributes that play no role in connective process Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 48

4 Composite Entities (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 50

4 Composite Entities (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 51

Developing an ER Diagram 4 • Database design is an iterative rather than a linear or sequential process • begins with a general narrative (problem description) • Iterative process – E-R model is created and presented for review. • process repeated until the end users and designers agree Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 52

4 Developing an ER Diagram (continued) • Building an ERD usually involves the following activities: – Create detailed narrative of organization’s description of operations – Identify business rules based on description of operations – Identify main entities and relationships from business rules – Develop initial ERD – Identify attributes and primary keys that adequately describe entities – Revise and review ERD Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 53

4 Database Design Challenges: Conflicting Goals • Database design must conform to design standards – avoidance of redundancy / protection against inconsistency • High processing speeds are often a top priority in database design – may lead to fewer tables than would be ideal design re: above • Quest for timely information might be focus of database design Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 67

4 End Chapter 4 Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 71