- •Министерство образования Российской Федерации Пензенский государственный университет
- •Пенза 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.2. Предметная область «Скачки»
Словесное описание предметной области. В информационной системе клуба любителей скачек должна быть представлена информация об участвующих в скачках лошадях (кличка, пол, возраст), их владельцах (имя, адрес, телефон) и жокеях (имя, адрес, возраст, рейтинг). Необходимо сформировать таблицы для хранения информации по каждому состязанию: дата, время и место проведения скачек (ипподром), название состязаний (если таковое имеется), номера заездов, клички участвующих в заездах лошадей и имена жокеев, занятые ими места и показанное в заезде время.
Разрабатываемая информационная система предназначена для участников тотализатора, владельцев лошадей и ассоциации жокеев.
Схема базы данных в нотации IDEF1X
Описание таблиц базы данных
№ п.п |
Название |
Идентификатор |
Назначение |
Тип связи |
Атрибуты для связи |
1. |
Лошади |
Horse |
Список лошадей |
1:М Состязания |
Ид. № лошади |
М:1 Владельцы |
Ид. № владельца |
||||
2. |
Владельцы |
Owners |
Список владельцев лошадей |
1:М Лошади |
Ид. № владельца |
3. |
Жокеи |
Jockey |
Список жокеев |
1:М Состязания |
Ид. № жокея |
4. |
Ипподромы |
Ippodromy |
Список ипподромов |
1:М Состязания |
Ид. № ипподрома |
5. |
Состязания |
Competiti-ons |
Список состязаний |
М:1 Лошади |
Ид. № лошади |
М:1 Жокеи |
Ид. № жокея |
||||
М:1 Ипподромы |
Ид. № ипподрома |
Описание атрибутов таблицы Лошади
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № лошади |
ID_Horse |
N |
4 |
1÷9999 |
– |
+ |
PK |
2. |
Кличка |
Moniker |
С |
15 |
– |
– |
+ |
АК |
3. |
Возраст |
Age |
N |
2 |
1÷99 |
– |
+ |
– |
4. |
Порода |
Sort |
С |
20 |
Только буквы |
– |
– |
– |
5. |
Масть |
Color |
С |
20 |
Только буквы |
– |
– |
– |
6. |
Ид. № владельца |
FIO_Owner |
C |
20 |
Только буквы |
– |
+ |
FK |
7. |
Пол |
Sex |
B |
1 |
0 или 1 |
1 |
+ |
– |
Описание атрибутов таблицы Владельцы
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № владельца |
ID_Owner |
N |
4 |
1÷9999 |
– |
+ |
PK |
2. |
ФИО владельца |
FIO_Owner |
С |
20 |
Только буквы |
– |
+ |
AK |
3. |
Адрес владельца |
Address_Ow |
С |
40 |
– |
– |
+ |
– |
4. |
Телефон владельца |
Phone_Ow |
С |
15 |
– |
– |
– |
– |
Описание атрибутов таблицы Жокеи
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № жокея |
ID_Jo-ckey |
N |
4 |
1÷9999 |
– |
+ |
PK |
2. |
ФИО жокея |
FIO_ Jo-ckey |
С |
20 |
Только буквы |
– |
+ |
AK |
3. |
Адрес жокея |
Address_J |
С |
40 |
– |
– |
+ |
– |
4. |
Дата рождения |
Date_R |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
5. |
Вес |
Weight |
R |
6,2 |
40÷100 |
– |
+ |
– |
6. |
Рейтинг |
Rating |
N |
2 |
0÷100 |
– |
+ |
– |
Описание атрибутов таблицы Ипподромы
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № ипподрома |
ID_Ippodro-my |
N |
2 |
1÷99 |
– |
+ |
PK |
2. |
Название ипподрома |
Name_Ippodromy |
С |
20 |
Только буквы |
– |
+ |
AK |
3. |
Адрес ипподрома |
Address_Ip |
С |
40 |
– |
– |
+ |
– |
4. |
Длина дорожки |
Length |
R |
7,2 |
1000÷5000 |
3800 |
+ |
– |
Описание атрибутов таблицы Состязания
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № лошади |
ID_Horse |
N |
4 |
1÷9999 |
– |
+ |
FK |
2. |
Ид. № жокея |
ID_Jo-ckey |
N |
4 |
1÷9999 |
– |
+ |
FK |
3. |
Ид. № ипподрома |
ID_Ippodro-my |
N |
2 |
1÷99 |
– |
+ |
FK |
4. |
Дата состязания |
Date_S |
D |
8 |
01.01.2004÷01.01.2010 |
– |
+ |
PK |
5. |
№ заезда |
N_drive |
N |
2 |
0÷99 |
– |
+ |
PK |
6. |
Результат |
Result |
R |
5,2 |
0÷20 |
– |
+ |
– |
5. |
Место |
Place |
N |
2 |
0÷20 |
– |
– |
– |
Запросы на выборку данных
Выдать список всех лошадей заданной масти в возрасте от 2-х до 7-и лет. Список упорядочить по возрасту.
Выдать список владельцев лошадей и их адреса для всех лошадей, участвовавших в заданном заезде на заданном ипподроме, в заданную дату, устранив дублирование из результатов поиска.
Для каждого владельца найти количество его лошадей, максимальный, минимальный и средний возраст его лошадей.
Используя самосоединение таблиц выдать список пар жокеев, имеющих одинаковый рейтинг, превышающий заданный.
Выбрать все ипподромы, в которых длина дорожки превышает среднее значение.
Представления
Создать представление, в котором для каждого владельца лошади хранились его фамилия, адрес и телефон, а также основные характеристики принадлежащих ему лошадей.
Создать представление, в котором для заданного своим названием ипподрома хранились все проведенные на нем заезды, с указанием для каждого заезда характеристик участвовавших в нем лошадей и жокеев.
Хранимые процедуры
Написать хранимую процедуру, которая для всех проведенных за указанный период заездов увеличивает рейтинг жокеев, занявших 1-е место, на 5 баллов, 2-е место – на 3-и балла, 3-е место – на 1-н балл.
Написать хранимую процедуру, осуществляющую продажу лошади другому владельцу. Если для покупателя это первая его лошадь, то добавить его в список владельцев. Если владелец продает последнюю свою лошадь, удалить его из списка владельцев.
Триггеры
При удалении владельца лошади проверять, остались ли принадлежащие ему лошади.
При изменении рейтинга жокея проверять, не превысил ли средний рейтинг всех жокеев заданной величины.
При добавлении нового участника заезда в таблицу состязаний проверять следующие сведения. К соревнованиям допускаются только лошади в возрасте от 3-х до 8-и лет. К соревнованиям допускаются только жокеи, вес которых не превышает 65 кг. В случае нарушения этих условий добавление недопустимо.