Базы данных | Задания для практического занятия 11

Создание ИНДЕКСА в SQL.

---

Вариант 1: Платформа электронной коммерции

Сценарий: Вы работаете над базой данных для платформы электронной коммерции. Производительность критически важна для пользовательского опыта, особенно при поиске товаров и просмотре истории заказов. Вам необходимо добавить соответствующие индексы для оптимизации часто выполняемых запросов.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS OrderItems;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Products;

-- Создаем таблицу Products
CREATE TABLE Products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL, -- Артикул (единица складского учета)
    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
);

-- Создаем таблицу Orders
CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL, -- Предполагается, что отдельная таблица Customers существует где-то еще
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending', -- например, 'pending' (в ожидании), 'processing' (в обработке), 'shipped' (отправлен), 'delivered' (доставлен), 'cancelled' (отменен)
    total_amount NUMERIC(12, 2)
);

-- Создаем таблицу OrderItems (связывающую Orders и 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 -- Цена на момент заказа
);

-- Вставляем примерные данные
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), -- Ошибка исправлена: итоговая сумма соответствует заказу 1
(2, 2, 1, 1299.00),
(3, 4, 2, 29.99), (3, 1, 1, 49.99), -- Требуются скорректированные количества/цены, если итоговая сумма должна быть ровно 79.98. Предположим, что итоговая сумма заказа была приблизительной.
(4, 5, 1, 450.00),
(5, 3, 1, 89.50);

Задания:

1. Базовый индекс: Создайте индекс для таблицы Products, чтобы ускорить поиск по name товара.
2. Индекс по внешнему ключу: Создайте индекс для таблицы Orders, чтобы быстро находить все заказы, сделанные определенным customer_id.
3. Составной (многоколоночный) индекс: Создайте составной индекс для таблицы Products для оптимизации запросов, которые фильтруют по category, а затем сортируют или фильтруют по price. Убедитесь, что category является ведущим столбцом.
4. Индекс по внешнему ключу (связь многие-ко-многим): Создайте индекс для таблицы OrderItems, чтобы эффективно находить позиции, принадлежащие определенному order_id.
5. Индекс по внешнему ключу (связь многие-ко-многим): Создайте еще один индекс для таблицы OrderItems, чтобы эффективно находить все заказы, включающие определенный product_id.
6. Перестроение индексов таблицы: Перестройте все существующие индексы таблицы Products, чтобы оптимизировать их структуру после возможных изменений данных.
7. Частичный индекс: Создайте частичный индекс для таблицы Orders по столбцу order_date, но включите в него только заказы со status ‘processing’ или ‘pending’. Это поможет оптимизировать запросы для поиска активных, неотправленных заказов.
8. Индекс по выражению: Создайте индекс для таблицы Products на основе значения столбца name в нижнем регистре, чтобы облегчить поиск без учета регистра.
9. Вывод списка индексов: Используйте запрос к системному каталогу (pg_indexes), чтобы вывести список всех индексов, определенных для таблицы OrderItems.
10. Удаление индекса: Удалите индекс по выражению (LOWER(name) для Products), созданный в шаге 8.

---

Вариант 2: Система управления библиотекой

Сценарий: Вы проектируете базу данных для библиотечной системы. Эффективный поиск книг, авторов и отслеживание выданных книг имеет решающее значение для библиотекарей и читателей. Вам необходимо реализовать индексы для оптимизации этих операций.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS BorrowingRecords;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;

-- Создаем таблицу Authors (Авторы)
CREATE TABLE Authors (
    author_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100) NOT NULL,
    birth_year INT
);

-- Создаем таблицу Books (Книги)
CREATE TABLE Books (
    book_id SERIAL PRIMARY KEY,
    isbn VARCHAR(17) UNIQUE NOT NULL, -- Международный стандартный книжный номер
    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)
);

-- Создаем таблицу BorrowingRecords (Записи о выдаче)
CREATE TABLE BorrowingRecords (
    record_id SERIAL PRIMARY KEY,
    book_id INT NOT NULL REFERENCES Books(book_id),
    member_id INT NOT NULL, -- Предполагается, что отдельная таблица Members (Читатели) существует где-то еще
    borrow_date DATE NOT NULL DEFAULT CURRENT_DATE,
    due_date DATE NOT NULL,
    return_date DATE -- NULL, если книга еще не возвращена
);

-- Вставляем пример данных
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), -- Не возвращена
(2, 501, '2024-03-10', '2024-03-24', NULL), -- Не возвращена
(5, 503, '2024-03-12', '2024-03-26', '2024-03-20'),
(1, 502, '2024-03-15', '2024-03-29', NULL); -- Не возвращена

Задания:

1. Базовый индекс: Создайте индекс для таблицы Authors, чтобы ускорить поиск по last_name (фамилии) автора.
2. Базовый индекс: Создайте индекс для таблицы Books, чтобы обеспечить более быстрый поиск по title (названию).
3. Объяснение уникального индекса: Таблица Books имеет ограничение UNIQUE для столбца isbn. Объясните, какие последствия это имеет для индексирования этого столбца.
4. Индекс по внешнему ключу: Создайте индекс для таблицы Books, чтобы оптимизировать запросы, соединяющие (JOIN) таблицы Books и Authors (т.е. проиндексируйте внешний ключ author_id).
5. Составной индекс: Создайте составной (многоколоночный) индекс для таблицы Books, чтобы повысить производительность запросов, которые ищут книги по определенному genre (жанру) и publication_year (году публикации). genre должен быть первым столбцом в индексе.
6. Индекс по внешнему ключу (Таблица отслеживания): Создайте индекс для таблицы BorrowingRecords, чтобы быстро находить все записи о выдаче, связанные с определенным book_id.
7. Индекс по внешнему ключу (Таблица отслеживания): Создайте индекс для таблицы BorrowingRecords, чтобы быстро находить все книги, взятые определенным member_id (читателем).
8. Частичный индекс: Создайте частичный индекс для таблицы BorrowingRecords, охватывающий столбец due_date, но только для тех записей, где return_date равен NULL. Это поможет эффективно находить просроченные книги или текущие (невозвращенные) выдачи.
9. Индекс по выражению: Создайте индекс для таблицы Books на основе столбца publication_year, но сохраняющий только десятилетие (например, 1950 для 1951, 1810 для 1813). Используйте целочисленное деление или другое подходящее выражение. Это может оптимизировать поиск по десятилетиям. CREATE INDEX idx_books_pub_decade ON Books (((publication_year / 10) * 10));
10. Просмотр и удаление индекса: Используйте мета-команду \d Books (в psql или в SQL-панели pgAdmin Query Tool), чтобы просмотреть индексы таблицы Books. Затем удалите индекс для столбца title, созданный в шаге 2.

