Министерство образования и науки Российской Федерации
Государственное образовательное учреждение высшего

профессионального образования

«РЯЗАНСКИЙ ГОСУДАТСТВЕННЫЙ РАДИОТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ»
Кафедра ЭВМ
К защите

Руководитель проекта

_____________________________

дата, подпись
ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

К КУРСОВОМУ ПРОЕКТУ

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

«Системы управления базами данных»

Выполнил студент группы 542 Нефедов В.В.		__________________________ дата сдачи на проверку, подпись
Руководитель проекта доц. каф. ЭВМ Гринченко Н.Н.	________________ оценка		__________________________ дата защиты, подпись

Рязань 2009

Оглавление

1. Теоретическая часть

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

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

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

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

При проектировании базы данных решаются две основные проблемы:

1.Отображение объектов предметной области в абстрактные объекты модели данных таким образом, чтобы это отображение не противоречило семантике предметной области, и было по возможности лучшим (эффективным, удобным и т.д.). Часто эту проблему называют проблемой логического проектирования баз данных;

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

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

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

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

Моделирование данных – это процесс создания логического представления структуры базы данных.

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

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

В настоящее время существует два различных подхода к моделированию данных:

1. 
   модель базы данных типа «сущность - связь» (entity-relationship model), имеющая значительное количество сторонников среди профессионалов;
   
2. 
   семантическая объектная модель (некоторые считают ее более простой и точной).
   

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

Эта модель введена Питером Ченом в 1976 году.

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

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

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

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

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

Центральной компонентой инфологической модели является описание объектов предметной области и связей между ними (ER-модель).

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

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

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

Экземпляр сущности относится к конкретной вещи в наборе. Например, типом сущности может быть ГОРОД, а экземпляром – Москва, Киев и т.д.

Атрибут – поименованная характеристика сущности.

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

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

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

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

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

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

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

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

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

Нормализация выполняется поэтапно. Первые три шага были описаны доктором Э.Ф. Коддом в статье "Дальнейшая нормализация реляционной модели базы данных" в 1972г.

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

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

Вторая нормальная форма (2НФ). Для 2НФ требуется, чтобы все поля таблицы зависели от первичного ключа, то есть, чтобы первичный ключ однозначно определял запись и не был избыточен.

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

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

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

Для третьей нормальной формы (ЗНФ) требуется, чтобы все не ключевые столбцы таблицы зависели от первичного ключа таблицы, но были независимы друг от друга. Для этого требуется, чтобы таблицы были приведены к 1НФ и 2НФ.

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

Если в отношении имеется много функциональных зависимостей, 4НФ не устраняет избыточность, то применяют пятую нормальную форму (ЗНФ).

Разложение отношений из 4НФ в пятую нормальную форму должно быть выполнено так, чтобы каждая проекция, полученная из 4НФ, содержала не менее одного возможного ключа и хотя бы один неключевой атрибут из исходного отношения. Для 5НФ требуется, чтобы можно было восстановить исходную таблицу на основе информации таблиц, на которые она была разбита. Кроме того, необходимо, чтобы таблицы соответствовали ЗНФ, а при наличии отношений "многие-ко-многим" - 4НФ.

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

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

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

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

• 
  Таблицы
  
• 
  Правила
  
• 
  Умолчания
  
• 
  Определяемые пользователем типы данные
  
• 
  Представления
  
• 
  Хранимые процедуры
  
• 
  Триггеры
  

Таблица – основной объект для хранения информации в реляционной базе данных. Она состоит из содержащих данные строк и столбцов, занимает в базе данных физическое пространство и может быть постоянной или временной.

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

Базовый синтаксис оператора создания таблицы имеет следующий вид:

CREATE TABLE имя_таблицы

(имя_столбца тип_данных [,...n])
Правила используются для ограничения значений, хранимых в столбце таблицы или в пользовательском типе данных. Они существуют как самостоятельные объекты базы данных, которые связываются со столбцами таблиц и пользовательскими типами данных. Контроль значений данных может быть реализован и с помощью ограничений целостности. Синтаксис:
CREATE RULE <имя правила> AS

<условия правила>
Привязка правил осуществляется с помощью процедуры sp_bindrule:
sp_bindrule имя_правила, таблица.столбец
Умолчания (значения по умолчанию) – это значения, которые заносятся в определенную колонку, когда не указано явно никакого значения. Оператор CREATE DEFAULT имеет следующий синтаксис:
CREATE DEFAULT имя_умолчания AS выражение-константа

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

