Database

1. Database Design & ER Modeling

Relationships

1:1 (One-to-One)

  • Rare; used for optional or sensitive extra data.

  • Implementation: PK of one table becomes FK in the other (usually the optional side).

1:N (One-to-Many)

  • Most common relationship.

  • Implementation: PK of “One” table becomes FK in “Many” table.

M:N (Many-to-Many)

  • Requires junction table with two FKs.

  • Junction table PK = composite (FK1, FK2).

2. Normalization (1NF → 3NF)

1NF – Atomicity

  • No repeating groups (no lists in one cell).

  • Unique rows + unique column names.

2NF – No Partial Dependencies

  • Must be in 1NF.

  • Applies only if PK is composite.

  • Non-key attributes must depend on entire composite key, not part of it.

3NF – No Transitive Dependencies

  • Must be in 2NF.

  • Non-key column must not depend on another non-key column.

3. Data Types

String Types

  • CHAR(N): Fixed-length, right-padded with spaces. Fast for fixed-length fields.

  • VARCHAR(N): Variable length, stores only needed characters.

  • TEXT: Long text; no DEFAULT allowed.

Numeric Types

  • INT: Whole numbers.

  • FLOAT/DOUBLE: Approximate decimal (don’t use for money).

  • DECIMAL(p, s): Exact decimal (good for money).

  • BOOLEAN: Stored as TINYINT(1). 0 = False; non-zero = True.

Attributes

  • UNSIGNED: Prevents negatives; doubles max positive.

  • ZEROFILL: Pads display with zeros (e.g., 001).

Date/Time

  • DATE: ‘YYYY-MM-DD’

  • DATETIME: Full timestamp.

  • TIMESTAMP: Time-zone converted.

Special Types

  • ENUM: Single choice from a list.

  • SET: Multiple choices from a list.

4. Constraints & Integrity Rules

PRIMARY KEY

  • UNIQUE + NOT NULL.

  • Identifies a row.

FOREIGN KEY

  • Enforces referential integrity.

  • Must match PK in parent table.

ON DELETE/UPDATE options

  • CASCADE: Propagate change.

  • SET NULL: Replace child FK with NULL.

  • RESTRICT / NO ACTION: Prevent deletion/update.

  • SET DEFAULT: Rare in MySQL.

UNIQUE

  • No duplicates (NULL allowed).

NOT NULL

  • Must have a value.

CHECK (MySQL 8.0.16+)

  • Enforces condition (e.g., salary > 0).

AUTO_INCREMENT

  • Increases automatically.

  • Must be PK or UNIQUE.

5. SQL Categories

DDL – Define Structure

  • CREATE, ALTER, DROP, TRUNCATE

DML – Manipulate Data

  • INSERT, UPDATE, DELETE

DQL – Query

  • SELECT

DCL – Permissions

  • GRANT, REVOKE

6. DDL (Creating & Modifying Structures)

Database

CREATE DATABASE db;
DROP DATABASE IF EXISTS db;
USE db;

Table Creation

CREATE TABLE t (
  id INT PRIMARY KEY,
  name VARCHAR(50)
) ENGINE=InnoDB;

  • Use InnoDB for FK support.

ALTER TABLE

  • Add column: ALTER TABLE t ADD col datatype;

  • Drop: ALTER TABLE t DROP COLUMN col;

  • Modify: ALTER TABLE t MODIFY col datatype;

  • Rename: ALTER TABLE t CHANGE old new datatype;

  • Add FK:

ALTER TABLE child
ADD CONSTRAINT fk_name
FOREIGN KEY (col) REFERENCES parent(pk);

Views

  • Virtual tables based on SELECT query:

CREATE VIEW v AS SELECT ...;

Indexes

  • Improve SELECT speed; slow down writes.

CREATE INDEX idx ON table(col);

7. DML (Inserting, Updating, Deleting Data)

INSERT

INSERT INTO t (a,b) VALUES (1,2);
INSERT INTO t (a) VALUES (1), (2), (3);

UPDATE

UPDATE t SET col=val WHERE id=5;

⚠ Missing WHERE updates ALL rows.

DELETE

DELETE FROM t WHERE id=5;

⚠ Missing WHERE deletes ALL rows.

8. Transactions (ACID)

ACID Properties

  • Atomicity: All or nothing.

  • Consistency: Constraints preserved.

  • Isolation: Avoid interference.

  • Durability: Persists after commit.

Commands

START TRANSACTION;
COMMIT;      -- Save permanently
ROLLBACK;    -- Undo to last commit
SAVEPOINT x;
ROLLBACK TO x;

9. SELECT / DQL (Querying Data)

Order of Execution

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Filtering

  • Operators: =, <>, <, >, <=, >=

  • Logic: AND, OR, NOT

  • NULL: IS NULL, IS NOT NULL

  • Pattern matching:

    • % = any string, _ = one char

    • LIKE 'A%'

Aliases

SELECT col AS alias;
FROM table AS t;

Sorting

ORDER BY col ASC;
ORDER BY col1, col2 DESC;

LIMIT

LIMIT 10;
LIMIT 5 OFFSET 10;

10. Functions & Aggregation

Aggregate Functions

  • COUNT()

    • COUNT(*) includes NULL

    • COUNT(col) ignores NULL

  • SUM()

  • AVG()

  • MIN()

  • MAX()

GROUP BY

  • All non-aggregate columns must be grouped.

HAVING

  • Post-group filter.

Scalar Functions

String

  • CONCAT(), SUBSTR(), LENGTH(), TRIM(), UPPER(), LOWER()

Math

  • ROUND(x, d), FLOOR(), CEIL(), ABS(), MOD()

Date

  • NOW(), CURDATE(), DATEDIFF(), DATE_ADD()

11. Joins

CROSS JOIN (Cartesian Product)

INNER JOIN

SELECT ...
FROM A JOIN B ON A.id = B.id;

USING

JOIN B USING (shared_column);

NATURAL JOIN

  • Auto-join on same-named columns.

OUTER JOINS

  • LEFT JOIN: everything from left

  • RIGHT JOIN: everything from right

  • FULL OUTER JOIN (not in MySQL): use UNION

SELF JOIN

SELECT e.name, m.name
FROM Employee e
JOIN Employee m ON e.manager_id = m.emp_id;

12. Subqueries

Scalar Subquery (returns one value)

WHERE salary > (SELECT AVG(salary) FROM Employees)

Row Subquery

WHERE (col1, col2) = (SELECT a, b FROM t LIMIT 1);

Table Subquery

SELECT * FROM (SELECT ... ) AS temp;

Correlated Subquery

(Inner query depends on outer row)

WHERE salary > (
  SELECT AVG(salary)
  FROM Employees E2
  WHERE E2.dept_id = E1.dept_id
);

Operators

  • IN / NOT IN

  • EXISTS / NOT EXISTS

  • ANY / ALL

13. Set Operations

UNION

  • Removes duplicates.

UNION ALL

  • Keeps duplicates.

INTERSECT

  • Rows appearing in both (MySQL: simulate with INNER JOIN or DISTINCT).

MINUS / EXCEPT

  • Rows in first query not in second.

14. Metadata, System Tables & Utilities

INFORMATION_SCHEMA

  • Stores table, column, index metadata.

Commands

SHOW DATABASES;
SHOW TABLES;
SHOW CREATE TABLE t;
DESCRIBE t;
SHOW COLUMNS FROM t;