2.10. Предметная область базы данных для обслуживания склада

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

• информация о «единицах хранения» – дата поставки, код поставщика, код материала по справочнику материалов, код единицы измерения, количество пришедшего материала, цена единицы измерения;

• информация о хранящихся на складе материалах – справочник материалов – код класса материала, код группы материала, наименование материала;

• информация о единицах измерения (метры, килограммы, литры и т.д.).

• информация о поставщиках материалов – код поставщика, его наименование, ИНН, адрес (индекс, город, улица, дом), адрес банка (индекс, город, улица, дом), номер банковского счета.

Схема базы данных в нотации IDEF1X

Описание таблиц базы данных

№ п.п	Название	Идентификатор	Назначение	Тип связи	Атрибуты для связи
1.	Материалы	Material	Список материалов	1:М Единицы хранения	Код материала
2.	Единицы измерения	Units	Список единиц измерения	1:М Единицы хранения	Код единицы измерения
3.	Поставщики	Suppliers	Список поставщиков	1:М Единицы хранения	Код поставщика
4.	Единицы хранения	Keeping	Список единиц хранения	М:1 Материалы	Код материала
				М:1 Единицы измерения	Код единицы измерения
				М:1 Поставщики	Код поставщика

Описание атрибутов таблицы Материалы

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Код материала	Code_mate-rial	N	6	0÷999999	–	+	PK
2.	Код класса материала	Code_class	N	5	0÷99999	–	+	–
3.	Код группы материалов	Code_group	N	4	0÷9999	–	+	–
4.	Наименование	Name_mate-rial	С	40	Только буквы	–	+	AK

Описание атрибутов таблицы Единицы измерения

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Код единицы измерения	Code_unit	N	3	0÷999	–	+	PK
2.	Название единицы измерения	Name_unit	С	15	Только буквы	–	+	AK

Описание атрибутов таблицы Поставщики

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Код поставщика	Code_sup-plier	N	3	0÷999	–	+	PK
2.	Наименование поставщика	Name_sup-plier	C	40	Только буквы	–	+	AK
3.	ИНН	INN	С	20	Только цифры	–	+	–
4.	Адрес	Address	C	40	–	–	+	–
5.	Банк	Bank	C	40	–	–	–	–
6.	№ счета	Count	С	20	Только цифры	–	+	–
7.	Юридическое / физическое лицо	Person	В	1	0 или 1	1	+	–

Описание атрибутов таблицы Единицы хранения

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Код единицы хранения	Code_kee-ping	N	8	0÷99999999	–	+	PK
2.	Дата поставки	Date_D	D	8	01.01.2003÷01.01.2015	–	+	–
3.	Количество	Amount	N	3	0÷999	–	+	–
4.	Цена за единицу измерения	Price	R	8,2	1÷99999	–	+	–
5.	Код материала	Code_mate-rial	N	6	0÷999999	–	+	FK
6.	Код единицы измерения	Code_unit	N	3	0÷999	–	+	FK
7.	Код поставщика	Code_sup-plier	N	3	0÷999	–	+	FK

Запросы на выборку данных

1. Выдать список всех поставщиков, являющихся юридическим лицом. Список упорядочить по городу по убыванию, по наименованию – по возрастанию.

2. Для заданной своим названием единицы измерения выдать характеристики всех соответствующих единиц хранения, код класса, код группы и наименование материала.

3. Вывести список всех единиц измерения, цена которых меньше средней по всем единицам измерения.

4. Используя самосоединение таблиц получить все пары поставщиков, обслуживающихся в одном и том же банке.

5. Выдать список всех поставщиков, которые когда-либо поставляли заданный своим названием материал. В результирующей таблице должны быть указаны характеристики материала и характеристики каждой единицы хранения.

Представления

1. Создать представление, в котором для каждого материала хранились бы все характеристики поставляющих его поставщиков.

2. Создать представление, в котором для каждого поставщика хранились бы все характеристики поставляемых ими единиц хранения с расшифровкой наименования единицы измерения.

Хранимые процедуры

1. Написать хранимую процедуру, которая обеспечит корректное удаление поставщика, предварительно удалив все поставленные им единицы хранения.

2. Написать хранимую процедуру, которая будет создавать список всех единиц хранения и их поставщиков для заданной группы материалов.

Триггеры

1. При удалении записи о материале необходимо проверять, есть ли на складе хотя бы одна единица хранения с данным кодом материала. Если да, то удаление выполнять нельзя.

