2 Relationships within Relational Database Relationship classifications 1

2 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, 5 th 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, 5 th Edition, Rob & Coronel

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

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

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

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

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

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

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

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

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

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

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

2 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, 5 th Edition, Rob & Coronel

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

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

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

2 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, 5 th Edition, Rob & Coronel