May 8

1. May 8 More on Data Modeling

2. Types of Entities • Fundamental Entity • regular rectangle (exist by themselves) • Attributive Entity • only exists because of the existence of some other entity • Associative Entity • result from many to many relationship

3. John Ahmed Mary Ngan Steve CS211 CS212 CS255 IS315 Registers for Course Student Many to Many relationships are not stable

4. John Ahmed Mary Ngan Steve CS211 CS212 CS255 IS315 Solution Registration Course Student

5. ERD Concepts Attribute • A characteristic of an entity or relationship • Primary Key • Simple • Composite or concatenated • Nonkey • These are all of the other attributes • Multivalued • May contain two or more values • We use an Attributive Entity to resolve these

6. ERD Concepts Attribute • A characteristic of an entity or relationship • Primary Key • Simple • Composite or concatenated • Nonkey • These are all of the other attributes • Multivalued • May contain two or more values • We use an Attributive Entity to resolve these • Foreign Keys • Pointers to a record in another entity

7. Primary Key Attribute Multivalued Attribute

8. Attributes • Atomic Fact • cannot have attributes of its own • Single Value • cannot have more than one value First_name Persons Last_name

9. Some Attributes Last_name Persons Address First_name

10. Street Street Last_name Last_name City City State State First_name First_name Zip Zip Some Attributes Persons

11. Same data shown as a table PERSONS PERSONS

12. Marriage is an M:N proposition is married to Persons

13. Notice the functional dependency is married to marriage _date Persons

14. is married to Marriage Date becomes an attribute of the relation marriage _date Persons

15. Functional Dependence For a relation R, attribute A is functionally dependent of attribute B if, for every valid instance, the value of B determines the value of A. We draw this as: B --> A In English, what all this means is…

16. Suppose we have a sales relation: • Year Sales (millions) • 1998 $32.4 • $33.6 • $29.4 • $33.5 • Then, Year --> Sales $33.6

17. is married to Marriage Date becomes an attribute of the relation marriage _date Persons

18. Student Roster Class Student(Student-ID, StudentLName, StudentFName, Address, Phone) Class(Department, Course-ID, Section-ID, Semester, Year, CourseTitle, Credits) Roster(Student-ID, Department, Course-ID, Section-ID, Semester, Year, Grade)

19. DePaul University • Grade Report • Fall Semester 1998 • Name: James Jordan ID: 123456789 • Address: 1201 Peach Drive • Major: IS • Instructor Instructor • Course ID Title Name Location Grade • IS 315 SA&D Valacich CST 440B A- • IS 512 Groupware Tyran CST 430G B+ • What are the functional dependencies on this report?

20. Course Roster Student # Course # Course Title Instructor Instructor Location Grade A123 IS315 SA & D Valacich CST440B A- O459 IS512 Groupware Tyran CST430G B+ L889 CS319 Database Gilson CST712 C-

21. Keys • Primary Keys • Foreign Keys • How keys work in Associative Entities • How keys work in Attributive Entities

22. ? ? Business rule: each customer has one sales rep, but sales reps can sell to many customers.

23. Business rule: each customer has one sales rep, but sales reps can sell to many customers. For each salesman there can be many customers; for each customer there is only one salesman.

24. Business rule: each customer has one sales rep, but sales reps can sell to many customers. For each salesman there can be many customers; for each customer there is only one salesman.

25. Business rule: each customer has one sales rep, but sales reps can sell to many customers. For each salesman there can be many customers; for each customer there is only one salesman.

26. NEWBusiness rule: each customer has one sales rep, and each sales rep has only one customer. For each salesman there is only one customer; for each customer there is only one salesman.

27. Review of the rules • Keys • Primary Keys • Every record has a unique primary key • Foreign Keys • In a 1:M relationship, the M side always has the FK • In a 1:1 relationship, both sides have FKs • How keys work in Associative Entities • How keys work in Attributive Entities

28. Business rule: customers can buy from many salesmen; salesmen can sell to many customers. In order to produce an efficient and stable database, this requires an Associative Entity

29. Business rule: customers can buy from many salesmen; salesmen can sell to many customers. For each customer there can be many transactions; for each transaction there is only one customer; for each salesman there can be many transactions; for each transaction there is only one salesman.

30. Business rule: each order can have many order items In order to produce an efficient and stable database, this requires an Attributive Entity

31. Business rule: each order can have many order items In order to produce an efficient and stable database, this requires an Attributive Entity

32. Acme Equipment sells product using the purchase orders (Pos) shown at left. Draw the ERD that this PO suggests. Make sure to indicate your Primary and Foreign Keys and assign fields for each entity.

33. Review of the rules • Keys • Primary Keys • Every record has a unique primary key • Foreign Keys • In a 1:M relationship, the M side always has the FK • In a 1:1 relationship, both sides have FKs • How keys work in Associative Entities • The Associative Entity takes the many side of all 1:M relationships. The key of the Associative Entity is a compound key of all primary entities. • How keys work in Attributive Entities • The Attributive Entity is the many side of the 1:M relationship. The Attributive Entity has a compound key comprised of the key of the primary entity plus a unique marker of the attribute.

34. How do you build an ERD for your case project? Here is a cookbook approach

35. Cookbook 1 • Derive a high level, first cut data model • What are the techniques? • look at forms, look at existing database, • Identify data entities • What are the entities of interest around which data must be stored? • “Model the world” be more rather than less comprehensive • Be aware of the domain you are exploring

36. Requirements Determination Questions for Data Modeling • What people, places, and things are used in the business? How many of each are there? How do you differentiate them? Entities and attributes • What unique characteristics distinguish one object from another? Attributes and primary key • What characteristics describe each object?Attributes and secondary keys

37. Requirements Determination Questions for Data Modeling • How do you use the data? What is the source of the data? Who controls it? • Over what time periods are you interested in this data? If it changes over time, do you need to know that? Cardinality and time issues • What kinds of transactions do you keep track of? Relationships

38. Requirements Determination Questions for Data Modeling • Are transactions always handled the same way, or are there sometimes exceptions? Can only some of the attributes of an object change? Cardinality • Can the associations or relationships among objects change over time?

39. ERD Data Sources • Data entry forms (paper and screen) • Reports (paper and screen) • Existing Databases • Interviews

40. Cookbook 2 • Identify likely attributes of those entities • What are the keys? (Primary, Alternate, Foreign) • How should you name them? • Determine constraints (cardinalities) of attributes • Determine relationships among entities • How are they related? Optional or mandatory? • What are the cardinalities of the relationships? • Fundamental, attributive, and associative entities

41. Cookbook 3 • Balance the model into 1:N relationships • For each 1:1 relationship • determine whether the 2 entities are truly separate, or can be combined • M:N relationships • can always be split into 1:N relationships by discovering the associative entity • For each 1:N relationship • analyze whether there is dependency • Is one entity dependent on the other, or are they independent?