2. При изменении атрибута Количество единиц хранения необходимо проверять, чтобы суммарное количество всех единиц хранения с этим же кодом материала не превысило бы некоторое заданное максимальное значение, и не было бы меньше некоторого заданного минимального значения.

3. При добавлении новой единицы хранения проверять, чтобы цена за единицу хранения не превышала максимальную для данного материала.

СОДЕРЖАНИЕ

ВВЕДЕНИЕ 3

1. Архитектура баз данных MS SQL Server 2000 5

1.1. Физическая архитектура базы данных 5

SQL Server 2000 5

1.1.1. Файлы данных и группы файлов 6

1.1.2. Страничная организация файлов данных 8

1.2. ЛОГическая архитектура базы данных 9

SQL Server 2000 9

1.3. Системные базы данных SQL SERVER 2000 11

2. Создание и сопровождение баз данных средствами enterprise manager 16

3. Создание и сопровождение таблиц баз данных средствами enterprise manager 24

3.1. Основные принципы создания таблиц 24

3.2. Создание, модификация и удаление таблиц средствами Enterprise Manager 27

3.3. Управление диаграммами 32

4. Создание и управление индексами средствами Enterprise manager 34

5. Создание представлений средствами Enterprise manager 40

6. Основы программирования на языке Transact-SQL 42

6.1. Средства языка Transact–SQL 44

6.2. Константы, имена, идентификаторы, переменные, выражения в языке Transact–SQL 45

6.3. Типы данных SQL Server 2000 48

6.4. СОЗДАНИЕ И УДАЛЕНИЕ БАЗ ДАННЫХ, ТАБЛИЦ И ПРЕДСТАВЛЕНИЙ 51

6.4.1. Создание и удаление баз данных 51

6.4.2. Создание и удаление таблиц 52

6.4.3. Создание представлений 53

6.5. Создание и управление индексами 54

6.6. Вставка, удаление и изменение данных 56

6.7. Выборка данных 59

6.8. Функции SQL Server 2000 64

6.8.1. Встроенные функции SQL Server 2000 65

6.8.2. Функции пользователя 66

6.9. Хранимые процедуры SQL Server 2000 74

6.9.1. Создание хранимых процедур 76

6.10. Триггеры 82

6.11. Создание и управление транзакциями 85

6.12. Создание, отладка и оптимизация SQL–модулей 88

7. Лабораторный практикум 94

Рекомендуемые ИСТОЧНИКИ 96

Приложение 1. Примеры операторов языка Transact-SQL 98

1.1. Создание баз данных 98

Физическая характеристика 98

Sales_log 98

Размер файла 98

GO 102

1.2. Создание таблиц баз данных 102

1.3. Создание индексов 103

1.4. Создание представлений 104

FROM dbo. authors 104

CAST (title as char(30)) + 104

1.5. Добавление, модификация и удаление данных в таблицах 104

1.6. Выборка данных из таблиц 106

1.7. Создание триггеров 110

Задание 1.7.1. Создать таблицу authsmall из таблицы authors базы данных Pubs и для новой таблицы запрограммировать триггер auth_del, который будет выводить информацию о попытках удаления и количестве удаляемых строк. 110

Сначала создадим таблицу authsmall c колонками au_id, au_fname, au_lname, phone и скопируем в нее данных из таблицы authors следующей командой 110

SELECT au_id, au_fname, au_lname, phone 110

INTO authsmoll 110

FOR DELETE 110

AS 110

BEGIN 110

END 110

ELSE 111

DELETE FROM authsmall WHERE au_fname = ‘Johnson’ 111

AS 111

AS 112

1.8. Создание транзакций 112

1.9. Создание пользовательских функций 113

1.10. Создание хранимых процедур пользователя 116

Приложение 2. Описание предметных областей, схем баз данных и запросов для лабораторного практикума 117

2.1. Предметная область «Летопись острова Санта-Белинда» 118

2.2. Предметная область «Скачки» 121

2.3. Предметная область «Хроника восхождений в альпинистском клубе» 125

2.4. Предметная область базы данных медицинского кооператива 128

2.5. Предметная область базы данных «Городская Дума» 132

2.6. Предметная область базы данных рыболовной фирмы 135

2.7. Предметная область база данных фирмы, проводящей аукционы 138

2.8. Предметная область база данных библиотеки 142

2.9. Предметная область базы данных для обслуживания 146

работы конференции 146

2.10. Предметная область базы данных для обслуживания склада 150

1 Артемов Д. Microsoft SQL Server 2000. Новейшие технологии. – М.: Издательско-торговый дом «Русская редакция», 2001.