Page 222 - Computer - 10(A&B)

Types of Relationship

There are three types of relationships:
v One-to-one: Both tables can have only one record on either side of the relationship. Each primary key value
relates to only one (or no) record in the related table.
For example, each record in the Employee table is about one employee. That record relates to one, and only
one, record in the Payroll table. Each record in the Payroll table relates to one, and only one, record in the
Employee table.

Employee
1 Payroll
EmpId
1
First Name EmpId
Last Name Salary
Department

v One-to-many: It is used to create a relationship between two tables where a single row of the first table can
be related to one or more rows of the second tables, but the rows of second tables can relate to only one row
in the first table.
For example, a customer can check out any number of books, so a single record in the CUSTOMER table
can be related to one or more records in the LIBRARY table. A single book, however, is associated with
only one customer at any given time, so a single record in the LIBRARY table is related to only one record
in the CUSTOMER table.
Customer Library
CustID 1 CustID
First Name BookID

Last Name Book Name
DOB Date of Issue
v Many to many: Many to many relationship exists between tables when each record of the first table can
relate to any records (or no records) in the second table. Similarly, each record of the second table can also
relate to more than one record of the first table.
For example, a business probably needs Customers and Orders tables, and likely also needs a Products
table.
The relationship between the Customers and Orders table is one-to-many, but consider the relationship
between the Orders and Products table. An order can contain multiple products, and a product could be
linked to multiple orders since several customers might submit an order that contains some of the same
products.

104