247

Разработка информационного обеспечения автоматизированной системы Муниципальный заказ г. Сургут

Курсовая

Информатика, кибернетика и программирование

Проектирование базы данных ER-методом. Генерация SQL-скрипта для создания базы данных. Обеспечение взаимодействия структурных подразделений Администрации города при размещении и контроле исполнения муниципальных заказов на поставку товаров.

Русский

2012-11-14

427 KB

54 чел.

Введение               3

Определения, обозначения и сокращения        4

1 Описание задачи            6

  1.1 Размещение муниципального заказа      6

  1.2 Подготовка и проведение открытого конкурса    6

  1.3 Контроль исполнения муниципального контракта   8

2 Постановка задачи           10

2.1 Характеристики комплекса задач      10  2.1.1 Назначение комплекса задач      10

        2.1.2 Наименования подразделений, определяющих условия и временные характеристики  конкретного решения задачи         10

    2.1.3 Описание автоматизируемых функций    11

2.2 Выходная информация         12

2.3 Входная информация         12

3 Разработка информационного обеспечения автоматизированной системы “Муниципальный заказ г. Сургут”      17

3.1 Требования к информационному обеспечению    17

3.1.1 Выбор СУБД          17

3.1.2 Организация хранения данных      17

3.2 Проектирование базы данных ER-методом     18

3.2.1 Построение диаграммы ER-типа      18       3.2.2 Генерация  набора предварительных отношений  22

3.2.3 Исследование набора отношений на избыточность  23

3.3 Проектирование базы данных с использованием

     CASE-средства ERwin                26 3.3.1 Создание логической и физической модели данных  26

 3.3.2 Оптимизация быстродействия при работе с базой

                       данных           29

 3.3.3 Поддержка бизнес-логики       30

  3.3.3.1 Определение пользователей, ролей и прав

                                  доступа          30

  3.3.3.2 Создание триггеров       31

  3.3.3.3 Создание хранимых процедур     32

  3.3.3.4 Создание представлений      33

3.4 Генерация SQL-скрипта для создания базы данных   34

4 Тестирование базы данных          35

Заключение              36

Список использованных источников         37

Приложение А

Приложение Б   SQL-скрипт           38

Введение

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

Целью разработки автоматизированной системы (далее - АС)  "Муниципальный заказ г. Сургут" является обеспечение взаимодействия структурных подразделений Администрации города при размещении и контроле исполнения муниципальных заказов на поставку товаров, выполнение работ и оказание услуг в целях обеспечения нужд муниципального образования городского округа город Сургут. Поскольку ответственность за размещение и контроль исполнения муниципальных заказов несет Департамент по экономической политике (далее - ДЭП), основанием разработки АС является упрощение, уменьшение трудоемкости процессов и функций, выполняемых ДЭП. Таким образом, для более продуктивной работы системы муниципального заказа, ее автоматизация необходима, к тому же это предусмотрено законодательством РФ. Разработка АС "Муниципальный заказ г. Сургут" является актуальной задачей, всвязи с недавними изменениями законодательства РФ, регламентирующего формирование и размещение муниципального заказа в муниципальных образованиях.

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

Определения, обозначения и сокращения

номер

АС – автоматизированная система

ДЭП – Департамент по экономической политике

ОСП ответственные структурные подразделения Администрации города

БД – база данных

СУБД – система управления БД  

SQL

ER-метод

гос. – государственный

 В данной пояснительной записке применяются следующие термины с соответствующими определениями.

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

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

Система муниципального заказа - упорядоченная система отношений, определяющая и регулирующая деятельность органов городского самоуправления по формированию, размещению и реализации муниципального заказа.

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

Закупки - приобретение заказчиком товаров, работ, услуг за счет средств бюджета города способами, определяемыми настоящим Положением.

Продукция - товары, работы, услуги.

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

Муниципальный заказчик (далее именуется – Заказчик) – администрация города, ее структурные подразделения, муниципальные учреждения, осуществляющие закупку продукции для муниципальных нужд.

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

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

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

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

Реестр муниципального заказа - перечень, содержащий объем, номенклатуру и стоимость закупаемой продукции, финансируемый из бюджета города.

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

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

Организатор торгов (конкурса) - департамент по экономической политике администрации города.

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

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

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

Уполномоченная организация – организация, отобранная в результате открытых торгов на выполнение муниципального заказа по поставке товаров, работ, услуг, широко представленных на рынке.

1 Описание задачи

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

1.1 Размещение муниципального заказа

Под размещением муниципальных заказов подразумевается осуществление путем проведения торгов (открытых конкурсов) подбора исполнителя для выполнения муниципального контракта. Конкурсы проводятся при размещении следующих заказов Департамента по социальным вопросам и здравоохранению:

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

1.2 Подготовка и проведение открытого конкурса

Организатор конкурса:

  •  принимает и регистрирует заказы;
  •  определяет дату проведения муниципальных торгов (конкурсов);
  •  разрабатывает форму заявки на участие в торгах (конкурсах) и формы конкурсной документации с учетом специфики муниципального заказа;
  •  готовит информационное сообщение о проведении торгов (конкурса).

Информационное сообщение о проведении конкурса содержит:

  •  наименование заказа;
  •  время, дату и место  проведения конкурса (заседание межотраслевой конкурсной комиссии);
  •  адрес и телефон для приема заявок на участие в торгах (конкурсе);
  •  стоимость конкурсной документации и реквизиты ее оплаты;
  •  порядок и реквизиты получения конкурсной документации;
  •  критерии оценки конкурсных заявок;
  •  критерии отбора победителей, в том числе как один из обязательных критериев - сроки поставки продукции;
  •  реквизиты организатора конкурса.

При размещении муниципального заказа путем проведения конкурса создаются комиссии, порядок работы которых утверждается распоряжением Администрации города. Организатор конкурса осуществляет прием и регистрацию заявок участников размещения заказа, представление заявок участников размещения заказа на рассмотрение комиссий. Заявка представляет собой пакет документов: конверт№1 и конверт№2. Финансовые предложения принимаются в запечатанных конвертах (конверт№2), вскрытие которых производится на заседании конкурсной комиссии. В конверт №1 вкладываются следующие документы:

  •  типовая форма оформления конверта№1;
  •  для юридического лица - нотариально заверенные копии учредительных документов, в том числе изменения и дополнения к ним; свидетельство о государственной регистрации для юридических лиц зарегистрированных до 01.07.2002; свидетельство о внесении в единый государственный реестр юридических лиц;
  •  для индивидуального предпринимателя - нотариально заверенная копия свидетельства о регистрации в качестве индивидуального предпринимателя и его паспортные данные;
  •  нотариально заверенные копии лицензий на заявленные виды деятельности, подлежащие лицензированию;
  •  копия бухгалтерского баланса предприятия;
  •  копия отчета о прибылях и убытках с подлинной отметкой налогового органа;
  •  документ, подтверждающий оплату за предоставление конкурсной документации;
  •  резюме (общие сведения о претенденте),
  •  список документов, вложенных  в данный конверт.

В конверт №2 вкладываются следующие документы:

  •   типовая форма оформления конверта№2,
  •   конкурсная заявка,
  •   таблица цен,
  •   форма оплаты,
  •   сроки выполнения работ. 

После регистрации данных последнего претендента в журнале учета оформляется запись о прекращении приема заявок на проведение торгов.

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

Уведомление заказчику (победителю) содержит:

  •  ссылку на конкретное решение конкурсной комиссии;
  •  полное наименование Заказчика (победителя) и реквизиты для связи с ним;
  •  основные условия договора (порядок оплаты, сроки исполнения, сумма договора).

Реквизиты Заказчика:

  •  Адрес заказчика;
  •  Ф.И.О. ответственного от заказчика;
  •  контактный телефон.

