Creating an INDEX in SQL

Variant 1: E-commerce Platform

Scenario: You are working on the database for an e-commerce platform. Performance is critical for user experience, especially when searching for products and viewing order histories. You need to add appropriate indexes to optimize common queries.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS OrderItems;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Products;

-- Create the Products table
CREATE TABLE Products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL, -- Stock Keeping Unit
    name VARCHAR(200) NOT NULL,
    category VARCHAR(100),
    price NUMERIC(10, 2) CHECK (price >= 0),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
    date_added DATE DEFAULT CURRENT_DATE
);

-- Create the Orders table
CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL, -- Assuming a separate Customers table exists elsewhere
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending', -- e.g., 'pending', 'processing', 'shipped', 'delivered', 'cancelled'
    total_amount NUMERIC(12, 2)
);

-- Create the OrderItems table (linking Orders and Products)
CREATE TABLE OrderItems (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES Orders(order_id),
    product_id INT NOT NULL REFERENCES Products(product_id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL -- Price at the time of order
);

-- Insert sample data
INSERT INTO Products (sku, name, category, price, stock_quantity, date_added) VALUES
('BK-ADV-01', 'Advanced SQL Guide', 'Books', 49.99, 150, '2023-01-10'),
('EL-LAP-X1', 'UltraSlim Laptop X1', 'Electronics', 1299.00, 50, '2023-02-20'),
('KT-MIX-S2', 'Super Mixer Pro', 'Home Goods', 89.50, 200, '2023-03-15'),
('BK-PYT-BEG', 'Python for Beginners', 'Books', 29.99, 300, '2023-04-01'),
('EL-CAM-Z5', 'Digital Camera Z5', 'Electronics', 450.00, 75, '2023-05-10');

INSERT INTO Orders (customer_id, order_date, status, total_amount) VALUES
(101, '2023-11-01 10:00:00', 'shipped', 179.49),
(102, '2023-11-05 14:30:00', 'processing', 1299.00),
(101, '2023-11-10 09:15:00', 'pending', 79.98),
(103, '2023-11-12 16:45:00', 'delivered', 450.00),
(104, '2023-11-15 11:00:00', 'processing', 89.50);

INSERT INTO OrderItems (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 49.99), (1, 4, 1, 29.99), (1, 3, 1, 89.50), -- Mistake fixed: total matches order 1
(2, 2, 1, 1299.00),
(3, 4, 2, 29.99), (3, 1, 1, 49.99), -- Corrected quantities/prices needed if total should be exactly 79.98. Let's assume order total was an estimate.
(4, 5, 1, 450.00),
(5, 3, 1, 89.50);

Tasks:

  1. Basic Index: Create an index on the Products table to speed up searches based on the product name.
  2. Foreign Key Index: Create an index on the Orders table to quickly find all orders placed by a specific customer_id.
  3. Multicolumn Index: Create a multicolumn index on the Products table to optimize queries that filter by category and then sort or filter by price. Ensure category is the leading column.
  4. Foreign Key Index (Many-to-Many): Create an index on the OrderItems table to efficiently look up items belonging to a specific order_id.
  5. Foreign Key Index (Many-to-Many): Create another index on the OrderItems table to efficiently find all orders that include a specific product_id.
  6. Reindex Table: Rebuild all indexes currently existing on the Products table to optimize their structure after potential data modifications.
  7. Partial Index: Create a partial index on the Orders table for the order_date column, but only include orders with a status of ‘processing’ or ‘pending’. This helps optimize queries looking for active, unshipped orders.
  8. Index on Expression: Create an index on the Products table based on the lowercase version of the name column to facilitate case-insensitive searches.
  9. List Indexes: Use a system catalog query (pg_indexes) to list all indexes currently defined for the OrderItems table.
  10. Drop Index: Remove the expression index created in step 8 (LOWER(name) on Products).
Variant 2: Library Management System

Scenario: You are designing the database for a library system. Efficient searching for books, authors, and tracking borrowed items is crucial for librarians and patrons. You need to implement indexes to optimize these operations.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS BorrowingRecords;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;

-- Create the Authors table
CREATE TABLE Authors (
    author_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100) NOT NULL,
    birth_year INT
);

-- Create the Books table
CREATE TABLE Books (
    book_id SERIAL PRIMARY KEY,
    isbn VARCHAR(17) UNIQUE NOT NULL, -- International Standard Book Number
    title VARCHAR(255) NOT NULL,
    publication_year INT,
    genre VARCHAR(50),
    author_id INT REFERENCES Authors(author_id),
    copies_available INT DEFAULT 1 CHECK (copies_available >= 0)
);

-- Create the BorrowingRecords table
CREATE TABLE BorrowingRecords (
    record_id SERIAL PRIMARY KEY,
    book_id INT NOT NULL REFERENCES Books(book_id),
    member_id INT NOT NULL, -- Assuming a separate Members table exists elsewhere
    borrow_date DATE NOT NULL DEFAULT CURRENT_DATE,
    due_date DATE NOT NULL,
    return_date DATE -- NULL if not yet returned
);

-- Insert sample data
INSERT INTO Authors (first_name, last_name, birth_year) VALUES
('George', 'Orwell', 1903),
('Jane', 'Austen', 1775),
('Isaac', 'Asimov', 1920),
('J.R.R.', 'Tolkien', 1892);

INSERT INTO Books (isbn, title, publication_year, genre, author_id, copies_available) VALUES
('978-0451524935', 'Nineteen Eighty-Four', 1949, 'Dystopian', 1, 3),
('978-0141439518', 'Pride and Prejudice', 1813, 'Romance', 2, 5),
('978-0553293378', 'Foundation', 1951, 'Science Fiction', 3, 2),
('978-0547928227', 'The Hobbit', 1937, 'Fantasy', 4, 4),
('978-0451524900', 'Animal Farm', 1945, 'Allegory', 1, 3);

INSERT INTO BorrowingRecords (book_id, member_id, borrow_date, due_date, return_date) VALUES
(1, 501, '2024-03-01', '2024-03-15', '2024-03-14'),
(3, 502, '2024-03-05', '2024-03-19', NULL), -- Not returned
(2, 501, '2024-03-10', '2024-03-24', NULL), -- Not returned
(5, 503, '2024-03-12', '2024-03-26', '2024-03-20'),
(1, 502, '2024-03-15', '2024-03-29', NULL); -- Not returned

Tasks:

  1. Basic Index: Create an index on the Authors table to speed up searches based on the author’s last_name.
  2. Basic Index: Create an index on the Books table to allow for faster lookups by title.
  3. Unique Index Explanation: The Books table has a UNIQUE constraint on the isbn column. Explain the implication this has for indexing this column.
  4. Foreign Key Index: Create an index on the Books table to optimize queries joining Books with Authors (i.e., index the author_id foreign key).
  5. Multicolumn Index: Create a multicolumn index on the Books table to improve performance for queries that search for books within a specific genre and publication_year. genre should be the first column in the index.
  6. Foreign Key Index (Tracking Table): Create an index on the BorrowingRecords table to quickly find all borrowing records associated with a specific book_id.
  7. Foreign Key Index (Tracking Table): Create an index on the BorrowingRecords table to quickly find all books borrowed by a specific member_id.
  8. Partial Index: Create a partial index on the BorrowingRecords table covering the due_date column, but only for records where the return_date is NULL. This helps efficiently find overdue books or currently borrowed items.
  9. Index on Expression: Create an index on the Books table based on the publication_year column, but only storing the decade (e.g., 1950 for 1951, 1810 for 1813). Use integer division or another appropriate expression. This might optimize broad searches by decade. CREATE INDEX idx_books_pub_decade ON Books (((publication_year / 10) * 10));
  10. List and Drop Index: Use the \d Books meta-command (in psql or pgAdmin’s Query Tool SQL pane) to view the indexes on the Books table. Then, drop the index on title created in step 2.
Variant 3: Project Management Tool

Scenario: You are the database administrator for a project management application. Users frequently search for projects, filter tasks by status, assignee, and priority, and view task lists. Optimizing these queries with indexes is essential for application responsiveness.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS TaskAssignments; -- Adjusted for clarity
DROP TABLE IF EXISTS Tasks;
DROP TABLE IF EXISTS Projects;
DROP TABLE IF EXISTS Users;


-- Create the Users table
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(150)
);

-- Create the Projects table
CREATE TABLE Projects (
    project_id SERIAL PRIMARY KEY,
    project_code VARCHAR(20) UNIQUE NOT NULL, -- e.g., 'ALPHA', 'OMEGA-2'
    name VARCHAR(200) NOT NULL,
    start_date DATE,
    deadline DATE,
    status VARCHAR(20) DEFAULT 'planning' -- 'planning', 'active', 'completed', 'on_hold'
);

