Лабораторная работа №3. Построение бд в Oracle средствами Oracle Designer (od) для индивидуальной предметной области. Обозначения

RON – компонент OD.

GLOBAL SHARED WORKAREA – выбранный элемент репозитория или диалога.

Edit – альтернатива меню или кнопка.

<имя предметной области> – вводимый с клавиатуры элемент.

Initial Password = * – задание значения для свойства в окне Properties или диалоговых окнах.

Задачи и методические указания

1. Запуск OD в среде Windows7:

   1. Запустить виртуальную машину с диска c: c:\Virtual Machinesс\WindowsXPOracle\WindowsXPProfessional -> Start This virtual machine;

   2. После загрузки виртуальной машины запустить OD.

2. Изменить пароль пользователя:

   1. Войти под старым паролем в PLSQL Developer (database = stu12)

   2. Открыть окно SQL – File -> New -> SQLWindow

   3. Выполнить команду

alter user имя пользователя identified by "новый пароль";

3. Создать в OD новую Application System:

   1. RON (Repository Object Navigator) -> GLOBAL SHARED WORKAREA -> Edit -> Create Child… -> Application Systems -> OK -> <имя предметной области>.

4. Создать в этой прикладной системе Oracle Database:

   1. RON -> <имя предметной области> -> Edit -> Create Child… -> Oracle Databases -> OK -> <имя БД (последний параметр окна приветствия Oracle Designer, т.е. stu12)>.

5. Создать в этой базе данных User:

   1. RON -> <имя предметной области> -> Oracle Databases -> <имя БД> -> Users -> Edit -> Create -> <имя пользователя (как при регистрации в OD)> -> Initial Password = * (Примечание: не нужно * заменять вашим паролем).

6. Создать в прикладной системе Domains для каких-нибудь атрибутов кроме первичных и внешних ключей (например, Телефоны):

   1. RON -> <имя предметной области> -> Edit -> Create Child… -> Domains -> OK -> <имя очередного домена>. В дальнейшем можно использовать эти домены для соответствующих атрибутов.

7. Построить ER-диаграмму (по ER-схеме утвержденной в лабораторной работе №2):

   1. Создать новую диаграмму ERD (Entity Relationship Diagrammer) -> File -> New -> Выбрать место хранения ERD-диаграммы.

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

   3. Задать естественные ключи, где необходимо определить ID-зависимость (при создании реляционной схемы во всех отношениях, где отсутствуют ключи, будут сгенерированны суррогатные первичные ключи).

   4. Проверить и при необходимости добавить все остальные виды ограничений целостности кроме Check Constraints (могут быть заданы только в реляционной модели), которые вы хотите отразить в реляционной модели.

   5. сохранить, напечатать ER-диаграмму.

8. Предоставить преподавателю (распечатанные на бумаге) старые ER-диаграмму, а также утвердить у него новую ER-схему в нотации Баркера (в дальнейшем необходимо иметь ее при себе при любых контактах с преподавателем по поводу выполнения последующих лабораторных работ).

9. Сгенерировать объекты реляционной схемы:

   1. DDT (Database Design Transformer) -> Run the Transformer in Default Mode = Yes -> All entities = Yes -> Settings ->

Закладка Database:

Database = <имя БД>

Database User = <имя пользователя>

Commit frequency for changes = After each phase

Закладка Keys:

Surrogate Keys

Create surrogate keys for all new tables = No -> OK -> Run.

10. Построить новую Server Model Diagram:

    1. DE -> <имя предметной области> -> Server Model Diagrams -> Edit -> Create -> Edit -> Include… -> Table Definitions -> OK.

11. При обнаружении ошибок в реляционной схеме c помощью RON внести нужные изменения в ER-схему и выполнить повторную генерацию:

    1. Удалить все редактируемые элементы репозитория типа Table Definitions (индексы, ключи, колонки или таблицы целиком).

    2. Для этих же таблиц удалить созданные последовательности (Sequence Definitions) для генерации значений суррогатных атрибутов.

    3. DDT (Database Design Transformer) -> Customize the Database Design Transformer = Yes -> Закладка Table Mappings - выбрать новые или редактируемые объекты -> Закладка Run Options – выбрать элементы для добавления и редактирования -> Run.

12. Внести изменения в Server Model Diagram. Нормализовать диаграмму, проверить и сохранить с тем же именем.

