Relational Model & ER Diagrams
The Relational Model and Entity-Relationship (E-R) Model represent two cornerstone paradigms in database management systems. The Relational Model, proposed by E.F. Codd in 1970, provides a formal mathematical basis for representing and manipulating data using relations (tables). The E-R Model, introduced by Peter Chen in 1976, offers a high-level conceptual data model for designing databases.
This guide covers entities, attributes, relationships, cardinality constraints, keys, enhanced E-R features (specialization, generalization, aggregation), E-R to relational schema conversion, SQL walkthroughs, and a 10-question practice quiz.
Building a Student-Course E-R Diagram
1Introduction
These models are critical for several reasons. They are the bedrock of database design, guiding software engineers and data architects in creating robust, efficient, and maintainable database systems. From e-commerce platforms to financial systems and scientific data repositories, the principles derived from these models dictate how data is structured, stored, and accessed.
They provide a formal language for reasoning about data structures and operations, enabling the development of query languages (like SQL), optimization techniques, and integrity constraints. The mathematical rigor of the Relational Model, rooted in set theory and first-order logic, ensures data consistency and predictable behavior.
In the database curriculum, this topic typically follows an introduction to database systems and precedes the study of SQL, database normalization, transaction management, and physical database design. The E-R Model serves as the primary tool for conceptual database design, while the Relational Model provides the framework for logical database design.
A common real-world application involves designing a new inventory management system. E-R diagrams are used by business analysts and database architects to model entities like Products, Suppliers, Orders, and Customers, along with their relationships and attributes. This conceptual design is then systematically converted into a relational schema, defining tables, columns, primary keys, and foreign keys, which can be implemented in PostgreSQL or MySQL using SQL DDL commands.
| StudentIDPK | Name | MajorAttribute (Column) | GPA |
|---|---|---|---|
| 1001 | Alice | CS | 3.8 |
| 1002 | Bob | MATH | 3.5 |
| 1003 | Carol | BIOL | 3.9 |
2Key Definitions
Essential terms for understanding the Relational and E-R Models at the university level.
Attribute
A named property describing a characteristic of an entity or relationship
Domain
The set of all permissible values for an attribute (e.g., INT, VARCHAR, DATE)
Entity
A real-world object or concept distinguishable from other objects
Entity Set
A collection of entities of the same type sharing the same attributes (rectangle)
Relationship Set
An association among two or more entity sets (diamond)
Relation (Table)
A set of tuples conforming to a schema; the core structure in the Relational Model
Tuple (Row)
A single record in a relation representing a specific entity or relationship
Schema (Intension)
The logical structure of a database: relation names, attributes, domains, constraints
Instance (Extension)
The actual data stored in the database at a particular moment in time
Superkey
A set of attributes that uniquely identifies a tuple in a relation
Candidate Key
A minimal superkey — no proper subset is also a superkey
Primary Key
The chosen candidate key to uniquely identify tuples; underlined in schema notation
Foreign Key
An attribute referencing the primary key of another relation; enforces referential integrity
Relational Algebra
A procedural query language operating on relations: select (σ), project (π), join (⋈)
Weak Entity Set
An entity set without its own primary key, dependent on a strong entity (double rectangle)
Discriminator
Partial key of a weak entity; combined with owner's PK to form full identification
3Entities and Attributes
The E-R Model identifies the primary entities in a system and describes their properties using attributes. An entity set is a collection of real-world objects that share common properties, represented by a rectangle in E-R diagrams.
Types of Attributes
Simple Attribute
Cannot be divided into smaller sub-parts. Represented by a single oval.
Example: Age, Gender, CourseID
Composite Attribute
Can be divided into smaller sub-attributes, each with its own meaning.
Example: Name → FirstName, MiddleInitial, LastName
Single-valued Attribute
Holds exactly one value for a particular entity.
Example: DateOfBirth, StudentID
Multi-valued Attribute
Can hold multiple values for a single entity. Represented by a double oval.
Example: PhoneNumbers, Degrees
Derived Attribute
Value computed from other attributes; not stored explicitly. Represented by a dashed oval.
Example: Age (derived from DateOfBirth)
Key Attribute
Uniquely identifies each entity in an entity set. Shown as an underlined name inside the oval.
Example: StudentID, CourseID
Strong vs. Weak Entity Sets
Strong Entity Set
Has a primary key of its own to uniquely identify entities. Represented by a single rectangle.
Example: STUDENT, COURSE, DEPARTMENT
Weak Entity Set
Cannot be uniquely identified by its own attributes. Depends on an identifying (owner) entity set. Represented by a double rectangle. Uses a discriminator (partial key) combined with the owner's PK.
Example: DEPENDENT of EMPLOYEE
University E-R Diagram
4Relationships and Cardinality
A relationship set is an association among two or more entity sets, represented by a diamond in E-R diagrams. Mapping cardinalities express the number of entities to which another entity can be associated via a relationship.
Mapping Cardinalities
One-to-One (1:1)
An entity in E1 is associated with at most one entity in E2, and vice versa.
Example: Employee MANAGES Department — one manager per department, one department per manager
One-to-Many (1:N)
An entity in E1 is associated with any number of entities in E2, but an entity in E2 is associated with at most one entity in E1.
Example: Department HAS Employees — one department has many employees, each employee in one department
Many-to-Many (M:N)
An entity in E1 is associated with any number of entities in E2, and an entity in E2 is associated with any number of entities in E1.
Example: Student ENROLLS_IN Course — many students per course, many courses per student
Participation Constraints
Total Participation
Every entity must participate in at least one relationship. Shown with a double line.
Example: Every Employee MUST work for a Department
Partial Participation
An entity may or may not participate in the relationship. Shown with a single line.
Example: Not every Employee is a Manager
E-R Diagram Notation Reference
Strong Entity
A real-world object with its own primary key. The most common building block of E-R diagrams.
Example: STUDENT, COURSE, DEPARTMENT
1 / 13
5Keys
Keys are fundamental to the Relational Model for uniquely identifying tuples and establishing relationships between tables. Understanding the key hierarchy is essential for proper database design.
Superkey
A set of one or more attributes that, taken collectively, uniquely identifies a tuple. May contain redundant attributes.
Example: In Students(StudentID, Name, SSN), both {StudentID, Name, SSN} and {StudentID} are superkeys
Candidate Key
A minimal superkey — no proper subset of it is also a superkey. A relation may have several candidate keys.
Example: {StudentID} and {SSN} are both candidate keys if each is unique
Primary Key
The candidate key chosen by the designer to uniquely identify tuples. Underlined in schema notation. Must be unique and NOT NULL.
Example: StudentID chosen as PK from {StudentID} and {SSN}
Alternate Key
Any candidate key that is not chosen as the primary key. Often enforced with a UNIQUE constraint.
Example: If StudentID is PK, then SSN is an alternate key
Foreign Key
An attribute (or set of attributes) in one relation that references the primary key of another relation. Establishes links between tables and enforces referential integrity.
Example: DeptName in Courses REFERENCES Departments(DeptName)
6Enhanced E-R Modeling
The Extended E-R (EER) model addresses limitations of the basic E-R model by incorporating concepts for modeling complex relationships like generalization, specialization, and aggregation.
Specialization and Generalization
Specialization (Top-Down)
Breaking a higher-level entity set (superclass) into lower-level entity sets (subclasses) based on distinguishing characteristics.
Example: PERSON → EMPLOYEE, CUSTOMER
Generalization (Bottom-Up)
Combining common features of multiple entity sets into a higher-level superclass. The reverse of specialization.
Example: EMPLOYEE, CUSTOMER → PERSON (shared: Name, Address)
ISA Hierarchy Constraints
Disjoint (d)
An entity can belong to at most one subclass.
Example: A PERSON is either an EMPLOYEE or a CUSTOMER, not both
Overlapping (o)
An entity can belong to more than one subclass simultaneously.
Example: A PERSON can be both a STUDENT and an EMPLOYEE
Total Specialization
Every entity in the superclass must belong to at least one subclass. Shown with a double line.
Partial Specialization
An entity in the superclass may not belong to any subclass. Shown with a single line.
Aggregation
Aggregation represents a relationship between a relationship set and an entity set. It is used when a relationship itself participates in another relationship. Notation: a dashed rectangle around a relationship set and its participating entities, treating the whole as a higher-level entity. For example, if a PROJECT USES PARTS, that entire relationship can be MANAGED_BY an EMPLOYEE.
Attribute Inheritance
Subclasses inherit all attributes and relationships of their superclass. For example, if PERSON has Name and SSN, then both EMPLOYEE and CUSTOMER also have these attributes, plus their own specific ones (e.g., Salary, CreditRating).
7SQL Walkthroughs
Creating Strong Entity Tables
CREATE TABLE Departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL UNIQUE,
location VARCHAR(100)
);
CREATE TABLE Professors (
prof_id INT PRIMARY KEY,
p_name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE,
rank VARCHAR(50)
);
CREATE TABLE Courses (
course_id VARCHAR(10) PRIMARY KEY,
c_title VARCHAR(255) NOT NULL,
credits INT CHECK (credits > 0 AND credits <= 6)
);
CREATE TABLE Students (
student_id INT PRIMARY KEY,
s_name VARCHAR(255) NOT NULL,
major VARCHAR(100)
);Lines 1–4: Defines Departments table with dept_id as PK and dept_name as UNIQUE.
Lines 7–11: Defines Professors table with email as UNIQUE constraint.
Lines 14–17: Defines Courses table with a CHECK constraint on credits.
Key insight: Strong entities map directly to tables with their primary keys. Constraints like NOT NULL, UNIQUE, and CHECK enhance data integrity.
Implementing 1:N and M:N Relationships with Foreign Keys
-- 1:N Relationship: Professor works for Department
ALTER TABLE Professors
ADD COLUMN dept_id INT,
ADD CONSTRAINT fk_prof_dept
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
ON DELETE SET NULL ON UPDATE CASCADE;
-- 1:N with attributes: Professor teaches Course
CREATE TABLE Teaches (
prof_id INT,
course_id VARCHAR(10),
semester VARCHAR(20) NOT NULL,
year INT NOT NULL,
PRIMARY KEY (prof_id, course_id, semester, year),
FOREIGN KEY (prof_id) REFERENCES Professors(prof_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
-- M:N Relationship: Student enrolls in Course
CREATE TABLE Enrollments (
student_id INT,
course_id VARCHAR(10),
grade VARCHAR(2) CHECK (grade IN
('A+','A','A-','B+','B','B-','C+','C','C-','D','F','W')),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students(student_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
ON DELETE CASCADE ON UPDATE CASCADE
);Lines 2–6: Adds dept_id as FK to Professors. ON DELETE SET NULL keeps the professor if their department is deleted.
Lines 9–19: Creates Teaches junction table. Composite PK includes semester and year to allow a professor to teach a course multiple times.
Lines 22–32: Creates Enrollments junction table for M:N relationship with a CHECK constraint on grade values.
Key insight: 1:N relationships embed the FK on the "many" side. M:N relationships always require a new junction table.
Populating Tables and Basic Queries
INSERT INTO Departments (dept_id, dept_name, location) VALUES
(1, 'Computer Science', 'Building A'),
(2, 'Physics', 'Building B');
INSERT INTO Students (student_id, s_name, major) VALUES
(1001, 'David Lee', 'Computer Science'),
(1002, 'Eve White', 'Physics');
INSERT INTO Enrollments (student_id, course_id, grade) VALUES
(1001, 'CS101', 'A'),
(1001, 'CS305', 'B+'),
(1002, 'PHY201', 'A-');
-- Get all professors in Computer Science
SELECT p_name, email FROM Professors
WHERE dept_id = (
SELECT dept_id FROM Departments
WHERE dept_name = 'Computer Science'
);Lines 1–3: Inserts sample data into the Departments table.
Lines 9–12: Inserts enrollment data linking students to courses with grades.
Lines 15–19: Uses a subquery to find professors in the Computer Science department.
Key insight: Populating tables tests the schema. Subqueries can link conditions across tables without explicit joins.
Complex Queries with Joins and Aggregation
-- Student enrollment details with grades
SELECT
S.s_name AS StudentName,
C.c_title AS CourseTitle,
E.grade AS Grade
FROM Students S
JOIN Enrollments E ON S.student_id = E.student_id
JOIN Courses C ON E.course_id = C.course_id
ORDER BY S.s_name, C.c_title;
-- Count enrollments per course
SELECT
C.c_title AS CourseTitle,
COUNT(E.student_id) AS NumberOfEnrollments
FROM Courses C
LEFT JOIN Enrollments E ON C.course_id = E.course_id
GROUP BY C.c_title
HAVING COUNT(E.student_id) > 0
ORDER BY NumberOfEnrollments DESC;Lines 2–8: Joins Students, Enrollments, and Courses to combine information from all three tables.
Lines 12–19: Uses LEFT JOIN with GROUP BY and HAVING to count enrollments per course, filtering out courses with zero enrollments.
Key insight: JOINs are fundamental for querying across related tables. GROUP BY with aggregate functions (COUNT, SUM, AVG) enables summarized analysis. HAVING filters groups after aggregation.
8Memory Aids
“Rectangles hold things (entities), diamonds connect things (relationships), ovals describe things (attributes).”
“Super contains Candidate, Candidate picks Primary, the rest are Alternate, Foreign points elsewhere.”
“Many-to-many? Make a new table. Take both PKs, they become your composite PK and FKs.”
“The foreign key always goes on the ‘many’ side. Many employees belong to one department — put dept_id in the Employee table.”
“Weak entities are like apartment numbers — ‘Apt 3B’ means nothing without the building address. They need their owner's PK.”
“Double line = must participate (mandatory). Single line = may participate (optional). Think: double = definite.”
“Sigma (σ) selects rows (horizontal slice). Pi (π) projects columns (vertical slice). Join (⋈) merges tables.”
9Common Mistakes
Mistake: Modeling PhoneNumber as an entity when it is just an attribute of Person.
Correct: If a concept has complex attributes of its own or participates in relationships independently, it is an entity. If it is just a descriptive property, it is an attribute. Multi-valued attributes are a common source of this confusion.
Mistake: Modeling Enrollment as an entity with StudentID and CourseID, instead of a relationship ENROLLS between STUDENT and COURSE.
Correct: A relationship describes an interaction between entities. An entity represents a distinct object. If a concept primarily describes a link between others, it is a relationship set.
Mistake: Specifying 1:1 for Student ENROLLS_IN Course when it should be M:N.
Correct: Carefully analyze business rules. Ask: “Can a student enroll in multiple courses?” “Can a course have multiple students?” If yes to both, it is M:N.
Mistake: Putting the foreign key on the “one” side instead of the “many” side in a 1:N relationship.
Correct: In a 1:N relationship, the FK always goes on the “many” side. Department (1) has many Employees (N) — put dept_id in the Employee table.
Mistake: Making an entity weak when it clearly has a natural primary key.
Correct: A weak entity is truly dependent on another entity for its identification. If it has a unique identifier of its own (e.g., SSN, ISBN), it is a strong entity.
Mistake: Defining foreign keys without specifying ON DELETE or ON UPDATE actions.
Correct: Always specify referential actions (CASCADE, SET NULL, RESTRICT) to handle deletions and updates of referenced rows. Without them, orphaned records or unexpected errors can occur.
Mistake: Not identifying primary keys for strong entities or discriminators for weak entities.
Correct: Every entity set must have a mechanism for unique identification. Strong entities need a primary key; weak entities need a discriminator combined with their owner's PK.
Mistake: Having both WORKS_FOR and BELONGS_TO between Employee and Department when they mean the same thing.
Correct: Ensure each relationship conveys unique semantic information. Redundant relationships lead to data inconsistency and wasted storage.
Frequently Asked Questions
- What is the difference between the Relational Model and the E-R Model?
- The E-R Model is a high-level conceptual data model used for database design — it focuses on entities, relationships, and attributes using diagrams. The Relational Model is a logical data model that represents data as relations (tables) with tuples (rows) and attributes (columns). E-R diagrams are used during the design phase and then systematically converted into a relational schema for implementation.
- What is the difference between a primary key and a foreign key?
- A primary key uniquely identifies each tuple (row) in a relation. It must be unique and not null. A foreign key is an attribute (or set of attributes) in one relation that references the primary key of another relation, establishing a link between the two tables and enforcing referential integrity.
- How do you convert a many-to-many (M:N) relationship to a relational schema?
- Create a new junction (bridge) table that contains the primary keys of both participating entity sets as foreign keys. The composite of these foreign keys typically forms the primary key of the new table. Any attributes of the relationship itself are also added to this junction table.
- What is a weak entity set and how is it represented?
- A weak entity set cannot be uniquely identified by its own attributes alone — it depends on a strong (identifying) entity set. In E-R diagrams, it is shown as a double rectangle, connected to its identifying entity via a double diamond (identifying relationship). Its partial key (discriminator) is underlined with a dashed line, and its full primary key combines the owner's primary key with the discriminator.
- What is the difference between specialization and generalization in E-R modeling?
- Specialization is a top-down approach: a higher-level entity set (superclass) is broken into lower-level entity sets (subclasses) based on distinguishing characteristics. Generalization is the reverse bottom-up approach: common attributes of multiple entity sets are abstracted into a higher-level superclass. Both use the ISA triangle notation. Constraints include disjoint vs. overlapping and total vs. partial.
- What is referential integrity and why is it important?
- Referential integrity ensures that a foreign key value in one table always references a valid, existing primary key in another table. It prevents orphaned records — for example, an enrollment record pointing to a student who does not exist. It is enforced using FOREIGN KEY constraints with optional ON DELETE and ON UPDATE actions (CASCADE, SET NULL, RESTRICT).
Practice Quiz
Test your understanding of the relational model and E-R diagrams — select the correct answer for each question.
1.Which of the following is NOT a fundamental property of a relation in the relational model?
2.In an E-R diagram, what does a rectangle typically represent?
3.What does a diamond shape represent in an E-R diagram?
4.Which type of key is a superkey that contains no redundant attributes, meaning no proper subset of its attributes is also a superkey?
5.A relationship where an entity in set A can be associated with multiple entities in set B, and an entity in set B can be associated with multiple entities in set A, is known as what type of mapping cardinality?
6.If every entity in an entity set must participate in a relationship set, what type of participation constraint is it?
7.Which of the following statements is true about a weak entity set?
8.The 'ISA' relationship in an E-R model, often represented by a triangle, signifies which extended E-R feature?
9.When converting an M:N (many-to-many) relationship set from an E-R diagram to a relational schema, how is it typically represented?
10.Which relational algebra operation is used to select rows (tuples) from a relation that satisfy a given predicate (condition)?
Study Tips
- Practice E-R to Schema conversion: Take any real-world scenario (library, hospital, e-commerce) and draw the E-R diagram, then convert it to a relational schema step by step.
- Identify keys carefully: For every relation, list all superkeys, narrow down to candidate keys, choose a primary key, and identify foreign keys.
- Write SQL DDL: After converting to a schema, actually write the CREATE TABLE statements with all constraints (PK, FK, NOT NULL, UNIQUE, CHECK).
- Test with sample data: Insert data and run queries to verify your schema handles real scenarios correctly, including edge cases like deletions and cascading updates.