-- Create the Tasks table
CREATE TABLE Tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INT NOT NULL REFERENCES Projects(project_id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    priority INT DEFAULT 3, -- e.g., 1 (High) to 5 (Low)
    status VARCHAR(20) DEFAULT 'todo', -- 'todo', 'in_progress', 'review', 'done', 'blocked'
    due_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the TaskAssignments table (linking Tasks and Users)
CREATE TABLE TaskAssignments (
    assignment_id SERIAL PRIMARY KEY,
    task_id INT NOT NULL REFERENCES Tasks(task_id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES Users(user_id),
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (task_id, user_id) -- Ensure a user isn't assigned the same task twice
);


-- Insert sample data
INSERT INTO Users (username, email, full_name) VALUES
('jdoe', 'john.doe@company.com', 'John Doe'),
('asmith', 'alice.smith@company.com', 'Alice Smith'),
('bwhite', 'bob.white@company.com', 'Bob White');

INSERT INTO Projects (project_code, name, start_date, deadline, status) VALUES
('PHOENIX', 'Project Phoenix Rebirth', '2024-01-15', '2024-07-31', 'active'),
('NEBULA', 'Nebula Cloud Migration', '2024-03-01', '2024-12-31', 'planning'),
('TITAN', 'Titan Platform Upgrade', '2023-11-01', '2024-05-31', 'active');

INSERT INTO Tasks (project_id, title, priority, status, due_date) VALUES
(1, 'Define Core Modules', 1, 'done', '2024-02-15'),
(1, 'Develop UI Mockups', 2, 'in_progress', '2024-04-30'),
(1, 'Setup Development Environment', 3, 'todo', '2024-04-10'),
(3, 'Analyze Current Platform', 1, 'in_progress', '2024-04-20'),
(3, 'Draft Upgrade Plan', 2, 'review', '2024-05-05'),
(2, 'Gather Cloud Requirements', 1, 'todo', '2024-04-15');

INSERT INTO TaskAssignments (task_id, user_id) VALUES
(1, 1), -- Task 1 assigned to John Doe
(2, 2), -- Task 2 assigned to Alice Smith
(3, 1), -- Task 3 assigned to John Doe
(4, 3), -- Task 4 assigned to Bob White
(5, 2), -- Task 5 assigned to Alice Smith
(4, 1); -- Task 4 also assigned to John Doe

Tasks:

  1. Basic Index: Create an index on the Projects table to quickly find projects based on their status.
  2. Unique Index Explanation: The Users table has UNIQUE constraints on username and email. What does this mean regarding automatically created indexes on these columns?
  3. Foreign Key Index: Create an index on the Tasks table to efficiently retrieve all tasks belonging to a specific project_id.
  4. Foreign Key Index (Assignment Table): Create an index on the TaskAssignments table to quickly find all assignments (and thus tasks) for a given user_id.
  5. Foreign Key Index (Assignment Table): Create an index on the TaskAssignments table to quickly find all assignees for a given task_id.
  6. Multicolumn Index: Create a multicolumn index on the Tasks table to optimize filtering tasks first by project_id and then by status.
  7. Multicolumn Index (Assignments): Create a multicolumn index on TaskAssignments table to optimize queries finding tasks assigned to a specific user (user_id) ordered by assigned_at timestamp.
  8. Partial Index: Create a partial index on the Tasks table covering the due_date column, but only for tasks whose status is not ‘done’ or ‘blocked’. This helps find upcoming deadlines for active tasks.
  9. Index on Expression: Create an index on the Tasks table based on the UPPER() function applied to the title column to support case-insensitive title searches.
  10. List and Drop Index: Use the pg_indexes system view to list all indexes on the Tasks table. Identify the index created in step 9 (UPPER(title)) and then drop it.
Variant 4: Online Forum Platform

Scenario: You are responsible for the database performance of a growing online forum. Users need to quickly find posts by topic, author, and search content. Moderators also need efficient ways to review reported posts. Indexing is key to maintaining a good user experience.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS Posts;
DROP TABLE IF EXISTS Topics;
DROP TABLE IF EXISTS Users;

-- Create the Users table
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    join_date DATE DEFAULT CURRENT_DATE,
    post_count INT DEFAULT 0
);

-- Create the Topics table
CREATE TABLE Topics (
    topic_id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_by INT REFERENCES Users(user_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the Posts table
CREATE TABLE Posts (
    post_id SERIAL PRIMARY KEY,
    topic_id INT NOT NULL REFERENCES Topics(topic_id),
    user_id INT NOT NULL REFERENCES Users(user_id),
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_reported BOOLEAN DEFAULT FALSE,
    last_edited_at TIMESTAMP NULL
);

-- Insert sample data
INSERT INTO Users (username, email, join_date) VALUES
('forumFan', 'fan@example.com', '2023-01-15'),
('topicMaster', 'master@example.com', '2023-02-20'),
('postGuru', 'guru@example.com', '2023-03-10');

INSERT INTO Topics (name, description, created_by, created_at) VALUES
('Database Performance', 'Discussions about optimizing database queries.', 1, '2023-05-01 10:00:00'),
('Web Development Trends', 'Latest news and trends in web development.', 2, '2023-06-15 14:30:00'),
('General Chat', 'Off-topic discussions.', 1, '2023-01-20 09:00:00');

INSERT INTO Posts (topic_id, user_id, title, content, created_at, is_reported) VALUES
(1, 1, 'Indexing Strategies in PostgreSQL', 'Let us discuss B-Trees, Hash indexes...', '2023-05-02 11:00:00', FALSE),
(1, 2, 'Re: Indexing Strategies', 'Partial indexes are great for specific subsets!', '2023-05-03 09:20:00', FALSE),
(2, 3, 'Rise of Serverless', 'Is serverless the future?', '2023-06-16 10:00:00', FALSE),
(2, 1, 'Re: Rise of Serverless', 'Depends on the use case...', '2023-06-17 15:00:00', TRUE), -- Reported post
(3, 2, 'Favorite weekend hobbies?', 'What do you do on weekends?', '2023-01-21 11:00:00', FALSE),
(1, 3, 'Full-Text Search Tips', 'Using tsvector and tsquery...', '2023-07-01 14:00:00', FALSE);

Tasks:

  1. Basic Index: Create an index on the Users table to speed up lookups based on join_date.
  2. Foreign Key Index: Create an index on the Posts table to efficiently find all posts made by a specific user_id.
  3. Foreign Key Index: Create an index on the Posts table to efficiently retrieve all posts belonging to a specific topic_id.
  4. Multicolumn Index: Create a multicolumn index on the Posts table to optimize viewing posts within a topic_id, ordered by their created_at timestamp. Ensure topic_id is the leading column.
  5. Partial Index: Create a partial index on the Posts table for the created_at column, but only including entries where is_reported is TRUE. This is for the moderator’s review queue.
  6. Index on Expression: Create an index on the Posts table using the LOWER() function on the title column to support fast, case-insensitive searches by post title.
  7. Unique Index Explanation: The Topics table has a UNIQUE constraint on the name column. What does this constraint imply about indexing on that column? Is a separate CREATE INDEX needed for basic lookups on name?
  8. List Indexes: Use the \d Posts meta-command (in psql or pgAdmin’s Query Tool SQL pane) to display all indexes associated with the Posts table.
  9. Reindex Table: Rebuild all indexes on the Posts table, assuming frequent edits and deletions might have occurred.
  10. Drop Index: Remove the expression index created in step 6 (LOWER(title) on Posts).
Variant 5: Flight Booking System

Scenario: You are developing the database backend for a flight booking system. Performance is critical for searching available flights between destinations, checking specific flight details, and managing passenger bookings. Indexes are needed to ensure quick query responses.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS Bookings;
DROP TABLE IF EXISTS Flights;
DROP TABLE IF EXISTS Airlines;
DROP TABLE IF EXISTS Airports;

-- Create the Airports table
CREATE TABLE Airports (
    airport_code CHAR(3) PRIMARY KEY, -- e.g., 'JFK', 'LAX'
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100),
    country VARCHAR(100)
);

-- Create the Airlines table
CREATE TABLE Airlines (
    airline_id SERIAL PRIMARY KEY,
    iata_code VARCHAR(3) UNIQUE NOT NULL, -- e.g., 'AA', 'UA'
    name VARCHAR(100) NOT NULL
);

-- Create the Flights table
CREATE TABLE Flights (
    flight_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) NOT NULL,
    airline_id INT NOT NULL REFERENCES Airlines(airline_id),
    departure_airport CHAR(3) NOT NULL REFERENCES Airports(airport_code),
    arrival_airport CHAR(3) NOT NULL REFERENCES Airports(airport_code),
    departure_time TIMESTAMP WITH TIME ZONE NOT NULL,
    arrival_time TIMESTAMP WITH TIME ZONE NOT NULL,
    base_price NUMERIC(10, 2) CHECK (base_price >= 0),
    total_seats INT,
    seats_booked INT DEFAULT 0
);

-- Create the Bookings table
CREATE TABLE Bookings (
    booking_id SERIAL PRIMARY KEY,
    flight_id INT NOT NULL REFERENCES Flights(flight_id),
    passenger_name VARCHAR(150) NOT NULL,
    seat_number VARCHAR(4), -- e.g., '12A', '30F'
    booking_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'confirmed' -- 'confirmed', 'cancelled'
);

-- Insert sample data
INSERT INTO Airports (airport_code, name, city, country) VALUES
('JFK', 'John F. Kennedy International', 'New York', 'USA'),
('LAX', 'Los Angeles International', 'Los Angeles', 'USA'),
('LHR', 'London Heathrow', 'London', 'UK'),
('CDG', 'Charles de Gaulle', 'Paris', 'France');

INSERT INTO Airlines (iata_code, name) VALUES
('AA', 'American Airlines'),
('BA', 'British Airways'),
('DL', 'Delta Air Lines');

INSERT INTO Flights (flight_number, airline_id, departure_airport, arrival_airport, departure_time, arrival_time, base_price, total_seats) VALUES
('AA101', 1, 'JFK', 'LAX', '2024-07-15 09:00:00-04', '2024-07-15 12:00:00-07', 350.00, 180),
('BA286', 2, 'LHR', 'LAX', '2024-07-15 11:30:00+01', '2024-07-15 14:45:00-07', 680.00, 220),
('DL405', 3, 'JFK', 'CDG', '2024-07-16 18:00:00-04', '2024-07-17 07:30:00+02', 550.00, 200),
('AA202', 1, 'LAX', 'JFK', '2024-07-18 14:00:00-07', '2024-07-18 22:30:00-04', 360.00, 180);

INSERT INTO Bookings (flight_id, passenger_name, seat_number, status) VALUES
(1, 'Alice Wonderland', '15A', 'confirmed'),
(1, 'Bob The Builder', '15B', 'confirmed'),
(2, 'Charlie Chaplin', '22F', 'confirmed'),
(3, 'Diana Prince', '10C', 'cancelled'),
(4, 'Edward Scissorhands', '5A', 'confirmed');

-- Update seats_booked based on insertions
UPDATE Flights SET seats_booked = 2 WHERE flight_id = 1;
UPDATE Flights SET seats_booked = 1 WHERE flight_id = 2;
UPDATE Flights SET seats_booked = 0 WHERE flight_id = 3; -- Cancelled booking doesn't count
UPDATE Flights SET seats_booked = 1 WHERE flight_id = 4;

Tasks:

  1. Basic Index: Create an index on the Airports table to quickly search for airports located in a specific city.
  2. Basic Index: Create an index on the Flights table to quickly find flights using their flight_number.
  3. Multicolumn Index (Core Search): Create a crucial multicolumn index on the Flights table to optimize the most common search: finding flights based on departure_airport, arrival_airport, and departure_time. Ensure columns are in this order.
  4. Foreign Key Index: Create an index on the Flights table to efficiently find all flights operated by a specific airline_id.
  5. Foreign Key Index: Create an index on the Bookings table to quickly retrieve all bookings associated with a specific flight_id.
  6. Partial Index (Availability): Create a partial index on the Flights table covering departure_time, but only for flights where seats_booked < total_seats. This optimizes searches for flights that still have available seats.
  7. Index on Expression (Date Search): Create an index on the Flights table based on the date part of the departure_time column (use CAST(departure_time AS DATE) or DATE(departure_time)). This will speed up queries searching for all flights departing on a specific date, regardless of time.
  8. Unique Index (Seat Assignment): Create a unique index on the Bookings table covering flight_id and seat_number to prevent assigning the same seat on the same flight to multiple passengers. Allow NULLs in seat_number if seat assignment can be optional initially (though the current schema implies it’s usually present).
  9. List Indexes: Use the pg_indexes system catalog view to list all indexes currently defined for the Flights table, showing their names and definitions.
  10. Drop Index: Remove the basic index created in step 2 on the flight_number column of the Flights table.
Variant 6: University Course Enrollment System

Scenario: You are managing the database for a university’s course enrollment system. Students, faculty, and administrators need fast access to course information, student schedules, class rosters, and departmental listings. Effective indexing is vital for the system’s responsiveness, especially during peak registration periods.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS Enrollments;
DROP TABLE IF EXISTS Courses;
DROP TABLE IF EXISTS Departments;
DROP TABLE IF EXISTS Students;

-- Create the Students table
CREATE TABLE Students (
    student_id SERIAL PRIMARY KEY,
    student_uid VARCHAR(10) UNIQUE NOT NULL, -- University ID, e.g., 'u1234567'
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    enrollment_year INT,
    major VARCHAR(100)
);

-- Create the Departments table
CREATE TABLE Departments (
    dept_id SERIAL PRIMARY KEY,
    dept_code VARCHAR(10) UNIQUE NOT NULL, -- e.g., 'CSCI', 'MATH'
    name VARCHAR(150) NOT NULL
);

-- Create the Courses table
CREATE TABLE Courses (
    course_id SERIAL PRIMARY KEY,
    course_code VARCHAR(10) NOT NULL, -- e.g., '101', '315W'
    title VARCHAR(200) NOT NULL,
    credits INT CHECK (credits > 0),
    dept_id INT NOT NULL REFERENCES Departments(dept_id),
    UNIQUE (dept_id, course_code) -- Course code unique within a department
);

-- Create the Enrollments table
CREATE TABLE Enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL REFERENCES Students(student_id),
    course_id INT NOT NULL REFERENCES Courses(course_id),
    semester VARCHAR(20) NOT NULL, -- e.g., 'Fall 2024', 'Spring 2025'
    registration_date DATE DEFAULT CURRENT_DATE,
    grade CHAR(2) NULL, -- e.g., 'A', 'B+', 'P', 'F', NULL if in progress
    UNIQUE (student_id, course_id, semester) -- Student cannot enroll in the same course multiple times in one semester
);

