Table Relationships

1. Table Relationships RDBM

2. Establishing Table Relationships • RDBMS allow us to establish relationships among tables • Have a primary key in our REGIONS table and a foreign key in the STATE table • Must link or join tables Prof. Leighton

3. Three Types of Table Relationships • One to one relationship • One to many relationship • Many to many relationship Prof. Leighton

4. One to One Relationship • Each record in one table has exactly one matching record in another table • Choose one table as the primary table with the primary key • Split the REGIONS table into two tables • Table one: physical characteristics of the region such as the area • Table two: information about home ownership rates • Have the same number of records in each table • Tables share a one to one relationship • But they contain different fields Prof. Leighton

5. One to Many Relationship • REGIONS table contains a primary key, Region ID • STATES table has foreign key,Region ID • One record in the first table (the REGION table) matches many records in the second table (the STATES table) • One record in the STATES table matches only one record in the REGIONS table • If you join the two tables you will have as many records as you have in the table with the foreign key, the many side of the relationship • Many may mean zero or only one. (For example, one employer could have advertised none or only one job.) Prof. Leighton

6. One to Many Relationship, Cont.. • The one table, the table with the primary key, is the primary table • The many table or the table with the foreign key is the related table Prof. Leighton

7. Many to Many Relationship • One record in the first table matches many records in the second table • One record in the second table matches many records in the first • These situations are handled by creating a number of one to many relationships • Example: registration at a university • Many classes and many students Prof. Leighton

9. Establishing the Relationship

10. The Relationship

11. Referential Integrity - Internal Consistency • Can’t accidentally delete or change related data • System of rules to ensure relationships between related tables are valid • Can’t enter a value in the foreign key field (related table) that doesn’t exist in the primary key (primary table) • Can’t delete record from a primary table if there are matching records in a related table • Can’t change a primary key value in primary table if that record has related records • Enforce referential integrity is an option Prof. Leighton

12. Override Referential Integrity • Set referential integrity on • Override • Cascade deletes - when you delete a record from a primary table, Access automatically deletes from all related tables those records with a foreign key value equal to the primary key value you deleted • Cascade updates - when you change a primary key value, Access automatically changes all related tables’ foreign key values that equal the primary key value Prof. Leighton

13. Override Referential Integrity

14. Print the Relationship

15. Print the Relationship

19. The End