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

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ

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

1.1 Функциональная структура

2. ВЫБОР СУБД

3. ФИЗИЧЕСКАЯ МОДЕЛЬ

4. СЕРВЕРНОЕ ПРИЛОЖЕНИЕ

4.1 Хранимые процедуры

4.2 Триггеры (целостность данных, аудит, целостность приложения)

4.3 Пакеты

ЗАКЛЮЧЕНИЕ

СПИСОК ИСПОЛЬЗУЕМОЙ ЛИТЕРАТУРЫ



ВВЕДЕНИЕ

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

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

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

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

Для реализации данной задачи была выбрана СУБД ORACLE.



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

В рамках данного индивидуального задания рассматривается предметная область «Альпинистский клуб».

В рамках базы данных необходимо реализовать выполнение следующих требований:

1) Для каждой вершины показать группы, которые на нее восходили;

2) Предоставить возможность добавления новой вершины;

3) Предоставить возможность изменения данных о вершине;

4) Показать альпинистов, осуществляющих восхождение в указанный период дат;

5) Предоставить возможность добавления нового альпиниста;

6) Показать информацию о количестве восхождений каждого альпиниста на каждую гору;

7) Показать список восхождений, осуществлявшихся в указанный период времени;

8) Предоставить возможность добавления новой группы;

9) Предоставить информацию о том сколько, альпинистов побывали на каждой горе.



2. ВЫБОР СУБД

В данной курсовой работе в качестве СУБД, выбрана система управления базами данных Oracle 11g. Ниже приведена краткая информация о выбранной системе. Oracle Database 11g - база данных, разработанная специально для работы в сетях распределенных вычислений Grid, предназначенная для эффективного развертывания на базе различных типов оборудования, от небольших серверов до мощных симметричных многопроцессорных серверных систем, от отдельных кластеров до корпоративных распределенных вычислительных систем. СУБД предоставляет возможность автоматической настройки и управления, что делает ее использование простым и экономически выгодным.

- СУБД Oracle Database 11g поставляется в четырех различных редакциях, ориентированных на различные сценарии разработки и развертывания приложений. Кроме того, корпорация Oracle предлагает несколько дополнительных программных продуктов, расширяющих возможности Oracle Database 11g для работы с конкретными прикладными пакетами. Ниже перечислены существующие редакции СУБД Oracle Database 11g;

- Oracle Database 11g Standard Edition One характеризуется беспрецедентной простотой эксплуатации, мощью и выгодным соотношением цены и производительности для приложений масштаба рабочих групп, отдельных подразделений или приложений, работающих в среде интернет/интранет. Работая в различных средах, начиная от односерверных конфигураций для малого бизнеса и заканчивая распределенными средами крупных филиалов, Oracle Database 11g Standard Edition One обладает всеми функциональными возможностями для обеспечения работы критических для бизнеса приложений. Редакция Standard Edition One лицензируется только для серверов, имеющих не более двух процессоров;

- Oracle Database Standard Edition 11g (SE) обеспечивает столь же беспрецедентную простоту эксплуатации, мощь и производительность, что и редакция Standard Edition One, поддерживая работу более мощных вычислительных систем с использованием технологии кластеризации сервисов Real Application Clusters. Эта редакция лицензируется для использования на одном сервере с числом процессоров, не превышающим четырех, или на серверном кластере, поддерживающем не более четырех процессоров;

- Oracle Database 11g Enterprise Edition (EE) обеспечивает эффективное, надежное и безопасное управление данными таких критически важных для бизнеса приложений, как онлайновые среды, выполняющие масштабную обработку транзакций (OLTP), хранилища данных с высокой интенсивностью потока запросов, а также ресурсоемкие интернет-приложения. Редакция Oracle Database Enterprise Edition предоставляет инструментальные средства и функции, обеспечивающие соответствие требованиям современных корпоративных приложений в области доступности и масштабируемости. Эта редакция содержит все компоненты Oracle Database, а также допускает расширение посредством приобретения дополнительных модулей и приложений, описанных далее в этой статье;

