Global Joins

Classes

Global Joins

Key Terms: One to One - One to Many - Many to Many- Primary Key - Foreign Key- Common Field - Entity Integrity - Referential Integrity - Cascade Update - Cascade Delete - Alternate Keys - Composite Key - Candidate Key

One to One - exists between two tables (relations) when each row in one relation (table) has at most one matching row in the other table (relation).

One to Many - exists between two relations (tables) when one row in the first relation matches many rows in the second relation (table) and one row in the second relation (table) matches only one row in the first table (relation).

For example: to track student classes and grades.

Solution:

Step 1 is to identify a common field between the tables that you are going to create. Common fields must be of the same data type. In this case one student can take one or many classes. We can use a one to many relationship to keep track of a students classes and grades. The common field will be the students SSN. The primary table (the table containing the one relationship) will be called student and must have the common field SSN be unique, not null (blank), and the first index of the table. When this condition exist the field is called a primary key. The entity integrity of a primary key is that it is unique in content, never blank, and the records are ordered (indexed) first on the primary key SSN in the table. The related table (the table containing the many relationship) will be called classes and the common field SSN is called the foreign key in the related table.

student	classes
SSN primary key-one	SSN foreign key - many
FirstName	SectionNo.
MI	ClassName
LastName	Grade

When a one to many relationship is created in access you will be asked to select the following three options.

1. Referential Integrity - when selected will not allow you to enter a SSN into the foreign key in the classes table unless it already exist in the primary key SSN of the Student table.

2. Cascade Update - when this option is selected any change you make to the primary key content in the student table will be updated in the foreign key of related records in the classes table.

3. Cascade Delete - this option when selected will deleted all related records in the classes table if you delete a record in the student table.

Many to Many - exists between two relations (tables) when one row in the first table (relation) matches many rows in the second relation (table) and one row in the second table (relation) matches many rows in the first table (relation). A third table must be used to serve as a bridge between the two tables and contains the primary keys of the two tables in a one to many relationship with them.

One attribute, or collection of attributes that could serve as a primary key are called a candidate keys. Once a key is chosen as the primary key the remaining keys are referred to as alternate keys. A primary key that is composed of several attributes is called a composite or concatenated key.