- •Министерство образования Российской Федерации Пензенский государственный университет
- •Пенза 2004
- •Введение
- •1. Архитектура баз данных ms sql Server 2000
- •Физическая архитектура базы данных sql Server 2000
- •1.1.1. Файлы данных и группы файлов
- •1.1.2. Страничная организация файлов данных
- •ЛоГическая архитектура базы данных sql Server 2000
- •1.3. Системные базы данных sql server 2000
- •Создание и сопровождение баз данных средствами enterprise manager
- •3. Создание и сопровождение таблиц баз данных средствами enterprise manager
- •3.1. Основные принципы создания таблиц
- •3.2. Создание, модификация и удаление таблиц средствами Enterprise Manager
- •3.3. Управление диаграммами
- •4. Создание и управление индексами средствами Enterprise manager
- •5. Создание представлений средствами Enterprise manager
- •6. Основы программирования на языке Transact-sql
- •6.1. Средства языка Transact–sql
- •6.2. Константы, имена, идентификаторы, переменные, выражения в языке Transact–sql
- •6.3. Типы данных sql Server 2000
- •6.4. Создание и удаление баз данных, таблиц и представлений
- •6.4.1. Создание и удаление баз данных
- •6.4.2. Создание и удаление таблиц
- •6.4.3. Создание представлений
- •6.5. Создание и управление индексами
- •6.6. Вставка, удаление и изменение данных
- •6.7. Выборка данных
- •Раздел into позволяет создать новую таблицу для хранения результатов запроса, структура которой будет определяться списком выбора, т.Е. Динамически при выполнении запроса.
- •6.8. Функции sql Server 2000
- •6.8.1. Встроенные функции sql Server 2000
- •6.8.2. Функции пользователя
- •6.9. Хранимые процедуры sql Server 2000
- •6.9.1. Создание хранимых процедур
- •6.10. Триггеры
- •6.11. Создание и управление транзакциями
- •6.12. Создание, отладка и оптимизация sql–модулей
- •7. Лабораторный практикум
- •Создание диаграммы базы данных;
- •Рекомендуемые источники Печатные издания
- •Интернет-ресурсы1
- •Приложение 1. Примеры операторов языка Transact-sql
- •1.1. Создание баз данных
- •Create database Archive
- •Use master
- •Filegroup SalesGroup1
- •Filegroup SalesGroup2
- •Create database Sales
- •Create database Employees
- •1.2. Создание таблиц баз данных
- •1.3. Создание индексов
- •1.4. Создание представлений
- •From dbo. Authors
- •1.5. Добавление, модификация и удаление данных в таблицах
- •Insert Into TabF default values
- •Insert Into TabG (Col1,Col2)
- •1.6. Выборка данных из таблиц
- •If update (Phone)
- •Insert into #aaa values (111)
- •Insert into #aaa values (222)
- •Insert into #aaa values (333)
- •1.9. Создание пользовательских функций
- •1.10. Создание хранимых процедур пользователя
- •Приложение 2. Описание предметных областей, схем баз данных и запросов для лабораторного практикума
- •2.1. Предметная область «Летопись острова Санта-Белинда»
- •2.2. Предметная область «Скачки»
- •2.3. Предметная область «Хроника восхождений в альпинистском клубе»
- •2.4. Предметная область базы данных медицинского кооператива
- •2.5. Предметная область базы данных «Городская Дума»
- •2.6. Предметная область базы данных рыболовной фирмы
- •2.7. Предметная область база данных фирмы, проводящей аукционы
- •2.8. Предметная область база данных библиотеки
- •Предметная область базы данных для обслуживания работы конференции
- •2.10. Предметная область базы данных для обслуживания склада
2.6. Предметная область базы данных рыболовной фирмы
Словесное описание предметной области. Фирме принадлежит небольшая флотилия рыболовных катеров. Каждый катер имеет «паспорт», куда занесены его название, тип, водоизмещение и дата постройки. Фирма регистрирует каждый выход на лов, записывая название катера, имена и адреса членов команды с указанием их должностей (капитан, боцман и т.д.), даты выхода и возвращения, а также вес пойманной рыбы.
Схема базы данных в нотации IDEF1X
Описание таблиц базы данных
№ п.п |
Название |
Идентификатор |
Назначение |
Тип связи |
Атрибуты для связи |
1. |
Катера |
Motorboat |
Список катеров |
1:М Команды |
Ид. № катера |
2. |
Рыбаки |
Fishermen |
Список рыбаков |
1:М Команды |
Ид. № рыбака |
3. |
Команды |
Com-mands |
Команды катеров |
М:1 Катера |
Ид. № катера |
М:1 Рыбаки |
Ид. № рыбака |
||||
1:М Лов |
Ид. № катера Ид. № рыбака |
||||
4. |
Лов |
Catch |
Список выходов на лов |
М:1 Команды |
Ид. № катера Ид. № рыбака |
Описание атрибутов таблицы Катера
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № катера |
ID_Motor-boat |
N |
2 |
1÷99 |
– |
+ |
PK |
2. |
Название |
Name |
С |
35 |
Только буквы |
– |
+ |
AK |
3. |
Тип |
Type |
С |
20 |
– |
– |
+ |
– |
4. |
Водоизмещение |
Vodoizme-schenie |
R |
8,2 |
1÷99999 |
– |
– |
– |
5. |
Дата постройки |
Date_B |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
Описание атрибутов таблицы Рыбаки
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № рыбака |
ID_Fisher-men |
N |
3 |
1÷999 |
– |
+ |
PK |
2. |
ФИО |
FIO |
С |
35 |
Только буквы |
– |
+ |
– |
3. |
Адрес |
Address |
С |
40 |
– |
– |
+ |
– |
4. |
Телефон |
Phone |
С |
15 |
– |
– |
– |
– |
5. |
Пол |
Sex |
B |
1 |
0 или 1 |
1 |
+ |
– |
Описание атрибутов таблицы Команды
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № катера |
ID_Motor-boat |
N |
2 |
1÷99 |
– |
+ |
FK |
2. |
Ид. № рыбака |
ID_Fisher-men |
N |
3 |
1÷999 |
– |
+ |
FK |
3. |
Должность |
Job |
С |
20 |
Только буквы |
Матрос |
+ |
– |
4. |
Дата поступления |
Date_D |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
5. |
Дата отчисления |
Date_E |
D |
8 |
01.01.1980÷01.01.2010 |
– |
– |
– |
Описание атрибутов таблицы Лов
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № катера |
ID_Motor-boat |
N |
2 |
1÷99 |
– |
+ |
FK |
2. |
Ид. № рыбака |
ID_Fisher-men |
N |
3 |
1÷999 |
– |
+ |
FK |
3. |
Дата выхода |
Date_O |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
PK |
4. |
Дата возвращения |
Date_I |
D |
8 |
01.01.1980÷01.01.2010 |
– |
– |
– |
5. |
Вес пойманной рыбы |
Weight |
R |
6,2 |
1÷999 |
– |
– |
– |
Запросы на выборку данных
Выдать список всех катеров, которые были построены не более 5-ти лет назад. Список упорядочить по типу по возрастанию, по названию – по убыванию.
Для каждого рыбака выдать список всех команд, членом которых он когда-либо являлся, их тип, название, дату поступления и отчисления.
Вывести список всех катеров, которые получили улов выше среднего за указанный период времени.
Используя самосоединение таблиц получить все пары катеров, построенных в одном и том же году.
Выдать список всех рыбаков, которые вышли на лов в заданную дату. В результирующей таблице должны быть указаны адреса и телефоны рыбаков, их должность.
Представления
Создать представление, в котором для каждого катера выдать список всех ходивших на нем в разное время рыбаков, характеристики рыбаков, характеристики катера.
Создать представление, в котором для каждого катера хранились бы все его выходы на лов, характеристики катера и характеристики лова.
Хранимые процедуры
Написать хранимую процедуру, которая перед удалением рыбака, удаляет его из всех команд, проставляя в дату отчисления текущую дату.
Написать хранимую процедуру, которая для каждого катера рассчитает средний улов на каждого члена команды за указанный период.
Триггеры
При удалении записи о катере обеспечить удаление всех членов его команды.
При изменении должности члена команды на должность капитана или боцмана проверять, нет ли членов команды, уже имеющих эту должность.
При добавлении нового члена команды на должность капитана или боцмана проверять, не является ли он капитаном или боцманом в другой команде.