---

Вариант 3: Инструмент управления проектами

Сценарий: Вы являетесь администратором базы данных для приложения по управлению проектами. Пользователи часто ищут проекты, фильтруют задачи по статусу, исполнителю и приоритету, а также просматривают списки задач. Оптимизация этих запросов с помощью индексов важна для отзывчивости приложения.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS TaskAssignments; -- Скорректировано для ясности
DROP TABLE IF EXISTS Tasks;
DROP TABLE IF EXISTS Projects;
DROP TABLE IF EXISTS Users;


-- Создаем таблицу Users
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(150)
);

-- Создаем таблицу Projects
CREATE TABLE Projects (
    project_id SERIAL PRIMARY KEY,
    project_code VARCHAR(20) UNIQUE NOT NULL, -- например, 'ALPHA', 'OMEGA-2'
    name VARCHAR(200) NOT NULL,
    start_date DATE,
    deadline DATE,
    status VARCHAR(20) DEFAULT 'planning' -- 'planning' (планирование), 'active' (активен), 'completed' (завершен), 'on_hold' (приостановлен)
);

-- Создаем таблицу Tasks
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, -- например, 1 (Высокий) до 5 (Низкий)
    status VARCHAR(20) DEFAULT 'todo', -- 'todo' (сделать), 'in_progress' (в работе), 'review' (на проверке), 'done' (выполнено), 'blocked' (заблокировано)
    due_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Создаем таблицу TaskAssignments (связывает Задачи и Пользователей)
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) -- Гарантирует, что пользователь не назначен на одну и ту же задачу дважды
);


-- Вставляем примерные данные
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), -- Задача 1 назначена John Doe
(2, 2), -- Задача 2 назначена Alice Smith
(3, 1), -- Задача 3 назначена John Doe
(4, 3), -- Задача 4 назначена Bob White
(5, 2), -- Задача 5 назначена Alice Smith
(4, 1); -- Задача 4 также назначена John Doe

Задания:

1. Базовый индекс: Создайте индекс для таблицы Projects, чтобы быстро находить проекты по их status.
2. Объяснение уникального индекса: Таблица Users имеет ограничения UNIQUE на столбцы username и email. Что это означает относительно автоматически создаваемых индексов для этих столбцов?
3. Индекс для внешнего ключа: Создайте индекс для таблицы Tasks, чтобы эффективно извлекать все задачи, принадлежащие определенному project_id.
4. Индекс для внешнего ключа (Таблица назначений): Создайте индекс для таблицы TaskAssignments, чтобы быстро находить все назначения (и, следовательно, задачи) для заданного user_id.
5. Индекс для внешнего ключа (Таблица назначений): Создайте индекс для таблицы TaskAssignments, чтобы быстро находить всех исполнителей для заданной task_id.
6. Составной индекс: Создайте составной (многоколоночный) индекс для таблицы Tasks, чтобы оптимизировать фильтрацию задач сначала по project_id, а затем по status.
7. Составной индекс (Назначения): Создайте составной индекс для таблицы TaskAssignments, чтобы оптимизировать запросы, ищущие задачи, назначенные конкретному пользователю (user_id), упорядоченные по временной метке assigned_at.
8. Частичный индекс: Создайте частичный индекс для таблицы Tasks, охватывающий столбец due_date, но только для задач, чей status не равен ‘done’ или ‘blocked’. Это поможет находить предстоящие сроки выполнения для активных задач.
9. Индекс по выражению: Создайте индекс для таблицы Tasks на основе функции UPPER(), примененной к столбцу title, для поддержки поиска по названию без учета регистра.
10. Вывод списка и удаление индекса: Используйте системное представление pg_indexes, чтобы вывести список всех индексов таблицы Tasks. Определите индекс, созданный на шаге 9 (UPPER(title)), а затем удалите его.

---

Вариант 4: Платформа онлайн-форума

Сценарий: Вы отвечаете за производительность базы данных растущего онлайн-форума. Пользователям необходимо быстро находить сообщения по теме, автору и искать по содержимому. Модераторам также нужны эффективные способы для просмотра сообщений, на которые поступили жалобы. Индексирование является ключом к поддержанию хорошего пользовательского опыта.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS Posts;
DROP TABLE IF EXISTS Topics;
DROP TABLE IF EXISTS Users;

-- Создаем таблицу Users
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
);

-- Создаем таблицу Topics
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
);

-- Создаем таблицу Posts
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 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), -- Сообщение, на которое поступила жалоба
(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);

Задачи:

1. Базовый индекс: Создайте индекс для таблицы Users, чтобы ускорить поиск по join_date.
2. Индекс по внешнему ключу: Создайте индекс для таблицы Posts, чтобы эффективно находить все сообщения, сделанные конкретным user_id.
3. Индекс по внешнему ключу: Создайте индекс для таблицы Posts, чтобы эффективно извлекать все сообщения, принадлежащие конкретному topic_id.
4. Многоколоночный индекс: Создайте многоколоночный индекс для таблицы Posts для оптимизации просмотра сообщений в рамках topic_id, упорядоченных по временной метке created_at. Убедитесь, что topic_id является ведущей колонкой.
5. Частичный индекс: Создайте частичный индекс для таблицы Posts по столбцу created_at, включающий только те записи, где is_reported имеет значение TRUE. Это для очереди просмотра модераторов.
6. Индекс по выражению: Создайте индекс для таблицы Posts, используя функцию LOWER() для столбца title, чтобы обеспечить быстрый поиск по заголовку сообщения без учета регистра.
7. Пояснение к уникальному индексу: Таблица Topics имеет ограничение UNIQUE для столбца name. Что это ограничение подразумевает для индексирования этого столбца? Нужен ли отдельный CREATE INDEX для базового поиска по name?
8. Список индексов: Используйте мета-команду \d Posts (в psql или панели SQL Query Tool в pgAdmin), чтобы отобразить все индексы, связанные с таблицей Posts.
9. Переиндексация таблицы: Перестройте все индексы для таблицы Posts, предполагая, что могли происходить частые правки и удаления.
10. Удаление индекса: Удалите индекс по выражению, созданный на шаге 6 (LOWER(title) для таблицы Posts).