sp_bindefault 'Имя умолчания', 'таблица.столбец'
Пользовательские типы данных – это типы данных, которые создает пользователь на основе системных типов данных, когда в нескольких таблицах необходимо хранить однотипные значения; причем нужно гарантировать, что столбцы в таблице будут иметь одинаковый размер, тип данных и чувствительность к значениям NULL. Синтаксис:
sp_addtype имя пользовательского типа, системный тип данных, 'NOT NULL'|'NULL'
Представления, или просмотры (VIEW), представляют собой временные, производные (иначе - виртуальные) таблицы и являются объектами базы данных, информация в которых не хранится постоянно, как в базовых таблицах, а формируется динамически при обращении к ним. Обычные таблицы относятся к базовым, т.е. содержащим данные и постоянно находящимся на устройстве хранения информации. Представление не может существовать само по себе, а определяется только в терминах одной или нескольких таблиц. Применение представлений позволяет разработчику базы данных обеспечить каждому пользователю или группе пользователей наиболее подходящие способы работы с данными, что решает проблему простоты их использования и безопасности. Содержимое представлений выбирается из других таблиц с помощью выполнения запроса, причем при изменении значений в таблицах данные в представлении автоматически меняются. Представление - это фактически тот же запрос, который выполняется всякий раз при участии в какой-либо команде. Результат выполнения этого запроса в каждый момент времени становится содержанием представления. У пользователя создается впечатление, что он работает с настоящей, реально существующей таблицей.

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

Создания и изменения представлений в стандарте языка и реализации в MS SQL Server совпадают и представлены следующей командой:
{ CREATE| ALTER} VIEW имя_просмотра

[(имя_столбца [,...n])]

[WITH ENCRYPTION]

AS SELECT_оператор

[WITH CHECK OPTION]

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

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

Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:
{CREATE | ALTER } PROC[EDURE] имя_процедуры

[{@имя_параметра тип_данных} [Значение по умлочанию][OUTPUT] ][,...n]

[WITH {RECOMPILE, ENCRYPTION }]

AS

sql_оператор [...n]

GO

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

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

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

CREATE TRIGGER имя_триггера

ON <имя_таблицы>|<представление>

[WITH ENCRYPTION]

{AFTER|INSTEAD OF TRIGGER}{INSERT,UPDATE,DELETE}

AS

<тело_триггера>

GO

2. Проектирование базы данных

Описание предметной области

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

Разработка базы данных проводилась в Microsoft SQL Server, который предназначен для управления, проектирования и разработки баз данных.

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

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

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

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

1 Этап. Выделение сущностей и связей

В результате этих рассуждений мы можем выделить следующий набор сущностей:

1. 
   Сотрудник
   
2. 
   Отдел
   
3. 
   Должность
   
4. 
   Препарат
   
5. 
   Поставка
   
6. 
   Поставщик
   
7. 
   Отпуск
   
8. 
   Рецепт
   
9. 
   Зарплата
   

и следующий набор связей:

1. 
   Сотрудник получает зарплату
   
2. 
   Сотрудник занимает должность
   
3. 
   Сотрудник работает в отделе
   
4. 
   Сотрудник уходит в отпуск
   
5. 
   Препарат относится к определенному отделу
   
6. 
   Препарат имеет цену
   
7. 
   Поставка осуществляется поставщиком
   
8. 
   Поставка содержит в себе препарат
   
9. 
   Рецепт выписывается на препарат
   

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


1). Сотрудник обязательно получает зарплату



Сотрудник обязательно получает зарплату. Зарплата обязательно выдаётся сотруднику. Фиксируется дата выдачи зарплаты, также предусмотрены премии сотрудникам.

2). Сотрудник обязательно занимает одну должность



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

3). Сотрудник обязательно работает в каком-то отделе



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

4). Каждому сотруднику соответствует один отпуск, в каждом году



Каждому сотруднику соответствует один отпуск, в каждом году. Каждая запись об отпуске соответствует одному сотруднику, фиксируется начало и конец отпуска сотрудника.
5). Каждая поставка осуществляется одним поставщиком



Каждая поставка осуществляется одним поставщиком. Каждый поставщик может совершать несколько поставок. Фиксируется информация Адреса и телефона поставщика, если она известна.

6). В каждой поставке может быть несколько препаратов



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

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



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

