190 likes | 335 Views
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
E N D
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 Prof. Leighton
Three Types of Table Relationships • One to one relationship • One to many relationship • Many to many relationship Prof. Leighton
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
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
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
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
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
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