Database FundamentalsIS 380 Entity Relationship Diagram Chapter 4

Database Fundamentals-IS 380 Entity Relationship Diagram Chapter 4 : Lecture Notes for Asela M. Thomason IS 380, A. M. Thomason 1

Topic Objectives • Understand use the basic constructs of entity modeling: – Entity – Relationship – Attribute • Additional concepts: – – Subtypes/Super-types Composite, Associative, Recursive, Exclusive, etc. Optional/Mandatory Cardinality IS 380, A. M. Thomason 2

Data Model Components Entity Relationship Diagram Data Dictionary Data Model Business Data Documentation i. e. , policy, constraints, etc. Decision Log Model Set Integration IS 380, A. M. Thomason 3

What is Entity Modeling? • Entity modeling is a formal process of describing data in terms of entities, attributes, and relationships. IS 380, A. M. Thomason 4

What is a Business Rule? • A business policy is a required course of action shown to guide decisions. • A business rule is a business condition under which: – Data items are created, related, and maintained. – Business processes are executed. IS 380, A. M. Thomason 5

What is an Entity? • An entity is a business object important to the organization and describes a person, place, thing, or event about which data is stored. – May refer to a tangible object in the real world, such as CUSTOMER or PRODUCT – May refer to an intangible business concept, such as ORDER or INVOICE. IS 380, A. M. Thomason 6

Diagramming Entities CUSTOMER IS 380, A. M. Thomason 7

Naming Entities Data Entity Name Noun (singular) Adjective (optional) IS 380, A. M. Thomason 8

Entity Definitions • A good entity definition says only what the entity is. • It excludes how the entity is used. IS 380, A. M. Thomason 9

Entity Examples Order Processing CUSTOMER ORDER PRODUCT SALESPERSON INVOICE Banking BANK HOUSEHOLD CUSTOMER ACCOUNT Purchasing VENDOR PART PURCHASE ORDER INVOICE PURCHASING AGENT Project Management PROJECT CLIENT EMPLOYEE IS 380, A. M. Thomason 10

Entity Type Instances Entity Instance Customer # N Customer # 5 Customer # 4 Customer # 3 Customer # 2 Customer # 1 Entity Type CUSTOMER IS 380, A. M. Thomason 11

Entity Type Vs. Entity Instance TYPE INSTANCE Data Modeling COURSE Sybase Concepts Process Dynamic Modeling IS 380, A. M. Thomason 12

Identify Entity Relationships EMPLOYEE is assigned to is staffed by IS 380, A. M. Thomason PROJECT 13

Identify Entity Relationships EMPLOYEE TEACHER is assigned to is staffed by drives is driven by teaches is taught by IS 380, A. M. Thomason PROJECT VEHICLE STUDENT 14

Relationships Should Be Meaningful DESIGNER fashions is designed by wears is worn by IS 380, A. M. Thomason GARMENT 15

Diagramming Relationships Read left to right above the line (CUSTOMER owns INSURANCE POLICY) CUSTOMER owns is owned by INSURANCE POLICY Read right to left below the line (INSURANCE POLICY is owned by CUSTOMER) CUSTOMER is owned by owns INSURANCE POLICY Clockwise Convention Holds When Rotated 90 Degrees IS 380, A. M. Thomason 16

Types of Connectivity A B One-to-one Relationship C D One-to-many Relationship (1: M), or (M: 1) many-to-one E F Many-to-many Relationship (M: N) IS 380, A. M. Thomason (1: 1) 17

Connectivity – One-to-one INSTRUCTOR maintains is maintained by CURRICULUM • An instructor maintains one curriculum • A curriculum is maintained by one instructor. IS 380, A. M. Thomason 18

Connectivity – One-to-many COURSE is offered by offers CLASS • A course is offered by many classes • A class offers one course. IS 380, A. M. Thomason 19

Connectivity – Many-to-many CURRICULUM consists of is part of COURSE • A curriculum consists of many courses • A course is part of many curriculum. IS 380, A. M. Thomason 20

Maximum and Minimum Instances Relationship Participation: Optional/Mandory ORDER is placed by places Maximum = Many Minimum = 0 Cardinality CUSTOMER Maximum = Many Minimum = 0 ( min. , max. ) (0, 10) Maximum = 10 Minimum = 0 IS 380, A. M. Thomason 21

