Cardinality (data modeling)

Cardinalities are quantities, is defined by the in data modeling for entity-relationship diagrams (ER- diagrams ) for each relationship type, how many entities of an entity type with exactly one entity of the other on the type of relationship involved entity (and vice versa) can be related or need.

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

To represent the cardinality exist various forms of notation with combinations of numbers, letters or graphic symbols; see notations in the ER model. For example, only shown for simplicity with the Chen notation and other forms of notation, how many entities can most stand in relationship to a given entity or Entitätskombination. With minimum and maximum figures, however, can be the cardinality specify in more detail.

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

The cardinality, listed on the connecting edges for descriptive diamond or ( in the absence of diamond) at the connecting line between the involved ( two in this case ) entity types. In the min-max cardinality notation in the ERD diagram is reversed to Chen notations positioned ('1, 1 ' next EMPLOYEES, '0 ' n ' next DEPARTMENT ) - but this is not always practiced.

The information shall be used in the technical database design correctly implement the quantitative related determinations each relationship type and, if necessary, to specify additional constraints that should ensure a database system; eg: A EMPLOYEE must be assigned to a DEPARTMENT.

Distinguish the fundamentally different meanings of " cardinality " in cardinality (databases) of the relationship types described here (in the context of an ER model ) term.

  • 4.1 1:1
  • 4.2 1: n
  • 4.3 n: m

Detailed definition

If the Chen notation used to specify the cardinalities, then each entity type participate either with a cardinality 1 or with a cardinality of N on the relationship type. By cardinality 1 is a partial function is defined that states that the entities of this entity are functionally dependent on the combination of the other involved in the relationship type entity. If the min-max notation used, is replaced by " min, max " is defined per entity type that each entity of this type participates in at least min and at most max relationship to the relationship type.

Classification

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

  • N: m

In this basic form, the relationship sets are only at its maximum statement called - which typically is applied only in the early stages of modeling so. To implement database design detailed information is required, which is possible by using a min-max notation: This is determined by an additional "Min - declaration 'with '0' or ' c' ( ' conditioned ') that the relationship optional is - or at '1 'and that the relationship (with ' n ' at least once ) must exist. Examples: 1.1: 0, n or 1: 1c

With additional information may be - but this is no longer part of the " cardinality " - further constraints are defined, such as may exist that most 3 relations or relations that only certain entities ( line relationship for departments with ' internal employees ' ... ) are allowed. The stability of relationships between entities can be defined and saved in the database design on attitudes toward referential integrity.

Examples

1:1

In a 1:1 relationship, an entity is in each case precisely precisely assigned to another entity.

Examples:

  • Marriage: One spouse is (usually - ie apart from special cases polygamous ) married exactly one spouse
  • License Plate: An approved vehicle has exactly one license plate number and any license plate belongs to exactly one vehicle (only for Germany, Switzerland, for example, has interchangeable number plates. . )

1: n (n: 1)

One entity on the one side of the relationship (master) are zero, one or more entities on the other side opposite (detail).

N: 1 is rarely indicated, since it is a right-to- left read 1: n. The Entitätsbezeichnungen are usually quoted on both sides of the relationship statement in the singular, for example, because the relationship statement " mother has children " would not make sense for reversal of the relationship statement and can be misleading, but the cardinality is always defined starting on both sides of exactly one entity.

Examples:

  • Art: A ( specific ) work of art can be issued ( at the same time ) only in a museum (as original); In a particular museum has several works of art are presented. ⇒ Museum 1: n artwork (s)
  • Family: A child has exactly one biological mother, this mother may have several children. ⇒ nut 1: n child (ren)
  • Our Solar System: The Sun orbiting several planets, but has (circled ) each planet exactly one sun. ⇒ Sun 1: n Planet (s)

N: m

On both sides of any number of entities can relate to each other.

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

Examples:

  • Student ↔ Professor: A professor teaches usually several students. A student attended lectures by several professors. ⇒ professor n: m student
  • Ownership of real estate: A property can include several owners ( homeowners association ). An owner can own multiple properties. ⇒ owner n: m property.
  • Shops ↔ Products: A shop can have various products, but the product just may be present in different stores. ⇒ shops n: m products.

Preliminary:

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

1:1

Here the primary key of one of the two tables is included as a foreign key in the other table in an additional column. Which of the tables happens, is technically irrelevant. In practice, one tries to show the real dependence 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 (eg triggers, UNIQUE constraints, etc.).

1: n

The detail table has an additional column holding the foreign key to the primary key of the master table. With a 1: n relationship is called the attribute that can have "instances" of several, so that the n-side ' A multi-valued attribute.

N: m

N: m - relationships can not be directly implemented in most relational databases. To realize an additional table is created that contains the primary key of both tables as a foreign key. The n: m relationship is thus dissolved, and you receive an additional database table, the two 1: n relationships realized.

Often, for the designation of the n: uses the names of the two tables involved m relationship realizing table; at the tables "student" and "professor" could be so hot the extra table "Student Professor ".

Belong to the n: m relationship other attributes, it is frequently formed a separate entity already in the ER model, which two separate one -to-many relationships occur. Example: hotel is reserved for person; new entity type ' reservation ' - with n: 1 relationship to person and hotel and other attributes such as reservation period, reservation status etc.

Database trigger Constraint (mathematics)
11859
de