Modeling Complex Structures

1. Modeling Complex Structures Data Modeling and Relational Database Design Lesson Preparation This session is about discussing the further complexities of data modeling. There are two practices. Make sure you are fully conversant with the solutions and are able to discuss them with the students. Lesson Controls Make sure there is plenty of interaction here. There is a lot of information to get across. Timings Lesson 60 minutes Practice 110 minutes Intended as the third lesson on the second day. Practice Controls Discuss the results of practice 9-1 before moving on to 9-2.Lesson Preparation This session is about discussing the further complexities of data modeling. There are two practices. Make sure you are fully conversant with the solutions and are able to discuss them with the students. Lesson Controls Make sure there is plenty of interaction here. There is a lot of information to get across. Timings Lesson 60 minutes Practice 110 minutes Intended as the third lesson on the second day. Practice Controls Discuss the results of practice 9-1 before moving on to 9-2.

2. Objectives Identify and model exclusive entities Identify and model exclusive relationships Model over time Identify and resolve fan traps Identify and resolve chasm traps Consider convergent and divergent models Model non-transferable relationships

3. Exclusive Entities

4. Exclusive Entities

5. Splitting Entities

6. Nesting Entities

7. Nesting Entities Technical Notes Refer to CASE* Method Entity Relationship Modeling, p7-4, for a discussion of overlapping groups of an entity. This construct is rarely used. Technical Notes Refer to CASE* Method Entity Relationship Modeling, p7-4, for a discussion of overlapping groups of an entity. This construct is rarely used.

8. Recursive Subtypes Technical Notes Another way of modeling a hierarchy where the top level is explicitly modeled.Technical Notes Another way of modeling a hierarchy where the top level is explicitly modeled.

9. Modeling Exclusive Relationships Technical Notes An instance of membership is either for a Company or for a Customer not both.Technical Notes An instance of membership is either for a Company or for a Customer not both.

10. Modeling Exclusivity Class Management Discuss the merits of each method and why and when they might use one rather than another.Class Management Discuss the merits of each method and why and when they might use one rather than another.

11. Modeling Data over Time Technical Notes How extensive does the audit trail need to be. Class Management Discuss with the participants potential requirements for keeping historical data.Technical Notes How extensive does the audit trail need to be. Class Management Discuss with the participants potential requirements for keeping historical data.

12. Modeling Data over Time Technical Notes This technique is essentially equivalent to adding an intersection entity, but it may confuse the participants to talk about resolving a M:!. Class Management This technique is essentially equivalent to adding an intersection entity, but it may confuse the participants to talk about resolving a M:!. Discuss the UID of RENTAL HISTORY ENTRY. This model is from the perspective of the apartment owner. Alternatively the UID bar could be on the relationship to PERSON, and not on the relationship to APARTMENT. Under what circumstances would both relationships be part of the UID?Technical Notes This technique is essentially equivalent to adding an intersection entity, but it may confuse the participants to talk about resolving a M:!. Class Management This technique is essentially equivalent to adding an intersection entity, but it may confuse the participants to talk about resolving a M:!. Discuss the UID of RENTAL HISTORY ENTRY. This model is from the perspective of the apartment owner. Alternatively the UID bar could be on the relationship to PERSON, and not on the relationship to APARTMENT. Under what circumstances would both relationships be part of the UID?

13. Modeling Data over Time Technical Notes The model might either represent only current employee/employer relationships, or it might represent all employee/employer relationships over time.Technical Notes The model might either represent only current employee/employer relationships, or it might represent all employee/employer relationships over time.

14. Modeling Data over Time

15. Fan Traps

16. Fan Traps Technical Notes This is the initial reaction to a fan trap but is probably not the best way to resolve it. Class Management Ask the participants where they would hold the date a person stated a job with a company?Technical Notes This is the initial reaction to a fan trap but is probably not the best way to resolve it. Class Management Ask the participants where they would hold the date a person stated a job with a company?

17. Resolving Fan Traps

18. Chasm Traps Class Management Ask a participant which company cars are assigned to a department.Class Management Ask a participant which company cars are assigned to a department.

19. Resolving Chasm Traps

20. Convergence Technical Notes Keep the diagrams as simple as you can. Converge where possible, but it is possible to have to much of a good thing.Technical Notes Keep the diagrams as simple as you can. Converge where possible, but it is possible to have to much of a good thing.

21. Divergence

22. Transferable Relationships

23. Non-Transferable Relationships

24. Summary Exclusive entities - supertypes and subtypes Exclusive relationships Modeling over time - extra entities Complex structures - fan and chasm traps When to stop - convergence and divergence Non-transferable relationships