- Oracle Database 11g Personal Edition поддерживает однопользовательскую разработку и развертывание приложений, полностью совместимых с редакциями Oracle Database Standard Edition One, Oracle Database Standard Edition и Oracle Database Enterprise Edition. Предоставив отдельным пользователям мощную функциональность пакета Oracle Database 11g, корпорация Oracle создала базу данных, сочетающую мощь популярнейшей в мире СУБД и простоту эксплуатации, которую вы вправе ожидать от приложения для настольного ПК. Расширенные средства самоуправления и автоматизации. Функции обеспечения управляемости в Oracle Database 11g призваны помочь организациями легко управлять корпоративными grid-инфраструктурами и отвечать ожиданиям пользователей, связанным с уровнями обслуживания. Oracle Database 11g предлагает расширенные функции самоуправления и автоматизации, которые помогут клиентам сокращать затраты на управление системами, повышая производительность, масштабируемость, доступность и безопасности приложений для баз данных. Новые средства обеспечения управляемости в Oracle Database 11g включают автоматическую настройку SQL и области памяти, новый компонент Partitioning Advisor, предлагающий администраторам рекомендации по секционированию таблиц и индексов для повышения производительности, а также улучшенные средства диагностики производительности для кластерных баз данных. Кроме того, Oracle Database 11g включает новый компонент Support Workbench, который предлагает простой в использовании интерфейс, представляющий администраторам сведения о проблемах, возникающих при работе СУБД, вместе с информацией о том, как можно быстро решить эти проблемы. Максимальная доступность информации. Всегда являясь лидером отрасли по обеспечению защиты приложений для баз данных от плановых и внеплановых простоев, Oracle укрепляет лидерство с Oracle Database 11g. Администраторам стало проще отвечать ожиданиям пользователей, связанным с уровнем доступности информации. В новой версии появились новые функции обеспечения высокой надежности, такие как: Oracle Flashback Transaction (упрощает откат транзакции, сделанной по ошибке, а также любых связанных транзакций), Parallel Backup and Restore (повышает скорость резервного копирования и восстановления баз данных) и Hot Patching (повышает уровень доступности систем благодаря применению пакетов обновлений без остановки СУБД). Кроме того, новый компонент Data Recovery Advisor позволяет администраторам значительно ускорить процедуры восстановления, автоматизируя процесс исследования проблем, предлагая план восстановления и учитывая сбои, предшествующие данному сбою.



3. ФИЗИЧЕСКАЯ МОДЕЛЬ

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

Типы данных, используемые в моей БД «Альпинистский клуб» представлены в таблице 1.

Таблица 1 - Типы данных

Тип	Наим.тип	Размер (байты)	Содержание
Текстовый	varchar2	каждый символ по 1	Буквы, цифры, спец. символы(%, &, #)
Числовой	Integer	4	Используется, чтобы выполнять арифметические операции над значениями из этого поля
Дата	Date	8	Даты до 31 декабря 9999 года

1) Таблица «Альпинисты»

CREATE TABLE ALPINISTI(ID_ALPINISTA INT PRIMARY KEY, FIO VARCHAR2(500), ADRESS VARCHAR2(200), ID_GROUP INT);

2) Таблица «Горы»

CREATE TABLE GORI(ID_GORI INT PRIMARY KEY, NAME_GORI VARCHAR2(200), VISOTA INT, COUNTRY VARCHAR2(200), RAYON VARCHAR2(200), SLOGNOST VARCHAR2(200));

3) Таблица «Восхождения»

CREATE TABLE VOSHOGDENIE(ID_VOSHOGDENIYA INT PRIMARY KEY,FIRST_DATE DATE, SECOND_DATE DATE, ID_GROUP INT, ID_MOUNT INT,STATUS VARCHAR2(200));

4) Таблица «Группы»

CREATE TABLE GROUPP(ID_GROUP INT PRIMARY KEY, INSTRUCTOR VARCHAR2(200), COLICHESTVO INT);

На рисунках 1-4 изображены примеры обозначения сущностей и их атрибутов.

Рисунок 1 - Атрибуты сущности «Альпинисты»

Рисунок 2 - Атрибуты сущности «Горы»

Рисунок 3 - Атрибуты сущности «Группы»



