table relationships n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Table Relationships PowerPoint Presentation
Download Presentation
Table Relationships

Loading in 2 Seconds...

play fullscreen
1 / 19

Table Relationships - PowerPoint PPT Presentation


  • 138 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Table Relationships' - holmes-hatfield


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
establishing table relationships
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
Three Types of Table Relationships
  • One to one relationship
  • One to many relationship
  • Many to many relationship

Prof. Leighton

one to one relationship
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
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
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
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
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
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