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. 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

3. 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]

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

5. E/R Modeling • The E/R model is used to construct a conceptual data model -- a representation of the structure and constraints of a database and is the technology independent.

6. Why Data Modeling is Important? • The characteristics of the data are crucial in the design of the database, programs, and other system components. • Dataare the most complex aspects of many modern MIS - not the processes. • Data tend to be more stable than the business processes that use that data.

7. SDLC Revisited – Data Modeling is an Analysis Activity (see also Figure 1-7) Project Identification and Selection Purpose –thorough analysis Deliverable – functional system specifications Project Initiation and Planning Analysis Logical Design Physical Design Database activity – conceptual data modeling Implementation Maintenance Growth and Change

8. 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?)

9. A Good Business Rule is: (Table 2-1) • Declarative – what, not how • Precise – clear, agreed-upon meaning • Atomic – one statement • Consistent – internally and externally • Expressible – structured, natural language • Distinct – non-redundant • Business-oriented – understood by business people

10. A Good Data Name is: technical • Related to business, not __________, characteristics • Meaningful and self-documenting • _________ • Readable • Composed of words from an approved list • Repeatable • Written in standard syntax Unique

11. Data Definitions Explanation of a term or fact Term–word or phrase with specific meaning Fact–association between two or more terms Guidelines for good data definition Gathered in conjunction with systems requirements Accompanied by diagrams Concise description of essential data meaning Achieved by consensus, and iteratively refined 11

12. 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)

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

14. 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

15. What Should an Entity Be? • SHOULD BE: • An object that will have many instances in the database • An object that will be composed of multiple attributes • An object that we are trying to model • SHOULD NOT BE: • A user of the database system • An output of the database system (e.g. a report)

16. Appropriate entities Figure 2-4 Example of inappropriate entities System output System user Inappropriate entities

17. 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

18. Identifiers (Keys) • Identifier (Key) - An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type • Simple Key versus Composite Key • Candidate Key – an attribute that could be a key…satisfies the requirements for being a key

19. 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

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

21. Q: (Review Q:#12; p.101) Another example of Weak Entity Phone Call (see below) is an example of a weak entity because a phone call must be placed by a PERSON. In this simple example, PHONE CALL is related to only one other entity type, thus, it is not necessary to show the identifying relationship; however, if this data model were ever expanded so that PHONE CALL related to other entity types, it is good practice to always indicate the identifying relationship. Weak entity Strong entity

22. 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

23. 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

24. 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)

25. 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]

26. derived/ computed EMPLOYEE (How to manage three issues? ) composite multi-valued How?

27. An entity type name should be all of the following EXCEPT: A) concise. B) specific to the organization. C) as short as possible. D) a singular noun. Answer: _______ C

28. 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

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

30. E-R Model Constructs (Continued) • Criteria for Selecting Identifiers • Will not change in value over the life of each instance of the entity type. • Will not be NULL. • No intelligent identifiers (containing e.g. locations or people that might change) • Substitute new, simple (e.g., surrogate attribute) keys for long, composite keys (e.g., entity type of Game: Game# instead of Home_Team and Visitor_Team)

31. More on Relationships Relationship Types vs. Relationship Instances The relationship type is modeled as lines between entity types…the instance is between specific entity instances Relationships can have attributes These describe features pertaining to the association between the entities in the relationship Two entities can have more than one type of relationship between them (multiple relationships) Associative Entity–combination of relationship and entity

32. 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

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

34. 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

35. 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

36. 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

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

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

39. Figure 2-11(c )An associative entity using Microsoft VISIO

40. JustLee E-R Model (Fig. 1-5; p.11 Oracle Text) PUBLISHER PubID CUSTOMERS Customer# ORDERS Order# BOOKS ISBN AUTHOR AuthorID

41. JustLee E-R Model (Fig. 1-5; p.11 Oracle Text) PUBLISHER PubID CUSTOMERS Customer# ORDERS Order# BOOKS ISBN AUTHOR AuthorID ORDERITEMS Order# Item# BOOKAUTHOR ISBN AuthorID

42. JustLee E-R Model (Fig. 1-5; p.11 Oracle Text) PUBLISHER PubID PROMOTION Gift CUSTOMERS Customer# ORDERS Order# BOOKS ISBN AUTHOR AuthorID ORDERITEMS Order# Item# BOOKAUTHOR ISBN AuthorID

43. JustLee E-R Model (Fig. 1-5; p.11 Oracle Text) PUBLISHER PubID PROMOTION Gift CUSTOMERS Customer# ORDERS Order# BOOKS ISBN AUTHOR AuthorID ORDERITEMS Order# ISBN BOOKAUTHOR ISBN AuthorID

44. Entity Type /Entity (attributes) Entity Instance Object Value E-R Model vs. O-O Model A collection of entities that share common properties or characteristics Class Attributes + operations A single occurrence of an entity type/class

45. Entity Type /Entity (attributes) Entity Instance Object Value E-R Model vs. O-O Model A collection of entities that share common properties or characteristics Class Attributes + operations A single occurrence of an entity type/class

46. Break ! (Ch. 2 - Part I) In class exercise - #7 (p. 87) HW - 1). #10; p. 87 [Draw ER-D (use Word/Visio) ]

47. Relationships Degree • ________ 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)

48. 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

49. 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

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