Database Systems
Complete Course

Table: Student
+----------+-----------+-----+
| roll_no  | name      | age |
+----------+-----------+-----+
| 101      | Ahmad Ali | 20  |
| 102      | Sara Khan | 21  |
+----------+-----------+-----+

Strong Entity: Employee (emp_id, name)
Weak Entity:   Dependent (dep_name, relation)
               — Cannot exist without Employee
               — Identified by: emp_id + dep_name

-- Create a table
CREATE TABLE Student (
    roll_no   INT PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    age       INT,
    dept_id   INT,
    email     VARCHAR(100) UNIQUE
);

-- Add a new column
ALTER TABLE Student ADD COLUMN cgpa FLOAT;

-- Delete the table permanently
DROP TABLE Student;

-- Insert a record
INSERT INTO Student (roll_no, name, age, dept_id)
VALUES (101, 'Ahmad Ali', 20, 1);

-- Update a record
UPDATE Student SET age = 21 WHERE roll_no = 101;

-- Delete a record
DELETE FROM Student WHERE roll_no = 101;

-- Get all students
SELECT * FROM Student;

-- Get only name and age
SELECT name, age FROM Student;

-- Filter with WHERE
SELECT name FROM Student WHERE age > 20;

-- Sort results
SELECT name, cgpa FROM Student ORDER BY cgpa DESC;

-- Limit results
SELECT name FROM Student LIMIT 10;

-- INNER JOIN: only matching rows from both tables
SELECT Student.name, Department.dept_name
FROM Student
INNER JOIN Department ON Student.dept_id = Department.dept_id;

-- LEFT JOIN: all rows from left + matching from right
SELECT Student.name, Department.dept_name
FROM Student
LEFT JOIN Department ON Student.dept_id = Department.dept_id;

SELECT COUNT(*) FROM Student;              -- Total students
SELECT AVG(cgpa) FROM Student;             -- Average CGPA
SELECT MAX(cgpa) FROM Student;             -- Highest CGPA
SELECT MIN(cgpa) FROM Student;             -- Lowest CGPA
SELECT SUM(fee)  FROM Student;             -- Total fee

-- Count students per department
SELECT dept_id, COUNT(*) AS total
FROM Student
GROUP BY dept_id;

-- Departments with more than 10 students
SELECT dept_id, COUNT(*) AS total
FROM Student
GROUP BY dept_id
HAVING COUNT(*) > 10;

-- Students with CGPA higher than average
SELECT name FROM Student
WHERE cgpa > (SELECT AVG(cgpa) FROM Student);

roll_no  → name         (roll_no determines name)
roll_no  → dept_id      (roll_no determines dept_id)
dept_id  → dept_name    (dept_id determines dept_name)

-- BEFORE (Unnormalized): Redundancy problem
| roll_no | name  | dept_id | dept_name  |
| 101     | Ahmad | 1       | Computer   |
| 102     | Sara  | 1       | Computer   |   ← dept_name repeated!

-- AFTER 3NF: Split into two tables
Student:    (roll_no, name, dept_id)
Department: (dept_id, dept_name)

-- Bank Transfer Example
BEGIN TRANSACTION;
    UPDATE Account SET balance = balance - 5000 WHERE acc_id = 1;
    UPDATE Account SET balance = balance + 5000 WHERE acc_id = 2;
COMMIT;    -- Save both changes

-- If anything goes wrong:
ROLLBACK;  -- Undo all changes

T1 locks Row A, waits for Row B
T2 locks Row B, waits for Row A
--> Neither can proceed = DEADLOCK

Solutions:
- Deadlock Prevention (acquire all locks at start)
- Deadlock Detection (kill one transaction)
- Timeout (abort transaction if waiting too long)

-- Create an index
CREATE INDEX idx_name ON Student(name);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON Student(email);

-- Drop an index
DROP INDEX idx_name ON Student;

-- Check if a query uses an index
EXPLAIN SELECT * FROM Student WHERE name = 'Ahmad';

-- See execution plan in MySQL
EXPLAIN SELECT s.name, d.dept_name
FROM Student s
JOIN Department d ON s.dept_id = d.dept_id
WHERE s.cgpa > 3.0;

-- Key output fields:
-- type: ALL=bad (full scan), ref=good, const=best
-- key:  which index is being used
-- rows: estimated rows examined

-- Vulnerable login query (bad practice):
SELECT * FROM users WHERE username = [user_input] AND password = [pass];

-- Attacker types as username:  ' OR 1=1 --
-- The query becomes: WHERE username = '' OR 1=1 --
-- Result: ALL users returned, login bypassed!

-- Safe fix: Use Prepared Statements
SELECT * FROM users WHERE username = ? AND password = ?;

-- Create a new user
CREATE USER 'ali'@'localhost' IDENTIFIED BY 'password123';

-- Give SELECT permission only
GRANT SELECT ON university_db.* TO 'ali'@'localhost';

-- Give full access
GRANT ALL PRIVILEGES ON university_db.* TO 'admin'@'localhost';

-- Remove a privilege
REVOKE INSERT ON university_db.* FROM 'ali'@'localhost';

FLUSH PRIVILEGES;

-- Create a view that hides salary
CREATE VIEW public_employee AS
SELECT emp_id, name, department FROM Employee;

-- User only sees emp_id, name, department
-- salary column is completely hidden

CREATE TABLE Department (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE Student (
    roll_no INT PRIMARY KEY,
    name    VARCHAR(50),
    cgpa    FLOAT,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    title     VARCHAR(100),
    credits   INT,
    dept_id   INT
);

CREATE TABLE Enrollment (
    roll_no   INT,
    course_id INT,
    grade     CHAR(2),
    PRIMARY KEY (roll_no, course_id),
    FOREIGN KEY (roll_no)   REFERENCES Student(roll_no),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);