We are tasked with learning and practicing fundamental SQL operations through several interconnected phases. Each phase builds upon the previous one, taking us from basic database design to complex data manipulations. Let's break down what we need to accomplish in each phase:
We need to design a database schema for a coffee shop loyalty program where:
Each coffee purchase earns 1 point
New members start with 5 points
10 points can be redeemed for a free coffee
We need to track customer information and purchase/redemption history
We need to implement our design by creating tables with specific constraints:
-- Customers Table Structure
CREATE TABLE customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(40) NOT NULL,
phone BIGINT UNIQUE,
email VARCHAR(255) UNIQUE,
points INTEGER NOT NULL DEFAULT 5,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Coffee Orders Table Structure
CREATE TABLE coffee_orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER,
is_redeemed BOOLEAN DEFAULT 0,
ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
We'll practice various SQL operations including:
INSERT operations for adding new customers and orders
SELECT operations for checking point balances
UPDATE operations for modifying customer information
DELETE operations for removing records
We'll create a more complex database system for an office environment tracking:
Employee information and departments
Workplace relationships
Performance reviews
Office parties and their budgets
Let's approach this systematically, breaking it down into manageable steps:
1. Identify all necessary entities (customers, orders)
2. Determine relationships between entities
3. Define required attributes for each entity
4. Establish constraints to maintain data integrity
1. Create table drop statements for clean slate
2. Implement customers table with constraints
3. Implement coffee_orders table with constraints
4. Add foreign key relationships
1. Create helper functions for common operations
2. Implement customer registration process
3. Create purchase recording system
4. Implement point redemption logic
1. Design expanded schema for office management
2. Implement additional tables for new requirements
3. Create operations for managing relationships
4. Implement party and review tracking system
-- Core tables needed:
-- 1. customers (store customer information and points)
-- 2. coffee_orders (track purchases and redemptions)
-- 3. customer_contacts (store multiple contact methods)
CREATE TABLE customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(40) NOT NULL,
points INTEGER NOT NULL DEFAULT 5,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE customer_contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL,
contact_type VARCHAR(20) NOT NULL,
contact_value VARCHAR(255) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE coffee_orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL,
is_redeemed BOOLEAN DEFAULT FALSE,
ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
To make our tables rerunnable, we start with DROP statements:
-- First ensure clean slate
DROP TABLE IF EXISTS coffee_orders;
DROP TABLE IF EXISTS customers;
-- Then create tables with proper constraints
CREATE TABLE customers (
-- Table definition as shown above
);
CREATE TABLE coffee_orders (
-- Table definition as shown above
);
Here's how we handle common operations:
-- Adding a new customer
INSERT INTO customers (name, phone, email)
VALUES ('Rachel', '1111111111', NULL);
-- Recording a coffee purchase
INSERT INTO coffee_orders (customer_id, is_redeemed)
VALUES (1, 0);
UPDATE customers
SET points = points + 1
WHERE id = 1;
-- Redeeming points
UPDATE customers
SET points = points - 10
WHERE id = 1
AND points >= 10;
INSERT INTO coffee_orders (customer_id, is_redeemed)
VALUES (1, 1);
-- Create department table
CREATE TABLE departments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL
);
-- Create employees table
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
department_id INTEGER,
role VARCHAR(100) NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
-- Create relationships table
CREATE TABLE relationships (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employee1_id INTEGER NOT NULL,
employee2_id INTEGER NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
FOREIGN KEY (employee1_id) REFERENCES employees(id),
FOREIGN KEY (employee2_id) REFERENCES employees(id)
);
-- Create performance_reviews table
CREATE TABLE performance_reviews (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employee_id INTEGER NOT NULL,
score DECIMAL(3,1) NOT NULL,
review_date DATE NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees(id)
);
-- Create office_parties table
CREATE TABLE office_parties (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date DATE NOT NULL,
budget DECIMAL(10,2) NOT NULL,
is_offsite BOOLEAN DEFAULT FALSE,
is_cancelled BOOLEAN DEFAULT FALSE
);
Think of database normalization like organizing a closet. Just as you wouldn't want to throw all your clothes into one big pile, we don't want to store all our data in one big table. Instead, we organize related items together and create connections between them.
Foreign keys are like reference numbers in a library. When you check out a book, the library keeps a record that connects your library card number (foreign key) to the book's ID. Similarly, in our database, we use foreign keys to connect orders to customers, or employees to departments.
Database constraints are like rules in a board game. Just as you can't make certain moves in chess, constraints prevent invalid data operations in your database. For example, the NOT NULL constraint is like a rule that says "you must fill in this field before continuing."
1. Always drop tables in reverse order of their dependencies
2. Use DEFAULT values to ensure data consistency
3. Implement foreign key constraints to maintain referential integrity
4. Use transactions for operations that must succeed or fail together
The coffee shop system we built is similar to many real-world loyalty programs, including:
Airline frequent flyer programs
Restaurant reward systems
Retail store point systems
Our office management system is similar to HR systems used by companies to track:
Employee information and relationships
Performance management
Company events and budgets
Try extending the systems with these features:
1. Add a promotion history tracking system
2. Implement a point expiration system for the loyalty program
3. Create a reporting system for analyzing customer purchase patterns
4. Add an audit trail for tracking all data changes