Modeling Relationships

1. Modeling Relationships Data Modeling and Relational Database Design

2. Objectives • Analyze and model the relationships between entities • Draw an initial entity relationship diagram • Read the relationships on an ER diagram • Clearly define relationship names

3. Relationship Definitions • The way one entity relates to another • The business rules that link together business information needs • What one thing has to do with another • A named association between entities

4. Bi-directional Relationships SMT COURSE SMT COURSE DMDD COURSE INSTRUCTOR COURSE

5. Diagramming Conventions • A line between two entities • Lower case relationship name • Optionality (Minimum cardinality) • Degree (Maximum cardinality) Mandatory - must be Optional - may be One or more One and only one

6. COPY TITLE Diagramming Conventions many (crow’s foot) optional one mandatory

7. Relationship Syntax one or more or one and only one must be or may be relationship name Each entity 1 entity 2 Object entity Subject entity Name Optionality Degree

8. Validation - in class practice assigned to EMPLOYEE DEPARTMENT

9. Validation - in class solution assigned to EMPLOYEE DEPARTMENT Each EMPLOYEE must be assigned to one and only one DEPARTMENT

10. Validation - in class practice EMPLOYEE DEPARTMENT responsible for

11. Validation - in class solution EMPLOYEE DEPARTMENT responsible for Each DEPARTMENT may be responsible for one or more EMPLOYEES

12. Validation - in class solution assigned to EMPLOYEE DEPARTMENT DEPARTMENT EMPLOYEE responsible for Each EMPLOYEE must be assigned to one and only one DEPARTMENT Each DEPARTMENT may be responsible for one or more EMPLOYEES

13. Validation - in class practice enrolled in COURSE STUDENT taken by

14. Validation - in class solution enrolled in COURSE STUDENT taken by Each STUDENT may be enrolled in one or more COURSES Each COURSE may be taken by one or more STUDENTS

15. Relationship Types Many-to-One Many-to-Many One-to-One

16. Many-to-One Relationships visited by SALES REPRESENTATIVE CUSTOMER assigned to

17. Many-to-Many Relationships attended by HEALTH CARE WORKER PATIENT assigned to

18. One-to-One Relationships is ridden by BICYCLE CYCLIST the rider of Represents a snapshot in time

19. Analyzing & Modeling Relationships • Determine the existence of a relationship • Name each direction of the relationship • Determine the degree of each direction of the relationship • Determine the optionality of each direction of the relationship • Read the relationship aloud to validate it

20. COPY Determining a Relationship’s Existence Existence Name MEMBERSHIP RENTAL COPY Degree MEMBERSHIP Optionality Validate COPY RENTAL MEMBERSHIP RENTAL

21. TITLE COPY Naming the Relationship Existence Each title is available as a copy and each copy is of a title Name Degree Optionality Validate of available as

22. TITLE COPY Determining the Degree Existence Each title is available as a copy, there could be lots of copies but there is only ever one title on a copy Name Degree Optionality Validate one many

23. TITLE COPY Determining the Optionality Every copy must have a title on it but we need information about titles even if there is no copy Existence Name Degree Optionality Validate optional mandatory

24. TITLE COPY Validating the Relationship Each copy must be of one and only one title, and each title may be available as one or more copies Existence Name Degree Optionality Validate of available as

25. Summary • Establish the existence of a relationship • Name the relationship • Determine its degree • Determine its optionality • Read the relationship to validate it