- •Министерство образования Российской Федерации Пензенский государственный университет
- •Пенза 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.4. Предметная область базы данных медицинского кооператива
Словесное описание предметной области. Базу данных использует для работы коллектив врачей. В таблицы должны быть занесены имя, пол, дата рождения и домашний адрес каждого их пациента. Всякий раз, когда врач осматривает больного (пришедшего на прием или на дому), фиксируется дата и место проведения осмотра, симптомы, диагноз и предписания больному, проставляется имя пациента и имя врача. Если врач прописывает больному какое-либо лекарство, в таблицу заносится название лекарства, способ его приема, словесное описание предполагаемого действия и возможных побочных эффектов.
Схема базы данных в нотации IDEF1X
Описание таблиц базы данных
№ п.п |
Название |
Идентификатор |
Назначение |
Тип связи |
Атрибуты для связи |
1. |
Врачи |
Physicians |
Список врачей |
1:М Посещения |
Ид. № врача |
2. |
Пациенты |
Patients |
Список пациентов |
1:М Посещения |
Ид. № пациента |
3. |
Посещения |
Visit |
Список посещений врачами пациентов |
М:1 Врачи |
Ид. № врача |
М:1 Пациенты |
Ид. № пациента |
||||
1:М Назначения |
Ид. № посещения |
||||
4. |
Лекарства |
Medicine |
Список лекарств |
1:М Назначения |
Ид. № лекарства |
5. |
Назначения |
Purposes |
Лекарства, назначенные пациентам |
М:1 Посещения |
Ид. № посещения |
М:1 Лекарства |
Ид. № лекарства |
Описание атрибутов таблицы Врачи
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № врача |
ID_Physi-cian |
N |
4 |
1÷9999 |
– |
+ |
PK |
2. |
ФИО врача |
FIO_Physi-cian |
С |
35 |
Только буквы |
– |
+ |
– |
3. |
Адрес врача |
Address_ Physician |
С |
40 |
– |
– |
+ |
– |
4. |
Телефон |
Phone |
С |
15 |
– |
– |
– |
– |
5. |
Специализация |
Specializa-tion |
С |
20 |
– |
– |
– |
– |
6. |
Стаж работы |
Time |
N |
2 |
1÷99 |
– |
+ |
– |
Описание атрибутов таблицы Пациенты
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № пациента |
ID_Patient |
N |
4 |
1÷9999 |
– |
+ |
PK |
2. |
ФИО пациента |
FIO_ Pati-ent |
С |
35 |
Только буквы |
– |
+ |
– |
3. |
Адрес пациента |
Address_ Patient |
С |
40 |
– |
– |
+ |
– |
4. |
Пол |
Sex |
B |
1 |
0 или 1 |
1 |
– |
– |
5. |
Дата рождения |
Date_R |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
Описание атрибутов таблицы Посещения
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № посещения |
ID_ Visit |
N |
5 |
1÷99999 |
– |
+ |
PK |
2. |
Ид. № врача |
ID_Physi-cian |
N |
4 |
1÷9999 |
– |
+ |
FK |
3. |
Ид. № пациента |
ID_Patient |
N |
4 |
1÷9999 |
– |
+ |
FK |
4. |
Дата посещения |
Date_P |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
5. |
Место посещения |
Place |
С |
30 |
– |
– |
– |
– |
6. |
Симптомы |
Simptomy |
С |
100 |
– |
– |
+ |
– |
7. |
Диагноз |
Diagnosis |
С |
100 |
– |
– |
+ |
– |
Описание атрибутов таблицы Лекарства
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № лекарства |
ID_Medici-ne |
N |
5 |
1÷99999 |
– |
+ |
PK |
2. |
Название |
Name |
С |
30 |
– |
– |
+ |
– |
3. |
Способ применения |
Using |
С |
100 |
– |
– |
+ |
– |
4. |
Действия |
Actions |
С |
80 |
– |
– |
+ |
– |
5. |
Побочные эффекты |
Effects |
С |
80 |
– |
– |
– |
– |
Описание атрибутов таблицы Назначения
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № посещения |
ID_ Visit |
N |
5 |
1÷99999 |
– |
+ |
FK |
2. |
Ид. № лекарства |
ID_Medici-ne |
N |
5 |
1÷99999 |
– |
+ |
PK |
3. |
Количество |
Amount |
N |
2 |
1÷99 |
– |
+ |
– |
Запросы на выборку данных
Выдать список всех врачей-терапевтов мужского пола, их адреса и телефоны. Список упорядочить по фамилии.
Для каждого пациента выдать список всех врачей, которые когда-либо их посещали, дату посещения и поставленный диагноз.
Получить количество врачей-хирургов, их средний, максимальный и минимальный стаж работы.
Используя самосоединение таблиц получить все пары пациентов, проживающих по одному адресу.
Найти всех врачей, стаж которых больше стажа любого врача-терапевта.
Представления
Создать представление, в котором для каждого пациента хранятся все выписанные ему лекарства, с указанием выписавшего их врача, дату назначения, количество единиц. Считать, что врач выписывает лекарства при посещении больного.
Создать представление, в котором для каждого врача хранить список всех больных, которых он посетил, с указанием симптомов и диагноза.
Хранимые процедуры
Написать хранимую процедуру, которая выдает список всех пациентов, которые за заданный период ни разу не посещались врачом.
Написать хранимую процедуру, выдающую список всех пациентов, которым за заданный период выписывалось заданное лекарство более 5-ти раз.
Триггеры
При удалении врача проверять, остались ли в базе данных еще врачи данной специализации. Если врач данной специализации только один, удалять его нельзя.
При изменении специализации врача проверять, остались ли в базе данных еще врачи данной специализации. Если врач данной специализации только один, изменять его специализацию нельзя.
При добавлении нового посещения необходимо учитывать, что в один и тот же день одного больного может посетить только один врач, и каждый врач в один и тот же день может посетить не более 15-ти больных.