8). Каждый препарат относится к одному отделу.



Каждый препарат относится к одному отделу. Не все отделы имеют препараты. У отдела есть заведующий, который отвечает за препараты, которые в нем есть. Есть номер телефона заведующего.
Общая ER-диаграмма:

Этап 3. Формирование предварительных отношений по ER-диаграмме

Сотрудник занимает должность

По правилу 4 получим 2 отношения:

• 
  Должности (Должность)
  
• 
  Сотрудники(Фамилия, имя, отчество, должность)
  

Сотрудник уходит в отпуск

По правилу 1 получим 1 отношение:

• 
  Отпуска (Фамилия, имя, отчество, год)
  

Сотрудник обязательно получает зарплату

По правилу 1 получим 1 отношение:

• 
  Зарплаты (Фамилия, имя, отчество, дата)
  

Сотрудник работает в отделе

По правилу 4 получим 2 отношения:

• 
  Сотрудники(Фамилия, имя, отчество, отдел)
  
• 
  Отделы(Отдел)
  

Препарат занимает место в отделе
По правилу 4 получим 2 отношения:

• 
  Отделы(Отдел)
  
• 
  Препараты(Препарат, форма выпуска, отдел)
  

Препарат выписывается на рецепт

По правилу 6 получим 3 отношения:

• 
  Препараты(Препарат, форма выпуска, по рецепту)
  
• 
  Препараты по рецепту(Препарат, форма выпуска, № рецепта)
  
• 
  Рецепты(№ рецепта)
  

Поставка содержит препараты

По правилу 6 получим 3 отношения:

• 
  Препараты(Препарат, форма выпуска)
  
• 
  Поставляемы препараты(Препарат, форма выпуска, Номерпост)
  
• 
  Поставки(Номерпост, Поставщик)
  

Поставка осуществляется поставщиком

По правилу 4 получим 2 отношения:

• 
  Поставки(Номерпост, Поставщик)
  
• 
  Поставщики(Поставщик)
  

4 Этап. Подготовка списка атрибутов

1. 
   Фамилия сотрудника
   
2. 
   Имя сотрудника
   
3. 
   Отчество сотрудника
   
4. 
   Номер паспорта
   
5. 
   Серия паспорта
   
6. 
   Дата выдачи паспорта
   
7. 
   Адрес
   
8. 
   Домашний телефон
   
9. 
   Отдел
   
10. 
    Заведующий отделом
    
11. 
    Телефон отдела
    
12. 
    Требуемое образование
    
13. 
    Оклад
    
14. 
    Должность
    
15. 
    Цена
    
16. 
    Форма выпуска
    
17. 
    По рецепту
    
18. 
    Показания
    
19. 
    Условия хранения
    
20. 
    Препарат
    
21. 
    Форма выпуска
    
22. 
    Поставка
    
23. 
    Дата поставки
    
24. 
    Количество в поставке
    
25. 
    Цена за штуку
    
26. 
    Адрес поставщика
    
27. 
    Телефон поставщика
    
28. 
    Поставщик
    
29. 
    Год выхода в отпуск
    
30. 
    Начало отпуска
    
31. 
    Конец отпуска
    
32. 
    № Рецепта
    
33. 
    Фамилия покупателя по рецепту
    
34. 
    Имя покупателя по рецепту
    
35. 
    Отчество покупателя по рецепту
    
36. 
    Дата выдачи рецепта
    
37. 
    Препарат по рецепту
    
38. 
    Количество по рецепту
    
39. 
    Дата начисления зарплаты
    
40. 
    Зарплата
    
41. 
    Премия
    

5 Этап. Распределение атрибутов по отношениям

1. 
   Сотрудники (Фамилия сотрудника, Имя сотрудника, Отчество сотрудника, Номер паспорта, Серия паспорта, Дата выдачи паспорта, Адрес, Домашний телефон, Отдел, Должность)
   
2. 
   Отделы (Отдел, Заведующий отделом, Телефон)
   
3. 
   Должности (Должность, Требуемое образование, Оклад)
   
4. 
   Препараты (Препарат, Форма выпуска, Цена, По рецепту, Отдел, Показания, Условия хранения)
   
5. 
   Поставки (№ Поставки, Поставщик, Дата)
   
6. 
   Поставщики (поставщик, Адрес, Телефон)
   
7. 
   Рецепты (№ рецепта, ФИО покупателя по рецепту, Дата)
   
