Проектирование базы данных

Размещено на http://www.allbest.ru/

СОДЕРЖАНИЕ

1. Построение инфологической концептуальной модели (ER-модель)

2. Получение реляционной схемы

3. Проектирование базы данных

3.1 Таблицы - сущности

3.2 Таблицы - ассоциации

4. Схема данных

5. Построение запросов к базе данных

5.1 Запрос 1

5.2 Запрос 2

5.3 Запрос 3

5.4 Запрос 4

Список использованных источников

1. Построение инфологической концептуальной модели (ER-модель)

Процитируем постановку задачи:

«Техническое задание: Справочная аптек нуждается в оперативном поиске информации о наличии тех или иных лекарств в аптеках города, для этого в базе данных необходимо хранить следующую информацию:

всю номенклатуру препаратов, продающихся или когда-либо продававшихся в аптеках города. О препарате необходимо знать его название, производителя, тип, количество в упаковке и т.д.

наличие препарата, его цену и количество по всем аптекам.

адреса и телефоны аптек, а также их привязку к району.

Необходимо сделать выборки:

выбрать все аптеки, в которых есть в продаже определенный препарат.

Определить в каких аптеках дешевле всего «анальгин»

Вывести динамику изменения цены на препараты группы «антибиотики» в аптеке №47.

Вывести все мази групп «обезболивающие», отпускаемые без рецепта и в каких аптеках они сейчас есть.

Дополнительная информация:

Типы препаратов: мазь, таблетки, микстура, пищевые добавки и пр.

Группа препаратов: обезболивающие, антибиотики, успокаивающие, сердечные и пр.» Из условия задачи..

Рисунок 1 - ER - диаграмма связей поставленной задачи

Для поставленной задачи выполним следующий комплекс работ.

Построим инфологическую концептуальную модель (ER-модель), для чего:

проанализируем предметную область, при необходимости уточнив и дополнив ее, выявить необходимый набор сущностей;

определим требуемый набор атрибутов для каждой сущности;

определим связи между объектами;

опишем полученную ER-модель на языке инфологического проектирования и на языке ER-диаграмм.

Получение реляционной схемы

Получим реляционную схему из ER-модели, для чего:

построим набор необходимых отношений базы данных;

выделим первичные и внешние ключи определенных отношений;

приведем полученные отношения к третьей нормальной форме;

определим ограничения целостности для внешних ключей отношений и для отношений в целом.

Используя имеющуюся СУБД создадим спроектированную базу данных.

На языке SQL запишем выражения для указанных в варианте задания типов запросов. Проверим работоспособность написанных запросов в интерактивном режиме.

Проанализируем предметную область, выделив основные сущности (объекты), принадлежащие предметной области.

Основными сущностями, на мой взгляд, являются:

Препараты

Аптеки

В самом деле, «аптеки» в процессе своей деятельности производят продажу «препаратов».

Сущности имеют свои атрибуты. Атрибуты, в свою очередь, делятся на сущности, имеющие свои атрибуты и собственно атрибуты. Таким образом, напрашивается способ отображать атрибуты, сущности и связи между ними в виде графов, называемых ER-диаграммами.

Опишем необходимый набор сущностей и их атрибутов к нашей задаче

Препарат
Название препарата;
Производитель
Кол-во в упаковке
Необходим ли рецепт
Тип препарата
Группа препарата

Тип препарата
название типа препарата
Группа препарата
Название группы препарата

Аптека
Название
Номер аптеки
Адрес
Телефон
Район

Район
Название района

Продажи
Секция
Продавец
Клиент
Дата-время продажи
Номенклатура
Количество
Цена
Сумма

Наличие препарата
Аптека
Препарат
Рабочий день (дата)
Продано препарата
Остаток на складе аптеки

Динамика цен
Препарат
Аптека
Цена
Дата начала цены
Дата окончания цены

Очевидно, что динамика цен, наличие (продажи) препарата являются ассоциациями, связывающие различные сущности во время выполнения какого-либо действия (осуществление продажи, изменение цены).

Все описанные сущности тесно взаимосвязаны между собой:

Каждая аптека находится в каком-то одном районе.

Каждый препарат имеет свой тип (мазь, таблетки, микстура и т.д.)

Каждый препарат относится к какой-то группе (обезболивающие, антибиотики, успокаивающие, сердечные и пр.)

Аптеки имеют в наличие какие-то определенные препараты

Препарат имеется в наличии в каких-то определенных аптеках

Каждая аптека устанавливает свои цены на препараты.

Каждый препарат имеет разные цены в зависимости от того, в какой аптеке он продается.

Мною решено было добавить еще одну сущность «динамика цен», отделив ее от сущности «Наличие препарата», поскольку продажи препарата осуществляются ежедневно, ежедневно фиксируется остаток на складе аптеки, тогда как цены изменяются гораздо реже. Поэтому решено было изменение цены заключить в границы: начало действия цены, окончание действия цены.

Если цену препарата отслеживать ежедневно, как и остатки на складе, то это приведет к тому, что в базе ежедневно придется добавлять новые записи о ценах к сущности «наличие» препарата. Легче один раз добавить запись в сущности «Динамика цен», если цена на препарат действительно изменилась.