После определения победителя конкурса ДЭП один экземпляр протокола оценки и сопоставления заявок направляет победителю конкурса, второй экземпляр остается в ДЭП. Заверенные копии протокола направляются в ОСП для обеспечения заключения муниципального контракта.

Муниципальный контракт в течение 3 дней после заключения представляется в ДЭП для внесения в реестр муниципальных контрактов с присвоением регистрационного номера. Внесенный в реестр муниципальный контракт представляется в департамент финансов. Подлежащие хранению материалы и документация (в том числе протоколы, составленные в ходе проведения конкурса, заявки на участие в конкурсе, конкурсная документация, аудиозаписи) хранятся не менее трех лет.

1.3 Контроль исполнения муниципального контракта

По результатам исполнения муниципального заказа ДЭП проводит оценку эффективности системы муниципальных закупок по установленным показателям:

  •  N1 – среднее количество поставщиков, участвующих в одном конкурсе;
  •  d1 – доля открытых конкурсов в общей стоимости закупок;
  •  dobj – среднее количество обжалований на один конкурс;
  •  Зрк – затраты на организацию конкурса;
  •  Урк – удельный вес расторгнутых контрактов в общем числе заключенных контрактов;

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

2 Постановка задачи

2.1 Характеристики комплекса задач

2.1.1 Назначение комплекса задач

 

Комплекс задач АС "Муниципальный заказ г. Сургут" предназначен для обеспечения взаимодействия структурных подразделений Администрации города при размещении и контроле исполнения муниципальных заказов Департамента по социальным вопросам и здравоохранению на поставку товаров, выполнение работ и оказание услуг. Комплексом задач АС "Муниципальный заказ" предусматривается обеспечение подготовки и проведения открытых конкурсов, хранения сопутствующей информации и документации, учет исполнения муниципальных контрактов, а также анализ организации и функционирования системы муниципального заказа. К задачам разработки информационного обеспечения  АС "Муниципальный заказ г. Сургут" относится:

  •  проектирование БД,
  •  определение бизнес-логики,
  •  создание логической и физической модели данных,
  •  обеспечение поддержки бизнес-логики,
  •  создание SQL-скрипта для разработки программного обеспечения автоматизированной системы “Муниципальный заказ г. Сургут”.

  1.  Наименования подразделений, определяющих  условия и  временные характеристики  конкретного решения задачи                                                                               

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

 

  1.  Описание автоматизируемых функций

Исходные данные

Формирование и размещение муниципального заказа в муниципальном образовании город окружного значения Сургут осуществляется в соответствии с:

  •  федеральным законом от 28.08.95 № 154-ФЗ "Об общих принципах организации местного самоуправления в Российской Федерации",
  •  федеральным законом от 25.09.97 № 126-ФЗ "О финансовых основах местного самоуправления в Российской Федерации",
  •  Гражданским кодексом Российской Федерации,
  •  Указом Президента РФ от 08.04.97 № 305 "О первоочередных мерах по предотвращению коррупции и сокращению бюджетных расходов при организации закупки продукции для государственных нужд",
  •  методическими указаниями и рекомендациями Министерства экономики РФ,
  •  законами Ханты - Мансийского автономного округа,
  •  Уставом муниципального образования город окружного значения Сургут,
  •   правовыми актами муниципального образования город окружного значения Сургут,
  •  Положением о муниципальном заказе в муниципальном образовании город окружного значения Сургут,
  •  Положением об отраслевой конкурсной комиссии по здравоохранению и социальным вопросам.

Характеристика функциональной структуры.

Разрабатываемая АС "Муниципальный заказ" представляет собой совокупность трех подсистем, каждая из которых должна обеспечивать соответствующие функциональные возможности для реализации своих целей.

Подсистема регистрации и учета конкурсных заказов и заявок, обеспечивающая подготовку и проведение торгов:

  •  регистрация заказов на проведение конкурса;
  •  создание информационного сообщения  о проведении конкурса;
  •  регистрация заявок на участие в конкурсе;
  •  формирование журнала учета, списка поставщиков-участников торгов;
  •  формирование протокола проведенного конкурса.

Подсистема публикации, обеспечивающая предоставление информации о проведении торгов для желающих принять участие в них, а также об итогах торгов для зарегистрированных участников в сети «Интернет»:

  •  публикация информационного сообщения  о проведении конкурса;
  •  публикация результатов проведенного конкурса;
  •  регистрация пользователей системы;
  •  авторизированный вход в систему.

Подсистема контроля исполнения муниципального заказа, обеспечивающая обработку и анализ результатов проведенного конкурса:

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

2.2 Выходная информация

Выходной информацией является  SQL-скрипт, приведенный в приложении Б.

2.3 Входная информация

В таблице 2.1 приведен перечень и описание входных сообщений.

Таблица 2.1 - Описание входных сообщений

Характеристика

Значение и содержание

Идентификатор

1

Наименование

Заказ на проведение конкурса

Форма представления

Документ

Вид представления

Таблица

Источник

Заказчик муниципального учреждения

Периодичность поступления

По необходимости

Состав

  1.  наименование заказа;
  2.  наименования заказчиков;
  3.  основные параметры продукции;
  4.  перечень, стандарты и объемы, требования к качеству продукции;
  5.  необходимые сроки исполнения муниципального заказа

Назначение

Основание для проведения конкурса

Идентификатор

2

Наименование

Резюме (общие сведения о претенденте)

Форма представления

Документ

Вид представления

См. Рисунок А.1, приложение А

Источник

Претендент на исполнение муниципального заказа

Периодичность поступления

В течение установленного срока при формировании конкурса

Продолжение таблицы 2.1

Состав

  1.  полное и краткое наименование;
  2.  регион;
  3.  ИНН и КПП;
  4.  дата и номер регистрации в налоговом органе, наименование налогового органа;
  5.  контактные телефоны, факс, e-mail;
  6.  юридический адрес;
  7.  адрес исполнительного органа;
  8.  уставный капитал;
  9.  количество акций;
  10.  наличие расчетных и валютных счетов;
  11.  состояние имущества;
  12.  основные виды оборудования и транспорта (для выполнения работ);
  13.  основные виды складских помещений;
  14.  квалификация и опыт работников, предлагаемых на ключевые должности с целью реализации;
  15.  сведения о рабочем персонале

Назначение

Формирование предварительного и окончательного списка поставщиков-участников торгов, формирование аналитической таблицы

Идентификатор

3

Наименование

Конкурсная заявка 

Форма представления

Документ

Вид представления

См. Рисунок А.2, приложение А

Источник

Претендент на исполнение муниципального заказа

Периодичность поступления

В течение установленного срока при формировании конкурса

Состав

Дата

Назначение

Проведение конкурса, формирование аналитической таблицы

Идентификатор

4

Наименование

Таблица цен

Форма представления

Документ

Вид представления

См. Рисунок А.3, приложение А

Источник

Претендент на исполнение муниципального заказа

Периодичность поступления

В течение установленного срока при формировании конкурса

Состав

Предлагаемые цены по позициям заказа

Продолжение таблицы 2.1

Назначение

Проведение конкурса, формирование аналитической таблицы

Идентификатор

5

Наименование

Форма оплаты

Форма представления

Документ

Вид представления

См. Рисунок А.4, приложение А

Источник

Претендент на исполнение муниципального заказа

Периодичность поступления

В течение установленного срока при формировании конкурса

Состав

  1.  размер предоплаты,
  2.  оплата по контракту по факту выполненных работ,
  3.  оплата по факту выполненных работ в течение 30 банковских дней

Назначение

Проведение конкурса, формирование аналитической таблицы

Идентификатор

6

Наименование

Сроки выполнения работ

Форма представления

Документ

Вид представления

См. Рисунок А.5, приложение А

Источник

Претендент на исполнение муниципального заказа

Периодичность поступления

В течение установленного срока при формировании конкурса

Состав

Предлагаемые сроки выполнения работ, оказания услуг по позициям заказа

Назначение

