Разработка информационной системы "Компьютерная фирма"

Для однозначного определения  записей в каждом из отношений  выделен первичный ключ (простой или составной).

Внешние ключи для  отношений БД:

• в отношении «Товар» – это ключ «Код_сервисного_центра»;
• в отношениях «Поставщик_Товар», «Продажа», «Серийный_номер» и «Гарантийный_журнал» – это ключ «Штрихкод_товара»;
• в отношении «Поставщик_Товар» – это ключ «Код_поставщика»;
• в отношении «Кредитный_договор» – это ключ «Номер_паспорта_клиента»;
• в отношении «Продажа» – это ключ «Код_договора»;
• в отношении «Гарантийный_талон» – это ключ «Номер_паспорта_сотрудника»;
• в отношениях  «Продажа» и «Серийный_номер» – это ключ «Код_гарантийного талона»;
• в отношении «Гарантийный_журнал» – это ключ «Серийный_номер_экземпляра».

На логическом уровне проектирования в моделируемой базе данных присутствуют следующие  типы связей между описанными сущностями:

• неиденцифицирующие связи;
• иденцифицирующие связи;
• связи многие-ко-многим.

Связь между сущностями «Сервисный_центр» и «Товар» неидентифицирующая, разрешающая присутствие нулей, т.к. каждый товар может иметь (но не обязательно) сервисный центр, в котором будет производиться его (товара) гарантийное обслуживание в случае поломки. Тип связи 1 ко многим, т.к. в один сервисный центр может быть закреплен за несколькими товарами.

Связь между сущностями «Кредитный_договор» и «Продажа» неидентифицирующая, разрешающая присутствие нулей, т.к. по любому товару может быть (но не обязательно) заключен договор по покупке товара в кредит. Тип связи 1 ко многим, т.к. в один и тот же договор может быть заключен на покупку сразу нескольких товаров.

Связь между сущностями «Клиент» и «Кредитный_договор» неидентифицирующая, не разрешающая присутствие нулей, т.к. любой кредитный договор обязаетельно заключается определенным клиентом. Тип связи 1 ко многим, т.к. в один и тот же клиент может заключать несколько договоров на покупку товаров в кредит.

Связь между сущностями «Гарантийный_талон» и «Серийный_номер» неидентифицирующая, не разрешающая присутствие нулей, т.к. серийный номер любого проданного экземпляра товара обязательно указан в каком-либо гарантийном талоне. Тип связи 1 ко многим, т.к. в одном гарантийном талоне может храниться информация о нескольких серийных номерах.

Связь между сущностями «Сотрудник» и «Гарантийный_талон» неидентифицирующая, не разрешающая присутствие нулей, т.к. каждый гарантийный талон обязательно выдается сотрудником фирмы (обычно продавцом). Тип связи 1 ко многим, т.к. в один и тот же сотрудник может выдать несколько гарантийных талонов.

Связь между сущностями «Поставщик» и «Поставщик_Товар» идентифицирующая, т.к. для определения ассортимента товаров поставщика необходима информация о нем. Тип связи 1 ко многим, т.к. один и тот же поставщик может предлагать несколько товаров для оптовой закупки.

Связь между сущностями «Товар» и «Поставщик_Товар» идентифицирующая, т.к. для определения ассортимента товаров поставщика необходима информация о товаре. Тип связи 1 ко многим, т.к. товар может предлагаться несколькими поставщиками.

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

Связь между сущностями «Гарантийный_талон» и «Продажа» идентифицирующая, т.к. для регистрирования факта продажи необходима информация о гарантийном талоне. Тип связи 1 ко многим, т.к. гарантийный талон может включать несколько проданных товаров.

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

Связь между сущностями «Гарантийный_талон» и «Серийный_номер» идентифицирующая, т.к. для регистрирования факта гарантийного обслуживания необходими серийный номер экземпляра. Тип связи 1 ко многим, т.к. один и тот же экземпляр может обслуживаться несколько раз.

Связь между сущностями «Товар» и «Готовое_решение» многие-ко-многим, т.к. один товар может включаться в разные решения (конфигурации), а в одно готовое решение может включаться несколько товаров.

Связь между сущностями «Поставщик» и «Товар» многие-ко-многим (уже разрешена через сущность «Поставщик_Товар»), т.к. один товар может поставляться многими поставщиками, а один поставщик может предлагать несколько товаров.

