Даталогическое проектирование БД
Содержание
Введение……………………………………………………………………………………..………………4
1. Анализ задачи
Исследование информационных потребностей пользователей ИС………………………………………………………………………………………………...5
Разработка общей структуры ИС…………………………………………………7
2. Разработка серверной части информационной системы.
Инфологическое проектирование БД…………………………………...............8
Датологическое проектирование БД……………………………………….….14
Разработка хранимых процедур.............................................................................24
Обеспечение целостности данных……………………………………………....41
Разработка сценария создания новой БД…………………………………….43
3. Разработка клиентской части информационной системы.
Взаимосвязь клиентской программы с БД …………………………………..46
Запросы к БД……………………………………………………………………………….49
Интерфейс пользователя……………………………………………………………..50
Сценарий инсталляции клиентской программы………………………….72
Руководство пользователя…………………………………………………………...74
Заключение………………………………………………………………………………………………...78
Список используемой литературы……………………………………………………………..79
Приложение 1: исходный текст хранимых процедур и триггеров………….….80
Приложение 2: исходный текст клиентской программы………………….118
Приложение 3: сценарий создания БД……………………………………………………142
Приложение 4: сценарий инсталляции программы…………………………………143
Введение
Разработка программного обеспечения и его сопровождение было и остается важнейшей функцией компьютерных специалистов, наряду с системным администрированием и проектированием систем управления базами данных (СУБД). Широкое внедрение вычислительных машин во все сферы промышленности, связи, систем управления и документооборота требует массу программного обеспечения непрерывно возрастающей сложности.
Еще недавно программирование считалось искусством, теперь - специальностью, работой как отдельных личностей, так и больших коллективов.
Выбор создания и проектирования базы данных «Типография» продиктован насущными потребностями современного рынка труда, поскольку издательское дело занимает одно из важных мест в развитии нашей страны и нас самих. На данный момент существуют множество программных продуктов, позволяющих автоматизировать процесс издательства. Но наша программа заметно отличается от других аналогов, поскольку данная база данных совмещает в себе не только книгопечатание и наличие специальных средств для обработки, хранения и накопления информации, но и возможность заказчикам размещать рекламу в каких-либо периодических изданиях. Об остальных возможностях данного программного продукта вы узнаете далее на страницах нашей пояснительной записки. Разработка проекта проводилась в Microsoft SQL Server, который предназначен для управления, проектирования и разработки баз данных.
1. Анализ задачи
Исследование информационных потребностей пользователя
Для того, чтобы база данных адекватно отражала предметную область, необходимо хорошо представлять себе все нюансы, присущие данной предметной области (ПО), и уметь отобразить их в базе данных. Поэтому прежде чем начинать проектирование базы данных, необходимо как следует разобраться, как функционирует предметная область, для отображения которой создается БД. Предметная область должна быть предварительно описана.Автоматизация подсистемы управления типографией является одной из важнейших частей автоматизации производственно-хозяйственной деятельности издательства.
Определенная задача состояла в разработке полноты базы, т.е. осуществлена такая архитектура, которая могла бы осуществить как работу поисковых систем для типографии, так и формирование выходной информации, необходимой для функционирования задач подсистемы управления типографией и сведений для руководства издательства.
Основная задача заключается в создании информационной системы, которая совмещала бы в себе удобство, простоту, надежность, достоверность, актуальность. Необходимо предусмотреть следующие возможности в нашей информационной системе:
~ добавление новых изданий, которые должны издаваться в нашей типографии, будь то периодические издания (газеты и журналы), либо непериодические (книги и буклеты);
~ возможность добавлять, редактировать, удалять в периодические издания определенные рубрики, статьи;
~ изменение информации об изданиях, дополнение изданий;
~ удаление таких изданий, которые перестали быть актуальными;
~ добавление или удаление заказчиков рекламы;
~ заключение договора на размещение рекламы заказчика с нашим издательством;
~ обсуждение с заказчиком рекламного проекта (какие-либо характеристики рекламы, размер, цвет, место размещения, количество повторов размещения данной рекламы в изданиях, цену);
~ размещение заказанной рекламы в выбранных заказчиком периодических изданиях;
~ расторжение или изменение существующего договора на рекламу;
~ выдача определенной информации по запросу пользователя;
~ поиск и предоставление информации об определённых изданиях, заказчиках, договорах на рекламу и т.д.
При этом пользователь должен, работая с данной базой данных, иметь доступ к нужной ему информации. К примеру, заказчик, желая заказать рекламу, должен иметь возможность просмотра и выбора в нашей базе тех изданий, в которых будет размещена его реклама. А для сотрудника издательства необходимо предусмотреть права на любые действия с данными. Он, в зависимости от определенной ситуации, может получить интересующую его информацию, а именно:
§ - издавалось ли данное издание в нашей типографии;
§ - заключен ли данный договор с данным заказчиком;
§ - реализован ли данный договор на рекламу в периодических изданиях;
§ - с какими характеристиками и в каких изданиях размещался данный договор на рекламу;
§ - существуют ли договоры данного заказчика рекламы;
§ - можно ли удалить заказчика, если заключенный с ним договор исчерпал свою силу;
§ - просмотреть информацию об интересующих изданиях, заказчиках и заключенных с ними договорах;
§ - издавались какие-либо издания данного автора;
§ - просмотреть интересующие номера выпуска периодических изданий, а также все статьи в них и т.д.
Т.е. сотрудник может найти любую интересующую информацию, пользуясь при этом поиском и фильтрацией. На пример, в качестве результата поиска определенного периодического издания сотруднику будет предоставлена возможность вывода информации обо всех статьях, размещавшихся в определенном номере выпуска искомого периодического издания.
Также сотрудник типографии может по своему усмотрению распоряжаться данными в базе, а именно:
§ - добавлять новые экземпляры к издательству;
§ - редактировать и удалять издания;
§ - регистрировать новых заказчиков рекламы;
§ - заключать договоры на рекламу с заказчиками;
§ - размещать рекламу с выбранными заказчиком характеристиками в определенных периодических изданиях;
§ - удалять договоры, если они уже были размещены;
§ - редактировать, удалять заказчиков при потере актуальности данных.
При этом программа не допускает ввода заведомо неверной информации, к примеру, при добавлении нового издания стоимость его может варьироваться в пределах от 0 до 100000, т.е. отрицательные цифры вводить нельзя. Все продумано так, чтобы целостность данных ни в коем случае не нарушалась. Также нельзя удалить какого-либо заказчика, если существуют с ним заключенные договоры на рекламу. Необходимо сначала удалить договоры, а уж затем заказчика и т.д.
Разработка общей структуры ИС
Архитектура информационной системы оказывает непосредственное влияние на ее функционирование и эффективность. К основным функциям относят:
· Хранение и защита информации
· Изменение и обновление данных
· Поиск и сбор данных по запросам пользователя
· Обработка данных
Прикладные программы – служат для обработки данных, различных вычислений и формирования выходных документов по заданной форме. ПП могут создаваться с помощью встроенных средств СУБД. При построении приложения БД используют два основных подхода.
· Файл-сервер;
· Клиент-сервер.
Более простым для реализации является подход файл-сервер. В данном случае на запросы пользователей передается весь файл БД. Но вследствие высокой интенсивности передачи данных скорость работы системы существенно уменьшается, также недостатком является передача пользователю ненужной информации (базы данных целиком).
Наиболее распространенным, разработанным позднее подхода файл-сервер, является подход клиент-сервер. Достоинством данного подхода является достижение рационального использования ресурсов сети, и следовательно, повышения скорости работы системы в целом.
В результате запроса пользователя на особом языке SQL передаётся лишь часть данных, а предварительная обработка и выборка необходимых данных производится СУБД, расположенной на сервере. В тоже время этот подход является более сложным для реализации. В настоящее время данная архитектура перспективна.
В виду перечисленных характеристик подхода клиент-сервер нами был выбран этот подход .
Структура распределенной ИС, построенной по архитектуре клиент-сервер с использованием сервера баз данных (рис. 1.2.1).
…
рис. 1.2.1
В системах, организованных в архитектуре клиент/сервер, поддерживается коллективный доступ к данным. Здесь каждый компьютер осуществляет операции, связанные с хранением, доступом и обработкой данных. При этом выполняемые задачи разделяются между сервером и рабочей станцией, причем, исхода из неравенства аппаратных возможностей сервера и рабочих станций, это разделение должно учитываться в создаваемых системах. Так, например, мощность компьютера-сервера отражается на скорости пересылки данных, возможности хранения большего объема информации и т.д.
Сервер БДреализует функции управления БД, запрашиваемые другими (клиентскими) программами, как правило, с помощью SQL операторов. Клиентскими программами могут являться различные программы полу функциональной СУБД, а также множество других приложений (электронные таблицы, текстовые редакторы и т.д.).
2. Разработка серверной части информационной системы
Инфологическое проектирование БД
Инфологическое проектирование выполняется в базисе концептуальных компонентов и учитывает возможности и ограничения модели данных реализации БД.
При проектировании информационной базы необходимо выяснить запросы и пожелания клиента и определить возможный подход к решению задачи. И на основе анализа требований реализовать конкретную модель в конкретной программной среде. Результаты каждого этапа проектирования использовать в качестве исходного материала следующего этапа.
При анализе текущей организации предприятия выделяются проблемы для решения, определяются объекты отношения между ними , составляется «эскиз» текущей организации предприятия, разрабатывается модель с учетом конкретных условий ее функционирования.
База данных ориентирована на определенную предметную область и организована на основе некоторого подмножества данных. Возможности баз данных полезны в областях, связанных с долговременным управлением информацией, таких как электронные библиотеки и хранилища данных.
При проектировании системы обработки данных больше всего нас интересует организация данных. Помочь понять организацию данных призвана информационная модель.
При разработке ER-диаграммы информационной системы «Типография» можно выделить следующие сущности:
Ø Издание;
Ø Непериодические издания;
Ø Периодические издания;
Ø Номер выпуска;
Ø Статья;
Ø Заказчик;
Ø Договор на рекламу;
Ø Характеристики рекламы;
Ø Реализация договора.
Поясним каждую сущность.
Издание – сущность для описания всех изданий, которые издаются в издательстве.
Непериодические издания – сущность, описывающая непериодические издания(книги, буклеты).
Периодические издания – сущность, описывающая периодические издания (газеты, журналы).
Номер выпуска – сущность, описывающая номера выпуска периодических изданий.
Статья– сущность, описывающая статьи, имеющиеся в “Номерах выпуска” периодических изданий.
Заказчик – сущность, отображающая информацию о всех заказчиках, заключавших договора с издательством.
Договор на рекламу – сущность, описывающая все заключенные договора на рекламу.
Характеристики рекламы – сущность, описывающая какими характеристиками наделена реклама, описанная в договоре. Например, один договор может содержать несколько характеристик, исходя из того, что в разных изданиях договор на рекламу может размещаться с разными характеристиками.
Реализация договора – сущность, отображающая в каких изданиях размещается данный договор и с какими характеристиками.
Рассмотрим связи между сущностями.
Сущность “Издание” связывается со следующими сущностями:
- “Периодические издания”, т.к. все имеющиеся издания подразделяются на периодические и непериодические
- “Непериодические издания”, т.к. все имеющиеся издания подразделяются на периодические и непериодические.
Сущность “Периодические издания” связана с сущностью “Издание”.
Сущность “Периодические издания” связана с сущностями:
- “Номер выпуска”, поскольку периодические издания характеризуются номерами выпуска.
- “Издание”.
Сущность “Номер выпуска” связана с сущностями:
1. “Реализация договора”, т.к. именно в определенных выпусках периодических изданий размещаются договоры на рекламу.
2.“Статья’’, т.к. в каждых номерах выпуска периодических изданий имеются статьи, другими словами, номера периодических изданий состоят из статей.
3.“Периодические издания”.
Сущность “Статья” связана с сущностью “Номер выпуска”.
Сущность“Договор на рекламу” связана со следующими сущностями:
- “Заказчик”, т.к. заказчики заключают договора на рекламу.
- “Реализация договора”, поскольку составленный договор на рекламу размещается в изданиях благодаря реализации этого договора.
Сущность “Заказчик” связана с сущностью “Договор на рекламу”.
Сущность “Реализация договора” связана с сущностями:
1.”Номер выпуска”.
2. “Характеристики рекламы”, т.к. реклама в периодических изданиях размещается строго по составленному с заказчиком договору, включающему определенные характеристики;
3. “Договор на рекламу”.
Сущность “Характеристики рекламы”связана с сущностями:
1. “Реализация договора”.
Рассмотрим степени связи и классы принадлежностей сущностей.
Рассмотрим сущность “Заказчики”, у сущности “Договор на рекламу” степень связи будет равна N, а у сущности “Заказчики” – 1, т.к. если заказчик производит заказ на размещение рекламы впервые, осуществляется первичная регистрация его данных и сведений о сделанном заказе. Если же заказчик производит заказ повторно, осуществляется регистрация только данного заказа. Класс принадлежности сущности “Договор на рекламу” обязательный , а у сущности “Заказчики” необязательный, т.к. необязательно каждый заказчик должен заключать договор на рекламу.
N
|
|
* 1 *
рис. 2.1.1
При связи с сущностью “Договор на рекламу”, у сущности “Номер выпуска” степень связи будет равна N, а у сущности “Договор на рекламу” – N, т.к. N договоров на рекламу может быть реализованы в N номерах выпуска. Класс принадлежности сущности “Договор на рекламу” обязательный, т.к. договор на рекламу без номера выпуска не реализуется. А у сущности “Номер выпуска” – необязательный, т.к. номер выпуска может быть без рекламы.
 |
