База данных по учету металлопродукции на платформе SQL Server

Курсовая работа

по дисциплине

«Корпоративные информационные системы»

на тему:

«База данных по учету металлопродукции на платформе SQL Server »

Содержание

Введение

1. Анализ предметной области

2. Описание модели сущность - связь

3. Разработка информационной системы

3.1 Требования к информационной системе

3.2 Выбор языка программирования

3.3Физическое описание базы данных

3.4 Выбор типа базы данных

3.5 Описание таблиц базы данных

4. Техническое проектирование

4.1 Описание структуры данных

4.1.1 Таблицы

4.1.2 Индексы

4.1.3 Ограничения

4.1.4 Значения по умолчанию

4.2 Описание объектов базы данных

4.2.1 Представления

4.2.2 Хранимые процедуры

4.2.3 Триггеры

4.3 Описание типов блокировок

5. Программная реализация

5.1 Описание основных хранимых процедур

5.2 Описание представлений

5.3 Описание программы

Список используемой литературы

Введение

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

Такой выбор был сделан не случайно. На принятие данного решения повлияли многие факторы.

Мы считаем, что данная тема весьма актуальна в наши дни, и не утратит своей востребованности и в дальнейшем.

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

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

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

1. Анализ предметной области

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

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

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

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

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

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

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

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

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

2. Описание модели сущность - связь

Данный раздел, в котором описывается модель сущность - связь, то есть дается семантическое описание предметной области, мы будем рассматривать в разрезе сущностей.

Сущность «Номенклатура».

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

В качестве ключа здесь берем «код» номенклатуры - уникальный идентификатор. Атрибутами здесь являются

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

«Размер» - длина, ширина, толщина, диаметр и прочие измерители размера. Данный атрибут также необходим а базе данных, так как помимо наименования идентификация и выбор металлопродукции в первую очередь осуществляется и по размеру. Все виды металлопроката имеют достаточно широкое распределение по размерам. Например, трубы могут быть в диаметре от 15 до 140 мм. Здесь следует учесть и тот факт, что размеры по видам также разнообразны. Так трубы классифицируют по диаметру, листы по толщине, квадраты по ширине и т.д.;

«Цена реализации» - цены на металлопродукцию. Цена также является очень важной характеристикой, особенно для металлопродукции. Ведь цена на такую продукцию как металлопрокат и метизы является, наверное, главной составляющей. Особенно при покупке и продаже товара. Конечно, важно и качество и при выборе товаров обычно смотрят на совокупность этих показателей т.е соотношение цена/качество. Однако, в данном случае это соотношение скорее всего неприемлемо, т.к. производителей металла не так много, потому что это очень сложный, технологический процесс и заводы производители - это огромные комплексы предприятий, поэтому о таких вещах, как подделка здесь говорить не приходится.

Сущность «Единица измерения».

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

«Код по ОКЕИ» - Код по общему классификатору единиц измерения, который здесь является ключом.

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

«Краткое наименование» - краткое наименование единиц измерения (шт, кг, т, м). Список как полных, так и кратких наименований берется из классификатора единиц измерения.

Сущность «Материал».

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

«Код» - код материала изготовления - ключ. Атрибутами являются

«Наименование» - наименование вида материала

«ГОСТ» - государственный стандарт, который берется из справочника.

Сущность «Способ изготовления».

Ключом здесь будет

«Код» - код способа изготовления.

«Наименование» - название способа изготовления - атрибут рассматриваемой сущности.

Сущность «Агент».

Партию поставляет некое юридическое лицо - контрагент. Контрагентов будем идентифицировать но коду.

«Код» - код контрагента - ключ.

«Полное наименование», «краткое наименование» - атрибуты. Краткое для «внутреннего использования», то есть в самой программе, для более быстрого поиска и более легкого восприятия.

«Контактное лицо» - контактное лицо поставщика. В атрибуте указывается ФИО представителя поставщика или покупателя, с которым осуществляются контакты. Следующие атрибуты хранят информацию о реквизитах поставщика, это

«Адрес» - адрес контрагента в формате: город, улица, дом (строение), офис;

«Телефон» - контактный телефон контрагента;

«Факс» - номер факса;

«E-mail» - адрес электронной почты;

«ИНН/КПП» - идентификационный налоговый номер / код причины постановки на учет. Данные реквизиты рассматриваем в совокупности, так как используются они в основном вместе.

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

«Расчетный счет» - расчетный счет поставщика в банке.

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

Сущность «Банк».

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

«БИК» - банковский идентификационный код - уникальный идентификатор;

«Наименование» - наименование банка;

«Корр - счет» - корреспондентский счет. Используется в случае непрямых расчетов;

«Адрес» - адрес банка.

Модель сущность - связь приведена на схеме 1.

Схема1 - Концептуальная схема даталогической модели

3. Разработка информационной системы

3.1 Требования к информационной системе

На первом этапе разработки системы мы рассматриваем требования, которым должно удовлетворять, разрабатываемое нами программное обеспечение.

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

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

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

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

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

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

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

3.2 Выбор языка программирования

Выдвигаемые требования к средствам разработки в общем виде можно сформулировать как: "быстрота, простота, эффективность, надежность".

В качестве языка программирования самой базы был выбран SQL. SQL (Structured Query Language) - это язык программирования, который используется при работе с реляционными базами данных в современных СУБД (ORACLE, dBASE IY, dBASE Y, Paradox, Access и др.).

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

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

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

Особо стоит обратить внимание на мощную и гибкую работу с базами данных в Delphi. Она основана на низкоуровневом ядре - процессоре баз данных Borland Database Engine, позволяет осуществлять доступ к данным как с использованием традиционного record-ориентированного (навигационного) подхода, так и с использованием set-ориентированного подхода, используемого в SQL-серверах баз данных. Кроме BDE, Delphi позволяет осуществлять доступ к базам данных, используя технологию (и, соответственно, драйверы) Open DataBase Connectivity (ODBC) фирмы Microsoft. Но, как показывает практика, производительность систем с использованием BDE гораздо выше, чем оных при использовании ODBC. ODBC драйвера работают через специальный “ODBC socket”, который позволяет встраивать их.

Все инструментальные средства баз данных Borland - Paradox, dBase, Database Desktop - используют BDE. Все особенности, имеющиеся в Paradox или dBase, “наследуются” BDE, и поэтому этими же особенностями обладает и Delphi.

Библиотека объектов содержит набор визуальных компонент, значительно упрощающих разработку приложений для СУБД с архитектурой клиент-сервер. Объекты инкапсулируют в себя нижний уровень - Borland Database Engine.

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

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

3.3 Физическое описание базы данных

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

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

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

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

3.4 Выбор типа базы данных

База данных организованна в формате баз данных на платформе SQL Server. Важнейшие характеристики данной СУБД - это:

простота администрирования,

возможность подключения к Web,

быстродействие и функциональные возможности механизма сервера СУБД,

наличие средств удаленного доступа,

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

3.5 Описание таблиц базы данных

База данных представлена двенадцатью таблицами (или по терминологии реляционных баз данных - двенадцатью реляционными отношениями): Nomenklatura, Realiz, Klient, Bank, Material, Edin_izm, Sposob, Vid, MOL. Рассмотрим структуру каждой более подробно.

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

Таблица1 - Nomenklatura.

Первичным ключем является поле Kod_nomen, однозначно определяющее любую запись в таблице. Поля Kod_mater, Kod_edin, Kod_sposob, Kod_specif, Kod_partia, Kod_realiz обеспечивают связь данной таблицы с другими. Также по ним построены вторичные индексы. Индексы - объекты базы данных, которые обеспечивают быстрый доступ к отдельным строкам в таблице. Индекс создается с целью повышения производительности операций запросов и сортировки данных таблицы.

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

Таблица 2 - Partia

Здесь ключевым элементом является Kod_partii, а связующими элементами Kod_klient, Kod_sklad, по которым также строятся вторичные индексы, что видно из таблицы.

В таблице Realiz представлена информация о реализации товаров. Информация о полях, их типов, назначений и ограничений, накладываемых на поля содержится в таблице 3.