Exercise – Reading Maximums & Minimums has scheduled COURSE consists of is scheduled for CLASS is taught by teaches INSTRUCTOR is part of is maintained by CURRICULUM maintains IS 380, A. M. Thomason 22

Exercise – Reading Cardinality casts MOVIE is made into acts in is filmed from ACTOR teaches learns from DRAMA TEACHER SCRIPT IS 380, A. M. Thomason 23

Possible Solution Identifying Maximums and Minimums casts MOVIE ACTOR acts in is made into is filmed from teaches learns from DRAMA TEACHER SCRIPT IS 380, A. M. Thomason 24

Naming Attributes Attribute Name Qualifier Class IS 380, A. M. Thomason 25

Identifying Attributes What are the attributes of the following: “Data Modeling is a three-day course. ” IS 380, A. M. Thomason 26

Exercise – Identifying Maximums and Minimums MOVIE ACTOR SCRIPT DRAMA TEACHER IS 380, A. M. Thomason 27

Assigning Attributes To determine where an attribute lives, ask: “What primary key determines the value of this attribute? ” OR “What entity does this attribute describe? ” IS 380, A. M. Thomason 28

Choosing Primary Keys EMPLOYEE: PRODUCT: CUSTOMER: SIN CHECKING ACCOUNT: IS 380, A. M. Thomason 29

Entity Identifiers COURSE Title Department Duration INSTRUCTOR is taught by Name teaches • A Key is one or more attributes that will uniquely identify an instance of an entity. IS 380, A. M. Thomason 30

Primary Key Guidelines • Identify all candidate keys • Select one to be the primary key • If no good candidate exists, a non-intelligent key can be generated (Customer ID, Order Number). IS 380, A. M. Thomason 31

Foreign Keys Examples EMPLOYEE: Foreign Key Primary Key EMPLOYEE # NAME ADDRESS JOB CODE DEPARTMENT: DEPARTMENT # EMPLOYEE DEPARTMENT Primary Key DEPARTMENT # DEPARTMENT NAME FIXED ASSET: Foreign Key Primary Key INVENTORY # DESCRIPTION ORIGINAL COST FIXED ASSET DEPARTMENT # IS 380, A. M. Thomason 32

Defining Foreign Keys COURSE Course ID (PK) consists of has scheduled is scheduled for CLASS Class ID (PK) is part of CURRICULUM is maintained by Curriculum ID (PK) maintains IS 380, A. M. Thomason INSTRUCTOR Instructor ID (PK) 33

Defining Relationship Instances has scheduled COURSE CLASS is scheduled for Class Name (PK) Course ID (FK) Course ID (PK) IS 380, A. M. Thomason 34

Resolving Many-to-Many Relationships COURSE Course ID (PK) consists of has scheduled is scheduled for CLASS Class ID (PK) is part of CURRICULUM is maintained by Curriculum ID (PK) maintains IS 380, A. M. Thomason INSTRUCTOR Instructor ID (PK) 35

Why Is It Important to Resolve Many-tomany Relationships? is assigned to EMPLOYEE: PROJECT: Primary Key EMPLOYEE # E 2 E 3 E 7 PROJECT has assigned NAME MCELREATH TAMMI SAVIANO PROJ # P 1 P 2 P 3 IS 380, A. M. Thomason DESCRIPTION ORDER PROCESSING MORTAGE LOANS PAYROLL 36

How are Many-to-many Relationships Resolved? is assigned to EMPLOYEE Primary key: Employee# PROJECT has assigned ASSIGNMENT PROJECT Primary key: Employee# Project# Primary key: Project# Associative Entity (Composite Entity) IS 380, A. M. Thomason 37

Relational Table View EMPLOYEE: Primary Key EMPLOYEE # E 2 E 3 E 7 ASSIGNMENT: PROJECT: NAME EMPLOYEE MCELREATH TAMMI SAVIANO Primary Key EMP # PROJ # START DATE HOURS E 2 E 3 E 4 E 7 P 1 P 2 P 1 P 3 P 2 02/89 01/90 06/89 11/89 07/89 100 50 500 75 10 ASSIGNMENT Primary Key PROJ # P 1 P 2 P 3 DESCRIPTION PROJECT ORDER PROCESSING MORTAGE LOANS PAYROLL IS 380, A. M. Thomason 38

What is a Subtype? LOAN COMMERCIAL LOAN CUSTOMER Subtype (Child) RESIDENTIAL LOAN COMMERCIAL CUSTOMER RESIDENTIAL CUSTOMER Supertype (Parent) IS 380, A. M. Thomason 39