---

Вариант 5: Система бронирования авиабилетов

Сценарий: Вы разрабатываете бэкенд базы данных для системы бронирования авиабилетов. Производительность критически важна для поиска доступных рейсов между пунктами назначения, проверки деталей конкретных рейсов и управления бронированиями пассажиров. Индексы необходимы для обеспечения быстрого ответа на запросы.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS Bookings;
DROP TABLE IF EXISTS Flights;
DROP TABLE IF EXISTS Airlines;
DROP TABLE IF EXISTS Airports;

-- Создаем таблицу Airports (Аэропорты)
CREATE TABLE Airports (
    airport_code CHAR(3) PRIMARY KEY, -- например, 'JFK', 'LAX'
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100),
    country VARCHAR(100)
);

-- Создаем таблицу Airlines (Авиакомпании)
CREATE TABLE Airlines (
    airline_id SERIAL PRIMARY KEY,
    iata_code VARCHAR(3) UNIQUE NOT NULL, -- например, 'AA', 'UA'
    name VARCHAR(100) NOT NULL
);

-- Создаем таблицу Flights (Рейсы)
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
);

-- Создаем таблицу Bookings (Бронирования)
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), -- например, '12A', '30F'
    booking_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'confirmed' -- 'confirmed' (подтверждено), 'cancelled' (отменено)
);

-- Вставляем примерные данные
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');

-- Обновляем seats_booked на основе вставленных данных
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; -- Отмененное бронирование не учитывается
UPDATE Flights SET seats_booked = 1 WHERE flight_id = 4;

Задания:

1. Базовый индекс: Создайте индекс для таблицы Airports для быстрого поиска аэропортов, расположенных в определенном city.
2. Базовый индекс: Создайте индекс для таблицы Flights для быстрого поиска рейсов по их flight_number.
3. Составной индекс (основной поиск): Создайте ключевой составной индекс для таблицы Flights для оптимизации самого частого поиска: нахождения рейсов по departure_airport, arrival_airport и departure_time. Убедитесь, что столбцы указаны в этом порядке.
4. Индекс по внешнему ключу: Создайте индекс для таблицы Flights для эффективного поиска всех рейсов, выполняемых определенным airline_id.
5. Индекс по внешнему ключу: Создайте индекс для таблицы Bookings для быстрого извлечения всех бронирований, связанных с определенным flight_id.
6. Частичный индекс (доступность): Создайте частичный индекс для таблицы Flights, охватывающий departure_time, но только для тех рейсов, где seats_booked < total_seats. Это оптимизирует поиск рейсов, на которых еще есть свободные места.
7. Индекс по выражению (поиск по дате): Создайте индекс для таблицы Flights на основе части даты столбца departure_time (используйте CAST(departure_time AS DATE) или DATE(departure_time)). Это ускорит запросы для поиска всех рейсов, вылетающих в определенную дату, независимо от времени.
8. Уникальный индекс (назначение мест): Создайте уникальный индекс для таблицы Bookings, охватывающий flight_id и seat_number, чтобы предотвратить назначение одного и того же места на одном и том же рейсе нескольким пассажирам. Разрешите значения NULL в seat_number, если назначение места может быть необязательным на начальном этапе (хотя текущая схема подразумевает, что оно обычно присутствует).
9. Вывод списка индексов: Используйте системное представление каталога pg_indexes, чтобы вывести список всех индексов, определенных в настоящее время для таблицы Flights, показав их имена и определения.
10. Удаление индекса: Удалите базовый индекс, созданный в шаге 2, для столбца flight_number таблицы Flights.

---

Вариант 6: Система регистрации на курсы университета

Сценарий: Вы управляете базой данных системы регистрации на курсы университета. Студентам, преподавателям и администраторам требуется быстрый доступ к информации о курсах, расписаниям студентов, спискам студентов в курсах и спискам кафедр. Эффективное индексирование жизненно важно для отзывчивости системы, особенно в периоды пиковой нагрузки при регистрации.

Схема базы данных:

-- Удалить таблицы, если они существуют, для чистого старта
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, -- Идентификатор университета, например, '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, -- например, '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, -- например, '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) -- Код курса уникален в рамках кафедры
);

-- 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, -- например, 'Fall 2024', 'Spring 2025'
    registration_date DATE DEFAULT CURRENT_DATE,
    grade CHAR(2) NULL, -- например, 'A', 'B+', 'P', 'F', NULL если в процессе
    UNIQUE (student_id, course_id, 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), -- В процессе
(1, 2, 'Spring 2024', NULL), -- В процессе
(2, 3, 'Spring 2024', NULL); -- В процессе

Задания:

1. Базовый индекс: Создайте индекс на таблице Students для эффективного поиска по last_name.
2. Индекс для внешнего ключа: Создайте индекс на таблице Courses для быстрого поиска всех курсов, предлагаемых определенным dept_id.
3. Индекс для внешнего ключа (Enrollments): Создайте индекс на таблице Enrollments для эффективного получения всех курсов, на которые записан или которые прошел определенный student_id.
4. Индекс для внешнего ключа (Enrollments): Создайте индекс на таблице Enrollments для быстрого формирования списка студентов для определенного course_id.
5. Составной индекс (Расписание студента): Создайте составной индекс на таблице Enrollments для оптимизации получения расписания определенного студента за заданный semester. Индекс должен включать student_id и semester, именно в таком порядке.
6. Составной индекс (Список студентов курса): Создайте составной индекс на таблице Enrollments для оптимизации получения списка студентов для определенного course_id за конкретный semester. Индекс должен включать course_id и semester, именно в таком порядке.
7. Частичный индекс (В процессе): Создайте частичный индекс на таблице Enrollments, включающий столбец course_id, но только для строк, где grade имеет значение NULL. Это помогает быстро найти студентов, текущих записанных на курс (без оценки).
8. Индекс по выражению (Нечувствительный к регистру поиск по специальности): Создайте индекс на таблице Students, основанный на применении функции LOWER() к столбцу major, для поддержки поиска студентов по специальности без учета регистра.
9. Перечисление индексов: Используйте метакоманду \d Enrollments для вывода всех индексов, связанных в данный момент с таблицей Enrollments. Убедитесь, что индексы, созданные на предыдущих шагах, присутствуют.
10. Переиндексирование и удаление: Перестройте все индексы на таблице Students. После этого удалите индекс, созданный на шаге 1 (last_name на Students).