8. 
   Отпуска (ФИО сотрудника, Год, Начало отпуска, Конец отпуска)
   
9. 
   Зарплаты (ФИО сотрудника, Дата, Зарплата, Премия)
   
10. 
    Поставляемые препараты (№ Поставки, Препарат, Форма выпуска Количество, Цена за единицу)
    
11. 
    Препараты по рецептам (Препарат, Форма выпуска, № рецепта, Количество по рецепту)
    

6 Этап. Проверка отношений на БКНФ

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

Отношение находится в 3НФ, т. к. в нём нет транзитивных зависимостей неключевых атрибутов.

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

• 
  Сотрудники (Фамилия сотрудника, Имя сотрудника, Отчество сотрудника, Номер паспорта, Серия паспорта, Дата выдачи паспорта, Адрес, Домашний телефон, Отдел, Должность)
  

• 
  Отделы (Отдел, Заведующий отделом, Телефон)
  

• 
  Должности (Должность, Требуемое образование, Оклад)
  

• 
  Препараты (Препарат, Форма выпуска, Цена, По рецепту, Отдел, Показания, Условия хранения)
  

• 
  Поставки (№ Поставки, Поставщик, Дата)
  

• 
  Поставщики (поставщик, Адрес, Телефон)
  

• 
  Рецепты (№ рецепта, ФИО покупателя по рецепту, Дата)
  

• 
  Отпуска (ФИО сотрудника, Год, Начало отпуска, Конец отпуска)
  

• 
  Зарплаты (ФИО сотрудника, Дата, Зарплата, Премия)
  

• 
  Поставляемые препараты (№ Поставки, Препарат, Форма выпуска, Количество, Цена за единицу)
  

• 
  Препараты по рецептам (Препарат, Форма выпуска, № рецепта, Количество по рецепту)
  

Все отношения находятся в БКНФ, т.к. 2 потенциальных ключа полностью определяют все функциональные зависимости.

7 Этап. Перестройка ER-диаграмм.

Не выполняется, т.к. все отношения находятся в БКНФ и нет нераспределенных атрибутов.


8 Этап. Построение схемы БД.

3. Разработка объектов БД

В курсовом проекте были созданы пользовательские типы данных:

• 
  «Телефон» – 10 символьная строка. Допускаются NULL-значения
  
• 
  «Адрес» - 100 символьная строка. Допускаются NULL-значения
  
• 
  «Дата» – Основан на типе smalldatetime. Допускаются NULL-значения
  

В базе данных созданы правила:

• 
  Price_rule – Правило задающее положительное значение величины но не большей чем 1000.
  
• 
  ZAGLAV – Правило определяющее первую букву закглавной.
  
• 
  Num – правило предполагает, что строка должна начинаться с цифр
  

В базе данных созданы умолчания:

• 
  Bomj– задает значение n/а по умолчанию
  
• 
  God – задает 2009 год по умолчанию.
  
• 
  Obr – задает значение поля по умолчанию равным «среднее»
  

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

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

Были разработаны хранимые процедуры:

• 
  Изменить_Поставщик – изменяет информацию о поставщике и обновляет таблицу
  
• 
  Новый_Поставщик – добавляет информацию о новом поставщике в таблицу Поставщики(Имя поставщика, Адрес и телефон)
  
• 
  Удаляем_Поставщики – позволяет удалять поставщика из таблицы Поставщики с ее информацией о нем.
  

При разработке данной БД были спроектированы триггеры:

• 
  TRIGGER T1 – Alter триггер позволяющий удалять информацию о поставках из таблицы поставки и других таблиц содержащую информацию об удаляемой поставки, кроме поставки под номером 6
  
• 
  TRIGGER Print_Update – Выводит информации обновлении/модификации Таблицы Рецепты, при добавлении нового рецепта.
  
• 
  TRIGGER УДАЛ_СОТР – позволяет удалять (увольнять) сотрудников из таблицы сотрудники. Удалять можно всех кроме заведующего аптекой.
  

4. Список литературы

1. И.Ф. Астахова, А.П. Толстобров, В.М. Мельников «SQL в примерах и задачах» М., Новое знание, 2002 г.

2. А.Д. Хомоненко «Базы данных» СПб, Корона-принт, 2004 г

3. Мартин Грубер «Понимание SQL» М., 1993 г

4. Кевин Клайн «SQL справочник» М, Кудиц-образ, 2006