Проведение конкурса, формирование аналитической таблицы

Идентификатор

7

Наименование

Контракт на исполнение муниципального заказа

Форма представления

Документ

Вид представления

Документ

Источник

Заказчик и победитель торгов

Периодичность поступления

Единожды, после заключения

Состав

  1.  реквизиты контракта (см. Таблицу 2, 5),
  2.  сумма контракта,
  3.  срок действия контракта,
  4.  цена контракта

Назначение

Осуществление контроля выполнения муниципального заказа

Продолжение таблицы 2.1

Идентификатор

8

Наименование

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

Форма представления

Документ

Вид представления

См. Рисунок А.6, приложение А

Источник

Заказчик

Периодичность поступления

Ежеквартально, в срок до 5 числа месяца, следующего после отчетного квартала

Состав

  1.  сумма бюджетных ассигнований;
  2.  номер реестра муниципального заказа;
  3.  фактическое расходование средств

Назначение

Осуществление контроля выполнения муниципального заказа

Идентификатор

9

Наименование

Сведения об исполнении муниципальных контрактов

Форма представления

Документ

Источник

Заказчик

Вид представления

См. Рисунок А.7, приложение А

Периодичность поступления

Ежеквартально, в срок до 5 числа месяца, следующего после отчетного квартала

Состав

  1.  сведения об исполнении контракта;
  2.  сведения о наличии претензий к поставщику

Назначение

Осуществление контроля исполнения муниципальных контрактов

В таблице 2.2 приведен перечень и описание структурных единиц информации входных сообщений.

Таблица 2.2 - Описание структурных единиц информации входных сообщений

Характеристика

Значение и содержание

Идентификатор

1

Наименование

Основные виды оборудования и транспорта

Идентификатор входного сообщения

2

Продолжение таблицы 2.2

Состав

  1.  Вид оборудования и транспорта,
  2.  описание,
  3.  модель,
  4.  год выпуска,
  5.  количество единиц,
  6.  принадлежность

Идентификатор

2

Наименование

Основные виды складских помещений

Идентификатор входного сообщения

2

Состав

  1.  Наименование объектов,
  2.  характеристики,
  3.  местонахождения,
  4.  общая площадь,
  5.  принадлежность

Идентификатор

3

Наименование

Квалификация и опыт работников

Идентификатор входного сообщения

2

Состав

  1.  Должность;
  2.  образование, учебное заведение, год факультет, специальность;
  3.  стаж работы по специальности;
  4.  стаж работы в данной должности;
  5.  основа

Идентификатор

4

Наименование

Сведения о рабочем персонале

Идентификатор входного сообщения

2

Состав

  1.  Специальность, разряд;
  2.  численность;
  3.  основа;
  4.  общий стаж работы на предприятии

Идентификатор

5

Наименование

Реквизиты контракта

Идентификатор входного сообщения

8

Состав

  1.  Номер регистрации,
  2.  дата регистрации,
  3.  номер протокола

3 Разработка информационного обеспечения автоматизированной системы “Муниципальный заказ г. Сургут”

 

3.1 Требования к информационному обеспечению

Автоматизация технологических и управленческих процессов, без которой немыслимо эффективное решение задач управления государственной структурой, основывается на переработке больших объемов информации. Эффективность автоматизированных информационных систем в значительной мере зависит от того, насколько обеспечивается скорость доступа к данным, их полнота, достоверность, непротиворечивость. Проектируемая АС “Муниципальный заказ г. Сургут” представляет собой интегрированную систему, ядром которой является база данных. Основное направление в разработке автоматизированной информационной системы –  ориентация на использование реляционной СУБД. Сервер БД должен обеспечивать:

  •  поиск в базе данных по заданным условиям,
  •  сравнение, сортировку и предоставление данных в табличном виде,
  •  изменение хранимых данных,
  •  добавление новых данных в базу,
  •  удаление данных из БД,
  •  создание новых структур данных,
  •  выполнение программного кода на сервере.

3.1.1 Выбор СУБД

InterBase представляет собой полнофункциональный SQL-сервер. Сервер регулирует доступ к отдельным записям данных  в пределах БД и обеспечивает монопольный доступ к записям при запросах на изменение данных в записях. Отличительными качествами InterBase являются:

  •  высокая производительность и надежность сервера при минимальных требованиях к техническим средствам,
  •  поддержка стандарта SQL-92,
  •  простота установки и поддержки сервера,
  •  удобный и не требующий специальной подготовки механизм администрирования БД.

Все это делает InterBase оптимальным выбором для реализации корпоративных систем малого и среднего масштаба.

3.1.2 Организация хранения данных

Для обеспечения эффективного хранения данных, а это означает быстрый поиск, обновление данных, защиту от ошибочных вводов, обеспечение конфиденциальности информации и другое, необходима соответствующая их организация. Для быстрого поиска необходимо упорядочение хранимых данных, поддержание связей между ними, контроль на непротиворечивость, обеспечение однократного ввода или изменения при многократном последующем использовании. Ключевую роль при этом играют методы поддержания логических связей между данными. Использованный в разработке АС “Муниципальный заказ г. Сургут” способ организации хранения связей – реляционная модель данных, работу с которой поддерживает СУБД InterBase.

3.2 Проектирование базы данных ER-методом

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

3.2.1 Построение диаграммы ER-типа

1) Выделение сущностей и их ключевых атрибутов

Стержневые сущности и их ключевые атрибуты отражены в таблице 3.1.

Таблица 3.1 - Сущности и их ключевые атрибуты

Сущность

Атрибуты

Заказчик

№З, наименование заказчика, адрес заказчика, ФИО ответственного от заказчика, контактный телефон

Муниципальный заказ

№МЗ, наименование МЗ, дата, срок исполнения

Позиция заказа

№ПЗ, наименование ПЗ, количество

Позиция-Товар

№Т, ед. измерения, параметр, характеристика, стандарт

Позиция-Оборудование

№О, марка, страна-производитель, гарантия, год выпуска

Заявка

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

Юридическое лицо

№Ю, нотариально заверенные копии учредительных документов, свидетельство о гос. регистрации, свидетельство о внесении в единый гос. реестр, юридический адрес

Продолжение таблицы 3.1

Физическое лицо

№Ф, нотариально заверенная копия свидетельства о регистрации в качестве индивидуального предпринимателя, паспорт

Поставщик

№П, тип, полное наименование, ФИО руководителя, телефон руководителя, ФИО заместителя, телефон заместителя, ФИО главного бухгалтера, телефон бухгалтера, регион, ИНН, КПП, факс, e-mail, адрес исполнительного органа, уставный капитал, количество акций, расчетный счет, валютный счет, состояние имущества

Контракт

№К, №регистрации, дата регистрации, №протокола, срок действия, сумма контракта, цена контракта

Отчет исполнения

№ОИ, сведения об исполнении, сведения о претензиях, сумма бюджетных ассигнований,реестра МЗ, фактическое расходование средств

Оборудование и транспорт

№ОТ, вид, описание, модель, год выпуска, количество единиц, принадлежность

Складские помещения

№СП, наименование объектов, характеристика, местонахождение, общая площадь, принадлежность

Квалификация и опыт работников

№КР, должность, образование, учебное заведение, год, факультет, специальность, стаж работы по специальности, стаж работы в данной должности, основа

Рабочий персонал

№Р, специальность, разряд, численность, основа, общий стаж работы

Финансовое предложение

№ФП, цена, предлагаемый срок исполнения

Форма оплаты

№ФО, предоплата, по факту, по факту с банковской гарантией

2) Определение связей между сущностями и их характеристик

Все связи между сущностями и их характеристики отражены в таблице 3.2.

Таблица 3.2 - Связи между сущностями

Связь

Характеристика

Заказчик – Позиция заказа

Каждая позиция заказа относится к одному заказчику. У одного заказчика должно быть несколько позиций заказа.