|
|
N * N *
рис. 2.1.2
При связи с сущностью “Договор на рекламу ”, у сущности “ Характеристики рекламы ” степень связи будет равна N, а у сущности “Договор на рекламу” –N, т.к. N договоров на рекламу может содержать N характеристик. Класс принадлежности сущности “Договор на рекламу” обязательный, т.к. договор на рекламу обязательно должен содержать какие-либо характеристики рекламы. А у сущности “Характеристики рекламы” – необязательный, т.к. характеристики рекламы могут не включаться ни в один договор.
 |
|
|
N * N *
рис. 2.1.3
При связи с сущностью “Издание”, у сущности “Непериодические издания” степень связи будет равна 1, а у сущности “Издание” – 1, т.к. непериодические издания относятся к изданиям, а издания подразделяются на непериодические. Класс принадлежности сущности “Непериодические издания” необязательный, а сущности “Издание” обязательный, т.к. каждое издание может являться непериодическим, причём в непериодических изданиях может не находиться ни одно издание.
|
|
1 1* рис. 2.1.4
При связи с сущностью “Периодические издания”, у сущности “Издание” степень связи будет равна 1, а у сущности “Периодические издания” – 1, т.к. периодические издания относятся к изданиям, а издания также подразделяются и на периодические. Класс принадлежности сущности “Периодические издания” необязательный, а сущности “Издание” обязательный, т.к. каждое издание может являться периодическим, причём в периодических изданиях может не находиться ни одно издание.
 |
