Complete Relational Model Notes

🔶 1. What is the Relational Model?

Definition

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

Data is stored in Relations (Tables)
Each row = Tuple (Record)
Each column = Attribute (Field)
Each column has values from a Domain (Allowed values)

📌 Real-World Analogy: College Record System

Roll_No	Name (only allowed String → Domain)	Age (only allowed Integer → Domain)	Course_ID
101	Ravi	20	C101
102	Meena	21	C102

Table = "Student"
Each row = 1 student → Tuple
Each column → Attribute
Age column only allows integers → Domain

📌 MySQL Datatypes with Example & Use Case

-- Creating the Student table with MySQL datatypes
CREATE TABLE Student (
    Roll_No INT PRIMARY KEY,              -- INT → Numeric values (e.g., 101, 102)
    Name VARCHAR(50),                     -- VARCHAR → Variable length string (e.g., 'Ravi')
    Age INT CHECK (Age >= 18),            -- INT → Only whole numbers (e.g., 20)
    Course_ID CHAR(5),                    -- CHAR → Fixed length string (e.g., 'C101')
    DOB DATE,                             -- DATE → Stored in 'YYYY-MM-DD' format
    Admission_Date DATETIME,              -- DATETIME → 'YYYY-MM-DD HH:MM:SS'
    Remarks TEXT                          -- TEXT → Long text storage (not CLOB in MySQL)
);

🔹 Common MySQL Datatypes & Use Cases

INT → Whole numbers (e.g., Roll_No, Age, Salary, Quantity)
VARCHAR(n) → Variable-length text (e.g., Names, Emails, Addresses)
CHAR(n) → Fixed-length text (e.g., Course_ID, Gender 'M/F')
DATE → Stores only date in 'YYYY-MM-DD' (e.g., '2025-10-01')
DATETIME → Stores date & time in 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP → Auto-updated time values (useful for Created_At, Updated_At)
DECIMAL(p,s) → Exact decimals (e.g., Price DECIMAL(10,2) → 99999999.99)
BOOLEAN (alias of TINYINT(1)) → True/False values
TEXT → Long character text (e.g., Remarks, Description) ✅ replaces CLOB
BLOB → Binary data (e.g., Images, PDF files)

🔶 2. Keys (Uniqueness & Linking)

✅ Primary Key

Uniquely identifies each row in a table.

⚠️ Cannot be NULL or duplicate.

Example: Roll_No in Student table

✅ Candidate Key

All possible unique keys.

Example: Roll_No, Email

✅ Alternate Key

Candidate keys that are NOT chosen as Primary Key.

Example: Email (when Roll_No is Primary)

✅ Super Key

Any set of attributes that uniquely identifies rows.

Example: {Roll_No, Name}

✅ Foreign Key

Links to a Primary Key from another table.

Example: Student.Course_ID → Course.ID

✅ Composite Key

Combination of 2+ columns used as a Primary Key.

Example: {Student_ID, Subject_ID} in Marks table

🔄 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

📌 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

🔶 3. Constraints (Rules for Clean Data)

Constraint Type	Description	Example
Domain	Limits value type	Age should be 18–25
Entity Integrity	Primary Key can't be NULL	Roll_No always required
Referential Integrity	Foreign Key must match valid Primary Key	Course_ID must exist in Course table
Unique	No duplicates allowed	Email
Not Null	Must have value	Name, Age

📌 Tip to Remember Constraints:

💡 D-E-R = Domain, Entity, Referential

🔶 4. Relational Algebra (Operations on Tables)

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

🔹 Select (σ): Filter Rows

σ_Age>21(Student)

Selects all students with age greater than 21

🔹 Project (π): Select Columns

π_Name, Age(Student)

Shows only Name and Age columns

🔹 Union (∪): Combine Rows

Student ∪ Alumni

✅ Both tables must have same attributes

🔹 Set Difference (−): Subtract

Student − Alumni

Students who are not alumni

🔹 Cartesian Product (×): Cross Join

Student × Course

Every student paired with every course

🔹 Join (⨝): Match Rows

Student ⨝ Student.Course_ID = Course.ID

Combines related records from two tables

💡 Trick to Remember Operations:

📌 SPU-JCD = Select, Project, Union, Join, Cartesian, Division

🔶 5. Extended Example (Student + Course)

Student Table

Roll_No	Name	Age	Course_ID
101	Ravi	20	C101
102	Meena	21	C102

Course Table

Course_ID	Course_Name
C101	DBMS
C102	OS

Query: Show names of students who have DBMS course

π_Name(σ_Course_Name='DBMS'(Student ⨝ Course))

🔶 7. Interview Tips ✅

Tip No.	Tip
1	Always explain with Student/Course/Employee example
2	Emphasize why keys are important: uniqueness and relation
3	Prepare 2–3 relational algebra queries
4	Be able to explain JOIN vs PRODUCT
5	Practice how constraints keep data clean
6	Draw mini tables during interviews to explain answers

🔶 8. Common Interview Questions & Solutions

Q1: Difference between Primary Key and Unique Key?

Answer:

Primary Key: One per table, can't be NULL
Unique Key: Can be multiple, allows NULLs

Q2: Write query to get names of students aged 18–21

π_Name(σ_Age>=18 AND Age<=21(Student))< /div>

Q3: Can Foreign Key be NULL?

Answer: Yes, it can be NULL if the relation is optional.

Q4: What is the result of Cartesian Product of 2 tables of 3 and 4 rows?

Answer: 3 × 4 = 12 rows

🎯 Quick Reference Card

Essential Formula

                    Relation = Table
                    Tuple = Row
                    Attribute = Column
                    Domain = Allowed Values
                

Remember: The 3 Golden Rules

Every table must have a Primary Key
Foreign Keys link tables together
Constraints keep data clean and consistent

📚 Complete Notes on Relational Model (DBMS)