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

ВведениеТема данного проекта «Проектирование и разработка базы данных для учета договоров и контроля за их исполнением».Ведение договорного документооборота является одной из ключевых задач в работе любой организации, а такой процесс, как заключение договора, требует повышенного внимания. Стоит отметить, что этот процесс включает в себя каждый шаг работы с договором, от создания и утверждения до внесения корректировок и контроля соблюдения обязательств сторон.Полный цикл работы с договором — это сложный процесс, в который обычно вовлечены сразу несколько подразделений компании, что влечет за собой большие риски возникновения ошибок и весьма размытые сроки введения документа в силу.В ходе выполнения данной работы необходимо выполнить следующие задачи:подготовить характеристику и анализ предметной области;разработать инфологическую, даталогическую модели базы данных для хранения информации;рассмотреть возможности резервного копирования и сохранения информации для возможности восстановления данных в случае аварийного повреждения базы данных;разработка инструментария работы с данными;подготовка пользователей базы данных.Проектирование базы данныхКраткая характеристика предметной областиПроцедура подготовки и подписания договоров является достаточно трудоемким процессом. Для его выполнения на предприятиях используют регламент договорной работы (далее - регламент).Регламент нужен, чтобы построить договорную работу в организации, а также по возможности сбросить необходимость долгих переговоров и согласований (это самая непродуктивная и рутинная часть работы).Договорную работу нужно сформировать как бизнес-процесс: обеспечить ее эффективность, скорость принятия решений, взаимодействие всех заинтересованных лиц. В итоге должен получиться структурированный, продуманный результат – договор, который приносит экономические выгоды и страхует от рисков. Для этого нужен регламент договорной работы, который поможет: организовать бизнес-процессы договорной деятельности.оптимизировать сроки выполнения процедур составления и согласования договоров.улучшит качество подготовки договоров.снизит правовые риски.организует защиту интересов.повысит прозрачность деятельности и процессов принятия управленческих решений.Работу с договорами в организации можно вести двумя способами:Централизованный подход подразумевает наличие специального структурного подразделения или рабочей группы, которая занимается договорами. Задача исполнителя — доставить проект договора в этот отдел, а согласованием, учетом и хранением занимаются специалисты. Децентрализованный подход предполагает, что каждый исполнитель ведет свои договоры самостоятельно. Даже если в компании практикуется такой подход, хранить все оригинальные экземпляры договоров нужно в одном месте: у секретаря, юриста или в канцелярии.Чтобы договорная работа проходила без ошибок, нужно внедрить в компании единые правила подготовки и заключения договоров. Когда есть система — становится неважно, кто занимается договором, потому что сотрудники следуют четкой пошаговой инструкции. Следует разделить зоны ответственности структурных подразделений и сотрудников. Юристы, производственники, финансисты, руководители захотят добавить в договор пункты, важные для них. Координация действия всех сотрудников и руководства на каждом этапе движения договора является неотъемлемой частью работы над договором. Работу над договором всех заинтересованных подразделений должен контролировать один исполнитель. Желательно, чтобы это был коммуникабельный и уравновешенный человек.Работа над договором начинается с переговоров с клиентами, контрагентами. Из обсуждений становится ясно, когда пора готовиться к сделке и начинать работать с проектом документа. Сначала он отправляется на согласование — как с внутренними, так и с внешними клиентами (партнерами). Далее проходит этап подписания и, наконец, начинается этап исполнения. В процессе договор где-то должен храниться, но к нему нужно открыть доступ и проследить, что у всех заинтересованных сторон есть копии документа. Если стоит вопрос о завершении короткой сделки или длящихся отношений, нужно продумать процедуру продления, подписания дополнительных соглашений.В регламент договорной работы обязательно включают аспекты: Порядок подготовки проектов договоров.Процедуры проверки контрагентов.Согласование условий с заинтересованными сотрудниками.Согласование договоров с контрагентами.Порядок подписания договоров уполномоченными лицами.Организацию исполнения договоров.Контроль за исполнением договоров.Порядок претензионно-исковой работы.Хранение и архивирование.Инфологическая модель БДИнфологическая модель предметной области представляет собой описание структуры и динамики предметной области, характера информационных потребностей пользователей в терминах, понятных пользователю и не зависимых от реализации БД. Это описание выражается в терминах не отдельных объектов предметной области и связей между ними, а их типов, связанных с ними ограничений целостности и тех процессов, которые приводят к переходу предметной области из одного состояния в другое.Прежде всего требуется выделить и описать основные информационные объекты будущей системы. Что считать значимым информационным объектом системы зависит от того, какие цели автоматизации требуется решить и что лежит в основе информации, используемой в приложении.В ходе работы над инфологической моделью используются следующие определения и понятия:Сущность – любой различимый объект (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных. Сущностями могут быть люди, места, самолеты, рейсы, вкус, цвет и т.д. Необходимо различать такие понятия, как тип сущности и экземпляр сущности. Понятие тип сущности относится к набору однородных личностей, предметов, событий или идей, выступающих как целое. Экземпляр сущности относится к конкретной вещи в наборе. Например, типом сущности может быть ГОРОД, а экземпляром – Москва, Киев и т.д.Атрибут – поименованная характеристика сущности. Его наименование должно быть уникальным для конкретного типа сущности, но может быть одинаковым для различного типа сущностей (например, ЦВЕТ может быть определен для многих сущностей: СОБАКА, АВТОМОБИЛЬ, ДЫМ и т.д.). Атрибуты используются для определения того, какая информация должна быть собрана о сущности. Примерами атрибутов для сущности АВТОМОБИЛЬ являются ТИП, МАРКА, НОМЕРНОЙ ЗНАК, ЦВЕТ и т.д. Здесь также существует различие между типом и экземпляром. Тип атрибута ЦВЕТ имеет много экземпляров или значений: Красный, Синий, Банановый, Белая ночь и т.д., однако каждому экземпляру сущности присваивается только одно значение атрибута.Абсолютное различие между типами сущностей и атрибутами отсутствует. Атрибут является таковым только в связи с типом сущности. В другом контексте атрибут может выступать как самостоятельная сущность. Например, для автомобильного завода цвет – это только атрибут продукта производства, а для лакокрасочной фабрики цвет – тип сущности.Ключ – минимальный набор атрибутов, по значениям которых можно однозначно найти требуемый экземпляр сущности. Минимальность означает, что исключение из набора любого атрибута не позволяет идентифицировать сущность по оставшимся. Для сущности Расписание ключом является атрибут Номер_рейса или набор: Пункт_отправления, Время_вылета и Пункт_назначения (при условии, что из пункта в пункт вылетает в каждый момент времени один самолет).Связь – ассоциирование двух или более сущностей. Если бы назначением базы данных было только хранение отдельных, не связанных между собой данных, то ее структура могла бы быть очень простой. Однако одно из основных требований к организации базы данных – это обеспечение возможности отыскания одних сущностей по значениям других, для чего необходимо установить между ними определенные связи. А так как в реальных базах данных нередко содержатся сотни или даже тысячи сущностей, то теоретически между ними может быть установлено более миллиона связей. Наличие такого множества связей и определяет сложность инфологических моделей.Исследуя ранее описанные характеристики и процессы, идущие в исследуемой области, выделим основные информационные объекты системы.Так потребуется информация о сотрудниках, работающих на предприятии, занимаемых ими должностях, отделах и подразделениях, в которых они работают, также необходимо учесть оборудование, и компьютерную технику, используемую в организации. Для описания техники потребуется хранить информацию о ее параметрах, наименование производителя, модели устройства, серийного номера, даты ввода в эксплуатацию и даты завершения гарантии.На основе перечисленных и ранее сформированных в ходе научно-исследовательской работы функциональных требований к работе построим описание информационных сущностей будущей базы данных.На основе анализа предметной области, выделим основные информационные объекты, участвующие в процессе:контрагентдоговорвид договорадолжностьотдел/подразделениесотрудниксостояние договорасписок согласованиякарьераСущность контрагент описывает контрагентов, с которыми заключаются договора.Договор – служит для хранения информации о самих договорах.Вид договора описывает виды заключаемых с контрагентами договоров.Должности содержит перечень должностей сотрудников организации.Отдел/подразделение – перечень структурных подразделений организации, заключающей договора.Сотрудник – работающие или работавшие в организации люди.Состояние договора – перечень возможных состояний документа (на согласовании, в работе, в архиве, …).Список согласования – содержит перечень должностей, представители которых должны согласовать документ.Карьера – данная сущность описывает карьерное движение сотрудника внутри организации.Построим концептуальную схему базы данных, на которой отразим связи между сущностями в нотации Чэна ( REF _Ref93570875 \h Рисунок 1.1).Рисунок 1.1 – Нотация ЧэнаДополним построенную схему атрибутами ( REF _Ref93572226 \h Рисунок 1.2).Рисунок 1.2 – Полноатрибутная схема в нотации ЧэнаНа основе перечисленных и ранее сформированных в ходе научно-исследовательской работы функциональных требований к работе построим полное описание информационных сущностей будущей базы данных. Представив данные описания в виде таблиц.Сущность Контрагент ( REF _Ref50023561 \h Таблица 1.1).Таблица 1.1 – Сущность КонтрагентАтрибутКлючОбязательноеТип данныхКод контрагента++ЧислоНаименование+ТекстАдресТекстТелефонТекстСущность Вид договора ( REF _Ref93581294 \h Таблица 1.2).Таблица 1.2 – Сущность Вид договораАтрибутКлючОбязательноеТип данныхКод вида++ЧислоНаименование +ТекстСущность Договор ( REF _Ref93581300 \h Таблица 1.3).Таблица 1.3 – Сущность ДоговорАтрибутКлючОбязательноеТип данныхНомер договора++ЧислоДата заключения+ДатаСуммаЧислоСрок действияДатаКод контрагента+ЧислоКод вида+ЧислоКод состояния+ЧислоСущность Состояние договора ( REF _Ref93581304 \h Таблица 1.4).Таблица 1.4 – Сущность Состояние договораАтрибутКлючОбязательноеТип данныхКод состояния++ЧислоНаименование+ТекстСущность Отдел ( REF _Ref93581309 \h Таблица 1.5).Таблица 1.5 – Сущность ОтделАтрибутКлючОбязательноеТип данныхКод отдела++ЧислоНаименование+ТекстСущность Сотрудник ( REF _Ref93581313 \h Таблица 1.6).Таблица 1.6 – Сущность СотрудникАтрибутКлючОбязательноеТип данныхТаб. Номер++ЧислоФИО+ТекстКод отдела+ЧислоЛогин+ТекстПароль+ТекстСущность Должность ( REF _Ref93581318 \h Таблица 1.7).Таблица 1.7 – Сущность ДолжностьАтрибутКлючОбязательноеТип данныхКод должности++ЧислоНаименование+ТекстСущность Карьера ( REF _Ref93581322 \h Таблица 1.8).Таблица 1.8 – Сущность КарьераАтрибутКлючОбязательноеТип данныхКод карьеры++ЧислоТаб. Номер+ЧислоКод должности+ЧислоДата приема+ДатаДата увольненияДатаСущность Список согласования ( REF _Ref93581329 \h Таблица 1.9).Таблица 1.9 – Сущность Список согласованияАтрибутКлючОбязательноеТип данныхКод должности++ЧислоНомер договора+ЧислоСогласованоЛогическоеЗамечанияТекстНа основе построенных описаний сущностей и их атрибутов построим инфологическую схему базы данных, являющуюся прототипом таблиц для хранения данных в будущей базе данных проектируемой системы. Схема представлена на рисунке ( REF _Ref93582814 \h Рисунок 1.3).Рисунок 1.3 – Инфологическая схема базы данныхНа основе представленной инфологической модели становиться возможным перейти к даталогической разработке базы данных.Даталогическая модель БДПод даталогической понимается модель, отражающая логические взаимосвязи между элементами данных безотносительно их содержания и физической организации. При этом даталогическая модель разрабатывается с учетом конкретной реализации СУБД, также с учетом специфики конкретной предметной области на основе ее инфологической модели.В качестве базы данных для реализации рассмотрим SQL Server.Подготовим описание таблиц базы данных на основе описанных ранее сущностей.Таблица Contragent – Контрагент ( REF _Ref93588982 \h Таблица 1.10).Таблица 1.10 – Таблица Contragent – КонтрагентАтрибутПоле КлючNot nullТип данныхКод контрагентаId_caPK+IntegerНаименованиеcaTitle+Varchar(150)АдресcaAdresVarchar(250)ТелефонcaPhoneVarchar(20)Таблица ContractType – Вид договора ( REF _Ref93588991 \h Таблица 1.11).Таблица 1.11 – Таблица ContractType – Вид договораАтрибутПолеКлючNot nullТип данныхКод видаId_ctPK+IntegerНаименование ctTitle+Varchar(50)Таблица Contract – Договор ( REF _Ref93589000 \h Таблица 1.12).Таблица 1.12 – Таблица Contract – ДоговорАтрибутПолеКлючNot nullТип данныхНомер договораcontrNoPK+IntegerДата заключенияcontrDate+DateСуммаcontrSumMoneyСрок действияcontrSrokDateКод контрагентаId_caFK+IntegerКод видаId_ctFK+IntegerКод состоянияId_stFK+IntegerТаблица ContrState – Состояние договора ( REF _Ref93589012 \h Таблица 1.13).Таблица 1.13 – Таблица ContrState – Состояние договораАтрибутПолеКлючNot nullТип данныхКод состоянияId_stPK+IntegerНаименованиеstTitle+Varchar(50)Таблица Department – Отдел ( REF _Ref93589049 \h Таблица 1.14).Таблица 1.14 – Таблица Department – ОтделАтрибутПолеКлючNot nullТип данныхКод отделаId_dpPK+IntegerНаименованиеdpTitle+Varchar(50)Таблица Sotr – Сотрудник ( REF _Ref93589056 \h Таблица 1.15).Таблица 1.15 – Таблица Sotr – СотрудникАтрибутПолеКлючNot nullТип данныхТаб. НомерtabNoPK+IntegerФИОstFio+Varchar(70)Код отделаId_dpFK+IntegerЛогинstLogin+Varchar(100)ПарольstPass+Varchar(100)Таблица Post – Должность ( REF _Ref93589060 \h Таблица 1.16 REF _Ref93581318 \h ).Таблица 1.16 – Таблица Post – ДолжностьАтрибутПолеКлючNot nullТип данныхКод должностиId_dlPK+IntegerНаименованиеdlTitle+Varchar(50)Таблица Career – Карьера ( REF _Ref93589065 \h Таблица 1.17 REF _Ref93581322 \h ).Таблица 1.17 – Таблица Career – КарьераАтрибутПолеКлючNot nullТип данныхКод карьерыId_crPK+IntegerТаб. НомерtabNoFK+IntegerКод должностиId_dlFK+IntegerДата приемаcrBegin+DateДата увольненияcrEndDateТаблица ListSogl – Список согласования ( REF _Ref93589071 \h Таблица 1.18 REF _Ref93581329 \h ).Таблица 1.18 – Таблица ListSogl – Список согласованияАтрибутПолеКлючNot nullТип данныхКод должностиId_dlPK+IntegerНомер договораcontrNoPK+IntegerСогласованоisOkBitЗамечанияnotesTextНа основе разработанных структур приготовим физическую схему базы данных ( REF _Ref93587282 \h Рисунок 1.4).Рисунок 1.4 – Физическая схемаСтратегия резервного копирования и восстановленияРезервное копирование защищает данные от нескольких рисков, включая сбои оборудования, человеческие ошибки, кибератаки, повреждение данных и стихийные бедствия. Важно защитить данные от любых потенциальных проблем, чтобы организация не осталась в затруднительном положении, когда что-то случится. Надлежащая платформа резервного копирования данных позволит пользователю вернуться к последней известной хорошей точке во времени до возникновения проблемы.В качестве стратегии применим правило 3-2-1.Правило резервного копирования 3-2-1 — это надежная стратегия, которой следует придерживаться. Согласно этой концепции, организация имеет три копии данных, хранящихся как минимум на двух различных типах носителей, причем одна копия отправляется за пределы предприятия.Схематично данное правило представим в виде рисунка ( REF _Ref93587628 \h Рисунок 1.5).Рисунок 1.5 – Правило создания резервных копийСамой распространенной стратегией резервного копирования является резервное копирование всей базы данных через заранее заданные промежутки времени (например, каждую ночь). Благодаря такой стратегии аварийного восстановления можно восстановить базу данных до состояния на момент выполнения последнего резервного копирования. Эта стратегия реализуется посредством выполнения полных резервных копий базы данных, как рассказывается ниже.Полная резервная копия базы данных содержит все данные и метаинформацию базы данных, которые необходимы для восстановления базы данных полностью, включая полнотекстовые каталоги. При восстановлении базы данных из полной резервной копии восстанавливаются все файлы базы данных, причем данные извлекаются в непротиворечивом состоянии на тот момент времени, в который выполнялось резервное копирование. Пока выполняется резервное копирование, база данных работает в рабочем режиме, и пользователь может выполнять транзакции, изменяя данные обычным путем. Термин "непротиворечивое состояние" означает, что все транзакции, которые были зафиксированы в процессе выполнения резервного копирования базы данных, применяются, а все транзакции, которые не были завершены, подвергаются откату. Для ситуаций, которые могли бы привести к нарушению непротиворечивости данных вследствие выполнения транзакций, изменяющих данные в процессе выполнения резервного копирования, в SQL Server есть особый процесс, который позволяет гарантировать непротиворечивость данных. Этот процесс выполняет запись на устройство резервного копирования как страниц данных, так и журнала транзакций.Реализация базы данныхХарактеристика СУБД и других программных средствВ качестве хранилища информации используем базу данных Microsoft SQL Server – это высоконадежная, с большой эффективностью и интеллектуальностью платформа для управления данными, приспособленная к работе с самыми ответственными и требовательными бизнес-приложениями, помогающая сократить затраты на обслуживание существующих систем и разработку новых приложений, имеющая широкие возможности BI для всех сотрудников компании.Для работы с данными на серверах с операционной системой Windows наиболее удобно использовать также наиболее совместимый продукт.Для этих целей подойдет сервер баз данных MS SQL Server. Кроме SQL Server существует множество других как реляционных, так и не реляционных (NoSQL) баз данных, которые можно использовать для организации и управления данными проектируемой системы. Наиболее вероятными конкурентами в данной области будут являться системы Oracle Database и MySQL. Система Oracle изначально ориентирована на применение в Unix системах, несмотря на наличие версий для Windows, ее использование в сети с серверами Windows связано с определенными неудобствами и может приводить к достаточно неожиданным проблемам, так, например, Oracle Database не воспринимает использование кириллических имен в путях файловой системы или имени компьютера (по крайней мере это справедливо для версии 11g). Применение MySQL нашло нишу в области интернет технологий, за счет своей легкости в настройке и масштабируемости. Однако на протяжении многих версий в MySQL отсутствовали такие инструменты как транзакции, хранимые процедуры и триггеры, присущие более «серьезным» системам. То, что не являлось критичным в web-разработке, становилось препятствием для применения данной базы данных в проектировании сложных, нагруженных систем. Несмотря на свое развитие, MySQL остается инструментом интернета.Использование NOSQL баз данных оправдано в тех случаях, когда структура данных не регламентирована (или слабо типизирована, если проводить аналогии с языками программирования), т.е. в отдельной строке или документе можно добавить произвольное поле без предварительного декларативного изменения структуры всей таблицы. Касательно нашего приложения все данные, необходимые для функционирования системы имеют четкую регламентацию, в связи с чем рассмотрение возможности применения такого рода систем не требуется.Microsoft SQL Server – это высоконадежная, эффективная в использовании и интеллектуальная платформа для управления данными. Рассчитанная на работу в наиболее ответственных и требовательных бизнес-приложениях, способная сократить временные и финансовые затраты на обслуживание эксплуатируемых систем и разработку новых приложений.В SQL Server встроен большой набор интегрированных служб, позволяющий расширить возможности использования и управления данными: можно составлять запросы, осуществлять поиск, выполнять синхронизацию, готовить отчеты, анализировать данные. Все данные хранятся на основных серверах, входящих в состав центра обработки данных. Доступ к ним осуществляется как с настольных компьютеров, так и с мобильных устройств. Таким образом, с SQL Server возможен полный контроль данных в независимости от того, где они были сохранены.SQL Server предоставляет возможность динамического шифрования баз данных, файлов данных, а также файлов журналов без внесения изменений в имеющиеся приложения. Это также обеспечивает поиск по зашифрованным данным, как с использованием диапазонов, так и при помощи нечеткого поиска, а также поиск защищенных данных для пользователей, не прошедших авторизацию. SQL Server развивает шифрование и управление ключами при поддержке продуктов от сторонних компаний по управлению ключами и аппаратными модулями безопасности. В программе сделаны улучшения зеркального отображения баз данных, поддерживается автоматический механизм восстановления страниц данных.Кроме всего перечисленного SQL Server в сочетании с применением Visual Studio дают мощный инструмент по созданию приложений баз данных, так как имеют возможности взаимной интеграции, что немаловажно для удобства и скорости разработки приложений.Учитывая, что существует бесплатная версия SQL Server Express, функциональных возможностей которого вполне достаточно для небольших компаний и небольших команд разработчиков – использование данной версии, по крайней мере, на начальном этапе существенно сэкономит ресурсы предприятия по оплате лицензий на применяемое программное обеспечение.Создание таблицНа основе физической модели создадим табличные объекты в базе данных. Для реализации используем инструмент SQL Management Studio. SQL Management Studio – это условно-бесплатный инструментарии для управления базами данных среды SQL Server, обладающий все необходимым инструментарием для управления и создания базами данных данной среды.Построим табличные объекты базы данных.Проект таблицы Contragent ( REF _Ref93590708 \h Рисунок 2.1).Рисунок 2.1 – Таблица ContragentSQL скрипт для создания: -- Table ContragentCREATE TABLE [Contragent]( [id_ca] Int IDENTITY NOT NULL, [caTitle] Varchar(150) NOT NULL, [caAdres] Varchar(250) NULL, [caPhone] Varchar(20) NULL)go-- Add keys for table ContragentALTER TABLE [Contragent] ADD CONSTRAINT [PK_Contragent] PRIMARY KEY ([id_ca])goПроект таблицы Contract ( REF _Ref93590715 \h Рисунок 2.2).Рисунок 2.2 – Таблица ContractSQL скрипт для создания:-- Table ContractCREATE TABLE [Contract]( [contrNo] Int IDENTITY NOT NULL, [contrDate] Date NOT NULL, [contrSum] Money NULL, [contrSrok] Date NULL, [id_ca] Int NULL, [id_ct] Int NULL, [id_st] Int NULL)go-- Create indexes for table ContractCREATE INDEX [IX_Relationship3] ON [Contract] ([id_ca])goCREATE INDEX [IX_Relationship4] ON [Contract] ([id_ct])goCREATE INDEX [IX_Relationship5] ON [Contract] ([id_st])go-- Add keys for table ContractALTER TABLE [Contract] ADD CONSTRAINT [PK_Contract] PRIMARY KEY ([contrNo])goПроект таблицы ContractType ( REF _Ref93590722 \h Рисунок 2.3).Рисунок 2.3 – Таблица ContractTypeSQL скрипт для создания:-- Table ContractTypeCREATE TABLE [ContractType]( [id_ct] Int IDENTITY NOT NULL, [ctTitle] Varchar(50) NOT NULL)go-- Add keys for table ContractTypeALTER TABLE [ContractType] ADD CONSTRAINT [PK_ContractType] PRIMARY KEY ([id_ct])GoПроект таблицы Department ( REF _Ref93590728 \h Рисунок 2.4).Рисунок 2.4 – Таблица DepartmentSQL скрипт для создания:-- Table DepartmentCREATE TABLE [Department]( [id_dp] Int IDENTITY NOT NULL, [dpTitle] Varchar(150) NOT NULL)go-- Add keys for table DepartmentALTER TABLE [Department] ADD CONSTRAINT [PK_Department] PRIMARY KEY ([id_dp])goПроект таблицы Sotrud ( REF _Ref93590735 \h Рисунок 2.5).Рисунок 2.5 – Таблица SotrudSQL скрипт для создания:-- Table sotrudCREATE TABLE [sotrud]( [tabNo] Int IDENTITY NOT NULL, [stFio] Varchar(70) NOT NULL, [id_dp] Int NOT NULL, [stLogin] Varbinary(100) NOT NULL, [stPass] Varbinary(100) NOT NULL)go-- Create indexes for table sotrudCREATE INDEX [IX_Relationship1] ON [sotrud] ([id_dp])go-- Add keys for table sotrudALTER TABLE [sotrud] ADD CONSTRAINT [PK_sotrud] PRIMARY KEY ([tabNo])goПроект таблицы ContrState ( REF _Ref93590741 \h Рисунок 2.6).Рисунок 2.6 – Таблица ContrStateSQL скрипт для создания:-- Table ContrStateCREATE TABLE [ContrState]( [id_st] Int IDENTITY NOT NULL, [stTitle] Varchar(50) NOT NULL)go-- Add keys for table ContrStateALTER TABLE [ContrState] ADD CONSTRAINT [PK_ContrState] PRIMARY KEY ([id_st])goПроект таблицы Post ( REF _Ref93590748 \h Рисунок 2.7).Рисунок 2.7 – Таблица PostSQL скрипт для создания:-- Table PostCREATE TABLE [Post]( [id_dl] Int IDENTITY NOT NULL, [dlTitle] Varchar(50) NOT NULL)go-- Add keys for table PostALTER TABLE [Post] ADD CONSTRAINT [PK_Post] PRIMARY KEY ([id_dl])goПроект таблицы Career ( REF _Ref93590755 \h Рисунок 2.8).Рисунок 2.8 – Таблица CareerSQL скрипт для создания:-- Table careerCREATE TABLE [career]( [id_cr] Int IDENTITY NOT NULL, [tabNo] Int NOT NULL, [id_dl] Int NOT NULL, [crBegin] Date NOT NULL, [crEnd] Date NULL)go-- Create indexes for table careerCREATE INDEX [IX_Relationship12] ON [career] ([tabNo])goCREATE INDEX [IX_Relationship13] ON [career] ([id_dl])go-- Add keys for table careerALTER TABLE [career] ADD CONSTRAINT [PK_career] PRIMARY KEY ([id_cr])goПроект таблицы ListSogl ( REF _Ref93590762 \h Рисунок 2.9).Рисунок 2.9 – Таблица ListSoglSQL скрипт для создания:-- Table ListSoglCREATE TABLE [ListSogl]( [id_dl] Int NOT NULL, [contrNo] Int NOT NULL, [isOk] Bit NULL, [notes] Text NULL)go-- Add keys for table ListSoglALTER TABLE [ListSogl] ADD CONSTRAINT [PK_ListSogl] PRIMARY KEY ([id_dl],[contrNo])goСоздание представленийПредставления или Views представляют виртуальные таблицы. Но в отличии от обычных стандартных таблиц в базе данных представления содержат запросы, которые динамически извлекают используемые данные.Представление – это запрос, хранящийся в базе данных, который выглядит подобно таблице и не требует для своего хранения дисковой памяти. Для хранения представлений используется только оперативная память.Преимущества использования представлений:Безопасность. Позволяют скрыть структуру базы данных от некоторых пользователей. Права доступа к данным могут быть предоставлены исключительно через представление. Актуальность. Изменение данных в любой из таблиц, указанных в запросе, немедленно отображаются на содержимом представления. Снижение стоимости. Позволяют упростить структуру запросов за счёт объединения данных из нескольких таблиц в единственную виртуальную таблицу. Недостатки представления: Структура представления устанавливается в момент его создания. Если определён запрос Select * From, то * ссылается на все столбцы, существующие в представлении. Если в последствие в таблицу добавятся столбцы, то в представлении они не появятся. Снижение производительности. Представление, определённое с помощью сложного многотабличного запроса, требует значительных затрат времени на обработку. Построим представление для отображения списка работающих на текущий момент сотрудников, с отображением их должностей и подразделений.SQL дамп просмотра:SELECT sotrud.tabNo ,sotrud.stFio ,Department.dpTitle ,Post.dlTitleFROM dbo.sotrudINNER JOIN dbo.Department ON sotrud.id_dp = Department.id_dpINNER JOIN dbo.career ON career.tabNo = sotrud.tabNoINNER JOIN dbo.Post ON career.id_dl = Post.id_dlWHERE career.crEnd IS NULLПример выполнения запроса ( REF _Ref93594556 \h Рисунок 2.10).Рисунок 2.10 – Выполнение запроса представления данныхКроме представлений для отображения данных также можно использовать хранимые процедуры. При чем в хранимую процедуру можно передать изменяемые параметры, что позволит получать динамические списки данных. Так, подготовим хранимую процедуру, которая возвращает список сотрудников, работающих в определенном подразделении организации.SQL дамп процедуры:CREATE PROCEDURE dbo.selSotr @id_dp INTAS SELECT s.tabNo ,s.stFio ,d.dpTitle ,p.dlTitle ,c.crBegin FROM sotrud s LEFT JOIN Department d ON s.id_dp = d.id_dp LEFT JOIN career c ON s.tabNo = c.tabNo LEFT JOIN Post p ON c.id_dl = p.id_dl WHERE d.id_dp = @id_dpGOДля выполнения процедуры требуется передать данные ( REF _Ref93594751 \h Рисунок 2.11).Рисунок 2.11- Запрос данных для выполнения процедурыРезультат выполнения процедуры ( REF _Ref93594893 \h Рисунок 2.12).Рисунок 2.12 – Результат выполненияПроцедура отображения списка договоров, имеющих определенный статус и заключенных в определенный временной интервал.SQL дамп процедуры:CREATE PROCEDURE dbo.selContract @id_st INT, @dtBegin DATE, @dtEnd DATEAS SELECT c.contrNo ,c.contrDate ,c.contrSum ,c.contrSrok ,c.id_ca, ca.caTitle ,c.id_ct, ct.ctTitle ,c.id_st, cs.stTitle FROM Contract c LEFT JOIN ContractType ct ON c.id_ct = ct.id_ct LEFT JOIN Contragent ca ON c.id_ca = c1.id_ca LEFT JOIN ContrState cs ON c.id_st = cs.id_st WHERE c.id_st = @id_st AND c.contrDate BETWEEN @dtBegin AND @dtEndGOВыполним вызов процедуры при помощи SQL:USE [Contracts]GODECLARE@return_value intEXEC@return_value = [dbo].[selContract]@id_st = 1,@dtBegin = "01.01.2022",@dtEnd = "31.01.2022"SELECT'Return Value' = @return_valueGOРезультат выполнения процедуры ( REF _Ref93610600 \h Рисунок 2.13).Рисунок 2.13 – Выполнение процедуры отображения договоровТак же при помощи данных процедур организовывается управление данными, добавление записей в таблицы, удаление записей, проведение комплексных изменений в нескольких таблицах системы путем использования транзакций. При чем, при помощи хранимых процедур происходит отделение логики управления данными от клиентской части системы. А также это позволяет снизить нагрузку на сетевые ресурсы, что является достаточно существенным аргументом в сторону их использования в высоконагруженных системах.Описание триггеровСпециальный класс хранимых процедур – триггер – предназначен для автоматического запуска системой SQL Server при модифицировании какой-либо таблицы одним из трех операторов: UPDATE, INSERT или DELETE. Введение триггеров обусловлено желанием создать более безопасные и устойчивые базы данных.Добавим в базу данных триггеры, которые проверяют вносимые или изменяемые данные на соответствие требованиям функциональным и целостным правилам ограничений в таблицах базы данных.Так добавим проверку на правильность вносимой в договор суммы. Сумма договора не может иметь значения NULL, 0 или быть отрицательным.Также срок действия заключаемого договора не может быть меньше текущей даты.Для проверки соответствия вносимых данных в таблицу подготовим триггер, реагирующий на добавление и изменение информации в таблице Contract. Если в данных будет обнаружена ошибка – процедура отменит проводимую операцию.SQL дамп процедуры триггера:USE [Contracts]GO/****** Object: Trigger [dbo].[contrAU_trg] Script Date: 20.01.2022 23:07:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[contrAU_trg]ON [dbo].[Contract]AFTER INSERT, UPDATEAS DECLARE @sum MONEY; DECLARE @dat DATE; SELECT @sum = contrSum, @dat = contrSrok FROM INSERTED IF @sum IS NULL OR @sum <= 0 OR @dat < GETDATE() ROLLBACKДобавим проверку на соответствие дат в таблице карьера, где дата приема (начала исполнения обязанностей) сотрудника не может быть больше или равна даты увольнения. Так как увольнение не может предшествовать приему на работу.Построим SQL дамп триггерной процедуры:USE [Contracts]GO/****** Object: Trigger [dbo].[careerAU_trg] Script Date: 20.01.2022 23:18:33 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[careerAU_trg]ON [dbo].[career]AFTER INSERT, UPDATEAS DECLARE @prdate DATE DECLARE @uvdate DATE SELECT @prdate = crBegin, @uvdate = crEnd FROM INSERTED IF @prdate <= @uvdate BEGIN ROLLBACK END Для проверки работы триггеров следует внести данные соответствующие логике базы данных и заведомо неверные и после проверить состояние изменяемых таблиц. Неверные данные не должны попасть в базу данных.Для проверки выполним следующие команды:INSERT INTO career (tabNo, id_dl, crBegin, crEnd) VALUES (3, 1, '01.01.2019', '01.10.2019')GOINSERT INTO career (tabNo, id_dl, crBegin, crEnd) VALUES (3, 1, '01.01.2020', '01.10.2019')GOСостояние таблицы career до внесения изменений показано на рисунке ().Рисунок 2.14 – Таблиа career до измененияВыполним подготовленные скрипты и проверим состояние таблицы ().Рисунок 2.15 – Таблица после измененияКак можно убедиться, информация с неверными данными не была добавлена. Создание пользователей и назначение привилегийПосле проектирования логической структуры базы данных, связей между таблицами, ограничений целостности и других структур необходимо определить круг пользователей, которые будут иметь доступ к базе данных.В системе SQL-сервер организована двухуровневая настройка ограничения доступа к данным. На первом уровне необходимо создать так называемую учетную запись пользователя (login), что позволяет ему подключиться к самому серверу, но не дает автоматического доступа к базам данных. На втором уровне для каждой базы данных SQL-сервера на основании учетной записи необходимо создать запись пользователя. На основе прав, выданных пользователю как пользователю базы данных (user), его регистрационное имя (login) получает доступ к соответствующей базе данных. В разных базах данных login одного и того же пользователя может иметь одинаковые или разные имена user с разными правами доступа. Иначе говоря, с помощью учетной записи пользователя осуществляется подключение к SQL-серверу, после чего определяются его уровни доступа для каждой базы данных в отдельности.В системе SQL-сервер существуют дополнительные объекты – роли, которые определяют уровень доступа к объектам SQL-сервера. Они разделены на две группы: назначаемые для учетных записей пользователя сервера и используемые для ограничения доступа к объектам базы данных.На уровне сервера система безопасности оперирует следующими понятиями:аутентификация;учетная запись;встроенные роли сервера.На уровне базы данных применяются следующие понятия;пользователь базы данных;фиксированная роль базы данных;пользовательская роль базы данных.Каждый созданный в среде SQL объект имеет своего владельца, который изначально является единственной персоной, знающей о существовании данного объекта и имеет право выполнять с ним любые операции.Привилегиями, или правами, называются действия, которые пользователь имеет право выполнять в отношении данной таблицы базы данных или представления. В стандарте SQL определяется следующий набор привилегий:SELECT – право выбирать данные из таблицы;INSERT – право вставлять в таблицу новые строки;UPDATE – право изменять данные в таблице;DELETE – право удалять строки из таблицы;REFERENCES – право ссылаться на столбцы указанной таблицы в описаниях требований поддержки целостности данных;USAGE – право использовать домены, проверки и наборы символов.При подключении к SQL Server все возможные действия пользователей определяются правами (привилегиями, разрешениями), выданными их учетной записи, группе или роли, в которых они состоят.Права можно разделить на три категории:права на доступ к объектам;права на выполнение команд;неявные права.ЗаключениеВ ходе выполнения данной работы были выполнены следующие задачи:подготовлена характеристика и проведен анализ предметной области;разработаны инфологическая, даталогическая модели базы данных для хранения информации;рассмотрены возможности резервного копирования и сохранения информации для возможности восстановления данных в случае аварийного повреждения базы данных;разработаны методы доступа и контроля над данными.подготовка пользователей базы данных.Список использованных источниковВишневский Алексей/ Microsoft SQL Server. Эффективная работа. Питер. 2017.Гвоздева Т.В., Баллод Б.А. Проектирование информационных систем. - М.: Феникс, 2018. - 512 с.Карпова Т. С. Базы данных: модели, разработка, реализация: учебное пособие. Интернет-Университет Информационных Технологий. 2008.Коннолли Т., Базы данных: проектирование, реализация, сопровождение. Теория и практика., М.: Изд. дом «Вильямс», 2016.Петкович Д., Microsoft SQL Server 2016, С-т Петербург: БХВ-Петербург, 2017.Резервное копирование и восстановление баз данных SQL Server Режим доступа: http://technet.microsoft.com/ru-ru/library/ms187048.aspx#BnrStrategies, дата обращения: 09.10.2012Проектирование реляционных баз данных: Метод. указания к курсовому проектированию по курсу "Базы данных" / Московский государственный институт электроники и математики; Сост.: И.П. Карпова. – М., 2010. – 32 с.Методические указания по дипломному проектированию для специальности: «Прикладная информатика (по областям)» /под ред. Тельнова Ю.Ф., Сорокина А.А. - М.: МЭСИ. - 2004 г. – 103 с.