---

Вариант 7: Система записи на прием к врачу

Сценарий: Вы отвечаете за управление базой данных системы записи на прием в клинике. Быстрый доступ к информации о пациентах, доступным временным слотам и истории записей критически важен для работы клиники. Вам необходимо добавить индексы для оптимизации частых операций поиска и фильтрации.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS Appointments;
DROP TABLE IF EXISTS Patients;
DROP TABLE IF EXISTS Doctors;

-- Создаем таблицу Doctors (Врачи)
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
);

-- Создаем таблицу Patients (Пациенты)
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, -- Может быть NULL, но должно быть уникальным, если указано
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Создаем таблицу Appointments (Записи на прием)
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, -- Дата и время приема
    duration_minutes INT DEFAULT 30 CHECK (duration_minutes > 0),
    status VARCHAR(20) DEFAULT 'scheduled', -- например, 'scheduled' (запланирован), 'completed' (завершен), 'cancelled' (отменен), 'no_show' (неявка)
    reason TEXT -- Причина визита
);

-- Вставляем примеры данных
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');

Задания:

1. Базовый индекс: Создайте индекс для таблицы Patients для ускорения поиска по фамилии пациента (last_name).
2. Уникальный индекс: Создайте уникальный индекс для таблицы Patients по столбцу contact_phone, чтобы обеспечить уникальность для НЕ-NULL номеров телефонов.
3. Индекс по внешнему ключу: Создайте индекс для таблицы Appointments для эффективного получения всех записей на прием для конкретного patient_id.
4. Индекс по внешнему ключу: Создайте индекс для таблицы Appointments для эффективного поиска всех записей на прием, запланированных к конкретному doctor_id.
5. Составной индекс: Создайте составной индекс для таблицы Appointments для оптимизации запросов, фильтрующих сначала по doctor_id, а затем по appointment_datetime.
6. Индекс по выражению: Создайте индекс для таблицы Patients на основе года (YEAR), извлеченного из date_of_birth, для оптимизации запросов поиска пациентов, родившихся в определенном году. CREATE INDEX idx_patients_birth_year ON Patients (EXTRACT(YEAR FROM date_of_birth));
7. Частичный индекс: Создайте частичный индекс для таблицы Appointments по столбцу appointment_datetime, включающий только те записи на прием, у которых status равен ‘scheduled’. Это поможет оптимизировать запросы для поиска предстоящих, активных записей на прием.
8. Переиндексировать таблицу: Перестройте все существующие индексы для таблицы Appointments.
9. Показать индексы: Используйте мета-команду \d Appointments (в psql или на панели SQL Query Tool в pgAdmin), чтобы просмотреть индексы таблицы Appointments.
10. Удалить индекс: Удалите индекс, созданный в шаге 6 (idx_patients_birth_year).

---

Вариант 8: Система учёта складских запасов

Сценарий: Вы управляете базой данных для системы учёта складских запасов. Система должна отслеживать товары, их местоположение на различных складах и текущие уровни запасов. Эффективный поиск информации о наличии товаров, уровнях запасов и содержимом складов имеет решающее значение.

Схема базы данных:

-- Удаляем таблицы, если они существуют, для чистого старта
DROP TABLE IF EXISTS InventoryLevels;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Warehouses;

-- Создаем таблицу Warehouses (Склады)
CREATE TABLE Warehouses (
    warehouse_id SERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL, -- например, 'NYC-01', 'LAX-02'
    city VARCHAR(100) NOT NULL,
    capacity_sqft INT, -- Вместимость в кв. футах
    is_refrigerated BOOLEAN DEFAULT FALSE -- Требуется ли охлаждение
);

-- Создаем таблицу Products (Товары)
CREATE TABLE Products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL, -- Артикул (единица складского учёта)
    name VARCHAR(200) NOT NULL,
    category VARCHAR(100),
    unit_cost NUMERIC(10, 2), -- Себестоимость единицы
    supplier_info JSONB -- Хранение имени поставщика, контактов и т.д.
);

-- Создаем таблицу InventoryLevels (Уровни запасов), связывающую Products и 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) -- Товар может находиться на складе только в одной записи
);

-- Вставляем пример данных
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), -- Телевизор в NYC
(1, 3, 75, 15), -- Телевизор в Chicago
(2, 1, 120, 20), -- Стол в NYC
(3, 2, 200, 50), -- Яблоки в LA (требуется охлаждение)
(4, 1, 25, 5),  -- Камера в NYC
(4, 3, 30, 5);  -- Камера в Chicago

Задания:

1. Базовый индекс: Создайте индекс для таблицы Products, чтобы ускорить поиск по name (названию) товара.
2. Базовый индекс: Создайте индекс для таблицы Warehouses, чтобы ускорить поиск по city (городу).
3. Индекс по внешнему ключу: Создайте индекс для таблицы InventoryLevels, чтобы эффективно запрашивать уровни запасов для конкретного product_id на всех складах.
4. Индекс по внешнему ключу: Создайте индекс для таблицы InventoryLevels, чтобы быстро находить все товары, хранящиеся на конкретном warehouse_id (складе).
5. Составной индекс: Таблица InventoryLevels уже имеет ограничение уникальности (и, следовательно, соответствующий индекс) по полям (product_id, warehouse_id). Объясните, почему создание другого индекса ON InventoryLevels (warehouse_id, product_id) все еще может быть полезным для определенных запросов.
6. Частичный индекс: Создайте частичный индекс для таблицы InventoryLevels по столбцу quantity, но включайте в него только те строки, где quantity меньше reorder_level. Это поможет эффективно выявлять товары, требующие пополнения запасов.

   CREATE INDEX idx_inventory_low_stock ON InventoryLevels (product_id, warehouse_id) WHERE quantity < reorder_level;
   