Все эти пронумерованные связи графически иллюстрируются в ER-диаграмме, где связи пронумерованы в соответствии с их словесным описанием, приведенным выше.

Тип каждой из этих связей является «один ко многим».

Рисунок 2. Инфологическая модель базы данных «Центральная база данных справочной аптек»

2. Получение реляционной схемы

Даталогическая модель

Даталогическая модель является моделью логического уровня и представляет собой отображение логических связей между элементами данных безотносительно к их содержанию и среде хранения. Эта модель строится в терминах информационных единиц, допустимых в той конкретной СУБД, в среде которой мы проектируем базу данных. Этап создания даталогической модели называется даталогическим проектированием. Описание логической структуры базы данных на языке СУБД называется схемой.

Хотя даталогическое проектирование является логической структурой базы данных, на него влияют возможности физической организации данных, представляемые конкретной СУБД. Поэтому знание особенностей физической организации данных является полезным при проектировании логической структуры.

Логическая структура базы данных, а также сама заполненная данными база данных является отображением реальной предметной области. Поэтому на выбор проектных решений самое непосредственное влияние оказывает специфика отображаемой предметной области, отраженная в инфологической модели.

Для реляционной базы данных проектирование логической структуры заключается в том, чтобы разбить всю информацию по файлам (отношениям), а также определить состав полей (атрибутов) для каждого из этих файлов.

Чаще же применяется менее наглядный, но более содержательный язык инфологического моделирования (ЯИМ), в котором сущности и ассоциации представляются предложениями вида:

СУЩНОСТЬ (атрибут 1, атрибут 2 , ..., атрибут n)

АССОЦИАЦИЯ [СУЩНОСТЬ S1, СУЩНОСТЬ S2, ...]

(атрибут 1, атрибут 2, ..., атрибут n)

где S - степень связи, а атрибуты, входящие в ключ, должны быть отмечены с помощью подчеркивания.

Атрибуты, помеченные двойным подчеркиванием, являются внешними ключами. Что такое внешний ключ? Это значит, что значение атрибута -внешнего ключа в этой сущности является первичным уникальным ключом для какой-то другой сущности. Каждой сущности присваивается уникальный первичный ключ для того, чтобы использовать значение этого первичного ключа в качестве внешнего ключа для другой сущности. Это обеспечивает целостность данных, то есть возможность всегда найти необходимую информацию и избежать потерянных записей (которые существуют физически, но доступ к ним невозможен из-за неверной или отсутствия организации доступа к данным).

Тип препарата (Код, название типа препарата;)

Группа препаратов (Код, название группы препаратов)

Препарат(Код, Препарат, количество в упаковке, Производитель, необходим рецепт, Тип препарата, Группа препарата)

Район (Код, Название района)

Наличие [Код, Код аптеки, Код препарата, Остаток на складе аптеки, Продано, Рабочий день]

Цена [Код, Код аптеки, Код препарата, Цена, Дата начала цены, Дата окончания цены]

Аптеки (Код, наименование аптеки, номер, улица, корпус, квартира, район, телефон)

Рисунок 3. Схема организации данных, при которой сохраняется целостность данных

Описанная нами инфологическая модель удовлетворяет требованиям к схеме организации данных, при которой сохраняется целостность данных. Наша организация данных исключает связи «многие-ко-многим», а значит наша реляционная схема приведена к третьей нормальной форме.

Если представить каждую сущность в нашей модели как отношение, то мы получили реляционную базу данных.

Нормализацией называется формальная процедура, в ходе которой атрибуты данных группируются в таблицы, а таблицы группируются в базу данных (БД).

Анализ предметной области обычно осуществляется на основании известных сведений о ней с учетом целей проектирования программной системы.

Базами данных называют электронные хранилища информации, доступ к которым осуществляется с помощью одного или нескольких компьютеров.

Обычно БД создается для хранения и доступа к данным, содержащим сведения о некоторой предметной области, т.е. некоторой области человеческой деятельности или области реального мира.

Для реляционной БД проектирование логической структуры заключается в том, чтобы разбить всю информацию по файлам (по отношениям), а также определить состав полей (атрибутов) для каждого из этих файлов

Единицей, хранящейся в БД информации, является таблица. Каждая таблица представляет собой совокупность строк и столбцов, где строки соответствуют экземпляру, а столбцы - атрибутам (признакам, характеристикам, параметрам объекта, события, явления).

В терминах БД столбцы таблицы называются полями, а ее строки - записями.

Нормализация выполняется поэтапно. Первые три шага были описаны доктором Э.Ф. Коддом в статье "Дальнейшая нормализация реляционной модели базы данных" в 1972г.

Первая нормальная форма (1НФ). Для нее требуется, чтобы таблица была плоской и не содержала повторяющихся групп. У плоской таблицы есть только две характеристики - длина (количество записей или строк) и ширина (количество полей или столбцов). Такая таблица не должна содержать ячеек, включающих несколько значений.

Никакую из систем управления базами данных (СУБД) не удовлетворяет только 1НФ, так как в этом случае необходимо определить большое число полей, многие из которых остаются в основном пустыми. Избыточные данные могут послужить причиной проблем целостности и снижение эффективности при внесении изменений, поэтому подобных решений при проектировании баз данных необходимо избегать.

Вторая нормальная форма (2НФ). Для 2НФ требуется, чтобы все поля таблицы зависели от первичного ключа, то есть, чтобы первичный ключ однозначно определял запись и не был избыточен.

В каждой таблице БД может существовать первичный ключ - поле или набор полей, однозначно идентифицирующий запись.

Значение первичного ключа в таблице БД должно быть уникальным, т.е. в таблице не должно существовать двух и более записей с одинаковым значением первичного ключа.

Те поля, которые зависят только от части первичного ключа, должны быть выделены в составе отдельных таблиц. 2НФ позволяет удалить большую часть повторяющихся данных, которые часто остаются после первого этапа нормализации.

Для третьей нормальной формы (ЗНФ) требуется, чтобы все не ключевые столбцы таблицы зависели от первичного ключа таблицы, но были независимы друг от друга. Для этого требуется, чтобы таблицы были приведены к 1НФ и 2НФ.

Отношение находится в четвертой нормальной форме (4НФ), если в нем не присутствуют функциональные многозначные зависимости. Для 4НФ требуется, чтобы в одной таблице не содержались независимые элементы данных, если между ними существует отношение "многие-ко-многим".

Если в отношении имеется много функциональных зависимостей, 4НФ не устраняет избыточность, то применяют пятую нормальную форму (ЗНФ).

Разложение отношений из 4НФ в пятую нормальную форму должно быть выполнено так, чтобы каждая проекция, полученная из 4НФ, содержала не менее одного возможного ключа и хотя бы один неключевой атрибут из исходного отношения. Для 5НФ требуется, чтобы можно было восстановить исходную таблицу на основе информации таблиц, на которые она была разбита. Кроме того, необходимо, чтобы таблицы соответствовали ЗНФ, а при наличии отношений "многие-ко-многим" - 4НФ.

Для большинства существующих СУБД необходимо представить проект базы данных в ЗНФ, так как этого вполне достаточно практически для всех обычных приложений. При разработке исключительно больших систем, когда необходимо максимальное сокращение объемов хранимых данных, желательно произвести дальнейшую нормализацию. Но в этом случае можно столкнуться с недостатками нормализации. Поскольку порог человеческого восприятия не позволяет одновременно анализировать большое число объектов с учетом их взаимосвязей, можно утверждать, что с увеличением числа нормализованных таблиц уменьшается целостное восприятие базы данных как системы взаимосвязанных данных. Другим недостатком нормализованной базы данных является необходимость считывать связанные данные из нескольких таблиц при выполнении одного запроса.

Наши описанные отношения приведены к третьей нормальной форме в процессе проектирования и потому переходим к следующему этапу выполнения задания.

Процесс нормализации можно сравнить с «восходящим» программированием, когда решаются мелкие задачи, а потом объединяются в комплекс больших, это более низкий уровень по сравнению с «нисходящим» программированием, когда логически ставится условие задачи, а затем постановка задачи разбивается на несколько подзадач.

В процессе проектирования, в ходе так называемого «нисходящего программирования» берутся неупорядоченные данные из плоской таблицы (1НФ, 2НФ), а затем строятся логические отношения (3НФ, 4НФ,5НФ). Мы применили нисходящее проектирование, заключающееся в том, что сразу запроектировали БД, отвечающую требованиям 3НФ.

3. Проектирование базы данных

Описание таблиц базы данных

Условно разделим таблицы баз данных на два типа:

Таблицы сущностей;

Таблицы ассоциаций;

Каждая таблица начинается с автоинкрементного поля типа Счетчик с одинаковым именем Kod. Эта автоматическая кодировка позволит обеспечить реляционную целостность данных, обеспечивая устойчивые связи. Каждая добавленная в базу данных запись, независимо в какой из таблиц, автоматически получает уникальный код, который затем используется в других таблицах. Необходимо отметить, что если удалить какую-то запись, то её уникальный код не будет присвоен новой созданной записи. Эта система кодировки позволяет увидеть процесс удаления записей.

3.1 Таблицы - сущности

Справочник TypePrepat содержит типы препаратов (мазь, таблетки, микстура, пищевые добавки и пр.);

Рисунок 4 - Конструктор таблицы TypePrepat

инфологический база данные реляционный

Справочник GrupPreparat содержит название групп препаратов (обезбаливающие, антибиотики, успокаивающие, сердечные, и пр.);

Рисунок 5 - Конструктор таблицы GrupPreparat

Справочник Rayons содержит названия районов

Рисунок 6 - Конструктор таблицы Rayons

справочник Preparat содержит список препаратов вместе с их характеристиками.

Рисунок 7 - Конструктор таблицы Preparat

справочник Apteki - справочник содержит справочные сведения об аптеках - название аптеки, номер аптеки, улица, дом, корпус, квартира, район, телефон.

Рисунок 8 - Конструктор таблицы Apteki

Опишем далее некоторые особенности заполнения таблиц-сущностей. В таблице Preparat необходимо, таким образом, вместо текстового названия типа препарата указывать код типа препарата, который является внешним ключом таблицы TypePrepat, вместо текстового названия группы препарата указывать код группы, который является внешним ключом таблицы GrupPreparat.

Такая же ситуация складывается и в таблице Apteki - вместо текстового названия района необходимо вписать числовое значение кода района, который является внешним ключом таблицы Rayons. Запись числовых значений кодов вместо текстовых значений гарантирует, что по кодам типа препарата, группы препарата никогда не будет потеряна при выборке необходимая запись, в отличие от текстовых указателей на тип и группу, поскольку нет никакой гарантии, что эти сведения будут указываться одинаково. Таким образом, в наборе значений группы препаратов могут появиться два значения «обезболивающие», «обезболивающие», при выборке группы «обезболивающие», записи с типом «обезболивающие» будут потеряны.

Но возникает трудность при заполнении таблицы Preparat - тогда при заполнении необходимо будет запоминать числовые коды группы препаратов. Так же необходимо будет поступить и с типами препаратов, и с районами в таблице Apteki. Чтобы устранить эту трудность, и вместе с тем, не потерять преимущества кодирования значений, воспользуемся Мастером подстановки. Опишем шаги мастера подстановки.

Поставим курсор в поле TypePrep, которое уже было сохранено как числовое и выберем в списке типов полей «Мастер подстановки». Возникнет окно «Создание подстановки». Так как мы собираемся использовать значения таблицы TypePrepat, то выберем первую опцию и нажмем Далее.

Рисунок 9 - Первый шаг мастера подстановок

На втором шаге Мастер подстановки предлагает выбрать таблицу или запрос, содержащие требуемые значения. Выберем таблицу TypePrepat, как показано на рисунке и нажмем кнопку Далее.

Рисунок 10 - Второй шаг Мастера подстановки

На этом шаге мастер подстановки предлагает выбрать значения, которые будут включены в столбец подстановки. Выберем поле TypePrep, нажав на кнопку «>» и поле TypePrep переместится из секции Доступные поля в секцию Выбранные поля.

Рисунок 11 - Третий шаг Мастера подстановки

В самом деле, нам необходимо текстовые значения типов препаратов, которые хранятся в вышеуказанном поле. Эти текстовые значения будут выбираться из списка, который мы сейчас создаем и подставляться в таблицу Preparat. Нажмем Далее. На этом шаге расширим столбец TypePrep, показанный на рисунке, поскольку эта ширина будет использоваться при заполнении основной таблицы Preparat.

Рисунок 12 - Четвертый шаг мастера подстановки

На заключительном шаге мастера подстановки нажмем кнопку Готово.

Рисунок 13 - Заключительный шаг мастера подстановки.

Мастер подстановки создает связь между таблицами TypePrepat и Preparat, связывая поле Kod из таблицы TypePrepat с полем TypePrep в таблице Preparat

Рисунок 14 - Предварительное сохранение таблицы перед созданием связи.

Если теперь открыть Схему данных, желая увидеть, какая была создана связь, то наблюдаем, что в схему данных добавились две указанные таблицы, соединенные описанной связью. Но связь никак пока не классифицирована «один-к-одному», «один-ко-многим». Сделаем это далее, совершенствуя базу данных.

Рисунок 15 - В результате Мастера Подстановки была создана связь

В результате работы мастера подстановки получаем: при заполнении таблицы Preparat в режиме «таблицы» появляется выпадающий список со значениями типов препаратов, которые выбираются из таблицы TypePrepat:

Рисунок 16 -- При заполнении используются значения из внешней таблицы TypePrep

Текстовые значения отображаются теперь в поле TypePrep, которое на самом деле в Конструкторе описано как числовое. На физическом уровне в таблице Preparat и хранятся в этом поле числовые значения кодов, но так как мы задали подстановку этого поля, то отображаются текстовые значения, соответствующие этим кодам.

Еще одна характерная особенность: если теперь добавить в таблицу TypePrepat еще какое-либо значение типа препарата, то оно автоматически отобразится в столбце подстановки в таблице Preparat поля TypePrep. Если удалить какую-либо запись в таблице-справочнике TypePrepat, то в столбце подстановок появится значение «#Удалено».

Аналогичным образом создадим подстановку для поля Gruppa таблицы Preparat из таблицы GrupPreparat, и для поля Rayon таблицы Apteki из таблицы Rayons.

3.2 Таблицы - ассоциации

Таблица, содержащая историю о динамике цен на препараты в аптеках.

Рисунок 17 - Конструктор таблицы динамики цен

Эта таблицы содержит код аптеки, код препарата, цену препарата, дату начала действия цены, дату окончания действия цены. Таким образом, KodApt+KodPrep - внешний составной ключ. Автоинкрементное поле Kod является внутренним ключом, ссылающийся на каждую добавляемую запись в этой таблице. При выборке данных будет выбираться цена, в зависимости от даты, указанной в выборке, то есть будет выбираться та запись, которая, помимо совпадения кода аптеки и кода препарата, будет удовлетворять условию попадания заданной даты в интервалы даты начала и даты окончания цены.