ER-диаграмма логического уровня представлена на рисунке 11.

Рисунок 11 – ER-диаграмма логического уровня

2.2.2 ER-диаграмма физического уровня. Ограничения ссылочной целостности. Определение триггеров

Физическая модель данных зависит от конкретной СУБД. В ней  содержится информация обо всех объектах БД.  Одной и той же логической модели может соответствовать несколько разных физических. В физической модели важно описать всю информацию о конкретных физических объектах – таблицах, колонках, индексах, процедурах. 

Проверим, удовлетворяют  ли все имеющиеся отношения соответствующим наборам ограничений.

Первая нормальная форма  требует, чтобы значения всех атрибутов отношения были атомарными. При рассмотрении информационной модели было отмечено, что значения атрибутов всех отношений логически разделить на элементы нельзя и, следовательно, они удовлетворяют условию первой нормальной формы. Пример, рассмотрим таблицу «Кредитный_договор». Ключевой атрибут в ней – «Код_договора» не может быть разделен на элементы.  Не ключевые аттрубуты – «Номер_паспорта_клиента», «Первоначальный_взнос», «Ежемесячная_выплата», «Срок_оплаты» также являются атомарными.

Вторая нормальная форма  требует, чтобы отношение находилось в первой нормальной форме, и каждый не ключевой атрибут функционально полно зависел от первичного ключа. И это требование также выполняется в рассматриваемой модели. Пример, рассмотрим таблицу «Кредитный_договор». Ключевой аттрубут в ней – «Код_договора». Не ключевые аттрубуты – «Номер_паспорта_клиента», «Первоначальный_взнос», «Ежемесячная_выплата», «Срок_оплаты» зависят функционально полно только от первичного ключа.

Для нормализации схем отношений к третьей нормальной форме необходимо чтобы каждый детерминант (любой атрибут, от которого функционально полно зависит некоторый другой атрибут) является возможным ключом. В рассматриваемой модели это условие соблюдается. Пример, рассмотрим таблицу «Кредитный_договор». Как было отмечено выше, все неключевые атрибуты функционально полно зависят от первичного ключа, т.е. первичный ключ является детерминантом.

Реализация ссылочной  целостности:

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

Типы данных

1. Рассмотрим таблицу  «Товар». Ключевое поле этой таблицы Штрихкод_товара содержит значение стандартного EAN-13 штрихкода товара. Код представляет собой набор из 13 цифр (например, 4606782003756). Следовательно, тип данных поля Штрихкод_товара должен быть строковым с длиной не менее 13 символов. Выбираем тип varchar(20).

2. В таблице «Товар» имеется поле Название_товара. Оно представляет собой коммерческое название товара и может содержать символы национального алфавита (например, «Термопаста «КПТ-8»). Следовательно, тип данных поля Название_товара должен быть строковым с поддержкой национальных алфавитов и длиной не менее 30 символов. Выбираем тип nvarchar(50).

3. В таблице «Товар» имеется поле Описание. Это поле содержит подробное описание технических характеристик товара и может содержать символы национального алфавита (например, «Микрофон MIC-140 динамический для караоке (беспроводной)»). Следовательно, тип данных поля Описание должен быть текстовым с поддержкой национальных алфавитов. Выбираем тип ntext.

4. В таблице «Гарантийный_талон» имеется поле Дата_время. Это поле содержит время, день, месяц, год даты, когда был выдан гарантийный талон, а значит и осуществлена продажа. Тип данных для него – datetime.

5. Рассмотрим таблицу «Товар». Поле Количество_склад этой таблицы  представляет собой число имеющихся в данный момент экземпляров данного товара на складе. Следовательно, тип данных поля Количество_склад должен быть числовым целым. Выбираем тип int.

6. Рассмотрим таблицу «Товар». Поле Цена этой таблицы содержит розничную цену данного товара. Следовательно, тип данных поля Цена должен быть денежным. Выбираем тип money.

Для приложения были разработаны следующие триггеры:

