Semantic Attributes of Foreign Keys

Semantic Attributes of Foreign Keys

A foreign key describes a relationship between two tables. This relationship can be described in more detail by specifying the cardinality and type of the foreign key fields.

The definitions of the cardinality and type of the foreign key fields are not taken into consideration in the value check. This information is optional and is primarily for documentary purposes.

Cardinality

The cardinality (n:m) describes the foreign key relationship with regard to the number of possible dependent records (records of the foreign key table) or referenced records (records of the check table).

The left and right sides of the cardinality are defined as follows:

n=1: There is exactly one record assigned to the check table for each record of the foreign key table.

n=C: The foreign key table may contain records which do not correspond to any record of the check table because the foreign key field is empty. This can occur for example if the field of the foreign key table is optional, in which case it does not have to be filled.

m=1: There is exactly one dependent record for each record of the check table.

m=C: There is at most one dependent record for each record of the check table.

m=N: There is at least one dependent record for each record of the check table.

m=CN: There may be any number of dependent records for each record of the check table.

Type of Foreign Key Fields

The Type of foreign key fields describes what the foreign key fields in the foreign key table mean.

The following types of foreign key field can be defined:

No key fields/candidates: The foreign key fields are neither primary key fields of the foreign key table nor do they uniquely identify a record of the foreign key table (key candidates). For this reason, the foreign key fields do not (partially) identify the foreign key table.

Key fields/candidates: The foreign key fields are either primary key fields of the foreign key table or they already uniquely identify a record of the foreign key table (key candidates). The foreign key fields therefore (partially) identify the foreign key table.

Key fields of a text table: The foreign key table is a text table for the check table, that is the key of the foreign key table only differs from the key of the check table in that it has an additional language key field. This is a special case of the type Key fields/candidates.

The foreign key between the tables SBOOK and SCOUNTER ensures that only existing counters can be entered in field COUNTER (counter at which the flight was booked). See the example in Foreign Keys.

A booking can be made at either a travel agency or at the carrier’s sales counter. If the booking is made at a travel agency, the field COUNTER of table SBOOK remains empty. The foreign key fields do not have to be filled, that is the left side of the cardinality is C. Any number of bookings may be made at each counter. There may therefore be any number of entries (bookings) in foreign key table SBOOK for each record of the check table SCOUNTER. The right side of the cardinality is therefore CN.

Of course several bookings can be made for the same carrier at a counter. These bookings do not differ in their foreign key fields (MANDT, CARRID, COUNTER). The entries in the foreign key fields therefore do not uniquely identify an entry in the foreign key table SBOOK (a booking). The foreign key fields therefore have the type No key fields/candidates.