<sub>2025-05-14</sub> <sub>#data-visualization #data-management #sql #hmp669</sub>
<sup>[[maps-of-content|🌐 Maps of Content — All Notes]] </sup>
<sup>Series: [[hmp669|Data Management and Visualization]]</sup>
## Database Implementation: Recursive Relationships
> [!abstract]- Quick Review
>
> Database implementation requires translating business rules into structured relationships, with special attention to recursive relationships (where an entity relates to itself) and many-to-many relationships that must be decomposed using associative entities.
>
> **Key Concepts**:
>
> - Recursive relationships connect instances of the same entity (one-to-many, one-to-one, or many-to-many)
> - Many-to-many relationships must be decomposed into two one-to-many relationships using associative entities
> - ERDs serve as templates for database implementation in tools like Microsoft Access
>
> **Must Remember**:
>
> - Implement recursive one-to-many with a foreign key referencing the primary key within the same table
> - Implement recursive many-to-many with an associative entity containing two foreign keys to the same table
> - Always enforce referential integrity when defining relationships
>
> [!code]- Code Reference
>
> |Action/Feature|Purpose|Example|
> |---|---|---|
> |**Microsoft Access Design**|||
> |Design View|Create and modify table structure|Right-click table → Design View|
> |Set Primary Key|Establish unique identifier for records|Select field(s) → Primary Key button|
> |Data Types|Define type of data stored in fields|Text, Number, Date/Time, etc.|
> |**Relationship Creation**|||
> |Relationships Tool|Define connections between tables|Database Tools → Relationships|
> |Edit Relationships|Set options for relationship|Drag PK to FK → Edit Relationships dialog|
> |Enforce Referential Integrity|Maintain data consistency|Checkbox in Edit Relationships dialog|
> |**ERD Notation**|||
> |Entity|Represent a table|Rectangle with entity name|
> |Relationship Line|Connect related entities|Line with cardinality symbols|
> |Cardinality Symbols|Show relationship type|1-to-1, 1-to-many, many-to-many|
## Database Design Process Overview
### From Business Rules to Implementation
Database implementation follows a structured progression:
1. **Business Rules Analysis**: Understanding operational requirements. Identify entities, relationships, and attributes
2. **Entity-Relationship Diagram (ERD)**: Develop a graphical representation of entities and relationships. Conceptual Model.
3. **Relational Schema**: Detailed tabular format with attributes and keys. Create tables, define keys, and establish relationships in a DBMS like Microsoft Access. Logical Model
4. **Physical Implementation**: Actual database creation. Physical Model.
## Understanding Recursive Relationships
### What is a Recursive Relationship?
A **recursive relationship** occurs when instances of the same entity are related to each other.
**Definition**: "A recursive relationship is one in which a relationship can exist between occurrences or instances of the same entity set."
```mermaid
erDiagram
ENTITY ||--o{ ENTITY : "relates to"
```
> [!tip] Mental Model
> Think of a recursive relationship as an entity "talking to itself." The relationship doesn't connect to a different entity—it connects back to other instances of the same entity.
### Types of Recursive Relationships
Recursive relationships can take the same forms as regular relationships:
- **One-to-Many (1:M)**: One instance relates to many instances (e.g., an employee manages many employees)
![[recursive-relationship-in-a-college-database-1747284623503.webp|389x317]]
- **One-to-One (1:1)**: One instance relates to exactly one other instance (e.g., a person is married to one person)
![[recursive-relationship-in-a-college-database-1747284757017.webp]]
- **Many-to-Many (M:N)**: Many instances relate to many instances (e.g., a course has many prerequisite courses, and a course can be a prerequisite for many courses)
![[recursive-relationship-in-a-college-database-1747284769137.webp]]
> [!important] Key Insight
> "It's like a regular relationship. It has all the three types, one-to-many, many-to-many, one-to-one. The only difference is that the relationship of the entity is to itself."
### Implementing Recursive One-to-Many Relationships
To implement a one-to-many recursive relationship:
1. Create a foreign key in the table that references the primary key of the same table
2. The foreign key represents the "one" side of the relationship
**Example**: Employee-Manager relationship
```mermaid
erDiagram
EMPLOYEE ||--o{ EMPLOYEE : manages
EMPLOYEE {
int employeeID PK
string name
int employeeManager FK
}
```
In this example:
- `employeeID` is the primary key
- `employeeManager` is a foreign key referencing `employeeID` in the same table
- Each employee has one manager (the "one" side)
- Each manager can manage multiple employees (the "many" side)
## Handling Many-to-Many Relationships
### RDBMS Limitation
Relational Database Management Systems (RDBMS) have a fundamental limitation:
> [!warning] Important Rule
> "An RDBMS cannot capture many-to-many relationships. Any many-to-many relationship has to be broken down into two one-to-many relationships using an associative entity."
### Decomposing Many-to-Many Relationships
To implement a many-to-many relationship:
1. Create an **associative entity** (also called junction, bridge, or intersection entity)
2. Create two one-to-many relationships from the original entities to this associative entity
3. The associative entity typically has:
- Foreign keys to both original entities
- These foreign keys often form a composite primary key
- Any attributes specific to the relationship
```mermaid
erDiagram
ENTITY-A ||--o{ ASSOCIATIVE-ENTITY : has
ENTITY-B ||--o{ ASSOCIATIVE-ENTITY : has
ASSOCIATIVE-ENTITY {
int entityA_ID FK,PK
int entityB_ID FK,PK
string attribute1
date attribute2
}
```
### Recursive Many-to-Many Relationships
When a many-to-many relationship is recursive (connects an entity to itself), the same principles apply:
1. Create an associative entity
2. Create two one-to-many relationships from the original entity to this associative entity
3. The associative entity includes two foreign keys to the same table
**Example**: Course Prerequisites relationship
```mermaid
erDiagram
COURSE ||--o{ PREREQ : "is prereq for"
COURSE ||--o{ PREREQ : "has prereq"
PREREQ {
string courseID FK,PK
string prereqID FK,PK
}
```
In this example:
- A course can be a prerequisite for many courses
- A course can have many prerequisites
- The PREREQ table resolves this many-to-many relationship
- Both `courseID` and `prereqID` reference the `COURSE` table
## Microsoft Access Implementation
### Creating Tables and Setting Keys
In Microsoft Access, implementation follows these key steps:
1. Create tables in Design View
2. Define fields (attributes) and data types
3. Set primary keys (single field or composite)
4. Add foreign key fields to establish relationships
### Defining Relationships
To establish relationships in Access:
1. Navigate to Database Tools → Relationships
2. Add relevant tables to the Relationships view
3. Drag primary key from one table to foreign key in another
4. In the dialog, enable "Enforce Referential Integrity"
> [!note] Virtual Copies
> When implementing recursive relationships or one-to-one relationships, Access "automatically creates a virtual copy of the table" in the Relationships view to visually represent the connection back to itself.
### Enforcing Referential Integrity
Referential integrity ensures that:
- Foreign key values must match existing primary key values
- Primary key values cannot be deleted if referenced by foreign keys in other tables
- Changes to primary keys are either prevented or cascaded to foreign keys
> [!tip] Always Enforce
> Always check the "Enforce Referential Integrity" option when defining relationships to maintain data consistency and prevent orphaned records.
## Case Application: College Database Implementation
> [!example]- College Database Implementation
>
> **Business Rules**:
>
> 1. Each student is registered with one department & each department can have many students registered in it
>2. Each student is advised by one faculty, and each faculty can advise many students
>3. Each faculty is affiliated with one department, and a department can have many faculty affiliated
>4. Each department is chaired by one faculty, and each faculty can chair only one department
>5. Each course is listed under one department, and a department can have many courses
>6. Each student can take many course offerings
>7. Each course can have multiple pre-requisite courses, and a course can be a pre-requisite of multiple courses
>
> **ERD Development**:
>
> - Identify entities: STUDENT, FACULTY, DEPARTMENT, COURSE, COURSE_OFFERING
> - Identify relationships: affiliation, assignment, advising, enrollment, prerequisites
> - Identify associative entities: ENROLLMENT, PREREQ
>
> ![[recursive-relationship-in-a-college-database-1747284935601.webp]]
>
> **Implementation Steps**:
>
> 1. Create tables with appropriate fields and data types
> - STUDENT (studentID, firstName, lastName, departmentID, advisorID)
> - FACULTY (facultyID, firstName, lastName, departmentID)
> - DEPARTMENT (departmentID, name, chairID)
> - COURSE (courseID, title, credits, departmentID)
> - COURSE_OFFERING (offeringID, courseID, term, year, instructor)
> - ENROLLMENT (studentID, offeringID, grade)
> - PREREQ (courseID, prereqID)
> 2. Set primary keys:
> - Single-field primary keys for most tables
> - Composite primary keys for ENROLLMENT (studentID + offeringID) and PREREQ (courseID + prereqID)
> 3. Define relationships with referential integrity:
> - Student-Department (departmentID foreign key in STUDENT)
> - Student-Faculty (advisorID foreign key in STUDENT)
> - Faculty-Department (departmentID foreign key in FACULTY)
> - Department-Faculty (chairID foreign key in DEPARTMENT)
> - Course-Department (departmentID foreign key in COURSE)
> - Course Offering-Course (courseID foreign key in COURSE_OFFERING)
> - Student-Course Offering via ENROLLMENT (studentID and offeringID foreign keys)
> - Course-Course via PREREQ (courseID and prereqID foreign keys)
>
> > | Table Name | Column Name | Primary Key | Foreign Key (References) |
> | :------------------ | :----------- | :---------- | :-------------------------- |
> | **STUDENT** | studentID | Yes | |
> | | firstName | | |
> | | lastName | | |
> | | departmentID | | DEPARTMENT(departmentID) |
> | | advisorID | | FACULTY(facultyID) |
> | **FACULTY** | facultyID | Yes | |
> | | firstName | | |
> | | lastName | | |
> | | departmentID | | DEPARTMENT(departmentID) |
> | **DEPARTMENT** | departmentID | Yes | |
> | | name | | |
> | | chairID | | FACULTY(facultyID) |
> | **COURSE** | courseID | Yes | |
> | | title | | |
> | | credits | | |
> | | departmentID | | DEPARTMENT(departmentID) |
> | **COURSE_OFFERING** | offeringID | Yes | |
> | | courseID | | COURSE(courseID) |
> | | term | | |
> | | year | | |
> | | instructor | | FACULTY(facultyID) |
> | **ENROLLMENT** | studentID | Yes (Part) | STUDENT(studentID) |
> | | offeringID | Yes (Part) | COURSE_OFFERING(offeringID) |
> | | grade | | |
> | **PREREQ** | courseID | Yes (Part) | COURSE(courseID) |
> | | prereqID | Yes (Part) | COURSE(courseID) |
>
>
> ### Key Implementation Details
>
> #### Course Prerequisites (Recursive Many-to-Many)
>
> The college database includes a crucial recursive many-to-many relationship: course prerequisites.
>
> Business Rule: "Each course can have multiple prerequisite courses, and a course can be a prerequisite of multiple courses."
>
> Implementation:
>
> 1. Create a PREREQ table with two foreign keys to COURSE
> 2. Both foreign keys together form the composite primary key
> 3. Each record in PREREQ links one course to one of its prerequisites
>
> ```mermaid
> erDiagram
> COURSE ||--o{ PREREQ : "has prereq"
> COURSE ||--o{ PREREQ : "is prereq for"
> COURSE {
> string courseID PK
> string title
> int credits
> string departmentID FK
> }
> PREREQ {
> string courseID FK,PK
> string prereqID FK,PK
> }
> ```
>
> ![[recursive-relationship-1747286469490.webp]]
> ![[recursive-relationship-1747288960679.webp]]
>
>
> #### Course vs. Course Offering
>
> An important distinction in the college database:
>
> - **Course**: General description (e.g., "Database Management")
> - **Course Offering**: Specific instance of a course in a term (e.g., "Database Management in Winter 2022")
>
> Students enroll in course offerings, not courses directly. This distinction allows:
>
> - The same course to be offered multiple times
> - Different instructors to teach different sections
> - Students to enroll in specific instances with specific schedules
## Conceptual Insights for Database Design
### Entity vs. Instance
Understanding the difference between an entity and its instances is crucial:
- **Entity**: The category or type (e.g., COURSE)
- **Instance**: A specific occurrence of that entity (e.g., "Database Management 101")
Recursive relationships connect different instances of the same entity.
### Relationship Implementation Patterns
Different relationship types have standard implementation patterns:
- **One-to-Many**: Foreign key in the "many" side table
- **One-to-One**: Foreign key in either table with unique constraint
- **Many-to-Many**: Associative entity with foreign keys to both related entities
### The Power of Associative Entities
Associative entities do more than just resolve many-to-many relationships—they can:
- Store relationship-specific attributes (like enrollment grades or assignment dates)
- Provide flexibility for future expansion
- Create clear boundaries between different types of relationships
## Summary: From Concept to Implementation
The database implementation process for a college database demonstrates several key concepts:
1. **Recursive Relationships** connect instances of the same entity and can be one-to-many, one-to-one, or many-to-many
2. **Many-to-Many Relationships** (including recursive ones) must be decomposed using associative entities
3. **ERDs serve as templates** for database implementation, guiding table creation and relationship definition
4. **Implementation in Access** involves creating tables with appropriate keys and defining relationships with referential integrity
--
Reference: