Безопасность систем баз данных: Задания на лабораторные работы № 1-7. Вариант 11 (База данных "Аптека")

Лабораторная работа №1

Исходной информацией для построения ER-диаграммы базы данных является следующий перечень атрибутов:

·  регистрационный номер лекарства;

·  торговое название лекарства;

·  описание лекарства;

·  срок годности лекарства;

·  цена единицы товара;

·  дата прекращения поставки (если не определена, лекарство может поставляться);

·  регистрационный номер партии лекарственных средств;

·  дата поставки партии лекарственных средств в аптеку;

·  дата выпуска партии;

·  количество медикаментов в партии;

·  необходимое количество единиц запаса лекарства в аптеке;

·  название аптеки;

·  сведения о местоположении аптеки (город, адрес);

·  телефон аптеки;

·  наименование фирмы-производителя лекарств;

·  местоположение производителя (страна, город, адрес);

·  телефон производителя;

·  наименование фирмы-поставщика лекарств;

·  местоположение поставщика (страна, город, адрес);

·  телефон поставщика.

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

Лабораторная работа №3

Реализовать следующие запросы к БД.

1. Получить перечень медикаментов, находящихся в указанной аптеке, с истекшим сроком годности.

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

3. Получить перечень аптек, получивших указанное лекарственное средство в течение последнего месяца.

4. Получить сведения о количестве единиц указанного лекарства в указанной аптеке.

5. Получить статистическую информацию об общей стоимости каждого лекарственного средства (с упорядочением по наименованию лекарства). Реализовать запрос в виде представления:

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

7. Получить названия медикаментов, имеющих количество единиц в наличии менее 10 % от необходимого запаса (сведения собираются по всем аптекам, в результирующем наборе данных показывается название, местоположение и телефон аптеки). Реализовать запрос в виде представления.

8. Получить перечень медикаментов, поставка которых прекращена (с указанием названия фирмы-производителя и даты прекращения поставки). Реализовать запрос в виде представления.

9. Получить перечень отечественных производителей и поставщиков лекарственных средств. Реализовать запрос в виде представления. В последнем столбце представления напротив каждой фирмы должно стоять слово «Производитель» или «Поставщик».

Лабораторная работа №4

Реализовать в базе данных следующие функции и хранимые процедуры.

1. Функция, возвращающая перечень медикаментов, находящихся в указанной аптеке, с истекшим сроком годности. Параметры: страна, город, адрес, название аптеки.

2. Функция, возвращающая перечень медикаментов, полученных указанной аптекой от указанного поставщика и указанного изготовителя. Параметры: страна, город, адрес, название аптеки; название фирмы-производителя, название фирмы-поставщика.

3. Функция, возвращающая перечень аптек, получивших указанное лекарственное средство в течение последнего месяца. Параметр: наименование лекарственного средства.

4. Функция, возвращающая сведения о количестве единиц указанного лекарства в указанной аптеке. Параметры: наименование лекарственного средства; страна, город, адрес, название аптеки.

5. Процедура регистрации нового лекарственного средства.

Примечание. В момент регистрации сразу устанавливается связь между лекарством и производителем.

Входные параметры: регистрационный номер, торговое название, описание, срок годности лекарства, название фирмы-производителя.

Выходной параметр: код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – неверное название производителя.

6. Процедура регистрации новой партии лекарства.

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

Выходной параметр: код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – неверное название лекарства.

7. Процедура регистрации сведений о поставке лекарства в аптеку.

Входные параметры: номер партии, название и местоположение аптеки, дата поставки.

Выходной параметр: код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – неверный номер партии;

·  2 – неверные сведения об аптеке.

Лабораторная работа №6

Реализовать в базе данных следующие ограничения.

1. Регистрационный номер, название лекарства, номер партии лекарств, название аптеки, название производителя, название поставщика – уникальны и всегда известны.

2. Срок годности лекарства всегда известен, положительная величина.

3. Наименование категории – уникально и всегда определено.

4. Количество медикаментов в партии и необходимое количество запаса лекарства в аптеке – положительные величины.

5. Дата выпуска партии лекарств – более ранняя, чем дата поставки в аптеку.

6. Нельзя поставлять лекарство, если определена дата прекращения его поставки.

Лабораторная работа №7

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