Relationship cardinality and participation database

relationship cardinality and participation database

The number of different entity sets participating in a relationship set is called as entity of an entity set participates in a relationship set is known as cardinality. 2. ❑ Concepts/Constructs in ER Approach and diagram o Cardinality vs. Participation Constraint o Weak Entity Type, EX/ID Relationship Types, generalization. Chapter 2: Database Design and Entity-Relationship Model. Database Design of database constraints: Mapping cardinalities; Participation constraints; Keys.

relationship cardinality and participation database

Consider a marriage - a relationship with two mandatory roles filled by the same entity set. In most relationships, the entity sets also define the roles, but when an entity set appears multiple times in a single relationship, we distinguish them in different roles.

In the example above, a Patient can Purchase Medicine with or without a Prescription.

relationship cardinality and participation database

A Purchase can't exist without a Patient and Medicine, but a Prescription is optional overall, though it may be required in specific cases. A weak entity is an entity that can't be identified by its own attributes and therefore has another entity's key as part of its own. An identifying relationship is the relationship between a weak entity and its parent entity. Both the identifying relationship and the weak entity are indicated with double borders.

relationship cardinality and participation database

Weak entity sets must necessarily participate totally in their identifying relationship. In this example, a Prescription contains LineItems which are identified by the Prescription's key and a line number. For examples of non-identifying relationships, see the previous examples. While Medicine participates totally in the Produce relationship, it has its own identity e.

Entity-Relationship Model

Note that surrogate keys always imply regular entities. Total or partial participation indicate whether a certain relationship is required for an entity to exist.

relationship cardinality and participation database

A Laboratory can exist without producing any medicine, but Medicine can't be Produced without a Laboratory. Medicine can't exist without being Produced, and a Laboratory can't Produce something unspecified. A Prescription can exist without being Purchased, and a Purchase can exist without a Prescription.

ER Diagram Representation

That leaves optional total participation, which I had to think about a bit to find an example: Partial participation in an identifying relationship isn't possible. Total participation doesn't imply an identifying relationship - Medicine can't exist without being Produced by a Laboratory but Medicine is identified by its own attributes.

Partial participation in a non-identifying relationship is very common. For example, Medicine can exist without being Purchased, and Medicine is identified by its own attributes. Identifying relationships are binary relationships, so the parent and child roles will be mandatory - the Contain relationship between Prescription and LineItem can't exist without both entities.

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.

ER Diagram Representation

A complex data model can involve hundreds of related tables. A renowned computer scientist, Edgar F. Coddcreated a systematic method to decompose and organize relational databases.

Codd's steps for organizing database tables and their keys is called database normalizationwhich 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. 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.