5. Леон Аткинсон «MySQL. Библиотека профессионала» M, «Вильяме», 2002

6. Артеменко Ю.Н. «MySQL. Справочник по языку» M, «Вильяме», 2005

7. INTUIT.RU

8. Роб Хортон. Разработка баз данных Microsoft SQL Server 2000 на примерах. Вильямс. 2001

9. Функции SQL. Справочник программиста, Эйри Джоунс, Райан К. Стивенз, Рональд Р. Плю, Роберт Ф. Гарретт, Алекс Кригель

10. Луис Дэвидсон. Проектирование баз данных на SQL Server 2000.: Бином. Лаборатория знаний, 2003» и «Kursach_Log.mdl» а также текстовых файлов, содержащих скрипты создания БД, запросов, триггеров, хранимых процедур.

Приложение 1. Сценарий создания структуры базы данных

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Сотрудники_Должности]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Сотрудники] DROP CONSTRAINT FK_Сотрудники_Должности

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Препараты_Отделы]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Препараты] DROP CONSTRAINT FK_Препараты_Отделы

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Сотрудники_Отделы]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Сотрудники] DROP CONSTRAINT FK_Сотрудники_Отделы

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Поставляемые препараты_Поставки]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Поставляемые препараты] DROP CONSTRAINT FK_Поставляемые препараты_Поставки

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Поставки_Поставщики]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Поставки] DROP CONSTRAINT FK_Поставки_Поставщики

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Поставляемые препараты_Препараты]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Поставляемые препараты] DROP CONSTRAINT FK_Поставляемые препараты_Препараты

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Препараты по рецептам_Препараты]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Препараты по рецептам] DROP CONSTRAINT FK_Препараты по рецептам_Препараты

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Препараты по рецептам_Рецепты]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Препараты по рецептам] DROP CONSTRAINT FK_Препараты по рецептам_Рецепты

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Зарплаты_Сотрудники]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Зарплаты] DROP CONSTRAINT FK_Зарплаты_Сотрудники

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Отпуска_Сотрудники]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Отпуска] DROP CONSTRAINT FK_Отпуска_Сотрудники

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ПОСТ3]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[ПОСТ3]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Преппрод]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[Преппрод]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[сотрвыс]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[сотрвыс]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Должности]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Должности]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Зарплаты]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Зарплаты]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Отделы]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Отделы]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Отпуска]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Отпуска]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Поставки]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Поставки]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Поставляемые препараты]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Поставляемые препараты]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Поставщики]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Поставщики]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Препараты]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Препараты]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Препараты по рецептам]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Препараты по рецептам]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Рецепты]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Рецепты]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Сотрудники]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Сотрудники]

GO

if exists (select * from dbo.systypes where name = N'adr')

exec sp_droptype N'adr'

GO

if exists (select * from dbo.systypes where name = N'data')

exec sp_droptype N'data'

GO

if exists (select * from dbo.systypes where name = N'phone')

exec sp_droptype N'phone'

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Num ]') and OBJECTPROPERTY(id, N'IsRule') = 1)

drop rule [dbo].[Num ]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZAGLAV]') and OBJECTPROPERTY(id, N'IsRule') = 1)

drop rule [dbo].[ZAGLAV]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[price_rule]') and OBJECTPROPERTY(id, N'IsRule') = 1)

drop rule [dbo].[price_rule]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Бомж]') and OBJECTPROPERTY(id, N'IsDefault') = 1)

drop default [dbo].[Бомж]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Обр-е]') and OBJECTPROPERTY(id, N'IsDefault') = 1)

drop default [dbo].[Обр-е]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[год]') and OBJECTPROPERTY(id, N'IsDefault') = 1)

drop default [dbo].[год]

GO

create default [Бомж] as 'n/a'

GO

create default [Обр-е] as 'среднее'

GO

create default [год] as 2009

GO

create rule [Num ] as --правило, определяющее начало слова с цифры

(@name LIKE '[0-9]%' )

GO

CREATE RULE ZAGLAV

AS

(@Наименование LIKE '[А-Я, A-Z]%')

GO

CREATE RULE price_rule AS

(@Цена >= .01 AND @Цена <= 1000.00)

GO

setuser

GO

EXEC sp_addtype N'adr', N'char (100)', N'null'

GO

setuser

GO

setuser

GO

EXEC sp_addtype N'data', N'smalldatetime', N'null'