7. Индекс по выражению: Создайте индекс для таблицы Products на основе значения столбца category в нижнем регистре, чтобы обеспечить поиск по категории без учета регистра.
8. Индекс GIN (JSONB): Создайте индекс GIN для столбца supplier_info в таблице Products, чтобы эффективно выполнять запросы по ключам или значениям в данных JSONB (например, для поиска товаров от конкретного поставщика по его имени).

   CREATE INDEX idx_products_supplier_info_gin ON Products USING gin (supplier_info);
   

9. Список индексов: Используйте системное представление pg_indexes, чтобы вывести список всех индексов, связанных с таблицей InventoryLevels.
10. Удаление индекса: Удалите индекс по выражению, созданный в шаге 7 (для LOWER(category) в таблице Products).

---

Вариант 9: Блог-платформа

Сценарий: Вы являетесь администратором базы данных для блог-платформы. Пользователям необходимо иметь возможность быстро находить посты по автору, тегу или дате, а также просматривать комментарии, связанные с постами. Вы должны добавить индексы для эффективной поддержки этих функций.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
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;

-- Создаем таблицу Users
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
);

-- Создаем таблицу Posts
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, если черновик
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(15) DEFAULT 'draft' -- например, 'draft', 'published', 'archived'
);

-- Создаем таблицу Comments
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_text TEXT NOT NULL,
    commented_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Создаем таблицу Tags
CREATE TABLE Tags (
    tag_id SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) UNIQUE NOT NULL
);

-- Создаем таблицу PostTags (связь Многие-ко-Многим)
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) -- Гарантирует, что тег применяется к посту только один раз
);

-- Вставляем пример данных
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!'); -- Автор отвечает

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

INSERT INTO PostTags (post_id, tag_id) VALUES
(1, 1), -- Пост 1 с тегом 'introduction'
(2, 2), -- Пост 2 с тегом 'sql'
(2, 3), -- Пост 2 с тегом 'databases'
(3, 4), -- Пост 3 с тегом 'guide'
(3, 5); -- Пост 3 с тегом 'meta'

Задания:

1. Базовый индекс: Создайте индекс для таблицы Posts, чтобы быстро находить все посты, написанные конкретным author_id.
2. Базовый индекс: Создайте индекс для таблицы Comments, чтобы быстро извлекать все комментарии, оставленные конкретным user_id.
3. Индекс по внешнему ключу: Создайте индекс для таблицы Comments, чтобы эффективно извлекать все комментарии, принадлежащие конкретному post_id.
4. Индекс по внешнему ключу (M2M): Создайте индекс для таблицы PostTags, чтобы эффективно находить все теги, связанные с заданным post_id. (Примечание: Первичный ключ уже покрывает (post_id, tag_id), но индекс конкретно по post_id может использоваться планировщиком иначе).
5. Индекс по внешнему ключу (M2M): Создайте индекс для таблицы PostTags, чтобы эффективно находить все посты, связанные с заданным tag_id.
6. Многоколоночный индекс: Создайте многоколоночный индекс для таблицы Posts, чтобы оптимизировать запросы, ищущие посты по конкретному author_id с одновременной фильтрацией по status (например, найти все ‘published’ посты автора X).
7. Индекс по выражению: Создайте индекс для таблицы Posts, используя функцию DATE() для столбца published_at, чтобы оптимизировать запросы, фильтрующие посты, опубликованные в конкретную дату (игнорируя компонент времени). CREATE INDEX idx_posts_publish_date ON Posts (DATE(published_at));
8. Частичный индекс: Создайте частичный индекс для таблицы Posts по столбцу published_at, но включайте только те строки, где status равен ‘published’. Это полезно для эффективного выполнения запросов или сортировки только опубликованных постов по дате.
9. Список индексов: Используйте мета-команду \d PostTags, чтобы просмотреть индексы (включая индекс первичного ключа) для таблицы PostTags.
10. Удаление индекса: Удалите индекс, созданный в шаге 7 (idx_posts_publish_date).

---

Вариант 10: Система бронирования столиков в ресторане

Сценарий: Вы управляете базой данных для онлайн-платформы бронирования ресторанов. Эффективный поиск ресторанов, проверка доступности столиков и управление бронированиями клиентов являются ключевыми функциями. Вам необходимо добавить индексы для оптимизации производительности запросов.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS Reservations;
DROP TABLE IF EXISTS RestaurantTables;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Restaurants;

-- Создаем таблицу Restaurants
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), -- например, 4.50
    phone_number VARCHAR(20) UNIQUE
);

-- Создаем таблицу RestaurantTables
CREATE TABLE RestaurantTables (
    table_id SERIAL PRIMARY KEY,
    restaurant_id INT NOT NULL REFERENCES Restaurants(restaurant_id),
    table_number VARCHAR(10), -- например, 'T1', 'P2' (Патио 2)
    capacity INT NOT NULL CHECK (capacity > 0),
    is_available BOOLEAN DEFAULT TRUE
);

-- Создаем таблицу Customers
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)
);