Таблица 3 - Realiz

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

В таблице Klient содержится информация о контрагентах. Информация о полях, их типах, назначениях и ограничениях, накладываемых на поля содержится в таблице 4.

Таблица 4 - Klient

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

В таблице Bank представлена информация о банках. Описание данной таблицы дано ниже, в таблице 5.

Таблица 5 - Bank

В таблице Bank ключевое поле - Kod_Bank.

Таблица Material отражает сведения об материалах, из которых изготавливается металлопродукция. Ее описание представлено в таблице 7.

Таблица 7 - Material

В данной таблице ключевой элемент - Kod_Mater. Остальные поля отражают необходимые в рамках рассматриваемой области сведения о материалах.

Таблица Edin_Izm представлена ниже. В данной таблице отражаются сведения о различных видах единиц измерения. Информация о полях, их типах, назначениях и ограничениях, накладываемых на поля представлена в таблице 8.

Таблица 8 - Edin_Izm

Ключом здесь является поле Kod_OKEI.

В таблице Sposob представлены сведения о способах изготовления металлопродукции. Информация о полях содержится в таблице 9.

Таблица 9 - Sposob

Как видно из таблицы, ключевым элементом здесь является поле Kod_Sposob.

Таблица Vid отражает сведения о видах металлопродукции.

Таблица 11 - Vid

В данной таблице Kod_Vid - ключевой элемент.

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

4. Техническое проектирование

4.1 Описание структуры данных

4.1.1 Таблицы

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

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

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

4.1.2 Индексы

Индексы - одно из самых мощных средств, доступных разработчику базы данных. Индекс - это вспомогательная структура, позволяющая повышать производительность запросов за счет снижения количества операций ввода-вывода, необходимых для поиска запрошенных данных; т.е. индекс позволяет системе Microsoft SQL Server 2000 находить данные, используя меньшее число операций ввода-вывода, чем при поиске данных путем доступа только к таблице базы данных. Если для поиска строки данных вы используете индекс таблицы базы данных, SQL Server может быстро определить, где хранятся эти данные и сразу считать эти данные. Таким образом, индексы таблиц базы данных во многом похожи на индексы (алфавитные указатели) в книгах: в обоих случаях обеспечивается быстрый доступ к большим объемам информации.

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

Структура индексов ориентирована на быстрый возврат результирующих наборов. SQL Server поддерживает два типа индексов: кластерные и некластерные. Индексы могут быть созданы для одного или нескольких столбцов таблицы или представления. Индексированные таблицы поддерживаются всеми редакциями SQL Server 2000, а индексированные представления -- только SQL Server Enterprise Edition и SQL Server Developer Edition. Интенсивность использования системных ресурсов и производительность при поиске данных зависит от свойств индекса. Оптимизатор запросов использует индекс, если это позволяет повысить производительность запроса.

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

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

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

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

Для таблицы или представления можно создать до 250 некластерных индексов или 249 некластерных и 1 кластерный. Прежде чем создавать некластерные индексы для представления, необходимо создать уникальный кластерный индекс. Однако это ограничение не относится к таблицам. Некластерный индекс напоминает предметный указатель книги, где у каждого элемента проставлена соответствующая страница. Базы данных используют некластерный индекс для поиска записей, соответствующих запросу. Если в таблице нет кластерного индекса, таблица является неупорядоченной и называется кучей. Некластерный индекс, созданный для кучи, содержит указатели на записи таблицы. Каждый элемент страницы индекса содержит идентификатор строки {row ID,RID) -- указатель на табличную строку в куче, содержащий номер страницы, номер файла и номер ячейки. При наличии кластерного индекса страницы некластерного индекса содержат ключи кластерного индекса, а не R1D. Указатель индекса (как RID, так и ключ индекса) называется закладкой.

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

4.1.3 Ограничения

Ограничение - свойство, назначаемое столбцу таблицы, которое позволяет предотвратить занесение недопустимых данных в столбец. Например, ограничения UNIQUE или PRIMARY_KEY предотвращают занесение значений, дублируюших существующие.Ограничения позволяют определять, каким образом SQL Server автоматически обеспечивает целостность данных. Ограничения определяют правила, проверяющие допустимые значения столбцов, и представляют собой стандартные механизмы обеспечения целостности. Ограничения предпочтительнее триггеров, правил или умолчаний.

