Методические указания к лабораторным работам по дисциплине "организация баз данных" для студентов специальности Утверждено (стр. 4 из 6)
INSERT INTO "Товары" ("id_товара", "Артикул", "Наименование", "Цвет", "ЕдИзмерения") VALUES (8, 'С4444', 'Столик журнальный', 'черный', 'шт', 700.00);
INSERT INTO "Товары" ("id_товара", "Артикул", "Наименование", "Цвет", "ЕдИзмерения") VALUES (9, 'К5777', 'Крем "Нежность" для лица, 75г.', 'без цвета', 'шт', 10.40);
INSERT INTO "Товары" ("id_товара", "Артикул", "Наименование", "Цвет", "ЕдИзмерения") VALUES (10, 'К3434', 'Крем "Аленка" для рук, 50г.', 'без цвета', 'шт', 5.80);
INSERT INTO "Товары" ("id_товара", "Артикул", "Наименование", "Цвет", "ЕдИзмерения") VALUES (11, '5555', 'Шампунь "Shamtu", 250мл.', 'без цвета', 'шт', 6.70);
INSERT INTO "Товары" ("id_товара", "Артикул", "Наименование", "Цвет", "ЕдИзмерения") VALUES (12, '9999', 'Мыло хозяйственное 72%', 'без цвета', 'шт', 2.30);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (1, '2007-08-20', 1, 9, 1, 10.20, 10);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (2, '2007-08-20', 1, 10, 1, 9.70, 20);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (3, '2007-08-20', 1, 12, 2, 7.60, 15);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (4, '2007-08-21', 1, 11, 2, 6.40, 10);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (5, '2007-08-21', 2, 1, 1, 5.30, 20);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (6, '2007-08-22', 2, 2, 2, 5.40, 20);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (7, '2007-08-22', 2, 3, 2, 4.70, 14);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (8, '2007-08-22', 2, 4, 2, 1.30, 24);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (9, '2007-08-22', 2, 5, 2, 2.20, 10);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (10, '2007-08-23', 3, 6, 1, 3000.00, 4);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (11, '2007-08-23', 3, 7, 1, 2500.00, 2);
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (12, '2007-08-23', 3, 8, 1, 1500.00, 3);
INSERT INTO "Продажи" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (1, '2007-08-25', 1, 9, 2, 12.20, 1);
INSERT INTO "Продажи" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (2, '2007-08-25', 1, 10, 1, 11.70, 1);
INSERT INTO "Продажи" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (3, '2007-08-25', 2, 5, 3, 2.60, 2);
INSERT INTO "Продажи" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (4, '2007-08-25', 3, 6, 3, 3500.00, 1);
INSERT INTO "Продажи" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (5, '2007-08-25', 3, 7, 3, 4500.00, 1);
INSERT INTO "Продажи" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (6, '2007-08-26', 3, 8, 3, 2000.00, 1);
В результате получили такие таблицы:
Места хранения:
Контрагенты:
Товары:
Поставки:
Продажи:
Дополнение
Рассмотрим таблицу поставки из примера 1. Обратим внимание на поле НомерНакладной, это поле является первичным ключом, каждой новой накладной присваивается следующий по порядку номер. Для того чтобы пользователю не нужно было следить за нумерацией накладных, в PostgreSQL используются последовательности. В других СУБД этот механизм часто называется счетчиком.
Последовательность – это объект базы данных, который фактически представляет собой автоматически увеличивающееся число.
Опишем последовательность для таблицы Поставки.
create sequence НомерНакладной;
Затем, при добавлении строки в таблицу:
INSERT INTO "Поставки" ("№Накладной", "Дата", "Склад", "Товар", "Контрагент", "Цена", "Количество") VALUES (nextval('НомерНакладной'), '2007-08-20', 1, 9, 1, 10.20, 10);
Функция nextval увеличивает текущее значение заданной последовательности и возвращает новое значение в виде величины типа integer.
Для большей надежности использование последовательности НомерНакладной в качестве счетчика в таблице Поставки можно задать на этапе определения таблицы:
create table Поставки(
№Накладной int primary key default nextval(‘НомерНакладной’),
Дата date default current_date,
Склад int references МестаХранения(Id_склада),
Товар int references Товары(Id_товара),
Контрагент int references Контрагенты(Id_контрагента),
Цена decimal(10,2),
Количество int check(Количество>0));
Также для этих целей можно использовать тип данных serial.
Для более подробной информации о последовательностях смотрите документацию.
Пример 2
В этом примере показана связь супертип-подтип. В PostgreSQL для организации связи такого типа существует инструмент, называемый наследование. Таблица может наследовать некоторые атрибуты и их свойства от одной или нескольких других таблиц.
Производная таблица создается командой Create table, в которую включается секция inherits, выглядит это так:
Create table ПроизводнаяТаблица
(Определение) inherits (БазоваяТаблица) ;
В предлагаемом примере базовой таблицей является таблица Файлы, производными таблицами Музыка, Фильмы и Книги. Каждая из производных таблиц обладает своими уникальными свойствами, но у них также много общего, объединенного в таблице Файлы.
К сожалению, данный механизм в PostgreSQL реализован не полностью, ограничения таблиц не наследуются. В результате со стороны СУБД нет полного контроля над целостностью данных. Скорее всего, в ближайших версиях PostgreSQL эта проблема будет решена, но пока, в данном примере реализуем этот контроль другим путем.
В данной схеме данных существует две проблемы: первая, это то, что мы можем добавить в таблицы Файлы, Музыка и Книги одинаковые значения поля «Id_файла», т.е. ограничение primary key не наследуется; вторая проблема состоит в том, что мы не можем в таблице Закачки для поля «Файл» создать ограничение references Файлы(Id_файла).
Первая проблема решается путем создания последовательности id_файла, которая будет использоваться как счетчик для поля «Id_файла» всех трех производных таблиц Фильмы, Музыка, Книги. Это должно гарантировать неповторяющиеся значения в этом поле.
Вторую проблему решим при помощи добавления триггера КонтрольЦедостности в лабораторной работе №5.
Создание таблиц.
Создание таблицы Файлы:
create sequence id_файла; /*Создание последовательности id_файла*/
create table Файлы(
Id_файла int primary key default nextval('id_файла'), /*Целое число, первичный ключ, по умолчанию значение поля равно следующему элементу последовательности id_файла*/
Наименование char(50) unique, /*Массив символов длины 50, значение уникально*/
Размер int, /*Целое число*/
Цена decimal(10,2), /*Фиксированное число с плавающей точкой, 2 знака после запятой*/
Формат char(4)); /*Массив символов длины 4*/
Создание таблицы Музыка:
create table Музыка(
НаименованиеТрека char(50), /*Массив символов длины 50*/
Альбом char(50), /*Массив символов длины 50*/
Исполнитель char(50), /*Массив символов длины 50*/
ГодВыпуска char(4), /*Массив символов длины 4*/
Направление char(50)) /*Массив символов длины 50*/
inherits (Файлы); /*Данная таблица наследует поля таблицы Файлы*/
Создание таблицы Фильмы:
create table Фильмы(
Название char(50), /*Массив символов длины 50*/
Жанр char(50), /*Массив символов длины 50*/
Режиссер char(50), /*Массив символов длины 50*/
ГодВыпуска char(4)) /*Массив символов длины 4*/
inherits (Файлы); /*Данная таблица наследует поля таблицы Файлы*/
Создание таблицы Книги:
create table Книги(
Название char(50), /*Массив символов длины 50*/
Автор char(50), /*Массив символов длины 50*/
Издание char(50), /*Массив символов длины 50*/
ГодВыпуска char(4), /*Массив символов длины 4*/
КоличествоСтр int, /*Целое число*/
Жанр char(50)) /*Массив символов длины 50*/
inherits (Файлы); /*Данная таблица наследует поля таблицы Файлы*/
Создание таблицы Пользователи:
create table Пользователи(