4 Module 4 Entity Relationship ER Modeling Module

4 Module 4 Entity Relationship (ER) Modeling Module #4: ENTITYRELATIONSHIP MODELLING/ DIAGRAMMING Prepared by: KIM GASTHIN M. CALIMQUIM I. T. Instructor Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 1

Module 4 Entity Relationship (ER) Modeling 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

Module 4 Entity Relationship (ER) Modeling 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

Module 4 Entity Relationship (ER) Modeling 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

Module 4 Entity Relationship (ER) Modeling 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

Module 4 Entity Relationship (ER) Modeling 4 Attributes (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 6

Module 4 Entity Relationship (ER) Modeling 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

Module 4 Entity Relationship (ER) Modeling 4 Identifiers (Primary Keys) • Underlined in the ERD. • Key attributes are also underlined in frequently used table structure shorthand. 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 8

Module 4 Entity Relationship (ER) Modeling 4 Composite Primary Keys (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 9

Module 4 Entity Relationship (ER) Modeling 4 Composite and Simple Attributes • Composite attribute can be subdivided. • Simple attribute cannot be subdivided. Single-Valued Attributes • Single-value attribute can have only a single value. Multivalued Attributes • Multivalued attributes can have many values. Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 10

Module 4 Entity Relationship (ER) Modeling 4 Multivalued Attributes (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 11

Module 4 Entity Relationship (ER) Modeling 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 12

Module 4 Entity Relationship (ER) Modeling 4 Resolving Multivalued Attribute Problems (cont'd) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 13

Module 4 Entity Relationship (ER) Modeling 4 Resolving Multivalued Attribute Problems (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 14

Module 4 Entity Relationship (ER) Modeling 4 Resolving Multivalued Attribute Problems (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 15

Module 4 Entity Relationship (ER) Modeling 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 16

Module 4 Entity Relationship (ER) Modeling 4 Derived Attributes (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 17

Module 4 Entity Relationship (ER) Modeling 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. • Relationship classification is difficult to establish if know only one side of the relationship. Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 18

Module 4 Entity Relationship (ER) Modeling 4 Connectivity and Cardinality • Connectivity: Used to describe the relationship classification, e. g. , 1: 1, 1: M, M: N • 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 19

Module 4 Entity Relationship (ER) Modeling 4 Connectivity and Cardinality (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 20

Module 4 Entity Relationship (ER) Modeling 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 21

Module 4 Entity Relationship (ER) Modeling 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 22

Module 4 Entity Relationship (ER) Modeling 4 Weak (Non-Identifying) Relationships Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 23

Module 4 Entity Relationship (ER) Modeling 4 Weak (Non-Identifying) Relationships (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 24

Module 4 Entity Relationship (ER) Modeling 4 Strong (Identifying) Relationships Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 25

Module 4 Entity Relationship (ER) Modeling 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 26

Module 4 Entity Relationship (ER) Modeling 4 Weak Entities (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 27

Module 4 Entity Relationship (ER) Modeling 4 Weak Entities (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 28

Module 4 Entity Relationship (ER) Modeling 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 29

Module 4 Entity Relationship (ER) Modeling 4 Relationship Participation (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 30

Module 4 Entity Relationship (ER) Modeling 4 Relationship Participation (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 31

Module 4 Entity Relationship (ER) Modeling 4 Relationship Participation (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 32

Module 4 Entity Relationship (ER) Modeling 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 or more entities are associated. Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 33

Module 4 Entity Relationship (ER) Modeling 4 Relationship Degree (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 34

Module 4 Entity Relationship (ER) Modeling 4 Relationship Degree (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 35

Module 4 Entity Relationship (ER) Modeling 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 36

Module 4 Entity Relationship (ER) Modeling 4 Recursive Relationships (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 37

Module 4 Entity Relationship (ER) Modeling 4 Recursive Relationships (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 38

Module 4 Entity Relationship (ER) Modeling 4 Recursive Relationships (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 39

Module 4 Entity Relationship (ER) Modeling 4 Recursive Relationships (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 40

Module 4 Entity Relationship (ER) Modeling 4 Recursive Relationships (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 41

Module 4 Entity Relationship (ER) Modeling 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 42

Module 4 Entity Relationship (ER) Modeling 4 Composite Entities (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 43

Module 4 Entity Relationship (ER) Modeling 4 Composite Entities (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 44

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram • Database design is iterative rather than linear or sequential process. • Iterative process: Based on repetition of processes and procedures. • 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 45

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) • 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 profe. ssors 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. – 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 46

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 47

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 48

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 49

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 50

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 51

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 52

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 53

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 54

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 55

Module 4 Entity Relationship (ER) Modeling 4 Developing an ER Diagram (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 56

Module 4 Entity Relationship (ER) Modeling 4 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 57

Module 4 Entity Relationship (ER) Modeling 4 Database Design Challenges: Conflicting Goals (cont’d) Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 58

Module 4 Entity Relationship (ER) Modeling 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. • 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 59

Module 4 Entity Relationship (ER) Modeling 4 • The DB should track homes and owners. A home has a unique home identifier, a street address, a city, a state, a zip, a number of bedrooms, a number of bathrooms, and square feet. A home is either owner-occupied or rented. An owner has a social security number, a name, an optional spouse name, a profession, an optional spouse profession. An owner can possess one or more homes. Each home has only one owner. Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 60

Module 4 Entity Relationship (ER) Modeling • • • The DB should track homes and owners. A home has a unique home identifier, a street address, a city, a state, a zip, a number of bedrooms, a number of bathrooms, and square feet. A home is either owner-occupied or rented. An owner has a social security number, a name, an optional spouse name, a profession, an optional spouse profession. An owner can possess one or more homes. Each home has only one owner. Agents represent owners in the sale of a home. An agent can list many homes but only one agent can list a home. An agent has a unique agent identifier, a name, a office identifier, and a phone number. When an owner agrees to list a home with an agent, a commission and selling price are determined. Transform the attribute office identifier into an entity type. Data about an office include the phone number, the manager name, and the address. Add a buyer entity type. A buyer entity type has a social security number, a name, a phone, preferences for the number of bedrooms and bathrooms, and a price range. An agent can work with many buyers, but a buyer works with only one agent. Create a generalization hierarchy to depict similarities between buyers and owners. Add an offer entity type. A buyer makes an offer on a home for a specified sales price. The offer starts on the submission date and time and expires on the specified date and time. A unique offer number identifies an offer. A buyer may submit multiple offers for the same home. 4 61

Module 4 Entity Relationship (ER) Modeling 4 • Add an offer entity type. A buyer makes an offer on a home for a specified sales price. The offer starts on the submission date and time and expires on the specified date and time. A unique offer number identifies an offer. A buyer may submit multiple offers for the same home. Database Systems: Design, Implementation, & Management, 7 th Edition, Rob & Coronel 62