Designing a multi-currency payment platform with Users, Accounts, Transactions, Merchants, and Escrow Holds. Users have a 1:N relationship with Accounts, Accounts have a 1:N relationship with Ledger Entries, and Transactions have a Many-to-Many relationship with Verification Checks enforced via strict Foreign Keys with ON DELETE RESTRICT to guarantee financial auditability.
-- Relational Schema Definition with Referential & Domain Constraints
CREATE TABLE accounts (
account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL,
currency VARCHAR(3) NOT NULL CHECK (currency IN ('USD', 'EUR', 'GBP', 'JPY')),
balance NUMERIC(18, 4) NOT NULL DEFAULT 0.0000 CHECK (balance >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT
);
CREATE TABLE ledger_entries (
entry_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
account_id UUID NOT NULL,
amount NUMERIC(18, 4) NOT NULL,
entry_type VARCHAR(6) NOT NULL CHECK (entry_type IN ('DEBIT', 'CREDIT')),
transaction_ref UUID NOT NULL,
posted_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE RESTRICT
);Visual representation of control loops, memory layout, and execution flow for DBMS Architecture, ER Modeling & Relational Algebra.
External View Level (individual user query interfaces) -> Conceptual Level (entire database schema structure, tables, constraints) -> Internal/Physical Level (record storage, data compression, disk block pointers). Logical Data Independence allows conceptual schema changes without rewriting application views; Physical Data Independence allows changing file organization or adding indexes without altering the conceptual schema.
Identify Strong Entities (independent existence with Primary Key) and Weak Entities (existence-dependent, identified by Partial Key / Discriminator via an Identifying Relationship). Define Attributes: Simple, Composite (e.g., Name = First + Last), Single-valued, Multi-valued (double oval, mapped to separate junction table), and Derived (dashed oval, computed on the fly). Determine Cardinalities (1:1, 1:N, M:N) and Participation (Total = double line / mandatory; Partial = single line / optional).
Super Key: Any set of attributes uniquely identifying a tuple. Candidate Key: A minimal Super Key with no superfluous attributes. Primary Key: The chosen candidate key. Alternate Key: Candidate keys not chosen as Primary Key. Foreign Key: Attribute referencing a Candidate/Primary key in another table, enforcing Referential Integrity. Constraints: Domain Constraint (valid data type and CHECK conditions), Entity Integrity (Primary Key cannot contain NULLs), Referential Integrity (Foreign key must reference an existing key or be NULL).
Selection (σ_predicate(R)): Filters rows satisfying boolean conditions. Projection (π_A1,A2(R)): Extracts specific columns and eliminates duplicates. Cartesian Product (R × S): Pairs every tuple in R with every tuple in S. Union (R ∪ S), Intersection (R ∩ S), and Set Difference (R - S): Require Union-Compatibility (same degree and matching domains). Join Operations: Theta Join (R ⋈_θ S), Equi Join, Natural Join (R ⋈ S on common attributes), Left/Right/Full Outer Joins. Division (R ÷ S): Finds tuples in R associated with ALL tuples in S (used for queries like "Students who enrolled in ALL courses").
| Feature / Dimension | Relational Algebra (Procedural) | Relational Calculus (Declarative) |
|---|---|---|
| Paradigm | Procedural: Specifies HOW to retrieve data step-by-step through algebraic operators (σ, π, ⋈) | Declarative / Non-Procedural: Specifies WHAT data to retrieve using first-order predicate logic without execution steps |
| Equivalence | Forms the internal execution plan representations for query optimizers | Forms the conceptual mathematical basis of SQL (Codd's Theorem proves expressive equivalence with safe TRC/DRC) |
| Variants | Selection, Projection, Cartesian Product, Join, Division, Rename | Tuple Relational Calculus (TRC: { t | P(t) }) and Domain Relational Calculus (DRC: { <x1,x2> | P(x1,x2) }) |
Detailed answers, interviewer pro tips, key takeaway summaries, and code examples formulated for technical rounds.
✅ Correction: A Foreign Key can legally reference ANY Unique Candidate Key in the target table, not solely the designated Primary Key.
✅ Correction: In Relational Algebra, Selection (σ) corresponds to the SQL WHERE clause (row filtering), whereas Projection (π) corresponds to the SQL SELECT column list.
✅ Correction: Weak entities have a Partial Key (or Discriminator, denoted by a dashed underline), which combined with the Owner entity Primary Key uniquely identifies a weak entity tuple.
Architectural abstractions, conceptual ER schemas, and formal relational operations.