Продолжение таблицы 3.2

Муниципальный заказ – Позиция заказа

Каждая позиция заказа относится к одному заказу. У одного заказа должно быть несколько позиций заказа.

Позиция заказа – Позиция-Товар

Сущность «Позиция-Товар» является подтипом сущности «Позиция заказа»

Позиция заказа – Позиция- Оборудование

Сущность «Позиция-Оборудование» является подтипом сущности «Позиция заказа»

Поставщик – Заявка

Каждая заявка подается одним поставщиком. Каждый поставщик подает одну или несколько заявок.

Заявка – Финансовое предложение

Каждое финансовое предложение относится к одной заявке. К каждой заявке относится несколько финансовых предложений.

Позиция заказа – Финансовое предложение

Каждому финансовому предложению соответствует одна позиция заказа. Каждой позиции заказа соответствует несколько финансовых предложений.

Заявка – Юридическое лицо

Сущность «Юридическое лицо» является подтипом сущности «Поставщик»

Заявка – Физическое лицо

Сущность «Физическое лицо» является подтипом сущности «Поставщик»

Заявка – Оборудование и транспорт

Каждый вид оборудования и транспорта содержится в одной из заявок. Каждая заявка содержит данные о нескольких видах оборудования и транспорта.

Заявка – Складские помещения

Каждое складское помещение содержится в одной из заявок. Каждая заявка содержит данные о нескольких складских помещениях.

Заявка – Квалификация и опыт работников

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

Заявка – Рабочий персонал

Каждому рабочему соответствует одна заявка. Каждой заявке соответствуют данные о нескольких рабочих.

Муниципальный заказ – Заявка

К каждой заявке относится один заказ. К каждому заказу относится несколько заявок.

Заказчик – Заявка

К каждой заявке относится один заказчик. К каждому заказчику относится несколько заявок.

Заказчик – Контракт

Каждый контракт соответствует одному заказчику. Каждому заказчику соответствует один или несколько контрактов.

Продолжение таблицы 3.2

Муниципальный заказ – Контракт

Каждый контракт соответствует одному заказу.

Каждому заказу соответствует несколько контрактов.

Поставщик – Контракт

Каждый контракт соответствует одному поставщику. Каждому поставщику соответствует один или несколько контрактов.

Контракт – Исполнение

Каждое сведение об исполнении соответствует одному контракту. Одному контракту соответствует несколько сведений об исполнении.

Форма оплаты – Заявка

К каждой заявке относится одна форма оплаты. К каждой форме оплаты относится несколько заявок.

Диаграмма ER-типа для проектируемой БД приведена на рис.1.

3.2.2 Генерация  набора предварительных отношений

Заказчик (№З, наименование_З, адрес, ФИО_ответственного, телефон)

Заказ (№МЗ, наименование_МЗ, дата_МЗ, срок_исполнения)

Позиция (№ПЗ, наименование_ПЗ, количество, №З, №МЗ)

Товар (№ПТ, ед._измерения, параметр, характеристика, стандарт)

Оборудование (№ПО, марка, производство, гарантия, год_выпуска)

Заявка (№Заяв, дата_заявки, копия_4, дата_регистрации_нал, №регегистрации_нал, наименование_нал_органа, проверка,  №З, №МЗ, №ФО)

Юридическое лицо (№Ю, копия_2, юридический_адрес)

Физическое лицо (№Ф, копия_1, паспорт)

Поставщик (№П, тип, наименование_П, ФИО_руководителя, №тел_руководителя, ФИО_заместителя, №тел_заместителя, ФИО_глав_бух, №тел_бухгалтера, регион, ИНН, КПП, факс, e-mail, адрес, уставний_капитал, количество_акций, расч_счет, вал_счет, состояние_имущества)

 

Контракт (№К, №регистрации, дата_регистрации, №протокола, срок_действия, сумма, цена, №П, №З, №МЗ)

Отчет (№ОИ, исполнение, претензии, сумма_бюджета, №Реестра_МЗ, расход, №К)

Транспорт (№ОТ, вид, описание, модель, год_выпуска, количество_единиц, принадлежность_Т, №Заяв)

Склад (№СП, наименование_объекта, характеристика, местонахождение, площадь, принадлежность_С, №Заяв)

Квалификация (№КР, должность, образование, учебное_заведение, факультет, специальность_год, стаж_по_специальности, стаж_должности, основа, №Заяв)

Рабочий персонал (№Р, специальность, разряд, численность, основа,

общий_стаж, №Заяв)

Предложение (№ФП, цена, срок, №Заяв, №ПЗ)

Оплата (№ФО, предоплата, по_факту, по_факту_банк)

3.2.3 Исследование набора отношений на избыточность

Проверим, находятся ли отношения базы данных в НФБК, для чего определим детерминанты и возможные ключи отношений. Детерминанты и возможные ключи отношений представлены в таблице 3.

Таблица 3.3 - Детерминанты и возможные ключи отношений

Отношение

Детерминант

Возможный ключ

Заказчик

<№З>

<№З>

< наименование_З >

< наименование_З >

Заказ

<№МЗ>

<№МЗ>

< дата_МЗ >

< дата_МЗ >

Позиция

<№ПЗ>

<№ПЗ>

Товар

<№ПТ>

<№ПТ>

Оборудование

<№ПО>

<№ПО>

Заявка

<№Заяв >

<№Заяв >

< №регегистрации_нал >

< №регегистрации_нал >

Юридическое лицо

<№Ю>

<№Ю>

Физическое лицо

<№Ф>

<№Ф>

< паспорт >

< паспорт >

Поставщик

<№П>

<№П>

< наименование_П, ФИО_руководителя, №тел_руководителя >

< наименование_П, ФИО_руководителя, №тел_руководителя >

Продолжение таблицы 3.3

< ИНН, КПП>

< ИНН, КПП>

Контракт

<№К>

<№К>

< №регистрации, дата_регистрации, №протокола >

Отчет

<№ОИ>

<№ОИ>

Транспорт

<№ОТ>

<№ОТ>

Склад

<№СП>

<№СП>

Квалификация

<№КР>

<№КР>

Рабочий персонал

<№Р>

<№Р>

Предложение

<№ФП>

<№ФП>

Оплата

<№ФО>

<№ФО>

Все отношения находятся в НФБК, т.к. имеющиеся детерминанты являются возможными ключами. Набор предварительных отношений не является избыточным, т.к. не существует отношения, множество атрибутов которого являлось бы подмножеством атрибутов другого отношения данного набора, и ни одно отношение не может быть получено из других отношений набора серией JOIN операций. Следовательно, предварительный набор отношений является окончательным.

Рисунок 1 – Диаграмма ER - типа

3.3 Проектирование базы данных с использованием CASE-средства ERwin

ERwin – case-средство для проектирования реляционных БД. ERwin сочетает графический интерфейс Windows, инструменты для построения ER-диаграмм, редакторы для создания логического и физического описания модели данных и прозрачную поддержку ведущих реляционных СУБД. Erwin обеспечивает прямое подключение к БД: создание структуры БД непосредственно из ERwin; описание процедур и триггеров при построении модели, их автоматическое создание в БД при генерации; имеет хранимые наборы параметров отображения для построения отчетов и диаграмм.

Проектирование модели в ERwin наглядно представляет ограничения ссылочной целостности. В то же время для выбранной целевой СУБД ERwin автоматически генерирует нужные элементы ссылочной целостности - внешние и альтернативные ключи, триггеры, ограничения.

 

3.3.1 Создание логической и физической модели данных 

В ERwin существуют два уровня представления и моделирования - логический и физический. На логическом уровне не рассматривается использование конкретной СУБД, не определяются типы данных и не определяются индексы для таблиц.

Целевая СУБД, имена объектов и типы данных, индексы составляют второй (физический) уровень модели ERwin.

