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 In this chapter, you will learn: • The main characteristics of entity relationship components • How relationships between entities are defined and refined and how those relationships are incorporated into the database design process • How ERD components affect database design and implementation • That real-world database design often requires the reconciliation of conflicting goals Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 2

4 4. 1 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 • 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 Domains • Attributes have domain – Domain is attribute’s set of possible values • Attributes may share a domain Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 7

4 Identifiers (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

4 Composite Primary Keys • Primary keys ideally composed of only single attribute • Possible to use a composite key – Primary key composed of more than one attribute Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 9

4 Composite Primary Keys (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 10

4 Composite and Simple Attributes • Composite attribute can be subdivided • Simple attribute cannot be subdivided Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 11

4 Single-Valued Attributes • Single-value attribute can have only a single value Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 12

4 Multivalued Attributes • Multivalued attributes can have many values 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

4 Resolving Multivalued Attribute Problems • Although conceptual model can handle M: N relationships and multivalued attributes, you should not implement them in relational DBMS – Within original entity, create several new attributes, one for each of the original multivalued attribute’s components • Can lead to major structural problems in table – Create new entity composed of original multivalued attribute’s components 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 16

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

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

4 Derived Attributes • Attribute whose value may be calculated (derived) from other attributes • Need not be physically stored within database • Can be derived by using an algorithm Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 19

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

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

4 Relationships • Association between entities • Participants are entities that participate in a relationship • Relationships between entities always operate in both directions • Relationship can be classified as 1: M, 1: 1, M: N • Relationship classification is difficult to establish if know only one side of the relationship Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 22

4 Connectivity and Cardinality • Connectivity – Used to describe the relationship classification • Cardinality – Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity • Established by very concise statements known as business rules Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 23

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

4 Existence Dependence • Existence dependence – Exist in database only when it is associated with another related entity occurrence • 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 25

4 Relationship Strength • Weak (non-identifying) relationships – Exists if PK of related entity does not contain PK component of parent entity • 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 26

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

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

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

4 Weak Entities • Weak entity meets two conditions – Existence-dependent • Cannot exist without entity with which it has a relationship – Has primary key that is partially or totally derived from parent entity in relationship • Database designer usually determines whether an entity can be described as weak based on business rules Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 30

Weak Entities (continued) (1, 1) 4 (0, N) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 31

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

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 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 Participation (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 37

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 38

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

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

4 Recursive Relationships • Relationship can exist between occurrences of the same entity set • Naturally found within unary relationship Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 41

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

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

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

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

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

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

4 4. 2 Developing an ER Diagram • Database design is iterative rather than linear or sequential process • Iterative process – Based on repetition of processes and procedures 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 Developing an ER Diagram (continued) • Tiny College – Tiny College is divided into several schools • Each school is composed of several departments – Each department may offer courses – Each department may have many professors assigned to it – Each professor may teach up to four classes; each class is section of course – Student may enroll in several classes, but (s)he takes each class only once during any given enrollment period Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 54

4 Developing an ER Diagram (continued) • Tiny College (continued) – Each department has several students • Each student has only a single major and is associated with a single department – Each student has an advisor in his or her department • Each advisor counsels several students – The relationship between class is taught in a room and the room in the building Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 55

Developing an ER Diagram (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 4 56

4 Developing an ER Diagram (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 57

4 Developing an ER Diagram (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 58

4 Developing an ER Diagram (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 59

4 Developing an ER Diagram (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 60

4 Developing an ER Diagram (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 61

4 Developing an ER Diagram (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 62

4 Developing an ER Diagram (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 63

4 Developing an ER Diagram (continued) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 64

Developing an ER Diagram (continued) 4 M: N Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 65

4 Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 66

4 4. 3 Database Design Challenges: Conflicting Goals • Database design must conform to design standards • High processing speeds are often a top priority in database design • Quest for timely information might be focus of database design Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 67

Database Design Challenges: Conflicting Goals (continued)4 Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 68

4 Summary • Entity relationship (ER) model – Uses ERD to represent conceptual database as viewed by end user – ERM’s main components: • Entities • Relationships • Attributes – Includes connectivity and cardinality notations Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 69

4 Summary (continued) • Connectivities and cardinalities are based on business rules • In ERM, M: N relationship is valid at conceptual level • ERDs may be based on many different ERMs • Database designers are often forced to make design compromises Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 70

4 練習 • 請以 Crow’s Foot Model(請標示部份屬性)畫出符 合以下限制之 ER Diagram，注意需標示出其 connectivity、 cardinality及 是否為optional： • • • 一個課程(Course)可以開多個班(Class)，但也有可 能未開班 一個學生(Student)可以上多門班(Class)的課，但至 少需上一班的課 一個班(Class)至少需有 10 名學生(Student) 來上 Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 71