WEEK 13: Relationships between tables in a Microsoft Access database

Types of Table Relationships

A relationship works by matching data in key columns, usually columns with the same name in both tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table.

There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined.

One-To-Many Relationships

A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint. In Access, the primary key side of a one-to-many relationship is denoted by a key symbol. The foreign key side of a relationship is denoted by an infinity symbol.

Many-To-Many Relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.

One-To-One Relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:

Divide a table with many columns.
Isolate part of a table for security reasons.
Store data that is short-lived and could be easily deleted by simply deleting the table.
Store information that applies only to a subset of the main table.

In Access, the primary key side of a one-to-one relationship is denoted by a key symbol. The foreign key side is also denoted by a key symbol.



REFLECTION:

We are going to key in all the cataloging information in the table by using these details:

Material Table:

1.Title
2.author
3.additional entries
4.edition
5.publisher
6.Publish year
7.ISBN /ISSN
8.call number
9.type of material
10.physical description
11.Subject 1 & 2
12.note

 Publisher Table:

1.Pub ID (code name publishers )
2.Pub NAME (full name of the issue)
3.Pub PLACE (Place published)