Cardinality (database modeling)

from Wikipedia, the free encyclopedia

Cardinalities are quantities used in data modeling for entity relationship diagrams (ER diagrams) to determine for each relationship type how many entities of one entity type can be related to exactly one entity of the other entity type involved in the relationship type (and vice versa) have to.

Example: EMPLOYEE works in DEPARTMENT (n: 1)
Each employee works in 1 department; n EMPLOYEES can work in each DEPARTMENT

There are various forms of notation with combinations of numbers, letters or graphic symbols to represent the cardinality; see notations in the ER model . For example, the Chen notation and other forms of notation only show in a simplified manner the maximum number of entities that can be related to a given entity or combination of entities . With minimum and maximum information, however, the cardinality can be specified more precisely.

Example: EMPLOYEE works in DEPARTMENT (0, n: 1,1)
Every employee works in exactly 1 department (there is no one who doesn't work in any department).
0 to n EMPLOYEES can work in each DEPARTMENT (there are also departments without employees)

The cardinality information is noted on the connecting edges to the descriptive diamond or (if there is no diamond) on the connecting line between the (in this case two) participating entity types. In the Min-Max notation , the cardinality in the ERD graphic is positioned the other way round to Chen notations ('1,1' next to EMPLOYEES, '0, n' next to DEPARTMENT) - which is not always done this way.

The information is used to correctly implement the quantity-related specifications for each relationship type in the technical database design and, if necessary, to specify further integrity conditions that a database system should ensure; z. B .: An EMPLOYEE must be assigned to a DEPARTMENT.

The meaning of cardinality for relationship types (in the context of an ER model or in database modeling) must be distinguished from the concept of cardinality in databases .

Full definition

If the Chen notation is used to specify the cardinalities, then each entity type can participate in the relationship type either with a cardinality of 1 or with a cardinality of N. Cardinality 1 defines a partial function , which states that the entities of this entity type are functionally dependent on the combination of the other entity types involved in the relationship type.

If the min-max notation is used, the specification "min, max" for each entity type defines that each entity of this type has at least a min and a maximum of max. Relationship type relationships.

Classification

The most common relationships are classified in terms of their cardinality in their basic form as follows:

  • 1: 1
Each entity of one entity type is related to an entity of the other entity type; the same applies to the opposite direction.
  • 1: n
Each entity of one entity type is related to any number of entities of the other entity type. In the opposite direction, each entity of one entity type is related to an entity of the other entity type.
  • n: m
Each entity of one entity type is related to any number of entities of the other entity type; the same applies to the opposite direction.

In this basic form , the relationship sets are only mentioned with their maximum statement - which i. d. This is usually only used in early modeling stages. For implementation in the database design, more precise information is required, which is possible by using a min-max notation : This means that an additional "min" with '0' or 'c' ('conditioned') specifies that the relationship is optional is - or with '1' that the relationship (with 'n' at least once) must exist. Examples: 1,1: 0, n or 1: 1c

With additional information - this is no longer part of "cardinality" - further integrity conditions can be defined, e.g. B. that a maximum of 3 relationships may exist or that relationships are only permitted to certain entities (management relationship for departments only with 'internal employees' ...). The stability of relationships between entities can be specified and secured in the database design using settings for referential integrity .

Examples

1: 1

In a 1: 1 relationship, exactly one entity is assigned to exactly one other entity.

Examples :

  • Marriage: A spouse is (usually - ie apart from special polygamous cases) married to exactly one spouse
  • License plate: A registered vehicle in Germany had exactly one license plate up to July 2012 and each license plate belonged to exactly one vehicle (see also exchange license plates )
Notation 1: 1
Cardinality 1: 1

1: n

An entity on one side of the relationship (master) is opposed to none, one or more entities on the other side (detail).

n: 1 is rarely specified because it is a 1: n read from right to left. The entity names are usually noted in the singular on both sides of the relationship statement, because z. For example, the relationship statement “mother has children” would make no sense and could be misleading if the relationship statement was reversed, but the cardinality is always defined on both sides based on exactly one entity.

Examples :

  • Art: A (specific) work of art can (at the same time) only be exhibited in a museum (as an original); In a particular museum, however, several works of art are presented. → Museum 1: n artwork (s)
  • Family: A child has exactly one birth mother; this mother can have several children. → Mother 1: n child (ren)
  • Our planetary system: The sun orbits several planets, but each planet has (orbited) only exactly one sun. → Sun 1: n planet (s)
Notation 1: n
Cardinality 1: n
Preliminary consideration: Cardinality 1: n (preliminary consideration)

n: m

Any number of entities can be related to each other on both sides.

A common typo is: n: n . But that would imply that there are the same number of entities on both sides.

Examples :

  • Student ↔ Professor: A professor usually teaches several students. One student listens to lectures from several professors. → Professor n: m student
  • Ownership of real estate: A real estate can belong to several owners (community of owners). One owner can own multiple properties. → Owner n: m property.
  • Shops ↔ Products: A shop can have different products, but the same product can be available in different shops. → Shops n: m products
  • Customer ↔ product: A customer can buy different products, but the same product can be bought by several customers. → Customer n: m product
Notation n: m

Cardinality m: n

Preliminary consideration: Cardinality m: n (preliminary consideration)

Implementation of the ER model in database tables in the relational data model

1: 1

The primary key of one of the two tables is included in an additional column as a foreign key for the other table. In which of the tables this happens is technically irrelevant. In practice, one tries to represent the real dependency by including the primary key of the master table in an additional column of the detail table. In addition, it must be ensured that the values ​​in the column with the foreign key appear only once (e.g. through triggers , UNIQUE constraints , etc.).

1: n

The detail table has an additional column that contains the primary key of the master table as a foreign key. In a 1: n relationship, the attribute that can have several "instances" is called that of the n-side 'multiple attribute'.

n: m

Many-to-many relationships can not be implemented directly in most relational databases . To implement this, an additional table is created, which is referred to as an auxiliary table . This auxiliary table contains the primary keys of both tables as foreign keys. The n: m relationship is therefore resolved by the auxiliary table, and a further database table is obtained which implements two 1: n relationships that are directly related to one another.

Often the names of the two tables involved are used to designate the table realizing the n: m relationship; for the tables “Student” and “Professor” the additional table could be called “StudentProfessor”.

If further attributes belong to the n: m relationship, a separate entity type is often created in the ER model, which results in two separate 1: n relationships. Example: hotel is reserved for person; New entity type 'Reservation' - with n: 1 relationships to person and hotel and other attributes such as reservation period, reservation status, etc.

literature

  • Alfons Kemper, André Eickler: Database systems. An introduction. R. Oldenbourg, Munich 1999, ISBN 3-486-27392-2 .
  • Ramez Elmasri, Shamkant Navathe: Basics of database systems. Pearson Studium, Munich 2002, ISBN 3-8273-7136-8 .
  • Tobias Eggendorfer: Database systems for business IT specialists . Books on Demand, Norderstedt 2005, ISBN 3-8334-2493-1 .
  • Helmut Jarosch: Database draft. An example-based introduction for students and practitioners. Vieweg, Wiesbaden 2002, ISBN 3-528-15800-X .
  • Hans Schwinn: Relational Database Systems. Hanser, Munich 1992, ISBN 3-446-15782-4 .
  • Hermann Sauer: Relational Databases. Addison-Wesley Verlag, Munich 2002, ISBN 3-8273-2060-7 .