DAT376 Modeling Business Requirements using Object Role Modeling (Part 2)

1. DAT376Modeling Business Requirements using Object Role Modeling (Part 2) LeRoy Tuttle, Jr. Program Manager Microsoft

2. Agenda – Part 2(DAT376) • Visual Studio .NET Enterprise Architect • Object Role Modeling • Database Design Process • Set Theory Review • Object Role Modeling • Conceptual Schema Design Procedure • Modeling fact types • Constraining fact types • Documenting the Model • Implementing the Model

3. VSEA Database Design Tools • Visio-based (VEA) • Conceptual data modeling (ORM) • Logical database modeling (Relational, IDEF1X, “ER”) • Physical database modeling(SQL Server, Access, Oracle, DB2, etc.) • Forward and reverse engineering, sync, import/export, reports, etc. • Non-Visio • Online physical database design tools • SQL query designer 3

4. The Baseline Database Design Process Modeling business requirements Modeling databases External Conceptual Logical Physical 4

5. Universe of Discourse Record Keeping SQL Business Context as a Foundation 5

6. Conceptual Schema Design Procedure (CSDP)

7. CSDP Step 4Add uniqueness constraints, and check the arity of fact types • Constrain population of fact types • Redundancy is not allowed in a completed conceptual model • Re-express non-elementary facts (N-1 Rule) • Minimal ORM Model!

8. How Internal Uniqueness Constraints Are Symbolized • Arrow-tipped line over role shape • Spans one or more roles 7

9. Examples of internal uniqueness constraints on binary facts For each A, one B All of AB is unique For each B, one A, and for each A, one B 9

10. Examples of internal uniqueness constraints on ternary facts For each AB, one C For each AC, one B All ABC is unique 10

11. Example of overlapping internal uniqueness constraints For every AB, one C, and for every AC, one B 11

12. How External Uniqueness Constraints Are Symbolized • Dashed line between roles • Circled ‘U’ in middle of line • Line connects to roles in the constraint 12

13. Example of external uniqueness constraint Instances of BC are unique 13

14. How Nested Object Types Are Symbolized 15

15. demo CSDP Step 4 16

16. CSDP Step 5Add mandatory role constraints and check logical derivations • Nullability • Cardinality • Role functional dependency

17. 2 1 3 Roles and Object Type Populations Instances of Person in Roles … drives … A, B Subset of Person Population Role Population Person A, B A, C A, B A, C B, C A, B … resides in … A, C Role Population Subset of Person Population Union of Role Instances … works for … B, C Role Population Subset of Person Population Figure 6.1 18

18. A A D D B B E C C Independent Object Types • A given instance is not required to participate in any roles • An object type’s population is larger than all of its role’s populations, except for its reference mode Object Type Population Role Population Instance Is Not In Role’s Population 19

19. How Mandatory Role Constraints Are Symbolized 21

20. How Disjunctive Role Constraints Are Symbolized 23

21. 2 1 3 Emp num Employee name Dept Location Phone Tenured/ nontract- expiry 720 Brown T Biochemistry 62 Blg 406 Room 9642 Ext Access LOC 01/31/95 715 720 139 430 Adams A Brown T Cantor G Codd EF Computer Science Biochemistry Mathematics Computer Science 69 62 67 69 301 406 301 507 2345 9642 1221 2911 LOC LOC INT INT 01/31/95 01/31/95 Tenured Tenured Identifying Entity Types • Global means • Candidate identifiers • Primary reference scheme Candidate Identifier Candidate Identifier Candidate Identifier Candidate Identifier 24

22. What Is a Simple Reference Scheme? • Definition A simple reference scheme uses instances of a single value type to identify an entity type • Characteristics • Implied binary fact type • Mandatory role • Simple uniqueness constraint 25

23. How a Internal Compound Reference Scheme is Symbolized 27

24. How a External Compound Reference Scheme is Symbolized 28

25. 2 1 3 What Are Transitively Implied Relationships? • Definition Object types exhibit a transitive implied relationship when an unbroken chain of comparable relationships is formed spanning a series of object types • Characteristics • Use in logic and mathematics • Conducts characteristics • Examples • If X =Y and Y = Z, then X = Z • If X > Y and Y > Z, then X > Z • If X is like Y and Y is like Z, then X is like Z 30