Entity Subtyping Rules • Entity subtypes are subdivisions of an entity that represent the same object as the original entity. BANK ACCOUNT OR is a CHECKING ACCOUNT is a SAVINGS ACCOUNT IS 380, A. M. Thomason SAVINGS ACCOUNT 40

Exercise - Subtyping • Subtypes and draw each of the following entities once by life cycle state and once by variety: – EMPLOYEE – INSURANCE POLICY – FAMILY MEMBER IS 380, A. M. Thomason 41

Possible Solution - Subtyping VARIETY LIFE CYCLE STATE EMPLOYEE INSURANCE POLICY FAMILY MEMBER MANAGEMENT EMPLOYEE TERM LIFE PARENT UNION EMPLOYEE VARIABLE LIFE CHILD EMPLOYEE INSURANCE POLICY FAMILY MEMBER ACTIVE EMPLOYEE ACTIVE POLICY ADULT RETIRED EMPLOYEE LAPSED POLICY MINOR IS 380, A. M. Thomason 42

Subtyping Strategies An entity can be subtyped by: Life Cycle State STUDENT Variety BOOK MYSTERY GRADUATE ADVENTURE UNDERGRADUATE ADVENTURE IS 380, A. M. Thomason 43

When is Subtyping Useful? • When the business views subsets of an entity differently; for instance, when business policies are different for different subsets. • When only certain subsets of an entity have a relationship with another entity. • When the cardinality between two entities is different for one or more subsets. • When the attributes of an entity vary significantly from subset to subset. IS 380, A. M. Thomason 44

Entity Subtypes Example OWNER DOCK is owned by is accommodating owns is parked in VEHICLE is moored at contains GARAGE BECOMES Parent (Supertype) OWNER is owned by is accommodating is moored at owns VEHICLE BOAT DOCK Child (Subtype) AUTO IS 380, A. M. Thomason is parked in contains GARAGE 45

Resolving Entity Subtypes must be resolved before creating your Physical ERD, model. There are 3 different options: Option 1: Create dependent entities and preserve the parent entity. ( a dependent entity contains the primary key of another entity, or has an existence dependency on that entity) BOAT AUTO IS 380, A. M. Thomason VEHICLE 46

Resolving Entity Subtypes Option 2: Create an entity for every subtype and eliminate the parent. The vehicle information will be duplicated in auto and boat. BOAT IS 380, A. M. Thomason AUTO 47

Resolving Entity Subtypes Option 3: Create an entity that will store all the information for both the supertype and subtype. VEHICLE IS 380, A. M. Thomason 48

Data Model Types Conceptual (First draft) Logical Physical (Access/Oracle) Note: These three models do not match the Database book in class. Similar but not exact. IS 380, A. M. Thomason 49

Why is Conceptual Entity Modeling Important? • It identifies the high-level data requirements of each major business function. • It portrays the business view of the data. • It illustrates important entity relationships. • It forces you to question and crystallize business policy. IS 380, A. M. Thomason 50

Characteristics of a Conceptual Entity Model • • Shows Only Important Entities Based on Partial Understanding Includes Subtypes Allows Many-to-many Relationships Shows Primary Keys Shows Few or No Non-key Attributes Is Independent of Physical Constraints IS 380, A. M. Thomason 51

Developing the Conceptual Entity Model • Identify the major entities • Define the relationships between the major entities • Define entity keys IS 380, A. M. Thomason 52

Conceptual Vs. Logical – What’s the Difference? Conceptual Entity Model Logical Entity Model Scope Business Area Development Release Entities Major Entities and Subtypes All Entities, with Subtypes Resolved Relationships High-level, M: N Detailed with M: N Resolved Keys Primary, Some Foreign All Keys and RI Rules Attributes Few Attributes Non-key All Attributes Normalized IS 380, A. M. Thomason 53

Characteristics of a Logical Entity Model • • Shows All Entities Resolves Subtypes Resolves All Many-to-many (M: N) Relationships Identifies and Defines All Attributes May Identify All Foreign Keys Is in Third Normal Form Identifies Domains Is Independent of Physical Constraints IS 380, A. M. Thomason 54

What Information is Available? Conceptual Entity Model Logical Entity Model • Detailed Entities and Relationships • Attributes • Statistics and Characteristics IS 380, A. M. Thomason 55