Cardinality (data modeling)

Last updated

Within data modelling, cardinality is the numerical relationship between rows of one table and rows in another. Common cardinalities include one-to-one, one-to-many, and many-to-many. Cardinality can be used to define data models as well as analyze entities within datasets.

Contents

Relationships

For example, consider a database of electronic health records. Such a database could contain tables like the following:

Natural relationships exist between these entities:

Modeling

In data modeling, collections of data elements are grouped into "data tables" which contain groups of data field names called "database attributes". Tables are linked by "key fields". A "primary key" assigns a field to its "special order table". For example, the "Doctor Last Name" field might be assigned as a primary key of the Doctor table with all people having same last name organized alphabetically according to the first three letters of their first name. A table can also have a foreign key which indicates that field is linked to the primary key of another table. [2]

Types of models

A complex data model can involve hundreds of related tables. Computer scientist Edgar F. Codd created a systematic method to decompose and organize relational databases. [3] Codd's steps for organizing database tables and their keys is called database normalization, which avoids certain hidden database design errors (delete anomalies or update anomalies). In real life the process of database normalization ends up breaking tables into a larger number of smaller tables. [3]

Two related entities shown using Crow's Foot notation. In this example, the three lines next to the song entity indicate that an artist can have many songs. The two vertical lines next to the artist entity indicate songs can only have one performer. ERD-artist-performs-song.svg
Two related entities shown using Crow's Foot notation. In this example, the three lines next to the song entity indicate that an artist can have many songs. The two vertical lines next to the artist entity indicate songs can only have one performer.

In the real world, data modeling is critical because as the data grows voluminous, tables linked by keys must be used to speed up programmed retrieval of data. If a data model is poorly crafted, even a computer applications system with just a million records will give the end-users unacceptable response time delays. For this reason, data modeling is a keystone in the skills needed by a modern software developer.[ citation needed ]

Database modeling techniques

The entity–relationship model proposes a technique that produces entity–relationship diagrams (ERDs), which can be employed to capture information about data model entity types, relationships and cardinality. A Crow's foot shows a one-to-many relationship. Alternatively a single line represents a one-to-one relationship. [4]

Application program modeling approaches

In the object-oriented application programming paradigm, which is related to database structure design, UML class diagrams may be used for object modeling. In that case, object relationships are modeled using UML associations, and multiplicity is used on those associations to denote cardinality. Here are some examples: [5]

RelationshipExampleLeftRightNarrative
One-to-oneperson ←→ birth certificate11A person must have their own birth certificate, it is specific to that person by its Id number.
One-to-one (optional on one side)person ←→ driving license10..1 or ?A person may have a driving license, it is specific to that person by its Id number.
One-to-manyorder ←→ line item11..* or +An order contains at least one item
Many-to-oneperson ←→ birthplace1..* or +1Many people can be born in the same place, but 1 person can only be born in 1 birthplace
Many-to-manycourse ←→ student1..* or +1..* or +Students follow various courses
Many-to-many (optional on both sides)person ←→ book0..* or *0..* or *A person may own many books(copies), and a book may be owned by many people(readers).

See also

Related Research Articles

<span class="mw-page-title-main">Database</span> Organized collection of data in computing

In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS), the software that interacts with end users, applications, and the database itself to capture and analyze the data. The DBMS additionally encompasses the core facilities provided to administer the database. The sum total of the database, the DBMS and the associated applications can be referred to as a database system. Often the term "database" is also used loosely to refer to any of the DBMS, the database system or an application associated with the database.

Database normalization is the process of structuring a relational database accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by British computer scientist Edgar F. Codd as part of his relational model.

A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A database management system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems are equipped with the option of using SQL for querying and updating the database.

The relational model (RM) is an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by English computer scientist Edgar F. Codd, where all data is represented in terms of tuples, grouped into relations. A database organized in terms of the relational model is a relational database.

<span class="mw-page-title-main">Data model</span> Abstract model

A data model is an abstract model that organizes elements of data and standardizes how they relate to one another and to the properties of real-world entities. For instance, a data model may specify that the data element representing a car be composed of a number of other elements which, in turn, represent the color and size of the car and define its owner.

First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if no attribute domain has relations as elements. Or more informally, that no table column can have tables as values. Database normalization is the process of representing a database in terms of relations in standard normal forms, where first normal is a minimal requirement. SQL-92 does not support creating or using table-valued columns, which means that using only the "traditional relational database features" most relational databases will be in first normal form by necessity. Database systems which do not require first normal form are often called NoSQL systems. Newer SQL standards like SQL:1999 have started to allow so called non-atomic types, which include composite types. Even newer versions like SQL:2016 allow JSON.

