The Entity-Relationship Model (Chapter 3 Additional Notes – Part a)

1. The Entity-Relationship Model(Chapter 3 Additional Notes – Part a) Laku Chidambaram W.P. Wood Professor of MIS University of Oklahoma

2. Overview • Database Design: The E-R Model • Entities • Attributes • Relationships

3. General Design (Comprehensive Data Model) Conceptual Model (ERD) Blueprint (Database Schema) Logical Model (Relational) Aircraft (Database) Physical Reality (DBMS) Database Design Pilot (Order Entry) Engineer (A/C Receivable) Passenger (Inventory) External View

4. The Entity Relationship Diagram • An E-R diagram is a graphical representation of an organization’s data • Such data includes the people, places, objects, events, or concepts (along with their characteristics and relationships) that are relevant to an organization

5. Entities • Person, place, object, event, or concept about which the organization wishes to collect and maintain data • Nouns are used to describe entities • Examples: • Employee, Student, Nurse • City, State, Country • Sale, Registration, Account • Entity Type versus Entity Instance

6. Types of Entities • Strong Entity: can exist independently • Weak Entity: cannot exist without the “owner” entity (with which it has an identifying relationship) • Has a primary key that is derived from the owner entity in the relationship EMPLOYEE DEPENDENT EMPLOYEE has

7. Attributes • Properties or characteristics of entities (or relationships) • Provide the actual data that describe entities (or relationships) EMPLOYEE E_ID E_Name E_Address

8. Unique Attributes • Candidate keys are ALL attributes that uniquely define an instance of an entity • A primary key is the candidate key that is chosen as the “most” unique of them all! • A primary key (or identifier) is ideal if it: • does not change over time • has unique values that are not null • does not use “intelligent” features • (Note: may need to substitute simple keys for long, i.e., composite, keys)

9. Composite Attributes • Attributes that are not “atomic” (or simple), i.e., those attributes that can (and need to) be broken down further EMPLOYEE E_ID E_Name E_Address (E_Street, E_City, E-Zip)

10. Multi-valued Attributes • Some attributes may have multiple values ... • … but they may need to be “fixed” before proceeding EMPLOYEE {E_Skill} EMPLOYEE SKILL S_Code S_Name has

11. Derived Attributes • Those attributes whose values can be calculated from the values of other attributes (either in the database or in the system). • Value is not stored in the database (since it does not take up any space) • E.g., Age can be calculated, where date-of-birth is stored

12. Relationships • Refer to the associations (or links) between entities • Use verbs to describe the links • May have attributes (in some cases) • Are directional STUDENT DORMITORY Lives in EMPLOYEE STORE STORE Works in

13. Associative Entities • A (many-to-many) relationship that is better represented as an entity because it: • contains attributes from the entities it links and has its own attributes • has independent meaning to end-users CUSTOMER PRODUCT Orders CUSTOMER ORDER PRODUCT