|
|
*1 1 рис. 2.1.5
У сущности “Номер выпуска” при связи с сущностью “Периодические издания” степень связи будет равна N, а у сущности “Периодические издания” – 1, т.к. в одном периодическом издании может находиться N номеров выпусков. Класс принадлежности сущности “Периодические издания” обязательный и у сущности “Номер выпуска” обязательный, т.к. каждое периодическое издание должно иметь свои номера выпусков, а номер выпуска должно относиться к определенному периодическому изданию.
 |
|
|
N 1 * *
рис. 2.1.6
При связи с сущностью “Статья”, у сущности “Номер выпуска” степень связи будет равна 1, а у сущности “Статья” – N, т.к. в одном номере выпуска может размещаться N статей. Класс принадлежности сущности “Статья” обязательный, также как у сущности “Номер выпуска”, т.к. каждый номер выпуска состоит из статей, а каждая статья печатается исключительно в номерах выпуска.
 |
|
|
N 1 * *
рис. 2.1.7
ER – диаграмма:
*ИН издания 
|
|
*Ин издания
|
*код заказчика
 | |||
 | |||
*Ин издания
 | |||
 | |||
*Ин договора
*Ин издания
*Ин выпуска
|
 |  | ||||
 | |||||
*ИН характеристики
|
*статья
Даталогическое проектирование БД
Даталогическое проектирование баз данных выполняется с учетом среды конкретной выбранной СУБД. Строго говоря, именно даталогическая схема БД является реляционной схемой. Даталогическое проектирование выполняется на основе принятой инфологической схемы и заключается в выполнении следующих операций:
· выделение таблиц для реализации схем логических объектов;
· определение физических форматов атрибутивного описания свойств логических объектов на основе типов соответствующих концептуальных символов;
· выделение внешних, первичных и потенциальных ключей таблиц;
· определение индексируемых полей и полей реализации логических связей между таблицами;
· установка бизнес-правил;
· проектирование представлений (view) для организации хранимых результатов промежуточного доступа в БД;
· определение процедур обработки изменений атрибутивных значений по связям между таблицами и т. д.
Таким образом, даталогическое проектирование выполняет разложение логических слов и выражений на схемные элементы, которыми оперирует реляционная схема.
При проектировании БД информацию обычно размещают в нескольких таблицах. При этом отношения оказываются связанными с семантикой (смысловой нагрузкой) информации.
В реляционных СУБД для указания связей таблиц производят операцию их связывания. Различают следующие виды связей:
ü бинарные связи (или связи между двумя отношениями);
ü тернарные связи (или связи между тремя отношениями);
ü n-арные (это самый общий вид связей, позволяющий связывать n отношений).
Наиболее часто встречаются бинарные связи. При связывании двух отношений выделяют основную и подчиненную таблицы. Логическое связывание производится с помощью ключа связи. Суть связывания состоит в установлении соответствия полей связи основной и подчиненной таблицы.
После построения ER – диаграммы нужно сформулировать набор предварительных отношений с указанием первичного ключа для каждого отношения. После этого добавить не ключевые атрибуты в отношения.
В качестве полей связи подчиненной таблицы как правило используются ключевые поля (первичные ключи), в основной таблице внешние ключи.
В зависимости от того, как соотносятся ключевые поля с полями связей различают 4 вида связей:
ü 1:1 один к одному;
ü 1:N один ко многим;
ü N:1 многие к одному;
ü М:N многие ко многим.
Взаимосвязь «один к одному» (1:1) (между двумя типами объектов)
Поля связи основной и подчиненной таблиц являются ключевыми.
По сути таблицы равноправны. Имеет место взаимно однозначное соответствие между записями в таблице. Примером служат таблицы «Непериодические издания» и «Издания», (а также «Периодические издания» и «Издания»), т.к. в принципе их можно объединить в одну общую таблицу, ведь, по сути, это все издания.
Взаимосвязь «один ко многим» (1:N )(между двумя типами объектов)
В определенный момент времени один заказчик может заключить
несколько договоров на рекламу, при этом несколько заказчиков не могут являться обладателями одного договора (на условии если заказчик не претендует на часть договора).В этом случае одной записи данных первого объекта (его часто называют родительским или основным) будет соответствовать несколько записей второго объекта (дочернего или подчиненного). Взаимосвязь «один ко многим» очень распространена при разработке реляционных баз данных. В качестве родительского объекта часто выступает справочник, а в дочернем хранятся уникальные ключи для доступа к записям справочника. В нашем примере в качестве такого справочника можно представить объект Заказчик, в котором хранятся сведения о всех заказчиках. При обращении к записи для определенного заказчика нам доступен список всех договоров, которые он заключил, и сведения о которых хранятся в объекте Договор на рекламу.
Взаимосвязь «многие к одному» (N:1)
Данная связь имеет место, когда одной или нескольким записям основной таблицы ставится в соответствие одна запись дополнительной таблицы. По сути, такой вид связи является отражением связи 1 : N. Всё зависит от того какая таблица является основной, а какая дополнительной. Если хотим узнать, кто заключил данный договор на рекламу, то 1 : N, а если нужно узнать какой договор заключил данный заказчик, то N : 1.
Взаимосвязь «многие ко многим» (М:N )
Данная связь является наиболее общим видом связи. Ее также называют слабой связью, т.к. она не накладывает ограничений целостности на отношения.
Далее необходимо привести все отношения к нормальной форме Бойса-Кодда.
Приведение модели к требуемому уровню нормальной формы Бойса-Кодда является основой построения реляционной БД. В процессе нормализации элементы данных группируются в таблицы, представляющие объекты и их взаимосвязи. Теория нормализации основана на том, что определенный набор таблиц обладает лучшими свойствами при включении, модификации и удалении данных, чем все остальные наборы таблиц, с помощью которых могут быть представлены те же данные. Введение нормализации отношений при разработке информационной модели обеспечивает минимальный объем физической, то есть записанной на каком-либо носителе БД и ее максимальное быстродействие, что впрямую отражается на качестве функционирования информационной системы. Нормализация информационной модели выполняется в несколько этапов.
Данные, представленные в виде двумерной таблицы, являются первой нормальной формой реляционной модели данных. Первый этап нормализации заключается в образовании двумерной таблицы, содержащей все необходимые свойства информационной модели, и в выделении ключевых свойств. Очевидно, что полученная весьма внушительная таблица будет содержать очень разнородную информацию. В этом случае будут наблюдаться аномалии включения, обновления и удаления данных, так как при выполнении этих действий нам придется уделить внимание данным (вводить или заботиться о том, чтобы они не были стерты), которые не имеют к текущим действиям никакого отношения. Например, может наблюдаться такая парадоксальная ситуация.
Отношение задано во второй нормальной форме, если оно является отношением в первой нормальной форме и каждое свойство, не являющийся первичным свойством в этом отношении, полностью зависит от любого возможного ключа этого отношения.
Если все возможные ключи отношения содержат по одному свойству, то это отношение задано во второй нормальной форме, так как в этом случае все свойства, не являющиеся первичными, полностью зависят от возможных ключей. Если ключи состоят более чем из одного свойства, отношение, заданное в первой нормальной форме, может не быть отношением во второй нормальной форме. Приведение отношений ко второй нормальной форме заключается в обеспечении полной функциональной зависимости всех свойств от ключа за счет разбиения таблицы на несколько, в которых все имеющиеся свойства будут иметь полную функциональную зависимость от ключа этой таблицы. В процессе приведения модели ко второй нормальной форме в основном исключаются аномалии дублирования данных.
Отношение задано в третьей нормальной форме, если оно задано во второй нормальной форме и каждое свойство этого отношения, не являющийся первичным, не транзитивно зависит от каждого возможного ключа этого отношения.
Транзитивная зависимость выявляет дублирование данных в одном отношении. Если А, В и С - три свойства одного отношения и С зависит от В, а В от А, то говорят, что С транзитивно зависит от А. Преобразование в третью нормальную форму происходит за счет разделения исходного отношения на два.
Для исключения ссылок на длинные текстовые значения, последние обычно нумеруют. Введение нумерации приводит к тому, что в отношении появляется несколько возможных ключей. В этом случае может оказаться, что результирующее отношение которое ранее находилось в 3 НФ может содержать лишние функциональные зависимости. Для исключения этого явления Бойс и Кодд предложили БКНФ. Отношение находится в БКНФ тогда и только тогда, если существующая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа.
Отношение R находится в 4 НФв том и только в том случае, когда существует многозначная зависимость AÞB, а все остальные атрибуты отношения В функционально зависят от атрибута А.
Существуют также 5 НФ и 6 НФ.
Переход к предварительным отношениям производится с использованием построенной ER – диаграммы по специальным правилам.
Всего известно 7 правил, из которых первые 6 применяют для бинарных связей, а 7-е для тернарных связей и связей более высоких порядков. Рассмотрим первые 6 правил.
1. Если степень связи 1:1 и класс принадлежностей обеих сущностей обязательный, то формируется одно отношение. Первичным ключом в отношении может быть ключ из двух сущностей.
2. Если степень связи 1:1 и класс принадлежностей одной сущности обязательный, а другой сущности необязательный, то под каждую сущность формируется отношение. Первичным ключами в отношениях являются ключи соответствующих сущностей. Далее отношению, имеющему обязательный класс принадлежности, добавляется ключ сущности с необязательным классом принадлежности.
3. Если степень связи 1:1 и принадлежностей обеих сущностей необязательный, то необходимо сформировать 3 предварительных отношения: по одному отношению на каждую сущность и одно отношение связи. В отношениях для сущностей первичным ключами будут являться ключи соответствующих сущностей. Отношение связи будет содержать два атрибута, ключи каждой сущности.
4. Если степень связи 1:N (N:1) и класс принадлежности многосвязной сущности обязательный, то для построения предварительных отношений достаточно использовать две таблицы: одно отношение для первой сущности и второе для второй. Ключами в отношениях будут ключи соответствующих сущностей. Кроме того, в отношение для многосвязной сущности добавляется в качестве атрибута связи ключ односвязной сущности.
5. Если степень связи 1:N (N:1) и класс принадлежности многосвязной сущности необязательный, то необходимо сформировать 3 предварительных отношения: по одному отношению на каждую сущность и одно отношение для связи. В отношениях для сущностей первичным ключами будут являться ключи соответствующих сущностей, а отношение связи будет содержать два атрибута, ключи каждой сущности.
6. Если степень связи N:N, то независимо от классов принадлежности обеих сущностей требуется сформировать 3 предварительных отношения: по одному отношению для каждой сущности и одно отношение связи. Первичным ключами отношений для сущностей будут являться ключи соответствующих сущностей, а отношение связи будет содержать ключи обеих сущности.
Таблица применения правил построения предварительных отношений
 КП СС | ОО | ОН | НО | НН |