ERwin предоставляет возможности создавать и управлять этими двумя различными уровнями представления одной диаграммы (модели).

    Для логического представления структуры проектируемой базы данных в нотации IDEF1X, использующейся в ERwin,  необходимо учитывать следующие соответствия для связей с нотацией Хау диаграммы ER-типа, которые указаны в таблице 3.4.

Таблица 3.4 - Соответствие связей между сущностями нотации IDEF1X и Хау

Нотация Хау

Нотация IDEF1X

Тип связи

Класс принадлежности 1й сущности

Класс принадлежности 2й сущности

Тип связи

Мощность

1: 1

необязательный

обязательный

категоризации

1 to 0,1

1: n

необязательный

обязательный

неидентифицирующая

1 to 1,n

Логическая и физическая модели данных представлены на рисунках 2 и 3, соответственно.

Рисунок 2 - Логическая модель данных в нотации IDEF1X

Рисунок 3 - Физическая модель данных для сервера InterBase

3.3.2 Оптимизация быстродействия при работе с базой данных

Скорость доступа к данным определяется объемом просматриваемой в процессе выборки информации. При отсутствии условий для выборки (фильтрации) и упорядочения просмотр осуществляется всегда в физическом порядке, обеспечивая максимальную скорость поиска. В случае наличия некоторых условий, скорость поиска увеличивается за счет упорядочения данных. В InterBase упорядочения данных достигается путем задания индексов по интересующему полю или группе полей. Хотя обязательное обновление индексов значительно увеличивает затраты времени при обновлении данных, обеспечение быстрого поиска для разрабатываемой системы куда важнее, поскольку в базе не предусматриваются частые обновления. Индекс представляет собой список значений индексных полей, в котором каждому набору значений сопоставляется указатель на соответствующие строки таблиц, сами индексы организуются в виде бинарных деревьев или списков. Использование индекса требует меньшего количества обращений к диску, чем последовательное чтение строк в таблице.

Другим возможным способом улучшения быстродействия является введение избыточности в БД или её денормализация,  результатом является увеличение времени на выполнение запросов, связанных с обновлением данных, но при этом сокращается время их выборки.

Планирование индексов

Для каждого первичного и внешнего ключа ERwin создает при генерации структуры БД уникальный индекс.

Для альтернативного ключа, как и для первичного, ERwin автоматически создает индексы при генерации БД. Атрибуты, составляющие альтернативный ключ, однозначно (уникально) идентифицируют экземпляры сущности. В ERwin можно также составлять группы атрибутов, которые не идентифицируют уникально экземпляры сущности, но часто используются для доступа к данным. Для каждой такой группы атрибутов ERwin создает неуникальные индексы.

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

CREATE UNIQUE INDEX XAK1Statement ON Statement (RegNalID);

CREATE UNIQUE INDEX XAK1Physical ON Physical (pasport);

CREATE UNIQUE INDEX XAK1Contract ON Contract (RegID, RedDate, ProtocolID);

CREATE UNIQUE INDEX XAK1Supplyer ON Supplyer (INN, KPP);

CREATE INDEX XIE1Supplyer ON Supplyer (SupName, LeaderName, LeaderPhone);

CREATE INDEX XIE1Customer ON Customer (CustName);

CREATE INDEX XIE1Orders ON Orders (OrDate);

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

Введение избыточности

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

 

3.3.3 Поддержка бизнес-логики

3.3.3.1 Определение пользователей, ролей и прав доступа

Поддержание целостности данных предполагает их защиту от случайного и намеренного искажения. Кроме того, доступ к отдельным данным должен быть ограничен  по соображениям их конфиденциальности. Все это требует ограничения прав доступа к данным различных групп пользователей. Для защиты данных от несанкционированного доступа целесообразно формирование списка пользователей и предоставление прав на доступ к определенным данным и  процедурам. У проектируемой системы предполагается наличие двух типов пользователей с различными правами: оператор, регулярно работающий с базой, и секретарь, работающий с базой во время проведения конкурса. Для регулирования доступа групп пользователей различных типов  в  InterBase предусмотрен механизм «ролей». Отдельные группы работают с одними и теми же данными,  средствами, и как следствие имеют одни и те же права, а имя пользователя идентифицирует вносимые им данные. Ниже перечислены этапы определения пользователей, ролей и прав доступа.

  1.  Создание ролей:

 CREATE ROLE secretary;

 CREATE ROLE operator;

  1.  Формирование списка прав, связанных с ролями:

а) для доступа к таблицам -

GRANT SELECT, INSERT, REFERENCES ON Orders, OrderPosition, FinancialOffer, Statement, FormPayment TO secretary;

 

GRANT SELECT, INSERT, UPDATE, REFERENCES ON Orders, OrderPosition, PosMachinery, PosWares, Account, Contract, Customer, Supplyer, Juridical, Physical, Statement, Qualification,Transport, Warehouse, Worker TO operator TO PUBLIC;

б) для вызова процедуры -

GRANT EXECUTE ON PROCEDURE win TO secretary;

GRANT EXECUTE ON PROCEDURE getN TO operator TO PUBLIC;

GRANT EXECUTE ON PROCEDURE getY TO operator TO PUBLIC;

  1.  Формирование прав пользователей на основе ролей – передача прав на объекты БД, объявленных для ролей:

GRANT secretary TO username1;

GRANT operator TO username2, username2;

  1.  Связывание пользователей с ролями – связь между ролью и пользователем осуществляется при соединении пользователя с базой:

CONNECT USER ‘username1’ PASSWORD ‘CONFID’ ROLE ‘secretary’;

3.3.3.2 Создание триггеров

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

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

CREATE TRIGGER tD_FormPayment FOR FormPayment AFTER DELETE AS

DECLARE VARIABLE numrows INTEGER;

BEGIN

   delete from Statement

     where

       Statement.PID = OLD.PID;

END !!

CREATE TRIGGER tD_Statement FOR Statement AFTER DELETE AS

DECLARE VARIABLE numrows INTEGER;

BEGIN

   delete from FinancialOffer

     where

              FinancialOffer.StID = OLD.StID;

   delete from Warehouse

     where

             Warehouse.StID = OLD.StID;

   delete from Qualification

     where

              Qualification.StID = OLD.StID;

   delete from Transport

     where

           Transport.StID = OLD.StID;

   delete from Worker

     where

              Worker.StID = OLD.StID;

   delete from Physical

     where

           Physical.PhysID = OLD.StID;

   delete from Juridical

     where

           Juridical.JurID = OLD.StID;

   delete from FormPayment

     where

           OLD.PID = FormPayment.PID;

END !!

 

3.3.3.3 Создание хранимых процедур

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

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

Для выполнения функции «расчет  и оформление показателей эффективности системы муниципальных закупок» подсистемы контроля исполнения муниципального заказа, обеспечивающей обработку и анализ результатов проведенного конкурса, целесообразно создание хранимых процедур, возвращающих значения показателей эффективности системы муниципальных закупок N1 и Урк, где N1 – среднее количество поставщиков, участвующих в одном конкурсе; Урк – удельный вес расторгнутых контрактов в общем числе заключенных контрактов.

SET TERM !

CREATE PROCEDURE GetN (paramd INTEGER)

RETURNS (paramn INTEGER)

AS

BEGIN

FOR SELECT AVG(SupID) FROM Statement

WHERE (StDate=:paramd)

INTO :paramn;

DO

SUSPEND;

END !

CREATE PROCEDURE GetY (paramr VARCHAR(20))

RETURNS (paramrc INTEGER)

AS

BEGIN

FOR SELECT SUM(AcID) FROM Account

WHERE (execution=:paramr)

INTO :paramrc;

DO

SUSPEND;

END !

3.3.3.4 Создание представлений (обзоров)

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

  •  упрощенный доступ к данным,
  •  настраиваемый доступ к данным,
  •  независимость приложений от организации хранения данных,
  •  обеспечение защиты данных (ограничение доступа к отдельным элементам данных).

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

