Database Relationships

1. Database Relations

An entity relationship is the link between two tables. It is created through having matching attributes in each of the two tables.

Within entity relationships we commonly model the relations between two tables as being either generic or semantic.

Generic entity relationships take a higher and more non-specific view of the relationship. It is more concerned with which entities are related to help you understand the overall structure of the database.

Semantic entity relationships incorporate more of the actual attributes of the entities, and shows the primary & foreign key attributes that create the relationship between the tables.

2. Relational Keys

Relationships between two tables are created through matching fields that appear in both tables. These fields are known as the relational keys and there are a few different kinds of key.

To demonstrate these different keys, we’ll use the example table below:

• Super Key – any attribute, or group of attributes, that can uniquely identify a tuple in a relation. In the table above, the following attributes or group of attributes are super keys.
  • {Employee_ID}
  • {Employee_Username}
  • {Employee_ID, Employee_Username }
  • {Employee_ID, Employee_Name}
  • {Employee_Username, Employee_Name}
  • {Employee_ID, Employee_Username, Employee_Name}
• Candidate Key – a super key that has no super key subsets. So, the smallest subset of super key attributes. In the table above, the following super keys are also candidate keys.
  • {Employee_ID}
  • {Employee_Username}
• Primary Key – a candidate key that has been selected to be the unique identifier of each tuple in a relation. We would choose one of the candidate keys above, Employee_ID or Employee_Username, to be our primary key.
• Foreign Key – an attribute of a table that is also a primary key in another table. This establishes a link, or relationship, between these two tables. In the table below, the Employee_ID attribute establishes the relationship with the Employee_ID primary key attribute in the table above.

• Composite Key – a combination of two fields that together can uniquely identify a record. This can be used as an alternative to a primary key.

3. Integrity Constraints

Integrity constraints are used to ensure the accuracy and consistency of data in your relational database. The main two types of integrity constraints are:

• Entity Integrity – This rule states that every entity (table) must have a primary key field and the data in that primary key field must be unique to each record.
• Referential Integrity – This rule states that every foreign key value must match the primary key value of a record in another table, or must be null. We’ll learn more about referential integrity in the lesson “The Role of Normalisation”.

4. Entity Relationships

We saw earlier that an entity relationship is the link between two tables and is created through having matching attributes in each of the two tables.

There are three different types of entity relationships that we can have. By far the most common form of entity relationship is the one-to-many relationship.

• One-to-One – A single record of a table is related to a single record of another table. This could be the relationship between a person and their passport. This would be illustrated as below:

• One-to-Many – A single record of a table is related to more than one record in another table. In this relationship the foreign key would appear in the many side of the relationship. This could be the relationship between a customer and their orders. This would be illustrated as below:

• Many-to-Many – More than one record of a table is related to more than one record of another table. This could be the relationship between students and their courses. This would be illustrated as below:

Unfortunately, many-to-many relationships cannot be represented by a relational database. Where a many-to-many relationship exists, we must resolve this into two one-to-many relationships. For example, the above student-course relationship can be resolved as below: