π Database Dashboard
Live statistics from the in-browser SQLite database.
π Entity Relationship Overview
Supertype
π€ Users
Stores all system users.
Role: Mentor / Mentee / Admin
Role: Mentor / Mentee / Admin
Subtypes (IS-A)
π§βπ« Mentors | π Mentees
Generalization / Specialization.
Table-Per-Subclass mapping.
Table-Per-Subclass mapping.
Weak Entity
π EmergencyContacts
Composite PK (ContactID, MenteeID).
Depends on Mentee for existence.
Depends on Mentee for existence.
M:N Resolution
π
Sessions
Resolves M:N between Mentor and Mentee.
FK to both subtypes.
FK to both subtypes.
Restricted Access
π SensitiveNotes
Confidential mentor observations.
Access via Views + GRANT/REVOKE.
Access via Views + GRANT/REVOKE.
Normalization
1NF β 2NF β 3NF β BCNF
All tables proved to BCNF.
Zero anomalies; no transitive FDs.
Zero anomalies; no transitive FDs.
π Quick Query
π₯ Users Table
Supertype β all system users regardless of role.
π§βπ« Mentors Table
Subtype of Users β mentor-specific attributes.
π Mentees Table
Subtype of Users β student-specific attributes.
π Sessions Table
Resolves M:N between Mentors and Mentees.
π SensitiveNotes Table
Confidential mentor observations β restricted access.
π Emergency Contacts
Weak Entity β Composite PK (ContactID, MenteeID).
β‘ Query Runner
Run the 5 required queries or write your own SQL.
Query 1 β Aggregation
GROUP BY + HAVING + COUNT + AVG
Query 2 β Multi JOIN
4-table INNER JOIN with aliases
Query 3 β Subquery
Same result as Query 2 using subqueries
Query 5 β Relational Algebra SQL
Priya's completed sessions + mentees
β Relational Algebra
Theoretical foundation for query optimization.
SQL Query (Query 5)
SELECT ume.name AS MenteeName, s.session_date, s.topic
FROM Users um
JOIN Sessions s ON um.UserID = s.MentorID
JOIN Users ume ON s.MenteeID = ume.UserID
WHERE um.name = 'Dr. Priya Sharma'
AND s.status = 'Completed';
Relational Algebra Expression
Step 1: Ο(name = 'Dr. Priya Sharma')(Users) β Mentor_M
Step 2: Ο(status = 'Completed')(Sessions) β CompSessions
Step 3: Mentor_M β[UserID = MentorID] CompSessions β MentorSessions
Step 4: MentorSessions β[MenteeID = UserID] Users β FullResult
Step 5: Ο(name, session_date, topic)(FullResult) β FINAL OUTPUT
Full Expression:
Ο(name, session_date, topic) (
( Ο(name='Dr. Priya Sharma')(Users) β[UserID=MentorID] Ο(status='Completed')(Sessions) )
β[MenteeID=UserID]
Users
)
Why This Matters β Query Optimization
| Optimization Rule | What Happens | Performance Impact |
|---|---|---|
| Selection Pushdown | Ο applied BEFORE the join β filters Users to 1 row | Reduces join from O(NΓM) to O(1ΓM) |
| Projection Pushdown | Ο removes unused columns before joins | Smaller tuples β less I/O and memory |
| Join Reordering | Optimizer joins smallest result first | 1-row Mentor_M joined first is always cheaper |
βΆ Run the Relational Algebra Query
π Relational Schema
All Primary Keys, Foreign Keys, and Constraints.
Users (Supertype)
Users(
UserID INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
phone VARCHAR(20),
role VARCHAR(10) NOT NULL CHECK (role IN ('Mentor','Mentee','Admin'))
)
Mentors (Subtype of Users)
Mentors(
MentorID INT PRIMARY KEY, β FK β Users(UserID) ON DELETE CASCADE
department VARCHAR(100) NOT NULL,
specialization VARCHAR(150),
max_mentees INT NOT NULL CHECK (max_mentees >= 1)
)
Mentees (Subtype of Users)
Mentees(
MenteeID INT PRIMARY KEY, β FK β Users(UserID) ON DELETE CASCADE
enrollment_year INT NOT NULL,
program VARCHAR(100) NOT NULL,
GPA DECIMAL(3,2) CHECK (GPA BETWEEN 0.00 AND 4.00)
)
EmergencyContacts (Weak Entity)
EmergencyContacts(
ContactID INT NOT NULL, β Partial key
MenteeID INT NOT NULL, β FK β Mentees(MenteeID) ON DELETE CASCADE
contact_name VARCHAR(100) NOT NULL,
relationship VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL,
PRIMARY KEY (ContactID, MenteeID) β Composite PK
)
Sessions
Sessions(
SessionID INT PRIMARY KEY,
MentorID INT NOT NULL, β FK β Mentors(MentorID)
MenteeID INT NOT NULL, β FK β Mentees(MenteeID)
session_date DATE NOT NULL,
duration_mins INT NOT NULL CHECK (duration_mins > 0),
topic VARCHAR(200) NOT NULL,
status VARCHAR(15) NOT NULL CHECK (status IN ('Scheduled','Completed','Cancelled'))
)
SensitiveNotes
SensitiveNotes(
NoteID INT PRIMARY KEY,
SessionID INT NOT NULL, β FK β Sessions(SessionID)
MentorID INT NOT NULL, β FK β Mentors(MentorID)
MenteeID INT NOT NULL, β FK β Mentees(MenteeID)
note_text TEXT NOT NULL,
confidentiality_level VARCHAR(10) NOT NULL CHECK (confidentiality_level IN ('Low','Medium','High')),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
β Normalization Proof
Formal proof that the schema is in 3NF and BCNF.
Functional Dependencies
Users: UserID β name, email, phone, role
Mentors: MentorID β department, specialization, max_mentees
Mentees: MenteeID β enrollment_year, program, GPA
EmergencyContacts:(ContactID, MenteeID) β contact_name, relationship, phone
Sessions: SessionID β MentorID, MenteeID, session_date, duration_mins, topic, status
SensitiveNotes: NoteID β SessionID, MentorID, MenteeID, note_text, confidentiality_level, created_at
Normal Form Proofs
| Normal Form | Requirement | Status | Evidence |
|---|---|---|---|
| 1NF | Atomic attributes, no repeating groups, PK exists | β Satisfied | All attributes are single-valued; emergency contacts in separate table |
| 2NF | 1NF + no partial dependencies on composite PK | β Satisfied | EmergencyContacts: contact_name depends on full (ContactID, MenteeID); all other tables have single-column PKs |
| 3NF | 2NF + no transitive dependencies (non-keyβnon-key) | β Satisfied | No non-key attribute depends on another non-key attribute in any table; cross-table FDs handled by separate tables |
| BCNF | Every determinant of a non-trivial FD is a superkey | β Satisfied | In every table, only the PK determines non-key attributes; PK is always a superkey |
Anomaly Prevention
| Anomaly | Bad Design Example | How Our Schema Prevents It |
|---|---|---|
| Insertion | Cannot add a Mentor without assigning a Mentee | Mentors table is independent; insert with zero sessions |
| Update | Mentor's department stored in every Session row β N updates | Department stored once in Mentors; FK joins retrieve it everywhere |
| Deletion | Deleting last session of a mentor deletes department info | Mentor data persists independent of Sessions |
π ACID & Concurrency Control
Transactions, isolation, and the Lost Update problem.
ACID Properties β Simultaneous Session + Note Insert
| Property | Definition | Application to This System |
|---|---|---|
| Atomicity | All ops succeed or none | If SensitiveNotes INSERT fails, Sessions UPDATE also rolls back β no orphaned completed session |
| Consistency | DB moves between valid states | After commit: all FK, CHECK, UNIQUE constraints hold β DB is never in a constraint-violating state |
| Isolation | Transactions appear serial | Other mentors see either the old state or fully committed state β never partial data |
| Durability | Committed changes persist | After COMMIT, Write-Ahead Log ensures data survives power failure |
The Lost Update Problem
T1 (Priya): BEGIN
T2 (Arjun): BEGIN
T1: SELECT note_text FROM SensitiveNotes WHERE NoteID=1001; -- reads "Rahul shows..."
T2: SELECT note_text FROM SensitiveNotes WHERE NoteID=1001; -- reads same value
T1: UPDATE SensitiveNotes SET note_text='[Priya version]' WHERE NoteID=1001;
T2: UPDATE SensitiveNotes SET note_text='[Arjun version]' WHERE NoteID=1001;
T1: COMMIT; -- Priya's update lands
T2: COMMIT; -- Arjun OVERWRITES Priya β LOST UPDATE ANOMALY!
Prevention Approaches
| Approach | Mechanism | Best For |
|---|---|---|
| Pessimistic Locking (2PL) | SELECT FOR UPDATE acquires X-Lock; T2 blocks until T1 commits | High-contention writes; strong consistency needed |
| Optimistic CC | Version column: UPDATE fails if version changed since read; app retries | Low-contention; high read throughput required |
| MVCC (PostgreSQL) | Writers create new row versions; readers see consistent snapshot | Mixed read/write workloads; readers never block writers |
B-Tree vs B+-Tree for SensitiveNotes.created_at
| Feature | B-Tree | B+-Tree (Used Here) |
|---|---|---|
| Data Storage | All nodes (internal + leaf) | Leaf nodes only |
| Leaf Linkage | Not linked | Doubly-linked list |
| Range Queries | Poor β needs upward traversal | Excellent β sequential leaf scan |
| ORDER BY support | Requires extra sort step | Free β leaves already sorted |
| Verdict for created_at | β Not optimal | β Optimal choice |