Таблица истории продаж препаратов по аптекам и остатков на складе аптеки каждый рабочий день аптеки -- isExist. Здесь, так же, как и в предыдущей таблице, внешним составным ключом является KodApt +KodPrep, внутренним ключом автоинкрементное поле Kod.

Поле WorkDay типа дата/время содержит значения дат рабочих дней. Таким образом, при выборке остатков на складе и продаж, помимо указания кодов аптеки и препарата необходимо указывать значение рабочей даты. Если значение рабочей даты не указывать, то будет проведена выборка по всем рабочим дням (динамика).

Рисунок 18 - Конструктор таблицы isExist

Для всех таблиц в каждом поле заполним свойство поле «Подпись», задав русскоязычные названия подписи поля. Это название выводится в заголовке таблицы в режиме таблицы вместо англоязычного названия поля.

Рисунок 19 - В заголовке таблицы вместо названий полей - подписи

В схеме данных настроим связи между этими таблицами, определив типы отношений следующим образом:

Добавляем таблицы, содержащие связи в схему данных. В нашем случае это все таблицы.

Создаем связь, перетаскивая мышью поля из одной таблицу в другую.

Щелкаем дважды по созданной связи, определяя тип связи, как показано на рисунке:

Рисунок 20 - Редактирование связи.

Зададим условия обеспечения целостности данных, каскадного обновления и каскадного удаления связанных записей, поставив необходимые флажки. Если в указанной связи нет коллизий, то Access позволит проставить флажки напротив этих условий, иначе выдаст сообщение, что это невозможно.

4. Схема данных

Рисунок 21. Схема данных с настроенными связями (все связи типа «один-ко-многим»)

5. Построение запросов к базе данных

Для построение запросов к базе данных будем использовать конструктор запросов, проверять работу запроса с помощью кнопки Выполнить запрос, если работа запроса удовлетворительна, то в меню Вид выбираем команду Режим SQL, где автоматически порождается код запроса на языке SQL.

5.1 Запрос 1

Выбрать все аптеки, в которых есть в продаже определенный препарат.

Это запрос на выборку информации. Добавим на конструктор запроса таблицы Apteki, Price, Preparat, isExist.

Рисунок 22--Конструктор запроса 1.

Так как в схеме данных уже настроены связи, то таблицы отображаются вместе с настроенными связями. Правда, они добавляются на конструктор запроса, и выстраиваются в ряд слева направо, так что связи отображаются нечитаемым образом. Разместим таблицы так, чтобы связи между ними не пересекались, и таким образом, их было легко анализировать. Иногда Access сам добавляет связи к таблицам на том основании, что у них имеются поля с одинаковыми названиями. Например, связь между полем Kod одной таблицы и полем Kod другой таблицы. Такую связь необходимо выделить и удалить, нажав Del. Такие «лишние» связи легко увидеть, поскольку они не имеют типа, тогда как в схеме данных, все типы связей были определены.

Добавляем поля на запрос из различных таблиц, щелкая дважды по необходимому полю в макетах таблиц верхней части окна. Поля таблиц добавляются в конструктор запроса, также выстраиваясь слева направо.

Зададим условия отбора в поле KolVoOstat таблицы isExist « >0», так как требуемый запрос на то, что в продаже есть необходимый препарат, а также зададим значение рабочей даты. Так как дату необходимо будет вводить всякий раз при запуске этого запроса, и, чтобы не вписывать всякий раз в конструктор запроса необходимую дату настроим параметр запроса [Введите дату] и зарегистрируем этот параметр в окне Запросы Параметры (окно Параметры Запроса)

Рисунок 23 - Параметры запроса

Здесь также регистрируется параметр [Часть названия препарата], поскольку препараты имеют сложные названия и легче в выборке указывать часть названия. Так как в запросе требуется отобразить определенный препарат, то мы вынуждены будем всякий раз в строке условие отбора вводить новое значение части наименования препарата. Чтобы не переписывать текст запроса и настроим этот параметр. Таким образом, при запуске запроса будут выдаваться окна диалога с просьбой ввести необходимые значения:

Рисунок 24 - Задаем дату (первый параметр)

Рисунок 25 - Задаем часть названия препарата (второй параметр)

Таким образом, был написан параметрический запрос на выборку. При программировании такой запрос называется динамическим.

Приведем текст SQL-запроса, порожденный Аccess и который можно увидеть в меню ВидРежим SQL

PARAMETERS [Введите дату] DateTime, [Часть названия препарата] Text ( 255 );

SELECT Apteki.NameApt, Apteki.Street, Apteki.House, Apteki.Korp, Apteki.Flat, Apteki.Rayon, Apteki.Telefon, isExist.KolVoOstat, isExist.WorkDay, Price.Price, Preparat.Preparat

