Database Relationships and Cardinality

Podcast Beta

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What type of relationship occurs when an entity has a relationship to itself?

Trinary relationship

Recursive relationship (correct)

Associative relationship

Binary relationship

Which cardinality describes a scenario where a single EMPLOYEE can be associated with many COMPUTER instances?

One-to-one (1:1)

One-to-many (1:N) (correct)

Mandatory-to-mandatory (M-M)

Many-to-many (N:M)

What is a necessary step when dealing with many-to-many (N:M) relationships in data modeling?

Create an associative entity (correct)

Introduce a binary relationship

Ignore the relationship

Implement a unary relationship

What does maximum cardinality represent in an entity-relationship model?

Maximum number of instances that can participate in a relationship Signup and view all the answers

In a scenario where both an EMPLOYEE must have a BADGE and a BADGE is allocated to an EMPLOYEE, this relationship is categorized as:

Mandatory-to-mandatory (M-M) Signup and view all the answers

What term describes a relationship that occurs between instances of the same entity type?

Unary relationship Signup and view all the answers

Which of the following is NOT a type of cardinality in an E-R model?

Many-to-many (M:M) Signup and view all the answers

When transforming a data model into a relational database, how are relationships of all degrees treated?

As a combination of binary relationships Signup and view all the answers

What type of relationship does the Computer_Assignment represent?

Optional to optional (O-O) Signup and view all the answers

Which statement correctly describes the Qualification relationship?

An EMPLOYEE may not have any SKILL assigned. Signup and view all the answers

What is a primary key in the context of database entities?

A key that uniquely identifies a single entity instance. Signup and view all the answers

Which of the following describes a surrogate key?

A unique value that is assigned when a row is created. Signup and view all the answers

What happens when a row in the parent table is deleted and the ON DELETE action is set to CASCADE?

The child table rows corresponding to the deleted row are also deleted. Signup and view all the answers

What is the purpose of a foreign key in a database?

To link records in one table to records in another table. Signup and view all the answers

What is the function of the SET NULL action with foreign key constraints?

To set the foreign key columns in the child table to NULL. Signup and view all the answers

What distinguishes an alternate key from a primary key?

An alternate key is not selected as the primary key. Signup and view all the answers

What distinguishes a weak entity from a strong entity?

Weak entities must rely on another entity for their existence. Signup and view all the answers

What is an ID-dependent entity?

An entity whose identifier includes the identifier of another entity. Signup and view all the answers

Which statement accurately describes the relationship between STUDENT and ADVISER?

STUDENT is a strong entity as it can logically exist without an ADVISER. Signup and view all the answers

Which of the following represents a non-ID-dependent weak entity?

VEHICLE, as it has its own identity but is related to an AUTO_MODEL. Signup and view all the answers

In data modeling, how is specialization used?

To represent unique traits among subgroups of an entity. Signup and view all the answers

What is the main requirement for a weak entity to exist?

It must depend on another entity for its identification. Signup and view all the answers

Which of the following is an example of an ID-dependent entity?

VEHICLE, as it includes AUTO_MODEL in its identification. Signup and view all the answers

How are non-ID-dependent weak entities represented in data modeling?

Using nonidentifying relationships with separate documentation. Signup and view all the answers

Study Notes

Recursive Relationships

A recursive relationship is a relationship between different instances of the same entity.
It is also known as a unary relationship.
Recursive relationships can be one-to-one (1:1), one-to-many (1:N), or many-to-many (N:M).

Ternary Relationships

Ternary relationships involve three entity classes.
They are represented as combinations of binary relationships in relational database designs.

Cardinality

Cardinality defines the number of entity instances that can participate in a relationship.

Maximum Cardinality

One-to-one (1:1) relationship: At most one entity instance of each type is related.
One-to-many (1:N) relationship: One entity instance can be associated with many instances of another type, but the reverse is not true.
Many-to-many (N:M) relationship: Multiple instances of each entity type can be associated with each other.

Minimum Cardinality

Minimum Cardinality specifies the minimum number of entity instances that must participate in a relationship instance.

Relationship Types Based on Minimum Cardinality

Mandatory-to-mandatory (M-M) relationship: Entities are required on both sides of the relationship.
Optional-to-optional (O-O) relationship: Neither entity is required to participate in the relationship.
Optional-to-mandatory (O-M) relationship: An entity must be related to at least one instance of the other entity, but not vice versa.

Data Modeling Concepts: Identification

Candidate key: A key that can serve as the primary key of an entity.
Primary key: The candidate key selected to uniquely identify an entity instance.
Alternate key: A candidate key that is not chosen as the primary key.
Surrogate key: A DBMS-supplied identifier for each row of a table.
Foreign key: A primary key of an entity used in another entity to identify related instances.

Action with Foreign Key Constraint

ON UPDATE and ON DELETE actions specify how child table rows are affected when a parent table row is updated or deleted.
Actions include:
- NO ACTION: The operation on the parent table is rolled back.
- CASCADE: The child rows are updated or deleted in the same way as the parent row.
- SET NULL: Child rows are set to NULL.
- SET DEFAULT: Child rows are set to their default values.

Strong Entities and Weak Entities

A strong entity can exist independently.
A weak entity depends on the presence of another entity for its existence.
ID-dependent entities: Their identifier includes the identifier of another entity.
Non-ID-dependent weak entities: They have their own identifier but still depend on another entity.

The Ambiguity of the Weak Entity

A business rule may require a relationship, but logically, an entity can exist without it.
Example: A STUDENT may need an ADVISER, but it can still exist without one.

Specialization and Generalization

Specialization: Subgrouping entities of an entity type based on distinguishing characteristics.
Generalization: Combining entities of different subtypes into a single entity type.

Crow's Foot Model

A graphical representation of entity-relationship diagrams (ERDs) using symbols to represent relationships.
It uses lines and symbols to depict the relationship's cardinality and participation constraints.
The symbol is used to represent a relationship's type, such as 1:1, 1:N, or N:M.
Participation constraints indicate whether an entity is required to participate in a relationship.
Crow's foot notation provides a visual representation of data model relationships, helping to understand how entities connect and how data is structured.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge on recursive and ternary relationships in databases, along with concepts of cardinality. This quiz covers key definitions and examples to enhance understanding of how entities interact in relational database designs.