Basic concepts of the relational data model

Basic concepts of the relational data model


Content
  1. What are the basic concepts of the relational data model?
  2. What is the data type in the relational data model?
  3. What data types are supported by database management systems?
  4. Domains in the relational data model
  5. Attributes in the relational data model
  6. What is a relationship scheme? What is a database schema?
  7. What is the degree of relationship?
  8. What is a tuple in databases?
  9. What is called the cardinal number or power of relation?
  10. What is an empty value (NULL) in the database?
  11. What are relationship keys? What is the primary key?
  12. What is the simple and composite (complex) keys?
  13. What is an artificial (surrogate) key?
  14. What is a natural key?
  15. What are the advantages and disadvantages of using natural keys?

1. What are the basic concepts of the relational data model?

As is known, the relational data model is based on saving data in the form of related tables. The relationship between the tables can be implemented within a certain field and is called a relation.

The relational data model uses the following basic concepts:

  • data type;
  • domain;
  • attribute;
  • relationship scheme;
  • tuple;
  • key.

 

2. What is the data type in the relational data model?

In the programming language the data type is the characteristic of the object. Such an object can be a variable, a constant, etc. The data type defines an acceptable set of values that a variable or object can take.

In database management systems, the data type has the same meaning as in programming languages.

Example. Let the ‘Worker’ table, which describes the data about the employee of the enterprise, be given.

Identification code Name Address Date of Birth Sex Category
2931123455 Johansson S. Los Angeles 12.06.1897 M 3
3425526651 Johnson K. London 11.03.1998 M 4
2765165253 Becker B. Manchester 18.02.1987 M 2
3293847890 Locksley R. Sherwood 10.08.1937 F 3
2298489472 Lincoln L. Milan 12.06.1990 F 4
3234802998 Ferguson A. Paris 11.02.1993 M 5

In the above table it is advisable to set the next data type for each field:

  • field “Identification code” – a string of characters (string type). The code is a number of 10 symbols, so it is presented as a string. However, if desired, you can submit it as a long integer;
  • Name – the ‘string’ type;
  • Address – the ‘string’ type;
  • Date of Birth – Data type “Date/Time” (Date/Time);
  • Sex – logical type (True – Female, False – Male);
  • “Category” – integer.

 

3. What data types are supported by database management systems?

 Modern DBMSs support the following basic data types:

  • integers;
  • real (floating point);
  • strings;
  • data types that describe the date and time;
  • specialized types of data that describe the monetary value;
  • The data types that describe complex Binary Objects (Binary Large Object).

 

4. Domains in the relational data model

A domain is a set of separate valid data values that:

  • are indivisible (atomic) for this model;
  • have the same type.

Example. Let the ‘Worker’ table describing the employee data be given.

Identification code Name Address Date of Birth Sex Category
2931123455 Johansson S. Los Angeles 12.06.1897 M 3
3425526651 Johnson K. London 11.03.1998 M 4
2765165253 Becker B. Manchester 18.02.1987 M 2
3293847890 Locksley R. Sherwood 10.08.1937 F 3
2298489472 Lincoln L. Milan 12.06.1990 F 4
3234802998 Ferguson A. Paris 11.02.1993 M 5

In the domain “Identification code”, valid are symbols that have strictly 10 digits. In the “Sex” domain, only 2 values are possible. The domain “Category” can have integer values from 1 to 6.

 

5. Attributes in the relational data model

Attributes are table columns (table fields). Attributes have names. By the attribute name, the table is accessed.

Example. In the ‘Worker’ table (see section 4), the attribute names are as follows:

  • Identification_code;
  • Name;
  • Address;
  • Date_of_Birth;
  • Sex;
  • Category.

 

6. What is a relationship scheme? What is a database schema?

A relationship schema is a list of the names of the relationship attributes with the names of the types.

Example. For the ‘Worker’ table, the relationship scheme will be approximately as follows:

WORKER { [Identification_code] char(10),
         [Name] char(20),
         [Address] char(100),
         [Date_of_Birth] DateTime,
         [Sex] bool,
         [Category] int }

A set of named relationship schemes is called a database schema.

 

7. What is the degree of relationship?

The number of attributes in the table is called the degree of the relationship. For an example (see paragraph 4) of the ‘Worker’ table, the degree of relationship is 6 (the table has 6 fields).

A unary relation is a relation of degree one. A binary relation is a relation of degree two. A ternary relation is a relation of degree three. An n-ary relation is a relation of degree n.

 

8. What is a tuple in databases?

A tuple is considered for a particular (given) relationship scheme. In this scheme, a tuple is a set of pairs, which are represented as follows:

{ Attribute_name, value }

where Attribute_name – the name of the specific attribute.

For example. Let the table ‘Worker’ with such data be given

