Table Relationships

1. Table Relationships • Establishing table relationships is the way we draw together data from separate tables. This minimizes redundant data and maintains higher levels of database reliability. • Types of relationships • One-to-One • One-to-Many • Many-to-Many

2. Homework Reminder • First step was to develop list of tables and fields and have a partner look it over • As part of your database project, I will be looking for a minimum of three tables • As you develop and refine the list of tables and fields, it is useful to have copies of the intermediate steps with some annotation describing why you did particular refinements. This will help you in the project to describe why you made particular choices • Second step is to create your tables in access. I won’t be collecting this as homework, but this is part of the process of developing your database project

3. Table RelationshipsOne-to-One

4. Table Relationships • One-to-one relationships are relatively rare in most database structures. Further, one-to-one relationships can be thought of as a specialized case of one-to-many. • The most common type of “true” one-to-one relationship is when one table is a SUBSET TABLE of another. Example- faculty are a subset of all university staff members. Graduate students are also a subset of all university staff. Thus, one way of implementing a personnel database would be to have a staff table (with information common to all staff members such as social security number), and separate faculty and graduate student tables to hold data that applies only to each of these specialized type of staff members. • When you have a one-to-one relationship, the linkage is achieved by having the same primary key in both tables (e.g., social security number in the staff table, and in the faculty or graduate student table).

5. Table RelationshipsOne-to-Many

6. Table Relationships One-to-Many Example-Many fish are caught at each site Site TableIndividual Fish Table Stream Name (CPK) Stream Name (CPK/FK) Position (CPK) Position (CPK/FK) Site (CPK) Site (CPK/FK) Year (CPK) Year (CPK/FK) Month (CPK) Month (CPK/FK) Day (CPK) Day (CPK/FK) Width Fish_ID (CPK) Depth Species Code Temperature Length Conductivity Age Linkage is established by taking primary key (PK, or composite primary key CPK) from “One” side of relationship, and inserting that into the “Many” side of the relationship. This now becomes what is known as a “FOREIGN KEY”.

7. Many-to-Many Relationships Student TableClass Table Student_ID (PK) Class_ID (PK) First Name Class Name Last Name Class Description Street Instructor_ID City Max Enrollment State Zipcode Phone Problem here is how to link Students with classes. Each student can take multiple classes, and each class has multiple students.

8. Rectifying Many-to-Many Relationships Enrollment Student TableTable Class Table Student_ID (PK) Student_ID (CPK/FK) Class_ID (PK) First Name Class_ID (CPK/FK) Class Name Last Name Class Description Street Instructor_ID City Max Enrollment State Enrollment (???) Zipcode Phone Notice that the Enrollment Table serves to link the Student Table and the Class Table. As such, it has both of the primary keys from each of these tables as foreign keys. The relationship between Student Table and Enrollment Table is One-to-Many (because each student can take many classes, but there is only one Student_ID, Class_ID combination in the Student-Class Table for each student Class Table and Enrollment Table is One to Many because each student can be enrolled in a class only once

9. Table RelationshipsRectifying Many-to-Many

10. Creating Relationships in Access • Open Relationship Window (has three little boxes connected with lines) • Go to menu and select Relationships, then Show Table • Select Tables to add (usually all tables in database), then close Show Table • It’s handy to stretch each table display so you can see all the fields

11. Creating Relationships in Access • Usually I start with “biggest” tables meaning the table with the largest unit of observation. In this example, it is the stream list and the sample list tables • To create relationship between two tables using a single key, simply click on field and drag to same field in connected table

12. Creating Relationships in Access • Note that in this example, Access “knows” that the relationship is one to many • Can click check box to Enforce Referential Integrity • This insures that a record must exist in the “One” table before a corresponding record in the “Many” table can be entered • “Cascade” options help enforce referential integrity

13. Creating Relationships in Access • Cascade options • “Cascade Update Related Fields” automatically changes the primary key field values in the “Many” table if you change the primary key field values in the “One” table (show example) • “Cascade Delete Related Records” automatically deletes related records in the “Many” table if you delete a record in the “One” table

14. Creating Relationships in Access • Select “Join Type” – Note that this is a critical decision and depends on your “business rules”!!! • Option 1 includes only records where both are equal EQUIJOIN • Option 2 includes only records where “One” side exists • Option 3 includes only records where “Many” side exists

15. Join Types Illustration

16. Join Types Illustration Option 1- both equal

17. Join Types Illustration Option 2- All Records from Primary Table

18. Join Types Illustration Option 3- Doesn’t make sense when enforce referential integrity because can’t have a record in the “Many” table without a corresponding record in “One” table