| 1:1 | ||||
| 1:N | ||||
| N:1 | ||||
| N:N |
рис. 2.2.1
Теперь в соответствии с этими правилами перейдём к предварительным отношениям.
Для сущностей “Заказчик” и “Договор на рекламу” (рис. 2.1.1) подойдёт правило № 4. В результате получим следующие предварительные отношения (рис. 2.2.2):
| Заказчик | |
| * |  Код заказчика |
| ФИО | |
| Адрес | |
| Телефон |
| Договор на рекламу | |
| * + | ИН договора |
| Код заказчика | |
| Характеристики рекламы | |
| Цена | |
| Дата |
1 N
 |
рис. 2.2.2
Для сущностей “Номер выпуска” и “Договор на рекламу” (рис. 2.1.2) подойдёт правило № 6. В результате получим следующие предварительные отношения (рис. 2.2.3):
| Номер выпуска | |
| * * * | ИН периодического издания |
| ИН выпуска | |
| Год издания | |
| Число страниц |
| Связь Реализация договора | |
| + |  ИН периодического издания |
| + | ИН выпуска |
| + | Год издания |
| + | ИН договора |
| + | ИН характеристики |
| Договор на рекламу | |
| * |  ИН договора |
| Код заказчика | |
| Характеристики рекламы | |
| Цена | |
| Дата |
1
 |