-- Insert sample data
INSERT INTO Students (student_uid, first_name, last_name, enrollment_year, major) VALUES
('u7654321', 'Hermione', 'Granger', 2021, 'Magical Law'),
('u1122334', 'Ron', 'Weasley', 2021, 'Defense Against Dark Arts'),
('u9876543', 'Harry', 'Potter', 2021, 'Auror Training');

INSERT INTO Departments (dept_code, name) VALUES
('CSCI', 'Computer Science'),
('MATH', 'Mathematics'),
('PHYS', 'Physics');

INSERT INTO Courses (course_code, title, credits, dept_id) VALUES
('101', 'Intro to Programming', 4, 1),
('210', 'Data Structures', 4, 1),
('151', 'Calculus I', 4, 2),
('201', 'Classical Mechanics', 3, 3);

INSERT INTO Enrollments (student_id, course_id, semester, grade) VALUES
(1, 1, 'Fall 2023', 'A'),
(1, 3, 'Fall 2023', 'A-'),
(2, 1, 'Fall 2023', 'C+'),
(3, 1, 'Spring 2024', NULL), -- In progress
(1, 2, 'Spring 2024', NULL), -- In progress
(2, 3, 'Spring 2024', NULL); -- In progress

Tasks:

  1. Basic Index: Create an index on the Students table to allow for efficient searching by last_name.
  2. Foreign Key Index: Create an index on the Courses table to quickly find all courses offered by a specific dept_id.
  3. Foreign Key Index (Enrollments): Create an index on the Enrollments table to efficiently retrieve all courses a particular student_id is enrolled in or has taken.
  4. Foreign Key Index (Enrollments): Create an index on the Enrollments table to quickly generate a class roster (list of students) for a specific course_id.
  5. Multicolumn Index (Student Schedule): Create a multicolumn index on the Enrollments table to optimize fetching a specific student’s schedule for a given semester. The index should cover student_id and semester, in that order.
  6. Multicolumn Index (Course Roster): Create a multicolumn index on the Enrollments table to optimize fetching the roster for a specific course_id within a particular semester. The index should cover course_id and semester, in that order.
  7. Partial Index (In Progress): Create a partial index on the Enrollments table covering the course_id column, but only for rows where the grade is NULL. This helps quickly find students currently enrolled in a course (ungraded).
  8. Index on Expression (Case-Insensitive Major): Create an index on the Students table based on the LOWER() function applied to the major column to support case-insensitive searches for students by major.
  9. List Indexes: Use the \d Enrollments meta-command to list all indexes currently associated with the Enrollments table. Verify the indexes created in previous steps are present.
  10. Reindex and Drop: Rebuild all indexes on the Students table. Afterwards, drop the index created in step 1 (last_name on Students).
Variant 7: Healthcare Appointment System

Scenario: You are responsible for managing the database of a clinic’s appointment scheduling system. Fast retrieval of patient information, available slots, and appointment history is critical for the clinic’s operations. You need to add indexes to optimize common lookup and filtering operations.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS Appointments;
DROP TABLE IF EXISTS Patients;
DROP TABLE IF EXISTS Doctors;

-- Create the Doctors table
CREATE TABLE Doctors (
    doctor_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    specialty VARCHAR(100),
    license_number VARCHAR(50) UNIQUE NOT NULL
);

-- Create the Patients table
CREATE TABLE Patients (
    patient_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    contact_phone VARCHAR(20) UNIQUE, -- Can be NULL but must be unique if present
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Create the Appointments table
CREATE TABLE Appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INT NOT NULL REFERENCES Patients(patient_id),
    doctor_id INT NOT NULL REFERENCES Doctors(doctor_id),
    appointment_datetime TIMESTAMP NOT NULL, -- Date and time of the appointment
    duration_minutes INT DEFAULT 30 CHECK (duration_minutes > 0),
    status VARCHAR(20) DEFAULT 'scheduled', -- e.g., 'scheduled', 'completed', 'cancelled', 'no_show'
    reason TEXT -- Reason for the visit
);

-- Insert sample data
INSERT INTO Doctors (first_name, last_name, specialty, license_number) VALUES
('Alice', 'Chen', 'Cardiology', 'LIC1001'),
('Bob', 'Miller', 'Pediatrics', 'LIC1002'),
('Carol', 'Davis', 'General Practice', 'LIC1003');

INSERT INTO Patients (first_name, last_name, date_of_birth, contact_phone, registration_date) VALUES
('Mark', 'Johnson', '1985-06-15', '555-1234', '2023-01-10'),
('Sarah', 'Williams', '1992-09-22', '555-5678', '2023-02-20'),
('David', 'Brown', '1978-03-01', NULL, '2023-05-15'),
('Emily', 'Smith', '2018-11-12', '555-9900', '2023-06-01');

INSERT INTO Appointments (patient_id, doctor_id, appointment_datetime, duration_minutes, status, reason) VALUES
(1, 1, '2024-05-10 10:00:00', 45, 'completed', 'Annual Checkup'),
(2, 3, '2024-05-10 11:00:00', 30, 'scheduled', 'Consultation'),
(1, 1, '2024-05-17 14:00:00', 45, 'scheduled', 'Follow-up'),
(4, 2, '2024-05-18 09:30:00', 30, 'scheduled', 'Vaccination'),
(2, 3, '2024-04-25 15:00:00', 30, 'cancelled', 'Feeling better'),
(3, 3, '2024-05-20 16:00:00', 30, 'scheduled', 'New Patient Visit');