-- Создаем таблицу Reservations
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 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), -- Суши-бар
(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'), -- Кларк в Gourmet Place, столик T2
(2, 4, '2024-06-16 20:00:00', 4, 'confirmed'), -- Лоис в Pasta Paradise, столик P1
(3, 8, '2024-06-17 18:30:00', 6, 'confirmed'), -- Брюс в Sushi Central, столик B1
(1, 5, '2024-06-18 19:30:00', 5, 'cancelled'); -- Кларк в Pasta Paradise, столик P2 (отменено)

Задания:

1. Базовый индекс: Создайте индекс для таблицы Restaurants, чтобы ускорить поиск по name ресторана.
2. Базовый индекс: Создайте индекс для таблицы Customers, чтобы быстро находить клиентов по их last_name.
3. Индекс по внешнему ключу: Создайте индекс для таблицы RestaurantTables, чтобы эффективно находить все столики, принадлежащие определенному restaurant_id.
4. Индекс по внешнему ключу: Создайте индекс для таблицы Reservations, чтобы быстро находить бронирования, сделанные определенным customer_id.
5. Индекс по внешнему ключу: Создайте индекс для таблицы Reservations, чтобы быстро находить все бронирования, связанные с определенным table_id.
6. Составной индекс: Создайте составной индекс для таблицы Restaurants, чтобы оптимизировать поиск с фильтрацией сначала по city, а затем по cuisine_type.
7. Составной индекс: Создайте составной индекс для таблицы Reservations, чтобы эффективно запрашивать бронирования на основе reservation_time и status. Поместите reservation_time первым в определении индекса.
8. Частичный индекс: Создайте частичный индекс для таблицы Reservations по столбцу reservation_time, но включайте только бронирования со статусом ‘confirmed’. Это поможет эффективно находить активные предстоящие бронирования.
9. Индекс по выражению: Создайте индекс для таблицы Customers, используя функцию LOWER() для столбца email, чтобы поддерживать поиск по email без учета регистра (несмотря на наличие ограничения уникальности, это поддерживает запросы с использованием LOWER()).
10. Просмотр и удаление: Используйте мета-команду \d Reservations, чтобы вывести список индексов таблицы Reservations. Затем удалите индекс, созданный в шаге 8 (частичный индекс по reservation_time для статуса ‘confirmed’).

---

Вариант 11: Система продажи билетов на мероприятия

Сценарий: Вы разрабатываете базу данных для онлайн-платформы продажи билетов на мероприятия. Пользователям необходимо быстро искать мероприятия, просматривать наличие и цены билетов, а также управлять своими покупками. Индексирование критически важно для обработки высокой нагрузки во время продаж билетов на популярные мероприятия.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
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; -- Предполагается наличие общей таблицы Users (Пользователи)

-- Создаем таблицу Venues (Места проведения)
CREATE TABLE Venues (
    venue_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    city VARCHAR(100),
    capacity INT
);

-- Создаем таблицу Events (Мероприятия)
CREATE TABLE Events (
    event_id SERIAL PRIMARY KEY,
    venue_id INT REFERENCES Venues(venue_id),
    name VARCHAR(255) NOT NULL,
    category VARCHAR(50), -- например, 'Музыка', 'Спорт', 'Театр', 'Конференция'
    event_start_time TIMESTAMP NOT NULL,
    event_end_time TIMESTAMP,
    status VARCHAR(20) DEFAULT 'scheduled' -- 'запланировано', 'отменено', 'перенесено', 'завершено'
);

-- Создаем таблицу TicketTypes (Типы билетов) (определяет уровни цен/секции для мероприятия)
CREATE TABLE TicketTypes (
    ticket_type_id SERIAL PRIMARY KEY,
    event_id INT NOT NULL REFERENCES Events(event_id),
    description VARCHAR(100), -- например, 'Входной (без места)', 'VIP', 'Балкон, сектор А'
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    total_quantity INT NOT NULL CHECK (total_quantity >= 0), -- Общее количество доступных билетов этого типа
    available_quantity INT CHECK (available_quantity >= 0 AND available_quantity <= total_quantity)
);

-- Создаем таблицу Users (Пользователи) (упрощенная)
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(150) UNIQUE NOT NULL,
    name VARCHAR(150)
);

-- Создаем таблицу Purchases (Покупки)
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 -- Уникальный код для данной транзакции покупки
);

-- Вставляем примерные данные
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'), -- Алекс покупает 2 билета в фан-зону (Floor Standing) на Rock Legends
(2, 4, 1, 90.00, 'CONF-HAMLET-67890DEF'), -- Белла покупает 1 билет в партер (Orchestra) на Гамлета
(1, 2, 4, 480.00, 'CONF-ROCK-11223XYZ'), -- Алекс покупает 4 билета на нижний ярус трибун (Lower Bowl) на Rock Legends
(3, 6, 1, 499.00, 'CONF-TECH-44556UVW'); -- Чарли покупает 1 стандартный пропуск (Standard Pass) на Tech Conference

Задания:

1. Базовый индекс: Создайте индекс для таблицы Events для быстрого поиска мероприятий по name.
2. Базовый индекс: Создайте индекс для таблицы Venues для оптимизации поиска по city.
3. Индекс по внешнему ключу: Создайте индекс для таблицы Events для эффективного поиска всех мероприятий, проходящих в определенном venue_id.
4. Индекс по внешнему ключу: Создайте индекс для таблицы TicketTypes для быстрого получения всех типов билетов, связанных с конкретным event_id.
5. Индекс по внешнему ключу: Создайте индекс для таблицы Purchases для быстрого поиска всех покупок, сделанных конкретным user_id.
6. Составной индекс: Создайте составной индекс для таблицы Events для оптимизации запросов, фильтрующих сначала по category, а затем по event_start_time.
7. Составной индекс: Создайте составной индекс для таблицы TicketTypes для поддержки эффективных запросов, фильтрующих по event_id и сортирующих/фильтрующих по price.
8. Частичный индекс: Создайте частичный индекс для таблицы Events по столбцу event_start_time, но включайте только те мероприятия, у которых статус (status) равен ‘scheduled’ или ‘postponed’. Это поможет быстро находить предстоящие или перенесенные мероприятия.
9. Перестроение индексов таблицы: Перестройте все индексы для таблицы Purchases. Это может потребоваться периодически, если данные о покупках часто изменяются (например, возвраты, вызывающие удаления/обновления).
10. Вывод списка и удаление: Используйте системное представление pg_indexes, чтобы вывести список всех индексов таблицы Events. Затем удалите индекс, созданный на шаге 8 (частичный индекс по event_start_time).

---

Вариант 12: Платформа доски объявлений о вакансиях

Сценарий: Вы отвечаете за базу данных веб-сайта доски объявлений о вакансиях. Быстрый поиск вакансий по местоположению, категории и ключевым словам имеет ключевое значение, так же как и эффективное извлечение заявок (откликов) для конкретных вакансий или кандидатов. Индексирование жизненно важно для производительности.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS Applications;
DROP TABLE IF EXISTS Jobs;
DROP TABLE IF EXISTS Companies;
DROP TABLE IF EXISTS Candidates;

-- Создаем таблицу Companies
CREATE TABLE Companies (
    company_id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL UNIQUE,
    industry VARCHAR(100),
    headquarters_location VARCHAR(150)
);

-- Создаем таблицу Jobs
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), -- например, 'Инженерное дело', 'Маркетинг', 'Продажи', 'Дизайн'
    location VARCHAR(150), -- Может быть 'Удаленно' или город/регион
    salary_range VARCHAR(50), -- например, '$80k-$100k', 'Конкурентная'
    date_posted DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT TRUE
);

-- Создаем таблицу Candidates
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), -- например, 'Python', 'Управление проектами', 'Графический дизайн'
    resume_url VARCHAR(255) -- Ссылка на сохраненный файл резюме
);