Рисунок 4 - Атрибуты сущности «Восхождение»



4. СЕРВЕРНОЕ ПРИЛОЖЕНИЕ

4.1 Хранимые процедуры

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

Хранимая процедура TakeGroupInTime, показывает группы восходившие на гору в промежуток дат:

CREATE OR REPLACE PROCEDURE TakeGroupInTime(argFirstDate Date,argSecondDate Date, idMount int

is

type curs is ref cursor;

myCurs curs;

MOUNTAIN VARCHAR2(200);

FIRST_DATEbuf VARCHAR2(200);

SECOND_DATEbuf VARCHAR2(200);

ID_GROUPbuf VARCHAR2(200);

begin

SELECT NAME_GORI INTO MOUNTAIN FROM GORI WHERE ID_GORI = IDMOUNT;

open myCurs for SELECT FIRST_DATE,SECOND_DATE,ID_GROUP FROM VOSHOGDENIE WHERE ID_MOUNT=idMount AND FIRST_DATE BETWEEN argFirstDate AND argSecondDate;

LOOP

FETCH myCurs INTO FIRST_DATEbuf,SECOND_DATEbuf,ID_GROUPbuf;

exit when myCurs%notfound;

DBMS_OUTPUT.PUT_LINE(MOUNTAIN||'||'||FIRST_DATEbuf||'||'||SECOND_DATEbuf||'||'||ID_GROUPbuf);

END LOOP;

end;

exec TakeGroupInTime(TO_DATE('2010/03/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),TO_DATE('2015/04/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),1);

Рисунок 5 - Результат выполнения хранимой процедуры «TakeGroupInTime»

Хранимая процедура AddMount для добавления новой вершины:

CREATE OR REPLACE PROCEDURE AddMount(argName varchar2,argVisota int,argCountry varchar2,argRayon varchar2,argDifficult varchar2)

is

maxId int;

begin

SELECT MAX(ID_GORI) INTO maxId FROM GORI;

maxId:= maxId+1;

INSERT INTO GORI VALUES (maxId,argName,argVisota,argCountry,argRayon,argDifficult);

DBMS_OUTPUT.PUT_LINE('Гора "'||argName||'" добавлена');

end;

exec AddMount('Арарат',5137,'Турция','Армянское нагорье','Средняя');

Рисунок 6 - Результат выполнения хранимой процедуры AddMount

Рисунок 7 - Таблица «Горы» после выполнения процедуры AddMount

Хранимая процедура ChangeMount позволяет вносить изменения в данные о вершинах:

CREATE OR REPLACE PROCEDURE ChangeMount(argIdGori int,argName varchar2,argVisota int,argCountry varchar2,argRayon varchar2,argDifficult varchar2

is

begin

UPDATE GORI SET NAME_GORI=argName, VISOTA=argVisota, COUNTRY=argCountry, RAYON = argRayon,SLOGNOST = argDifficult WHERE ID_GORI=argIdGori;

DBMS_OUTPUT.PUT_LINE('Гора "'||argName||'" изменена');

end;

exec ChangeMount(5,'Арарат',5137,'Турция','Армянское нагорье','Простая');

Рисунок 8 - Результат выполнения хранимой процедуры ChangeMount

Рисунок 9 - Таблица «Горы» после выполнения процедуры ChangeMount

Хранимая процедура TakeAlpinForDate выводит альпинистов совершивших восхождение в промежуток дат:



CREATE OR REPLACE PROCEDURE TakeAlpinForDate(argFirstDate Date,argSecondDate Dateis

type curs is ref cursor;

myCurs curs;

myCurs2 curs;

MOUNTAIN VARCHAR2(200);

ID_MOUNTbuf int;

FIObuf VARCHAR2(200);

ID_GROUPbuf VARCHAR2(200);

FIRST_DATEbuf VARCHAR2(200);

begin

open myCurs for SELECT ID_GROUP,ID_MOUNT,FIRST_DATE FROM VOSHOGDENIE WHERE FIRST_DATE BETWEEN argFirstDate AND argSecondDate;

LOOP

FETCH myCurs INTO ID_GROUPbuf,ID_MOUNTbuf,FIRST_DATEbuf;

exit when myCurs%notfound;

open myCurs2 for SELECT FIO FROM ALPINISTI WHERE ID_GROUP = ID_GROUPbuf;

loop

fetch myCurs2 into FIObuf;

exit when myCurs2%notfound;

SELECT NAME_GORI INTO MOUNTAIN FROM GORI WHERE ID_GORI = ID_MOUNTbuf;

DBMS_OUTPUT.PUT_LINE(MOUNTAIN||'||'||FIObuf||'||'||FIRST_DATEbuf);

MOUNTAIN:=null;

end loop;

close myCurs2;

END LOOP;

close myCurs;

end;

Рисунок 10 - Результат выполнения хранимой процедуры TakeAlpinForDate

Хранимая процедура AddAlpinist позволяет добавить альпиниста:

CREATE OR REPLACE PROCEDURE AddAlpinist(argName varchar,argAdress varchar2,argGroup int)---- процедура добавления альпиниста

is

maxID int;

begin

SELECT MAX(ID_ALPINISTA) INTO maxID FROM ALPINISTI;

maxID:=maxID+1;

INSERT INTO ALPINISTI VALUES (maxID,argName,argAdress,argGroup);

DBMS_OUTPUT.PUT_LINE('Альпинист "'||argName||'" добавлен');

end;

Рисунок 11 - Результат выполнения хранимой процедуры AddAlpinist

Рисунок 12 - Таблица «Альпинисты» после выполнения процедуры AddAlpinist

Хранимая процедура CountVoshAlpin выводит количество восхождений для каждого альпиниста на каждую вершину:



CREATE OR REPLACE PROCEDURE CountVoshAlpinis

type curs is ref cursor;

myCurs1 curs;

myCurs2 curs;

FIObuf1 varchar2(500);

ID_GROUPbuf int;

COUNTbuf2 int;

NAmeGORIbuf3 varchar2(200);

idGoribuf int;

begin

open myCurs1 for SELECT FIO,ID_GROUP FROM ALPINISTI;

loop

fetch myCurs1 into FIObuf1,ID_GROUPbuf;

exit when myCurs1%notfound;

open myCurs2 for SELECT COUNT(*),ID_MOUNT FROM VOSHOGDENIE WHERE ID_GROUP=ID_GROUPbuf GROUP BY ID_MOUNT;

loop

fetch myCurs2 into COUNTbuf2,idGoribuf;

exit when myCurs2%notfound;

SELECT NAME_GORI INTO NAmeGORIbuf3 FROM GORI WHERE ID_GORI=idGoribuf;

DBMS_OUTPUT.PUT_LINE(FIObuf1||'||'||NAmeGORIbuf3||'||COUNTbuf

NAmeGORIbuf3:=null;

end loop;

idGoribuf:=null;

close myCurs2;

end loop;

close myCurs1;

end;

Рисунок 13 - Результат выполнения хранимой процедуры CountVoshAlpinis

Хранимая процедура TakeVoshForDAte выводит восхождения за определенный период:

CREATE OR REPLACE PROCEDURE TakeVoshForDAte(argFirstDate Date,argSecondDate Date)----процедура, выводит восхождения за определенный период

is

type curs is ref cursor;

myCurs curs;

buf1 int;

buf2 varchar2(300);

buf3 varchar2(300);

buf4 int;

buf5 int;

buf6 varchar2(300);

begin

open myCurs for SELECT * FROM VOSHOGDENIE WHERE FIRST_DATE BETWEEN argFirstDate AND argSecondDate;

loop

fetch myCurs into buf1,buf2,buf3,buf4,buf5,buf6;

exit when myCurs%notfound;

DBMS_OUTPUT.PUT_LINE(buf1||'||'||buf2||'||'||buf3||'||'||buf4||'||'||buf5||'||'||buf6);

end loop;

close myCurs;

end;

Хранимая процедура AddGroup позволяет добавить группу:

CREATE OR REPLACE PROCEDURE AddGroup(argInstractor varchar)

is

maxId int;

begin

SELECT MAX(ID_GROUP) INTO maxID FROM GROUPP;

maxId:= maxID+1;

INSERT INTO GROUPP VALUES (maxId,argInstractor,0);

DBMS_OUTPUT.PUT_LINE('Группа добавлена');

end;

4.2 Триггеры (целостность данных, аудит, целостность приложения)

Триггер (англ. trigger) -- это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.

Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события; например, до добавления записи) или AFTER (после события). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись (конечно, при условии, что событие -- не удаление записи). Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, на которой «висит» триггер, и т. п.).

Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

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

create or replace trigger countAlp

after insert or update or delete on ALPINISTI

DECLARE

type curs is ref cursor;

Mycur curs;

ids int;

dffdf int;

begin

open Mycur for SELECT count(*),ID_GROUP FROM ALPINISTI group by ID_GROUP;

loop

fetch Mycur into dffdf,ids;

exit when Mycur%notfound;

UPDATE GROUPP SET COLICHESTVO = dffdf WHERE ID_GROUP=ids;

end loop;

end;

2) Создадим триггер, который устанавливает статус восхождения в зависимости от текущей даты:

create or replace trigger StatusVoshogdeniya

after insert on VOSHOGDENIE

DECLARE

type curs is ref cursor;

Mycur curs;

first_d DATE;

second_d DATE;

ids int;

begin

open Mycur for SELECT ID_VOSHOGDENIYA,FIRST_DATE,SECOND_DATE FROM VOSHOGDENIE;

loop

fetch Mycur into ids,first_d,second_d;

exit when Mycur%notfound;

IF first_d>SYSDATE THEN

UPDATE VOSHOGDENIE SET STATUS = 'Планируется' WHERE ID_VOSHOGDENIYA=ids;

ELSIF second_d>SYSDATE THEN

UPDATE VOSHOGDENIE SET STATUS = 'Выполняется' WHERE ID_VOSHOGDENIYA=ids;

ELSE

UPDATE VOSHOGDENIE SET STATUS = 'Выполнено' WHERE ID_VOSHOGDENIYA=ids;

END IF;

end loop;

end;

4.3 Пакеты

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

Создание заголовка пакета:

create or replace package MainPackage

as

procedure TakeAlpinOnMount(argid_mount int);

end MainPackage;

Создание тела пакета:

create or replace package body MainPackage

is

PROCEDURE TakeAlpinOnMount(argid_mount int)

is

type curs is ref cursor;

myCurs curs;

count_ int;

all_count_ int:=0;

ids int;

name_ varchar2(200);

begin

open myCurs for SELECT ID_GROUP FROM VOSHOGDENIE WHERE ID_MOUNT=argid_mount;

loop

fetch myCurs into ids;

exit when myCurs%notfound;

SELECT COUNT(*) INTO count_ FROM ALPINISTI WHERE ALPINISTI.ID_GROUP=ids;

all_count_:=all_count_+count_;

end loop;

close myCurs;

SELECT NAME_GORI INTO name_ FROM GORI WHERE ID_GORI= argid_mount;

DBMS_OUTPUT.PUT_LINE(name_||'||'||all_count_);

end;

end MainPackage;



ЗАКЛЮЧЕНИЕ

В результате выполнения индивидуального задания спроектирована и полностью проанализирована предметная область «Альпинистский клуб».

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



СПИСОК ИСПОЛЬЗУЕМОЙ ЛИТЕРАТУРЫ

1. Черноморов Г.А. Базы данных в промышленных СУБД.

2. С.П.Воробьев, Т.С.Черноморова, С.Н.Широбокова Информационные системы и технологии: выпускная квалификационная работа. 2014-258 с.

3. Джейсон Прайс - Oracle Database 11g SQL. Операторы SQL и программы PL/SQL, 3-е издание, 2014 г. - 1020 стр.

4. Кевин Е. Кляйн, Дэниэл Кляйн, Брэнд Хант - SQL. Справочник - 2-е издание, Символ, 2012, - 786 стр.

5. Владимир Илюшечкин - Основы использования и проектирования баз данных., - 1-е издание, Юрайт, 2011,- 876 стр.

6. Роберт Дж. Мюллер - Проектирование баз данных и UML., 2-е издание, Лори, 2010., 432 стр.

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