- •Министерство образования Российской Федерации Пензенский государственный университет
- •Пенза 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.5. Предметная область базы данных «Городская Дума»
Словесное описание предметной области. В базе хранятся имена, адреса, домашние и служебные телефоны всех членов Думы. В Думе работает порядка сорока комиссий, все участники которых являются членами Думы. Каждая комиссия имеет свой профиль, например, вопросы образования, проблемы, связанные с жильем, и так далее. Данные по каждой из комиссий включают: председатель и состав, даты включения и выхода из состава комиссии, избрания ее председателей. Члены Думы могут заседать в нескольких комиссиях. В базу заносятся время и место проведения каждого заседания комиссии с указанием депутатов Думы, которые участвуют в его организации.
Схема базы данных в нотации IDEF1X
Описание таблиц базы данных
№ п.п |
Название |
Идентификатор |
Назначение |
Тип связи |
Атрибуты для связи |
1. |
Депутаты |
Deputies |
Список депутатов |
1:М Депутаты в комиссиях |
Ид. № депутата |
2. |
Комиссии |
Commissi-ons |
Список комиссий |
1:М Депутаты в комиссиях |
Ид. № комиссии |
1:М Заседания |
Ид. № комиссии |
||||
3. |
Депутаты в комиссиях |
Deputies-Сommissi-on |
Участие депутатов в комиссиях |
М:1 Депутаты |
Ид. № депутата |
М:1 Комиссии |
Ид. № комиссии |
||||
4. |
Заседания |
Meetings |
Заседания комиссий |
М:1 Комиссии |
Ид. № комиссии |
Описание атрибутов таблицы Депутаты
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № депутата |
ID_ Deputy |
N |
3 |
1÷999 |
– |
+ |
PK |
2. |
ФИО |
FIO |
С |
35 |
Только буквы |
– |
+ |
AK |
3. |
Адрес |
Address |
С |
40 |
– |
– |
+ |
– |
4. |
Домашний телефон |
Phone_H |
С |
15 |
– |
– |
– |
– |
5. |
Служебный телефон |
Phone_O |
С |
15 |
– |
– |
+ |
– |
6. |
Пол |
Sex |
B |
1 |
0 или 1 |
1 |
+ |
– |
Описание атрибутов таблицы Комиссии
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № комиссии |
ID_ Com-mission |
N |
2 |
1÷99 |
– |
+ |
PK |
2. |
Наименование |
Name |
С |
35 |
Только буквы |
– |
+ |
AK |
3. |
Председатель |
Chairman |
С |
35 |
Только буквы |
– |
+ |
AK |
4. |
Дата избрания |
Date_E |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
5. |
Профиль |
Profile |
С |
80 |
Только буквы |
– |
+ |
– |
Описание атрибутов таблицы Депутаты в комиссиях
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № депутата |
ID_ Deputy |
N |
3 |
1÷999 |
– |
+ |
FK |
2. |
Ид. № комиссии |
ID_ Com-mission |
N |
2 |
1÷99 |
– |
+ |
FK |
3. |
Дата включения |
Date_In |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
4. |
Дата выхода |
Date_Out |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
Описание атрибутов таблицы Заседания
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № комиссии |
ID_ Com-mission |
N |
2 |
1÷99 |
– |
+ |
FK |
2. |
Дата заседания |
Date_M |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
PK |
3. |
Время начала |
Zero |
D |
8 |
01.01.1980÷01.01.2010 |
– |
– |
– |
4. |
Место заседания |
Place |
С |
35 |
Только буквы |
Зал № 5 |
+ |
– |
5. |
ФИО_ организатора |
Organizer |
С |
35 |
Только буквы |
– |
+ |
– |
Запросы на выборку данных
Выдать список всех комиссий, председатели которых были избраны в текущем году. Список упорядочить по фамилиям председателей комиссий по убыванию.
Для каждого депутата выдать список всех комиссий, членом которых он когда-либо являлся, их профиль, ФИО их председателей, дату включения и выхода.
По каждой комиссии показать количество проведенных заседаний в указанный период времени.
Используя самосоединение таблиц получить все пары комиссий, заседания которых проходили в один и тот же день в указанный период времени.
Выдать список всех депутатов, которые заседали в заданную дату в какой-либо комиссии. В результирующей таблице должны быть указаны адреса и телефоны депутатов, название комиссии, ее профиль и председатель.
Представления
Создать представление, в котором для каждой комиссии выдать список всех входивших в нее в разное время депутатов, их характеристики, характеристики комиссии.
Создать представление, в котором для каждой комиссии выдать список всех ее заседаний, характеристики комиссий и характеристики заседаний.
Хранимые процедуры
Написать хранимую процедуру, которая перед удалением депутата из таблицы Депутат, удаляет его из всех комиссий, проставляя в дату выхода из комиссии текущую дату.
Написать хранимую процедуру, которая перед удалением комиссии для каждого входящего в нее депутата проставляет в дату выхода из комиссии текущую дату.
Триггеры
При удалении записи о членстве депутата в комиссии проверять, является ли депутат членом какой-либо другой комиссии. Удаление недопустимо, если в результате депутат не будут входить ни в одну комиссию.
При изменении председателя комиссии проверять, является ли он членом этой комиссии. Не члены комиссии выступать в роли председателя комиссии не могут.
При добавлении нового заседания проверять, является ли организатор этого заседания членом комиссии. Не члены комиссии выступать в роли организаторов заседаний не могут.