GO

setuser

GO

setuser

GO

EXEC sp_addtype N'phone', N'char (10)', N'null'

GO

setuser

GO

CREATE TABLE [dbo].[Должности] (

[Должность] [char] (50) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Требуемое образование] [char] (20) COLLATE Cyrillic_General_CI_AS NULL ,

[Оклад] [money] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Зарплаты] (

[Фамилия] [char] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Имя] [char] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Отчество] [char] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Дата] [datetime] NOT NULL ,

[Зарплата] [money] NULL ,

[Премия] [money] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Отделы] (

[Отдел] [char] (80) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Заведующий] [char] (20) COLLATE Cyrillic_General_CI_AS NULL ,

[Телефон] [int] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Отпуска] (

[Фамилия] [char] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Имя] [char] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Отчество] [char] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Год] [int] NOT NULL ,

[Начало отпуска] [datetime] NULL ,

[Конец отпуска] [datetime] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Поставки] (

[Номерпост] [int] NOT NULL ,

[Поставщик] [char] (35) COLLATE Cyrillic_General_CI_AS NULL ,

[Дата] [datetime] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Поставляемые препараты] (

[Номерпост] [int] NOT NULL ,

[Препарат] [char] (70) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Форма выпуска] [char] (30) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Количество] [int] NULL ,

[Цена за единицу] [char] (10) COLLATE Cyrillic_General_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Поставщики] (

[Поставщик] [char] (35) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Адрес] [adr] NULL ,

[Телефон] [phone] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Препараты] (

[Препарат] [char] (70) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Форма Выпуска] [char] (30) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Цена] [money] NULL ,

[Отдел] [char] (80) COLLATE Cyrillic_General_CI_AS NULL ,

[По рецепту] [bit] NULL ,

[Показания] [char] (120) COLLATE Cyrillic_General_CI_AS NULL ,

[Условия хранения] [char] (100) COLLATE Cyrillic_General_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Препараты по рецептам] (

[Препарат] [char] (70) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Форма выпуска] [char] (30) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[№ рецепта] [int] NOT NULL ,

[Количество по рецепту] [int] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Рецепты] (

[№ рецепта] [int] NOT NULL ,

[Фамилия] [char] (20) COLLATE Cyrillic_General_CI_AS NULL ,

[Имя] [char] (20) COLLATE Cyrillic_General_CI_AS NULL ,

[Отчество] [char] (20) COLLATE Cyrillic_General_CI_AS NULL ,

[Дата] [data] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Сотрудники] (

[Фамилия] [char] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Имя] [char] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Отчество] [char] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,

[Серия паспорта] [int] NULL ,

[№ паспорта] [int] NULL ,

[Дата выдачи паспорта] [datetime] NULL ,

[Адрес] [char] (80) COLLATE Cyrillic_General_CI_AS NULL ,

[Домашний телефон] [int] NULL ,

[Отдел] [char] (80) COLLATE Cyrillic_General_CI_AS NULL ,

[Должность] [char] (50) COLLATE Cyrillic_General_CI_AS NULL

) ON [PRIMARY]

GO

setuser

GO

EXEC sp_bindefault N'[dbo].[Обр-е]', N'[Должности].[Требуемое образование]'

GO

setuser

GO

setuser

GO

EXEC sp_bindefault N'[dbo].[год]', N'[Отпуска].[Год]'

GO

setuser

GO

setuser

GO

EXEC sp_bindrule N'[dbo].[Num ]', N'[Поставщики].[Телефон]'

GO

setuser

GO

setuser

GO

EXEC sp_bindrule N'[dbo].[price_rule]', N'[Препараты].[Цена]'

GO

setuser

GO

setuser

GO

EXEC sp_bindefault N'[dbo].[Бомж]', N'[Сотрудники].[Адрес]'

GO

EXEC sp_bindrule N'[dbo].[ZAGLAV]', N'[Сотрудники].[Фамилия]'

GO

setuser

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE VIEW ПОСТ3

AS

SELECT *

FROM [Поставляемые препараты]

WHERE Номерпост = 3

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

Create View Преппрод

As

SELECT Препарат,

Отдел,

Цена

FROM Препараты

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE VIEW dbo.VIEW3

AS

SELECT dbo.Сотрудники.Должность, dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Отчество, dbo.Сотрудники.Адрес,

