Database Systems & SQL Revision Summary

1. Database Fundamentals

• Database: A structured collection of related data stored in tables.
• DBMS (Database Management System): Software used to create, manage, and manipulate databases (e.g., MS Access, MySQL, Oracle).
• Components of DBMS:
  • Hardware: Physical devices.
  • Software: The DBMS program itself.
  • Data: The information stored.
  • Users: Designers, admins, and end-users.

2. Table Structure & Constraints

• Table: Organized in Rows (Records) and Columns (Fields).
• Primary Key (PK): A unique identifier for each record.
  • Constraint: Must be UNIQUE and NOT NULL.
• Foreign Key (FK): A field in one table that refers to the Primary Key in another table to establish a relationship.
• Constraints: Rules to enforce data validity.
  • NOT NULL: Prevents empty values.
  • UNIQUE: Ensures no duplicate values in a column.
  • Auto_Increment: Automatically generates a sequential number (commonly used for PK).

3. Relationships

Relationships reduce redundancy and ensure consistency.

1. One-to-One (1:1): Each record in Table A matches exactly one in Table B (e.g., Employee -> ID Card).
2. One-to-Many (1:M): A record in Table A matches multiple records in Table B. The Foreign Key is placed in the "Many" table (e.g., Customer -> Orders).
3. Many-to-Many (M:M): Multiple records in A match multiple in B.
   • Solution: Requires a Junction Table (Intermediate table) containing FKs from both sides (e.g., Students <-> Courses linked by Enrollments).

• Referential Integrity: Ensures that relationships between tables remain consistent. It prevents "Orphan Records" (e.g., deleting a customer who has active orders).
  • ON DELETE CASCADE: Automatically deletes related child records when a parent is deleted.
  • ON UPDATE CASCADE: Automatically updates child FKs when the parent PK is changed.

4. SQL (Structured Query Language)

SQL is divided into sub-languages based on function:

A. DDL (Data Definition Language)

Defines the structure of the database.

• CREATE DATABASE db_name;
• CREATE TABLE table_name (col1 type, col2 type...);
• DROP TABLE table_name; (Deletes table structure and data permanently).
• ALTER TABLE ... ADD/DROP COLUMN; (Modifies structure).

B. DML (Data Manipulation Language)

Manages data inside the tables.

• INSERT INTO table (cols) VALUES (vals);
• UPDATE table SET col=val WHERE condition; (Crucial: Always use WHERE to avoid updating all rows).
• DELETE FROM table WHERE condition; (Crucial: Always use WHERE to avoid clearing the table).

C. DQL (Data Query Language)

Retrieves data.

• SELECT * FROM table; (Selects all).
• SELECT DISTINCT col FROM table; (Selects unique values only).
• ORDER BY col ASC/DESC; (Sorts results).

Common Data Types

• INT: Whole numbers.
• VARCHAR(n): Variable-length text.
• DATE/DATETIME: Dates and times.
• BOOLEAN: True/False values.
• DECIMAL: Precise numbers (currency).