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

ВСТУП

Трудова діяльність людини пов'язана зі сприйняттям і накопиченням інформації про навколишнє середовище, відбором і обробкою інформації при вирішенні різних завдань, обміном її з іншими людьми. Під інформацією розуміється сукупність знань про факти і залежності між ними. Таким чином, очевидною стає необхідність введення інформаційних систем для полегшення та систематизації трудової діяльності людини. Завдяки появі ПЕОМ стало можливе створення автоматизованих інформаційних систем (АІС).

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

У розвитку АІС намітилися два покоління. Перше покоління - інформаційні системи, що базуються на автономних файлах. Це системи з простою архітектурою й обмеженим колом можливостей. Вони складаються з набору автономних файлів і комплексу прикладних програм, призначених для обробки цих файлів і видачі документів. Такі системи мають ряд серйозних недоліків, що обмежили їх широке застосування: високу надмірність даних, складність ведення та спільної обробки файлів, залежність програм від даних Друге покоління - банки даних - система з високим ступенем інтеграції даних і автоматизації управління ними. Вони орієнтовані на колективне використання. Однією з відмінних особливостей банку даних є наявність спеціальних мовних і програмних засобів, що полегшують для користувачів виконання всіх операцій, пов'язаних з організацією зберігання даних, їх коригування та доступу до них. Така сукупність мовних і програмних засобів називається системою управління базою даних - СУБД.

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.