Ограничения позволяют определять способы, посредством которых SQL Server 2000 будет автоматически обеспечивать целостность базы данных. Ограничения определяют правила, имеющие отношение к допустимым значениям полей, и являются стандартным механизмом, обеспечивающим целостность. Предпочтительней применять ограничения, чем триггеры, правила или умолчания. Оптимизатор запросов также использует определения ограничений, чтобы создавать высокопроизводительные планы исполнения запросов.

Ограничения возможны как для столбцов, так и для таблиц:

* ограничение для столбца задается как масть определения столбца и применяется только к этому столбцу;

* ограничение для таблицы объявляется независимо от определения столбцов и применяется к нескольким столбцам таблицы.

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

4.1.4 Значения по умолчанию

Все поля записи должны быть заполнены (даже с использованием пустых значений). Иногда требуется загрузить в таблицу строку, но значение какого-либо поля не известно (или не существует). Если поле допускает пустые значения, можно загрузить строку с пустым значением. Поскольку поля, допускающие пустые значения, нежелательны, лучше определить для поля значение по умолчанию (определить ограничение DEFAULT). Например, в качестве значения по умолчанию, которое используется, если значение не указано, для числовых полей обычно задают ноль, а для символьных -- «N/A».

При загрузке в таблицу строки с неизвестным значением поля, для которого определено значение по умолчанию, вы неявно даете SQL Server указание загрузить в это поле значение по умолчанию.

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

Есть два способа определения значения по умолчанию:

* определить его во время создания самой таблицы (как часть определения таблицы);

* добавить это определение к существующей таблице (у любого столбца таблицы оно может быть только одно).

При модификации определения значения по умолчанию с помощью Transact-SQL необходимо сначала удалить существующее определение DEFAULT а затем создать новое определение «с нуля».

Невозможно создать определения DEFAULT в следующих столбцах:

* с типом данных timestamp;

* со свойствами IDENTITY или ROWGUIDCOL;

* с существующим определением или объектом по умолчанию.

Значение по умолчанию должно быть совместимо с типом данных столбца, к которому относится определение DEFAULT. Например, необходимо, чтобы значением по умолчанию для столбца с типом данных int было целое число, а не символьная строка, Когда определение DEFAULT применяется к существующему столбцу, SQL Server применяет (по умолчанию) новое определение только к новым строкам, добавленным к таблице. Существующие данные, вставленные во время действия старого определения значения по умолчанию, не затрагиваются. Однако при добавлении к существующей таблице нового столбца можно указать SQL Server вставить в существующие строки таблицы не пустые значения, а значения по умолчанию (заданные определением умолчания для этого столбца).

В нашей работе значения по умолчания не были созданы вследствие отсутствия необходимости в них.

4.2 Описание объектов базы данных

4.2.1 Представления

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

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

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

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

* ограничивать доступную пользователю область таблицы определенными строками и/или столбцами;

* объединять столбцы из нескольких таблиц, представляя их в виде единой таблицы;

* заменять детальные сведения агрегированными.

Представления позволяют секционировать данные и распределять их между несколькими БД или экземплярами SQL Server 2000. С помощью секционированных представлений распределяют нагрузку по обработке данных между несколькими серверами, составляющими одну группу.

SQL Server 2000 также поддерживает индексирование представлений. Это позволяет значительно повысить производительность сложных представлений, которые часто используются в хранилищах данных и других системах поддержки принятия решений. Результирующий набор стандартного представления, описанный логикой определяющего его оператора, не хранится в базе данных, а динамически создается в период выполнения.

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

4.2.2 Хранимые процедуры

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

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

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

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

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

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

Если разработчикам удобно писать сложные программы на таких языках, как C++, то затем эти программы можно вызывать из SQL Server средствами хранимых процедур особого типа, которые называются расширенными хранимыми процедурами.

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

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

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

