Рабочий лист: Создание, модификация и удаление таблиц

Дисциплина «ОП.08 Основы проектирования баз данных» — Занятие №30 (СИЭУиП)
Выберите назначенный практический вариант:

Вариант 1. Складской терминал логистического центра

Техническое задание:

  1. Создайте таблицу складских комплексов warehouses (с поддержкой IF NOT EXISTS):
    • warehouse_id — суррогатный первичный ключ (SERIAL PRIMARY KEY);
    • warehouse_name — строка до 100 символов (VARCHAR(100) NOT NULL);
    • city — город размещения (VARCHAR(60) NOT NULL);
    • total_area_sqm — общая площадь склада (NUMERIC(10, 2) NOT NULL CHECK (total_area_sqm > 0));
    • is_active — признак функционирования (BOOLEAN NOT NULL DEFAULT TRUE);
    • created_at — метка времени создания записи (TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP).
  2. Создайте временную сессионную таблицу оперативной суточной отгрузки temp_daily_shipments (с параметром ON COMMIT PRESERVE ROWS) со столбцами shipment_id INT, warehouse_id INT, item_barcode VARCHAR(50), quantity INT CHECK (quantity > 0), scanned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP.
  3. Используя оператор ALTER TABLE, добавьте в таблицу warehouses столбец температурного режима хранения: temperature_zone VARCHAR(20) NOT NULL DEFAULT 'STANDARD'.
  4. Переименуйте столбец total_area_sqm в usable_area_sqm с помощью команды ALTER TABLE ... RENAME COLUMN.
  5. Выполните безопасное удаление временной таблицы отгрузки (DROP TABLE IF EXISTS).

Вариант 2. Гостиничный сервис (модификация типов и CHECK)

Исходная таблица: В сервисе бронирования создана таблица номерного фонда:

CREATE TABLE hotel_rooms (
    room_id SERIAL PRIMARY KEY,
    room_number VARCHAR(10) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price_per_night INTEGER NOT NULL,
    is_available BOOLEAN NOT NULL DEFAULT TRUE
);

Техническое задание:

  1. Бухгалтерия отеля переходит на расчеты с копейками. С помощью команды ALTER TABLE измените тип данных столбца price_per_night с INTEGER на NUMERIC(10, 2). Обязательно используйте явное преобразование с помощью конструкции USING price_per_night::NUMERIC(10, 2).
  2. Добавьте проверочное ограничение целостности с явным именем chk_hotel_rooms_positive_price, гарантирующее, что стоимость суток строго больше нуля (price_per_night > 0.00).
  3. Напишите SQL-запрос к системной схеме information_schema.columns для проверки обновленного типа данных столбца price_per_night.

Вариант 3. Банковская система (ссылочная целостность и CASCADE)

Исходные таблицы: В процессинговой базе функционируют таблицы клиентов и счетов:

CREATE TABLE bank_clients (
    client_id SERIAL PRIMARY KEY,
    passport_hash VARCHAR(64) NOT NULL UNIQUE,
    full_name VARCHAR(150) NOT NULL,
    registered_at DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE bank_accounts (
    account_id SERIAL PRIMARY KEY,
    account_number VARCHAR(20) NOT NULL,
    client_id INT NOT NULL,
    balance NUMERIC(15, 2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'RUB',
    CONSTRAINT unq_account_number UNIQUE (account_number)
);

CREATE TABLE transaction_audit_logs (
    log_id BIGSERIAL PRIMARY KEY,
    account_id INT NOT NULL REFERENCES bank_accounts(account_id) ON DELETE CASCADE,
    action_type VARCHAR(30) NOT NULL,
    logged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Техническое задание:

  1. С помощью ALTER TABLE добавьте в таблицу bank_accounts внешний ключ fk_bank_accounts_client на таблицу bank_clients(client_id) с защитным правилом удаления ON DELETE RESTRICT.
  2. Удалите устаревшее ограничение уникальности unq_account_number из таблицы bank_accounts (команда ALTER TABLE ... DROP CONSTRAINT).
  3. Выполните удаление таблицы архивных логов transaction_audit_logs с каскадным снятием зависимостей (DROP TABLE ... CASCADE).

Вариант 4. Образовательный университетский портал (UNIQUE и TRUNCATE)

Техническое задание:

  1. Создайте таблицу академических дисциплин disciplines (с поддержкой IF NOT EXISTS):
    • discipline_id — суррогатный первичный ключ (SERIAL PRIMARY KEY);
    • discipline_code — шифр предмета (VARCHAR(20) NOT NULL);
    • title — название дисциплины (VARCHAR(150) NOT NULL);
    • semester_num — номер семестра (SMALLINT NOT NULL CHECK (semester_num BETWEEN 1 AND 12));
    • total_hours — объем часов (INT NOT NULL CHECK (total_hours > 0));
    • is_mandatory — обязательный курс (BOOLEAN NOT NULL DEFAULT TRUE).
  2. Используя ALTER TABLE, наложите составное ограничение уникальности с именем unq_discipline_code_semester на комбинацию полей (discipline_code, semester_num), чтобы исключить дублирование предмета в одном семестре.
  3. Создайте таблицу журнала посещаемости student_attendance_log (столбцы: log_id BIGSERIAL PRIMARY KEY, discipline_id INT REFERENCES disciplines(discipline_id), student_id INT NOT NULL, attended_at DATE DEFAULT CURRENT_DATE, presence_status BOOLEAN DEFAULT FALSE).
  4. В конце учебного года требуется моментально очистить таблицу посещаемости со сбросом автоинкрементного счетчика log_id в единицу. Напишите команду TRUNCATE TABLE с опцией RESTART IDENTITY.
Данные сохранены в браузере!