Computer · Chapter 06

🗄️ Database & SQL

DBMS, SQL queries, joins, normalization, ACID.

🗄️ Organized Data

A Database is an organized collection of structured data. A DBMS (Database Management System) is software that manages databases: MySQL, PostgreSQL, Oracle, MS SQL Server, SQLite.

RDBMS (Relational DBMS) — stores data in tables (relations) with rows and columns. Tables are related through keys. Examples: MySQL, PostgreSQL, Oracle, MS Access.

Key terms:
• Table — collection of rows (records) and columns (fields/attributes)
• Primary Key — unique identifier for each row (cannot be null/duplicate)
• Foreign Key — links to primary key of another table (establishes relationship)
• SQL (Structured Query Language) — language to interact with databases

ACID properties (transactions must follow):
Atomicity, Consistency, Isolation, Durability

💻 SQL basics — O-Level/CCC exam

DDL (Data Definition Language): CREATE TABLE, ALTER TABLE, DROP TABLE
DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language): GRANT, REVOKE
TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

SELECT * FROM Students WHERE Marks > 75 ORDER BY Name ASC;
INSERT INTO Students VALUES (1, 'Ravi', 85);
UPDATE Students SET Marks=90 WHERE ID=1;
DELETE FROM Students WHERE ID=1;

📋 Normalization forms

1NF — no repeating groups, each cell has atomic value
2NF — 1NF + no partial dependencies (all columns depend on full primary key)
3NF — 2NF + no transitive dependencies
BCNF — stronger version of 3NF
Goal: reduce redundancy and avoid update anomalies.
NoSQL databases — MongoDB (document), Redis (key-value), Cassandra (column), Neo4j (graph). No fixed schema. Scales horizontally. Used for big data, real-time apps.

🎬

Database Tables and Relationships

Animation
RELATIONAL DATABASE — CLICK TABLES TO EXPLORE 📚 STUDENTS StudentID 🔑 Name CourseID 🔗 1 Ravi Kumar C01 2 Priya Singh C02 3 Amit Sharma C01 4 Sita Devi C03 🔑 Primary Key: StudentID 🔗 Foreign Key: CourseID → Courses Many-to-One 🎓 COURSES CourseID 🔑 CourseName Duration C01 BCA 3 years C02 MCA 2 years C03 B.Tech CSE 4 years 🔑 Primary Key: CourseID Referenced by Students.CourseID CLICK A TABLE TO SEE SQL EXAMPLE SELECT * FROM Students; Both tables are related — CourseID in Students links to CourseID in Courses (foreign key relationship). ACID PROPERTIES A Atomicity All or nothing C Consistency Valid state always I Isolation Transactions independent D Durability Committed data persists

Relational databases power everything — bank transactions, e-commerce, social media, government records.

💻

SQL Query Explorer

Interactive
Basic SELECTSELECT * FROM Students;
With conditionSELECT Name FROM Students WHERE Marks > 75;
Order resultsSELECT * FROM Students ORDER BY Name ASC;
Limit rowsSELECT * FROM Students LIMIT 10;
Distinct valuesSELECT DISTINCT City FROM Students;
Practice (O-Level): What are the different types of JOINs in SQL?
SQL JOINs combine rows from two or more tables based on a related column:

INNER JOIN — returns rows that have matching values in BOTH tables
SELECT * FROM A INNER JOIN B ON A.id = B.id;
Only rows where match exists in both tables.

LEFT JOIN (LEFT OUTER JOIN) — returns ALL rows from left table, matched rows from right. NULL for non-matching right rows.
SELECT * FROM A LEFT JOIN B ON A.id = B.id;

RIGHT JOIN (RIGHT OUTER JOIN) — returns ALL rows from right table, matched rows from left.

FULL OUTER JOIN — returns ALL rows from both tables. NULL where no match.

CROSS JOIN — returns cartesian product (every row of A × every row of B). No ON clause needed.

SELF JOIN — table joined with itself. Used for hierarchical data (employee-manager in same table).

Most common in practice: INNER JOIN and LEFT JOIN.
Example: Customers LEFT JOIN Orders would show all customers, even those with no orders.
Practice (CCC): What is the difference between DELETE, DROP, and TRUNCATE?
DELETE:
• DML command — removes specific rows based on WHERE condition
• DELETE FROM Students WHERE ID = 5;
• Can be rolled back (undone) with ROLLBACK
• Triggers fire (if any defined)
• Table structure and other data remain
• Slower for large data

TRUNCATE:
• DDL command — removes ALL rows from a table
• TRUNCATE TABLE Students;
• Cannot be rolled back (in most databases)
• Faster than DELETE (doesn't log each row)
• Table structure remains, only data is gone
• Auto-increment resets to 1

DROP:
• DDL command — removes the ENTIRE TABLE (structure + data)
• DROP TABLE Students;
• Cannot be rolled back
• Table no longer exists after DROP

Summary: DELETE = remove some rows, TRUNCATE = empty the table, DROP = destroy the table.
Mnemonic: Delete rows, Truncate table data, Drop entire table.
←
Previous
Internet & Networking