Third normal form (3NF) is a database schema design approach for relational databases which uses normalizing principles to reduce the duplication of data, avoid data anomalies, ensure referential integrity, and simplify data management. It was defined in 1971 by Edgar F. Codd, an English computer scientist who invented the relational model for database management.

A hierarchical database model is a data model in which the data are organized into a tree-like structure. The data are stored as records which are connected to one another through links. A record is a collection of fields, with each field containing only one value. The type of a record defines which fields the record contains.

<span class="mw-page-title-main">Entity–relationship model</span> Model or diagram describing interrelated things

An entity–relationship model describes interrelated things of interest in a specific domain of knowledge. A basic ER model is composed of entity types and specifies relationships that can exist between entities.

Database design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model. A database management system manages the data accordingly.

<span class="mw-page-title-main">Domain model</span> A model in software engineering

In software engineering, a domain model is a conceptual model of the domain that incorporates both behavior and data. In ontology engineering, a domain model is a formal representation of a knowledge domain with concepts, roles, datatypes, individuals, and rules, typically grounded in a description logic.

<span class="mw-page-title-main">IDEF1X</span>

Integration DEFinition for information modeling (IDEF1X) is a data modeling language for the development of semantic data models. IDEF1X is used to produce a graphical information model which represents the structure and semantics of information within an environment or system.

An entity–attribute–value model (EAV) is a data model optimized for the space-efficient storage of sparse—or ad-hoc—property or data values, intended for situations where runtime usage patterns are arbitrary, subject to user variation, or otherwise unforeseeable using a fixed design. The use-case targets applications which offer a large or rich system of defined property types, which are in turn appropriate to a wide set of entities, but where typically only a small, specific selection of these are instantiated for a given entity. Therefore, this type of data model relates to the mathematical notion of a sparse matrix. EAV is also known as object–attribute–value model, vertical database model, and open schema.

Relational Model/Tasmania (RM/T) was published by Edgar F. Codd in 1979 and is the name given to a number of extensions to his original relational model (RM) published in 1970. The overall goal of the RM/T was to define some fundamental semantic units, at "atomic" and "molecular" levels, for data modelling. Codd writes: "the result is a model with a richer variety of objects than the original relational model, additional insert-update-delete rules and some additional operators that make the algebra more powerful."

<span class="mw-page-title-main">Associative entity</span> Term in relational and entity–relationship theory

An associative entity is a term used in relational and entity–relationship theory. A relational database requires the implementation of a base relation to resolve many-to-many relationships. A base relation representing this kind of entity is called, informally, an associative table.

<span class="mw-page-title-main">Database model</span> Type of data model

A database model is a type of data model that determines the logical structure of a database. It fundamentally determines in which manner data can be stored, organized and manipulated. The most popular example of a database model is the relational model, which uses a table-based format.

Entity Framework (EF) is an open source object–relational mapping (ORM) framework for ADO.NET. It was originally shipped as an integral part of .NET Framework, however starting with Entity Framework version 6.0 it has been delivered separately from the .NET Framework.

ModelRight is a database design and data modeling tool developed by ModelRight Inc. It is used by data modelers, database developers and database architects to create, visualize, and document their databases as an Entity Relationship Diagram (ERD).

<span class="mw-page-title-main">ERIL</span> Visual language for representing the data structure of a computer system

ERIL is a visual language for representing the data structure of a computer system. As its name suggests, ERIL is based on entity-relationship diagrams and class diagrams. ERIL combines the relational and object-oriented approaches to data modeling.

In systems analysis, a one-to-many relationship is a type of cardinality that refers to the relationship between two entities. For example, take a car and an owner of the car. The car can only be owned by one owner at a time or not owned at all, and an owner could own zero, one, or multiple cars. One owner could have many cars, one-to-many.

References

  1. ↑ Clarke, Alex; Hasnani, Aleen; Al-Ahasan, Abdullah; Islam, Nazmul (7 September 2022). "Data Modeling and Entity Relationship Diagram (ERD)". University of Regina - Computer Science Department.
  2. ↑ "Entity Relationship Mapping". Oracle Corporation. Retrieved 1 August 2002.
  3. 1 2 Codd, E. F. (1990). The relational model for database management : version 2. Reading, Mass.: Addison-Wesley. ISBN   0-201-14192-2. OCLC   19590880.
  4. ↑ "Crow's Foot Notation". University of Regina.
  5. ↑ "Cardinality". datacadamia. 7 September 2022.
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.