Chapter 2 Modeling Data in the Organization

1. Chapter 2Modeling Data in the Organization Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@gonzaga.edu

2. EMPLOYEE derived/ computed composite multi-valued • Questions: • 1. Can you find out “total number of employees” from state of Washington (or California)? • 2. Can you find out all employees (with their detailed information) from city of Spokane? • 3. Can we count # of skills for any employee? • 4. Can we add new skills for any employee? • More questions: • a. Is the table shown above the one we should create for EMPLOYEE table (using DDL)? Y/N • c. If No, why and how to create a table structure for STUDENT • d. What is (are) the draw back of creating a EMPLOYEETABLE with “Composite/ Multi-valued” attribute of skill? (and derived/computed attributes of years_employed and age) EMPLOYEE e_id e_name e_address(street, city, state, zip) dob date_employed {skill} [years_employed] [age]

3. Objectives • Definition of terms • Importance of data modeling • Write good names and definitions for entities, relationships, and attributes • Distinguish unary, binary, and ternary relationships • Model different types of attributes, entities, relationships, and cardinalities • Draw E-R diagrams for common business situations • Convert many-to-many relationships to associative entities • Model time-dependent data using time stamps

4. Abstraction • Concealing irrelevant details from the user. • Abstraction is the process of temporarily ignoring underlying details so we can focus on the ____ picture of the large problem at hand

5. Business Rules • Statements that define or constrain some aspect of the business • Assert business structure • Control/influence business behavior • Are expressed in terms familiar to end users • Are automated through DBMS software (What/Why and How?)

6. E-R Model Constructs • Entities: • Entity instance: person, place, object, event, concept (often corresponds to a row in a table) • Entity Type: collection of entities (often corresponds to a table). • entity type is always SINGULAR • Relationships: • Relationship instance–link between entities (corresponds to primary key-foreign key equivalencies in related tables) • Relationship type–category of relationship…link between entity types • Attribute • property or characteristic of an entity or relationship type (often corresponds to a field in a table)

7. Sample E-R Diagram (Figure 2-1)

8. Basic E-R notation (Figure 2-2) Entity symbols Attribute symbols A special entity that is also a relationship Relationship symbols Relationship degreesspecify number of entity types involved Relationship cardinalities specify how many of each entity type is allowed

9. Attributes • Attribute - property or characteristic of an entity type that is of interest to the organization. • Classifications of attributes: • Required versus Optional Attributes • Simple versus Composite Attribute • Single-Valued versus Multi-valued Attribute • Stored versus Derived Attributes • Identifier Attributes

10. Strong vs. Weak Entities, andIdentifying Relationships • Strong entities • exist independently of other types of entities • has its own unique identifier • identifier underlined with single-line • Weak entity • dependent on a strong entity (identifying owner)…cannot exist on its own • does not have a unique identifier (only a partial identifier) • Partial identifier underlined with double-line • Entity box has double line • Identifying relationship • links strong entities to weak entities

11. Figure 2-5 Example of a weak identity and its identifying relationship Weak entity Strong entity

12. Required vs. Optional Attributes Required – must have a value for every entity (or relationship) instance with which it is associated Optional – may not have a value for every entity (or relationship) instance with which it is associated

13. E-R Model Constructs (Continued) • Attribute - property or characteristic of an entity type that is of interest to the organization. • Simple versus Composite Attribute • Fig. 2-7 • Single-Valued versus Multivalued Attribute • Fig. 2-8 • Stored versus Derived Attributes • Fig. 2-8

14. Multivalued an employee can have more than one skill Derived from date employed and current date Figure 2-7 A compositeattribute: An attribute that has meaningful component parts (attributes) An attribute broken into component parts Figure 2-8 Entity with multivalued attribute (Skill) and derived attribute (Years Employed)

15. CUSTOMER (How to solve potential problems on “composite attributes”) CUSTOMER (after “breaking” the ‘composite’ address) • Now we are able to find out “total number of customers” from state of Washington? • HOW? • We can find out all customers (with their detailed information) from city of Spokane? • HOW? • and more …

16. derived/ computed EMPLOYEE (How to manage the “multi-value” attributes? ) composite multi-valued Can we count # of skills for any employee? EMPLOYEE e_id e_name e_address(street, city, state, zip) dob date_employed {skill} [years_employed] [age] Can we add new skills for any employee? • Should we create “composite” attributes? (see next slide)? • Should we create “derived/computed” attributes and save physically/ permanently in the database and why? • How to insert values into “multi-valued” attributes? (later this semester)

17. E-R Model Constructs (Continued) • Identifier or Key - An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type. • Simple Key versus Composite Key (Fig. 2-9) • Candidate Key

18. Figure 2-9 Simple and composite identifier attributes The identifier is boldfaced and underlined

19. Modeling Relationships • Relationship Type versus Instance • Fig. 2-10 • An associative entity • An entity type that associates the instances that are peculiar to the relationship between those entity instances. • Attributes on Relationships • Fig. 2-11

20. Figure 2-10 Relationship types and instances a) Relationship type (Completes) b) Relationship instances Smith

21. Associative Entities • An entity - has attributes • A relationship - links entities together • Associative Entity–combination of relationship and entity • When should a relationship with attributes instead be an associative entity? • All relationships for the associative entity should be many • The associative entity could have meaning independent of the other entities • The associative entity preferably has a unique identifier, and should also have other attributes • The associative entity may participate in other relationships other than the entities of the associated relationship • Ternary relationships should be converted to associative entities