N
рис. 2.2.3
Для сущностей “Договор на рекламу” и “Характеристики рекламы” (рис. 2.1.3) подойдёт правило № 6. В результате получим следующие предварительные отношения (рис. 2.2.4):
| Характеристики рекламы | |
| * | ИН характеристики |
| Место размещения | |
| Размер | |
| Цвет | |
| Кол-во повторов |
| Договор на рекламу | |
| * |  ИН договора |
| Код заказчика | |
| Характеристики рекламы | |
| Цена | |
| Дата |
| Связь Реализация договора | |
| + | ИН периодического издания |
| + | ИН выпуска |
| + | Год издания |
| + | ИН договора |
| + |  ИН характеристики |
 | |||
 | |||
N
рис. 2.2.4
Для сущностей “Непериодические издания” и “Издание” (рис. 2.1.4) подойдёт правило № 2. В результате получим следующие предварительные отношения (рис. 2.2.5):
| Непериодические издания | |
| * | ИН непериодического издания |
| Книга/буклет | |
| Автор |
| Издание | |
| * |  ИН издания |
| Редактор | |
| НАзвание | |
| Серия | |
| Тираж | |
| Цена | |
| Подписано в печать | |
| Формат | |
| Бумага | |
| Гарнитура | |
| Печать | |
| Учредитель |
1
рис. 2.2.5
Для сущностей “Издание” и “Периодические издания” (рис. 2.1.5) подойдёт правило № 2. В результате получим следующие предварительные отношения (рис 2.2.6):
| Издание | |
| * |  ИН издания |
| Редактор | |
| НАзвание | |
| Серия | |
| Тираж | |
| Цена | |
| Подписано в печать | |
| Формат | |
| Бумага | |
| Гарнитура | |
| Печать | |
| Учредитель |
| Периодические издания | |
| * | ИН периодического издания |
| Периодичность | |
| Газета/журнал |
1
 |