dbo.Сотрудники.Отдел, dbo.Сотрудники.[Домашний телефон], dbo.Должности.[Требуемое образование]

FROM dbo.Сотрудники INNER JOIN

dbo.Должности ON dbo.Сотрудники.Должность = dbo.Должности.Должность

WHERE (dbo.Должности.[Требуемое образование] = 'высшее')

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Приложение 2. Скрипт создания запросов

use Apteka

go

--Препараты начинающиеся на букву «Ц»

SELECT *

FROM Препараты

WHERE Препарат LIKE 'Ц%'

Go


--Количество сотрудников по должностям

SELECT Должность ,count(Должность) AS 'Количество' From Сотрудники

GROUP BY Должность

Go


--вывод в валюте цены по наименованию препарата

DECLARE @Препарат char(70), --наименование препарата

@Цена money, --цена препарата

@val char(3), --валюта

@Цена_in_val money --цена в валюте

--Определить наименование препарата и валюту

SET @Препарат='Ренни'

SET @val='$'

--Определить цену препарата по названию препарата

SELECT @Цена=Цена

FROM Препараты

WHERE Препарат=@Препарат

--Определить цену препарата в заданной валюте

SELECT @Цена_in_val=

CASE @val

WHEN 'руб' THEN @Цена

WHEN '$' THEN @Цена /28

WHEN 'EUR' THEN @Цена /34

END

--Вывод сообщения

PRINT CONVERT(char(5), @Цена_in_val)


--препараты дешевле 20 р.

SELECT * From Препараты

Where Цена<20

Go


--Сотрудники с высшим образованием

SELECT * From Должности

Where [Требуемое образование]='Высшее'

Go
--Снижение цен на препараты по рецепту

UPDATE Препараты

SET Цена=Цена*0.9

WHERE [По рецепту]=1

Go
--вывод ближайших поставок препаратов

SELECT * From поставки

Where Дата>GetDate()

Go

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

--изменение поставщика (ХП)

Use Apteka

go

CREATE PROCEDURE Изменить_Поставщик(

@postavshik char(35),

@adress char(20),

@telephon char(20)

)

AS

BEGIN

UPDATE Поставщики

SET Адрес = @adress, Телефон = @telephon

WHERE Поставщик = @postavshik

END

GO
--Добавление поставщика (ХП)

CREATE PROCEDURE Новый_Поставщик(

@post char(35),

@adress char(20),

@tel char(20)

)

AS

BEGIN

INSERT INTO Поставщики

VALUE (@post,@adress,@tel)

END

GO

--Удаление поставщика (ХП)

CREATE PROCEDURE Удаляем_Поставщики(@post char(35))

AS

BEGIN

delete

from Поставщики

where Поставщик = @post;

END

GO
--Триггер Т1

ALTER TRIGGER T1

ON Поставки

AFTER DELETE

AS

IF EXISTS (SELECT * FROM DELETED WHERE Номерпост=6)

BEGIN

ROLLBACK TRAN

PRINT 'Нельзя удалить'

END

GO

INSERT INTO Поставщики

VALUES ('Birn','Берлин','84484845')

INSERT INTO Поставщики

VALUES ('Moscow','Россия','84582135')

INSERT INTO Поставки

VALUES(7,'Birn','08.07.2008')

INSERT INTO Поставки

VALUES(8,'Moscow','08.06.2008')


GO
DELETE

FROM Поставки

where (Номерпост=6) or (Номерпост>6)

GO
--Триггер Print_Update

USE Apteka

GO

IF EXISTS (SELECT name

FROM sysobjects

WHERE name = 'Print_Update' AND

type = 'TR')

DROP TRIGGER Print_Update

GO

CREATE TRIGGER Print_Update

ON Рецепты

FOR UPDATE

AS

PRINT 'Таблица Рецепты обновлена/модифицирована'

GO


INSERT INTO Рецепты VALUES (6,'afds','sdfsdf','asdfasdf','05.01.2008')

GO


UPDATE Рецепты

SET Дата = '02.01.2008'

WHERE [№ рецепта] = 6

GO
--Триггер удал_сотр

CREATE TRIGGER УДАЛ_СОТР

ON Сотрудники

INSTEAD OF DELETE

AS
DELETE

FROM Сотрудники

WHERE Должность <>'Заведующий'

IF EXISTS(SELECT *

FROM Deleted

WHERE Должность='Заведующий')

Print 'Нельзя удалить/уволить заведующего'

go