Лабораторная работа создание баз данных. Лабораторная работа проектирование базы данных
 Скачать 1.01 Mb.
|
|
Атрибут – любая характеристика сущности, значимая для рассматриваемой предметной области. Он предназначен для квалификации, идентификации, классификации, количественной характеристики или выражения состояния сущности. Атрибут представляет тип характеристик (свойств), ассоциированных с множеством реальных или абстрактных объектов (людей, мест, событий, состояний, идей, пар предметов и т. д.) Уникальный идентификатор – это атрибут или совокупность атрибутов и/или связей, однозначно характеризующая каждый экземпляр данного типа сущности. В случае полной идентификации экземпляр данного типа сущности полностью идентифицируется своими собственными ключевыми атрибутами, в противном случае в идентификации участвуют также атрибуты другой сущности – родителя. При построении ER-модели используются следующие принципы: сущности на диаграмме представляются прямоугольниками; каждый прямоугольник может иметь различные визуальные атрибуты; каждой сущности должно быть присвоено уникальное имя; имена сущностей необходимо задавать в единственном числе; связи на диаграмме представляются линиями, идущими от одной сущности (таблицы) к другой; каждой связи присваивается уникальное имя; связанные таблицы разделяют на родительские и дочерние; родительские таблицы отображаются прямоугольниками с прямыми углами, дочерние – со скругленными. Логическое проектирование Логическое проектирование – создание схемы базы данных на основе конкретной модели базы данных, например, реляционной модели данных. Для реляционной модели данных даталогическая модель – набор схем отношений, обычно с указанием первичных ключей, а также «связей» между отношениями, представляющих собой внешние ключи. Преобразование концептуальной модели в логическую модель, как правило, осуществляется по формальным правилам. Этот этап может быть в значительной степени автоматизирован. На этапе логического проектирования учитывается специфика конкретной модели данных, но может не учитываться специфика конкретной СУБД. Пример логической модели представлен на рисунке 2. Рисунок 2 – Логическая модель Физическое проектирование Физическое проектирование – создание схемы базы данных для конкретной СУБД. Специфика конкретной СУБД может включать в себя ограничения на именование объектов базы данных, ограничения на поддерживаемые типы данных и т. п. Кроме того, специфика конкретной СУБД при физическом проектировании включает выбор решений, связанных с физической средой хранения данных (выбор методов управления дисковой памятью, разделение БД по файлам и устройствам, методов доступа к данным), создание индексов и т. д. Результатом физического проектирования, например, может стать скрипт на языке SQL. 1.4. Пример проектирования реляционной базы данных В качестве примера возьмем базу данных компании, которая занимается издательской деятельностью. Инфологическое проектирование Анализ предметной области База данных создаётся для информационного обслуживания редакторов, менеджеров и других сотрудников компании. БД должна содержать данные о сотрудниках компании, книгах, авторах, финансовом состоянии компании и предоставлять возможность получать разнообразные отчёты. В соответствии с предметной областью система строится с учётом следующих особенностей: каждая книга издаётся в рамках контракта; книга может быть написана несколькими авторами; контракт подписывается одним менеджером и всеми авторами книги; каждый автор может написать несколько книг (по разным контрактам); порядок, в котором авторы указаны на обложке, влияет на размер гонорара; если сотрудник является редактором, то он может работать одновременно над несколькими книгами; у каждой книги может быть несколько редакторов, один из них – ответственный редактор; каждый заказ оформляется на одного заказчика; в заказе на покупку может быть перечислено несколько книг. Для инфологического проектирования воспользуемся методом «сущность-связь». Для того, чтобы представить, как устроена предметная область нужно задать множество объектов реального мира (главная проблема что считать объектом). Объект – семантическое понятие, которое может быть полезно при обсуждении устройств реального мира. Сущность реального мира – объекты – не обязательно материальны – важно понятие существенно и различимо для других. Между объектами могут возникать связи трех видов: один к одному 1:1 (пациент: место в палате); один к многим 1:n и многие к одному n:1; многие ко многим n:n (пациент : хирург). При построении моделей используются следующие геометрические фигуры: Элемент ER-модели Условно графическое представление Объект Связь Атрибут В настоящее время существует большое множество прикладных программ для создания графического представления структуры БД. При этом могут быть использованы как специализированные средства (например, Visio), так и средства построения графических образов (Umbrello, OO Draw). Либо использовать облачный сервис https://www.draw.io/. Выделим базовые сущности этой предметной области: Сотрудники компании. Атрибуты сотрудников – ФИО, табельный номер, пол, дата рождения, паспортные данные, ИНН, должность, оклад, домашний адрес и телефоны. Для редакторов необходимо хранить сведения о редактируемых книгах; для менеджеров – сведения о подписанных контрактах. Авторы. Атрибуты авторов – ФИО, ИНН (индивидуальный номер налогоплательщика), паспортные данные, домашний адрес, телефоны. Для авторов необходимо хранить сведения о написанных книгах. Книги. Атрибуты книги – авторы, название, тираж, дата выхода, цена одного экземпляра, общие затраты на издание, авторский гонорар. Контракты будем рассматривать вместе с книгами, т.к. каждая книга связана непосредственно с одним контрактом. Для отражения финансового положения компании в системе нужно учитывать заказы на книги. Для заказа необходимо хранить номер заказа, заказчика, адрес заказчика, дату поступления заказа, дату его выполнения, список заказанных книг с указанием количества экземпляров. ER–диаграмма сущностей издательской компании приведена на рисунке 1.1 Авторы Книги Сотрудники Написать Заказать Редактировать Заказы Контракт Выполнить Заключить М М М М М М 1 М Рисунок 1.1 – ER–диаграмма издательской компании Логическое проектирование реляционной БД Преобразование ER–диаграммы в схему базы данных Связь типа 1:n (один-ко-многим) между отношениями реализуется через внешний ключ. Ключ вводится для того отношения, к которому осуществляется множественная связь (КНИГИ). Связь редактировать между отношениями КНИГИ и ЗАКАЗЫ принадлежит к типу n:m (многие-ко-многим). Этот тип связи реализуется через вспомогательное отношение, которое является соединением первичных ключей соответствующих отношений. Для создания логической модели необходимо «уточнить» созданную ранее инфологическую модель. Уточнить – значит конкретизировать, добавить атрибуты в ранее созданные сущности. Уточняя, мы отмечаем в каждой сущности какие именно данные, относящиеся к этой сущности, мы будем хранить далее в базе данных. Уточнённая схема реляционной БД издательской компании приведена на рисунке 1.2. Авторы Книги Сотрудники Написать Заказать Редактировать Заказы Контракт Выполнить Заключить М М М М М М 1 М Номер на обложке Процент гонорара ФИО ИНН Адрес Телефон Паспортные данные Дата подписания Номер Заказы Адрес заказчика Номер Дата поступления Дата выполнения Табельный номер ФИО Дата рождения Пол Паспортные данные ИНН Адрес Телефон Должность Оклад Название Дата выхода Затраты Гонорар Цена Тираж Рис.1.2 – Уточнённая ER–диаграмма издательской компании Тогда логическая модель БД будет выглядеть как показано на рисунке 1.3. Номер заказа Заказчик Дата поступ.заказа Адрес заказчика Дата выполн. Заказа Адрес заказчика Дата выполн. заказа КодАвтора ФИО ПаспортныеДанные ИНН Адрес Телефон Номер контракта Дата подпис. Контракта Менеджер Название книги Цена Затраты Авторский. Гонорар Дата выхода Тираж Ответств. редактор Табельный номер ФИО Дата рождения Пол Паспорт. Данные ИНН Должность Оклад Адрес Телефон Номер в списке Гонорар НомерЗаказа КодКниги Количество КодКниги КодРедактора Заказы Авторы Авторы_Книги Строки_заказа Книги_Редакторы Сотрудники М 1 М 1 М 1 1 М М 1 1 М Рисунок 1.3 – Логическая модель БД В этой модели были добавлены дополнительные таблицы: Книги_Редакторы. Эта сущность включена в диаграмму, во-первых, для устранения связи многие-ко-многим между редакторами и книгами, т.к. каждую книгу может редактировать множество редакторов и каждый редактор может редактировать множество книг. Во-вторых, т.к. и редакторы входят в число сотрудников и менеджеры, то каждый контракт (книгу) из числа сотрудников может подписывать только один менеджер и для этого установлена прямая связь один-ко-многим между сотрудниками и книгами. Строки_заказа. Эта сущность определена для устранения связи многие-ко-многим между заказами и книгами т.к. в одном заказе может быть множество книг. Также в этой сущности добавлен атрибут «Количество» для обозначения количества книг. Авторы_Книги. Эта сущность определена для устранения связи многие-ко- многим между авторами и книгами т.к. у одного автора может быть несколько книг. Составление реляционных отношений Понятие реляционный (англ. relation – отношение) связано с разработками известного английского специалиста в области систем баз данных Эдгара Кодда (Edgar Codd). Эти модели характеризуются простотой структуры данных, удобным для пользователя табличным представлением и возможностью использования формального аппарата алгебры отношений и реляционного исчисления для обработки данных. Реляционная модель ориентирована на организацию данных в виде двумерных таблиц. Каждая реляционная таблица представляет собой двумерный массив и обладает следующими свойствами: каждый элемент таблицы – один элемент данных; все ячейки в столбце таблицы однородные, то есть все элементы в столбце имеют одинаковый тип (числовой, символьный и т. д.); каждый столбец имеет уникальное имя; одинаковые строки в таблице отсутствуют; порядок следования строк и столбцов может быть произвольным. Основными понятиями реляционных баз данных являются тип данных, домен, атрибут, кортеж, первичный ключ и отношение. Для начала покажем смысл этих понятий на примере отношения СОТРУДНИКИ, содержащего информацию о сотрудниках некоторой организации как показано на рисунке 1.4. Рисунок 1.4 – Схема реляционного отношения Тип данных в реляционной модели данных полностью адекватно понятию типа данных в языках программирования. Обычно в современных реляционных БД допускается хранение символьных, числовых данных, битовых строк, специализированных числовых данных (таких как "деньги"), а также специальных "темпоральных" данных (дата, время, временной интервал). Понятие домена более специфично для баз данных, хотя и имеет некоторые аналогии с подтипами в некоторых языках программирования. В самом общем виде домен определяется заданием некоторого базового типа данных, к которому относятся элементы домена, и произвольного логического выражения, применяемого к элементу типа данных. Если вычисление этого логического выражения дает результат "истина", то элемент данных является элементом домена. Наиболее правильной интуитивной трактовкой понятия домена является понимание домена как допустимого потенциального множества значений данного типа. Например, домен "Имена" в схеме, показанной выше, определен на базовом типе строк символов, но в число его значений могут входить только те строки, которые могут изображать имя (в частности, такие строки не могут начинаться с мягкого знака). Кортеж, соответствующий данной схеме отношения, - это множество пар {имя атрибута, значение}, которое содержит одно вхождение каждого имени атрибута, принадлежащего схеме отношения. "Значение" является допустимым значением домена данного атрибута (или типа данных, если понятие домена не поддерживается). Тем самым, степень или "арность" кортежа, т.е. число элементов в нем, совпадает с "арностью" соответствующей схемы отношения. Попросту говоря, кортеж - это набор именованных значений заданного типа. Отношение - это множество кортежей, соответствующих одной схеме отношения. Иногда, чтобы не путаться, говорят "отношение-схема" и "отношение-экземпляр", иногда схему отношения называют заголовком отношения, а отношение как набор кортежей - телом отношения. На самом деле, понятие схемы отношения ближе всего к понятию структурного типа данных в языках программирования. Было бы вполне логично разрешать отдельно определять схему отношения, а затем одно или несколько отношений с данной схемой. В свою очередь отношения имеют два важных свойства: арность – число атрибутов в отношении; мощность - это кардинальное число отношения, т.е. число кортежей (строк) в отношении. Каждое реляционное отношение соответствует одной сущности (объекту предметной области) и в него вносятся все атрибуты сущности. Для каждого отношения необходимо определить первичный ключ и внешние ключи. Ключ или потенциальный ключ – это минимальный набор атрибутов, по значениям которых можно однозначно выбрать требуемый экземпляр сущности. Минимальность означает, что исключение из набора любого атрибута не позволяет идентифицировать сущность по оставшимся. Каждая сущность должна, но не обязана обладать хотя бы одним возможным ключом. Другими словами ключ – это поле или набор полей, однозначно идентифицирующий запись. Значение первичного ключа в таблице БД должно быть уникальным, то есть в таблице не должно существовать двух ил более записей с одинаковым значением первичного ключа. Первичные ключи облегчают установление связей между таблицами. Поскольку первичный ключ должен быть уникальным, для него могут использоваться не все поля таблицы. В том случае, если базовое отношение не имеет потенциальных ключей, вводится суррогатный первичный ключ, который не несёт смысловой нагрузки и служит только для идентификации записей (например ID записи). Примечание: суррогатный первичный ключ также может вводиться в тех случаях, когда потенциальный ключ имеет большой размер (например, длинная символьная строка) или является составным (не менее трёх атрибутов). Потенциальными ключами отношения АВТОРЫ являются атрибуты Паспортные данные и ИНН. Первый хранится как длинная строка, а последний по условиям предметной области не является обязательным. Поэтому для авторов необходимо ввести суррогатный ключ – A_id. Книги можно идентифицировать по атрибуту Контракт: его номер обязателен и уникален. Потенциальные ключи отношения СОТРУДНИКИ – атрибуты ИНН, Паспортные данные, Табельный номер, причём все они обязательные. Табельный номер занимает меньше памяти, чем ИНН, поэтому он и будет первичным ключом. Кортежи отношения ЗАКАЗЫ можно идентифицировать ключом Номер заказа. Потенциальными ключами вспомогательных отношений являются комбинации первичных ключей соответствующих базовых отношений. Отношения приведены в таблице 1.1-1.7. Для каждого отношения указаны атрибуты с их внутренним названием, типом и длиной. Типы данных обозначаются так: N – числовой, C – символьный, D – дата (последний имеет стандартную длину, зависящую от СУБД, поэтому она не указывается). Таблица 1.1 – Схема отношения СОТРУДНИКИ (Employees) Содержание поля Имя поля Тип, длина Примечания Табельный номер E_ID N(4) первичный ключ Фамилия, имя, отчество E_NAME C(50) обязательное поле Дата рождения E_BORN D Пол E_SEX C(1) обязательное поле Паспортные данные E_PASSP C(50) обязательное поле ИНН E_INN N(12) обязательное уникальное поле Должность E_POST C(30) обязательное поле Оклад E_SALARY N(8,2) обязательное поле Адрес E_ADDR C(50) Телефоны E_TEL C(30) многозначное поле Таблица 1.2 – Схема отношения КНИГИ (Books) Содержание поля Имя поля Тип, длина Примечания Номер контракта B_CONTRACT N(6) первичный ключ Дата подписания контракта B_DATE D обязательное поле Менеджер B_MAN N(4) внешний ключ (к Employees) Название книги B_TITLE N(40) обязательное поле Цена B_PRICE N(6,2) цена экземпляра книги Затраты B_ADVANCE N(10,2) общая сумма затрат на книгу Авторский гонорар B_FEE N(8,2) общая сумма гонорара Дата выхода B_PUBL D Тираж B_CIRCUL N(5) Ответственный редактор B_EDIT N(4) внешний ключ (к Employees) Таблица 1.3 – Схема отношения АВТОРЫ (Authors) Содержание поля Имя поля Тип, длина Примечания Код автора A_ID N(4) суррогатный первичный ключ Фамилия, имя, отчество A_NAME C(50) обязательное поле Паспортные данные A_PASSP C(50) обязательное поле ИНН A_INN N(12) уникальное поле Адрес A_ADDR C(50) обязательное поле Телефоны A_TEL C(30) многозначное поле Таблица 1.4 – Схема отношения ЗАКАЗЫ (Orders) Содержание поля Имя поля Тип, длина Примечания Номер заказа O_ID N(6) первичный ключ Заказчик O_COMPANY С(40) обязательное поле Дата поступления заказа O_DATE D обязательное поле Адрес заказчика O_ADDR C(50) обязательное поле Дата выполнения заказа O_READY D Таблица 1.5 – Схема отношения КНИГИ–АВТОРЫ (Titles) |