FROM Preparat INNER JOIN ((Apteki INNER JOIN isExist ON Apteki.Kod = isExist.KodApt) INNER JOIN Price ON Apteki.Kod = Price.KodApt) ON (Preparat.Kod = Price.KodPrep) AND (Preparat.Kod = isExist.KodPrep)

WHERE (((isExist.KolVoOstat)>0) AND ((isExist.WorkDay)=[Введите дату]) AND ((Preparat.Preparat) Like "*" & [Часть названия препарата] & "*") AND ((Price.DateBeg)<=[Введите дату]) AND ((Price.DateEnd)>=[Введите дату]))

ORDER BY Apteki.NameApt;

Зачем необходима эта опция? Ведь запрос и так хорошо работает, будучи сконструированным и сохраненным как запрос. Текст SQL- запроса можно скопировать и разместить его, программируя какой-либо элемент управления данными при создании приложения, так чтобы текст SQL- запроса заработал как команда, обращаясь к базе данных из программы на каком-либо языке проектирования приложений (VB, Delphi). Создав запрос один раз, скопировав текст SQL-запроса в программу, можно больше не беспокоиться о том, чтобы запрос был сохранен в базе данных. Главное, чтобы имелись таблицы указанной структуры в базе данных. Команда запроса будет всегда возвращать результирующий набор данных.

В нашем случае, так как мы не используем программирование, чтобы запрос работал всегда, необходимо его сохранить. Сохраним его под именем Zapros1. Вызвав запрос на выполнение, то есть, щелкнув его дважды в окне базы данных, разделе Запросы, снова получим окна диалога с просьбой ввести рабочую дату и название части препарата. Введя другую дату и другое название части препарата, получаем новую выборку.

Результат запроса приведен в таблице.

Zapros1
Название	Улица	Дом	Корп	Кв.	Район	Телефон	Остаток	Рабочий день	Цена	Наименование препарата
Авиценна	Волоколамский пр-пт	12		0	Старый город	43-54-32	25	24.02.2011	10,21р.	Ампицилин таб. три 0,25гр № 10
Авиценна	Волоколамский пр-пт	12		0	Старый город	43-54-32	70	24.02.2011	28,17р.	Ампицилина(сус-зи 125мг/5мл ) 100мл
Авиценна	Волоколамский пр-пт	12		0	Старый город	43-54-32	89	24.02.2011	26,06р.	Ампицилин капс. 250 мг № 20
Авиценна	Волоколамский пр-пт	12		0	Старый город	43-54-32	82	24.02.2011	17,61р.	Ампицилин тригидрат таб. 0,5 №10
Аптека Зеленая	пр-кт Чайковского	31		0	Советский	65-34-87	69	24.02.2011	19,09р.	Ампицилин капс. 250 мг № 20
Аптека Зеленая	пр-кт Чайковского	31		0	Советский	65-34-87	50	24.02.2011	20,63р.	Ампицилина(сус-зи 125мг/5мл ) 100мл
Аптека Зеленая	пр-кт Чайковского	31		0	Советский	65-34-87	81	24.02.2011	38,17р.	Ампицилин капс. 500мг № 20
Аптека Зеленая	пр-кт Чайковского	31		0	Советский	65-34-87	21	24.02.2011	12,90р.	Ампицилин тригидрат таб. 0,5 №10
Аптека Пастера	ул. Фадеева	20		0	Заволжский	78-45-32	52	24.02.2011	24,53р.	Ампицилина(сус-зи 125мг/5мл ) 100мл
Аптека Пастера	ул. Фадеева	20		0	Заволжский	78-45-32	62	24.02.2011	45,39р.	Ампицилин капс. 500мг № 20
Аптека Пастера	ул. Фадеева	20		0	Заволжский	78-45-32	30	24.02.2011	22,69р.	Ампицилин капс. 250 мг № 20
Фармаспейс	Лениа	65		0	Микрорайон Березка	23-45-54	20	24.02.2011	41,86р.	Ампицилин капс. 500мг № 20
Фармаспейс	Лениа	65		0	Микрорайон Березка	23-45-54	44	24.02.2011	8,20р.	Ампицилин таб. три 0,25гр № 10
Фармаспейс	Лениа	65		0	Микрорайон Березка	23-45-54	30	24.02.2011	22,62р.	Ампицилина(сус-зи 125мг/5мл ) 100мл
Фармацент	Юбилейная	210		0	Микрорайон Березка	98-45-67	82	24.02.2011	17,09р.	Ампицилин тригидрат таб. 0,5 №10
Фармацент	Юбилейная	210		0	Микрорайон Березка	98-45-67	10	24.02.2011	25,29р.	Ампицилин капс. 250 мг № 20
Фармацент	Юбилейная	210		0	Микрорайон Березка	98-45-67	91	24.02.2011	27,34р.	Ампицилина(сус-зи 125мг/5мл ) 100мл
Фармацент	Юбилейная	210		0	Микрорайон Березка	98-45-67	46	24.02.2011	9,91р.	Ампицилин таб. три 0,25гр № 10
Фармация	ул. Горького	50		0	Центральный	89-76-45	85	24.02.2011	10,29р.	Ампицилин таб. три 0,25гр № 10
Фармация	ул. Горького	50		0	Центральный	89-76-45	41	24.02.2011	52,53р.	Ампицилин капс. 500мг № 20
Фармация	ул. Горького	50		0	Центральный	89-76-45	81	24.02.2011	17,75р.	Ампицилин тригидрат таб. 0,5 №10
Фармация	ул. Горького	50		0	Центральный	89-76-45	31	24.02.2011	28,40р.	Ампицилина(сус-зи 125мг/5мл ) 100мл