-- Создаем таблицу Applications
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', -- 'подана', 'просмотрена', 'собеседование', 'предложение', 'отклонена', 'отозвана'
    cover_letter TEXT,
    UNIQUE (job_id, candidate_id) -- Предотвращение дублирования заявок
);

-- Вставляем пример данных
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); -- Неактивная вакансия

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'), -- Сара откликнулась на Senior Software Engineer
(2, 2, 'submitted'), -- Майкл откликнулся на Backend Developer
(3, 3, 'interviewing'), -- Прия откликнулась на Digital Marketing Manager
(1, 2, 'viewed'), -- Майкл также откликнулся на Senior Software Engineer
(4, 4, 'submitted'); -- Дэвид откликнулся на Project Manager

Задачи:

1. Базовый индекс: Создайте индекс для таблицы Jobs, чтобы ускорить поиск по title (названию) вакансии.
2. Базовый индекс: Создайте индекс для таблицы Companies, чтобы обеспечить более быстрый поиск по name (названию) компании.
3. Индекс по внешнему ключу: Создайте индекс для таблицы Jobs, чтобы эффективно находить все вакансии, опубликованные конкретной company_id.
4. Индекс по внешнему ключу: Создайте индекс для таблицы Applications, чтобы быстро извлекать все заявки (отклики), поданные на конкретную job_id.
5. Индекс по внешнему ключу: Создайте индекс для таблицы Applications, чтобы быстро находить все вакансии, на которые откликнулся конкретный candidate_id.
6. Многоколоночный индекс: Создайте многоколоночный индекс для таблицы Jobs, чтобы оптимизировать запросы, фильтрующие сначала по location, а затем по category.
7. Многоколоночный индекс: Создайте многоколоночный индекс для таблицы Applications, чтобы эффективно запрашивать заявки (отклики) на основе job_id и application_date. Поместите job_id первым.
8. Частичный индекс: Создайте частичный индекс для таблицы Jobs по столбцу date_posted, но только для тех вакансий, где is_active равно TRUE. Это поможет эффективно находить текущие активные вакансии, отсортированные по дате публикации.
9. Индекс по выражению: Создайте индекс для таблицы Jobs, используя функцию LOWER() для столбца title, чтобы поддерживать поиск по названию без учета регистра.
10. Вывод списка и удаление: Используйте системное представление pg_indexes, чтобы вывести список всех индексов, связанных с таблицей Applications. Затем удалите многоколоночный индекс, созданный на шаге 7 (по job_id, application_date).

---

Вариант 13: Музыкальный стриминговый сервис

Сценарий: Вы управляете базой данных музыкального стримингового сервиса. Пользователям необходимо иметь возможность быстро искать исполнителей, альбомы и треки, а также эффективно управлять своими плейлистами. Для обеспечения бесперебойной работы пользователей (user experience) требуются индексы.

Схема базы данных:

-- Удаляем таблицы, если они существуют, для чистого старта
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;

-- Создаем таблицу Artists
CREATE TABLE Artists (
    artist_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    genre VARCHAR(100),
    country VARCHAR(100)
);

-- Создаем таблицу Albums
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)
);

-- Создаем таблицу Tracks
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 -- Позиция трека в альбоме
);

-- Создаем таблицу UserPlaylists
CREATE TABLE UserPlaylists (
    playlist_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL, -- Предполагается, что таблица Users существует где-то еще
    name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Создаем таблицу PlaylistTracks (связывающую UserPlaylists и 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) -- Трек может находиться в конкретном плейлисте только один раз
);

-- Вставляем примерные данные
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);

Задания:

1. Базовый индекс: Создайте индекс для таблицы Artists, чтобы оптимизировать поиск исполнителей по name.
2. Базовый индекс: Создайте индекс для таблицы Albums, чтобы ускорить поиск по title альбома.
3. Индекс по внешнему ключу: Создайте индекс для столбца artist_id таблицы Albums, чтобы эффективно находить все альбомы конкретного исполнителя.
4. Индекс по внешнему ключу: Создайте индекс для столбца album_id таблицы Tracks, чтобы быстро извлекать все треки, принадлежащие определенному альбому.
5. Многоколоночный индекс: Создайте многоколоночный индекс для таблицы Tracks, используя (album_id, track_number), чтобы оптимизировать извлечение треков для альбома в их правильном порядке.
6. Индекс по внешнему ключу (связующая таблица): Создайте индекс для столбца playlist_id таблицы PlaylistTracks, чтобы быстро находить все треки в конкретном плейлисте.
7. Индекс по внешнему ключу (связующая таблица): Создайте индекс для столбца track_id таблицы PlaylistTracks, чтобы быстро определять, какие плейлисты содержат конкретный трек.
8. Частичный индекс: Создайте частичный индекс для столбца release_year таблицы Albums, индексируя только альбомы, выпущенные после 2000 года (включительно). Это ускоряет запросы, ориентированные на современную музыку.
9. Индекс по выражению: Создайте индекс для таблицы Tracks, чтобы поддерживать поиск названий треков без учета регистра, используя LOWER(title).
10. Вывод списка и удаление: Используйте системное представление pg_indexes, чтобы вывести список всех индексов, связанных с таблицей Tracks. Затем удалите индекс для поиска без учета регистра по названию, созданный на предыдущем шаге.

---

Вариант 14: Платформа для объявлений о недвижимости

Сценарий: Вы разрабатываете базу данных для веб-сайта по недвижимости. Пользователям необходимо искать объекты недвижимости по местоположению, цене, размеру и характеристикам. Агенты размещают объявления об объектах недвижимости, и производительность является ключевым фактором для функциональности поиска.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS PropertyFeatures;
DROP TABLE IF EXISTS Listings;
DROP TABLE IF EXISTS Properties;
DROP TABLE IF EXISTS Agents;

-- Создаем таблицу Agents
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)
);

-- Создаем таблицу Properties
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), -- например, 'Частный дом', 'Квартира', 'Таунхаус'
    beds INT CHECK (beds >= 0),
    baths NUMERIC(3,1) CHECK (baths >= 0), -- Позволяет указывать половинные санузлы (например, 2.5)
    square_footage INT CHECK (square_footage > 0),
    year_built INT
);

