Демонстрационные схемы

Мы будем использовать для примеров две схемы. Встроенную схему HR, которая представляет из себя пример приложения для управления человеческими ресурсами и схема WEBSTORE – пример приложения для обработки заказов. Схема HR может быть создана при создании базы данных либо быть создана позднее выполнив скрипты из Oracle Home.

 

Схемы HR и WEBSTORE

 

Схема HR состоит из семи таблиц, связанных первичными и внешними ключами. На рисунке 9-3 отображены отношения между таблицами как на диаграмме сущность-связь.

Два из отношений на рисунке 9-3 могут быть сразу не совсем понятны. Первое, это отношение один-ко-многим таблицы EMPLOYEE к таблице EMPLOYEE. Это self-referencing внешний ключ. Это значит что много сотрудников может быть связано с одним сотрудником, и это основано на том факте что у многих сотрудников может быть один менеджер, но менеджер это также сотрудник. Эта связь реализована за счёт столбца внешнего ключа manager_id и первичного ключа employee_id.

Вторая связь которая возможно требует дополнительного пояснения это двунаправленная  связь между DEPARTMENTS  и EMPLOYEES. Многие сотрудники могут работать в одном департаменте, и один сотрудник может быть менеджером многих департаментов.

В таблице 9-1 отображены столбцы таблиц схемы HR, используя описанную ранее нотацию для отображения первичных ключей (#), внешних ключей (\), обязательности (*) или необязательности (o) значения.

Таблицы в схеме

REGIONS содержит строки для георафических областей

COUNTRIES содержит строки для каждой страны, которые могут закрепляться за регионом

LOCATIONS содержат адреса, которые могут принадлежать стране

DEPARTAMENTS хранит информацию об отделах, с необязательным адресом и необязательным полем менеджера (менеджер должен существовать как сотрудник)

EMPLOYEES хранит строки для каждого сотрудника, каждому из которых должна быть назначена должность и необязательно менеджер и департамент. Менеджер в свою очередь должен быть в таблице сотрудников

JOBS содержит информацию о должностях в компании. Много сотрудников могут иметь одинаковую должность

JOB_HISTORY хранит информацию о предыдущих должностях сотрудника, уникально определяемую полями employee_id и start_date; сотруднику не может быть назначено несколько должностей в один момент времени. Каждая запись в истории ссылается на сотрудника с одной должностью в определённый момент и возможной принадлежности какому-либо отделу. Мы будем использовать HR схему для примеров и упражнений, так что она должна быть создана.

Схема WEBSTORE уже была создана если вы выполняли лабораторные работы. Если нет, мы определим отношения и связи и создадим схему и необходимые объекты. Схема WEBSTORE состоит из четырёх таблиц, связанных первичными и внешними ключами. На рисунке 9-4 покаданы связи между таблицами, как диаграмма сущность-связь.

Магазин управляет продуктами, покупателями и заказами в соответсвенно названных таблицах. Каждый заказ может состоять из нескольких продуктов с разным количеством – эти данные хранятся в таблице ORDER_ITEMS. Столбец order_item_id хранит номер для каждого уникального продукта в каждом заказе. Каждый заказ связан с одной или несколькими строками таблицы ORDER_ITEMS .

Таблицы схемы WEBSTORE

PRODUCTS хранит информацию о продуктах: описание, цена, статус и доступное количество.

CUSTOMERS содержит информацию о покупателях

ORDERS хранит информацию о заказах. Один покупатель может совершить много заказов. Заказ не может быть создан без существующего покупателя.

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

 

Создание демонстрационных схем

 

Если вы создавали БД используя лабораторное пособие то схема HR уже создана. Доступна опция создания демонстрационной схемы при создании базы с помощью DBCA.

Если схема не была создана в момент создания БД вы можете создать её выполнив скрипт установленный в домашнюю директорию БД. Этот скрип необходимо выполнить используя SQL *Plus иди SQL Developer с правами пользователя SYSDBA. Во время выполнения будут запрошены некоторые значения. Например в среде Linux вначале запустим SQL *Plus из командной строки ОС

root@> sqlplus / as sysdba

 

Доступны разные параметры для подключения к базе данных, но такая команда подключит вас к базе данных если вы работаете на той жемашине на которой установлен Oracle.

Затем выполните команду в SQL *Plus

 

SQL> @?/demo/schema/human_resources/hr_main.sql

 

Символ “?” это переменная которую SQL *Plus заменит на путь к домашней директории Oracle. Выведется запрос на пароль пользователя HR, табличное и временное пространство, пароль аккаунта SYS и путь куда записывать лог о выполнении скрипта. Обычно значения табличного пространства по умолчанию и временного пространтсва это USERS и TEMP, но они должны быть созданы перед запуском скрипта. После выполнения скрипта вы будете подключены к БД как пользователь HR. Чтобы убедиться в этом выполните команду

 

SQL> show user

 

Вы увидите что подключен аккаунт HR; затем выполните

 

select table_name from user_tables;

 

И вы увидите семть таблиц схемы HR.

Для создания схемы WEBSTORE (если она не создана в процессе выполнения лабораторных работ) выполните следующие запросы для создания необходимых объектов и добавления данных которые будут использоваться далее.

 

root@> sqlplus / as sysdba

 

SQL> create user webstore identified by oracle default tablespace users temporary tablespace temp quota unlimited on users;

SQL> grant create session, create table, create sequence to webstore;

SQL> ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;

 

SQL> connect webstore/oracle

 

SQL> create table customers(

customer_id number(8) not null constraint pk_customer_id primary key,

join_date date default sysdate not null,

customer_status varchar2(8) not null, customer_name varchar2(20) not null,

creditrating varchar2(10) not null, email varchar2(50) not null);

 

SQL> create table products(

product_id number(8) not null constraint pk_product_id primary key,

product_description varchar2(20) not null,

product_status varchar2(8) not null, price number(10,2) not null,

price_date date not null, stock_count number(8) not null);

 

SQL> create table orders(

order_id number(8) not null constraint pk_order_id primary key,

order_date date not null, order_status varchar2(8) not null,

order_amount number(10,2) not null,

customer_id number(8) constraint fk_customer_id references customers (customer_id));

 

SQL> create table order_items(

order_item_id number(8) not null,

order_id number(8) constraint fk_order_id references orders(order_id),

product_id number(8) constraint fk_prod_id references products(product_id),

quantity number);

 

SQL> create sequence cust_seq;

SQL> create sequence order_seq;

SQL> create sequence prod_seq;

 

После того как объекты созданы используйте следующие запросы которые запросят ввод данных для добавления строк в таблице, основываясь на данных в таблице 9-2.

 

insert into customers (customer_id, customer_status, customer_name, creditrating, email)

values (cust_seq.nextval, ‘&cust_status’, ‘&cust_name’, ‘&creditrating’, ‘&email’);

 

insert into products(product_id, product_description, product_status, price, price_date, stock_count)

values (prod_seq.nextval, ‘&product_description’, ‘ACTIVE’, &price, sysdate, &stock_count);

 

insert into orders(order_id, order_date, order_status, order_amount, customer_id)

values (order_seq.nextval, sysdate, ‘&order_status’, &order_amount, &customer_id);

 

insert into order_items values (&item_id, &order_id, &product_id, &quantity);

commit;