Topic 2 of 52
Relational Database
Overview
A relational database stores data in tables (relations) with rows (records) and columns (fields). Tables are linked through keys, allowing complex data relationships without duplication — the foundation of every SQL system.
Syntax
sql
-- A relational database consists of:
-- 1. Tables (relations) — structured data containers
-- 2. Rows (tuples) — individual records
-- 3. Columns (attributes) — fields with defined data types
-- 4. Keys — unique identifiers and relationships
-- 5. Constraints — rules that enforce data integrity
-- Example database schema for an e-commerce app:
-- users table ←→ orders table ←→ order_items table ←→ products table
-- Viewing existing tables (PostgreSQL)
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public';
-- Viewing table structure
\d users -- PostgreSQL
DESCRIBE users; -- MySQLCommon Pitfalls
- Relational databases enforce data integrity through constraints — referential integrity prevents orphan records.
- Never store comma-separated lists in a single column — create a separate relationship table (this violates 1NF).
- Interview tip: RDBMS (Relational Database Management System) examples: PostgreSQL, MySQL, Oracle, SQL Server. NoSQL alternatives: MongoDB, Redis, Cassandra.
Real-World Example
An e-commerce database with related tables:
example
sql
-- The relational model: users place orders, orders have items, items are products
-- users(id, name, email)
-- orders(id, user_id→users, total, status)
-- order_items(id, order_id→orders, product_id→products, qty, price)
-- products(id, name, category_id→categories, price, stock)
-- categories(id, name, parent_id→categories)
-- The power: one query across all tables
SELECT
u.name AS customer,
COUNT(o.id) AS total_orders,
SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id, u.name
ORDER BY lifetime_value DESC
LIMIT 10;