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

В этой книге для создания схемы базы данных использовалась программа Oracle SQL Developer Data Modeler. На рисунке 1.1 показан фрагмент схемы, созданной с помощью этой программы.

Рисунок 1.1. Фрагмент схемы базы данных

Буквой P отмечены столбцы, которые являются первичными ключами, а буквой F – столбцы, являющиеся внешними ключами. Связь между таблицами создается путем задания ограничения внешнего ключа.

Например, в приведенном фрагменте связь между таблицами Employees и Departments установлена путем определения ограничения внешнего ключа для столбца department_id в таблице Employees. Это означает что значение столбца department_id в таблице Employees должно совпадать с одним из значений одноименного столбца в таблице Departments или иметь значение NULL. За соблюдением этого ограничения будет следить СУБД и не допустит его нарушения. Буква U означает, что для этого столбца установлено ограничение уникальности значения. Это значит, что значение столбца не может повторяться. Символом * отмечены столбцы, которые не могут иметь значения NULL.

При создании SQL-запросов и программ PL/SQL нужно иметь четкое представление о структуре базы данных, с которой вы работаете, и знать бизнес-правила и ограничения, которые существуют в предметной области. Часть ограничений может быть реализована средствами языка определения данных, за их соблюдением будет следить СУБД и не допускать нарушения этих ограничений. Но существуют бизнес-правила и ограничения, которые должен отслеживать и обеспечивать программист.

Основу используемой в этой книге базы данных составляют таблицы демонстрационной базы СУБД Oracle Human Resources (HR). Human Resources – база данных, в которой хранятся данные отдела кадров некоторой компании. На рисунке 1.2 представлены основные таблицы этой базы данных и показаны связи между ними.

Рисунок 1.2. Основные таблицы базы данных Human Resources

Рассмотрим назначение этих таблиц и свойства некоторых столбцов. В таблице Employees содержатся данные о сотрудниках. Каждый сотрудник компании имеет уникальный идентификационный номер (employee_id), идентификационный номер должности (job_id), ставку заработной платы (salary) и менеджера (manager_id). Некоторые сотрудники в дополнение к зарплате получают комиссионные (commission_pct). Размер комиссионных определяется как часть от заработной платы. Столбец job_id используется для установления связи с таблицей Jobs, и для него определено ограничение внешнего ключа. Следствием этого является то, что значение данного столбца должно совпадать с одним из значений столбца job_id в таблице Jobs или иметь неопределенное значение NULL. Это ограничение обеспечивается средствами СУБД. Аналогичными свойствами обладает столбец department_id, который используется для установления связи с таблицей Departments.

В таблице Jobs содержится информация обо всех возможных должностях в организации. Каждая должность имеет уникальный идентификационный номер (job_id), наименование (job_title), минимальную (min_salary) и максимальную ставку заработной платы (max_salary).

Данные об отделах содержатся в таблице Departments. Каждый отдел имеет уникальный код (department_id), руководителя (manager_id), наименование (department_name), а также одно место расположения (location_id). Значение столбца manager_id должно совпадать со значением столбца employee_id в таблице Employees.

Эта компания имеет распределенную структуру, поэтому в таблице Locations хранятся данные о местонахождении отделов, которые состоят из адреса (street_address), почтового индекса (postal_code), названия города (city), названия штата (state_province) и кода страны (country_id). В таблице Locations также содержатся данные о населенных пунктах, в которых пока нет отделов.

