Relational Data Model

A relation is a set of tuples that share the same attributes. In practice, this is a table. The mathematical foundation matters because it guarantees that a relation has no duplicate tuples (rows) and no ordering. SQL relaxes both constraints: tables can have duplicate rows and results have no guaranteed order unless you specify ORDER BY. But the theoretical guarantee of uniqueness is why primary keys exist. Every properly designed relation has a primary key that enforces the no-duplicates rule.

A tuple is a single record within a relation. Each tuple contains one value for each attribute in the relation. In a customers relation with attributes (customer_id, name, email), a tuple might be (1, 'Alice Chen', 'alice@example.com'). Tuples are unordered within a relation. There is no 'first row' or 'last row' in the relational model. SQL engines store rows in physical order for performance, but the logical model treats all tuples as equal members of a set.

An attribute defines a named property with a specific domain (data type). The domain constrains what values the attribute can hold. An age attribute with domain INTEGER cannot hold the string 'thirty-five.' Attributes are unordered within a tuple. Saying 'the third column' has no meaning in the relational model, though SQL engines assign ordinal positions for convenience. Each attribute must have a unique name within its relation.

A domain is the set of allowable values for an attribute. INTEGER, VARCHAR(255), and BOOLEAN are domains. But domains go beyond simple types. An 'email_address' domain might be VARCHAR(255) with a CHECK constraint enforcing a valid format. A 'positive_amount' domain might be DECIMAL(10,2) with a CHECK that value > 0. Domains enforce data quality at the model level, not the application level. This distinction is critical in data engineering where multiple pipelines write to the same tables.

A primary key is an attribute (or set of attributes) that uniquely identifies each tuple in a relation. It must be unique and non-NULL. The relational model requires every relation to have a primary key because it enforces the fundamental no-duplicates rule. In practice, primary keys are either natural keys (business-meaningful values like email or ISBN) or surrogate keys (system-generated integers or UUIDs with no business meaning). Data warehouses typically use surrogate keys for dimensions and composite keys for facts.

A foreign key is an attribute in one relation that references the primary key of another relation. It creates a link between the two relations. The orders relation has a customer_id foreign key that references customers(customer_id). The database enforces referential integrity: you cannot insert an order with a customer_id that does not exist in the customers table. Foreign keys are the mechanism that makes the relational model relational. Without them, tables are just independent data stores.

A relational database must manage its stored data using only its relational capabilities. No bypassing the relational engine to manipulate data directly in files. This rule ensures the database enforces all constraints, not the application.

All information in the database must be represented as values in cells within tables. Metadata, schema definitions, and configuration are all stored in tables (system catalogs). There is no hidden data.

Every value must be accessible by specifying the table name, column name, and primary key value. No pointer chasing, no file offsets, no hidden navigation. This is why SELECT with a WHERE clause on the primary key always works.

NULLs represent missing or inapplicable data and must be handled uniformly. NULL is not zero, not an empty string, and not false. It is the absence of a value. Three-valued logic (TRUE, FALSE, UNKNOWN) governs comparisons involving NULL.

The database must support at least one language for data definition (CREATE TABLE), data manipulation (INSERT, UPDATE, DELETE, SELECT), integrity constraints (PRIMARY KEY, FOREIGN KEY, CHECK), and transaction management (BEGIN, COMMIT, ROLLBACK). SQL fulfills this rule.

All views that are theoretically updatable must be updatable through the system. A view on a single table with no aggregation should support INSERT, UPDATE, and DELETE. In practice, most databases limit updatable views to simple cases.

Rule: Each column contains atomic (indivisible) values. No repeating groups. Example: A customer table with a 'phone_numbers' column containing '555-1234, 555-5678' violates 1NF. Fix: create a separate customer_phones table with one row per phone number. Interview note: 1NF is the baseline. If a table violates 1NF, it is not even a proper relation. Interviewers rarely ask about 1NF directly but might present a denormalized dataset and ask you to identify the violations.

Rule: Must be in 1NF. Every non-key attribute depends on the entire primary key, not just part of it. Example: An order_items table with a composite key (order_id, product_id) that includes customer_name violates 2NF. customer_name depends on order_id alone, not on the full composite key. Fix: move customer_name to the orders table. Interview note: 2NF violations only occur with composite primary keys. If your table has a single-column primary key, it automatically satisfies 2NF. Interviewers test this by giving you a table with a composite key and asking what is wrong.

Rule: Must be in 2NF. No transitive dependencies. Non-key attributes must depend on the key, the whole key, and nothing but the key. Example: An employees table with (employee_id, department_id, department_name) violates 3NF. department_name depends on department_id, not directly on employee_id. Fix: move department_name to a departments table. Interview note: 3NF is the gold standard for OLTP databases. The classic interview question is: 'Normalize this table to 3NF.' Walk through each form step by step. State the dependency chain that violates the rule, then show the fix.

Rule: Must be in 3NF. For every functional dependency X -> Y, X must be a superkey. Example: A course_schedule table with (student, course, instructor) where each instructor teaches only one course. The dependency instructor -> course means instructor determines course, but instructor is not a superkey. Fix: decompose into (student, instructor) and (instructor, course). Interview note: BCNF comes up in senior-level interviews. The key difference from 3NF: 3NF allows non-prime attributes to depend on candidate keys other than the primary key. BCNF does not. Most tables that satisfy 3NF also satisfy BCNF.