CREATE VIEW Wares AS

      SELECT OrderPosition.PosName, OrderPosition.quantity, PosWares.measure, PosWares.parametr, PosWares.characteristic, PosWares.standart

      FROM OrderPosition, PosWares

      WHERE OrderPosition.PosID=PosWares.WarID;

заказы на поставку (тех. обслуживание) оборудования:

CREATE VIEW Machinery AS

      SELECT OrderPosition.PosName, OrderPosition.quantity, PosMachinery.TradeMark, PosMachinery.production, PosMachinery.guarantee, PosMachinery.OutputYear

      FROM OrderPosition, PosMachinery

      WHERE OrderPosition.PosID=PosMachinery.MachID;

Для реализации функции «формирование уведомлений заказчику» подсистемы контроля исполнения муниципального заказа, требуются далеко не все данные, хранящиеся в таблице поставщиков, для чего создан следующий обзор:

CREATE VIEW CustInformation AS

      SELECT DISTINCT Supplyer.SupName, Supplyer.LeaderName, Supplyer.LeaderPhone, Supplyer.DeputyName, Supplyer.DepPhone, Supplyer.fax, Supplyer.address, Supplyer.SupID

      FROM Supplyer;

3.4 Генерация SQL-скрипта для создания базы данных

Готовый SQL-скрипт содержит:

  •  17 таблиц;
  •  17 генераторов суррогатных ключей;
  •  17 триггеров для генерирования значений ключа;
  •  1 триггер на добавление;
  •  5 триггеров на обновление;
  •  2 триггера на удаление;
  •  2 хранимые процедуры;
  •  3 обзора.

Сгенерированный с использованием case-средства ERwin SQL-скрипт приведен в приложении Б.

4 Тестирование базы данных

С целью тестирования спроектированной БД было произведено исследование информационной модели с помощью CASE-средства ERwin Examiner. Для проверки был использован SQL-скрипт (Приложение Б)

Результат проверки приведен в следующей таблице (рисунок 4), сгенерированной программой ERwin Examiner.

Рисунок 4 - Отчет о диагностике информационной модели,

построенный программой ERwin Examiner.

В результате проверки информационной модели в ERwin Examiner были выявлены ошибки индексации. 16 ошибок, обнаруженных программой ERwin Examiner, найдены при проверке использования индексов и ограничений (Indexes and Constraints) и относятся к категории «Пропущенные индексы» (Missing Index). Ошибки типа: «отсутствие индекса для первичного ключа независимой таблицы в зависимой таблице». Однако все перечисленные индексы указаны в SQL-скрипт. Возможно, обнаружение этих ошибок связано с тем, что используемая версия программы ERwin Examiner не поддерживает SQL-диалект СУБД InterBase, для которой был построен данный SQL-скрипт.

Заключение

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

В процессе анализа предметной области были выявлены данные, которые должны храниться в базе данных, определена бизнес-логика.

В разделе «Постановка задачи» были выявлены определено функциональное назначение разрабатываемого ПО, определены виды и форматы выходных и входных данных.

На этапе проектирования информационного обеспечения были выделены основные сущности предметной области, определены атрибуты этих сущностей, а также связи между сущностями. Была составлена диаграмма типа «сущность – связь». На основании этой диаграммы сгенерированны предварительные отношения. Отношения были проверены на соответствие нормальной форме Бойса – Кодда. Далее, на основании предварительных отношений, были спроектированы логическая и физическая модели данных системы с помощью CASE – средства ErWin.

В качестве СУБД выбрана InterBase 6.5.

 

Данная информационная система полностью готова для создания клиентского приложения, так как в ней заложена большая часть бизнес-логики.  

   

Список использованных источников

  1.  Дейт, К. Дж. Введение в системы баз данных, восьмое издание. Вильямс, М. – 2005г.
  2.  Скляр А.Я. Введение в InterBase. М: Горячая линия – Телеком, 2002. – 517 с.
  3.  Калянов Г.Н. CASE-технологии. Консалтинг  при автоматизации бизнес-процессов. Третье издание, М: Горячая линия – Телеком, 2002г.
  4.  Кириллов В.В. Структурированный язык запросов (SQL). СПб.: ИТМО, 1994. – 80 с.
  5.  Томас Коннолли, Каролин Бегг, Анна Страчан. Базы данных. Проектирование, реализация и сопровождение. Теория и практика, Второе издание, исправленное и дополненное, Вильямс, М. – 2000г.
  6.  Когловский М.Р.,  "Технология баз данных на персональных ЭВМ", Москва, 'Финансы и статистика', 1992 г.
  7.  Джен Харрингтон «Проектирование объектно-реляционных баз данных», М. ДМК издательство, 2000г.
  8.  Мейер М. Теория реляционных баз данных. – М.: Мир, 1987. – 608 с.

    

Приложение Б

(обязательное)

SQL-скрипт

CREATE TABLE FormPayment (

      PID                  INTEGER NOT NULL,

      PredPay              VARCHAR(20),

      fact                 INTEGER,

      FactBank             INTEGER,

      PRIMARY KEY (PID));

CREATE TABLE Customer (

      CustID               INTEGER NOT NULL,

      CustName             VARCHAR(20),

      CustAddress          VARCHAR(40),

      ResponsibleFIO       VARCHAR(30),

      RespPhone            VARCHAR(20),

      PRIMARY KEY (CustID));

CREATE TABLE Orders (

      OrID                 INTEGER NOT NULL,

      OrDate               DATE,

      OrName               VARCHAR(60),

      DemTerm              INTEGER,

      PRIMARY KEY (OrID));

CREATE TABLE Supplyer (

      SupID                INTEGER NOT NULL,

      SupName              VARCHAR(20) NOT NULL,

      types                VARCHAR(20),

      LeaderName           VARCHAR(20),

      LeaderPhone          VARCHAR(20),

      DeputyName           VARCHAR(20),

      DepPhone             VARCHAR(20),

      AccountentName       VARCHAR(20),

      AcPhone              VARCHAR(20),

      region               VARCHAR(20),

      INN                  INTEGER,

      KPP                  INTEGER,

      fax                  VARCHAR(20),

      capital              NUMERIC,

      SharedQuant          INTEGER,

      WorkerQuant          INTEGER,

      PropertyCond         VARCHAR(20),

      address              VARCHAR(40),

      PRIMARY KEY (SupID));

CREATE TABLE Statement (

      StID                 INTEGER NOT NULL,

      StDate               DATE,

      RegNalID             INTEGER,

      SupID                INTEGER NOT NULL,

      DataRegNal           DATE,

      FourCopy             SMALLINT,

      OrID                 INTEGER NOT NULL,

      NalName              VARCHAR(20),

      CustID               INTEGER NOT NULL,

      verification         INTEGER,

      PID                  INTEGER NOT NULL,

      PRIMARY KEY (StID),

      FOREIGN KEY (PID)

                            REFERENCES FormPayment,

      FOREIGN KEY (CustID)

                            REFERENCES Customer,

      FOREIGN KEY (OrID)

                            REFERENCES Orders,

      FOREIGN KEY (SupID)

                            REFERENCES Supplyer);

CREATE TABLE Worker (

      WPID                 INTEGER NOT NULL,

      speciality           VARCHAR(20),

      category             SMALLINT,

      number               INTEGER,

      WBasis               VARCHAR(20),

      WLength              SMALLINT,

      StID                 INTEGER NOT NULL,

      PRIMARY KEY (WPID),

      FOREIGN KEY (StID)

                            REFERENCES Statement);

CREATE TABLE Qualification (

      QPID                 INTEGER NOT NULL,

      post                 VARCHAR(20),

      education            VARCHAR(20),

      university           VARCHAR(20),

      faculty              VARCHAR(20),

      YSpeciality          VARCHAR(20),

      PLength              SMALLINT,

      SLength              SMALLINT,

      QBasis               VARCHAR(20),

      StID                 INTEGER,

      PRIMARY KEY (QPID),

      FOREIGN KEY (StID)

                            REFERENCES Statement);