Для того чтобы расширить спектр рассматриваемых задач, к уже рассмотренным таблицам были добавлены таблицы: Products, Orders, Customers Эти таблицы используются во многих демонстрационных базах. После добавления этих таблиц была получена схема базы данных HR_POC, используемая в этой книге, рисунок 1.3. Неиспользуемые таблицы были удалены. Ссылка для скачивания этой схемы: HR_POC (https://yadi.sk/d/_cFzi0CMazFIdg).

При решении некоторых задач, рассматриваемых в этой книге, в базу данных были добавлены вспомогательные таблицы. Ссылка для скачивания схемы, которая содержит вспомогательные таблицы:HR_POC_T (https://yadi.sk/d/a3XxApDuj2Ksxw).

Рисунок 1.3. Схема базы данных HR_POC

Разберем назначение некоторых столбцов в таблицах Products, Orders, Customers и сформулируем бизнес-правила, которые могут быть определены с их использованием.

Столбец status в таблице Orders определяет состояние заказа и может принимать следующие значения: Pending – «в ожидании», Shipped – «отправлен», Canceled – «отменен». Используя этот столбец, сформулируем следующее бизнес-правило: можно изменить содержимое заказа, который находится в состоянии Pending, но нельзя изменить содержимое заказа, который находится в состоянии Shipped

Столбец credit_limit в таблице Customers содержит значение кредитного лимита клиента. Используя этот столбец можно сформулировать следующее правило: запретить оформление заказа, если общая сумма заказов клиента, находящихся в состоянии Pending, превышает его кредитный лимит. В этих таблицах отсутствуют данные об оплате заказов, поэтому будем считать, что заказы, находящиеся в состоянии Shipped, оплачены, а заказы, находящиеся в состоянии Pending, – нет.

Столбец price в таблице Products содержит текущую цену товара, а столбец unit_price в таблице Order_Items – цену, по которой он был продан. Разница между этими значениями может возникать из-за того, что клиенту предоставлена скидка. Также со временем значение price может измениться, а значение unit_price – нет.

В таблицу Employees был добавлен столбец rating_e. Значение элементов этого столбца целочисленные и должны лежать в диапазоне от 1 до 5. Будем считать, что значение столбца rating_e отражает квалификацию сотрудника.

В таблице Products содержится столбец rating_p. Значения элементов этого столбца также должны лежать в диапазоне от 1 до 5 и отражают сложность товара.

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

Oracle SQL Developer

Oracle SQL Developer – это визуальная среда для создания, отладки и выполнения SQL-запросов и программ PL/SQL. Oracle SQL Developer позволяет создавать и редактировать объекты базы данных, управлять ими, импортировать и экспортировать данные, а также создавать всевозможные отчеты.

После запуска Oracle SQL Developer на экране появляется главное окно, представленное на рисунке 1.4.

Окно SQL Developer содержит три основные области:

– область Connections («Соединения») предназначена для создания соединений с базой данных;

– область Worksheet («Рабочее пространство») используется для ввода, редактирования и запуска запросов SQL и программ PL/SQL;

– область Reports («Отчеты») позволяет запускать предварительно определенные отчеты или создавать и добавлять собственные отчеты.

Рисунок 1.4. Главное окно Oracle SQL Developer

Для того чтобы приступить к работе с базой данных, необходимо создать соединение с ней. При запуске SQL Developer в области Connections отображаются все доступные соединения. Установить соединение можно только с существующей базой данных (схемой). Изначально после установки СУБД существует только схема администратора базы данных – system. Остальные схемы создает администратор. Имя схемы совпадает с именем пользователя. Команды создания пользователей и предоставления им привилегий будут рассмотрены позже.

Для создания нового соединения следует нажать кнопку New Connections, которая расположена на панели инструментов в области Connections. На экране появится диалоговое окно для создания подключений к базе данных (рисунок 1.5).

Имя соединения (Name) может быть произвольным, а имя пользователя и пароль должны быть предварительно заданы администратором. Имя пользователя должно начинаться с символов c##. Для одного пользователя (схемы базы данных) можно создать несколько соединений. При входе по любому из этих соединений вы будете работать с одной и той же базой данных. Значения Hostname и SID или Servce_name следует взять из файла tnsnames. ora в папке c:\app\user\product\18.0.0\dbhomexe\network\admin.

Рисунок 1.5. Окно для создания подключений к базе данных

После ввода всех параметров рекомендуется сначала нажать кнопку Test. Если проверка пройдет успешно, то в строке Status появится сообщение Success. В противном случае будет выведено сообщение об ошибке. Если ошибок не будет обнаружено, то следует нажать кнопку Connect и приступить к работе с базой данных.

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

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

Рассмотрим основные операции, которые можно выполнить, используя Oracle SQL Developer. Разберем случай, когда база данных уже создана, созданы таблицы и они заполнены данными. Нажав кнопку + рядом с именем соединения мы увидим объекты базы данных, для которой создано это соединение.

Самыми важными объектами базы данных являются таблицы. Для того чтобы увидеть список таблиц, существующих в базе данных, и получить возможность выполнять с таблицами различные действия, следует нажать кнопку + рядом с узлом Tables. Если после этого сделать двойной щелчок на имени таблицы, то в рабочей области появится окно, которое содержит несколько вкладок. Используя эти вкладки, можно просматривать данные о таблице и вносить в нее изменения.

На рисунке 1.6 показана вкладка Columns, в которой отображены столбцы таблицы Customers. Операции, которые вы можете выполнить с таблицей, содержатся в раскрывающемся списке Actions (рисунок 1.7).

Рисунок 1.6. Вкладка Columns

Рисунок 1.7. Раскрывающийся список Actions

На вкладке Data (рисунок 1.9), где отображается содержимое таблицы, можно просматривать и редактировать данные, содержащиеся в таблице.

Рисунок 1.9. Вкладка Data

На вкладке Model (рисунок 1.10) в графическом виде отображаются структура таблицы и ее связи с другими таблицами.

Рисунок 1.10. Вкладка Model

Использование рабочей области (SQL Worksheet)

При установлении соединения с базой данных автоматически открывается окно рабочей области (SQL Worksheet) для этого соединения. Это окно можно использовать для ввода, редактирования и выполнения операторов SQL и программ PL/SQL.

Для одного соединения можно создать несколько рабочих областей. Новую рабочую область можно создать, используя кнопку SQL Worksheet на панели инструментов или комбинацию клавиш Alt—F10. При создании новой рабочей области нужно выбрать соединение, для которого она будет использована. Рабочая область имеет собственную панель инструментов (рисунок 1.11).

Рисунок 1.11. Панель инструментов SQL Worksheet

Эта панель содержит значки, предназначенные для решения следующих задач:

– Run Statement: выполняет оператор, в котором находится курсор «переменные».

– Run Script: выполняет все операторы в рабочей области.

– Explain Plan: создает план выполнения. План выполнения – это последовательность операций, которые будут выполнены при выполнении оператора. План выполнения показывает исходное дерево строк с иерархией операций, составляющих оператор.

– Autotrace: генерирует информацию трассировки для оператора. Эта информация может помочь определить операторы SQL, которые выиграют от настройки.

– SQL Tuning Advisory: анализирует объемные операторы SQL и предлагает рекомендации по настройке.

– Commit: записывает любые изменения в базу данных и завершает транзакцию.

– Rollback: отменяет любые изменения в базе данных, не записывая их в базу данных, и завершает транзакцию.

– Unshared SQL Worksheet: создает новую рабочую область для соединения.

– To Upper / Lower / InitCap: изменяет выделенный текст на прописные, строчные или initcap соответственно.

– Clear: стирает оператор или операторы в поле «Ввести оператор SQL».

– SQL History: отображает диалоговое окно с информацией о выполненных операторах SQL.

Для выполнения оператора SQL, размещенного в рабочей обрасти, следует нажать кнопку Run Statement на панели инструментов, или функциональную клавишу F9. На рисунке 1.12 показан пример выполнения оператора SQL.

Если рабочая область содержит несколько операторов SQL или PL/SQL, то их можно выполнить, нажав кнопку Run Script на панели инструментов или функциональную клавишу F5. На рисунке 1.13 показан пример выполнения нескольких операторов SQL. Следует обратить внимание на то, что вешний вид результатов при использовании кнопки Run Script отличается от внешнего вида результатов при использовании кнопки Run Statement.

Примечание: кнопка Run Script обычно используется для запуска операторов PL/SQL.

Рисунок 1.12. Пример выполнения одного оператора SQL

Рисунок 1.13. Пример выполнения нескольких операторов SQL

Экспорт и сохранение результатов выполнения запроса

Если для запуска оператора SQL использовалась кнопка Run Statement, то результаты выполнения запроса можно сохранить в определенном формате для дальнейшего использования и обработки. Для того чтобы выполнить эту операцию, нужно щелкнуть правой кнопкой и в появившемся контекстном меню (рисунок 1.14) выбрать команду Export.

В результате этих действий будет запушен мастер экспорта. В первом окне (рисунок 1.15) нужно выбрать формат, месторасположение и имя файла. На рисунке 1.16 показано содержимое файла export. xls, который содержит результат выполнения запроса.

Рисунок.1.14. Выбор команды Export

Рисунок 1.15. Выбор формата, месторасположения и имени файла

Рисунок 1.16. Результат экспорта – файл в формате xls

Сохранение операторов SQL

Для того чтобы сохранить операторы SQL в текстовом файле, следует нажать кнопку Save на панели инструментов или выбрать команду меню File – Save. На экране появится диалоговое окно Save (рисунок 1.17), в котором можно выбрать место сохранения и ввести имя файла.

Рисунок 1.17. Диалоговое окно Save

Выполнить сохраненные операторы SQL можно двумя способами:

Первый способ. Используя команду меню File – Open, открыть сохраненный файл, содержащий операторы SQL, и выполнить эти операторы кнопками Run Statement или Run Script (рисунок 1.18.