Identification code Name Address Date of Birth Sex Category
2931123455 Johansson S. Los Angeles 12.06.1897 M 3
3425526651 Johnson K. London 11.03.1998 M 4
2765165253 Becker B. Manchester 18.02.1987 M 2
3293847890 Locksley R. Sherwood 10.08.1937 F 3
2298489472 Lincoln L. Milan 12.06.1990 F 4
3234802998 Ferguson A. Paris 11.02.1993 M 5

The relationship scheme for this table will be as follows:

WORKER { [Identification_code] char(10),
         [Name] char(20),
         [Address] char(100),
         [Date_of_Birth] DateTime,
         [Sex] bool,
         [Category] int }

Then the tuple, that corresponds to the first row of the ‘Worker’ table, will look like this:

{ [Identification_code], '2931123455' }
{ [Name], 'Johansson S.' }
{ [Address], 'Los Angeles' }
{ [Date_of_Birth], '12.06.1897' }
{ [Sex], False }
{ [Category], 3 }

In this manner, it can be determined tuple that corresponds to the second row of the table ‘Worker’ as well as the following lines of the table.

 

9. What is called the cardinal number or power of relation?

The cardinal number is the number of tuples. In the ‘Worker’ table (see clause 8) the cardinal number is 7. The cardinal number is also called the power of the relation.

 

10. What is an empty value (NULL) in the database?

There are cases when in the database table some values are not yet known at this time. Such values are called empty values and can be filled later. To specify empty values, the database uses the word NULL. Database management systems allow the use of the NULL value to specify data that can be filled later.

It should be noted that the NULL value is not null and is not an empty string.

For example. In the table ‘Worker’ (item 8), it is possible that the employee does not yet have a category. In this case, enter the NULL value in the corresponding cell. Once a worker is assigned a certain category, the NULL value will be replaced with this new value.

 

11. What are relationship keys? What is the primary key?

An important condition for any database is that there should not be two identical records in it. Or in other words, there should not be two tuples in the database table that contain the same values. To avoid this problem, primary keys are used. The primary key is a special additional field (attribute) of the table, which is created to ensure the uniqueness of identifying table records. The main purpose of creating a primary key is to prevent duplication (repetition) of table entries.

For example. Let the ‘Worker’ table be given (see section 8). To avoid repeated records, an additional field (attribute) with a name (for example, ID_Worker) can be created in this table. The type of this field can be selected as a counter, which automatically increases when adding a new record to the table.

 

12. What is the simple and composite (complex) keys?

A simple key is a key that contains only one attribute (field). A complex or compound key is a key that contains several attributes, that is, it consists the several fields, whose values can not be repeated.

Example. Let the ‘Student’ table containing student data be given. The table contains the following fields:

Field name Type Description
ID_Student Integer (int) Unique field identifier, counter, primary key, simple key
Num_book Integer (int) Number of the record book
Name String, char(100) Surname and name of the student
Course Integer (int) The course at which the student is studying

In this table, the ID_Student field is the primary key that provides uniqueness. This field is a counter. When adding a new student to the table, the counter value increases by a certain number, usually by 1. If you delete a student from the table, the maximum value of the counter does not decrease. This provides a unique number that corresponds to this student.

In the Student table, a composite key can be a combination of the fields (attributes) ID_Student and Num_book (number of the record book). However, in this table such a combination does not make sense, because the ID_Student field already provides uniqueness.

 

13. What is an artificial (surrogate) key?

An artificial key is created by the DBMS itself or by the user. This key does not contain any information. An artificial key is used to create unique string identifiers. The creation of the row identifier is performed in such a way that the entity of the string is described in its entirety. This method allows you to uniquely identify a specific element (value).

The database management system supports the artificial key so that it is invisible to the user.

 

14. What is a natural key?

A natural key is based on the attributes (fields) that make sense. The value in such attributes (fields) can not be repeated in their essence.

Using natural keys allows you to get a more compact form of tables to represent data.

Example 1. In the Worker table (see item 8), the “Identification code” field is unique, since there can not be two people with the same identification code. This field is the natural key.

Example 2. In the Student table, the Num_book field (the record book number) is unique in nature. There can not be two students with the same record book number.

 

15. What are the advantages and disadvantages of using natural keys?

The advantage of using natural keys is that they carry information, and therefore do not need to add additional fields to the table. Natural keys allow you to avoid redundant (uninformative) information, which is used only for communication between database tables.

The main disadvantages of natural keys:

  • the value of the natural key should not be changed. For example. In case of loss of the student’s record book, he can get another student’s record book with another number. Thus, the relationship between table relationships can be broken. This, in turn, will lead to the need to perform record changes in several tables;
  • if the natural key is represented by a string, then the processing speed of such a key is lower than when a simple key (counter) is represented as an integer value. In this case (from the point of view of speed), the use of natural keys is inefficient.

Related topics