Tasks:

  1. Basic Index: Create an index on the Patients table to speed up searches based on the patient’s last_name.
  2. Unique Index: Create a unique index on the Patients table for the contact_phone column to enforce uniqueness for non-NULL phone numbers.
  3. Foreign Key Index: Create an index on the Appointments table to efficiently retrieve all appointments for a specific patient_id.
  4. Foreign Key Index: Create an index on the Appointments table to efficiently find all appointments scheduled with a specific doctor_id.
  5. Multicolumn Index: Create a multicolumn index on the Appointments table to optimize queries that filter by doctor_id and then by appointment_datetime.
  6. Index on Expression: Create an index on the Patients table based on the YEAR extracted from the date_of_birth to optimize queries searching for patients born in a specific year. CREATE INDEX idx_patients_birth_year ON Patients (EXTRACT(YEAR FROM date_of_birth));
  7. Partial Index: Create a partial index on the Appointments table for the appointment_datetime column, but only include appointments with a status of ‘scheduled’. This helps optimize queries looking for upcoming, active appointments.
  8. Reindex Table: Rebuild all indexes currently existing on the Appointments table.
  9. List Indexes: Use the \d Appointments meta-command (in psql or pgAdmin’s Query Tool SQL pane) to view the indexes on the Appointments table.
  10. Drop Index: Remove the index created in step 6 (idx_patients_birth_year).
Variant 8: Warehouse Inventory System

Scenario: You manage the database for a warehouse inventory system. The system needs to track products, their locations within different warehouses, and current stock levels. Efficient querying for product availability, stock levels, and warehouse contents is crucial.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS InventoryLevels;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Warehouses;

-- Create the Warehouses table
CREATE TABLE Warehouses (
    warehouse_id SERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL, -- e.g., 'NYC-01', 'LAX-02'
    city VARCHAR(100) NOT NULL,
    capacity_sqft INT,
    is_refrigerated BOOLEAN DEFAULT FALSE
);

-- Create the Products table
CREATE TABLE Products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL, -- Stock Keeping Unit
    name VARCHAR(200) NOT NULL,
    category VARCHAR(100),
    unit_cost NUMERIC(10, 2),
    supplier_info JSONB -- Store supplier name, contact, etc.
);

-- Create the InventoryLevels table (linking Products and Warehouses)
CREATE TABLE InventoryLevels (
    inventory_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES Products(product_id),
    warehouse_id INT NOT NULL REFERENCES Warehouses(warehouse_id),
    quantity INT NOT NULL CHECK (quantity >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reorder_level INT DEFAULT 10,
    UNIQUE (product_id, warehouse_id) -- A product exists only once per warehouse
);

-- Insert sample data
INSERT INTO Warehouses (code, city, capacity_sqft, is_refrigerated) VALUES
('NYC-01', 'New York', 50000, false),
('LAX-02', 'Los Angeles', 75000, true),
('CHI-01', 'Chicago', 60000, false);

INSERT INTO Products (sku, name, category, unit_cost, supplier_info) VALUES
('ELE-TV-55', '55-inch 4K TV', 'Electronics', 350.00, '{"name": "PanelCorp", "contact": "sales@panelcorp.com"}'),
('FUR-DSK-WD', 'Wooden Desk', 'Furniture', 120.50, '{"name": "WoodWorks Ltd.", "contact": "orders@woodworksltd.net"}'),
('GRC-APL-FU', 'Fuji Apples (Box)', 'Grocery', 15.75, '{"name": "FreshFarms Inc.", "region": "WA"}'),
('ELE-CAM-DL', 'Digital SLR Camera', 'Electronics', 899.00, '{"name": "LensMasters", "contact": "info@lensmasters.com"}');

INSERT INTO InventoryLevels (product_id, warehouse_id, quantity, reorder_level) VALUES
(1, 1, 50, 10), -- TV in NYC
(1, 3, 75, 15), -- TV in Chicago
(2, 1, 120, 20), -- Desk in NYC
(3, 2, 200, 50), -- Apples in LA (requires refrigeration)
(4, 1, 25, 5),  -- Camera in NYC
(4, 3, 30, 5);  -- Camera in Chicago

Tasks:

  1. Basic Index: Create an index on the Products table to speed up searches based on the product name.
  2. Basic Index: Create an index on the Warehouses table to enable faster lookups by city.
  3. Foreign Key Index: Create an index on the InventoryLevels table to efficiently query stock levels for a specific product_id across all warehouses.
  4. Foreign Key Index: Create an index on the InventoryLevels table to quickly find all products stored within a specific warehouse_id.
  5. Multicolumn Index: The InventoryLevels table already has a unique constraint (and thus an underlying index) on (product_id, warehouse_id). Explain why creating another index ON InventoryLevels (warehouse_id, product_id) might still be beneficial for certain queries.
  6. Partial Index: Create a partial index on the InventoryLevels table for the quantity column, but only include rows where quantity is less than the reorder_level. This helps efficiently identify items needing restocking. CREATE INDEX idx_inventory_low_stock ON InventoryLevels (product_id, warehouse_id) WHERE quantity < reorder_level;
  7. Index on Expression: Create an index on the Products table based on the lowercase version of the category column to facilitate case-insensitive category searches.
  8. GIN Index (JSONB): Create a GIN index on the supplier_info column in the Products table to efficiently query based on keys or values within the JSONB data (e.g., finding products from a specific supplier name). CREATE INDEX idx_products_supplier_info_gin ON Products USING gin (supplier_info);
  9. List Indexes: Use the pg_indexes system catalog view to list all indexes associated with the InventoryLevels table.
  10. Drop Index: Remove the expression index created in step 7 (LOWER(category) on Products).
Variant 9: Blog Platform

Scenario: You are the database administrator for a blogging platform. Users need to be able to quickly find posts by author, tag, or date, and view comments associated with posts. You must add indexes to support these features efficiently.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS PostTags;
DROP TABLE IF EXISTS Comments;
DROP TABLE IF EXISTS Tags;
DROP TABLE IF EXISTS Posts;
DROP TABLE IF EXISTS Users;

-- Create the Users table
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    join_date DATE DEFAULT CURRENT_DATE
);

-- Create the Posts table
CREATE TABLE Posts (
    post_id SERIAL PRIMARY KEY,
    author_id INT NOT NULL REFERENCES Users(user_id),
    title VARCHAR(255) NOT NULL,
    content TEXT,
    published_at TIMESTAMP, -- NULL if draft
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(15) DEFAULT 'draft' -- e.g., 'draft', 'published', 'archived'
);

-- Create the Comments table
CREATE TABLE Comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INT NOT NULL REFERENCES Posts(post_id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES Users(user_id), -- Comment author
    comment_text TEXT NOT NULL,
    commented_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the Tags table
CREATE TABLE Tags (
    tag_id SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) UNIQUE NOT NULL
);

-- Create the PostTags table (Many-to-Many link)
CREATE TABLE PostTags (
    post_id INT NOT NULL REFERENCES Posts(post_id) ON DELETE CASCADE,
    tag_id INT NOT NULL REFERENCES Tags(tag_id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id) -- Ensures a tag is applied only once per post
);

-- Insert sample data
INSERT INTO Users (username, email) VALUES
('blogger_a', 'a@example.com'),
('reader_b', 'b@example.com'),
('editor_c', 'c@example.com');

INSERT INTO Posts (author_id, title, content, published_at, status) VALUES
(1, 'My First Post', 'This is the content of my first post.', '2024-01-15 10:00:00', 'published'),
(1, 'Advanced SQL Tricks', 'Let us talk about window functions...', '2024-02-20 11:30:00', 'published'),
(3, 'Editing Guidelines', 'Please follow these guidelines...', '2024-03-01 09:00:00', 'published'),
(1, 'Draft Post Idea', 'Something about databases...', NULL, 'draft');

INSERT INTO Comments (post_id, user_id, comment_text) VALUES
(1, 2, 'Great first post!'),
(2, 2, 'Very useful SQL tips, thanks!'),
(2, 3, 'Well written, approved.'),
(1, 1, 'Thanks for the feedback!'); -- Author replying

INSERT INTO Tags (tag_name) VALUES
('introduction'), ('sql'), ('databases'), ('guide'), ('meta');

INSERT INTO PostTags (post_id, tag_id) VALUES
(1, 1), -- Post 1 tagged 'introduction'
(2, 2), -- Post 2 tagged 'sql'
(2, 3), -- Post 2 tagged 'databases'
(3, 4), -- Post 3 tagged 'guide'
(3, 5); -- Post 3 tagged 'meta'

Tasks:

  1. Basic Index: Create an index on the Posts table to quickly find all posts written by a specific author_id.
  2. Basic Index: Create an index on the Comments table to quickly retrieve all comments left by a specific user_id.
  3. Foreign Key Index: Create an index on the Comments table to efficiently fetch all comments belonging to a specific post_id.
  4. Foreign Key Index (M2M): Create an index on the PostTags table to efficiently find all tags associated with a given post_id. (Note: The primary key already covers (post_id, tag_id), but an index specifically on post_id might be used differently by the planner).
  5. Foreign Key Index (M2M): Create an index on the PostTags table to efficiently find all posts associated with a given tag_id.
  6. Multicolumn Index: Create a multicolumn index on the Posts table to optimize queries searching for posts by a specific author_id that also filter by status (e.g., find all ‘published’ posts by author X).
  7. Index on Expression: Create an index on the Posts table using the DATE() function on the published_at column to optimize queries filtering posts published on a specific date (ignoring the time component). CREATE INDEX idx_posts_publish_date ON Posts (DATE(published_at));
  8. Partial Index: Create a partial index on the Posts table for the published_at column, but only include rows where the status is ‘published’. This is useful for efficiently querying or sorting only the published posts by date.
  9. List Indexes: Use the \d PostTags meta-command to view the indexes (including the primary key index) on the PostTags table.
  10. Drop Index: Remove the index created in step 7 (idx_posts_publish_date).