Системные хранимые процедуры находятся в базе данных Master. Как правило, их имена начинаются с префикса sp_. Они предназначены для поддержки функций SQL Server (в частности, процедур для работы с каталогом). К ним относится выборка данных из системных таблиц внешними приложениями, администрирование базы данных и управление безопасностью.

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

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

Временная хранимая процедура похожа на локальную, однако она существует лишь до закрытия соединения, в котором создана, или до завершения работы SQL Server. В зависимости от типа такая процедура удаляется после завершения работы сервера или разрыва соединения. Непостоянство обусловлено тем, что временные хранимые процедуры находятся в базе данных TempDB. При каждом запуске сервера эта база создается заново, поэтому после закрытия сервера все объекты этой базы данных исчезают. Временные хранимые процедуры полезны при работе с более ранними версиями SQL Server, которые не поддерживают повторное использование планов исполнения, а также в тех случаях, когда нет смысла сохранять процедуру, поскольку значения ее параметров постоянно меняются.

Существует три типа временных хранимых процедур: локальные (или закрытые), глобальные и создаваемые непосредственно в TempDB. Локальная процедура всегда начинается с символа #, а глобальная -- с ##. При исполнении временной хранимой процедуры ее область действия ограничена соединением, в котором она создана. Однако такая процедура видима всем пользователям, установившим соединение с базой данных, в окне Object Browser в Query Analyzer. Ограниченность области ее действия исключает возникновение конфликтов имен с другими соединениями, в которых созданы временные хранимые процедуры. Чтобы гарантировать уникальность имени временной хранимой процедуры, SQL Server добавляет к нему набор символов подчеркивания и уникальный номер соединения. Привилегии для локальной процедуры не предоставляются другим пользователям. Временная хранимая процедура удаляется из TempDB при закрытии соединения, в котором она создана.

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

Глобальные временные процедуры так же непостоянны, как и локальные. Они удаляются после закрытия соединения, в котором созданы.

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

* для них разрешается настроить права доступа;

* они сохраняются даже после завершения соединения, в котором созданы;

* они не удаляются до завершения работы SQL Server.

Поскольку процедуры этого типа создаются непосредственнов TempDB, важно полностью определять имя объекта базы данных в коде Transact-SQL.

Расширенные хранимые процедуры обращаются к внешним программам, скомпилированным в виде 32-разрядных DLL. Некоторые системные хранимые процедуры также рассматриваются как расширенные. Соглашение об именовании предполагает использование в именах расширенных хранимых процедур префикса хр_. Однако имена некоторых расширенных процедур начинаются с префикса sp_, а в именах некоторых других, не расширенных процедур используется префикс хр_. Поэтому нельзя различить системные и расширенные хранимые процедуры, полагаясь лишь на отличия в именах.

Определить, является ли хранимая процедура расширенной, позволяет функция ОВ-JECTPROPERTY. Она возвращает для свойства IsExtendedProc значение 1, если процедура является расширенной, или 0, если процедура таковой не является.

Как следует из названия, удаленная хранимая процедура работает на удаленной копии SQL Server. Удаленные хранимые процедуры оставлены для совместимости с предыдущими версиями, в SQL Server 2000 их заменили распределенные запросы.

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

Процедуры удаления данных:

set ANSI_NULLS OFF

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[AgentDelete]

@Kod_Agent int

AS

Delete from Agent

where

Kod_Agent=@Kod_Agent

Процедуры добавления данных:

set ANSI_NULLS OFF

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[AgentEdit]

@Kod_Agent int,

@Naimen_Agent varchar(25),

@Kont_lico varchar(20),

@Adres varchar(25),

@Tel varchar (25),

@Schet varchar (25)

AS

Update Agent SET

Naimen_Agent = @Naimen_Agent,

Kont_lico = @Kont_lico,

Adres = @Adres,

Tel = @Tel,

Schet = @Schet

where

Kod_Agent=@Kod_Agent

Процедуры обновления данных:

set ANSI_NULLS OFF

set QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[UpdPrihKol]

AS

update Nomenkl

set prihod = p.kolich

from (select kod_nomen, sum(kolich) as kolich