Part 1: Fundamentals & Relational Design

1. DBMS vs. RDBMS (The Structural Shift)

• DBMS: A software system to manage data. Data is often stored in files (flat files, XML, JSON). Relationships are generally handled at the application level.

• RDBMS (Relational): A type of DBMS based on the relational model (E.F. Codd).

• Strict Structure: Data is in tables (rows/columns).

• Referential Integrity: Enforced by Foreign Keys.

• ACID Compliance: Guaranteed transactional integrity.

• SQL: A structured query language for accessing and manipulating data.

• Interview line: all RDBMS are DBMS, but not all DBMS are relational — the relational model adds structure, integrity constraints, and a query language.

2. The Three-Schema Architecture (ANSI/SPARC)

This is the standard framework for achieving Data Independence. The goal is to ensure that changes at one level do not require changes at other levels.

• Internal Level (Physical): Describes how data is stored on disk (file structures, indexing, data compression, encryption).
• Conceptual Level (Logical): The “big picture.” Describes what data is stored, the relationships, constraints, and security (e.g., the ER diagram or schema).
• External Level (View): The “user’s view.” Different users or applications see only the data they need (e.g., a customer sees their profile; an admin sees full account history).

Why it matters: It provides Data Independence.

• Logical data independence: change the conceptual schema without touching the external views (add a column — users don’t break).
• Physical data independence: change the storage layout/indexing without changing the conceptual or external schemas.

Schema vs. Instance: the schema is the database structure (tables, columns, constraints) — mostly stable; the instance is the actual data in the database at a given moment — constantly changing.

3. The Role of the Data Dictionary (System Catalog)

The “Database about the Database.” It is a special, hidden set of tables maintained by the DBMS.

• What it stores: Metadata — table names, column names, data types, constraints (Primary/Foreign Keys), user permissions, and index definitions.
• Why it’s vital: When you run a query like SELECT * FROM Users, the DBMS first consults the Data Dictionary to check if Users exists and if you have the permission to read it.

4. Keys & Referential Integrity

The key to the relational model: every row must be uniquely identifiable, and relationships are expressed through keys.

• Super Key: any set of attributes that uniquely identifies a row (a superset may contain redundant attributes).
• Candidate Key: a minimal super key — no proper subset still uniquely identifies a row. A table can have several.
• Primary Key (PK): the chosen candidate key — must be NOT NULL and UNIQUE. One per table.
• Alternate Key: the candidate keys not chosen as the PK.
• Surrogate Key: an artificial key with no business meaning (auto-increment id), used because natural keys may change or be large.
• Foreign Key (FK): a column referencing the PK of another table — it enforces referential integrity (no orphan rows; a child can’t reference a parent that doesn’t exist).
• Cardinality: the relationship multiplicity — 1:1, 1:N, M:N. An M:N relationship is always broken into two 1:N relationships via a junction table.

Key	Unique	Minimal	Nullable	Purpose
Super key	Yes	No	No	General identifier
Candidate key	Yes	Yes	No	All minimal identifiers
Primary key	Yes	Yes	No	Chosen identifier
Alternate key	Yes	Yes	No	Rejected candidates
Surrogate key	Yes	—	No	Artificial id (no meaning)
Foreign key	No	No	Yes (usually)	References another table’s PK

5. The ER Model

The Entity-Relationship model is a conceptual blueprint of the database before tables exist.

• Entity: a real-world object (Employee, Department) → becomes a table. Drawn as a rectangle.
• Attribute: a property of an entity (Name, Salary) → becomes a column. Drawn as an oval; the PK attribute is underlined.
• Relationship: an association between entities (Employee works in Department) → becomes a foreign key when mapped. Drawn as a diamond.
• Weak Entity: an entity that can’t exist without its owner (Room only under Building); identified by the owner’s key + a partial key. Drawn as a double rectangle.
• Participation: total (every entity must participate — double line) vs partial (optional — single line).

Mapping rules (ER → relational): entity → table; 1:N → FK on the “many” side; M:N → junction table with both PKs; weak entity → table keyed by owner PK + partial key.

6. SQL Command Landscape

SQL splits into sub-languages — a favorite “classify this command” question:

Sublanguage	Commands	Purpose
DDL	CREATE, ALTER, DROP, TRUNCATE	Define/change the structure (schema)
DML	INSERT, UPDATE, DELETE, SELECT	Manipulate the data
DCL	GRANT, REVOKE	Control permissions
TCL	COMMIT, ROLLBACK, SAVEPOINT	Manage transactions

• DELETE vs TRUNCATE vs DROP (the classic):
  • DELETE — removes rows, can be filtered with WHERE, is logged per-row, rollback-able, fires triggers. Slow on big tables.
  • TRUNCATE — removes all rows, DDL, no WHERE, minimal logging, not rollback-able (in most DBs), resets identity counters, no triggers. Fast.
  • DROP — removes the entire table structure (data + schema + indexes). Irreversible.
• WHERE vs HAVING: WHERE filters rows before grouping; HAVING filters groups after GROUP BY. HAVING can use aggregate functions, WHERE cannot.