-- Создаем таблицу Listings
CREATE TABLE Listings (
    listing_id SERIAL PRIMARY KEY,
    property_id INT NOT NULL UNIQUE REFERENCES Properties(property_id), -- Гарантирует, что у объекта недвижимости может быть только одно активное объявление одновременно
    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' -- например, 'активно', 'в ожидании', 'продано', 'снято с продажи'
);

-- Создаем таблицу PropertyFeatures (связь многие-ко-многим для характеристик)
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, -- например, 'Бассейн', 'Гараж', 'Камин', 'Вид на воду'
    UNIQUE (property_id, feature_name)
);

-- Вставляем пример данных
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');

Задания:

1. Базовый индекс: Создайте базовый индекс для таблицы Properties, чтобы ускорить поиск по столбцу city.
2. Многоколоночный индекс: Создайте многоколоночный индекс для таблицы Properties по столбцам (state, city), чтобы оптимизировать поиск с фильтрацией сначала по штату, затем по городу.
3. Индекс для внешнего ключа: Создайте индекс для таблицы Listings по столбцу agent_id, чтобы эффективно находить все объявления, управляемые конкретным агентом.
4. Индекс для ограничения UNIQUE: Индекс автоматически создается для ограничения UNIQUE на Listings.property_id. Тем не менее, создайте явный индекс для этого столбца дополнительно (некоторые администраторы баз данных предпочитают это для ясности или специфической настройки производительности, хотя это и избыточно при наличии индекса от ограничения). Назовите его idx_listings_property_explicit.
5. Многоколоночный индекс: Создайте многоколоночный индекс для таблицы Listings по столбцам (status, list_price), чтобы оптимизировать поиск объявлений по статусу (например, ‘active’), а затем фильтрацию или сортировку по цене.
6. Базовый индекс: Создайте индекс для таблицы Properties по столбцу property_type.
7. Частичный индекс: Создайте частичный индекс для таблицы Listings, включающий только строки, где status = 'active'. Этот индекс должен охватывать столбец list_price для оптимизации поиска активных объявлений в заданном ценовом диапазоне.
8. Индекс по выражению: Создайте индекс для таблицы Properties, используя выражение UPPER(zip_code), чтобы обеспечить поиск по почтовому индексу без учета регистра (хотя почтовые индексы обычно единообразны, это демонстрирует концепцию).
9. Индекс для внешнего ключа (Многие-ко-многим): Создайте индекс для таблицы PropertyFeatures по столбцу property_id, чтобы быстро находить все характеристики, связанные с объектом недвижимости.
10. Переиндексация и отображение: Перестройте все индексы для таблицы Properties. Затем используйте команду \d Listings (в psql или в панели SQL редактора запросов pgAdmin), чтобы отобразить структуру и индексы таблицы Listings.

---

Вариант 15: Система отслеживания ошибок

Сценарий: Вы являетесь администратором системы отслеживания ошибок программного обеспечения. Разработчикам и QA-инженерам (инженерам по тестированию) необходимо эффективно искать, фильтровать и обновлять информацию об ошибках на основе проекта, статуса, приоритета и исполнителя. Индексы критически важны для поддержания быстродействия системы.

Схема базы данных:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS BugComments;
DROP TABLE IF EXISTS Bugs;
DROP TABLE IF EXISTS Projects;
DROP TABLE IF EXISTS Users;

-- Создаем таблицу Users (упрощенная версия)
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(150),
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Создаем таблицу Projects
CREATE TABLE Projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

-- Создаем таблицу Bugs
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), -- Может быть NULL, если исполнитель не назначен
    status VARCHAR(20) NOT NULL DEFAULT 'new', -- например, 'new', 'assigned', 'in_progress', 'resolved', 'closed', 'reopened'
    priority VARCHAR(15) NOT NULL DEFAULT 'medium', -- например, 'low', 'medium', 'high', 'critical'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Создаем таблицу BugComments
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 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, 'Подтверждено. Изучаю проблему с CSS.', '2024-03-01 11:35:00'),
(2, 1, 'Работаю над исправлением. Связано с логикой локализации.', '2024-03-06 09:05:00'),
(4, 1, 'Исправление развернуто. Закрываю эту задачу.', '2024-03-08 17:00:00'),
(1, 2, 'Все еще наблюдаю эту проблему в Firefox 110.', '2024-03-09 10:00:00');

Задания:

1. Базовый индекс: Создайте базовый индекс для столбца status в таблице Bugs, так как фильтрация по статусу очень распространена.
2. Индекс по внешнему ключу: Создайте индекс для столбца project_id в таблице Bugs, чтобы быстро находить все ошибки, связанные с конкретным проектом.
3. Индекс по внешнему ключу: Создайте индекс для столбца reported_by_user_id в таблице Bugs.
4. Индекс по внешнему ключу (допускающий NULL): Создайте индекс для столбца assigned_to_user_id в таблице Bugs. Обратите внимание, что этот столбец может содержать значения NULL.
5. Составной индекс: Создайте составной индекс для столбцов (project_id, status) в таблице Bugs, чтобы эффективно фильтровать ошибки внутри конкретного проекта по их статусу.
6. Составной индекс: Создайте еще один составной индекс для столбцов (assigned_to_user_id, status) в таблице Bugs, чтобы помочь пользователям быстро находить назначенные им ошибки, отфильтрованные по статусу.
7. Частичный индекс: Создайте частичный индекс для столбца updated_at в таблице Bugs, но только для тех ошибок, которые не находятся в статусе ‘resolved’ или ‘closed’ (т. е. для активных ошибок). Это оптимизирует запросы на поиск недавно обновленных активных задач. WHERE status NOT IN ('resolved', 'closed')
8. Индекс по внешнему ключу (Комментарии): Создайте индекс для столбца bug_id в таблице BugComments, чтобы быстро извлекать все комментарии для конкретной ошибки.
9. Объяснение уникальных индексов: Таблица Users имеет ограничения UNIQUE для столбцов username и email. Объясните, почему обычно не требуется вручную создавать базовые B-tree индексы для этих конкретных столбцов.
10. Вывод списка и удаление индекса: Используйте системное представление каталога pg_indexes, чтобы вывести список всех индексов, определенных в настоящее время для таблицы Bugs. Найдите, а затем удалите базовый индекс, созданный для столбца status в шаге 1.

---

Инструкции по сдаче

• Создайте новый Google Doc. Пример
• Добавьте ссылку на ваш Google Doc здесь: Google Таблица