Relationships within Relational Database

1. Relationships within Relational Database • Relationship classifications • 1:1 • 1:M • M:N • E-R Model • ERD to map E-R model • Chen • Crow’s Foot Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

2. ERD Symbols • Rectangles represent entities • Diamonds represent the relationship(s) between the entities • “1” side of relationship • Number 1 in Chen Model • Bar crossing line in Crow’s Foot Model • “Many” relationships • Letter “M” and “N” in Chen Model • Three pronged “Crow’s foot” in Crow’s Feet Model Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

3. Example 1:M Relationship Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

4. Example 1:M Relationship Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

5. Example 1:M Relationship (con’t.) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

6. Example 1:M Relationship Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

7. Example M:N Relationship Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

8. Example M:N Relationship Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

9. Example M:N Relationship Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

10. Converting M:N Relationship to Two 1:M Relationships Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

11. Converting M:N Relationship to Two 1:M Relationships (con’t.) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

12. Converting M:N Relationship to Two 1:M Relationships (con’t.) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

13. Converting M:N Relationship to Two 1:M Relationships (con’t.) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

14. Data Redundancy Revisited • The proper use of foreign keys minimizes data redundancies. • Some redundancy is desirable to make the database serve crucial information purposes • Called controlled redundancy • Speed • Information requirements Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

15. Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

16. Data Redundancy Revisited (con’t.) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

17. Indexes • Points to location • Makes retrieval of data faster Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

18. Some Key Points in E-R Modeling • The orientation of the E-R diagram's components is immaterial • Avoid crossing relationship lines • Avoid homonyms - the use of same attribute name to label different attributes • Avoid synonyms - the use of different names to describe the same attribute • The dual relationships are a typical indication of the existence of synonyms. Try to remove. • Relationships are always read from the 1 to the M side in a 1:M relationship. • If a 1:1 relationship exists, that relationship is read from the entity that contains the common attributes to the entity that contains the unique attributes. Therefore, EMPLOYEE is a PILOT. (The EMPLOYEE entity contains the common attributes such as names and addresses, while the PILOT entity contains only those attributes – such as medical certificates and pilot licenses that are unique to pilots.) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel