DATABASE

SYSTEMS
INSTRUCTOR: AYSHA
SAFDAR
Constraints in ERD and
EERD
1. Cardinality Constraints
Cardinality constraints define the number of instances of one entity that can be associated with instances of
another entity in a relationship. They are used to specify the minimum and maximum number of instances that
can be involved in a relationship.

One-to-One (1:1): One instance of Entity A is associated with at most one instance of Entity B, and vice versa.
◦ Example: A Person can have only one Passport, and a Passport can be associated with only one Person.

One-to-Many (1:N): One instance of Entity A can be associated with many instances of Entity B, but an
instance of Entity B can be associated with only one instance of Entity A.
◦ Example: A Department can have many Employees, but an Employee works in only one Department.

Many-to-One (N:1): Many instances of Entity A can be associated with one instance of Entity B, but each
instance of Entity A is associated with only one instance of Entity B.
◦ Example: Many Books can belong to one Author, but an Author can write many Books.

Many-to-Many (M:N): Many instances of Entity A can be associated with many instances of Entity B.
◦ Example: A Student can enroll in many Courses, and a Course can have many Students.
2. Participation Constraint
A participation constraint in an Enhanced Entity-Relationship Diagram (EERD) specifies
whether all instances of an entity participate in a relationship or not. There are two types of
participation constraints:

1. Total Participation

Definition: Every instance of an entity must participate in at least one relationship instance.

Notation: Represented with a double line connecting the entity to the relationship in the
diagram.

Example:
◦ In an employee-department relationship, if every employee must work in at least one department, the
participation of the Employee entity in the Works_In relationship is total.
◦ EERD Example:
◦ Entity: Employee
◦ Relationship: Works_In
◦ Constraint: Every Employee must work in at least one department.
2. Participation Constraint
2. Partial Participation
Definition: Some instances of an entity may not participate in the relationship.
Notation: Represented with a single line connecting the entity to the
relationship in the diagram.
Example:
◦ In an employee-project relationship, not every employee might be assigned to a
project. Therefore, the participation of the Employee entity in the Assigned_To
relationship is partial.
◦ EERD Example:
◦ Entity: Employee
◦ Relationship: Assigned_To
◦ Constraint: Some employees may not be assigned to any project.
3. Key Constraints
Key constraints define the uniqueness of an entity’s identifier or a
combination of attributes that uniquely identify an entity instance.

Primary Key (PK): A set of one or more attributes that uniquely identify an
entity instance.
◦ Example: The Student_ID attribute is the primary key of the Student entity.

Foreign Key (FK): A set of attributes in one entity that refers to the primary
key of another entity, establishing a relationship between the entities.
◦ Example: The Department_ID in the Employee entity refers to the primary key
Department_ID in the Department entity.
4. Disjoint and Overlapping
Constraints
(Specialization/Generalization)
These constraints are used in specialization and generalization hierarchies to specify
how instances of a superclass are divided into subclasses.

Disjoint Constraint: An entity instance in the superclass can only belong to one subclass
at a time. This constraint is used when the subclasses are mutually exclusive.
◦ Notation: Represented with a D inside a circle placed above the line connecting the superclass
and its subclasses.
◦ Example: A Person can either be a Teacher or a Student, but not both at the same time.

Overlapping Constraint: An entity instance in the superclass can belong to one or more
subclasses.
◦ Notation: Represented with an O inside a circle above the line connecting the superclass and its
subclasses.
◦ Example: An Employee can be both a Manager and a Salesperson.
5. ISA Relationship
(Specialization and
Generalization)
In ERDs, ISA (stands for Is-A) is used to indicate a relationship between a
superclass and its subclasses.

Specialization: The process of defining one or more subclasses from a

superclass based on some differentiating characteristics.
◦ Example: A Course can be specialized into Self_Paced_Course and
Instructor_Led_Course.

Generalization: The process of defining a superclass from two or more

subclasses.
◦ Example: Full-Time_Employee and Part-Time_Employee can be generalized to
the Employee superclass.
6. Referential Integrity Constraint
Definition: This constraint ensures that relationships between entities are
consistent, specifically that foreign key values in one entity must exist as
primary key values in another entity.
◦ Example: If an Employee has a Department_ID as a foreign key, then every
Department_ID in the Employee entity must correspond to a valid Department_ID
in the Department entity.
7. Domain Constraints
Definition: Specifies that attribute values must come from a certain domain
or range of valid values (e.g., data types, value ranges).
◦ Example: An Age attribute in the Student entity should have values between 18
and 100.
Enhanced Entity Relationship
Diagram (EERD/ EER)
EERD stands for Enhanced Entity-Relationship Diagram. It is an advanced
version of the Entity-Relationship Diagram (ERD) used in the field of
database design and systems analysis. The EERD (or sometimes just
EER) includes all the features of the ERD, but also adds support for more
complex types of entities and relationships, making it a more expressive
tool.
Subclasses and
Superclasses
Superclass: An entity type that includes one or more distinct subgroupings
of its occurrences, which must be represented in a data model.

Subclass: A distinct subgrouping of occurrences of an entity type, which

must be represented in a data model.
Example of EERD
Suppose we have Staff entity as Superclass and Manager, SalesPersonnel
and Secretary as Subclasses. The relationship between Superclass and
Subclass is called a superclass/subclass relationship. E.g staff/manager
has superclass/subclass relationship.
Why Need EERD?
Sometimes we need EERD to introduce concept of inheritance in ERD,
where one entity inherits some attributes from other entity, with ERD we
cannot represent it, so we need EERD to do that.

It avoids describing similar concepts more than once.

Key enhancements in EERD
over ERD
Subclasses and Superclasses (Inheritance): This allows entities to
inherit attributes and relationships from a parent entity. For example, a
"Vehicle" entity could have subclasses like "Car" and "Truck", inheriting
common attributes from "Vehicle". Parent Class/Super
Vehicle Class

Car Truck

Child
Classes
Key enhancements in EERD
over ERD (…)
Specialization and Generalization:

Specialization Vehicle
Specialization is the process of breaking down
a general entity into more specific entities
based on certain distinguishing features or
attributes.

Purpose: Focus on creating sub-groups of

Car Truck Bike
entities to represent more detailed and specific
characteristics.

Characteristics:
◦ Starts with a general superclass entity and Top-
defines subclasses.
Down
◦ Subclasses inherit attributes and relationships of
the superclass. Approach
◦ Subclasses may add their own unique attributes
or participate in specialized relationships.
Generalization
Generalization is the process of
abstracting common features from two
or more specific entities to form a more Pare
general entity. nt
Class
Purpose: Combine similar entities into
a single general entity to reduce
redundancy.
Characteristics:
◦ Starts with specific subclasses and
merges them into a general superclass.
Child Child Child
class class class
◦ The superclass captures shared
attributes and relationships. 1 2 3 Bottom-up
Differences Between
Specialization and Generalization

Aspect Specialization Generalization

Breaking down a general Combining specific

Definition entity into specific entities into a general
entities. entity.
Direction Top-down approach. Bottom-up approach.
Adding details to Abstracting common
Focus
differentiate entities. features.
Book → Physical_Book,
Example Car, Bike → Vehicle.
eBook.
Specialization and
Generalization
BOOK
ISBN
{PK}
Title
Price

{ Optional,
Generalizat Specializa
OR}
ion Languages tion
Text Book Novel
BOOK
Language Subject Type
Diagrammatic(UML)
representation of
Specialization/Generalization
Explanation
Disjoint Constraint
Superclass (Staff)
◦ Specifies whether subclasses overlap:
◦ Attributes: staffNo (PK), name, position, salary.
◦ Disjoint: A Staff entity belongs to only one subclass (e.g., either
◦ This represents the general entity containing Manager or SalesPersonnel).
shared attributes across all subclasses.
Relationships
Specialization (ISA Hierarchy) ◦ Branch has a 1:N relationship with Staff (indicating
multiple staff members work at one branch).
◦ Subclasses derived from Staff:
◦ Manager has a 1:1 relationship with Branch
◦ Manager (with attributes mgrStartDate and bonus).
(indicating a manager manages one branch).
◦ SalesPersonnel (with attributes salesArea and carAllowance).
◦ Secretary (with attribute typingSpeed). Specialization vs Generalization in the Diagram
◦ Indicates that Staff is specialized into these sub-
entities based on roles. Generalization: The Manager, SalesPersonnel, and
Secretary subclasses share common attributes like
Participation Constraint staffNo, name, position, and salary, which are
◦ Specifies whether an entity in the superclass generalized into the Staff superclass.
(Staff) must also belong to a subclass:
◦ {Optional, And}: Indicates partial participation, where a Staff
Specialization: The Staff superclass is specialized
member may belong to one or more subclasses. into three distinct roles, each with unique attributes.
EERD: Case Study
Imagine an online bookstore where customers can purchase both physical
books and eBooks. In this system, you need to represent customers, books,
and orders. Customers have a unique ID, a name, and an email and have been
members since a certain date. Books are characterized by an ISBN, title,
author, genre, and price. There are two types of books: physical books, which
also have a shipping weight, and eBooks, which have a file format. Orders are
placed by customers and include a unique order ID, the date, and the total
amount. The relationship between customers and orders is one-to-many, as a
customer can place multiple orders, but each order is placed by a single
customer. Each order can contain several books. The EERD should clearly
depict these entities with their attributes, the relationships with proper
cardinalities, and the specialization of books into physical books and eBooks.
Specialization

EERD: Case The Book entity is specialized into:

◦ Physical_Book: Represents physical copies with an additional
attribute (Shipping_Weight).

Study ◦ eBook: Represents electronic versions with an additional attribute

(File_Format).

Constraints:
◦ Disjoint Constraint: A book can either be a physical book or an
eBook, not both.
Entities:
◦ Partial Participation: Not all Book entities must belong to a
Customer (Superclass): subclass. Some general information about books might be stored at
the superclass level.
◦ Attributes: Customer_ID (PK), Name, Email,
Member_Since.
Generalization
Book (Superclass):
it can be viewed as the reverse of specialization if focusing on
◦ Attributes: ISBN (PK), Title, Author, Genre, Price.
combining attributes of specific book types into the general Book entity.

Physical_Book (Subclass): Relationships and Cardinality

◦ Additional Attribute: Shipping_Weight.
Customer ↔ Order (1:N):
eBook (Subclass): ◦ Meaning: A customer can place multiple orders, but each order is
◦ Additional Attribute: File_Format. placed by exactly one customer.
◦ Cardinality:
Order: ◦ Customer: 1..1 (Each order belongs to exactly one customer).
◦ Attributes: Order_ID (PK), Order_Date,
◦ Order: 0..N (A customer can place multiple orders, or none).
Total_Amount.
◦ Physical_Book and eBook are derived from the Order ↔ Book (N:M):
superclass Book through specialization.
◦ Meaning: An order can contain multiple books, and a book can be
part of multiple orders.
◦ Cardinality:
◦ Order: 1..N (Each order contains one or more books).
◦ Book: 0..M (A book can appear in multiple orders, or none).
Example: Case Study
Scenario: Online Learning Platform

An online learning platform allows students to enroll in courses. The platform offers two types of courses: Self-
Paced Courses and Instructor-Led Courses. Each course has a unique course ID, a title, a description, a
price, and a duration. Self-paced courses include the number of videos and downloadable resources, while
instructor-led courses include the instructor's name, meeting schedules, and maximum number of participants.

Students can enroll in multiple courses, and each course can have multiple students enrolled. When students
enroll in a course, an enrollment record is created with a unique enrollment ID, the date of enrollment, and the
payment status.

Requirements:

Represent students, courses, and enrollment records as entities.

Differentiate between self-paced courses and instructor-led courses using specialization.

Clearly depict relationships with cardinalities and participation constraints.

Identify superclasses, subclasses, generalization, specialization, participation, and cardinality.

Specialization (ISA hierarchy):
◦ The Course entity is specialized into Self_Paced_Course and
Entity Identification Instructor_Led_Course:
◦ Disjoint Constraint: A course can either be a self-paced course or an instructor-led
course, not both.
Student (Superclass):
◦ Partial Participation: Some courses may not belong to any specific type.
◦ Attributes: Student_ID (PK), Name, Email, Date_Of_Joining.

Key Design Concepts

Course (Superclass):
◦ Attributes: Course_ID (PK), Title, Description, Price, Duration. Superclass:
◦ Course represents the shared attributes of both Self_Paced_Course and
Instructor_Led_Course.
Self_Paced_Course (Subclass):
◦ Additional Attributes: Num_Videos, Num_Resources. Subclasses:
◦ Self_Paced_Course and Instructor_Led_Course inherit from the
superclass Course.
Instructor_Led_Course (Subclass):
◦ Additional Attributes: Instructor_Name, Schedule, Max_Participants.
Specialization:
◦ Course is specialized into Self_Paced_Course and
Enrollment: Instructor_Led_Course to reflect distinct attributes.

◦ Attributes: Enrollment_ID (PK), Enrollment_Date, Payment_Status.

Generalization:
◦ If Self_Paced_Course and Instructor_Led_Course existed as separate
Relationships entities, we could generalize them into the superclass Course.

Student ↔ Enrollment (1:N): Participation Constraints:

◦ A student can enroll in multiple courses, but each enrollment belongs to ◦ Student ↔ Enrollment: Total participation of Enrollment (every record
must belong to a student).
one student.
◦ Course ↔ Enrollment: Partial participation of Course (not all courses
◦ Cardinality: must have enrollments).
◦ Student: 1..1 (Each enrollment is tied to one student).

◦ Enrollment: 0..N (A student can have multiple enrollments, or none). Make EERD??

Enrollment ↔ Course (N:1):

◦ Each enrollment corresponds to a specific course.
◦ Cardinality:
◦ Enrollment: 1..N (An enrollment must refer to one course).

◦ Course: 0..N (A course can have multiple enrollments or none).

Practice 1 EERD
Scenario: Online Music Streaming Platform

An Online Music Streaming Platform allows users (subscribers) to listen to and purchase different types of music content. The platform offers
two types of content: Albums and Singles. Each content piece has a unique content ID, a title, an artist, a release date, a price, and a genre.

Albums include additional attributes such as number of tracks, and duration, while Singles include attributes such as track length and
release year.

Users (subscribers) can subscribe to the platform and have the ability to listen to unlimited content during their subscription. A user can
purchase individual albums or singles. Purchase records are created when a user buys a specific content piece, containing a unique purchase
ID, the purchase date, and the payment status.

Requirements:

Represent users, content (albums and singles), and purchase records as entities.

Differentiate between albums and singles using specialization.

Clearly depict relationships with cardinalities and participation constraints.

Identify superclasses, subclasses, generalization, specialization, participation, and cardinality.

Practice 2 EERD
Scenario: Online Retail Platform

An Online Retail Platform allows customers to purchase various products. The platform offers two types of products: Physical Products and
Digital Products. Each product has a unique product ID, name, description, price, and stock quantity.

Physical Products include attributes like weight, dimensions, and shipping fees, while Digital Products include attributes such as file
format and download link.

Customers can place orders for multiple products, and each order can contain multiple products. Each order has a unique order ID, order
date, payment status, and total amount. When customers place an order, an order record is created, tracking the items purchased.

Requirements:

Represent customers, products, and order records as entities.

Differentiate between physical products and digital products using specialization.

Clearly depict relationships with cardinalities and participation constraints.

Identify superclasses, subclasses, generalization, specialization, participation, and cardinality.