Databases

Relational databases. The concept of a key. Types of keys. Relations. Master table and detail table

Relational databases. The concept of a key. Types of keys. Relations. Master table and detail table

In this topic, using the example of two tables, the basic concepts of relational databases are defined, namely:

  • primary key;
  • foreign key;
  • simple and complex keys;
  • relation, types of relations;
  • artificial and natural keys;
  • master table and detail table.

Contents

Search other websites:




Input data

Let the database of employees of the enterprise, which consists of two tables, be given. The first table contains data about the employee. The second table contains information about the employee’s wages.

The tables have the following structure.

Table “Employee”. Contains employee data.

database table photo

Table “Salary”. Contains information on the wages of employees.

database table photo

⇑

Question/Answer

1. What is the primary key in the database table? What are primary keys used for?

When working with tables in relational databases, it is desirable (necessary) that each table has a so-called primary key.

The primary key is the field that is used to ensure the uniqueness of the data in the table. This means that the value (information) in the primary key field in each row (record) of the table can be unique.

Uniqueness is necessary to avoid ambiguity, when it is not known which table entry can be accessed if there are duplicate entries in the table (two records have the same values in all fields of the table).

Example. For the Employee table, you can enter an additional field, which will be the primary key. However, the field (attribute) “Personnel number” also provides uniqueness. Since, theoretically, there can not be two identical personnel numbers. In practice, there may be cases where the same personnel number will be entered by mistake and the values of all fields in the table will equals. As a result, two identical entries appear in the table. To avoid such an error, it is better to create an additional counter field in the table that will ensure uniqueness.

Also for the “Salary” table, you can enter an additional field, which will be the primary key.

⇑

2. What is the ‘relationship’ between the tables? Example

Tables in the relational data model can have relationships. For the “Employee” and “Salary” tables, you can establish a relationship by the “Personnel Number” field.

Example. Let’s analyze the tables “Worker” and “Salary”. In these tables, you can set the relationship between the tables based on the “Personnel Number” field. That is, the relationship between the tables is based on the field (attribute) “Personnel Number.”

This means the following. If you need to find the accrued wages in the “Salary” table for employee Johnson J.J., then you need to perform the following actions:

  • find the personnel number of the employee Johnson J.J. in the table “Worker”. The personnel number is 7585;
  • in the table “Salary” find all the values that are equal to 7585 (personnel number);
  • select from the “Salary” table all the values of the field “Accrued”, which correspond to the number 7585.

database relationship tables photo

Figure 1. Illustration of the relationship between the tables. Personnel number 2145 of the table “Employee” is displayed in the table “Salary”

database tables relationship photo

Figure 2. Relationship (relationship) between fields of tables

⇑

3. What is a foreign key? Example

The concept of “foreign key” is important when considering related tables.

A foreign key is one or more fields (attributes) that are primary in another table and whose value is replaced by the values of the primary key of the other table.

Example. Let there be a relationship between the “Employee” and “Salary” tables in the field “Personnel number”. In this case, the “Personnel number” field of the “Employee” table can be a primary key, and the “Personnel number” field of the “Salary” table can be a foreign key. This means that the values of the “Personnel Number” field of the “Salary” table is replaced by the values in the “Personnel number” field of the “Employee” table.

⇑

4. What is a recursive foreign key?

A recursive foreign key is a foreign key that references the same table to which it belongs. In this case, the field (attribute) that corresponds to the foreign key is the key of the same relation.

⇑

5. Can the primary and foreign keys be simple or complex?

Primary, secondary and foreign keys can be either simple and complex. Simple keys are keys that contain only one field (one attribute). Compound (complex) keys are keys that contain several fields (attributes).

⇑

6. What is the difference between an artificial and natural key? Example

The natural key provides uniqueness from the very essence of the subject domain. There are cases when the values of records of some field (s) of the table are unique. This field can be a natural key.

An artificial key is added to provide unique values. Most often, an artificial key is a field of ‘counter’ type. In this field, when a new record (row) is added to the table, the value of the counter is incremented by 1 (or other value). If the record is deleted from the table, the maximum value of the row counter does not decrease, but remains as it is. As a rule, everything is monitored by the database management system.

Example. In the table “Employee” the natural key is the field (attribute) “Personnel Number”. The “Personnel number” field is unique in itself, as there can not be two employees with the same numbering number.

In the table “Salary” the value in all four fields can be accidentally repeated. Therefore, here it is advisable to add an additional field-counter, which will be an artificial key. In this case, the “Salary” table with an additional field can have approximately the following form:

database table salary photo

where the “Number” field is an artificial key that provides uniqueness.

⇑

7. What are the ways to choose a primary key?

There are 3 ways to select a primary key:

    • use the field-increment as an artificial key;
    • select from the data one field that can provide uniqueness;
    • select from the data several fields that can provide uniqueness. In this case, the key will be called complex (composite).

⇑




8. What do the terms “master table” and “detail table” mean?

If there is a link between the tables, then one of them can be the master, and the other can be a detail. The master table displays all the entries that are placed in it. The detail table displays only those records that match the value of the key of the master table, which is currently active (current). If the current record of the main table changes, then the set of available records of the subordinate table is changed.

Example. If you look at the “Employee” and “Salary” tables, then the “Employee” table is the master table, and the “Salary” table is a detail table.

⇑

9. What are the types of relationships between the tables?

There are 4 basic types of relationship between tables:

  • “one to one”. In this case, each record in one table corresponds to one record in another table;
  • “one to many”. This is when a single record of the master table corresponds to several records of the detail table. That is, each record, which is the primary key of one table, corresponds to several records of the linked table;
  • “many to one”. This is when a single record of a detail table corresponds to several records of the master table;
  • “many to many”. This is when there are several interrelated records in both tables.

Example. If we consider the relationship between the tables “Worker” and “Salary”, then this relationship is a type of “one to many”. The “Worker” table is the master table. Table “Salary” is a detail table.

⇑

Related topics

⇑