Проектирование базы данных "Аптека"
Федеральное
агентство по образованию
Государственное
образовательное учреждение высшего профессионального образования
‹‹Курский
государственный технический университет››
Кафедра
программного обеспечения вычислительной техники
КУРСОВАЯ РАБОТА
по дисциплине ‹‹Базы данных››
на тему ‹‹Аптека››
Специальность 230105 Программное обеспечение вычислительной
техники и автоматизированных систем
Автор работы __________________
(инициалы, фамилия) (подпись, дата)
Группа ПО - 71
Руководитель работы В. Г. Белов ______________________
(инициалы, фамилия) (подпись,
дата)
Работа защищена __________________
(дата)
Оценка____________________
Председатель комиссии______________________
(инициалы, фамилия) (подпись, дата)
Члены комиссии ________________________
(инициалы, фамилия) (подпись, дата)
Курск, 2010 г.
Федеральное
агентство по образованию
Государственное
образовательное учреждение высшего профессионального образования
‹‹Курский
государственный технический университет››
Кафедра программного обеспечения
вычислительной техники
ЗАДАНИЕ
НА КУРСОВУЮ РАБОТУ
Студент
Л. М. Москаленко шифр
347011 группа ПО - 71
1. Тема Аптека
2. Срок предоставления работы (проекта) к
защите ‹‹ ›› июня 2010 г
3. Исходные данные
Неформальное
описание предметной области и документы, которые в ней используются
4. Содержание пояснительной записки
курсовой работы
Введение
1. Техническое задание
1.1
Основание для разработки
1.2
Назначение разработки
1.3
Стандарты
1.4
Требования к программе или программному
изделию
1.4.2 Носители информации
1.4.3 Требования к составу и параметрам
технических средств
2 Технический проект
2.1
Описание предметной области
2.2
Словарь понятий и терминов
2.3
Определение функциональных зависимостей
2.4
Синтез схемы базы данных на основании
функциональных зависимостей
2.4.1 Построение неизбыточного покрытия
2.4.2 Построение леворедуцированного
покрытия
2.4.3 Построение праворедуцированного
покрытия
2.4.4 Построение классов эквивалентности
2.4.5 Построение редуцированного минимального
кольцевого покрытия
2.5
ER-схема базы данных
2.6
Построение запросов на SQL
2.6.1 Построение первого запроса
2.6.2 Построение второго запроса
3 Рабочий проект
3.1
Связи таблиц
3.2
Внешний вид формы
3.3
Функциональные тесты
3.3.1 Тест 1
Заключение
Библиографический список
Приложение А
Приложение Б
Реферат
Объем данного курсового проекта равен 55 страницам.
В курсовом проекте содержится два приложения, в которых приведены ER-схемы базы данных, код программы. Также в КП содержится
постановка задачи.
Ключевые слова:
База данных, ER-схема,
сущность, атрибут, ключ, связь, функциональная зависимость, система
функциональных зависимостей.
Объект разработки:
Объектом исследования в данной работе является
проектирование базы данных в соответствии с заданным вариантом.
Цель работы:
Спроектировать базу данных «Аптеки».
Метод проведения работы:
Использование ER-диаграмм,
минимизация зависимостей, построение запросов.
Summary
This termpaper contains 55 pages.
It contains two enclosures, which contain ER-diagrams. Also it contains target
setting.
Object of development is design
of the database according to specified variant.
The aim of work is design of the
database, describing «Pharmacy ».
It is necessary to make
ER-diagram, to minimize functional dependences, to write and minimize SQL
queries.
Определения
В данной работе
применены следующие термины с соответствующими определениями:
база данных: Файлы, снабженные описанием хранимых
в них данных и находящиеся под управлением СУБД.
er-диаграмма: Схема базы
данных, спроектированная с помощью программы ERWin.
сущность: Любой различимый объект (объект, который
мы можем отличить от другого), информацию о котором необходимо хранить в базе
данных.
атрибут: Поименованная характеристика сущности.
ключ: Минимальный набор атрибутов, по значениям
которых можно однозначно найти требуемый экземпляр сущности.
связь: Ассоциирование двух или более сущностей.
Обозначения и сокращения
В данной работе
применены следующие сокращения:
БД: База данных
СУБД: Система управления базами данных
ФЗ: Функциональная зависимость
Необходимо
спроектировать базу данных «Аптеки». Также целью данной работы является
ознакомление с основными принципами построения, использования и оптимизации
реляционных баз данных.
Данная
тема актуальна, так как большое количество людей заинтересовано в получении
информации о представленных лекарствах в конкретной аптеке.
Необходимо
предоставить пользователю информацию о составе представленных лекарств, цене.
Данная
задача будет решаться с помощью анализа предметной области, ее формализации с
помощью функциональных зависимостей. Затем необходимо провести этапы
минимизации системы функциональных зависимостей, описывающих предметную
область, и на основании полученной редуцированной системы спроектировать требуемую
модель базы данных.
Предполагается,
что проектируемый продукт будет иметь общественно-социальную значимость в
первую очередь для пользователей, заинтересованных в получении информации о лекарствах.
Основанием для
разработки программного продукта служит задание на курсовую работу “Аптека”.
Программный продукт
разрабатывается с целью создания программы для аптек.
Данная работа
оформлена в соответствии со стандартом СТУ 04.02.030.-2008.
В программе
требуется реализовать учет поставщиков (Название, номер лицензии , адрес поставщика,
телефонный номер). Учет сотрудников магазина (фамилия, имя отчество, серия и
номер паспорта, адрес проживания, телефонный номер, должность). Сбор информации
о поступлении продукции и даты поступления
1.4.1 Результирующие компоненты изделия
База данных в
визуальной среде разработки систем управления реляционными базами данных Microsoft Visual FoxPro и все сопровождающие файлы.
Продукт должен размещаться в виде
файлов на CD дисках. В
процессе работы могут использоваться Flash, CD-R/-RW, DVD-R/RW или другие
носители информации.
Программный
комплекс должен корректно работать на компьютере со следующими техническими
характеристиками:
−
процессор Pentium V 2200
МГц;
−
оперативная память объемом 1024
Мб;
−
жесткий диск Seagate Barracuda,
объемом 120 Гб;
−
видеоадаптер SVGA;
−
клавиатура;
−
манипулятор типа “мышь”;
устройство удаленного доступа к
сети: модем или сетевая карта.
2.1
Описание предметной области
Аптека состоит из
нескольких помещений: торговый зал, склад, бухгалтерия, включая кабинет
директора. Аптека взаимодействует с поставщиками в лице менеджера,
осуществляющими заказы на поставку лекарств, бытовой химии, медицинского
оборудования и косметических средств. Каждый препарат имеет свой состав,
наименование, артикул и показание к применению. Так же в аптеке есть свой штат
сотрудников: фармацевты, бухгалтера, грузчики-водители, менеджеры. Клиент,
приходя в аптеку, взаимодействует с фармацевтом, который, в свою очередь,
осуществляет продажу. Операции с денежными средствами и отбивку чека
осуществляет так же фармацевт. При покупке товара, клиент получает чек с
указанием товара и его стоимости. При отсутствии товара на складе покупатель
может оформить бланк-заказа, где оговариваются сроки поставки, и вносится
предоплата за выбранный товар.
В магазине ведется
документация: кассовая книга (сумма проданного товара, которая отдается
бухгалтеру, а затем передается в налоговую для отчетности), приходная
накладная, ведется учет доходов и расходов аптеки..
Словарь понятий и
терминов приведен в таблицах 2.1 – 2.11.
Таблица 2.1 –
Сотрудники
|
Атрибут
|
Смысл
|
Пример
|
|
serial_pas
|
Серия паспорта
сотрудника аптеки
|
3800
|
|
num_pas
|
Номер паспорта
сотрудника аптеки
|
884532
|
|
name
|
Имя сотрудника
магазина
|
Ольга
|
|
surname
|
Фамилия сотрудника аптеки
|
Кургузова
|
|
patronymic
|
Отчество сотрудника аптеки
|
Александровна
|
|
address
|
Адрес прописки
сотрудника аптеки
|
Ул. Сонина, д7, кв. 2
|
|
telephone_num
|
Телефонный номер
сотрудника аптеки
|
89516782355
|
|
dolgnost
|
Занимаемая должность
|
Фармацевт
|
Таблица 2.2 – Продукция
аптеки
|
Атрибут
|
Смысл
|
Пример
|
|
productid
|
Порядковый номер в
таблице продуктов
|
105
|
|
artikul
|
Артикул продукта
|
2345456
|
|
naimenovanie
|
Наименование продукта
|
Азаран
|
|
sostav
|
Состав продукта
|
цефтриаксон
|
|
Cost_one
|
Стоимость за штуку
|
200
|
|
count
|
Количество штук
|
34
|
|
sroc_godn
|
Срок годности
|
24.06.2011
|
|
description
|
Показания к применению
|
Антибиотик местного
действия с противовоспал. эффектом
|
Таблица 2.3 – Заказ
товара
|
Атрибут
|
Смысл
|
Пример
|
|
Data_zakaza
|
Дата заказа товара
|
24.02.09
|
|
predoplata
|
Сумма предоплаты за
товар
|
800
|
|
Order_execution
|
Дата выполнения заказа
|
02.03.09
|
Таблица 2.4 –
Заработная плата сотрудников
|
Атрибут
|
Смысл
|
Пример
|
|
oklad
|
Оклад сотрудника
аптеки
|
4000
|
|
premia
|
Премия сотрудника аптеки
|
2000
|
|
avans
|
Аванс сотрудника аптеки
|
1000
|
|
zarplata
|
Зарплата сотрудника аптеки
|
5000
|
Таблица 2.5 – Клиенты
|
Атрибут
|
Смысл
|
Пример
|
|
serial_pas_e
|
Серия паспорта клиента
|
3234
|
|
number_pas_e
|
Номер паспорта клиента
|
676767
|
|
name
|
Имя клиента
|
Александра
|
|
surname
|
Фамилия клиента
|
Кургузова
|
|
patronymic
|
Отчество клиента
|
Ивановина
|
|
address
|
Адрес прописки клиента
|
Ул. Ленина, д97, кв. 8
|
|
telephone_number
|
Телефонный клиента
|
89513302324
|
Таблица 2.6 –
Поставщики
|
Атрибут
|
Смысл
|
Пример
|
|
supplierid
|
Порядковый номер
поставщика
|
2
|
|
name
|
Название организации
поставщика
|
ООО Биофарм
|
|
address
|
Адрес поставщика
|
Ул. Кр. Армия, д.8
|
|
telephone
|
Телефон поставщика
|
546523
|
Таблица 2.7 –
Поставка продукции
|
Атрибут
|
Смысл
|
Пример
|
|
Data_supply
|
Дата поставки
продукции
|
10.05.10
|
|
Time_supply
|
Время поставки
продукции
|
16.20
|
Таблица 2.8 –
Продажа товара
|
Атрибут
|
Смысл
|
Пример
|
|
Data_prodagi
|
Дата продажи продукции
|
05.05.10
|
|
Time_prodagi
|
Время продажи
продукции
|
15.35
|
|
count
|
Количество проданной
продукции
|
2
|
Таблица 2.9 – Доход
аптеки
|
Атрибут
|
Смысл
|
Пример
|
|
Month_d
|
Месяц учета
|
июль
|
|
Year_d
|
Год учета
|
2009
|
|
Dohod_employee
|
Выручка сотрудника за
месяц
|
91233
|
Таблица 2.10 –
Расходы аптеки
|
Атрибут
|
Смысл
|
Пример
|
|
Month_r
|
Месяц учета
|
январь
|
|
Year_r
|
Год учета
|
2009
|
|
arenda
|
Сумма за аренду
|
15000
|
|
Communal_yslygi
|
Сумма за коммунальные
услуги
|
4500
|
|
reklama
|
Сумма за рекламу
|
3000
|
|
Vnutr_rashodi
|
Сумма за внутренние
расходы
|
10000
|
Таблица 2.11 – Бухгалтерия
|
Атрибут
|
Смысл
|
Пример
|
|
month
|
Месяц учета
|
август
|
|
year
|
Год учета
|
2008
|
Не может быть так,
что бы в один и тот же день, в одно и тоже время был оформлен заказ и прибыло
два поставщика.
data_supply,
vremy_postavki -> supplierid.
Не может такого быть,
что бы один и тот же продукт, в одно и тоже время, в один и тот же день, один и
тот же клиент, купил у двух и более продавцов.
productid,
data_cost, vremy_cost, clientid -> employeeid.
Не может такого быть,
что бы в один и тот же месяц, в один и тот же год фиксировались дважды расходы аптеки.
month_r, year_r -> arenda, kommunal_yslygi,
reklama, vnutr_rashodi.
Не может такого быть,
что бы в один и тот же месяц, в один и тот же год фиксировалась дважды выручка,
заработанная каждым продавцом
month_d,
year_d -> viruchka_emploee.
Не может такого быть,
что бы один и тот же продавец фиксировался дважды за месяц и год.
employeeid
-> month_d, year_d.
Не может такого быть,
что бы в один и тот же месяц, в один и тот же год фиксировались дважды доходы и
расходы.
month, year -> rashodid, dohodid.
Не может такого быть,
что бы один и тот же продавец получил дважды зарплату за месяц.
emloyeeid
-> oklad, premia, avans, zarplata.
Не может такого быть,
что бы один и тот же продукт имел два одинаковых артикула.
productid -> artikulid.
Не может такого
быть, чтобы один и тот же поставщик имел 2 порядковых номера.
supplierid -> name.
Исходные
функциональные зависимости представлены на рисунке 2.1
|
data_supply,
vremy_postavki -> supplierid
clientid,
productid, data_voz, vremy_voz -> employeeid
month_r,
year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi
month_d,
year_d -> viruchka_emploee
employeeid
-> month_d, year_d
month, year
-> rashodid, dohodid
emloyeeid
-> oklad, premia, avans, zarplata
productid
-> artikulid
supplierid -> name
|
Рисунок 2.1 –
Исходные функциональные зависимости
Этапы построения
неизбыточного покрытия представлены на рисунке 2.2.
Построение
неизбыточного покрытия.
|
G:
data_supply, vremy_postavki -> supplierid
clientid, productid, data_voz, vremy_voz -> employeeid
month_r, year_r -> arenda, mployee_yslygi, reklama,
vnutr_rashodi
month_d, year_d -> viruchka_emploee
employeeid -> month_d, year_d
month, year -> rashodid, dohodid
productid -> artikulid
supplierid -> name
Неизбыточное
покрытие
g:
data_supply,
vremy_postavki -> supplierid
clientid, productid, data_voz, vremy_voz -> employeeid
month_r, year_r -> arenda, mployee_yslygi, reklama,
vnutr_rashodi
month_d, year_d -> viruchka_emploee
employeeid -> month_d, year_d
month, year -> rashodid, dohodid
emloyeeid -> oklad, premia, avans, zarplata
productid -> artikulid
supplierid ->
name
|
Рисунок 2.2 –
Построение неизбыточного покрытия
Этапы построения
леворедуцированного покрытия представлены на рисунке 2.3.
Построение
леворедуцированного покрытия.
|
g:
data_supply,
vremy_postavki -> supplierid
clientid,
productid, data_voz, vremy_voz -> employeeid
month_r,
year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi
month_d,
year_d -> viruchka_emploee
employeeid
-> month_d, year_d
month,
year -> rashodid, dohodid
emloyeeid
-> oklad, premia, avans, zarplata
productid
-> artikulid
supplierid -> name
Леворедуцированное
покрытие
g:
data_supply,
vremy_postavki -> supplierid
data_voz,
vremy_voz, productid -> clientid
month_r,
year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi
month_d,
year_d -> viruchka_emploee
employeeid
-> month_d, year_d
month,
year -> rashodid, dohodid
emloyeeid
-> oklad, premia, avans, zarplata
productid
-> artikulid
supplierid -> name
|
Рисунок 2.3 -
Построение леворедуцированного покрытия
Этапы построения
праворедуцированного покрытия представлены на рисунке 2.4
Построение
праворедуцированного покрытия.
|
g:
data_supply, vremy_postavki -> supplierid
productid, data_voz, vremy_voz -> employeeid
month_r, year_r -> arenda, kommunal_yslygi, reklama,
vnutr_rashodi
month_d, year_d -> viruchka_emploee
employeeid -> month_d, year_d
month, year -> rashodid, dohodid
emloyeeid -> oklad, premia, avans, zarplata
productid -> artikulid
supplierid -> name
Удаление
зависимостей вида X->
Праворедуцированное
покрытие
g:
data_supply, vremy_postavki -> supplierid
productid, data_voz,
vremy_voz -> employeeid
month_r, year_r -> arenda, kommunal_yslygi, reklama,
vnutr_rashodi
month_d, year_d -> viruchka_emploee
employeeid -> month_d, year_d
month, year -> rashodid, dohodid
emloyeeid -> oklad, premia, avans, zarplata
productid -> artikulid
supplierid ->
name
|
Рисунок 2.4- Построение праворедуцированного
покрытия.
2.4.4 Построение классов эквивалентности
Этапы построения
классов эквивалентности представлены на рисунках 2.5 – 2.6. Построение классов
эквивалентности.
|
Построение классов
эквивалентности
1. Ef( data_supply,
vremy_postavki ):
data_supply, vremy_postavki -> supplierid
2. Ef( clientid,
productid, data_voz, vremy_voz ):
clientid,
productid, data_voz, vremy_voz -> employeeid
3. Ef(
month_r, year_r ):
month_r,
year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi
4. Ef(
month_d, year_d ):
month_d, year_d
-> viruchka_emploee
5. Ef(
employeeid ):
employeeid
-> month_d, year_d
6. Ef(
month, year ):
month,
year -> rashodid, dohodid
7. Ef(
emloyeeid ):
emloyeeid
-> oklad, premia, avans, zarplata
|
Рисунок 2.5 -
Построение классов эквивалентности
|
8. Ef(
productid ):
productid
-> artikulid
9. Ef(
supplierid ):
supplierid -> name
Построение
минимального покрытия на основе
прямой функциональной
определяемости
Редуцированное
минимальное покрытие
g:
data_supply,
vremy_postavki -> supplierid
clientid,
productid, data_voz, vremy_voz -> employeeid
month_r,
year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi
month_d,
year_d -> viruchka_emploee
employeeid
-> month_d, year_d
month,
year -> rashodid, dohodid
emloyeeid
-> oklad, premia, avans, zarplata
productid
-> artikulid
supplierid
-> name
|
Рисунок 2.6 - Построение
классов эквивалентности
Определение CF-зависимостей
по классам эквивалентности представлено на рисунках 2.7 – 2.11. Построение
кольцевого покрытия
|
Минимальное
кольцевое покрытие
( data_supply, vremy_postavki; ) -> supplierid
( productid, data_voz,
vremy_voz; ) -> employeeid
(
productid, data_zakaza, employeeid; clientid, data_zakaza, productid; )
( month_r,
year_r; ) -> arenda, kommunal_yslygi, reklama, vnutr_rashodi
( month_d,
year_d; ) -> viruchka_emploee
(
employeeid; ) -> month_d, year_d
( month,
year; ) -> rashodid, dohodid
(
emloyeeid; ) -> oklad, premia, avans, zarplata
(
productid; ) -> artikulid
(supplierid;) -> name
Получение кольцевого
минимального
редуцированного
покрытия
Естественное
характеристическое множество
для кольцевого
покрытия
f(C):
data_supply,
vremy_postavki -> supplierid
productid,
data_voz, vremy_voz -> employeeid
|
Рисунок 2.7 - Построение редуцированного
минимального кольцевого покрытия
|
month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi
month_d, year_d -> viruchka_emploee
employeeid ->
month_d, year_d
month,
year -> rashodid, dohodid
emloyeeid
-> oklad, premia, avans, zarplata
productid
-> artikulid
supplierid -> name
Левая редукция или
перенос в правую часть
1. Проверка CF-зависимости:
(
data_supply, vremy_postavki; ) -> supplierid
2. Проверка CF-зависимости:
(
clientid, productid, data_voz, vremy_voz; ) -> employeeid
3. Проверка CF-зависимости:
( month_r,
year_r; ) -> arenda, kommunal_yslygi, reklama,
vnutr_rashodi
4. Проверка CF-зависимости:
( month_d, year_d; )
-> viruchka_emploee
5. Проверка
CF-зависимости:
( employeeid; ) ->
month_d, year_d
|
Рисунок 2.8 - Построение редуцированного
минимального кольцевого покрытия
|
6. Проверка CF-зависимости:
( month, year; ) -> rashodid,
dohodid
7. Проверка CF-зависимости:
( emloyeeid; ) -> oklad, premia, avans, zarplata
8. Проверка
CF-зависимости:
( productid; ) ->
artikulid
9. Проверка
CF-зависимости:
( supplierid; ) ->
name
Проверка покрытия на
праворедуцированность
1. Проверка CF-зависимости:
( data_supply,
vremy_postavki; ) -> supplierid
2. Проверка CF-зависимости:
(
clientid, productid, data_voz, vremy_voz; ) -> employeeid
3. Проверка CF-зависимости:
( month_r,
year_r; ) -> arenda, kommunal_yslygi, reklama,
vnutr_rashodi
4. Проверка
CF-зависимости:
( month_d, year_d; )
-> viruchka_emploee
|
Рисунок 2.9 - Построение
редуцированного минимального кольцевого покрытия
|
5. Проверка
CF-зависимости:
( employeeid; ) ->
month_d, year_d
6. Проверка
CF-зависимости:
( month, year; )
-> rashodid, dohodid
7. Проверка
CF-зависимости:
( emloyeeid; ) ->
oklad, premia, avans, zarplata
8. Проверка
CF-зависимости:
( productid; ) ->
artikulid
9. Проверка
CF-зависимости:
( supplierid; ) ->
name
Минимальное
редуцированное кольцевое покрытие
C
(
data_supply, vremy_postavki; ) -> supplierid
(
clientid, productid, data_voz, vremy_voz; ) -> employeeid
( month_r,
year_r; ) -> arenda, kommunal_yslygi, reklama, vnutr_rashodi
( month_d,
year_d; ) -> viruchka_emploee
( employeeid;
) -> month_d, year_d
( month,
year; ) -> rashodid, dohodid
(
emloyeeid; ) -> oklad, premia, avans, zarplata
(
productid; ) -> artikulid
( supplierid; ) -> name
|
Рисунок 2.10 - Построение
редуцированного минимального кольцевого покрытия
|
Естественное
характеристическое множество
f(C):
data_supply,
vremy_postavki -> supplierid
clientid,
productid, data_voz, vremy_voz -> employeeid
month_r,
year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi
month_d,
year_d -> viruchka_emploee
employeeid
-> month_d, year_d
month,
year -> rashodid, dohodid
emloyeeid
-> oklad, premia, avans, zarplata
productid
-> artikulid
supplierid
-> name
R0 = (
data_supply, vremy_postavki, supplierid ) K0 = { data_supply,
vremy_postavki
}
R1 = (
clientid, productid, data_voz, vremy_voz, employeeid ) K1 = {
clientid,
productid, data_voz, vremy_voz }
R2 = (
month_r, year_r, arenda, kommunal_yslygi, reklama, vnutr_rashodi)
K2 = {
month_r, year_r }
R3 = (
month_d, year_d, viruchka_emploee ) K3 = { month_d, year_d }
R4 = (
employeeid, month_d, year_d ) K4 = { employeeid }
R5 = (
month, year, rashodid, dohodid ) K5 = { month, year }
R6 = (
emloyeeid, oklad, premia, avans, zarplata ) K6 = { emloyeeid }
R7 = (
productid, artikulid ) K7 = { productid }
R8 = ( supplierid,
name ) K8 = { supplierid }
|
Рисунок 2.11 - Построение
редуцированного минимального кольцевого покрытия
2.5
ER-схема базы данных
В приложении А
приведены схемы базы данных до минимизации и после минимизации. Построение запросов на SQL
2.5.1
Запрос на выборку
2.5.1.1
Словесная формулировка запроса
Перечислить всех клиентов и их паспортные данные,
которые приобрели продукцию компании Fuzafungin.
2.5.1.2
Текст запроса на языке SQL
Текст запроса на языке SQL
представлен на рисунке 2.20.
|
SELECT clients.name, clients.serial_pas, clients.number_pas,
product.name_product, orders.data_zakaza, orders.orderid as Number_Order;
FROM ((orders JOIN sotrud s ON orders. clientid = clients. clientid)
JOIN product ON orders.productid = product.productid);
WHERE (product.name_product = "Fuzafungin")
|
Рисунок 2.20 -
Текст запроса на языке SQL
2.5.1.3
Исходное дерево запроса
Исходное дерево для
запроса представлено на рисунке 2.21
|
q2
q1
product
clients orders
|
Рисунок 2.21- Исходное дерево запроса
2.6.1.4 Расчет сложности запроса
Расчет сложности
исходного запроса приведен ниже.
, (2.1)
где - результат естественного соединения таблиц;
- таблицы, участвующие в соединении;
- псевдонимы, используемые вместо имен таблиц для сокращения записи;
- операция естественного соединения.
, (2.2)
(2.3)
где - число записей в таблице ;
- число записей в таблице С;
- число записей в таблице O;
- число различных значений в столбце clientid таблицы Clients;
- число различных значений в столбце clientid таблицы Orders.
,
,
,
.
, (2.4)
, (2.5)
где - число блоков, записанных в таблице ;
- длина одной записи таблицы в блоках;
- длина одной записи таблицы в байтах;
- размер одного блока.
Очевидно, что
, (2.6)
где - объем в байтах поля ;
- объем в байтах поля ;
,
,
.
.
.
.
, (2.7)
,
,
,
,
. (2.8)
.
Очевидно, что
, (2.9)
,
,
,
.
.
.
(2.10)
где - операция селекции с логической формулой
.
Формула имеет вид:
, (2.11)
,
, (2.12)
.
Так как операция
селекции не изменяет структуру таблицы, то
,
.
Так как операция
проекции располагается последней, то ее не учитывают при расчете сложности
запроса.
Окончательно имеем:
, (2.13)
.
2.1
2.2
2.3
2.3.1
2.3.1.1
2.3.1.2
2.3.1.3
2.3.1.4
2.3.1.5
Оптимизированное дерево запроса
Оптимизированное дерево для запроса представлено
на рисунке 2.22
|
q3
q2
product
clients orders
|
Рисунок 2.22 - Оптимизированное дерево запроса
Текст оптимизированного запроса на языке SQL
Текст оптимизированного запроса представлен на
рисунке 2.23
|
SELECT product.productid, product.name_product;
FROM product;
WHERE (product.name_product = " Fuzafungin ")
INTO TABLE k
SELECT sotruds...name, sotruds..serial_pas, sotruds...number_pas,
orders.data_zakaza, orders.orderid as Number_Order;
FROM
((orders JOIN clients ON orders. sotrudid = sotruds. sotrudid) JOIN k ON
k.productid = orders.productid);
|
Рисунок 2.23
2.3.1.6
Расчет сложности оптимизированного
запроса
Расчет сложности
оптимизированного запроса приведен ниже.
, (2.14)
где - операция селекции с логической формулой
.
Формула имеет вид:
, (2.15)
,
,
, (2.16)
.
Так как операция
селекции не изменяет структуру таблицы, то
,
.
, (2.17)
где - результат естественного соединения
таблиц;
- таблицы, участвующие в соединении;
- псевдонимы, используемые вместо имен таблиц для сокращения записи;
- операция естественного соединения.
, (2.18)
(2.19)
,
,
.
, (2.20)
, (2.21)
где - число блоков, записанных в таблице ;
- длина одной записи таблицы в блоках;
- длина одной записи таблицы в байтах;
- размер одного блока.
Очевидно, что
, (2.22)
где - объем в байтах поля ;
- объем в байтах поля ;
,
,
.
.
.
.
, (2.23)
,
,
,
,
, (2.24)
.
Очевидно, что
, (2.25)
,
,
,
.
.
.
Так как операция
проекции располагается последней, то ее не учитывают при расчете сложности
запроса.
Окончательно имеем:
, (2.26)
2.5.2.1
Словесная формулировка запроса
Выбрать
всех клиентов, которые приобрели все без исключения препараты, перечисленные в
отдельном списке – отдельной таблице.
2.5.2.2
Текст запроса на языке SQL
|
SELECT COUNT(*);
FROM product INTO ARRAY Ar
SELECT clients.clientid, clients.name;
FROM ((clients JOIN orders ON orders.clientid =
clients.clientid) JOIN Ar ON orders.productid = Ar.productid);
GROUP BY clients.clientid, clients.name;
HAVING COUNT(*)=Ar;
|
q2
q1
product clients
clients orders
2.5.2.4
Расчет сложности запроса
Расчет сложности исходного запроса приведен ниже.
, (2.27)
где - результат естественного соединения
таблиц;
- таблицы, участвующие в соединении;
- псевдонимы, используемые вместо имен таблиц для сокращения записи;
- операция естественного соединения.
, (2.28)
(2.29)
где - число записей в таблице ;
- число записей в таблице С;
- число записей в таблице O;
- число различных значений в столбце clientid таблицы Clients;
- число различных значений в столбце clientid таблицы Orders.
,
,
,
.
, (2.30)
, (2.31)
где - число блоков, записанных в таблице ;
- длина одной записи таблицы в блоках;
- длина одной записи таблицы в байтах;
- размер одного блока.
Очевидно, что
, (2.32)
где - объем в байтах поля ;
- объем в байтах поля ;
,
,
.
.
.
.
, (2.33)
,
,
,
,
. (2.34)
.
Очевидно, что
, (2.35)
,
,
,
.
.
.
(2.36)
где - операция проекции из таблицы атрибутов .
.
.
,
,
.
Так как операция
группировки является последней, то для нее не производится расчет сложности.
Окончательно имеем:
, (2.37)
2.5.2.5
птимизированное дерево запроса
Оптимизированное дерево запроса представлено на
рисунке 2.25.
q2
q1
clients
product orders
|
Рисунок 2.25
2.5.2.6
Текст оптимизированного запроса на языке SQL
Текст
оптимизированного запроса представлен на рисунке 2.26
|
SELECT COUNT(*);
FROM product INTO ARRAY Ar
SELECT clients.clientid, clients.name;
FROM ((orders JOIN Ar ON orders.productid = Ar.productid)
JOIN clients ON orders.clientid = clients.clientid);
GROUP BY clients.clientid, clients.name;
HAVING COUNT(*)=Ar;
|
Рисунок
2.26
2.5.2.7
Расчет сложности оптимизированного запроса
Расчет сложности
оптимизированного запроса приведен ниже.
. (2.38)
,
,
,
, (2.39)
.
Очевидно, что
, (2.40)
,
,
,
.
.
.
(2.41)
, (2.42)
,
,
.
Очевидно, что
, (2.43)
,
,
,
.
.
.
(2.44)
где - операция проекции из таблицы атрибутов .
.
.
,
,
.
Так как операция
группировки является последней, то для нее не производится расчет сложности.
Окончательно имеем:
, (2.45)
3
Рабочий проект
Связи таблиц представлены на рисунке 3.1
Рисунок 3.1
3.2
Внешний вид формы
В состав проекта
входит форма Расходы аптеки, которая представлена на рисунке 3.2.
Рисунок 3.2
3.3.1
Тест 1
Тест на проверку
построения отчета в Excel.
Входные данные:
запрос на выборку из базы данных.
Выходные данные:
результирующая таблица.
Работа теста
приведена на рисунке 3.4.
Рисунок 3.4
Заключение
В ходе выполнения
курсовой работы была достигнута цель работы – проектирование базы данных аптеки.
Для достижения цели
был решен ряд задач: составление описания предметной области; составление
словаря понятий и терминов; построение исходной модели (ER-диаграммы)
базы данных; определение функциональных зависимостей; синтез схемы базы данных
на основании функциональных зависимостей; построение оптимизированной
концептуальной модели; построение запросов на выборку.
Библиографический
список
1. Атре, Ш., Структурный подход к организации баз данных.
/ [Текст] : Атре, Ш. - М.: Финансы и статистика, 1983. – 320 с.
2. Бойко, В.В., Проектирование баз данных информационных
систем. / [Текст] : Бойко, В.В., Савинков, В.М., – М.: Финансы и статистика,
1989. – 351 с.
3. Гарсия-Молина, Гектор, Ульман, Джеффри, Д., Уидом,
Дженнифер Системы баз данных. Полный курс. : Пер. с англ. – М. : Издательский
дом “Вильямс”, 2003. – 188 с. : ил.
4. Дейт, К., Руководство по реляционной СУБД DB2. /
[Текст] : Дейт, К. – М.: Финансы и статистика, 1988. – 320 с.
5. Джексон, Г., Проектирование реляционных баз данных для
использования с микроЭВМ. / [Текст] : Джексон, Г., -М.: Мир, 1991. – 252 с.
6. Кириллов, В.В., Структуризованный язык запросов (SQL).
/ [Текст] : Кириллов, В.В., – СПб.: ИТМО, 1994. – 80 с.
7. Мартин, Дж., Планирование развития автоматизированных
систем. / [Текст] : Мартин, Дж., – М.: Финансы и статистика, 1984. – 196 с.
8. Мейер, М., Теория реляционных баз данных. / [Текст] :
Мейер, М.,– М.: Мир, 1987. – 608 с.
9. Тиори, Т., Проектирование структур баз данных. /
[Текст] : Тиори, Т., Фрай, Дж., В 2 кн., – М.: Мир, 1985. Кн. 1. – 287 с.: Кн.
2. – 320 с.
10. Ульман, Дж., Базы данных на Паскале. / [Текст] :
Ульман, Дж., – М.: Машиностроение, 1990. – 386 с.
11. Хаббард, Дж., Автоматизированное проектирование баз
данных. / [Текст] : Хаббард, Дж., – М.: Мир, 1984. – 294 с.
12. Цикритизис, Д., Модели данных. / [Текст] :
Цикритизис, Д., Лоховски, Ф., – М.: Финансы и статистика, 1985. – 344 с.
Приложение А
ER – схемы базы данных
На рисунке А.1
приведен вариант схемы базы данных до минимизации.
Рисунок - А.1
На рисунке А.2 приведен вариант схемы базы данных
после минимизации.
Рисунок - А.2
Приложение Б
Код программы
Код запроса с созданием отчета в Excel
представлен на рисунке Б.1 – Б.2.
|
LOCAL objExcel, lnrow
SELECT clients.name, clients.serial_pas, clients.number_pas,
product.name_product, orders.data_zakaza;
FROM clients, orders, product;
INTO CURSOR cur_report;
WHERE (clients.clientid=orders.clientid) AND
(orders.data_zakaza="22/01/05") AND
(product.name_product="Acer")
objExcel=CreateObject("Excel.Application")
WITH objExcel
.Visible=.T.
.Workbooks.add
.Columns[1].ColumnWidth=20
.Columns[2].ColumnWidth=12
.Columns[3].ColumnWidth=10
.Columns[4].ColumnWidth=15
.Columns[5].ColumnWidth=7
|
Рисунок - Б.1
|
.Range("A1:E1").Select
WITH
.Selection
.MergeCells=.t.
ENDWITH
WITH
.ActiveCell
.Value
= "Клиенты"
.Font.Size
= 14
ENDWITH
lnrow
= 2
SELECT
cur_report
SCAN
lnrow
= lnrow + 1
.Cells(lnrow,1).value
= cur_report.name
.Cells(lnrow,2).value
= cur_report.serial_pas
.Cells(lnrow,3).value
= cur_report.number_pas
.Cells(lnrow,4).value
= cur_report.name_product
.Cells(lnrow,5).value
= cur_report.data_zakaza
ENDSCAN
ENDWITH
|
Рисунок - Б.2