5.2 Запрос 2

Определить в каких аптеках дешевле всего «анальгин»

Сконструируем групповой запрос на выборку, нажав на панели инструментов кнопку «Групповые операции». В конструктор запроса будет добавлена строка «Групповая операция». Напротив поля цена зададим тип групповой операции Min, в полях Препарат (Preparat) и Имя аптеки (NameApt) зададим тип групповой операции: Группировка.

В полях Дата начала цены и дата окончания цены зададим тип групповой операции Условие:

Рисунок 26 -Здесь используется строка «групповые операции»

Зададим также сортировку по возрастанию по полю Цена (Price)

Рисунок 27 - Как и в предыдущем запросе используем параметр запроса

Таким образом, получаем параметрический групповой запрос. Приведем текст SQL - запроса:

PARAMETERS [Задайте дату] DateTime;

SELECT Min(Price.Price) AS [Min-Price], Preparat.Preparat, Apteki.NameApt

FROM Rayons INNER JOIN (Preparat INNER JOIN (Apteki INNER JOIN Price ON Apteki.Kod = Price.KodApt) ON Preparat.Kod = Price.KodPrep) ON Rayons.Kod = Apteki.Rayon

WHERE (((Price.DateBeg)<=[Задайте дату]) AND ((Price.DateEnd)>=[Задайте дату]))

GROUP BY Preparat.Preparat, Apteki.NameApt

HAVING (((Preparat.Preparat)="Анальгин"))

ORDER BY Min(Price.Price);

Результат работы запроса приведен в таблице:

Zapros2
Цена	Наименование препарата	Название
22,50р.	Анальгин	Аптека Зеленая
24,67р.	Анальгин	Фармаспейс
26,75р.	Анальгин	Аптека Пастера
29,81р.	Анальгин	Фармацент
30,71р.	Анальгин	Авиценна
30,96р.	Анальгин	Фармация

5.3 Запрос 3

Вывести динамику изменения цены на препараты группы «антибиотики» в аптеке №47.

Рисунок 28 - Обычный запрос на выборку

Так как таблица динамики цен Price была спроектирована таким образом, чтобы хранить изменение цен по препаратам и аптекам, то сейчас не составит труда сконструировать обычный запрос на выборку, задав условия «Номер аптеки=47» (Apteki!nomer=47), Препарат = «Анальгин», (Preparat!Preparat='анальгин') и поставив сортировку по возрастанию полей Дата начала цены, Дата окончания цены.

Текст SQL - запроса

SELECT Apteki.NameApt, Apteki.Nomer, Preparat.Preparat, Price.Price, Price.DateBeg, Price.DateEnd

FROM Preparat INNER JOIN (Apteki INNER JOIN Price ON Apteki.Kod = Price.KodApt) ON Preparat.Kod = Price.KodPrep

WHERE (((Apteki.Nomer)=47) AND ((Preparat.Preparat)="анальгин"))

ORDER BY Price.DateBeg, Price.DateEnd;

Результат SQL--запроса:

Zapros3
Название	Номер	Наименование препарата	Цена	Дата начала цены	Дата окончания цены
Аптека Зеленая	47	Анальгин	32,49р.	01.01.2010	31.01.2010
Аптека Зеленая	47	Анальгин	31,23р.	01.02.2010	28.02.2010
Аптека Зеленая	47	Анальгин	23,96р.	01.03.2010	31.03.2010
Аптека Зеленая	47	Анальгин	34,22р.	01.04.2010	30.04.2010
Аптека Зеленая	47	Анальгин	32,65р.	01.05.2010	31.05.2010
Аптека Зеленая	47	Анальгин	18,43р.	01.06.2010	30.06.2010
Аптека Зеленая	47	Анальгин	34,01р.	01.07.2010	31.07.2010
Аптека Зеленая	47	Анальгин	23,79р.	01.08.2010	31.08.2010
Аптека Зеленая	47	Анальгин	26,60р.	01.09.2010	30.09.2010
Аптека Зеленая	47	Анальгин	30,83р.	01.10.2010	31.10.2010
Аптека Зеленая	47	Анальгин	18,38р.	01.11.2010	30.11.2010
Аптека Зеленая	47	Анальгин	27,78р.	01.12.2010	31.12.2010
Аптека Зеленая	47	Анальгин	27,55р.	01.01.2011	31.01.2011
Аптека Зеленая	47	Анальгин	22,50р.	01.02.2011	01.01.2500

5.4 Запрос 4

Вывести все мази групп «обезболивающие», отпускаемые без рецепта и в каких аптеках они сейчас есть.

