Difficulty: Intermediate | Prerequisites: Entity Types and Attributes in EER Modelling study notes (Part 1 of this set).
Once you have identified entity types and their attributes, the next step is modelling how those entities relate to each other, handling entities that depend on others (weak entities), and capturing inheritance hierarchies (specialization/generalization). These concepts complete the EER diagram and are heavily tested. If you are not yet comfortable with entity types, key attributes, and attribute characteristics, review those first.
Relationship types describe how entity types connect to each other, with constraints (degree, cardinality, participation) that define the rules of those connections. Weak entities and specialization/generalization (IS-A hierarchies) are extensions that handle dependent entities and inheritance. Together, these concepts let you build a complete EER diagram from a case study.
Relationship type
A meaningful association among two or more entity types. In simple terms, it is the connection between things in your domain, such as "a CUSTOMER places an ORDER."
Degree (of a relationship)
The number of entity types that participate in a relationship. A binary relationship involves two entity types (degree 2). A ternary relationship involves three (degree 3). Most relationships you will encounter in this course are binary.
Participation entity type
An entity type that takes part in a relationship type. Think of it as listing which "things" are involved in the connection.
Cardinality (min, max)
The minimum and maximum number of relationship instances an entity can participate in. The min value captures the participation constraint (0 = partial, 1 or more = total). The max value captures the structural constraint (1 = at most one, N = many). For example, (1, N) means every instance must participate at least once and can participate many times.
Total participation
Every instance of the entity type must participate in at least one instance of the relationship. In simple terms, the thing cannot exist in the database without being connected. Shown as min = 1 (or more) in (min, max) notation.
Partial participation
Some instances of the entity type may not participate in the relationship at all. In simple terms, the connection is optional. Shown as min = 0.
Identifying relationship type
The relationship that connects a weak entity type to its owner entity type. In simple terms, it is the link that gives the weak entity its full identity by combining the owner's key with the weak entity's partial key.
Partial key
An attribute of a weak entity type that distinguishes instances of that weak entity within the scope of a single owner entity. Think of it as a "local" identifier that only works when you already know which owner you are talking about.
Superclass (super entity type)
An entity type that is generalised from one or more subclasses. It holds the common attributes shared by all its subclasses. In simple terms, it is the parent in an inheritance hierarchy.
Subclass (sub entity type)
An entity type that inherits all attributes of its superclass and may have additional local attributes or relationships of its own. Think of it as a specialised version of the parent.
IS-A relationship type
The relationship between a subclass and its superclass, indicating that every instance of the subclass is also an instance of the superclass. "A MANAGER IS-A EMPLOYEE."
Disjointness constraint (disjoint / overlap)
Specifies whether an entity can be a member of more than one subclass at the same time. Disjoint (or "distinct") means no overlap: an entity belongs to at most one subclass. Overlap means an entity can belong to multiple subclasses simultaneously.
Completeness constraint (total / partial)
Specifies whether every instance of the superclass must belong to at least one subclass. Total means yes, every superclass instance is in some subclass. Partial means some superclass instances may not belong to any subclass.
Local attribute
An attribute that belongs only to a specific subclass, not to the superclass or other subclasses. In simple terms, it is a property that only the specialised version has.
Local relationship type
A relationship type that involves only a specific subclass, not the superclass as a whole. Think of it as a connection that only the specialised entity participates in.
A relationship type connects two or more entity types. The homework (question 3) asks you to list every relationship type in the case study.
Degree: most relationships in a typical case study are binary (degree 2). Ternary (degree 3) relationships are less common but do appear. A unary (degree 1, or recursive) relationship connects an entity type to itself (e.g., an EMPLOYEE supervises another EMPLOYEE).
Cardinality notation (min, max): each participating entity type gets its own (min, max) pair.
min = 0: partial participation (optional). min = 1 (or more): total participation (mandatory).
max = 1: each instance can participate in at most one relationship instance. max = N: each instance can participate in many.
Common patterns: (0, 1) optional one, (1, 1) mandatory one, (0, N) optional many, (1, N) mandatory many.
How to read a relationship: "Each CUSTOMER places (0, N) ORDERs" means a customer may place zero or many orders. "Each ORDER is placed by (1, 1) CUSTOMER" means every order belongs to exactly one customer.
When filling in the homework table, list every relationship with: its name, degree, participating entity types, and the (min, max) for each side.
A weak entity type is identified by question (4) in the homework. You need to find it, name its owner, and specify the identifying relationship.
The identifying relationship is always a binary relationship between the weak entity and its owner.
The weak entity's participation is always total on the identifying relationship side (it cannot exist without the owner).
The weak entity's partial key distinguishes instances within one owner. The full key is: owner's key + partial key.
Example: ORDER_ITEM is weak, owned by ORDER. Partial key = ItemNumber. Full key = (OrderID, ItemNumber).
If the case study has no weak entity types, simply state that none were found. Do not invent them.
Question (5) in the homework asks about IS-A relationships, also called specialization/generalization.
Specialization (top-down): you start with a superclass and define subclasses for specialised roles. Example: EMPLOYEE has subclasses MANAGER and DRIVER.
Generalization (bottom-up): you notice that several entity types share common attributes and create a superclass to hold those shared attributes.
Each subclass inherits all attributes and relationships of the superclass, plus may have its own local attributes and local relationships.
Constraints on IS-A hierarchies:
Disjointness:
Disjoint (distinct): an instance of the superclass can belong to at most one subclass. A person is either a MANAGER or a DRIVER, not both.
Overlap: an instance can belong to multiple subclasses simultaneously.
Completeness:
Total: every instance of the superclass must be a member of at least one subclass. Every EMPLOYEE must be either a MANAGER or a DRIVER (or some other subclass).
Partial: some superclass instances may not belong to any subclass. Some EMPLOYEEs might be neither MANAGER nor DRIVER.
Local attributes and local relationships:
The homework asks you to list any attributes that belong only to a subclass (not inherited from the superclass). For example, a DRIVER subclass might have a LicenceType attribute that EMPLOYEE does not.
Similarly, a subclass may participate in relationships that do not involve the superclass. For example, DRIVER might have a DELIVERS relationship with ORDER, while MANAGER does not.
Students often swap the (min, max) values between the two sides of a relationship. Read the constraint from the perspective of each entity type separately: "each instance of THIS entity participates in (min, max) instances of the relationship."
Students sometimes assume all relationships are binary. Ternary and higher-degree relationships do exist. If three entity types are all needed simultaneously to describe a single association, it is ternary, not three separate binary relationships.
Students confuse total participation with a max of N. Total participation means min >= 1 (every instance must participate). It says nothing about how many times it participates. A (1, 1) constraint is total participation with a max of one.
Students sometimes think the disjointness and completeness constraints are the same thing. Disjointness is about whether subclasses overlap (can an instance be in two subclasses?). Completeness is about whether the superclass is fully covered (must every instance be in some subclass?).
Cardinality (min, max) notation is tested in nearly every EER modelling question. Be fluent in reading and writing it from both sides of a relationship.
Weak entities are a perennial exam favourite. Know how to identify the owner, the identifying relationship, and the partial key. Expect a question that asks you to construct the full key of a weak entity.
Specialization/generalization questions often ask you to state the disjointness and completeness constraints and justify your choice. A common exam format is: "Given this scenario, is the specialization disjoint or overlapping? Total or partial? Explain."
Expect to be asked to list local attributes and local relationships of subclasses. If a subclass has no local attributes or relationships, you should state that explicitly.
True or False: A (0, N) cardinality means total participation on that side.
False. min = 0 means partial participation (optional). Total participation requires min >= 1.
Fill in the blank: The relationship that connects a weak entity type to its owner is called the ______ relationship type.
Identifying.
True or False: In a disjoint specialization, an instance of the superclass can belong to more than one subclass.
False. Disjoint means at most one subclass per instance. Overlap would allow multiple.
True or False: A ternary relationship can always be replaced by three binary relationships without losing information.
False. Ternary relationships capture a three-way association that may not be decomposable into binary pairs without losing meaning.
Fill in the blank: If every instance of the superclass must belong to at least one subclass, the completeness constraint is ______.
Total.
Q: A CUSTOMER places ORDERs. Every order must belong to exactly one customer, but a customer may have placed no orders yet. What are the (min, max) cardinalities for each side?
A: CUSTOMER participates with (0, N): a customer may place zero or many orders. ORDER participates with (1, 1): every order belongs to exactly one customer.
Q: Explain what it means for ORDER_ITEM to be a weak entity type owned by ORDER. What is the identifying relationship, and how is the full key formed?
A: ORDER_ITEM cannot be uniquely identified on its own because its partial key (e.g., ItemNumber) only distinguishes items within a single order. The identifying relationship (e.g., "Contains" or "Has") links ORDER_ITEM to ORDER. The full key of ORDER_ITEM is the combination of ORDER's key (OrderID) and the partial key (ItemNumber).
Q: In a pizza delivery system, EMPLOYEE has subclasses DRIVER and COOK. A given employee can be both a driver and a cook. Is this disjoint or overlapping? If every employee must be at least one of these, is the completeness total or partial?
A: Overlapping, because an employee can belong to both subclasses. If every employee must be a driver, a cook, or both, then the completeness is total. If some employees are neither (e.g., a manager who does not drive or cook), then it is partial.
Q: What is a local attribute? Give an example.
A: A local attribute belongs only to a subclass, not to the superclass. For example, if DRIVER is a subclass of EMPLOYEE, then LicenceType might be a local attribute of DRIVER, since not all employees have a driving licence relevant to the business.
Q: A student writes that the degree of the relationship "CUSTOMER places ORDER" is 1 because there is one relationship. What is wrong?
A: Degree refers to the number of participating entity types, not the number of relationships. Since CUSTOMER and ORDER are two entity types, the degree is 2 (binary).
This material connects directly to EER-to-relational mapping, which you will cover next. Every entity type, relationship, weak entity, and specialization you identify here will become one or more tables in the relational schema. Getting the cardinality and participation constraints right determines whether you use foreign keys, junction tables, or merged tables.
Specialization/generalization connects to object-oriented design and class inheritance. If you have a background in programming, think of superclass/subclass in EER as analogous to parent/child classes, with local attributes as the fields added in the child class.
Weak entity types reappear when you study functional dependencies and normalisation. The partial key concept maps onto how composite primary keys are formed in normalised tables.
Relationship type, binary relationship, ternary relationship, unary relationship, recursive relationship, degree, cardinality, min-max notation, participation constraint, total participation, partial participation, identifying relationship, weak entity, owner entity, partial key, specialization, generalization, IS-A hierarchy, superclass, subclass, disjoint constraint, overlap constraint, total completeness, partial completeness, local attribute, local relationship, EER diagram, enhanced entity-relationship model, ACS575, database systems, Purdue, pizza delivery case study, conceptual modelling.