26. Example of a Logically Derivable Fact Type 32

27. demo CSDP Step 5 34

28. CSDP Step 6Add value, set, and subtype constraints • Domain of a population • Comparing and contrasting set populations • Specialization of object types

29. Domain of a Population • All allowable values • Values in domain are unique • Population is not always unique Domain Population A A B B C Not in the Domain Z C A 36

30. How Value Constraints Are Represented • FORML expression Person(Name) is an entity object type. Every Person is identified by one distinct Name. The possible values of 'Name' are: 'Jeff', 'Maria', 'Pierre'. Value Constraint 38

31. Comparing and Contrasting Set Populations • Set equality • Subset • Set exclusion Set 1 Set 2 A B C A B C Set 1 Set 3 A B C A B Set 3 Set 4 A B C 39

32. 2 1 3 How Set Constraints Are Represented • Set exclusion • Set equality • Set subset Exclusion Constraint No Employee that is paid some HourlyWage is paid some Salary. Equality Constraint Employee e is paid some Salary if and only if Employee e works as some SalesManager. Subset Constraint If Employee e works as some SalesManager then Employee e works as some Salesperson. 41

33. A B C D E F G Specialization of Object Types • Subsets of a population • Subsets have distinctive characteristics • Specialized relationships • Specialized constraints • Start from primitive object types • Top-down process • Arrows point toward node that was specialized 42

34. 2 1 3 How Subtype Relationships Are Represented Salesperson is primarily identified by the identification scheme of Employee. Salesperson is a subtype of Employee / Employee is a supertype of Salesperson. Employee is the primary super type of Salesperson. Each Manager is a Salesperson but not every Salesperson is necessarily a Manager. Subtype Relationship Primary Subtype Relationship Secondary Subtype Relationship 44

35. demo CSDP Step 6 46

36. CSDP Step 7Add other constraints and perform final checks • Reoccurring domain values • Cardinality • Intra-population relationships • Other constraints

37. Reoccurring Domain Values • Each instance must have an allowable value from the population domain • Multiple instances could have the same value Domain Population A A A B C C C A 48

38. Frequency Constraint How Frequency Constraints Are Represented Each Person occurs exactly 2 times or not at all. Each Car, Location combination that occurs, occurs at least 3 and at most 4 times. 50

39. Object Type Intra-Population Relationships • Instances of a single object type • Related groups of instances • Circularly related groups • Hierarchically related groups • Set relationships between groups 51

40. 2 1 3 How Ring Constraints Are Symbolized Ring Constraint Ring Constraint Ring Constraint 53

41. Purpose Non-example data Mathematical expression x y 2 1 3 A B Figure 8.X B D C B D E Irreflexive Ring Constraints No instance is related to itself. R is irreflexive iff for all x ~ xRx Constraint Violation! A A A A 54

42. Purpose Non-example data Mathematical expression x y 2 1 3 A A Figure 8.X A B B B B A Symmetric Ring Constraints Must have mirror images Must have mirror image R is symmetric iff for all x, y xRy  yRx Constraint Violation! A A C C 55

43. Purpose Non-example data Mathematical expression x y 2 1 3 A B Figure 8.X B C A A C D Asymmetric Ring Constraints No opposites R is asymmetric iff for all x, y xRy  ~yRx Constraint Violation! B B A A 56

44. Purpose Non-example data Mathematical expression 2 1 3 Antisymmetric Ring Constraints No opposites or mirror images x y A B Figure 8.X B C Constraint Violation! A A A A C D R is antisymmetric iff for all x, y x = y & xRy  ~yRx Constraint Violation! B B A A / 57

45. Purpose Non-example data Mathematical expression x y 2 1 3 A B Figure 8.X B C C D D E Intransitive Ring Constraints You cannot skip a generation R is intransitive iff for all x, y, z xRy & yRz  ~xRz Constraint Violation! A A E E 58

46. Purpose Non-example data Mathematical expression x y 2 1 3 A B Figure 8.X B D C E D F Acyclic Ring Constraints No ancestor is a descendant R is acyclic iff for all x, y, z xRy & yRz  ~zRx Constraint Violation! F F A A 59

47. Valid Ring Constraint Combinations 60

48. demo CSDP Step 7 61

49. demo Documenting the ORM Model 62

50. Forward & Reverse Engineering 63