Рисунок 29 - Конструктор Запроса 4

Здесь приведен параметрический запрос на выборку, где в качестве параметра опять фигурирует рабочая дата. Чтобы не задавать значение кода препарата «мазь» к таблице Preparat, зададим текстовое значение условия «мазь» к таблице TypePrep и добавив ее на конструктор запроса. Так же поступим с группой препарата «Обезболивающие». К логическому полю Recept таблицы Preparat припишем условие «нет», к остатку (KolVoOstat) таблицы isExist добавим условие >0. Уберем флажки «Вывод на экран» напротив полей Тип препарата (TypePrep), Группа Препарата (GrupPreparat), Рецепт (Recept), чтобы эти значения не выводились на экран при выполнении запроса, так как их значения известны заранее.Запустим запрос на выполнение. Так как в параметрах запроса есть параметр, опять будет выдано окно диалога Введите дату. Введем дату 24.02.2011 и нажмем Ok.

Zapros4
Наименование препарата	Рабочий день	Остаток	Название	Улица	Дом	Корп	Кв.	Телефон	Район
Алмацид гель 200мл № 1	24.02.2011	100	Аптека Зеленая	пр-кт Чайковского	31		0	65-34-87	Советский
Алмацид гель 200мл № 1	24.02.2011	80	Фармация	ул. Горького	50		0	89-76-45	Центральный
Алмацид гель 200мл № 1	24.02.2011	20	Авиценна	Волоколамский пр-пт	12		0	43-54-32	Старый город
Алмацид гель 200мл № 1	24.02.2011	41	Фармацент	Юбилейная	210		0	98-45-67	Микрорайон Березка
Алмацид гель 200мл № 1	24.02.2011	80	Фармаспейс	Лениа	65		0	23-45-54	Микрорайон Березка
Анаферон мазь	24.02.2011	14	Аптека Пастера	ул. Фадеева	20		0	78-45-32	Заволжский
Анаферон мазь	24.02.2011	33	Аптека Зеленая	пр-кт Чайковского	31		0	65-34-87	Советский
Анаферон мазь	24.02.2011	93	Фармация	ул. Горького	50		0	89-76-45	Центральный
Анаферон мазь	24.02.2011	53	Авиценна	Волоколамский пр-пт	12		0	43-54-32	Старый город
Анаферон мазь	24.02.2011	53	Фармацент	Юбилейная	210		0	98-45-67	Микрорайон Березка
Анаферон мазь	24.02.2011	72	Фармаспейс	Лениа	65		0	23-45-54	Микрорайон Березка

Текст SQL - запроса:

PARAMETERS [Введите дату] DateTime;

SELECT Preparat.Preparat, isExist.WorkDay, isExist.KolVoOstat, Apteki.NameApt, Apteki.Street, Apteki.House, Apteki.Korp, Apteki.Flat, Apteki.Telefon, Apteki.Rayon

FROM TypePrepat INNER JOIN ((GrupPreparat INNER JOIN Preparat ON GrupPreparat.Kod = Preparat.Gruppa) INNER JOIN (Apteki INNER JOIN isExist ON Apteki.Kod = isExist.KodApt) ON Preparat.Kod = isExist.KodPrep) ON TypePrepat.Kod = Preparat.TypePrep

WHERE (((TypePrepat.TypePrep)="Мазь") AND ((GrupPreparat.GrupPreparat)="Обезболивающие") AND ((Preparat.Recept)=No) AND ((isExist.WorkDay)=[Введите дату]) AND ((isExist.KolVoOstat)>0));

Список использованных источников

1. Атре Ш. Структурный подход к организации баз данных. - М.: Финансы и статистика, 1983. - 320 с.

2. Бойко В.В., Савинков В.М. Проектирование баз данных информационных систем. - М.: Финансы и статистика, 1989. - 351 с.

3. Дейт К. Руководство по реляционной СУБД DB2. - М.: Финансы и статистика, 1988. - 320 с.

4. Джексон Г. Проектирование реляционных баз данных для использования с микроЭВМ. - М.: Мир, 1991. - 252 с.

5. Кириллов В.В. Структуризованный язык запросов (SQL). - СПб.: ИТМО, 1994. - 80 с.

6. Мартин Дж. Планирование развития автоматизированных систем. - М.: Финансы и статистика, 1984. - 196 с.

7. Мейер М. Теория реляционных баз данных. - М.: Мир, 1987. - 608 с.

8. Тиори Т., Фрай Дж. Проектирование структур баз данных. В 2 кн., - М.: Мир, 1985. Кн.1. - 287 с.: Кн.2. - 320 с.

9. Хаббард Дж. Автоматизированное проектирование баз данных. - М.: Мир, 1984. - 294 с.

10. Коннэл Дж. Visual Basic 6. Введение в программирование баз данных: Пер. с англ. - М.: ДМК, 2000--720 с.: ил. (Серия «для программистов»).

11. Сигел Ч. Изучи сам Access 97. Минск: Феникс, 1997г., 450 с. ил.

Размещено на Allbest.ru