Variant 10: Restaurant Reservation System

Scenario: You are managing the database for an online restaurant reservation platform. Efficiently finding restaurants, checking table availability, and managing customer reservations are key functionalities. You need to add indexes to optimize query performance.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS Reservations;
DROP TABLE IF EXISTS RestaurantTables;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Restaurants;

-- Create the Restaurants table
CREATE TABLE Restaurants (
    restaurant_id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    cuisine_type VARCHAR(50),
    city VARCHAR(100),
    rating NUMERIC(3, 2) CHECK (rating >= 0 AND rating <= 5), -- e.g., 4.50
    phone_number VARCHAR(20) UNIQUE
);

-- Create the RestaurantTables table
CREATE TABLE RestaurantTables (
    table_id SERIAL PRIMARY KEY,
    restaurant_id INT NOT NULL REFERENCES Restaurants(restaurant_id),
    table_number VARCHAR(10), -- e.g., 'T1', 'P2' (Patio 2)
    capacity INT NOT NULL CHECK (capacity > 0),
    is_available BOOLEAN DEFAULT TRUE
);

-- Create the Customers table
CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(20)
);

-- Create the Reservations table
CREATE TABLE Reservations (
    reservation_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES Customers(customer_id),
    table_id INT NOT NULL REFERENCES RestaurantTables(table_id),
    reservation_time TIMESTAMP NOT NULL,
    party_size INT NOT NULL CHECK (party_size > 0),
    status VARCHAR(20) DEFAULT 'confirmed', -- 'confirmed', 'cancelled', 'completed', 'no-show'
    special_requests TEXT
);

-- Insert sample data
INSERT INTO Restaurants (name, cuisine_type, city, rating, phone_number) VALUES
('The Gourmet Place', 'French', 'Metropolis', 4.80, '555-0101'),
('Pasta Paradise', 'Italian', 'Metropolis', 4.50, '555-0102'),
('Sushi Central', 'Japanese', 'Gotham', 4.70, '555-0201'),
('Taco Town', 'Mexican', 'Metropolis', 4.20, '555-0103');

INSERT INTO RestaurantTables (restaurant_id, table_number, capacity, is_available) VALUES
(1, 'T1', 2, TRUE), (1, 'T2', 4, TRUE), (1, 'T3', 4, FALSE),
(2, 'P1', 4, TRUE), (2, 'P2', 6, TRUE),
(3, 'S1', 2, TRUE), (3, 'S2', 2, TRUE), (3, 'B1', 8, TRUE), -- Sushi Bar
(4, 'M1', 4, TRUE);

INSERT INTO Customers (first_name, last_name, email, phone) VALUES
('Clark', 'Kent', 'clark.k@dailyplanet.com', '555-1111'),
('Lois', 'Lane', 'lois.l@dailyplanet.com', '555-2222'),
('Bruce', 'Wayne', 'bruce@waynecorp.com', '555-3333');

INSERT INTO Reservations (customer_id, table_id, reservation_time, party_size, status) VALUES
(1, 2, '2024-06-15 19:00:00', 2, 'confirmed'), -- Clark at Gourmet Place T2
(2, 4, '2024-06-16 20:00:00', 4, 'confirmed'), -- Lois at Pasta Paradise P1
(3, 8, '2024-06-17 18:30:00', 6, 'confirmed'), -- Bruce at Sushi Central B1
(1, 5, '2024-06-18 19:30:00', 5, 'cancelled'); -- Clark at Pasta Paradise P2 (cancelled)

Tasks:

  1. Basic Index: Create an index on the Restaurants table to speed up searches based on the restaurant name.
  2. Basic Index: Create an index on the Customers table to quickly find customers by their last_name.
  3. Foreign Key Index: Create an index on the RestaurantTables table to efficiently find all tables belonging to a specific restaurant_id.
  4. Foreign Key Index: Create an index on the Reservations table to quickly look up reservations made by a specific customer_id.
  5. Foreign Key Index: Create an index on the Reservations table to quickly find all reservations associated with a specific table_id.
  6. Multicolumn Index: Create a multicolumn index on the Restaurants table to optimize searches filtering by city first and then by cuisine_type.
  7. Multicolumn Index: Create a multicolumn index on the Reservations table to efficiently query reservations based on reservation_time and status. Place reservation_time first in the index definition.
  8. Partial Index: Create a partial index on the Reservations table for the reservation_time column, but only include reservations with a status of ‘confirmed’. This helps efficiently find active, upcoming reservations.
  9. Index on Expression: Create an index on the Customers table using the LOWER() function on the email column to support case-insensitive email lookups (even though there’s a unique constraint, this supports queries using LOWER()).
  10. List and Drop: Use the \d Reservations meta-command to list indexes on the Reservations table. Then, drop the index created in step 8 (the partial index on reservation_time for ‘confirmed’ status).
Variant 11: Event Ticketing System

Scenario: You are developing the database for an online event ticketing platform. Users need to quickly search for events, view ticket availability and pricing, and manage their purchases. Indexing is crucial for handling high traffic during popular event sales.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS Purchases;
DROP TABLE IF EXISTS TicketTypes;
DROP TABLE IF EXISTS Events;
DROP TABLE IF EXISTS Venues;
DROP TABLE IF EXISTS Users; -- Assuming a generic Users table

-- Create the Venues table
CREATE TABLE Venues (
    venue_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    city VARCHAR(100),
    capacity INT
);

-- Create the Events table
CREATE TABLE Events (
    event_id SERIAL PRIMARY KEY,
    venue_id INT REFERENCES Venues(venue_id),
    name VARCHAR(255) NOT NULL,
    category VARCHAR(50), -- e.g., 'Music', 'Sports', 'Theater', 'Conference'
    event_start_time TIMESTAMP NOT NULL,
    event_end_time TIMESTAMP,
    status VARCHAR(20) DEFAULT 'scheduled' -- 'scheduled', 'cancelled', 'postponed', 'completed'
);

-- Create the TicketTypes table (defines price levels/sections for an event)
CREATE TABLE TicketTypes (
    ticket_type_id SERIAL PRIMARY KEY,
    event_id INT NOT NULL REFERENCES Events(event_id),
    description VARCHAR(100), -- e.g., 'General Admission', 'VIP', 'Balcony Section A'
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    total_quantity INT NOT NULL CHECK (total_quantity >= 0), -- Total tickets of this type available
    available_quantity INT CHECK (available_quantity >= 0 AND available_quantity <= total_quantity)
);

-- Create the Users table (simplified)
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(150) UNIQUE NOT NULL,
    name VARCHAR(150)
);

-- Create the Purchases table
CREATE TABLE Purchases (
    purchase_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES Users(user_id),
    ticket_type_id INT NOT NULL REFERENCES TicketTypes(ticket_type_id),
    quantity INT NOT NULL CHECK (quantity > 0),
    purchase_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_cost NUMERIC(12, 2),
    confirmation_code VARCHAR(32) UNIQUE NOT NULL -- Unique code for this purchase transaction
);

-- Insert sample data
INSERT INTO Venues (name, city, capacity) VALUES
('Arena Central', 'Metropolis', 15000),
('Grand Theater', 'Gotham', 2500),
('Open Air Park', 'Metropolis', 30000);

INSERT INTO Events (venue_id, name, category, event_start_time, status) VALUES
(1, 'Rock Legends Concert', 'Music', '2024-08-15 20:00:00', 'scheduled'),
(2, 'Shakespeare''s Hamlet', 'Theater', '2024-09-10 19:30:00', 'scheduled'),
(1, 'Tech Conference 2024', 'Conference', '2024-10-01 09:00:00', 'scheduled'),
(3, 'Summer Music Festival', 'Music', '2024-07-20 12:00:00', 'completed');

INSERT INTO TicketTypes (event_id, description, price, total_quantity, available_quantity) VALUES
(1, 'Floor Standing', 75.00, 5000, 3500), (1, 'Lower Bowl Seat', 120.00, 8000, 6000), (1, 'VIP Box', 300.00, 100, 20),
(2, 'Orchestra', 90.00, 500, 450), (2, 'Mezzanine', 65.00, 1000, 800),
(3, 'Standard Pass', 499.00, 2000, 1500), (3, 'Workshop Addon', 150.00, 500, 300);

INSERT INTO Users (email, name) VALUES
('fan1@email.com', 'Alex Fan'),
('theater_lover@email.com', 'Bella Critic'),
('techguru@email.com', 'Charlie Dev');

INSERT INTO Purchases (user_id, ticket_type_id, quantity, total_cost, confirmation_code) VALUES
(1, 1, 2, 150.00, 'CONF-ROCK-12345ABC'), -- Alex buys 2 floor tickets for Rock Legends
(2, 4, 1, 90.00, 'CONF-HAMLET-67890DEF'), -- Bella buys 1 orchestra ticket for Hamlet
(1, 2, 4, 480.00, 'CONF-ROCK-11223XYZ'), -- Alex buys 4 lower bowl tickets for Rock Legends
(3, 6, 1, 499.00, 'CONF-TECH-44556UVW'); -- Charlie buys 1 standard pass for Tech Conference

Tasks:

  1. Basic Index: Create an index on the Events table to quickly search for events by name.
  2. Basic Index: Create an index on the Venues table to optimize searches by city.
  3. Foreign Key Index: Create an index on the Events table to efficiently find all events happening at a specific venue_id.
  4. Foreign Key Index: Create an index on the TicketTypes table to quickly retrieve all ticket types associated with a particular event_id.
  5. Foreign Key Index: Create an index on the Purchases table to rapidly find all purchases made by a specific user_id.
  6. Multicolumn Index: Create a multicolumn index on the Events table to optimize queries filtering first by category and then by event_start_time.
  7. Multicolumn Index: Create a multicolumn index on the TicketTypes table to support efficient queries filtering by event_id and sorting/filtering by price.
  8. Partial Index: Create a partial index on the Events table for the event_start_time column, but only include events whose status is ‘scheduled’ or ‘postponed’. This helps in quickly finding upcoming or rescheduled events.
  9. Reindex Table: Rebuild all indexes on the Purchases table. This might be done periodically if purchase data changes frequently (e.g., refunds causing deletes/updates).
  10. List and Drop: Use the pg_indexes system view to list all indexes on the Events table. Then, drop the index created in step 8 (the partial index on event_start_time).
