DBMS Notes
A relationship describes an association or connection between two or more entities in the ER model. In the relational model, relationships are implemented...
What is a Relationship?
A relationship describes an association or connection between two or more entities in the ER model. In the relational model, relationships are implemented using foreign keys.
2. One-to-Many (1:N)
One instance of Entity A can be associated with many instances of Entity B, but each instance of B is associated with at most one instance of A.
One department has many employees; each employee belongs to one department.
Real-world examples:
- Department → Employees (1:N)
- Customer → Orders (1:N)
- Parent → Children (1:N)
- Country → Cities (1:N)
Implementation:
CREATE TABLE Department (DeptID INT PRIMARY KEY, DeptName VARCHAR(100));
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(100),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);Foreign key is placed in the "many" side table (Employee).
3. Many-to-Many (M:N)
Each instance of Entity A can be associated with many instances of Entity B, and each instance of B can be associated with many instances of A.
Real-world examples:
- Students ↔ Courses
- Doctors ↔ Patients
- Products ↔ Orders
- Actors ↔ Movies
Implementation (Junction/Bridge Table):
CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(100));
CREATE TABLE Course (CourseID INT PRIMARY KEY, Title VARCHAR(100));
-- Junction table to handle M:N
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
Grade CHAR(2),
EnrollDate DATE,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);Participation Constraints
Total Participation (Mandatory)
Every instance of the entity MUST participate in the relationship.
- Represented by a double line in ER diagrams
- Enforced by NOT NULL constraint on the foreign key
Partial Participation (Optional)
Some instances of the entity may NOT participate in the relationship.
- Represented by a single line in ER diagrams
- Foreign key allows NULL
Cardinality Notation (Min-Max Notation)
The min-max notation specifies the minimum and maximum number of relationship instances for each entity:
(1,1)
(1,N)
- Each employee works in exactly 1 department.
- Each department has at least 1 and at most N employees.
Summary Table
| Relationship | Description | FK Placement | Junction Table |
|---|---|---|---|
| 1:1 | Each A → at most one B | Either table (with UNIQUE) | No |
| 1:N | One A → many B's | In the N-side table | No |
| M:N | Many A's ↔ many B's | Not in either; new table | Yes |
Relationship Attributes
Some relationships themselves have attributes. These are properties of the relationship, not of either entity.
In M:N relationships, relationship attributes are stored in the junction table.
Continue the lesson path