CREATE TABLE Contract (

      ContID               INTEGER NOT NULL,

      RegID                INTEGER,

      RedDate              DATE,

      ProtocolID           INTEGER,

      ContTerm             DATE,

      Summa                NUMERIC,

      Price                NUMERIC,

      CustID               INTEGER NOT NULL,

      OrID                 INTEGER NOT NULL,

      SupID                INTEGER NOT NULL,

      PRIMARY KEY (ContID),

      FOREIGN KEY (SupID)

                            REFERENCES Supplyer,

      FOREIGN KEY (OrID)

                            REFERENCES Orders,

      FOREIGN KEY (CustID)

                            REFERENCES Customer);

CREATE TABLE Account (

      AcID                 INTEGER NOT NULL,

      execution            VARCHAR(20),

      pretension           VARCHAR(20),

      BudgetSum            NUMERIC,

      RollOrID             INTEGER,

      expenditure          NUMERIC,

      ContID               INTEGER NOT NULL,

      PRIMARY KEY (AcID),

      FOREIGN KEY (ContID)

                            REFERENCES Contract);

CREATE TABLE OrderPosition (

      PosID                INTEGER NOT NULL,

      PosName              VARCHAR(20),

      quantity             INTEGER,

      CustID               INTEGER NOT NULL,

      OrID                 INTEGER NOT NULL,

      PRIMARY KEY (PosID),

      FOREIGN KEY (OrID)

                            REFERENCES Orders,

      FOREIGN KEY (CustID)

                            REFERENCES Customer);

CREATE TABLE FinancialOffer (

      OfID                 INTEGER NOT NULL,

      price                NUMERIC,

      OfTerm               INTEGER,

      PosID                INTEGER NOT NULL,

      form                 VARCHAR(20),

      StID                 INTEGER NOT NULL,

      PRIMARY KEY (OfID),

      FOREIGN KEY (StID)

                            REFERENCES Statement,

      FOREIGN KEY (PosID)

                            REFERENCES OrderPosition);

CREATE TABLE PosWares (

      measure              VARCHAR(20),

      parametr             DECIMAL(7,3),

      characteristic       VARCHAR(20),

      standart             VARCHAR(20),

      WarID                INTEGER NOT NULL,

      PRIMARY KEY (WarID),

      FOREIGN KEY (WarID)

                            REFERENCES OrderPosition);

CREATE TABLE PosMachinery (

      TradeMark            VARCHAR(20),

      production           VARCHAR(20),

      guarantee            SMALLINT,

      OutputYear           SMALLINT,

      MachID               INTEGER NOT NULL,

      PRIMARY KEY (MachID),

      FOREIGN KEY (MachID)

                            REFERENCES OrderPosition);

CREATE TABLE Juridical (

      JurID                INTEGER NOT NULL,

      JAddress             VARCHAR(20),

      TwoCopy              SMALLINT,

      PRIMARY KEY (JurID),

      FOREIGN KEY (JurID)

                            REFERENCES Statement);

CREATE TABLE Physical (

      PhysID               INTEGER NOT NULL,

      pasport              VARCHAR(20),

      OneCopy              SMALLINT,

      PRIMARY KEY (PhysID),

      FOREIGN KEY (PhysID)

                            REFERENCES Statement);

CREATE TABLE Warehouse (

      WHID                 INTEGER NOT NULL,

      WName                VARCHAR(20),

      WCharacteristic      VARCHAR(20),

      WAddress             VARCHAR(20),

      area                 FLOAT,

      WBelong              VARCHAR(20),

      StID                 INTEGER,

      PRIMARY KEY (WHID),

      FOREIGN KEY (StID)

                            REFERENCES Statement);

CREATE TABLE Transport (

      TID                  INTEGER NOT NULL,

      variety              VARCHAR(20),

      description          VARCHAR(20),

      model                VARCHAR(20),

      TOutput              SMALLINT,

      TQuant               INTEGER,

      TBelong              VARCHAR(20),

      StID                 INTEGER,

      PRIMARY KEY (TID),

      FOREIGN KEY (StID)

                            REFERENCES Statement);

CREATE UNIQUE INDEX XPKFormPayment ON FormPayment (PID);

CREATE UNIQUE INDEX XPKCustomer ON Customer (CustID);

CREATE UNIQUE INDEX XPKOrders ON Orders (OrID);

CREATE UNIQUE INDEX XPKSupplyer ON Supplyer (SupID);

CREATE UNIQUE INDEX XPKStatement ON Statement (StID);

CREATE INDEX XIF1Statement ON Statement (SupID);

CREATE INDEX XIF2Statement ON Statement (OrID);

CREATE INDEX XIF3Statement ON Statement (CustID);

CREATE INDEX XIF4Statement ON Statement (PID);

CREATE UNIQUE INDEX XPKWorker ON Worker (WPID);

CREATE INDEX XIF1Worker ON Worker (StID);

CREATE UNIQUE INDEX XPKQualification ON Qualification (QPID);

CREATE INDEX XIF1Qualification ON Qualification (StID);

CREATE UNIQUE INDEX XPKContract ON Contract (ContID);

CREATE INDEX XIF1Contract ON Contract (CustID);

CREATE INDEX XIF2Contract ON Contract (OrID);

CREATE INDEX XIF3Contract ON Contract (SupID);

CREATE UNIQUE INDEX XPKAccount ON Account (AcID);

CREATE INDEX XIF1Account ON Account (ContID);

CREATE UNIQUE INDEX XPKOrderPosition ON OrderPosition (PosID);

CREATE INDEX XIF1OrderPosition ON OrderPosition (CustID);

CREATE INDEX XIF2OrderPosition ON OrderPosition (OrID);

CREATE UNIQUE INDEX XPKFinancialOffer ON FinancialOffer (OfID);

CREATE INDEX XIF1FinancialOffer ON FinancialOffer (PosID);

CREATE INDEX XIF3FinancialOffer ON FinancialOffer (StID);

CREATE UNIQUE INDEX XPKPosWares ON PosWares (WarID);

CREATE UNIQUE INDEX XPKPosMachinery ON PosMachinery (MachID);

CREATE UNIQUE INDEX XPKJuridical ON Juridical ( JurID);

CREATE UNIQUE INDEX XPKPhysical ON Physical ( PhysID);

CREATE UNIQUE INDEX XPKWarehouse ON Warehouse (WHID);

CREATE INDEX XIF1Warehouse ON Warehouse (StID);

CREATE UNIQUE INDEX XPKTransport ON Transport (TID);

CREATE INDEX XIF1Transport ON Transport (StID);

CREATE VIEW Machinery AS

      SELECT OrderPosition.PosName, OrderPosition.quantity, PosMachinery.TradeMark, PosMachinery.production, PosMachinery.guarantee, PosMachinery.OutputYear

      FROM OrderPosition, PosMachinery

      WHERE OrderPosition.PosID=PosMachinery.MachID;

CREATE VIEW Wares AS

      SELECT OrderPosition.PosName, OrderPosition.quantity, PosWares.measure, PosWares.parametr, PosWares.characteristic, PosWares.standart

      FROM OrderPosition, PosWares

      WHERE OrderPosition.PosID=PosWares.WarID;

CREATE VIEW CustInformation AS

      SELECT DISTINCT Supplyer.SupName, Supplyer.LeaderName, Supplyer.LeaderPhone, Supplyer.DeputyName, Supplyer.DepPhone, Supplyer.fax, Supplyer.address, Supplyer.SupID

      FROM Supplyer ;

CREATE EXCEPTION ERWIN_PARENT_DELETE_RESTRICT "Cannot DELETE !!!";

CREATE EXCEPTION ERWIN_CHILD_DELETE_RESTRICT "Cannot DELETE !!!";