Variant 12: Job Board Platform

Scenario: You are responsible for the database behind a job board website. Fast searches for jobs based on location, category, and keywords are essential, as is efficiently retrieving applications for specific jobs or candidates. Indexing is vital for performance.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS Applications;
DROP TABLE IF EXISTS Jobs;
DROP TABLE IF EXISTS Companies;
DROP TABLE IF EXISTS Candidates;

-- Create the Companies table
CREATE TABLE Companies (
    company_id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL UNIQUE,
    industry VARCHAR(100),
    headquarters_location VARCHAR(150)
);

-- Create the Jobs table
CREATE TABLE Jobs (
    job_id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES Companies(company_id),
    title VARCHAR(200) NOT NULL,
    job_description TEXT,
    category VARCHAR(100), -- e.g., 'Engineering', 'Marketing', 'Sales', 'Design'
    location VARCHAR(150), -- Can be 'Remote' or a city/state
    salary_range VARCHAR(50), -- e.g., '$80k-$100k', 'Competitive'
    date_posted DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT TRUE
);

-- Create the Candidates table
CREATE TABLE Candidates (
    candidate_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(20),
    primary_skill VARCHAR(100), -- e.g., 'Python', 'Project Management', 'Graphic Design'
    resume_url VARCHAR(255) -- Link to stored resume file
);

-- Create the Applications table
CREATE TABLE Applications (
    application_id SERIAL PRIMARY KEY,
    job_id INT NOT NULL REFERENCES Jobs(job_id),
    candidate_id INT NOT NULL REFERENCES Candidates(candidate_id),
    application_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(30) DEFAULT 'submitted', -- 'submitted', 'viewed', 'interviewing', 'offered', 'rejected', 'withdrawn'
    cover_letter TEXT,
    UNIQUE (job_id, candidate_id) -- Prevent duplicate applications
);

-- Insert sample data
INSERT INTO Companies (name, industry, headquarters_location) VALUES
('Innovate Solutions', 'Technology', 'San Francisco, CA'),
('Global Marketing Inc.', 'Marketing', 'New York, NY'),
('BuildWell Construction', 'Construction', 'Austin, TX');

INSERT INTO Jobs (company_id, title, category, location, salary_range, is_active) VALUES
(1, 'Senior Software Engineer', 'Engineering', 'Remote', '$120k-$160k', TRUE),
(1, 'Backend Developer', 'Engineering', 'San Francisco, CA', '$100k-$130k', TRUE),
(2, 'Digital Marketing Manager', 'Marketing', 'New York, NY', '$90k-$110k', TRUE),
(3, 'Project Manager', 'Construction', 'Austin, TX', '$85k-$105k', TRUE),
(1, 'UI/UX Designer', 'Design', 'Remote', null, FALSE); -- Inactive job

INSERT INTO Candidates (first_name, last_name, email, primary_skill) VALUES
('Sarah', 'Chen', 'sarah.c@email.com', 'Python'),
('Michael', 'Bolton', 'm.bolton@email.com', 'JavaScript'),
('Priya', 'Sharma', 'priya.s@email.com', 'Digital Marketing'),
('David', 'Lee', 'david.lee@email.com', 'Project Management');

INSERT INTO Applications (job_id, candidate_id, status) VALUES
(1, 1, 'submitted'), -- Sarah applied for Senior Software Engineer
(2, 2, 'submitted'), -- Michael applied for Backend Developer
(3, 3, 'interviewing'), -- Priya applied for Digital Marketing Manager
(1, 2, 'viewed'), -- Michael also applied for Senior Software Engineer
(4, 4, 'submitted'); -- David applied for Project Manager

Tasks:

  1. Basic Index: Create an index on the Jobs table to speed up searches based on the job title.
  2. Basic Index: Create an index on the Companies table to allow faster lookups by company name.
  3. Foreign Key Index: Create an index on the Jobs table to efficiently find all jobs posted by a specific company_id.
  4. Foreign Key Index: Create an index on the Applications table to quickly retrieve all applications submitted for a specific job_id.
  5. Foreign Key Index: Create an index on the Applications table to quickly find all jobs a specific candidate_id has applied for.
  6. Multicolumn Index: Create a multicolumn index on the Jobs table to optimize queries filtering first by location and then by category.
  7. Multicolumn Index: Create a multicolumn index on the Applications table to efficiently query applications based on job_id and application_date. Place job_id first.
  8. Partial Index: Create a partial index on the Jobs table covering the date_posted column, but only for jobs where is_active is TRUE. This helps efficiently find currently active job listings sorted by posting date.
  9. Index on Expression: Create an index on the Jobs table using the LOWER() function on the title column to support case-insensitive title searches.
  10. List and Drop: Use the pg_indexes system view to list all indexes associated with the Applications table. Then, drop the multicolumn index created in step 7 (on job_id, application_date).
Variant 13: Music Streaming Service

Scenario: You are managing the database for a music streaming service. Users need to be able to quickly search for artists, albums, and tracks, and manage their playlists efficiently. Indexes are required to ensure a smooth user experience.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS PlaylistTracks;
DROP TABLE IF EXISTS UserPlaylists;
DROP TABLE IF EXISTS Tracks;
DROP TABLE IF EXISTS Albums;
DROP TABLE IF EXISTS Artists;

-- Create the Artists table
CREATE TABLE Artists (
    artist_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    genre VARCHAR(100),
    country VARCHAR(100)
);

-- Create the Albums table
CREATE TABLE Albums (
    album_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    artist_id INT NOT NULL REFERENCES Artists(artist_id),
    release_year INT CHECK (release_year > 1800),
    record_label VARCHAR(150)
);

-- Create the Tracks table
CREATE TABLE Tracks (
    track_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    album_id INT NOT NULL REFERENCES Albums(album_id),
    duration_seconds INT CHECK (duration_seconds > 0),
    track_number INT -- Position of the track within the album
);

-- Create the UserPlaylists table
CREATE TABLE UserPlaylists (
    playlist_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL, -- Assuming a Users table exists elsewhere
    name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the PlaylistTracks table (linking UserPlaylists and Tracks)
CREATE TABLE PlaylistTracks (
    playlist_track_id SERIAL PRIMARY KEY,
    playlist_id INT NOT NULL REFERENCES UserPlaylists(playlist_id) ON DELETE CASCADE,
    track_id INT NOT NULL REFERENCES Tracks(track_id) ON DELETE CASCADE,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (playlist_id, track_id) -- A track can only be in a specific playlist once
);

-- Insert sample data
INSERT INTO Artists (name, genre, country) VALUES
('Queen', 'Rock', 'UK'),
('Daft Punk', 'Electronic', 'France'),
('Miles Davis', 'Jazz', 'USA'),
('Taylor Swift', 'Pop', 'USA');

INSERT INTO Albums (title, artist_id, release_year, record_label) VALUES
('A Night at the Opera', 1, 1975, 'EMI'),
('Discovery', 2, 2001, 'Virgin'),
('Kind of Blue', 3, 1959, 'Columbia'),
('1989', 4, 2014, 'Big Machine');

INSERT INTO Tracks (title, album_id, duration_seconds, track_number) VALUES
('Bohemian Rhapsody', 1, 355, 11),
('Love of My Life', 1, 219, 9),
('One More Time', 2, 320, 1),
('Digital Love', 2, 301, 3),
('So What', 3, 562, 1),
('All Blues', 3, 693, 4),
('Shake It Off', 4, 219, 6),
('Blank Space', 4, 231, 2);

INSERT INTO UserPlaylists (user_id, name) VALUES
(101, 'Workout Mix'),
(102, 'Chill Vibes');

INSERT INTO PlaylistTracks (playlist_id, track_id) VALUES
(1, 1), (1, 3), (1, 7),
(2, 2), (2, 4), (2, 5), (2, 6);

Tasks:

  1. Basic Index: Create an index on the Artists table to optimize searching for artists by name.
  2. Basic Index: Create an index on the Albums table to speed up lookups based on the album title.
  3. Foreign Key Index: Create an index on the Albums table’s artist_id column to efficiently find all albums by a specific artist.
  4. Foreign Key Index: Create an index on the Tracks table’s album_id column to quickly retrieve all tracks belonging to a particular album.
  5. Multicolumn Index: Create a multicolumn index on the Tracks table using (album_id, track_number) to optimize fetching tracks for an album in their correct order.
  6. Foreign Key Index (Junction Table): Create an index on the PlaylistTracks table for the playlist_id column to rapidly find all tracks in a specific playlist.
  7. Foreign Key Index (Junction Table): Create an index on the PlaylistTracks table for the track_id column to quickly determine which playlists contain a specific track.
  8. Partial Index: Create a partial index on the Albums table for the release_year column, indexing only albums released after the year 2000 (inclusive). This speeds up queries focused on modern music.
  9. Index on Expression: Create an index on the Tracks table to support case-insensitive searches for track titles using LOWER(title).
  10. List and Drop: Use the pg_indexes system view to list all indexes associated with the Tracks table. Then, drop the case-insensitive title index created in the previous step.
Variant 14: Real Estate Listing Platform

Scenario: You are developing the database for a real estate website. Users need to search for properties based on location, price, size, and features. Agents list properties, and performance is key for search functionality.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS PropertyFeatures;
DROP TABLE IF EXISTS Listings;
DROP TABLE IF EXISTS Properties;
DROP TABLE IF EXISTS Agents;

-- Create the Agents table
CREATE TABLE Agents (
    agent_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    agency VARCHAR(150),
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20)
);

