Лабораторная работа №2. Создание базы данных
Цель работы – получение навыков описания таблиц БД Oracle APEX и заполнения их данными.
Задачи:
Описать структуры простых таблиц БД.
Заполнить таблицы данными.
Создать связи между таблицами.
Для знакомства с технологией APEX в последующих работах решается ряд прикладных задач на БД, описывающей структуру некоторой торговой корпорации. Первоначальная схема БД, полученная с помощью СУБД Access, представлена на рис. 1.1 (некоторые таблицы будут добавляться по ходу выполнения лабораторных работ):
Рисунок 1.1. Логическая схема БД, представленная средствами СУБД Access
БД включает 5 основных таблиц (далее приведены описания этих таблиц в конструкторе СУБД Access):
Countries:
Departments:
Locations:
Employees:
Jobs:
Фрагменты самих таблиц, созданных в СУБД Access, показаны ниже. Они демонстрируют примеры данных, заполняющих БД:
COUNTRIES
|
country_id
|
country_name
|
11
|
Россия
|
…
|
…
|
LOCATIONS
|
location_id
|
city
|
country_id
|
postal_code
|
street_address
|
state_province
|
123
|
Калининград
|
11
|
123456
|
Советский пр.
|
Калининградская обл.
|
…
|
…
|
…
|
…
|
…
|
…
|
DEPARTMENTS
|
department_id
|
department_name
|
manager_id
|
location_id
|
111
|
Виктория
|
1
|
123
|
…
|
…
|
…
|
…
|
EMPLOYEES
|
employee_id
|
first_name
|
last_name
|
hire_date
|
salary
|
commission_pct
|
phone_number
|
job_id
|
department_id
|
1
|
Иванов
|
Иван
|
01.12.1989
|
12000
|
15
|
23-34-56
|
1
|
111
|
…
|
…
|
…
|
…
|
…
|
…
|
…
|
…
|
…
|
JOBS
|
job_id
|
job_title
|
1
|
управляющий
|
…
|
…
|
Между таблицами сформированы связи (показаны на рис. 1.1 стрелками), позволяющие соединить между собой данные в единую систему. Наличие таких связей обеспечивает целостность данных в системе. Так, при попытке удалить родительскую запись выводится диагностическое сообщение о невозможности данной операции, поскольку существуют связанные записи. Таким образом, для удаления родительской записи сначала нужно удалить все подчиненные ей.
Кроме основных таблиц, в схеме представлена вспомогательная таблица Departments_1, которая сформирована самой СУБД для представления связи между основными таблицами (формирование связей рассматривается далее в данной работе).
Создание простых таблиц
Рассмотрим для начала технологию создания простых, не связанных между собой, таблиц в APEX на примере таблиц Countries и Locations:
Запуск мастера создания таблиц:
Откройте SQL Workshop и запустите Object Browser.
Щелкните по кнопке Create в правом верхнем углу страницы. Отобразится список типов объектов БД, которые можно создать.
Щелкните по ссылке Table. Запустится мастер создания таблицы. Вверху в виде линейного графика отражаются шаги создания таблицы. Активный шаг – Columns.
В поле Table Name введите название таблицы - Countries:
Поля Column Name (Имя столбца), Type (Тип Данных), Precision (Точность, Максимальный размер), Scale (Размер, Количество знаков после запятой), Not Null (Обязательное) предназначены для описания столбцов создаваемой таблицы. Добавьте описания следующих столбцов:
Столбец с названием country_id, тип данных – Number(4), обязательный. В этом столбце будет храниться уникальный числовой идентификатор страны.
Столбец с названием country_name, тип данных – Varchar2(20), обязательный. В этом столбце будет храниться название страны.
Нажмите Next>.
На шаге Primary Key:
В поле Primary Key выберите Populated from a new sequence (значения столбца первичного ключа будут браться из нового объекта-последовательности). Последовательность (Sequence) – это объект БД, который используется для генерации уникальных числовых значений.
В полях Primary Key Constraint Name (Название ограничения целостности первичного ключа) и Sequence Name (Название последовательности) оставьте значения по умолчанию, сгенерированные APEX.
В поле Primary Key (Первичный ключ) выберите столбец country_id.
Нажмите Next>.
Шаг Foreign Key (Внешний ключ) пропустите (т.е. нажмите Next>), т.к. в создаваемой таблице пока нет ссылок на другие таблицы.
На шаге Constraints (Ограничения целостности) добавьте уникальный ключ, определенный на столбце country_name:
Выберите радиокнопку Unique (Уникальный ключ).
В появившееся ниже поле-список Key Column(s) (Столбцы ключа) перенесите столбец country_name.
В поле Name должно быть указано название создаваемого ограничения целостности. Это имя должно быть уникальным в БД. Оставьте значение, предлагаемое по умолчанию.
Нажмите кнопку Add, чтобы добавить ограничение в список ограничений целостности создаваемой таблицы.
Нажмите Next>.
На шаге Confirm можно просмотреть сгенерированный мастером SQL-скрипт по созданию таблицы, щелкнув по ссылке  . Подтвердите создание таблицы, нажав на кнопку Create Table.
Созданная таблица появится в списке таблиц. Для выбранной таблицы в центральной части Object Browser отображается ее детальное описание, организованное в виде вкладок (Table, Data, Indexes, Model, Constraints, …). На каждой вкладке представлены кнопки с операциями, которые можно совершать над таблицей (например, на вкладке Table: Add Column, Modify Column и т.д.).
Изучите содержимое вкладок описания созданной таблицы.
Найдите и изучите описание созданного объекта-последовательности (Sequences).
Аналогичным образом создайте новую таблицу, предназначенную для хранения сведений о местах размещения отделений торговой корпорации:
Название таблицы - Locations.
В таблице должны быть определены столбцы следующим образом:
location_id - Number(4),обязательный,
city - Varchar2(40),обязательный,
country_id - Number(4),
postal_code – Number(6),
street_address - Varchar2(40),
state_province - Varchar2(40)
Обратите внимание на столбец country_id: при формировании связей между таблицами он будет содержать ссылку на страну (на запись в соответствующей таблице). Поэтому он должен иметь тот же тип данных и размер, что и столбец первичного ключа в созданной ранее таблице countries.
В первичный ключ должен входить столбец location_id, значения для него должны браться из новой последовательности, которая должна быть создана.
Изучите содержимое вкладок описания созданной таблицы. Обратите внимание на вкладку Model. На ней отображаются таблицы, на которые ссылается данная таблица, и которые ссылаются на данную таблицу. Поскольку мы создали простые, не связанные таблицы, в окне показана отдельная таблица.
Ниже в качестве справочного материала приведены некоторые соответствия между типами полей в Access и APEX:
-
Тип поля Access
|
Тип поля Oracle
|
Числовой, все форматы, в том числе:
Счетчик, длинное целое,
Действительное
Денежный
|
NUMBER — числовые данные
|
Текстовое
|
VARCHAR 2 — текстовые строки переменной длины до 4000байт
|
Поле MEMO
|
LONG - текстовые строки длиной до 2 ГБ
CLOB – тексты длиной до 4 Гб
|
Дата/время
|
DATE - даты
TIME - время
|
Заполнение таблиц
Заполнение таблиц Oracle возможно двумя способами: путем импортирования данных из готовых Excel-таблиц (при этом возможно также и создание таблиц); путем добавления данных в таблицы в среде APEX.
Если данными заполняются простые таблицы, проблем нет. При заполнении связанных таблиц надо сначала заполнить родительскую таблицу, а затем - подчиненную.
Импортирование данных из Excel-таблиц
Для применения данной технологии структура таблицы в БД Oracle должна совпадать со структурой таблицы в Excel.
Для импортирования следует выполнить команды:
SQL WorkshopUtilitiesData WorkshopSpredsheet Data.
Далее требуется выбрать, куда (Load to) закачивать данные:
если таблица уже существует в БД Oracle, то выбирается Existing Table.
если таблица отсутствует, создают новую структуру, одновременно заполняя ее данными. Для этого выбирают опцию New Table.
Потом выбирается, откуда загружать данные (Load from). Существуют две возможности:
из файла (Upload file),
копировать через буфер обмена Windows (Copy and paste) (рекомендуется).
Рассмотрим случай, когда данные копируются через буфер обмена:
выбираются соответствующие радиокнопки и нажимается Next>,
в новом окне устанавливается нужное имя таблицы, нажимается Next>,
на следующем шаге импорта мастер предлагает вставить данные из буфера в окошко, расположенное в центре страницы:
открывается таблица в Excel, ее строки копируются в буфер обмена,
мышью щелкается в окошке на странице APEX и нажимается комбинация клавиш CTRL+V (вставить),
в окне появятся данные из таблицы. Рекомендуется проверить самую первую строчку - там должны быть названия столбцов. Если имена полей совпадают в обеих таблицах, нажимается Next>. Если предполагается переименовать поля, то исправляются их названия в окне со вставленными данными, чтобы они совпадали с именами полей в БД APEX,
на следующем шаге мастер выводит всю структуру таблицы и данные. Здесь нужно проверить и исправить тип, длину и имена полей. Если какие-то столбцы не нужны, можно выбрать No в строке Upload,
нажимается кнопка Load Data.
Появляется список всех загруженных таблиц, где можно просмотреть результаты импорта, например, возможные ошибки. Если загрузка данных прошла успешно, то щелкнув по имени таблицы, можно перейти на страницу Object Browser с открытой структурой таблицы. Чтобы увидеть свои данные, надо выбрать Data в списке команд над таблицей.
Теперь можно редактировать данные, щелкнув по значку в столбце Edit в строке, которую нужно изменить. APEX не позволяет изменять данные прямо в табличном формате, как это принято в Access или Excel. Вместо этого он открывает анкетную форму для редактируемой записи.
После внесения изменений в анкетную форму нажать кнопку Apply Changes.
Добавление данных в среде APEX
В среде APEX данные можно вводить через опции меню. Для этого сразу после входа в среду APEX выполнить команды SQL Workshop Object Browser.
Выбрать нужную таблицу в левом столбце, выполнить команду Data. Нажимают кнопку Insert Row и в анкете вводят новые данные, после чего нажимают кнопку Create.
Следует отметить, что APEX-приложение, как правило, имеет специальные формы для пользователя, где последний сможет редактировать данные в таблицах, не обращаясь к среде разработки. Как и в Access, приложение более удобно для пользователя, чем средства, предназначенные для разработчиков.
Создание связей между таблицами
Из схемы БД видно, что между таблицами существуют связи, которые реализуются через соответствующие поля. Эти связи можно формировать как во время создания таблиц, так и после их создания и, возможно, заполнения данными.
Создание связей между простыми таблицами
Рассмотрим формирование связей после создания простых, не связанных между собой, таблиц. Для этого образуем связь подчинения Countries (страны) Locations (местонахождение) (связующими являются поля этих таблиц сountry_id, причем одноименное поле в таблице Locations ссылается на такое же поле в таблице Countries, показывая тем самым, в какой стране находится отделение корпорации):
Откройте вкладку Constraints описания созданной таблицы Locations.
Щелкните по кнопке Create. Отобразится мастер добавления ограничения целостности:
В поле Constraint Name впишите название создаваемого ограничения (оно должно быть уникальным в БД) например, location_con.
В поле Constraint Type выберите Foreign Key и Disallow Delete.
В списке Foreign Key Column(s) выделите столбец country_id.
В поле Reference Table Name (название родительской таблицы) выберите название созданной таблицы Countries.
В списке Reference Table Column (столбцы родительской таблицы, на которые будут ссылаться столбцы внешнего ключа) выберите столбец первичного ключа родительской таблицы – country_id.
Нажмите NEXT>.
Подтвердите создание внешнего ключа, нажав на кнопку Finish. Созданное ограничение целостности отобразится в списке ограничений целостности таблицы.
Откройте вкладку Model для таблицы Locations:
Видно, что теперь таблицы связаны: таблица Locations подчиняется таблице Countries.
Создание связанных таблиц
Можно «закладывать» связь между таблицами уже во время их создания с помощью Object Browser. Рассмотрим эту технологию на примере тех же таблиц в предположении, что мы их создаем заново как связанные таблицы:
Создается таблица Countries по описанной в разделе «Создание простых таблиц» технологии.
Аналогично создается таблица Locations до шага Foreing Key:
На шаге Foreign Key (Внешний ключ) добавляется ограничение ссылочной целостности (внешний ключ) для столбца country_id:
В секции Add Foreign Key (Добавить внешний ключ) в поле Name вписывается название создаваемого ограничения целостности, например, country_con. Необходимо убедиться, что длина названия не превышает 30 символов (ограничение СУБД Oracle на длину идентификаторов объектов). При необходимости надо сократить название.
Из группы радиокнопок {Disallow Delete, Cascade Delete, Set Null on Delete} оставить выбранным Disallow Delete (запрещать удаление родительской записи, если у нее есть дочерние).
В качестве столбца, на который накладывается ограничение целостности (поле со списком Key Column(s)), выбрать столбец country_id.
В качестве родительской таблицы (на которую ссылается столбец country_id) выбрать созданную ранее таблицу Countries.
Для того чтобы появились поля со списками столбцов для родительской таблицы, щелкнуть по иконке  справа от поля References Table.
В появившееся ниже поле со списком Referenced Column(s) добавить столбец country_id, на который будет ссылаться столбец country_id.
Нажать кнопку Add, чтобы добавить ограничение целостности в список внешних ключей.
Закончить создание таблицы по описанной ранее технологии.
Задание к работе (работа выполняется группами по 2 человека):
В соответствии с вариантом (табл. 1.1) разработать логическую схему БД, аналогичную рис. 1.1. Состав полей таблиц можно уточнить с преподавателем.
Таблица 1.1
Варианты
-
Вариант
|
Предметная область, сущности и их атрибуты
|
1
|
Обучение в вузе:
Студент (№ зачётки; фамилия; имя; отчество; группа; факультет; специальность; год поступления; дата рождения; адрес проживания; …).
Дисциплина учебного плана (код; название; группа дисциплин {гуманитарные и социально-экономические, математические и естественно-научные, общепрофессиональные, специальные}; число часов аудиторных занятий; число часов самостоятельной работы; семестр, в котором изучается дисциплина; …).
Зачётная книжка (№ зачётки; дисциплина; семестр; вид занятий {лекции, практические, лабораторные}; количество часов; дата сдачи; оценка; ФИО преподавателя; …).
Преподаватель (ФИО, ученая степень {кандидат наук, доктор наук}, научное звание {доцент, профессор}, должность {ассистент, старший преподаватель, доцент, профессор}, контактные данные,…).
|
2
|
Автомастерская:
Автомобиль (марка; год выпуска; номер; ФИО хозяина; объём двигателя; цвет; …).
Работник (код; фамилия; имя; отчество; специализация {маляр, слесарь, сварщик, жестянщик, механик}; дата приёма на работу; …).
Ремонт (дата; автомобиль; характер неисправности/повреждения; вид ремонта; работник; стоимость; …).
Автозапчасть (наименование, марка автомобиля, количество на складе, стоимость единицы,…)
|
3
|
Гостиница:
Номер (номер; этаж; кол-во мест; класс {стандарт, люкс}; стоимость проживания в сутки; …).
Постоялец (регистрационный номер; фамилия; имя; отчество; номер паспорта; кем выдан паспорт; дата выдачи паспорта; дата рождения; …).
Регистрация (номер постояльца; дата регистрации; предоставленный номер; дата выбытия; ФИО портье; …).
Обслуга (ФИО, контактные данные, должность…).
|
4
|
Чемпионат по футболу:
Команда (код; название; город; ФИО тренера; годовой бюджет; …).
Игрок (команда; фамилия; имя; отчество; специализация {вратарь, защитник, полузащитник, нападающий}; дата рождения; гражданство; дата окончания контракта; …).
Встреча (принимающая команда; гостевая команда; дата игры; итоговый счет; главный судья встречи; …).
Стадион (размещение, число мест, категория {закрытый, открытый}…)
|
5
|
Спортивный магазин:
Товар (код; наименование; вид спорта; цена; отдел {спорт. инвентарь, одежда, тренажёры, спортивное питание}; количество на складе, …).
Работник (личный номер; фамилия; имя; отчество; дата рождения; должность; отдел; дата приема на работу; …).
Продажа (личный номер работника; дата продажи; товар; количество; итоговая сумма без скидки; % скидки; …).
Поставщик (название предприятия, размещение, ФИО директора, контактные данные…).
|
6
|
Склад:
Продукция (наименование продукции, шифр продукции, единица измерения, цена единицы измерения, код поставщика…).
Поставщик (наименование, код, адрес,…).
Получатель (наименование, код, адрес,…) .
Поставка (наименование продукции, шифр продукции, количество в поставке, дата заказа, плановая дата поставки, фактическая дата поставки, размер штрафа за просрочку поставки,…).
|
7
|
Штат кафедры:
Сотрудник (ФИО, табельный номер, стаж, должность, оклад, кафедра,…).
Кафедра (название, месторасположение, ФИО заведующего, телефон, факультет,…).
Должность (название, требуемое образование, оклад…)
Факультет (название, аудитория, ФИО декана,…)
|
8
|
Перевозки:
Водитель (ФИО водителя, табельный номер водителя, номерной знак его автомобиля,…).
Автопарк (номерной знак автомобиля, марка автомобиля, год выпуска, местонахождение автомобиля {ремонт, рейс, база},…)
Автомобиль (марка автомобиля, грузоподъемность данной марки, средняя скорость данной марки км/час,…)
Поставка (наименование груза, вес груза, отправитель груза, получатель груза, но мерной знак автомобиля-перевозчика груза,…)
|
9
|
Библиотека:
Книга (название, автор, инвентарный номер, год выпуска,…).
Читатель (ФИО, адрес, контактные данные, номер читательского билета,…).
Библиотекарь (ФИО, контактные данные,…).
Выдача книги (ФИО библиотекаря, инвентарный номер книги, номер читательского билета, дата выдачи, плановая дата возврата, фактическая дата возврата, штраф за задержку книги,…).
|
10
|
Фитнес-клуб:
Клиент (ФИО, контактные данные, возраст, программа занятий,…).
Типовая программа занятий (название, вид упражнения, продолжительность упражнения, стоимость курса занятий,…).
Тренер (ФИО, квалификация, специализация, контактные данные,…).
Занятия клиента (ФИО клиента, ФИО тренера, программа занятий, дата начала занятий, дата окончания занятий,…)
|
Средствами APEX, следуя описанной технологии, описать таблицы и заполнить их данными. Состав полей может быть изменен по согласованию с преподавателем. В каждую таблицу включить идентификатор экземпляра сущности (поля типа *_id в примере). Связи подчинения между сущностями указать в таблицах с помощью идентификатора (так, например, в таблице EMPLOYEES показаны связи между подразделением и должностью через поля job_id и department_id).
Создать связи между таблицами для поддержания целостности данных.
Показать результаты преподавателю.
|