SET TERM !!

CREATE TRIGGER tD_FormPayment FOR FormPayment AFTER DELETE AS

DECLARE VARIABLE numrows INTEGER;

BEGIN

   delete from Statement

     where

       Statement.PID = OLD.PID;

END !!

CREATE TRIGGER tD_Statement FOR Statement AFTER DELETE AS

DECLARE VARIABLE numrows INTEGER;

BEGIN

   delete from FinancialOffer

     where

              FinancialOffer.StID = OLD.StID;

   delete from Warehouse

     where

              Warehouse.StID = OLD.StID;

   delete from Qualification

     where

              Qualification.StID = OLD.StID;

   delete from Transport

     where

               Transport.StID = OLD.StID;

   delete from Worker

     where

               Worker.StID = OLD.StID;

   delete from Physical

     where

               Physical.PhysID = OLD.StID;

   delete from Juridical

     where

              Juridical.JurID = OLD.StID;

   delete from FormPayment

     where

              OLD.PID = FormPayment.PID;

END !!

CREATE TRIGGER tU_Customer FOR Customer AFTER UPDATE AS

DECLARE VARIABLE numrows INTEGER;

BEGIN

 IF

   (OLD.CustID <> NEW.CustID) THEN

 BEGIN

   update Statement

     set

       

       Statement.CustID = NEW.CustID

     where

       

       Statement.CustID = OLD.CustID;

 END

 

 IF

      (OLD.CustID <> NEW.CustID) THEN

 BEGIN

   update OrderPosition

     set

       

       OrderPosition.CustID = NEW.CustID

     where

       

       OrderPosition.CustID = OLD.CustID;

 END

END !!

CREATE TRIGGER tU_Statement FOR Statement AFTER UPDATE AS

DECLARE VARIABLE numrows INTEGER;

BEGIN

 IF

      (OLD.StID <> NEW.StID) THEN

 BEGIN

   update FinancialOffer

     set

       

       FinancialOffer.StID = NEW.StID

     where

               FinancialOffer.StID = OLD.StID;

 END

 IF

       (OLD.StID <> NEW.StID) THEN

 BEGIN

   update Warehouse

     set

               Warehouse.StID = NEW.StID

     where

               Warehouse.StID = OLD.StID;

 END

 IF

       (OLD.StID <> NEW.StID) THEN

 BEGIN

   update Qualification

     set

              Qualification.StID = NEW.StID

     where

             Qualification.StID = OLD.StID;

 END

 IF

      (OLD.StID <> NEW.StID) THEN

 BEGIN

   update Transport

     set

       

       Transport.StID = NEW.StID

     where

               Transport.StID = OLD.StID;

 END

 IF

      (OLD.StID <> NEW.StID) THEN

 BEGIN

   update Worker

     set

      

       Worker.StID = NEW.StID

     where

               Worker.StID = OLD.StID;

 END

 IF

       (OLD.StID <> NEW.StID) THEN

 BEGIN

   update Physical

     set

       

       Physical.PhysID = NEW.StID

     where

               Physical.PhysID = OLD.StID;

 END

 IF

     (OLD.StID <> NEW.StID) THEN

 BEGIN

   update Juridical

     set

              Juridical.JurID = NEW.StID

     where

       

       Juridical.JurID = OLD.StID;

 END

END !!

CREATE TRIGGER tU_Contract FOR Contract AFTER UPDATE AS

DECLARE VARIABLE numrows INTEGER;

BEGIN

 IF

   (OLD.ContID <> NEW.ContID) THEN

 BEGIN

   update Account

     set

       

       Account.ContID = NEW.ContID

     where

               Account.ContID = OLD.ContID;

 END

   insert into Customer (CustID)

     select CustID

       from Contract

       where

                

         not exists (

           select * from Customer

             where

                              NEW.CustID = Customer.CustID

         );

END !!

CREATE TRIGGER tU_Juridical FOR Juridical AFTER UPDATE AS

DECLARE VARIABLE numrows INTEGER;

BEGIN

 select count(*)

   from Statement

   where

        NEW.JurID = Statement.StID into numrows;

 IF (    

   numrows = 0

 ) THEN

 BEGIN

   EXCEPTION ERWIN_CHILD_UPDATE_RESTRICT;

 END

END !!

CREATE TRIGGER tU_Physical FOR Physical AFTER UPDATE AS

DECLARE VARIABLE numrows INTEGER;

BEGIN

 select count(*)

   from Statement

   where

  

     NEW.PhysID = Statement.StID into numrows;

 IF (numrows = 0)

 THEN

 BEGIN

   EXCEPTION ERWIN_CHILD_UPDATE_RESTRICT;

 END

END !!


 

А также другие работы, которые могут Вас заинтересовать

40092. Частотное разделение каналов 135.63 KB
  2 Функциональная схема многоканальной системы с частотным разделением каналов В зарубежных источниках для обозначения принципа частотного разделения каналов ЧРК используется термин Frequency Division Multiply ccess FDM. В многоканальных системах передачи с частотным разделением каналов МСПЧРК по каналу передаётся только сигнал одной боковой полосы а несущая частота берётся от местного генератора. С целью уменьшения влияния соседних каналов уменьшения переходных помех обусловленного неидеальностью АЧХ фильтров между спектрами...
40093. Принцип временного разделения каналов 54.58 KB
  Принцип временного объединения каналов удобно пояснить с помощью синхронно вращающихся распределителей на передающей и приемной стороне рис. Основные этапы образования группового сигнала показаны на рис. Формируемые отсчеты сигналов на выходе первого импульсного модулятора рис.10в на выходе второго импульсного модулятора рис.
40094. Разделение сигналов по форме 13.93 KB
  Наиболее общим признаком является форма сигналов. Члены ряда линейно независимы и следовательно ни один из канальных сигналов cKtK1 не может быть образован линейной суммой всех других сигналов. В последние годы успешно развиваются цифровые методы разделения сигналов по их форме в частности в качестве переносчиков различных каналов используются дискретные ортогональные последовательности в виде функций Уолша Радемахера и другие.
40095. Ортогональное частотное мультиплексирование 32.57 KB
  Кроме того несущие в системе OFDM накладываются чтобы увеличить спектральную эффективность. Однако несущие в системе OFDM точно ортогональны к друг другу поэтому они накладываются без интерференции. В результате системы OFDM позволяют увеличить спектральную эффективность не вызывая интерференции в соседних каналах.
40096. Принципы построения модели открытой системы связи (ОSI) 30.44 KB
  1 ПП Например программа WEB формирует запрос на удаленный WEBсервер в виде сообщение стандартного формата. Сообщение состоит из заголовка и поля данных. Webсервер формирует сообщениеответ и направляет его на транспортный уровень. Наконец сообщение достигает нижнего физического уровня который собственно и передаёт его по линиям связи машинеадресату в виде последовательности битов.
40098. Волоконно-оптические системы передачи и перспективы их развития 31.86 KB
  Подавляющее большинство ВОСП использует одно ОВ для передачи излучения одной рабочей длины волны. При введении излучения с длиной волны 980 нм в легированный эрбием отрезок волокна фотоны меняют состояние и генерируется излучение с длиной волны 155 мкм. Это излучение взаимодействует с рабочим излучением на той же длине волны усиливая его. Высокомощный лазер с длиной волны 980 нм называется лазером накачки.
40100. Обеспечение стабильной работы ftp и http сервера 4.11 MB
  Спецификация защищаемого объекта В сети Internet имеется закрытый ftpсервер доступ к которому предоставляется через открытую http страницу в глобальной сети Интернет. ftpсервер предоставляет доступ к файлам различных музыкальных форматов. Сервер функционирует на базе операционной системы Windows Xp. Сервер территориально располагается в пределах одной комнаты имеет выход в глобальную сеть Интернет через сторонний сервер компании предоставляющей услуги доступа.