Lesson 1

This module introduces the vocabulary and core ideas behind relational database design. The goal is not memorization; the goal is fluency. If you can describe data in terms of tables, keys, and relationships, you can design databases that scale, enforce rules, and support reliable querying.

• Learning Objectives:
  After completing the lessons in this module, you should be able to:
  1. Describe the relational database model
  2. Describe the logical structure of a relational database (tables, keys, relationships)
  3. Explain how relational tables are linked for data access (foreign keys and joins)
  4. Explain why SQL is the standard language for relational databases
  5. Describe core functions of a database management system (DBMS)

A relational database stores information in tables (also called relations). Each table describes one subject area (customers, orders, products). Tables have:

• Rows (records/tuples): one instance of the subject (one customer, one order)
• Columns (attributes): properties of the subject (customer_name, order_date)

The relational model is built on ideas from set theory and predicate logic. Practically, that means we define rules about what data is valid, and the database helps enforce those rules.

A relational database is more than a collection of tables. It is a managed system that includes:

• Schema: the blueprint (tables, columns, data types, keys, constraints)
• Primary keys: columns (or column sets) that uniquely identify each row
• Foreign keys: columns that reference a primary key in another table (relationships)
• Constraints: rules such as NOT NULL, UNIQUE, and CHECK that protect data quality
• Indexes: structures that improve lookup speed (at the cost of storage and write overhead)
• Views: stored query definitions that present data in a reusable shape
• Metadata: information about the database itself (sometimes called the data dictionary)

This module focuses on the logical view (how data is modeled). Later modules can expand into physical storage and performance tuning.

Tables become powerful when they are related. Relationships are typically implemented with foreign keys, and queried using joins.

• Foreign key: a column in one table that references a primary key in another table
• Referential integrity: the rule that foreign-key values must match an existing parent row (or be NULL when optional)
• Join: a query operation that combines rows from multiple tables based on related columns

Example: An Orders table stores customer_id to indicate which customer placed each order. The database can then join Customers to Orders to produce reports and application views.

SQL (Structured Query Language) is the standard interface for relational databases because it is:

1. Standardized: supported across major platforms (Oracle, SQL Server, PostgreSQL, MySQL)
2. Declarative: you state what you want, and the DBMS chooses an efficient execution plan
3. Complete: covers data definition (DDL), data manipulation (DML), security (DCL), and transactions (TCL)
4. Optimized: mature engines include optimizers, statistics, and indexing strategies to execute queries efficiently

In a business environment, we store facts (customers, products, orders) and we store how those facts relate (a customer places orders; an order contains line items). A true database includes both:

• Data (the rows and columns)
• Relationships and rules (keys, constraints, referential integrity)

Users and applications query the database in terms of relationships. The DBMS translates that request into physical access operations (reading blocks, using indexes, sorting, joining).

A relational database is a collection of related tables. Each table stores rows, and each row stores values for a defined set of columns.

Customer(customer_id, name, city)
OrderHeader(order_id, customer_id, order_date)
OrderLine(order_id, product_id, qty, line_total)
Product(product_id, description, sale_price)

• Primary key (PK): uniquely identifies a row (for example, customer_id, order_id, product_id)
• Foreign key (FK): references a PK in another table (for example, OrderHeader.customer_id references Customer.customer_id)
• Cardinality: how many rows can relate (1:1, 1:N, M:N)
• Optional vs. mandatory relationships: whether a related row is required (often represented by NULLability and FK constraints)

The next lessons expand these concepts into modeling patterns you will use repeatedly: entity definition, relationship design, and normalization.

Use this list as a reference while reading the next lessons:

• Entity: a real-world concept you store data about (Customer, Order, Product)
• Attribute: a property of an entity (name, order_date, sale_price)
• Domain: the allowed set of values for an attribute (data type + rules)
• NULL: “unknown” or “not applicable” (not the same as zero or empty string)
• Normalization: structuring tables to reduce redundancy and update anomalies
• Transaction: a unit of work that commits or rolls back as a whole
• ACID: reliability properties of transactions (Atomicity, Consistency, Isolation, Durability)
• Index: a performance structure that accelerates lookups and joins
• Query optimizer: DBMS component that chooses an efficient execution plan
• DDL / DML / DCL / TCL: SQL categories for schema, data, security, transactions