-- Create the Properties table
CREATE TABLE Properties (
    property_id SERIAL PRIMARY KEY,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(50) NOT NULL,
    zip_code VARCHAR(10) NOT NULL,
    property_type VARCHAR(50), -- e.g., 'Single Family', 'Condo', 'Townhouse'
    beds INT CHECK (beds >= 0),
    baths NUMERIC(3,1) CHECK (baths >= 0), -- Allows for half baths (e.g., 2.5)
    square_footage INT CHECK (square_footage > 0),
    year_built INT
);

-- Create the Listings table
CREATE TABLE Listings (
    listing_id SERIAL PRIMARY KEY,
    property_id INT NOT NULL UNIQUE REFERENCES Properties(property_id), -- Ensures a property has only one active listing at a time
    agent_id INT NOT NULL REFERENCES Agents(agent_id),
    list_price NUMERIC(14, 2) NOT NULL CHECK (list_price > 0),
    listing_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'active' -- e.g., 'active', 'pending', 'sold', 'withdrawn'
);

-- Create the PropertyFeatures table (Many-to-Many for features)
CREATE TABLE PropertyFeatures (
    feature_id SERIAL PRIMARY KEY,
    property_id INT NOT NULL REFERENCES Properties(property_id) ON DELETE CASCADE,
    feature_name VARCHAR(100) NOT NULL, -- e.g., 'Pool', 'Garage', 'Fireplace', 'Waterfront'
    UNIQUE (property_id, feature_name)
);

-- Insert sample data
INSERT INTO Agents (first_name, last_name, agency, email, phone) VALUES
('Samantha', 'Green', 'Elite Homes', 'sam.g@elite.com', '555-1111'),
('David', 'Cho', 'Premier Properties', 'd.cho@premier.com', '555-2222'),
('Maria', 'Garcia', 'Elite Homes', 'maria.g@elite.com', '555-3333');

INSERT INTO Properties (address, city, state, zip_code, property_type, beds, baths, square_footage, year_built) VALUES
('123 Oak St', 'Maplewood', 'CA', '90210', 'Single Family', 4, 2.5, 2400, 1995),
('456 Pine Ave', 'Maplewood', 'CA', '90211', 'Condo', 2, 2, 1200, 2010),
('789 Elm Rd', 'Springfield', 'IL', '62704', 'Single Family', 3, 2, 1800, 1980),
('101 Maple Dr', 'Maplewood', 'CA', '90210', 'Townhouse', 3, 2.5, 1600, 2005);

INSERT INTO Listings (property_id, agent_id, list_price, listing_date, status) VALUES
(1, 1, 850000.00, '2024-02-15', 'active'),
(2, 2, 450000.00, '2024-03-01', 'active'),
(3, 1, 320000.00, '2024-01-20', 'pending'),
(4, 3, 510000.00, '2024-03-10', 'active');

INSERT INTO PropertyFeatures (property_id, feature_name) VALUES
(1, 'Pool'), (1, 'Garage'),
(2, 'Garage'), (2, 'Gym Access'),
(3, 'Fireplace'), (3, 'Large Yard'),
(4, 'Garage');

Tasks:

  1. Basic Index: Create a basic index on the Properties table to speed up searches based on the city.
  2. Multicolumn Index: Create a multicolumn index on the Properties table for (state, city) to optimize searches filtered first by state, then by city.
  3. Foreign Key Index: Create an index on the Listings table for the agent_id column to efficiently find all listings managed by a specific agent.
  4. Index for Unique Constraint: An index is automatically created for the UNIQUE constraint on Listings.property_id. However, create an explicit index on this column as well (some DBAs prefer this for clarity or specific performance tuning, though redundant with the constraint’s index). Name it idx_listings_property_explicit.
  5. Multicolumn Index: Create a multicolumn index on the Listings table for (status, list_price) to optimize finding listings by status (e.g., ‘active’) and then filtering or sorting by price.
  6. Basic Index: Create an index on the Properties table for the property_type column.
  7. Partial Index: Create a partial index on the Listings table including only rows where status = 'active'. This index should cover the list_price column to optimize searches for active listings within a price range.
  8. Index on Expression: Create an index on the Properties table using UPPER(zip_code) to facilitate case-insensitive zip code lookups (though zip codes are typically uniform, this demonstrates the concept).
  9. Foreign Key Index (Many-to-Many): Create an index on the PropertyFeatures table for the property_id column to quickly find all features associated with a property.
  10. Reindex and List: Rebuild all indexes on the Properties table. Then, use the \d Listings command (in psql or pgAdmin’s Query Tool SQL pane) to display the structure and indexes of the Listings table.
Variant 15: Bug Tracking System

Scenario: You are the administrator for a software bug tracking system. Developers and QA engineers need to efficiently search, filter, and update bugs based on project, status, priority, and assignee. Indexes are crucial for keeping the system responsive.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS BugComments;
DROP TABLE IF EXISTS Bugs;
DROP TABLE IF EXISTS Projects;
DROP TABLE IF EXISTS Users;

-- Create the Users table (simplified)
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(150),
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Create the Projects table
CREATE TABLE Projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

