1 / 19

Table Relationships

Table Relationships. RDBM. 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. Three Types of Table Relationships. One to one relationship

diamond
Download Presentation

Table Relationships

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. 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

  8. Establishing the Relationship

  9. The Relationship

  10. 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

  11. 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

  12. Override Referential Integrity

  13. Print the Relationship

  14. Print the Relationship

  15. The End

More Related