Database Relationships

A relational database is made up of more than one table and these tables will be linked together by certain fields. Where these fields are linked, this is called a relationship. This allows us to organise our data in a much more efficient manner.

This lesson we’ll learn about key relational database terminology, including:

  1. Database relations
  2. Relational keys
  3. Integrity constraints
  4. Entity relationships

Media Attachments: Presentation

1. Database Relations

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.

Further Thought

There are different types of entity relations that we can have. What are these? (Clue: We discuss these further down the page).

2. Relational Keys

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:

Employee_IDEmployee_UsernameEmployee_Name
17328Steve
29843Anna
32389Ahmed
44739Tina
  • 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.
Salary_IDDateSalaryEmployee_ID
1Dec-2017£15001
2Dec-2017£17002
3Dec-2017£20003
4Jan-2018£16501
  • 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.

Further Thought

Why might we use a composite key instead of a normal single field primary key?

3. Integrity Constraints

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”.

Further Thought

Another type of integrity constraint is domain integrity. What is this?

4. Entity Relationships

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-One Relationship
  • 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:
One-To-Many Relationship
  • 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:
Many-To-Many Relationship

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:

Resolved Many-To-Many Relationship

Further Thought

One example of a many-to-many relationship we commonly need to represent in a database is between a customer and the products they buy. How might we resolve this? Check out the lesson “The Normalisation Process” for help.

Lesson Summary

  • An entity relationship is the link between two tables. It is created through having matching attributes in each of the two tables.
  • Generic entity relationships take a higher and more non-specific view of the relationship whereas semantic relationships are more detailed.
  • A super key is any attribute, or group of attributes, that can uniquely identify a tuple in a relation. A candidate key is a super key that has no super key subsets.
  • A primary key is a candidate key that has been selected to be the unique identifier of each tuple in the relation. A foreign key is an attribute of a table that is also a primary key in another table.
  • A composite key is a combination of two fields that together can uniquely identify a record.
  • Integrity constraints are used to ensure the accuracy and consistency of data in your relational database. Two example constraints are entity integrity and relational integrity.
  • A one-to-one relationship is where a single record of a table is related to a single record of another table.
  • A one-to-many relationship is where a single record of a table is related to more than one record in another table. This is the most common kind of relationship.
  • A many-to-many relationship is where more than one record of a table is related to more than one record of another table. This has to be resolved into two one-to-many relationships.

QuizzesStatus