-- Create the Bugs table
CREATE TABLE Bugs (
    bug_id SERIAL PRIMARY KEY,
    project_id INT NOT NULL REFERENCES Projects(project_id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    reported_by_user_id INT NOT NULL REFERENCES Users(user_id),
    assigned_to_user_id INT REFERENCES Users(user_id), -- Can be NULL if unassigned
    status VARCHAR(20) NOT NULL DEFAULT 'new', -- e.g., 'new', 'assigned', 'in_progress', 'resolved', 'closed', 'reopened'
    priority VARCHAR(15) NOT NULL DEFAULT 'medium', -- e.g., 'low', 'medium', 'high', 'critical'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the BugComments table
CREATE TABLE BugComments (
    comment_id SERIAL PRIMARY KEY,
    bug_id INT NOT NULL REFERENCES Bugs(bug_id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES Users(user_id),
    comment_text TEXT NOT NULL,
    commented_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- Insert sample data
INSERT INTO Users (username, full_name, email) VALUES
('dev1', 'Alice Developer', 'alice.dev@company.com'),
('qa1', 'Bob Tester', 'bob.qa@company.com'),
('mgr1', 'Charlie Manager', 'charlie.mgr@company.com');

INSERT INTO Projects (project_name, description) VALUES
('WebApp V2', 'Main customer-facing web application'),
('MobileApp', 'iOS and Android companion app'),
('Backend API', 'Internal API services');

INSERT INTO Bugs (project_id, title, description, reported_by_user_id, assigned_to_user_id, status, priority, created_at, updated_at) VALUES
(1, 'Login button unresponsive on Firefox', 'Users on Firefox cannot click the login button.', 2, 1, 'assigned', 'high', '2024-03-01 10:00:00', '2024-03-01 11:30:00'),
(1, 'Incorrect currency symbol on checkout page', 'Shows USD instead of EUR for European users.', 2, 1, 'in_progress', 'medium', '2024-03-05 14:15:00', '2024-03-06 09:00:00'),
(2, 'App crashes when opening profile screen', 'Occurs on Android 13 devices.', 2, NULL, 'new', 'critical', '2024-03-10 16:00:00', '2024-03-10 16:00:00'),
(3, 'API endpoint /users returns 500 error', 'The /users endpoint fails intermittently.', 1, 1, 'resolved', 'high', '2024-02-20 09:30:00', '2024-03-08 17:00:00'),
(1, 'Typo in footer text', 'Copyright year is wrong.', 3, NULL, 'new', 'low', '2024-03-12 11:00:00', '2024-03-12 11:00:00');

INSERT INTO BugComments (bug_id, user_id, comment_text, commented_at) VALUES
(1, 1, 'Confirmed. Investigating CSS issue.', '2024-03-01 11:35:00'),
(2, 1, 'Working on fix. Related to localization logic.', '2024-03-06 09:05:00'),
(4, 1, 'Fix deployed. Closing this issue.', '2024-03-08 17:00:00'),
(1, 2, 'Still seeing this issue on Firefox 110.', '2024-03-09 10:00:00');

Tasks:

  1. Basic Index: Create a basic index on the Bugs table for the status column, as filtering by status is very common.
  2. Foreign Key Index: Create an index on the Bugs table for the project_id column to quickly find all bugs associated with a specific project.
  3. Foreign Key Index: Create an index on the Bugs table for the reported_by_user_id column.
  4. Foreign Key Index (Nullable): Create an index on the Bugs table for the assigned_to_user_id column. Note that this column can contain NULLs.
  5. Multicolumn Index: Create a multicolumn index on the Bugs table for (project_id, status) to efficiently filter bugs within a specific project by their status.
  6. Multicolumn Index: Create another multicolumn index on the Bugs table for (assigned_to_user_id, status) to help users quickly find their assigned bugs filtered by status.
  7. Partial Index: Create a partial index on the Bugs table covering the updated_at column, but only for bugs that are not in ‘resolved’ or ‘closed’ status (i.e., active bugs). This optimizes queries looking for recently updated active issues. WHERE status NOT IN ('resolved', 'closed')
  8. Foreign Key Index (Comments): Create an index on the BugComments table for the bug_id column to rapidly retrieve all comments for a specific bug.
  9. Unique Index Explanation: The Users table has UNIQUE constraints on username and email. Explain why you typically don’t need to manually create basic B-tree indexes on these specific columns.
  10. List and Drop Index: Use the pg_indexes system catalog view to list all indexes currently defined for the Bugs table. Identify and then drop the basic index created on the status column in step 1.
Variant 16: Car Rental Agency

Scenario: You are managing the database for a car rental company. Fast lookups for available vehicles, customer rental histories, and vehicle locations are essential for smooth operations at the rental counters and for the online booking system.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS Rentals;
DROP TABLE IF EXISTS Cars;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Locations;

-- Create the Locations table (where cars can be picked up/dropped off)
CREATE TABLE Locations (
    location_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100),
    address VARCHAR(255),
    phone_number VARCHAR(20)
);

-- Create the Customers table
CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    driver_license_id VARCHAR(50) UNIQUE NOT NULL,
    join_date DATE DEFAULT CURRENT_DATE
);

-- Create the Cars table
CREATE TABLE Cars (
    car_id SERIAL PRIMARY KEY,
    license_plate VARCHAR(15) UNIQUE NOT NULL,
    make VARCHAR(50),
    model VARCHAR(50),
    year INT,
    category VARCHAR(30), -- e.g., 'Sedan', 'SUV', 'Truck', 'Luxury'
    status VARCHAR(20) DEFAULT 'available', -- 'available', 'rented', 'maintenance'
    current_location_id INT REFERENCES Locations(location_id)
);

-- Create the Rentals table
CREATE TABLE Rentals (
    rental_id SERIAL PRIMARY KEY,
    car_id INT NOT NULL REFERENCES Cars(car_id),
    customer_id INT NOT NULL REFERENCES Customers(customer_id),
    pickup_location_id INT NOT NULL REFERENCES Locations(location_id),
    dropoff_location_id INT REFERENCES Locations(location_id), -- Can be null initially
    rental_start_date TIMESTAMP NOT NULL,
    rental_end_date TIMESTAMP, -- Date expected or actual return
    total_cost NUMERIC(8, 2),
    actual_return_date TIMESTAMP -- NULL if not returned
);

-- Insert sample data
INSERT INTO Locations (name, city, address) VALUES
('Airport Branch', 'Metropolis', '1 Airport Rd'),
('Downtown Station', 'Metropolis', '123 Main St'),
('Westside Rentals', 'Gotham', '456 West Ave');

INSERT INTO Customers (first_name, last_name, email, phone, driver_license_id) VALUES
('Clark', 'Kent', 'clark.kent@dailyplanet.com', '555-1111', 'DL12345'),
('Lois', 'Lane', 'lois.lane@dailyplanet.com', '555-2222', 'DL67890'),
('Bruce', 'Wayne', 'bruce@waynecorp.com', '555-3333', 'DL54321');

INSERT INTO Cars (license_plate, make, model, year, category, status, current_location_id) VALUES
('SUPRMAN', 'Ford', 'Fusion', 2022, 'Sedan', 'available', 1),
('R3P0RT', 'Toyota', 'Camry', 2023, 'Sedan', 'rented', 1),
('BATM0BL', 'Lamborghini', 'Aventador', 2023, 'Luxury', 'available', 2),
('TRUTH', 'Honda', 'CRV', 2021, 'SUV', 'maintenance', 1),
('JUST1CE', 'Chevrolet', 'Silverado', 2022, 'Truck', 'available', 3);

INSERT INTO Rentals (car_id, customer_id, pickup_location_id, rental_start_date, rental_end_date) VALUES
(2, 2, 1, '2024-03-10 09:00:00', '2024-03-17 09:00:00'), -- Lois rented the Camry
(3, 3, 2, '2024-03-15 14:00:00', '2024-03-18 14:00:00'); -- Bruce rented the Lambo (let's assume it's rented now for the task)

-- Update car status for rented car
UPDATE Cars SET status = 'rented' WHERE car_id = 3;

Tasks:

  1. Basic Index: Create an index on the Cars table to speed up searches based on the car’s make.
  2. Unique Index Check: The Customers table already has UNIQUE constraints on email and driver_license_id. Explain what kind of indexes PostgreSQL likely created automatically for these columns.
  3. Foreign Key Index: Create an index on the Cars table to efficiently find all cars currently located at a specific current_location_id.
  4. Foreign Key Index: Create an index on the Rentals table to quickly retrieve all rentals associated with a specific customer_id.
  5. Foreign Key Index: Create an index on the Rentals table to quickly find the rental history for a specific car_id.
  6. Multicolumn Index: Create a multicolumn index on the Cars table to optimize queries searching for cars of a specific category and year. Ensure category is the leading column.
  7. Partial Index: Create a partial index on the Cars table for the current_location_id column, but only include cars where the status is ‘available’. This helps optimize finding available cars at a specific location.
  8. Index on Expression: Create an index on the Customers table based on the lowercase version of the email column to facilitate case-insensitive email lookups.
  9. List Indexes: Use the \d Rentals meta-command (in psql or pgAdmin’s Query Tool SQL pane) to list all indexes currently associated with the Rentals table.
  10. Drop Index: Remove the index created in step 1 (on Cars.make).
Variant 17: Subscription Service Management

Scenario: You are responsible for the database of a popular online streaming service. Efficiently managing user subscriptions, tracking plan details, and recording billing events are critical for business operations and user account management.

Database Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS BillingEvents;
DROP TABLE IF EXISTS Subscriptions;
DROP TABLE IF EXISTS Plans;
DROP TABLE IF EXISTS ServiceUsers; -- Renamed from Users to avoid conflict with SQL keyword

-- Create the ServiceUsers table
CREATE TABLE ServiceUsers (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(150),
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Create the Plans table
CREATE TABLE Plans (
    plan_id SERIAL PRIMARY KEY,
    plan_name VARCHAR(100) UNIQUE NOT NULL, -- e.g., 'Basic', 'Premium', 'Family'
    monthly_cost NUMERIC(6, 2) NOT NULL CHECK (monthly_cost >= 0),
    features JSONB -- Store features like resolution, number of screens, etc.
);

-- Create the Subscriptions table
CREATE TABLE Subscriptions (
    subscription_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES ServiceUsers(user_id),
    plan_id INT NOT NULL REFERENCES Plans(plan_id),
    start_date DATE NOT NULL,
    end_date DATE, -- NULL for ongoing, set for cancelled/expired
    status VARCHAR(20) DEFAULT 'active', -- 'active', 'cancelled', 'expired', 'pending_payment'
    auto_renew BOOLEAN DEFAULT TRUE
);

-- Create the BillingEvents table
CREATE TABLE BillingEvents (
    event_id SERIAL PRIMARY KEY,
    subscription_id INT NOT NULL REFERENCES Subscriptions(subscription_id),
    event_type VARCHAR(30) NOT NULL, -- 'charge', 'refund', 'payment_failed'
    event_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    amount NUMERIC(8, 2),
    status VARCHAR(20) DEFAULT 'success' -- 'success', 'failed', 'pending'
);

-- Insert sample data
INSERT INTO ServiceUsers (username, email, full_name) VALUES
('neo', 'neo@matrix.net', 'Thomas Anderson'),
('trinity', 'trinity@matrix.net', 'Trinity'),
('morpheus', 'morpheus@nebuchadnezzar.org', 'Morpheus');

INSERT INTO Plans (plan_name, monthly_cost, features) VALUES
('Basic', 9.99, '{"resolution": "SD", "screens": 1}'),
('Standard', 15.99, '{"resolution": "HD", "screens": 2, "downloads": true}'),
('Premium', 19.99, '{"resolution": "UHD", "screens": 4, "downloads": true}');

INSERT INTO Subscriptions (user_id, plan_id, start_date, end_date, status, auto_renew) VALUES
(1, 2, '2023-01-15', NULL, 'active', TRUE), -- Neo, Standard
(2, 3, '2023-02-01', NULL, 'active', TRUE), -- Trinity, Premium
(3, 1, '2023-03-10', '2024-03-09', 'expired', FALSE), -- Morpheus, Basic (Expired)
(1, 3, '2022-11-01', '2022-11-30', 'expired', FALSE); -- Neo's old Premium sub

INSERT INTO BillingEvents (subscription_id, event_type, amount, status) VALUES
(1, 'charge', 15.99, 'success'), -- Neo's recent charge
(2, 'charge', 19.99, 'success'), -- Trinity's recent charge
(3, 'charge', 9.99, 'success'); -- Morpheus' last charge before expiry

Tasks:

  1. Basic Index: Create an index on the Plans table to allow for faster lookups based on plan_name.
  2. Unique Index Consideration: The ServiceUsers table has UNIQUE constraints on username and email. What type of index is automatically created to enforce these, and what is its secondary benefit?
  3. Foreign Key Index: Create an index on the Subscriptions table to quickly find all subscriptions belonging to a specific user_id.
  4. Foreign Key Index: Create an index on the Subscriptions table to quickly find all users subscribed to a specific plan_id.
  5. Foreign Key Index: Create an index on the BillingEvents table to efficiently retrieve all billing events related to a specific subscription_id.
  6. Multicolumn Index: Create a multicolumn index on the BillingEvents table to optimize queries filtering by event_type and then by event_date. event_type should be the leading column.
  7. Partial Index: Create a partial index on the Subscriptions table covering the end_date column, but only for subscriptions where the status is ‘active’ and auto_renew is true. This helps identify active, auto-renewing subscriptions for processing renewals.
  8. Index on Expression: Create an index on the ServiceUsers table using the LOWER() function on the username column to enable efficient case-insensitive username searches.
  9. Reindex Table: Rebuild all indexes on the Subscriptions table to ensure optimal performance after numerous status updates or renewals might have occurred.
  10. List and Drop Index: Use the pg_indexes system view to list all indexes on the BillingEvents table. Identify and then drop the multicolumn index created in step 6 (event_type, event_date).

Submission Instructions