Проектирование системы управления базой данных

ВВЕДЕНИЕ

Трудовая деятельность человека связана с восприятием и накоплением информации об окружающей среде, отбором и обработкой информации при решении различных задач, обменом ее с другими людьми. Под информацией понимается совокупность знаний о фактах и зависимостях между ними. Таким образом, очевидной становится необходимость введения информационных систем для облегчения и систематизации трудовой деятельности человека. Благодаря появлению ПЭВМ стало возможно создание автоматизированных информационных систем (АИС).

В целом под АИС понимают совокупность информационных массивов технических, программных и языковых средств, предназначенных для сбора, хранения, поиска, обработки и выдачи данных по запросам пользователей.

В развитии АИС наметились два поколения. Первое поколение - информационные системы, базирующиеся на автономных файлах. Это системы с простой архитектурой и ограниченным кругом возможностей. Они состоят из набора автономных файлов и комплекса прикладных программ, предназначенных для обработки этих файлов и выдачи документов. Такие системы имеют ряд серьезных недостатков, ограничивших их широкое применение: высокую избыточность данных, сложность ведения и совместной обработки файлов, зависимость программ от данных Второе поколение – банки данных – система с высокой степенью интеграции данных и автоматизации управления ими. Они ориентированы на коллективное использование. Одной из отличительных особенностей банка данных является наличие специальных языковых и программных средств, облегчающих для пользователей выполнение всех операций, связанных с организацией хранения данных, их корректировки и доступа к ним. Такая совокупность языковых и программных средств называется системой управления базой данных – СУБД.

1. ЗАДАНИЕ НА РАЗРАБОТКУ

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

a) Для каждой группы медикаментов необходимо хранить наименование.

b) Для каждого медикамента, принадлежащего какой-либо группе – наименование и единицу измерения.

c) Для каждой группы рецептур необходимо хранить наименование.

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

e) Каждый препарат изготовляется по какой-либо рецептуре.

f) Необходимо предоставить пользователю возможность изготовлять препараты из существующих медикаментов.

2. Реализовать разработанную схему данных при помощи SQL (подраздел DDL - «язык определения данных»). Реализация схемы данных должна содержать необходимые ограничения целостности.

3. Составить операторы SQL (подраздел DML - «язык манипулирования данными»), производящие добавление новой информации в базу данных, удаление или изменение существующей информации.

4. Составить операторы SQL, осуществляющие выбор из базы данных следующей информации:

a) Вывести информацию о медикаменте, присутствующем в наибольшем количестве рецептур.

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

c) Вывести список рецептур, в которых используется конкретный медикамент.

2. АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ

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

Таблица 1.

Каждый медикамент принадлежит какой-нибудь группе. Очевидно, одной группе может принадлежать несколько медикаментов, поэтому между этими сущностями существует связь «один-ко-многим» (1:M), которую можно изобразить следующим образом:

Каждый медикамент принадлежит какой-либо рецептуре. То есть, между сущностями «медикамент» и «рецептура» существует связь «один-ко-многим» (1:M):

Препарат изготавливается по какой-либо рецептуре из существующих медикаментов. Таким образом, между сущностями « группа рецептур» и «препарат» существует связь «один-ко-многим» (1:M):

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

3. РАЗРАБОТКА СХЕМЫ ДАННЫХ

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

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

Типы integer not null, varchar(*) not null, numeric(*,*) not null означают, что поля могут быть длинными целыми числами, не содержащими NULL. Тип varchar(*) означает, что поля содержат строку символов переменной длины. Тип numeric(*,*) означает, что поля содержат масштабируемые целые числа. Тип date означает, что поля содержат календарную дату.

Поля, выделенные на схеме данных курсивом, будут являться первичными ключами (PRIMARY KEY) таблиц.

Рисунок 1-Логическая схема реляционной базы данных

1) Таблица Group_receptures:

- поля ID, Name, не могут содержать NULL;

- поле ID содержит целые числа, поле Name - строка переменной длины.

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

create table Group_receptures

(IDinteger not null,

Name varchar(40) not null,

primary key (ID)

);

2) Таблица Group_medikaments:

- поля ID, Name, не могут содержать NULL;

- поле ID содержит целые числа, поле Name - строка переменной длины.

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

create table Group_medikaments

(IDinteger not null,

Name varchar(40) not null,

primary key (ID)

);

3) Таблица Medikamenty:

- поля ID, Name_med, Group_ID,Edinica не могут содержать NULL;

- поля ID, Group_ID содержат целые числа, поля Name и Edinica - строки переменной длины.

С учетом перечисленных требований оператор SQL, создающий таблицу выглядит следующим образом:

create table Medikamenty

(IDinteger not null,

Name_medvarchar(40) not null,

Group_ID integer not null,

Edinicavarchar(10) not null,

primary key (ID),

foreign key (Group_ID),

references Group_medikaments

);

4) Таблица Receptures:

- поля Name_ID, Preparat_ID не могут содержать NULL;

- поля Preparat_ID, Name_ID, Kol_vo содержат целые числа.

С учетом перечисленных требований оператор SQL, создающий таблицу выглядит следующим образом:

create table Receptures

(Preparat_ID integer not null,

Name_ID integer not null,

Kol_vo integer not null,

primary key (Preparat_ID,Name_ID),

foreign key (Preparat_ID),

references Preparat,

foreign key(Name_ID),

references Medikamenty

);

5) Таблица Preparat:

- все поля таблицы не могут содержать NULL;

- поля ID, Group_ID содержат целые числа, поле Name_preparat - строка переменной длины.

Оператор SQL, создающий таблицу Preparat :

create table Preparat

(IDinteger not null,

Group_ID integer not null,

Name_preparatvarchar(40) not null,

primary key (ID),

foreign key (Group_ID),

references Group_receptures

);

4. ВЕДЕНИЕ БАЗЫ ДАННЫХ

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

Добавление новых записей в таблицы производится при помощи оператора INSERT, удаление существующих записей - оператором DELETE, изменение - оператором UPDATE. Для удобства пользователя можно свести эти операторы вместе для каждой таблицы базы данных:

1) Таблица Group_receptures:

- добавление новой записи

insert into Group_receptures (Name)

values(:Name);

здесь и далее для всех таблиц вместо наименования поля с двоеточием перед ним (напр. «:Name») при выполнении оператора должно подставляться конкретное значение;

- удаление существующей записи

delete from Group_receptures where ID = value;

здесь и далее для всех таблиц вместо «value» при выполнении оператора должно подставляться конкретное значение;

- изменение существующей записи

update Group_receptures set Name = “value”

where ID = value2;

здесь и далее для всех таблиц вместо «value1, 2,...,n» при выполнении оператора должно подставляться конкретное значение.

2) Таблица Group_medikaments:

- добавление новой записи

insert into Group_medikaments (Name)

values(:Name);

- удаление существующей записи

delete from Group_medikaments where ID = value;

- изменение существующей записи

update Groups_medikaments set Name = “value1”

where ID = value2;

3) Таблица Medikamenty:

- добавление новой записи

insert into Medikamenty (Name_med, Group_ID, Edinica)

values (:Name_med, :Group_ID, :Edinica);

- удаление существующей записи

delete from Medikamenty where ID = value;

- изменение существующей записи

update Medikamenty set Name_med= “value1”, Edinica =” value2”,

where Group_ID = value4;

4) Таблица Receptures:

- добавление новой записи

insert into Receptures ( Preparat_ID, Name_ID, Kol_vo )

values( :Preparat_ID, :Name_ID, :Kol_vo );

- удаление существующей записи

delete from Receptures where Preparat_ID = value, Name_ID = value ;

- изменение существующей записи

update Receptures set Kol_vo= “value1”

where Preparat_ID = value2, Name_ID = value3;

5) Таблица Preparat:

- добавление новой записи

insert into Preparat ( Group_ID, Name_preparat )

values ( :Group_ID, :Name_preparat);

- удаление существующей записи

delete from Preparat where ID = value;

- изменение существующей записи

update Preparat set Name_preparat =” value1”,

where Group_ID = value3;

5. ВЫБОР ИНФОРМАЦИИ ИЗ БАЗЫ ДАННЫХ

Для выбора информации из базы данных используется оператор SELECT.

1) Вывести информацию о медикаменте, присутствующем в наибольшем количестве рецептур.

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

Обратимся к таблицам: Medikamenty, Receptures. Условие отбора строк - M.ID=R.Name_ID. Отобранные строки сортируем по M.Name_med. В подзапросе группируем по R.Name_ID. Искомые данные находятся в соответствии с условием, указанным в предложении HAVING.

SELECT M.Name_med, count(M.Name_med)

FROM Medikamenty AS M, Receptures AS R

WHERE M.ID=R.Name_ID

GROUP BY M.Name_med

HAVING count(R.Name_ID)>=all(select count(R.Name_ID)

FROM Receptures AS R Group by R.Name_ID);

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

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

Обратимся к таблицам: Medikamenty, Receptures, Preparat. Условие отбора строк – (M.ID=R.Name_ID and R.Preparat_ID=P.ID). Отобранные строки сортируем по R.Preparat_ID. В подзапросе группируем по

R.Preparat_ID. Искомые данные находятся в соответствии с условием, указанным в предложении HAVING.

SELECT R.Preparat_ID, count(R.Name_ID)

FROM Receptures AS R, Preparat AS P, Medikamenty AS M

WHERE (R.Preparat_ID=P.ID and R.Name_ID=M.ID)

GROUP BY R.Preparat_ID

HAVING count(R.Name_ID)>=all(SELECT count(R.Name_ID)

FROM Receptures R GROUP BY R.Preparat_ID);

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

Условие отбора строк указано в предложении WHERE. Оператор DISTINCT исключает одинаковые строки.

SELECT DISTINCT R.Preparat_ID

FROM Receptures AS R, Medikamenty AS M, Preparat AS P

WHERE (P.ID=R.Preparat_ID and M.ID=R.Name_ID and

M.Name_med='Метамизол натрия');

ЗАКЛЮЧЕНИЕ

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

СПИСОК ИСПОЛЬЗУЕМЫХ ИСТОЧНИКОВ

1 Озкарахан Э. Машины баз данных и управление базами данных. – 1989.

2 Джеймс Р. Грофф, Пол Н. Вайнберью. SQL: полное руководство. – 1998.

3 Диго С.М. Проектирование и использование баз данных. – 1995.