13. В RON или DE проверить и восстановить ограничения целостности (домены или типы (NUMBER, CHARACTER, VARCHAR2, DATE) и максимальные длины значений атрибутов, обязательность значений; первичные, возможные и внешние ключи; другие ограничения целостности (Check Constraints)).

14. Нормализовать, проверить, сохранить, напечатать Server Model Diagram и иметь ее при себе при любых контактах с преподавателем по поводу выполнения последующих лабораторных работ.

15. Создать базу данных в Oracle:

    1. DE -> <имя предметной области> -> Generate -> Generate Database from Server Model…

Закладка Target:

Database = Yes

Username = <имя пользователя в Oracle >

Password = <пароль пользователя в Oracle>

Connect = <имя БД>

Directory = <путь к каталогу, где есть права на запись (H:)>

Закладка Objects – проверить, что справа есть все таблицы и последовательности.

2. Start -> Execute DDL.

1. Запустить PL/SQL Developer (PD) и убедиться, что БД создана в Oracle, и проверить, что ее схема соответствует проектной реляционной схеме.

2. По мере необходимости (но при этом должно быть создано минимум по одному триггеру каждого типа) для таблиц создать триггеры следующих типов:

   1. Триггеры для генерации значений суррогатных ключей

CREATE OR REPLACE TRIGGER ВРАЧИ_B_I_R

BEFORE INSERT ON ВРАЧИ

FOR EACH ROW

BEGIN

SELECT ВРАЧ_SEQ.NEXTVAL INTO :NEW.ВРАЧ_ID FROM DUAL;

END;

2. Триггеры для ограничений целостности, которые не могут быть выражены декларативными средствами SQL

«В палате могут лежать только пациенты одного пола»

Понадобится дополнительная временная таблица

CREATE TABLE TEMP_ПАЛАТЫ

(НОМЕР_ПАЛАТЫ NUMBER(3,0) NOT NULL

БОЛЬНИЦА_ID NUMBER(10,0) NOT NULL )

Триггеры:

CREATE OR REPLACE TRIGGER РАЗМ_A_IU_R

AFTER INSERT OR

UPDATE OF ПАЛАТА_БОЛЬНИЦА_БОЛЬНИЦА_ID,

ПАЦИЕНТ_РЕГИСТРАЦИОННЫЙ_НОМЕР,

ПАЛАТА_НОМЕР_ПАЛАТЫ

ON РАЗМЕЩЕНИЯ

FOR EACH ROW

BEGIN

INSERT INTO TEMP_ПАЛАТЫ

VALUES (

:new.ПАЛАТА_НОМЕР_ПАЛАТЫ,

:new.ПАЛАТА_БОЛЬНИЦА_БОЛЬНИЦА_ID);

END;

CREATE OR REPLACE TRIGGER РАЗМ_A_IU_S

AFTER INSERT OR

UPDATE OF ПАЛАТА_НОМЕР_ПАЛАТЫ,

ПАЛАТА_БОЛЬНИЦА_БОЛЬНИЦА_ID,

ПАЦИЕНТ_РЕГИСТРАЦИОННЫЙ_НОМЕР

ON РАЗМЕЩЕНИЯ

DECLARE

CURSOR ПАЛ IS

SELECT DISTINCT БОЛЬНИЦА_ID, НОМЕР_ПАЛАТЫ

FROM TEMP_ПАЛАТЫ;

МИН_ПОЛ CHARACTER(10);

МАКС_ПОЛ CHARACTER(10);

BEGIN

FOR CUR_ПАЛ IN ПАЛ

LOOP

SELECT MIN(ПОЛ),MAX(ПОЛ)

INTO МИН_ПОЛ, МАКС_ПОЛ

FROM РАЗМЕЩЕНИЯ, ПАЦИЕНТЫ

WHERE

РАЗМЕЩЕНИЯ.ПАЛАТА_БОЛЬНИЦА_БОЛЬНИЦА_ID =

CUR_ПАЛ.БОЛЬНИЦА_ID AND

РАЗМЕЩЕНИЯ.ПАЛАТА_НОМЕР_ПАЛАТЫ =

CUR_ПАЛ.НОМЕР_ПАЛАТЫ AND