рис 2.2.6
Для сущностей “Периодические издания” и “Номер выпуска” (рис. 2.1.6) подойдёт правило № 4. В результате получим следующие предварительные отношения (рис. 2.2.7):
1 N
| Периодические издания | |
| * |  ИН периодического издания |
| Периодичность | |
| Газета/журнал |
| Номер выпуска | |
| + * * * | ИН периодического издания |
| ИН выпуска | |
| Год издания | |
| Число страниц |
 |
рис. 2.2.7
Для сущностей “Номер выпуска” и “Статья” (рис. 2.1.7.) подойдёт правило № 4. В результате получим следующие предварительные отношения (рис. 2.2.8):
| Номер выпуска | |
| + * *+ *+ |  ИН периодического издания |
| ИН выпуска | |
| Год издания | |
| Число страниц |
| Статья | |
| + * + | ИН выпуска |
| Рубрика | |
| Статья | |
| Автор | |
| ИН периодического издания | |
| + | Год издания |
 | ||
| |
рис. 2.2.8
Приводим все отношения к нормальной форме Бойса-Кодда:
Отношение “Статья”
 |
Отношение “Издание”
 |
 | |
 | |
 | |
 |
Отношение “Номер выпуска” будет содержать три ключевых атрибута и один не ключевой атрибут – автор:
Отношение “Периодические издания” будет содержать ключевой атрибут ИН периодического издания
Отношение “Непериодические издания”
|  | |||
 |
