1 / 24

CIS 218

CIS 218. Relationships. Relationships. Associations betwee n entities (tables) An Employee is assigned to a Car A Student takes a Class Three types One-to-One One-to-Many Many-to-Many. Crow’s Feet Notation.

zarek
Download Presentation

CIS 218

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CIS 218 Relationships

  2. Relationships • Associations between entities (tables) • An Employee is assigned to a Car • A Student takes a Class • Three types • One-to-One • One-to-Many • Many-to-Many

  3. Crow’s Feet Notation • Crow’s feet represent the number of entity instances (rows) that may participate in a relationship • One-to-One • One-to-Many • Many-to-Many

  4. One-to-One Relationship • 1:1 (one-to-one) • A single instance of one entity is related to a single instance of another entity • Each employee has one car • No employee has more than one car • Each car is assigned to one employee • No two employees share the same car

  5. One-to-Many Relationship • 1:N (one-to-many) • A single instance of one entity is related to many entity instances of another entity • Each repair is associated with only one technician • A technician may perform many repairs Technician Repair

  6. One-to-Many

  7. Many-to-Many Relationship • N:M (many-to-many) • Many entity instances of one entity are related to many entity instances of another entity • A computer can be attached to more than one printer • A printer can receive jobs from more than one computer Technician Repair

  8. Many-to-Many

  9. Practice • Manufacturer Computer • Software Computer • BookAuthor • Course Section • Student Club • Book Publisher Add the crow’s feet to indicate the type of relationship.

  10. Cardinality • Cardinality is the minimum and maximum number of entity instances that participate in a relationship. • Minimum is typically zero (optional) or one (mandatory). • Maximum is typically one or many. Exactly one One or more Zero or one Zero or more

  11. Cardinality: Examples O O V V STUDENT COURSE O V COURSE SECTION I I COMPUTER INSTRUCTOR O I O I

  12. Practice • Manufacturer Computer • Software Computer • BookAuthor • Course Section • Student Club • Book Publisher Add the minimum and maximum cardinality to each relationship.

  13. Representing 1:1 Relationships • The key from one relation is placed in the other as a foreign key • It does not matter which table receives the foreign key

  14. Representing 1:N Relationships • The primary key from the one side of the relationship becomes a foreign key in the many side of the relationship

  15. An Item can have more than one Detail row Item Item# Room Days Times Instructor Item Item# Instructor Detail DetailID Item# Room Days Times Item Item# Instructor Detail Room Days Times V I I I

  16. A Coordinator can coordinate more than one department Department deptName abbr coordinator email Coordinator coordID coordinator email Department abbr deptName coordID Coordinator coordinator email Department deptName abbr I I I V

  17. Representing Relationships – N:M • To represent a many-to-many relationship, a new table is created. This table is called an intersection table • An intersection table contains a foreign key from each of the tables that it connects • The foreign keys together form a composite primary key for the intersection table A Club can have more than one Member, and a Member can join more than one Club.

  18. O O O O V V V V Student SID firstName lastName Membership SID clubID Club clubID clubName I I I I I I I I

  19. N:M Relationship Example

  20. Association Tables • When an intersection table has columns beyond those in the primary key, it is called an association table

  21. Practice Student sid firstName lastName gender Ethnicity ethnic_code ethnicity Advisor advisorID advisorName extension Draw the relationships between the entities. Include minimum and maximum cardinality. Implement the relationships.

  22. Practice O O O V V V Student-Ethnicity sid ethnic_code Advisor advisorID advisorName extension Student sid firstName lastName gender advisorID Ethnicity ethnic_code ethnicity I I I I

  23. Case Study I:Heather Sweeney Designs Identify Entities Identify Attributes Choose Primary Keys Normalize Identify Relationships Implement Relationships

More Related