• tIU_Empl срабатывает при вставке или обновлении записи в таблице «Сотрудник». Он проверяет на корректность вставленные поля таблицы (например, на ненулевое значение). В случае ошибки записи изменены не будут.
• tIU_Service срабатывает при вставке или обновлении записи в таблице «Сервисный_центр». Он проверяет на корректность вставленные поля таблицы (например, на ненулевое значение). В случае ошибки записи изменены не будут.
• tIU_Goods срабатывает при вставке или обновлении записи в таблице «Товар». Он проверяет на корректность вставленные поля таблицы (например, на ненулевое значение). В случае ошибки записи изменены не будут.
• tIU_Vendor срабатывает при вставке или обновлении записи в таблице «Поставщик». Он проверяет на корректность вставленные поля таблицы (например, на ненулевое значение). В случае ошибки записи изменены не будут.
• tD_GoodsOwner проверяет права на удаление пользователем записи из таблицы «Товар». Если пользователь не является владельцем базы данных, то прав на удаление он не имеет. Запись удалена не будет;

ER-диаграмма физического уровня показана на рисунке 12.

Рисунок 12 – ER-диаграмма физического уровня

 

2.2.3 Определение представлений, хранимых процедур серверной компоненты

Представление (View) для конечных пользователей выглядит как таблица, но при этом само не содержит данных, а лишь представляет данные, расположенные в таблице. Физически представление реализовано в виде SQL-запроса, на основе которого производится выборка данных из одной или нескольких таблиц или представлений.

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

Для приложения были разработаны  следующие представления:

• GetAllClientPass – информация обо всех номерах паспортов клиентов. Содержит информацию из таблицы «Клиент»;
• GetAllEmplFIO – информация обо всех полных именах сотрудников (ФИО). Содержит информацию из таблицы «Сотрудник»;
• GetAllEmplPass – информация обо всех номерах паспортов сотрудников. Содержит информацию из таблицы «Сотрудник»;
• GetAllEmpPost – информация обо всех различных должностях сотрудников. Содержит информацию из таблицы «Сотрудник»;
• GetAllGoodsCategories – информация обо всех категориях товаров, имеющихся на данный момент в базе. Содержит информацию из таблицы «Товар»;
• GetAllGoodsCodes – информация обо всех штрихкодах товаров имеющихся на данный момент в базе. Содержит информацию из таблицы «Товар»;
• GetAllGoodsNames – информация обо всех названиях товаров имеющихся на данный момент в базе. Содержит информацию из таблицы «Товар»;
• GetAllPaymentTypes – информация обо всех типах оплаты товара при продаже. Содержит информацию из таблицы «Продажа»;
• GetAllReplacementCodes – информация обо всех кодах направлений на замену товара по гарантии. Содержит информацию из таблицы «Гарантийный_журнал»;
• GetAllServiceNames – информация обо всех названиях сервисных центров. Содержит информацию из таблицы «Сервисный_центр»;
• GetAllVendorNames – информация обо всех названиях поставщиков. Содержит информацию из таблицы «Поставищик»;

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

Для приложения были разработаны  следующие хранимые процедуры:

• DeleteEmpl  – для удаления данных из таблицы «Сотрудник»;
• DeleteGoods  – для удаления данных из таблицы «Товар»;
• DeleteVendor  – для удаления данных из таблицы «Поставщик»;
• DeleteVendorAssort  – для удаления данных из таблицы «Поставщик_Товар»;
• GetEmplByFIO – для поиска сотрудника по его полному имени в таблице «Сотрудник»;
• GetEmplByPass – для  поиска сотрудника по его номеру паспорта в таблице «Сотрудник»;
• GetGoodsByCode – для  поиска товара по его штрихкоду в таблице «Товар»;
• GetGoodsByName – для  поиска товара по его названию в таблице «Товар»;
• GetVendorAssort – для  поиска поставщиков, предлагающий данный товар, в таблице «Поставщик_Товар»;
• GetVendorByCode – для  поиска поставщика по его коду в таблице «Поставщик»;
• GetVendorByName – для  поиска поставщика по его названию в таблице «Поставщик»;
• InsertEmpl – для вставки данных в таблицу «Сотрудник»;
• InsertGoods – для вставки данных в таблицу «Товар»;
• InsertVendor – для вставки данных в таблицу «Поставщик»;
• InsertVendorAssort  – для вставки данных в таблицу «Поставщик_Товар»;
• test – для проверки соединения с базой данных;
• UpdateEmpl – для обновления данных в таблице «Сотрудник»;
• UpdateGoods – для обновления данных в таблице «Товар»;
• UpdateVendor – для обновления данных в таблице «Поставщик»;
• UpdateVendorAssort – для обновления данных в таблице «Поставщик_Товар».