22. Figure 2-11(a) Associate Entnty- A binary relationship with an attribute Attribute on a relationship (Link Attribute/Associative) Here, the date completed attribute pertains specifically to the employee’s completion of a course…it is an attribute of the relationship

23. Figure 2-11(b) An associative entity (CERTIFICATE) Associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right Note that the many-to-many cardinality between entities in Figure 2-11a has been replaced by two one-to-many relationships with the associative entity

24. Fig. 2-11: (b) An associative entity (CERTIFICATE) Employee_ID Course_ID What is an alternative to assign the pk?

25. Fig. 2-11: (b) An associative entity (CERTIFICATE) Employee_ID Course_ID grade (P/F) What is an alternative to assign the pk?

26. Figure 2-11c An associative entity using Microsoft VISIO

27. Break ! (Ch. 2 - Part I) In class exercise - #7 (p. 87) HW - 1). #10; p. 87 [Draw ER-D (use Word/Visio) ] -2). Read MVC mini-case and prepare for discussion

28. Relationships • ______ of a Relationship - number of entity types that participate in it (Fig. 2-12) • Unary (or Recursive) Relationship • (degree 1) • Bill-of-Materials (Fig. 2.12; 2-13) • Binary Relationship • (degree 2) • Ternary Relationship • (degree 3)

29. Cardinality of Relationships • One – to – One • Each entity in the relationship will have exactly one related entity • One – to – Many • An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity • Many – to – Many • Entities on both sides of the relationship can have many related entities on the other side

30. One entity related to another of the same entity type Entities of two different types related to each other Entities of three different types related to each other Degree of relationships – from Figure 2-2

31. Fig. 2-12: Example of relationships of different degrees (a) Unary relationships

32. Fig. 2-12: (b) Binary relationships

33. Note: a relationship can have attributes of its own Figure 2-12 Examples of relationships of different degrees (cont.) c) Ternary relationship

34. Note: a relationship can have attributes of its own Figure 2-12 Examples of relationships of different degrees (cont.) c) Ternary relationship

35. Fig. 2-14: Ternary relationships as an associative entity

36. Fig. 2-18: Cardinality constraints in a ternary relationship part_id vendor_id part_id warehouse_id cpk vendor_id ??? warehouse_id Quantity

37. Fig. 2-18: Cardinality constraints in a ternary relationship Each part can be supplied by any number of vendors to more than one WH, but each part must supplied by at least one vendor to a WH. Each vendor can supply many parts to any number of wareshouses, but need not supply any parts. Each WH can be supplied with any number of parts from more than one vendor, but each WH must be supplied with at least one part

38. Basic E-R notation (Figure 2-2) Entity symbols Attribute symbols A special entity that is also a relationship Relationship symbols Relationship degreesspecify number of entity types involved Relationship cardinalities specify how many of each entity type is allowed

39. Cardinality Constraints • Cardinality Constraints - the number of instances of one entity that can or must be associated with each instance of another entity. • Minimum Cardinality • If zero, then optional • If one or more, then mandatory • Mandatory One - when min & max both = 1 • Maximum Cardinality • The maximum number

40. A patient history is recorded for one and only one patient A patient must have recorded at least one history, and can have many Figure 2-17 Examples of cardinality constraints a) Mandatory cardinalities

41. A project must be assigned to at least one employee, and may be assigned to many An employee can be assigned to any number of projects, or may not be assigned to any at all Figure 2-17 Examples of cardinality constraints (cont.) b) One optional, one mandatory (4)

42. A person is married to at most one other person, or may not be married at all Figure 2-17 Examples of cardinality constraints (cont.) c) Optional cardinalities

43. Relationships • Modeling Time-Dependent Data • Time Stamps: a time value that is associated with a data value (Fig. 2-19; 2-20) • managing time-dependent data is inadequate using current data models --> data warehousing • Multiple Relationship: Business Rules • more than one relationship between the same entity types (Fig. 2-21)

44. Figure 2-19 Simple example of time-stamping This attribute is both multivalued andcomposite. You should know HOW to create the “Price History” attribute in PRODUCT entity!

45. Fig. 2-20: Examples of __________ relationships (a) Employees and departments Entities can be related to one another in more than one way

46. Fig. 2-20: (b) Professors and courses (fixed upon constraint) ?? Here, minimum cardinality constraint is 2, what’s for? At least two professors must be qualified to teach each course. Each professor must be qualified to teach at least one course.

47. Levels of Database Schemas Different schemas are presented to different users Enterprise Data Model External Level Conceptual to Internal mapping Conceptual Schema Conceptual Level Internal to conceptual mapping Internal Schema Internal Level Logical Schema Physical Schema

48. E/R, OO … Relations Database Figure 1-12: Three-schema database architecture External View Ch. 4 Ch. 2,3,4 Meta-data/ Repository/ D.D. Ch. 5 Internal View

49. The Entity Relationship (E-R) Model Congratulation !! You have just learned one of the most important modeling concept (E-R) for developing the data base systems.

50. MVC_Hospital HW Phase I - Logical Design Phase Draw a entity-relationship diagram (enterprise model) for Mountain View community Hospital, based on the narrative description of the case and this handout (but the entities are from the five (5) figures shown above). You should create a file and turn in with a hardcopy (called MVC_PhaseI_ERD_Lname_Fname.doc) contains the following materials: 1. Read and employ materials from chapters 1 and 2 2. Include entities, associations (with detail multiplicity), and attributes. 3. Determine and draw the order of entering data Email ONLY the .docxfile to me with an attachment to: chen@jepson.gonzaga.edu with subject title of : bmis441_MVC-Phase I