modeling relationships
DESCRIPTION
Modeling Relationships. Data Modeling and Relational Database Design. 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. Relationship Definitions. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/1.jpg)
Modeling RelationshipsData Modeling and Relational Database Design
![Page 2: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/2.jpg)
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
![Page 3: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/3.jpg)
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
![Page 4: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/4.jpg)
Bi-directional Relationships DMDD COURSE SMT COURSESMT COURSE
INSTRUCTOR COURSE
![Page 5: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/5.jpg)
Diagramming Conventions
A line between two entities
Lower case relationship name
Optionality (Minimum cardinality)
Degree (Maximum cardinality)Optional - may beMandatory - must be
One or more
One and only one
![Page 6: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/6.jpg)
Diagramming Conventions
COPY TITLE
many(crow’s foot)
mandatory
optionalone
![Page 7: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/7.jpg)
Relationship Syntax
Each
Subject entity
must beormay be
relationshipname
one or moreorone and only one
entity 1 entity 2
DegreeObject entityOptionality Name
![Page 8: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/8.jpg)
Validation - in class practice
EMPLOYEE DEPARTMENTassigned to
![Page 9: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/9.jpg)
Validation - in class solution
EMPLOYEE DEPARTMENTassigned to
Each EMPLOYEE must be assigned to one and only one DEPARTMENT
![Page 10: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/10.jpg)
Validation - in class practice
EMPLOYEE DEPARTMENTresponsible for
![Page 11: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/11.jpg)
Validation - in class solution
EMPLOYEE DEPARTMENT
Each DEPARTMENT may be responsible for one or more EMPLOYEES
responsible for
![Page 12: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/12.jpg)
EMPLOYEE DEPARTMENTassigned to
Each EMPLOYEE must be assigned to one and only one DEPARTMENT
EMPLOYEE DEPARTMENT
Each DEPARTMENT may be responsible for one or more EMPLOYEES
responsible for
Validation - in class solution
![Page 13: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/13.jpg)
Validation - in class practice
taken by
enrolled inSTUDENT COURSE
![Page 14: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/14.jpg)
Validation - in class solution
Each STUDENT may be enrolled in one or more COURSES
Each COURSE may be taken by one or more STUDENTS
taken by
enrolled inSTUDENT COURSE
![Page 15: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/15.jpg)
Relationship Types
Many-to-One
One-to-One
Many-to-Many
![Page 16: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/16.jpg)
Many-to-One Relationships
CUSTOMER SALES REPRESENTATIVE
assigned to
visited by
![Page 17: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/17.jpg)
Many-to-Many Relationships
PATIENT HEALTH CARE WORKER
assigned to
attended by
![Page 18: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/18.jpg)
One-to-One Relationships
BICYCLE CYCLISTthe rider of
is ridden by
Represents a snapshot in time
![Page 19: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/19.jpg)
Analyzing & Modeling Relationships
1 Determine the existence of a relationship
2 Name each direction of the relationship
3 Determine the degree of each direction of the relationship
4 Determine the optionality of each direction of the relationship
5 Read the relationship aloud to validate it
![Page 20: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/20.jpg)
Determining a Relationship’s ExistenceExistenceName
OptionalityDegree
Validate
MEMBERSHIP COPY
COPY
RENTAL
RENTAL
MEMBERSHIP
RENTAL
MEMBERSHIP
COPY
![Page 21: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/21.jpg)
Naming the RelationshipExistenceName
OptionalityDegree
Validate
COPY TITLEof
available as
Each title is available as a copy and each copy is of a title
![Page 22: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/22.jpg)
Determining the Degree
Name
OptionalityDegree
Validate
Existence
COPY TITLE
one
many
Each title is available as a copy, there could be lots of copies but there is only ever one title on a copy
![Page 23: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/23.jpg)
Determining the OptionalityExistenceName
OptionalityDegree
Validate
optional
mandatory
COPY TITLE
Every copy must have a title on it but we needinformation about titles even if there is no copy
![Page 24: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/24.jpg)
Validating the RelationshipExistenceName
OptionalityDegree
Validate
COPY TITLEof
available as
Each copy must be of one and only one title, and each title may be available as one or more copies
![Page 25: Modeling Relationships](https://reader037.vdocuments.us/reader037/viewer/2022110214/568162b6550346895dd34023/html5/thumbnails/25.jpg)
Summary
Establish the existence of a relationship
Name the relationship
Determine its degree
Determine its optionality
Read the relationship to validate it