F. SQL Relationships

1. What are Entities and Relationships?

Entity:

An entity can be a real-world object, either tangible or intangible, that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities. Each entity has some associated attributes that provide it an identity.

Relationships:

Relations or links between entities that have something to do with each other. For example, an employee's table in a company's database can be associated with the salary table in the same database.

2. List the different types of relationships in SQL.

A. One-to-one Relationship

Such a relationship exists when each record of one table is related to only one record of the other table.

For example each person can have only one passport and each passport belongs to only one person.

B. One-to-many Relationship

Such a relationship exists when each record of one table can be related to one or more than one record of the other table. This relationship is the most common relationship found. A one-to-many relationship can also be said as a many-to-one relationship depending upon the way we view it.

For example, each ‘Customer’ can have more than one ‘Account’ but each ‘Account’ is held by only one ‘Customer’.

C. Many-to-many Relationship

Such a relationship exists when each record of the first table can be related to one or more than one record of the second table and a single record of the second table can be related to one or more than one record of the first table. A many-to-many relationship can be seen as a two one-to-many relationship which is linked by a 'linking table' or 'associate table'. The linking table links two tables by having fields which are the primary key of the other two tables.

For example each customer can buy more than one product and a product can be bought by many different customers.