РАЗМЕЩЕНИЯ.ПАЦИЕНТ_РЕГИСТРАЦИОННЫЙ_НОМЕР =

ПАЦИЕНТЫ.РЕГИСТРАЦИОННЫЙ_НОМЕР;

IF МИН_ПОЛ <> МАКС_ПОЛ THEN

RAISE_APPLICATION_ERROR

(-20000, 'БОЛЬНИЦЫ-01 НАРУШЕНИЕ МОРАЛИ');

END IF;

END LOOP;

DELETE TEMP_ПАЛАТЫ;

END;

3. Триггеры для избыточных полей-агрегатов

«Число коек больницы равно сумме чисел коек ее палат»

Понадобится дополнительная временная таблица

CREATE TABLE TEMP_БОЛЬНИЦЫ

(БОЛЬНИЦА_ID NUMBER(10,0) NOT NULL)

Триггеры:

CREATE OR REPLACE TRIGGER ПАЛАТЫ_A_IUD_R

AFTER DELETE OR

INSERT OR

UPDATE OF ЧИСЛО_КОЕК, БОЛЬНИЦА_БОЛЬНИЦА_ID

ON ПАЛАТЫ

FOR EACH ROW

BEGIN IF DELETING THEN

INSERT INTO TEMP_БОЛЬНИЦЫ

VALUES (:old.БОЛЬНИЦА_БОЛЬНИЦА_ID);

ELSE

INSERT INTO TEMP_БОЛЬНИЦЫ

VALUES (:new.БОЛЬНИЦА_БОЛЬНИЦА_ID);

IF UPDATING AND

:old.БОЛЬНИЦА_БОЛЬНИЦА_ID <>

:new.БОЛЬНИЦА_БОЛЬНИЦА_ID THEN

INSERT INTO TEMP_БОЛЬНИЦЫ

VALUES (:old.БОЛЬНИЦА_БОЛЬНИЦА_ID);

END IF;

END IF;

END; CREATE OR REPLACE TRIGGER ПАЛАТЫ_A_IUD_S

AFTER DELETE OR

INSERT OR

UPDATE OF ЧИСЛО_КОЕК, БОЛЬНИЦА_БОЛЬНИЦА_ID

ON ПАЛАТЫ

DECLARE

CURSOR БОЛ IS

SELECT DISTINCT БОЛЬНИЦА_ID

FROM TEMP_БОЛЬНИЦЫ;

СУММА_ЧИСЛА_КОЕК_ПАЛАТ NUMBER(4);

BEGIN

FOR CUR_БОЛ IN БОЛ

LOOP

SELECT SUM(ЧИСЛО_КОЕК)

INTO СУММА_ЧИСЛА_КОЕК_ПАЛАТ

FROM ПАЛАТЫ

WHERE ПАЛАТЫ.БОЛЬНИЦА_БОЛЬНИЦА_ID =

CUR_БОЛ.БОЛЬНИЦА_ID;

UPDATE БОЛЬНИЦЫ

SET ЧИСЛО_КОЕК = СУММА_ЧИСЛА_КОЕК_ПАЛАТ

WHERE БОЛЬНИЦЫ.БОЛЬНИЦА_ID =

CUR_БОЛ.БОЛЬНИЦА_ID;

END LOOP;

DELETE TEMP_БОЛЬНИЦЫ;

END;

18. Процесс создания триггеров:

    1. DE -> <имя предметной области> -> Relational Table Definitions -> <имя таблицы> -> Triggers -> Edit -> Create -> Name of the trigger = <имя триггера> -> Next> -> <Указать моменты срабатывания триггера> -> Next> -> <Указать поля, при изменении которых будет срабатывать триггер> -> Next> -> Next> -> Create the trigger and then open the Logic Editor to edit the trigger’s PL/SQL definition = Yes -> Finish.

    2. В появившемся пустом окне ввести блок кода (BEGIN…END;) на PL/SQL.

    3. Запустить Generate Database from Server Model с опцией Generate Triggers.

Порядок срабатывания DML-триггеров и DML-операций:

1. Триггеры BEFORE уровня оператора DML

2. Для каждой строки, затронутой изменением:

   1. Строковые триггеры BEFORE

   2. Модифицирующая операция

   3. Строковые триггеры AFTER

3. Триггеры AFTER уровня оператора DML

19. Заполнить БД данными.