📚 Complete Notes on Relational Model (DBMS)

Definition

The Relational Model is a way to store and manage data in tabular form. It was introduced by E.F. Codd in 1970.

🔁 Table Relationships

• One-to-One: One student → One locker
• One-to-Many: One course → Many students
• Many-to-One: Many students → One course
• Many-to-Many: Students ↔ Clubs

🔄 Referential Integrity: INSERT, ON DELETE, and ON UPDATE

🎯 Purpose:

• Maintain consistency between parent and child tables.
• Handle what happens to child rows when a parent row is inserted, deleted, or updated.

🧩 Example Tables:

CREATE TABLE Course (
    Course_ID VARCHAR(10) PRIMARY KEY
);

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Course_ID VARCHAR(10),
    FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
    

➕ ON INSERT Rules

• You cannot insert a foreign key in the child table if it does not exist in the parent table.
• Problem: Trying to insert a Student with Course_ID = 'C999' when 'C999' is not in Course table.
• Fix: First insert the corresponding row in the parent (Course) table:

  
  INSERT INTO Course (Course_ID) VALUES ('C999');
  INSERT INTO Student (Student_ID, Course_ID) VALUES (101, 'C999');
              

🛠️ ON DELETE Actions

• ON DELETE CASCADE: Deletes child rows automatically
  Example: Deleting Course 'C101' also deletes all students enrolled in 'C101'
• ON DELETE SET NULL: Sets foreign key to NULL in child rows
  Example: Deleting Course 'C101' sets Student's Course_ID to NULL
• ON DELETE NO ACTION: Prevents deletion if child row exists
  Example: You can't delete Course 'C101' if any student is linked to it

✏️ ON UPDATE Actions

• ON UPDATE CASCADE: Automatically updates child foreign key
  Example: Changing Course_ID from 'C101' to 'C202' also updates Student's Course_ID to 'C202'
• ON UPDATE NO ACTION: Prevents update if foreign key is used in child
  Example: If 'C101' is used by students, you can't rename it

✅ Summary Table:

Action	Effect on Child
ON INSERT (invalid FK)	Insert fails if referenced key is missing in parent
ON DELETE CASCADE	Deletes matching child rows
ON DELETE SET NULL	Sets foreign key to NULL
ON DELETE NO ACTION	Prevents deletion if child exists
ON UPDATE CASCADE	Updates foreign key in child
ON UPDATE NO ACTION	Prevents update if child exists

📌 Quick Summary (Easy Revision)

• Primary Key: Uniquely identifies rows (No NULL)
• Candidate Key: All possible unique keys
• Alternate Key: Candidate key not selected as Primary
• Foreign Key: Points to another table’s Primary Key
• Composite Key: 2+ columns make the key
• Super Key: Any combo that uniquely identifies

It's a mathematical way to express queries on tables.