Introduction
A college database stores students, courses, instructors, and departments. These entities become useful when the database also records their associations: students enroll in courses, instructors teach courses, and departments employ instructors.
Relationships and structural constraints describe these associations and the rules they must follow. A relationship identifies how entities are connected. Structural constraints specify how many connections are allowed and whether participation is compulsory.
Relationships
A relationship represents a meaningful association among entities. In the entity relationship model, ENROLLS_IN can connect STUDENT and COURSE.
A relationship type defines the association in the database design. A relationship instance records one occurrence, such as student S101 enrolling in course C201. The collection of these occurrences at a particular time forms the relationship set.
Relationship Attributes
Some information describes an association rather than either participating entity independently.
For example, Grade belongs to a student’s enrollment in a particular course. Storing it only with STUDENT would not identify the relevant course, while storing it only with COURSE would not identify the relevant student. Relationship types can therefore have their own attributes
Degree and Roles
The degree describes how many entity types participate in a relationship. Common introductory classifications include:
Relationship Types
Unary or recursive relationship: An entity type relates to itself. For example, an instructor mentors another instructor. Role names such as mentor and mentee distinguish the two forms of participation.
Binary relationship: Two entity types participate. For example, STUDENT enrolls in COURSE.
Ternary relationship: Three entity types participate in one association. For example, an instructor teaches a course at a particular campus.
A ternary relationship preserves a fact about all three participants together. Replacing it with separate binary relationships may lose that meaning.
Cardinality and Participation Constraints
A relationship identifies how entities are connected, but its name does not specify which connections are allowed. For example, ENROLLS_IN connects students and courses without telling us how many courses a student can take or whether enrollment is compulsory.
Two structural constraints define these rules: cardinality ratio specifies the maximum number of associations, while participation determines whether an entity must have an association.
Cardinality Ratios
For a binary relationship, the cardinality ratio describes how many entities on one side can be associated with an entity on the other side.
Cardinality Ratios
One to one (1:1): An instructor can have at most one assigned office, and each office can be assigned to at most one instructor.
One to many (1:N): A department can employ many instructors, while each instructor belongs to at most one department.
Many to one (N:1): This is the reverse view of a one-to-many relationship. From instructor to department, many instructors can belong to the same department.
Many to many (M:N): A student can enroll in several courses, and each course can have several students.
Cardinality specifies the maximum allowed, not the minimum required. A 1:1 office assignment can still be optional; whether an instructor must have an office is determined by the participation constraint.
Participation Constraints
Total participation requires every entity in an entity set to participate in at least one relationship instance. If every instructor must belong to a department, INSTRUCTOR has total participation in BELONGS_TO.
Partial participation allows an entity to exist without participating. If a newly created department may have no instructors, DEPARTMENT has partial participation in that relationship.
Participation is determined independently for each side. The same relationship can therefore require every instructor to have a department while allowing departments without instructors.
Total and Partial Participation
Minimum and Maximum Notation
Minimum and maximum notation expresses both restrictions as a pair associated with an entity’s participation.
Pair | Interpretation |
|---|---|
(0,1) | Participation is optional, with at most one association |
(1,1) | Exactly one association is required |
(0,N) | Zero or many associations are permitted |
(1,N) | At least one association is required, and many are permitted |
The minimum establishes whether participation is optional; the maximum establishes the permitted upper bound. These pairs combine cardinality and participation in one notation.
For BELONGS_TO, an instructor with (1,1) must belong to exactly one department. A department with (0,N) may have no instructors or many instructors.
Common Design Mistakes
Confusing cardinality with participation: A 1:N relationship does not establish whether either side is mandatory. Specify minimum participation separately.
Inferring rules from sample data: A course with one enrolled student today can still support many students. Constraints describe permitted states, including future ones.
Assuming foreign keys enforce every requirement: A foreign key from INSTRUCTOR to DEPARTMENT does not require every department to have an instructor. A rule imposing that minimum needs additional enforcement.
Summary
Relationships describe meaningful associations among entities. Structural constraints define the permitted shape of those associations through cardinality and participation. Cardinality establishes maximum connections, while participation determines whether connections are required. Recording both rules clearly helps produce an accurate ER model and guides the choice of tables, keys, and enforcement mechanisms.
Be the first to add a comment.