Отношение “Заказчик”
 |
Отношение “Договор на рекламу”
 |
Отношение “Характеристики рекламы”
 |
Отношение “Реализация договора” будет состоять только из ключевых атрибутов:
|
Разработка хранимых процедур
Хранимые процедуры — это процессы, выполнение которых происходит непосредственно на сервере баз данных. Все хранимые процедуры в базе данных находятся в специально отведенном списке Stored Procedures.
Рассмотрим разработанные в программе хранимые поцедуры:
Добавление нового издания в таблицу издания, а затем добавление, в зависимости от типа издания, в таблицу периодических или непериодических изданий.
CREATE PROCEDURE Addizd1(
@r char(20),
@n char(20),
@s char(20),
@t int,
@c money,
@p datetime,
@f char(10),
@b char(10),
@g char(10),
@pe char(10),
@y char(20),
@ti varchar (20),
@vidKn char(20),
@vidGz char(20),
@avt char(50),
@per char(20)
@in int – шифр издания
Процедура добавления нового издания, причем ин издания ни в коем случае не должен повторяться, и данная процедура позволяет не допустить добавление нового издания с одинаковым кодом в две разные таблицы: периодические и непериодические.
@r -редактор
@n -название
@s -серия
@t -тираж
@c -цена,
@p –дата передачи в печать
@f -формат,
@b -бумага,
@g -гарнитура
@pe -печать
@y -учредитель
@ti -тип
@in шифр непериодического издания
@vidKn –вид: книга/буклет
@avt - автор
@in -шифр периодического издания
@per – периодичность издания
@vidGz –вид: газета/журнал
--Изменение издания
CREATE PROCEDURE Cngizd(
@in int,
@red char(20),
@name char(20),
@ser char(20),
@tir int,
@price money,
@podp datetime,
@form char(10),
@bym char(10),
@garn char(10),
@pec char(10),
@ycr char(20)
)
процедура изменения изданий (периодических и непериодических),
проверки: на наличие базовой таблицы и записи с таким кодом и если с записью не связаны дочерние записи:
редактор=@red,
название=@name,
серия=@ser,
тираж=@tir,
цена=@price,
подписано_в_печ=@podp ,
формат=@form,
бумага=@bym,
гарнитура=@garn,
печать=@pec,
учредитель=@ycr
--удаление непериодического издания по ин издания:
CREATE PROCEDURE Delizd(
@in int --Код удаляемого издания
--Изменение заказчика
CREATE PROCEDURE CngZak(
@fio char(50),
@adress char(50),
@kod int,
@tel char(20)
)
Процедура изменения заказчика по коду заказчика, если информация о данном заказчике присутствует в базе (если такая базовая таблица есть и запись с таким кодом тоже есть, и если с записью не связаны дочерние записи):
ФИО=@fio,
Адрес=@adress,
Тел=@tel
--Удаление заказчика, у которого не заключены договора на рекламу:
CREATE PROCEDURE DelZak(
@kod int – код удаляемого заказчика
--Добавление нового заказчика рекламы
CREATE PROCEDURE AddZakazchik(
@fio char (50),
@adress char (100),
@tel char (20),
@kod int)
@fiо – ФИО заказчика
@adress – адрес места жительства заказчика
@tel – его телефон
--процедура позволяет изменить договор на рекламу
CREATE PROCEDURE CngDog_na_rek(
@in int,
@xar char(20),
@price money,
@kod int,
@date datetime
)
если такая базовая таблица есть и запись с таким кодом тоже есть, и если с записью не связаны дочерние записи
Код_зак=@kod, - код заказчика
хар_рек=@xar, -характеристика рекламы: социальная/коммерческая
цена=@price, -стоимость размещения данной рекламы
дата=@date -дата составления договора
--Удаление Договора, не имеющего связанных с ним записей в других таблицах:
CREATE PROCEDURE DelDog_na_rek(
@in int - номер удаляемого договора на рекламу
--Добавление нового договора на рекламу, не имеющего уже ранее созданного номера:
CREATE PROCEDURE AddDog_ns_rek(
@in int,
@kod int,
@xar char(20),
@price money,
@date datetime
)
процедура позволяет добавить:
@kod – код заказчика рекламы
@xar –характеристика рекламы
@price стоимость рекламы
@date - дата заключения договора
--Изменение номера выпуска
CREATE PROCEDURE cngNomVip(
@in int,
@inv int,
@god datetime,
@chislo int)
--если такая базовая таблица есть
-- Если запись с таким кодом есть
-- Если с записью не связаны дочерние записи
число_стр=@chislo – то можно изменить число страниц в данном номере выпуска
--Добавление номера выпуска
CREATE PROCEDURE AddNomVip(
@in int,
@inv int,
@god datetime,
@chislo int)
процедура добавляет новую запись, если таковая уже не была введена ранее:
@in – ин периодического издания
@inv ин выпуска
@god год издания
@chislo число страниц
Данная процедура удаляет номер выпуска, если запись с таким кодом есть и если с записью не связаны дочерние записи:
CREATE PROCEDURE DelNomVip(
@in int,
@inv int,
@god datetime
)
--Добавление нового периодического издания, если такой записи еще не было в базе:
CREATE PROCEDURE AddIzdPN(
@in int,
@pp char(20),
@gaz char(20)
)
(@in – ин периодического издания
@pp -периодичность
@gaz –тип: газета/журнал)
--Добавление нового непериодического издания, если такой записи еще не было в базе:
CREATE PROCEDURE AddIzdN(
@in int,
@kn char(20),
@avt char(50)
)
(@in ин непериодического издания
@kn –тип: книга/буклет
@avt - автор)
--Добавление новой реализации договора на рекламу, если такой записи еще не было в базе:
CREATE PROCEDURE AddRealDog(
@id int,
@ix int,
@ii int,
@iv int,
@gi datetime
)
(@id, ин договора на рекламу,
@ix -ин характеристики,
@ii -ин выбранного издания, где будет размещена реклама,
@iv -ин номера выпуска,
@gi - год выпуска издания
)
--выборка всех договоров с их характеристиками и реализациями, сортированных по названию издания и номерам договора:
CREATE PROCEDURE seldog
--Изменение характеристики рекламы
CREATE PROCEDURE CngXar_rek(
@in int,
@mesto char(20),
@razm char(10),
@cvet char(10) ,
@kol int
)
процедура изменяет характеристики рекламы по @in :
место_разм=@mesto, -место размещения
размер=@razm , -размер рекламы
цвет=@cvet , -цвет
кол_повт=@kol -кол-во повторов
--Добавление характеристики рекламы, если такой записи еще не было:
CREATE PROCEDURE AddXar_rek(
@in int,
@mesto char(20),
@razm char(10),
@cvet char(10),