Проектирование базы данных интернет-магазина средствами structured query language Server

Размещено на http://www.allbest.ru/

Кировский филиал федерального государственного бюджетного образовательного учреждения высшего образования "Российская академия народного хозяйства и государственной службы при Президенте Российской Федерации"

Факультет экономики и управления

Кафедра информатики и математики

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

по дисциплине "Базы данных"

Проектирование базы данных интернет-магазина для ООО "Приволжской Механический Завод" средствами SQL Server



Содержание

Введение

1. Концептуальное проектирование

2. Логическое проектирование базы данных

2.1 Модель "сущность-связь"

2.2 Классификация связей

2.3 Определение связей

2.4 Нормализация отношений

3. Даталогическое проектирование

3.1 Состав таблиц базы банных

3.2 Средства поддержания целостности

4. Программная реализация базы данных интернет магазина для ООО "ПМЗ" средствами SQL SERVER

4.1 Создание базы данных

4.2 Установка связей между таблицами

4.3 Создание пользовательских ограничений

4.4 Заполнение базы данных тестовыми данными

4.5 Создание логических триггеров

4.6 Создание запросов

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

Заключение

Библиографический список

Приложение



Введение

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

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

Система управления базой данных - СУБД - это множество средств (языковых и программных), используемых для создания, управления и многопользовательского использования баз данных [2].

SQL (structures query language) - универсальный логический язык, позволяющий создавать, изменять и управлять данными в реляционных БД. Диалекты языка SQL являются полноценными языками программирования. Операторы SQL делятся на 4 группы:

· операторы определения данных;

· операторы манипуляции данными;

· операторы определения доступа к данным;

· операторы управления транзакциями.

Преимущества SQL - независимость от конкретных СУБД (запросы незатруднительно перенести из одной СУБД в другую); стандарты SQL; декларативность (СУБД сама определяет порядок выполнения запросов пользователя).

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

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

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

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

Целью данной курсовой работы является проектирование и разработка базы данных интернет-магазина для ООО "Приволжский Механический Завод".

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

· проанализировать данные, описанные в предметной области. Определить таблицы, необходимые для представления предметной области в рамках реляционной модели данных;

· спроектировать логическую структуру базы данных интернет-магазина;

· провести программную реализацию базы данных средствами SQL SERVER для интернет-магазина ООО "Приволжский Механический Завод".



1. Концептуальное проектирование

Концептуальное проектирование - построение смысловой модели предметной области без привязки к какой-либо базе данных [3].

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

Покупателями товаров являются пользователи интернет-магазина.

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

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

Следовательно, можно выделить следующие сущности семантической модели:

· Покупатель;

· Товар;

· Информация о товаре;

· Склад;

· Заказы;

· Подробная информация о заказе;

· Сотрудники;

· Отдел;

· Информация о сотрудниках.

Каждая сущность характеризуется своим набором атрибутов (см. таблицу 1).

Таблица 1. Перечень атрибутов

Сущность	Атрибуты
Покупатель	· Фамилия; · Имя; · Отчество; · Адрес; · Город; · Телефон; · Дата регистрации.
Сотрудники	· Фамилия, · Имя; · Отчество; · Должность; · Зарплата; · Премия; · Семейное положение; · Дата рождения; · Дата приема на работу; · Адрес; · Телефон. · Отдел
Отдел	· Название отдела;
Товар	· Название.
Описание товара.	· Цвет; · Материал; · Текстовое описание.
Складские запасы	· Количество.
Заказы	· Идентификатор покупателя; · Идентификатор сотрудника; · Дата заказа.
Детали заказа	· Количество заказанных позиций; · Идентификатор продукта; · Количество заказанных единиц; · Цена товара; · Итоговая сумма заказа.



2. Логическое проектирование базы данных

2.1 Модель "сущность-связь"

Модель сущность-связь (ER-модель) ? модель данных, позволяющая описывать концептуальные схемы предметной области [4]. ER-модель используется при высокоуровневом (концептуальном) проектировании баз данных. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями.

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

Ниже приведена модель "сущность-связь" интернет-магазина. Она включает в себя 8 сущностей (Покупатель, Сотрудники, Отдел, Товар, Описание Товаров, Складские запасы, Заказы, Детали заказа).

Атрибуты: "Фамилия", "Имя", "Отчество", "Адрес", "Город", "Телефон", "Дата регистрации", принадлежат сущности "Попупатели".

Атрибуты: "Фамилия", "Имя", "Отчество", "Должность", "Зарплата", "Премия", "Семейное положение", "Дата рождения", "Дата приема на работу", "Адрес", "Телефон", "Отдел" принадлежат сущности "Сотрудники".

Атрибуты: "Название отдела" принадлежат сущности "Отдел".

Атрибуты: "Название Товара" принадлежат сущности "Товар".

Атрибуты: "Цвет", "Материал", "Текстовое описание" принадлежат сущности "Описание товара".

Атрибуты: "Количество" принадлежат сущности "Склад".

Атрибуты: "Идентификатор покупателя", "Идентификатор сотрудника", "Дата заказа" принадлежат сущности "Заказы".

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



2.2 Классификация связей

Связь позволяет моделировать отношения между объектами предметной области. Наименование связи должно быть уникально во всей модели.

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

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

Таблица 2. Классификация связей

Сущность А	Сущность В	Тип связи
Отдел	Сотрудник	"Один-к-одному"
Детали заказа	Заказы	"Один-ко-многим"
Покупатель
Сотрудники
Детали товара	Товары	"Один-к-одному"
Склад
Детали заказа	Товары	"Один-ко-многим"

2.3 Определение связей

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

1. Связи таблиц: "Сотрудники" и "Отдел".

2. Таблицы "Сотрудники" и "Отдел" должны быть связаны связью "один к одному" по ключу "ID "из таблицы Сотрудники и ключом "ID" из таблицы "Отдел".

3. Связи таблиц: "Товар" и "Детали товара".

Таблицы "Товар" и "Детали товара" c должны быть связаны связью "один к одному" по ключу "ID" из таблицы "Товар" и ключу "ID" из таблицы "Детали товара".

4. Связи таблиц: "Товары" и "Склад".

Таблицы "Товары" и "Склад" должны быть связаны связью "один к одному" по ключу "ID" из таблицы "Товары" и ключу "ТоварID" из таблицы Склад.

5. Связи таблиц: "Покупатели" и "Заказы".

Таблицы "Покупатели" и "Заказы" должны быть связаны связью "один ко многим" по ключу "ID" из таблицы "Покупатели" и ключу "ПокупателиID" из таблицы "Заказы".

6. Связи таблиц: "Сотрудники" и "Заказы".

Таблицы "Сотрудники" и "Заказы". c должны быть связаны связью "один ко многим" по ключу "ID" из таблицы "Сотрудники" и ключу "СотрудникиID" из таблицы "Заказы".

7. Связи таблиц: Товары и Детали заказа.

Таблицы "Товары" и "Детали заказа" c должны быть связаны связью "один ко многим" по ключу "ID" из таблицы "Товары" и ключу "ТоварID" из таблицы "Детали заказа".

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

Рис. 1. Модель "сущность-связь"



2.4 Нормализация отношений

Нормальная форма ? требование, предъявляемое к структуре таблиц в теории реляционных баз данных для устранения из базы избыточных функциональных зависимостей между атрибутами (полями таблиц) [5].

Каждая таблица в реляционной БД удовлетворяет условию, в соответствии с которым в позиции на пересечении каждой строки и столбца таблицы всегда находится единственное атомарное значение, и никогда не может быть множества таких значений. Любая таблица, удовлетворяющая этому условию, называется нормализованной. Фактически, ненормализованные таблицы, т.е. таблицы, содержащие повторяющиеся группы, даже не допускаются в реляционной БД. [6]

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

Теперь в дополнение к 1НФ можно определить дальнейшие уровни нормализации - вторую нормальную форму (2НФ), третью нормальную форму (3НФ) и т.д.

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

Таким образом, каждая нормальная форма является в некотором смысле более ограниченной, но и более желательной, чем предшествующая. Это связано с тем, что "(N+1)-я нормальная форма" не обладает некоторыми непривлекательными особенностями, свойственным "N-й нормальной форме". Общий смысл дополнительного условия, налагаемого на (N+1)-ю нормальную форму по отношению к N-й нормальной форме, состоит в исключении этих непривлекательных особенностей. В п. 4.3 мы выявляли непривлекательные особенности таблицы рис. 4.2 и для их исключения выполняли "интуитивную нормализацию". [7]

Ключевым свойством 1НФ является атомарность: значение в ячейке таблицы может быть только одно, например, если столбец называется ФИО, то и значение "Иванов Иван Иванович" должно быть только одно.

Таблица 3. Пример неправильной 1НФ

Код	Сотрудник	Отдел	Дата рождения
1	Перминов Алексей Владимирович	Отдел продаж, Отдел поддержки.	08.12.1990
2	Савиных Олег Анатольевич	Отдел продаж	27.01.1989

В 1НФ атрибуты не должны иметь несколько значений, например, если в столбце "Отдел", прописано два значение "Отдел поддержки" и "Отдел поддержки" то необходимо разбить эти значения.

Таблица 4. Пример неправильной 1НФ

Код	Сотрудник	Отдел	Дата рождения
1	Перминов Алексей Владимирович	Отдел продаж	08.12.1990
2	Туманов Георгий Викторович	Отдел продаж	27.01.1989
3	Перминов Алексей Владимирович	Отдел поддержки.	08.12.1990

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

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

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

Таблица 5. Пример неправильной 2НФ

ID	Сотрудник	Товар	Клиент	Отдел	Тел. клиент
1	Перминов	Пивоварня Золингер	Савиных	Отдел продаж	22-43-77
2	Туманов	Коптильня ханхи	Вараксин	Отдел продаж	23-44-33
3	Суворов	Коптильня дымка	Жарков	Отдел продаж	11-33-35

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

Создаём таблицы на основе функциональных связей:

Таблица 6. Таблица-справочников "Продажи" во 2НФ

ID	Товар	Сотрудник	Клиент
1	Пивоварня Золингер	2	2
2	Коптильня ханхи	1	3
3	Коптильня дымка	3	1

Таблица 7. Таблица-справочников "Сотрудники" во 2НФ

ID	Сотрудник	Д.Р. сотрудника
1	Перминов	08.12.1990
2	Туманов	27.01.1989
3	Суворов	17.03.1991



Таблица 8. Таблица-справочников "Сотрудники" во 2НФ

ID	Клиент	Телефон клиента
1	Савиных	22-43-77
2	Вараксин	23-44-33
3	Жарков	11-33-35

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

Переменная отношения находится в третьей нормальной форме тогда и только тогда, когда она находится во 2НФ, и отсутствуют транзитивные функциональные зависимости неключевых атрибутов от ключевых [8].

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

Таблица 9. Пример таблицы не находящейся в 3НФ

Модель	Материал
Коптильня дымка	Нержавеющая сталь
Коптильня ханхи	Чёрная сталь
Пивоварня Золингер	Чёрная сталь

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



Таблица 10. Таблицы "Материал" находящейся в 3НФ

ID	Поставщик
1	Нержавеющая сталь
2	Чёрная сталь

Таблица 11. Пример таблицы не находящейся в 3НФ

Товар	Материал
Пивоварня Золингер	2
Коптильня ханхи	2
Коптильня дымка	1

Для правильного функционирования базы данных все таблицы необходимо преобразовать во 2НФ или 3НФ.



3. Даталогическое проектирование

3.1 Состав таблиц базы банных

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

Даталогическое проектирование ? определение особенностей хранения данных, методов доступа и т.д.[9]

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

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

Выбранная СУБД - MS SQL Server.

Определяем типы данных для атрибутов сущностей. Для всех уникальных полей - тип integer. Для текстовых полей (фамилия, Имя, Отчество и т.д.) - char с указанием количества символов.

3.2 Средства поддержания целостности

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

1. Таблица: Покупатели.

Содержит поля:

· ID. Идентификатор покупателя, устанавливаем автогенерацию, без NULL значений. Тип данных - int,

· Фамилия. Строковые данные переменной длины c поддержкой NULL значений. Тип данных - nvarchar(20).

· Имя. Строковые данные переменной длины c поддержкой NULL значений. Тип данных - nvarchar(20).

· Отчество. Строковые данные переменной длины c поддержкой NULL значений. Тип данных - nvarchar(20).

· Адрес. Строковые данные переменной длины c поддержкой NULL значений. Тип данных - nvarchar(50).

· Город. Строковые данные переменной длины c поддержкой NULL значений. Тип данных - nvarchar(20).

· Телефон. Строковые данные длины из 12 символов c поддержкой NULL значений. Тип данных -char (12).

· Дата_регистрации. Укажем значения по умолчанию, т.е. дату на момент регистрации. Тип данных -date.

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

2. Таблица: Сотрудники.

Содержит поля:

· ID. Идентификатор сотрудника, устанавливаем автогенерацию, без NULL значений. Тип данных - int.

· Фамилия. Строковые данные переменной длины без поддержки NULL значений. Тип данных - nvarchar (20).

· Имя. Строковые данные переменной длины c поддержкой NULL значений. Тип данных - nvarchar (20).

· Отчество. Строковые данные переменной длины без поддержки NULL значений. Тип данных - nvarchar (20).

· Зарплата. Данные без поддержки NULL значений. Тип данных - money.

· ОтделID. Идентификатор отдела, без NULL значений. Тип данных - int.

· Премия. Данные с поддержкой NULL значений. Тип данных - money.

· Семейное положение. Строковые данные переменной длины без поддержки NULL значений. Тип данных - nvarchar (10).

· Дата рождения. Данные без поддержки NULL значений. Тип данных -date.

· Адрес. Строковые данные переменной длины c поддержкой NULL значений. Тип данных - nvarchar(50).

· Телефон. Строковые данные длины из 12 символов c поддержкой NULL значений. Тип данных -char (12).

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

· Ограничения согласно которому премия не может равняться и быть больше чем зарплата

· Ограничение на корректность ввода номера телефона и ограничение, согласно которому в интернет-магазин могут устраиваться кандидаты в возрасте от 18 до 50 лет.

· Ограничение на ввод данных в столбец "Семейное положение" (ввод: Женат, Не женат, Замужем, Не замужем)

3. Таблица: Отдел.

Содержит поля:

· ID. Идентификатор отдела, без NULL значений. Тип данных - int.

· Название отдела. Строковые данные переменной длины c поддержкой NULL значений. Тип данных - nvarchar (20).

4. Таблица: Товары.

Содержит поля:

· ID. Идентификатор продукта, без поддержки NULL значений. Тип данных - int.

· Название продукта. Строковые данные переменной длины без поддержки NULL значений. Тип данных - nvarchar(50).

5. Таблица: Детали товара.

Содержит поля:

· ID. Идентификатор продукта, без поддержки NULL значений. Тип данных - int.

· Цвет. Строковые данные фиксированной длины с поддержкой NULL значений. Тип данных - nchar(20).

· Материал. Строковые данные фиксированной длины с поддержкой NULL значений. Тип данных - nchar(20).

· Текстовое описание. Строковые данные максимальной длины с поддержкой NULL значений. Тип данных - nvarchar (max).

6. Таблица склад.

Содержит поля:

· ТоварID. Идентификатор продукта, без поддержки NULL значений. Тип данных - int.

· Остаток, данные без поддержки NULL значений. Тип данных - int.

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

7. Таблица Заказы.

Содержит поля:

· ID. Идентификатор заказа, устанавливаем автогенерацию, без NULL значений. Тип данных - int.

· СотрудникID. Идентификатор покупателя, с поддержкой NULL значений. Тип данных - int,

· ПокупательID. Идентификатор сотрудника, с поддержкой NULL значений. Тип данных - int,

· Дата оформления. Укажем значения по умолчанию, т.е. дату на момент оформления покупки. Тип данных - date.

8. Таблица Детали заказа.

Содержит поля:

· ЗаказID. Идентификатор заказа, данные без поддержки NULL значений. Тип данных - int.

· Количество позиций. Количество позиций, данные без поддержки NULL значений. Тип данных - int.

· ТоварID. Идентификатор продукта, с поддержкой NULL значений. Тип данных - int,

· Количество проданного, данные без поддержки NULL значений. Тип данных - int.

· Цена. Данные без поддержки NULL значений. Тип данных - money.

· Стоимость заказа. Вычислимое столбе по форуме (CONVERT([money],[Количество проданного]*[Цена])) Для подсчета общей суммы заказа.

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

Рис. 2. Физическая модель базы данных

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



4. Программная реализация базы данных интернет магазина для ООО "ПМЗ" средствами SQL SERVER

4.1 Создание базы данных

Создадим новую базу данных при помощи универсальной среды для доступа, настройки и администрирования всех компонентов MS SQL Server. SQL Server Management Studio".

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

CREATE DATABASE InternetShopPMZ

COLLATE Cyrillic_General_CI_AS

GO

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

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

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

Создадим таблицы при помощи оператора CREATE TABLE языка Transact_SQL. CREATE TABLE создает новую таблицу, ее столбцы и ограничения целостности в базе данных.

1. Создание таблицы: "Покупатели".

CREATE TABLE ПОКУПАТЕЛИ

( ID int NOT NULL IDENTITY,

Фамилия nvarchar(20) NULL,

Имя nvarchar(20) NULL,

Отчество nvarchar(20) NULL,

Адрес nvarchar(50) NULL,

Город nvarchar(20) NULL,

Телефон char(12) NULL,

Дата_регистрации date DEFAULT GETDATE(), )

GO

Эта команда создаёт таблицу "Покупатели" и определяет значения полей: ID, Фамилия, Имя, Отчество, Адрес, Город, Телефон, Дата_регистрации.

2. Создание таблицы: "СОТРУДНИКИ".

CREATE TABLE СОТРУДНИКИ

( ID int NOT NULL IDENTITY,

ОтделID int NOT NULL,

Фамилия nvarchar(20) NOT NULL,

Имя nvarchar(20) NULL,

Отчество nvarchar(20) NOT NULL,

Должность nvarchar(25) NOT NULL,

Зарплата money NOT NULL,

Ппремия money NULL,

Датаприеманаработу date DEFAULT GETDATE(),

Семейноеположение varchar(10) NOT NULL,

Датарождения date NOT NULL,

Адрес nvarchar(50) NOT NULL,

Телефон char(12) NOT NULL )

GO

Эта команда создаёт таблицу "Сотрудники" и определяет значения полей: ID, ОтделID, Фамилия, Имя, Отчество, Должность, Зарплата, Премия, Датаприеманаработу, Семейноеположение, Датарождения, Адрес, Телефон.

3. Создание таблицы: "Отдел".

CREATE TABLE ОТДЕЛ

( ID int NOT NULL,

Названиеотдела nvarchar(20) NOT NULL,)

GO

Эта команда создаёт таблицу "Отдел" и определяет значения полей: ID, Названиеотдела.

4. Создание таблицы: "Товары".

CREATE TABLE ТОВАРЫ

( ID int NOT NULL IDENTITY,

НАЗВАНИЕ nvarchar(50) NOT NULL )

GO

Эта команда создаёт таблицу "Товары" и определяет значения полей: ID, Название.

5. Создание таблицы: "Детали товара".

CREATE TABLE ДЕТАЛИТОВАРА

( ID int NOT NULL,

Цвет nchar(20) NULL,

Материал nchar(20) NULL,

Текстовоеописание nvarchar(max) NULL )

GO

Эта команда создаёт таблицу "Деталитовара" и определяет значения полей: ID, Цвет, Материал.

6. Создание таблицы: "Склад".

CREATE TABLE СКЛАД

( ТоварID int NOT NULL,

Количество int DEFAULT 0)

GO

Эта команда создаёт таблицу "склад" и определяет значения полей: ID, Название.

7. Создание таблицы: "Заказы".

CREATE TABLE ЗАКАЗЫ

(ID int NOT NULL IDENTITY,

ПокупательID int NULL,

СотрудникID int NULL,

Датаоформления date DEFAULT GETDATE() )

GO

Эта команда создаёт таблицу "Заказы" и определяет значения полей: ID, ПокупательID, СотрудникID, Датаоформления.

8. Создание таблицы: "Детали заказы".

CREATE TABLE ДЕТАЛИЗАКАЗА

( ЗаказID int NOT NULL,

Количествопозиций int NOT NULL,

ТоварID int NULL,

Количествопродонного int NOT NULL,

Цена money NOT NULL,

Стоимостьзаказа AS CONVERT(money, Количествопродонного*Цена) )

GO

Эта команда создаёт таблицу "Детали заказа" с полями: ЗаказID, Количествопозиций, ТоварID, Количествопродонного, Цена, Стоимостьзаказа.

4.2 Установка связей между таблицами

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

1. Создание первичного ключа в таблице: "Покупатели".

ALTER TABLE ПОКУПАТЕЛИ ADD

CONSTRAINT Первичный_ключ_покупатели PRIMARY KEY(ID)

GO

2. Создание первичного ключа в таблице: "Сотрудники".

ALTER TABLE СОТРУДНИКИ ADD

CONSTRAINT Первичный_ключ_сотрудники PRIMARY KEY(ID)

GO

3. Создание уникального ключа в таблице: "Отдел".

ALTER TABLE ОТДЕЛ ADD

CONSTRAINT Уникальный_ключ_отдел UNIQUE(ID)

GO

4. Создание внешнего ключа в таблице: "СОТРУДНИКИ".

ALTER TABLE СОТРУДНИКИ ADD

CONSTRAINT внешний_ключ_сотрудники_к_отдел_ FOREIGN KEY (ОтделID)

REFERENCES ОТДЕЛ(ID)

ON DELETE CASCADE

GO

5. Создание первичного ключа в таблице: "Товары".

ALTER TABLE ТОВАРЫ ADD

CONSTRAINT первичный_ключ_товары PRIMARY KEY (ID)

GO

6. Создание уникального ключа в таблице: "Детали товара".

ALTER TABLE ДЕТАЛИТОВАРА ADD

CONSTRAINT Уникальный_ключ_деталитовара UNIQUE(ID)

GO

7. Создание внешнего ключа в таблице: "Детали товара".

ALTER TABLE ДЕТАЛИТОВАРА ADD

CONSTRAINT внешнийключ_деталитовара_к_товары FOREIGN KEY (ID)

REFERENCES ТОВАРЫ(ID)

ON DELETE CASCADE

GO

8. Создание уникального ключа в таблице: "Склад".

ALTER TABLE СКЛАД ADD

CONSTRAINT Уникальный_ключ_склад UNIQUE(ТоварID)

GO

9. Создание внешнего ключа в таблице: "Склад".

ALTER TABLE СКЛАД ADD

CONSTRAINT внешний_ключ_склад_к_товары FOREIGN KEY (ТоварID)

REFERENCES ТОВАРЫ(ID)

ON DELETE CASCADE

GO

10. Создание первичного ключа в таблице: "Заказы".

ALTER TABLE ЗАКАЗЫ ADD

CONSTRAINT первичный_ключ_заказы PRIMARY KEY (ID)

GO

11. Создание внешнего ключа в таблице: "Заказы".

ALTER TABLE ЗАКАЗЫ ADD CONSTRAINT

внешний_ключ_заказы_к_покупателям FOREIGN KEY(ПокупательID)

REFERENCES ПОКУПАТЕЛИ(ID)

ON DELETE SET NULL

GO

12. Создание внешнего ключа в таблице: "Заказы".

ALTER TABLE ЗАКАЗЫ ADD CONSTRAINT

Внешний_ключ_заказы_к_сотрудники FOREIGN KEY(СотрудникID)

REFERENCES СОТРУДНИКИ(ID)

ON DELETE SET NULL

GO

13. Создание первичного ключа в таблице: "Детали заказа".

ALTER TABLE ДЕТАЛИЗАКАЗА ADD CONSTRAINT

первичныйключ_детализаказа PRIMARY KEY

(ЗаказID,Количествопозиций)

GO

14. Создание внешнего ключа в таблице: "Детали заказа".

ALTER TABLE ДЕТАЛИЗАКАЗА ADD CONSTRAINT

внешнийключ_детализаказа_к_заказам FOREIGN KEY(ЗаказID)

REFERENCES ЗАКАЗЫ(ID)

ON DELETE CASCADE

GO

15. Создание внешнего ключа в таблице: "Детали заказа".

ALTER TABLE ДЕТАЛИЗАКАЗА ADD CONSTRAINT

внешний_ключ_детализаказа_к_товары FOREIGN KEY(ТоварID)

REFERENCES ТОВАРЫ(ID)

ON DELETE SET NULL

GO

4.3 Создание пользовательских ограничений

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

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

1. Создание ограничение на корректность ввода номера телефона для таблиц: "Сотрудники", "Покупатели":

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_телефон

CHECK (ТЕЛЕФОН LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

GO

ALTER TABLE ПОКУПАТЕЛИ

ADD CONSTRAINT ограничение_покупатели_телефон

CHECK (ТЕЛЕФОН LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

GO

Эта команда позволит вводить только корректные номера телефона в таблицах таблиц: "Сотрудники", "Покупатели".

2. Создание ограничения, согласно которому в интернет-магазин могут устраиваться кандидаты в возрасте от 18 до 50 лет для таблицы: "Сотрудники":

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_деньрождение

CHECK (Датарождения BETWEEN DATEADD(YEAR, -50, GETDATE()) AND DATEADD(YEAR, -18, GETDATE()))

Эта команда установит правила трудоустройства сотрудников по возврату на для таблицы: "Сотрудники".

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

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_семейноеположение

CHECK (Семейноеположение IN ('Женат','Не женат', 'Замужем','Не замужем'))

GO

Эта команда установит правила выбора статусов столбца "Семейное положение" для таблицы: "Сотрудники".

4. Создание ограничения, согласно которому премия не может быть больше чем зарплата для таблицы: "Сотрудники":

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_премия

CHECK (Премия < Зарплата)

GO

Эта команда установит правило контроля размера премии для таблицы: "Сотрудники".

5. Создание ограничения, согласно которому остаток товара на складе не может быть отрицательным для таблицы: "Склад":

ALTER TABLE СКЛАД

ADD CONSTRAINT CN_Stocks_Qty

CHECK (Количество>= 0)

GO

Эта команда установит правило контроля остатка товара для таблицы: "Склад".

4.4 Заполнение базы данных тестовыми данными

Для корректной работы базы данных необходимо заполнить её тестовыми данными.

1. Заполнение таблицы "Сотрудники" тестовыми данными.

INSERT СОТРУДНИКИ

(ОтделID, Фамилия, Имя, Отчество, Должность, Зарплата, Премия, Датаприеманаработу, Семейноеположение, Датарождения, Адрес, Телефон)

VALUES

(1,'Перминов', 'Алексей', 'Владимирович', 'Менеджер по продажам', 20000, 5000, GETDATE(),'Женат', '01/10/1990','Зянкина 9', '(953)1416433'),

(2,'Туманов', 'Георгий', 'Викторович', 'Менеджер по продажам', 20000, 5000, GETDATE(),'Женат', '04/10/1990','Воровского 29', '(912)4533554'),

(1,'Кочуров', 'Дмитрий', 'Николаевич', 'Системный администратор', 26000, 7000, GETDATE(),'Женат', '04/10/1990','Пугачева 29', '(951)7433545')GO

2. Заполнение таблицы "Покупатели" тестовыми данными.

INSERT ПОКУПАТЕЛИ

(Фамилия, Имя, Отчество, Адрес, Город, Телефон, Дата_регистрации)

VALUES

('Савиных','Викторович','Павлович','Труда 21а, 137','Киров','(912)3569385',DATEADD(DAY, -85, GETDATE())),

('Васильева','Алефтина','Генадьевна','Мопра 3а, 12','Пермь','(922)4622225',DATEADD(DAY, -35, GETDATE()))

GO

3. Заполнение таблицы "Товары" тестовыми данными.

INSERT ТОВАРЫ

(Название)

VALUES

('Коптильня дымка'), ('Коптильня ханхи'), ('Пивоварня Золингер'), ('Шампура столичные'), ('Дымогенератор дымка')

GO

4. Заполнение таблицы "Детали товара" тестовыми данными.

INSERT ДЕТАЛИТОВАРА

(ID, Цвет, материал, текстовоеописание)

VALUES

(1, 'Серый', 'Нержавеющая сталь','Коптильня преднозначена для горячего копчения'),

(2, 'Черный', 'Черная сталь','Коптильня преднозначена для горячего копчения'),

(3, 'Белый', 'Черная сталь','Пивоварня преднозначена для изготовления пивного сусла'),

(4, 'Стальной', 'Черная сталь','Шампура преднозначены для любителей отдыха на природе'),

(5, 'Стальной', 'Черная сталь','Дымогенератор преднозначен для производства дыма при холодном копчении для ')

GO

5. Заполнение таблицы "Склад" тестовыми данными.

INSERT СКЛАД

(ТоварID, Количество)

VALUES

(1, 20), (2, 10), (3, 7), (4, 8), (5, 9)

GO

6. Заполнение таблицы "Заказы" тестовыми данными.

INSERT ЗАКАЗЫ

(СотрудникID, ПокупательID, Датаоформления)

VALUES

(22,2, DATEADD(DAY, -85, GETDATE())), (24,1, DATEADD(DAY, -85, GETDATE()))

GO

7. Заполнение таблицы "Детали заказа" тестовыми данными.

INSERT ДЕТАЛИЗАКАЗА

(ЗаказID, Количествопозиций, ТоварID, Количествопродонного, Цена)

VALUES

(4,2,1,1,295), (5,1,2,1,445)

GO

8. Заполнение таблицы "Отдел" тестовыми данными.

INSERT ОТДЕЛ

(ID, Названиеотдела)

VALUES

(1, 'ОТДЕЛ ПРОДАЖ'), (2, 'ОТДЕЛ ПОДДЕРЖКИ')

GO

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

Выполнение команды на вывод таблиц: "СОТРУДНИКИ" "Покупатели", "Склад", "Отдел", "Заказы", "Детализаказа", "Товары", "Деталитовара".

SELECT * FROM СОТРУДНИКИ

SELECT * FROM ПОКУПАТЕЛИ

SELECT * FROM СКЛАД

SELECT * FROM ОТДЕЛ

SELECT * FROM ЗАКАЗЫ

SELECT * FROM ДЕТАЛИЗАКАЗА

SELECT * FROM ТОВАРЫ

SELECT * FROM ДЕТАЛИТОВАРА

Эта команда позволит вывести на экран пользователя все созданные таблицы с заполненными данными (см. рис. 3).

Рис.3. Таблицы с заполненными данными

4.5 Создание логических триггеров

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

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

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

CREATE TRIGGER УДАЛЕНИЯПРОДУКТА

ON ТОВАРЫ

INSTEAD OF DELETE

AS

IF @@ROWCOUNT = 0

RETURN

SET NOCOUNT ON

IF EXISTS (SELECT 1 FROM ДЕТАЛИЗАКАЗА od

JOIN deleted d

ON od.ТоварID = d.ID)

RAISERROR('Товар не может быть удален т.к. стоит в заказах', 10, 1)

ELSE IF EXISTS (SELECT 1 FROM СКЛАД s

JOIN deleted d

ON s.ТоварID = d.ID

WHERE s.Количество <> 0)

RAISERROR('Товар не может быть удален т.к. есть остаток на складе', 10, 2)

ELSE

DELETE ТОВАР WHERE ID IN (SELECT ID FROM deleted)

GO

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

· удаляемый товар отсутствует в заказах;

· остаток товара на складе нулевой.



4.6 Создание запросов

Запросы ? это объект базы данных, который служит для извлечения данных из таблиц и предоставления их пользователю в удобном виде. Особенность запросов состоит в том, что они черпают данные из базовых таблиц и создают на их основе временную таблицу. Применение запросов позволяет избежать дублирования данных в таблицах и обеспечивает максимальную гибкость при поиске и отображении данных в базе данных [10].

1. Создание запроса, с помощью которого можно вывести информацию о заказах.

SELECT c.Фамилия +' '+ c.Имя +' '+c.Отчество AS 'Заказы',

o.СотрудникID Сотрудники, Датаоформления, p.НАЗВАНИЕ, pd.Текстовоеописание, od.Цена

FROM Покупатели c INNER JOIN Заказы o

ON c.ID = o.ПокупательID

INNER JOIN Детализаказа od

ON o.ID = od.ЗаказID

INNER JOIN ТОВАРЫ p

ON od.ТоварID = p.ID

INNER JOIN Деталитовара pd

ON p.ID = pd.ID

WHERE o.СотрудникID is NULL;

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

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

SELECT Датаоформления , c.Имя +' '+ c.Фамилия +' '+ c.Отчество AS Покупатели,

p.Название, pd.ТоварID, od.Количествопродонного, od.Цена

FROM Покупатели c

INNER JOIN ЗАКАЗЫ o

ON c.ID = o.ПокупательID

INNER JOIN Детализаказа od

ON o.ID = od.ЗаказID

INNER JOIN ТОВАРЫ p

ON od.ТоварID = p.ID

INNER JOIN ДЕТАЛИЗАКАЗА pd

ON p.ID = pd.ЗаказID

WHERE Датаоформления BETWEEN DATEADD(month, -2, GETDATE()) AND GETDATE()

ORDER BY Датаоформления;

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

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

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

1. Создание представления, с помощью которого можно вывести данные о товаре.

CREATE VIEW Представлениеописаниетотовара

AS

SELECT p.ID, НАЗВАНИЕ, Цвет, Материал, Текстовоеописание

FROM ТОВАРЫ p

LEFT JOIN ДЕТАЛИТОВАРА pd

ON p.ID = pd.ID

GO

SELECT * FROM Представление_описание_тотовара

GO

Эта команда создаст представление, которое выводит информацию об атрибутах сущности "Товары": ID, название, цвет, описание товара.

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

CREATE VIEW представление_остатки

AS

SELECT p.ID, p.НАЗВАНИЕ, s.Количество Остаток,

(SELECT SUM(od.Количествопродонного) FROM ДЕТАЛИЗАКАЗА od

WHERE od.ТоварID = p.ID) Продано

FROM ТОВАРЫ p

LEFT JOIN СКЛАД s

ON p.ID = s.ТоварID

GO

SELECT * FROM представление_остатки

GO

Эта команда создаст представление, которое выводит информацию о продажах и остатках товара: ID, название, остаток товара на складе.

Создание процедур

Хранимая процедура ? объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам [12].

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

CREATE PROC процедурапоиска_покупателя

@Фамилия nvarchar(20) = '%',

@Город nvarchar(20) = '%'

AS

SET NOCOUNT ON

SELECT c.Фамилия + ' ' + c.Имя + ' ' + c.Отчество Покупатели,

o.Датаоформления,

p.НАЗВАНИЕ, od.Количествопродонного, od.Цена

FROM ПОКУПАТЕЛИ c

JOIN ЗАКАЗЫ o

ON c.ID = o.ПокупательID AND c.Город LIKE @Город

JOIN ДЕТАЛИЗАКАЗА od

ON o.ID = od.ЗаказID

JOIN ТОВАРЫ p

ON od.ТоварID = p.ID

WHERE c.Фамилия LIKE @Фамилия

GO

EXEC процедурапоиска_покупателя 'Кр%'

EXEC процедурапоиска_покупателя @Город = 'Л%'

GO

Эта команда создаст хранимую процедуру, с помощью которой можно производить поиск покупателя по фамилии и/или городу.

В практической части работы было произведено создание базы данных и ее объектов средствами программной среды SQL Server. Приведены примеры запросов для создания таблиц, их связей, хранимых процедур, триггеров и представлений. Таблицы были наполнены необходимыми начальными данными. Кроме того, здесь показаны запросы для работы с базой.

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



Заключение

Темой данной курсовой работы являлось создание базы данных интернет-магазина для ООО "Приволжской Механический Завод".

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

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

Практическая реализация спроектированной базы данных была произведена средствами интегрированной среды SQL Server Management Studio, которая сочетает в себе обширную группу графических инструментов с рядом отличных редакторов сценариев для обеспечения доступа, настройки, администрирования, разработки всех компонентов SQL Server и управления ими.

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

Итогом выполненной работы является созданная и апробированная база данных интернет-магазина для ООО "Приволжской Механический Завод.

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

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



Библиографический список

1. Грофф, Д.Р. Энциклопедия SQL / Д.Р. Грофф, П.Н. Вайнберг. - СПб.: Изд-во "Питер", 2003. - 896 с.

2. Дейт, К.Д. Введение в системы баз данных / К.Д. Дейт. - М.: Изд-во "Вильямс", 2003. - 1328 с.

3. Кириллов В.В. Введение в реляционные базы данных / В.В. Кириллов, Г.Ю. Громов. - СПб.: Изд-во "БХВ-Петербург", 2009. - 464 с.

4. Когаловский, М.Р. Энциклопедия технологий баз данных / М.Р. Когаловский. - М.: Финансы и статистика, 2002. - 800 с.

5. Корнеев, В.В. Базы данных. Интеллектуальная обработка информации / В.В. Корнеев, А. Гареев, С. Васютин, В. Райх. - М.: Изд-во "Нолидж", 2001. - 496 с.

6. Крёнке, Д.М. Теория и практика построения баз данных / Д.М. Крёнке. - СПб.: Изд-во "Питер", 2003. - 800 с.

7. Хомоненко, А.Д. Базы данных: Учебник для высших учебных заведений / А.Д. Хомоненко, В. Цыганков, В. Мальцев. - СПб.: Изд-во "Корона", 2002. - 672 с.

8. Сеть разработчиков Microsoft [Электронный ресурс]. - Альбукерке: Майкрософт, 1975-. - Режим доступа: http://msdn.microsoft.com, свободный. - Загл. с экрана.

9. ЦИТ Форум [Электронный ресурс]. - М.: Центр информационных технологий, 1992 -. - Режим доступа: http://citforum.ru, свободный. - Загл. с экрана.

10. Википедия [Электронный ресурс]. - Ашбурн: Википедия, 2001 -. - Режим доступа: https://wikipedia.org, свободный. - Загл. с экрана.

11. Elibrary.ru: научная электронная библиотека [Электронный ресурс]. - М.: Интра- Плюс, 1997 -. - Режим доступа: http://www.elibrary.ru, свободный. - Загл. с экрана.



Приложение

Листинг кода базы данных

-- Создание базы

CREATE DATABASE интернет_магазин5

COLLATE Cyrillic_General_CI_AS

GO

-- Код создания таблиц

CREATE TABLE ПОКУПАТЕЛИ

( ID int NOT NULL IDENTITY,

Фамилия nvarchar(20) NULL,

Имя nvarchar(20) NULL,

Отчество nvarchar(20) NULL,

Адрес nvarchar(50) NULL,

Город nvarchar(20) NULL,

Телефон char(12) NULL,

Дата_регистрации date DEFAULT GETDATE(),)

GO

CREATE TABLE СОТРУДНИКИ

( ID int NOT NULL IDENTITY,

ОтделID int NOT NULL,

Фамилия nvarchar(20) NOT NULL,

Имя nvarchar(20) NULL,

Отчество nvarchar(20) NOT NULL,

Должность nvarchar(25) NOT NULL,

Зарплата money NOT NULL,

Премия money NULL,

Датаприеманаработу date NOT NULL,

Семейноеположение varchar(10) NOT NULL,

Датарождения date NOT NULL,

Адрес nvarchar(50) NOT NULL,

Телефон char(12) NOT NULL)

GO

CREATE TABLE ОТДЕЛ

(

ID int NOT NULL,

Названиеотдела nvarchar(20) NOT NULL,

)

GO

CREATE TABLE ТОВАРЫ

(

ID int NOT NULL IDENTITY,

НАЗВАНИЕ nvarchar(50) NOT NULL

)

GO

CREATE TABLE ДЕТАЛИТОВАРА

(

ID int NOT NULL,

Цвет nchar(20) NULL,

Материал nchar(20) NULL,

Текстовоеописание nvarchar(max) NULL

)

GO

CREATE TABLE СКЛАД

(

ТоварID int NOT NULL,

Количество int DEFAULT 0

)

GO

CREATE TABLE ЗАКАЗЫ

(

ID int NOT NULL IDENTITY,

ПокупательID int NULL,

СотрудникID int NULL,

Датаоформления date DEFAULT GETDATE()

)

GO

CREATE TABLE ДЕТАЛИЗАКАЗА

(

ЗаказID int NOT NULL,

Количествопозиций int NOT NULL,

ТоварID int NULL,

Количествопродонного int NOT NULL,

Цена money NOT NULL,

Стоимостьзаказа AS CONVERT(money, Количествопродонного*Цена)

)

GO

-- Код установки связи между таблицами

ALTER TABLE ПОКУПАТЕЛИ ADD

CONSTRAINT Первичный_ключ_покупатели PRIMARY KEY(ID)

GO

ALTER TABLE СОТРУДНИКИ ADD

CONSTRAINT Первичный_ключ_сотрудники PRIMARY KEY(ID)

GO

ALTER TABLE ОТДЕЛ ADD

CONSTRAINT Уникальный_ключ_отдел UNIQUE(ID)

GO

ALTER TABLE СОТРУДНИКИ ADD

CONSTRAINT внешний_ключ_отделы_к_сотрудникам FOREIGN KEY (ОтделID)

REFERENCES ОТДЕЛ(ID)

ON DELETE CASCADE

GO

ALTER TABLE ТОВАРЫ ADD

CONSTRAINT первичный_ключ_товары PRIMARY KEY (ID)

GO

ALTER TABLE ДЕТАЛИТОВАРА ADD

CONSTRAINT Уникальный_ключ_деталитовара UNIQUE(ID)

GO

ALTER TABLE ДЕТАЛИТОВАРА ADD

CONSTRAINT внешнийключ_деталитовара_к_Товарам FOREIGN KEY (ID)

REFERENCES ТОВАРЫ(ID)

ON DELETE CASCADE

GO

ALTER TABLE СКЛАД ADD

CONSTRAINT Уникальный_ключ_склад UNIQUE(ТоварID)

GO

ALTER TABLE СКЛАД ADD

CONSTRAINT внешний_ключ_склад_к_товарам FOREIGN KEY (ТоварID)

REFERENCES ТОВАРЫ(ID)

ON DELETE CASCADE

GO

ALTER TABLE ЗАКАЗЫ ADD

CONSTRAINT первичный_ключ_заказы PRIMARY KEY (ID)

GO

ALTER TABLE ЗАКАЗЫ ADD CONSTRAINT

внешнийключ_заказы_к_покупателям FOREIGN KEY(ПокупательID)

REFERENCES ПОКУПАТЕЛИ(ID)

ON DELETE SET NULL

GO

ALTER TABLE ЗАКАЗЫ ADD CONSTRAINT

внешний_ключ_заказы_к_сотрудники FOREIGN KEY(СотрудникID)

REFERENCES СОТРУДНИКИ(ID)

ON DELETE SET NULL

GO

ALTER TABLE ДЕТАЛИЗАКАЗА ADD CONSTRAINT

первичныйключ_детализаказа PRIMARY KEY

(ЗаказID,Количествопозиций)

GO

ALTER TABLE ДЕТАЛИЗАКАЗА ADD CONSTRAINT

внешнийключ_детализаказа_к_заказам FOREIGN KEY(ЗаказID)

REFERENCES ЗАКАЗЫ(ID)

ON DELETE CASCADE

GO

ALTER TABLE ДЕТАЛИЗАКАЗА ADD CONSTRAINT

внешний_ключ_детализаказа_к_товары FOREIGN KEY(ТоварID)

REFERENCES ТОВАРЫ(ID)

ON DELETE SET NULL

GO

--. Код создания пользовательских ограничений

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_телефон

CHECK (ТЕЛЕФОН LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') GO

ALTER TABLE ПОКУПАТЕЛИ

ADD CONSTRAINT ограничение_покупатели_телефон

CHECK (ТЕЛЕФОН LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') GO

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_деньрождение

CHECK (Датарождения BETWEEN DATEADD(YEAR, -50, GETDATE()) AND DATEADD(YEAR, -18, GETDATE()))

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_Семейноеположение

CHECK (Семейноеположение IN ('Женат','Не женат', 'Замужем','Не замужем'))

GO

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_Премия

CHECK (Премия < Зарплата)

GO

ALTER TABLE СКЛАД

ADD CONSTRAINT CN_Stocks_Qty

CHECK (Количество>= 0)

GO

-- Код наполнения таблиц текстовыми данными

INSERT ОТДЕЛ

(ID, Названиеотдела)

VALUES

(1, 'ОТДЕЛ ПРОДАЖ'),

(2, 'ОТДЕЛ ПОДДЕРЖКИ')

GO

INSERT СОТРУДНИКИ

(ОтделID, Фамилия, Имя, Отчество, Должность, Зарплата, Премия, Датаприеманаработу, Семейноеположение, Датарождения, Адрес, Телефон)

VALUES

(1,'Перминов', 'Алексей', 'Владимирович', 'Менеджер по продажам', 20000, 5000, GETDATE(),'Женат', '01/10/1990','Зянкина 9', '(953)1416433'),

(2,'Туманов', 'Георгий', 'Викторович', 'Менеджер по продажам', 20000, 5000, GETDATE(),'Женат', '04/10/1990','Воровского 29', '(912)4533554'),

(1,'Кочуров', 'Дмитрий', 'Николаевич', 'Системный администратор', 26000, 7000, GETDATE(),'Женат', '04/10/1990','Пугачева 29', '(951)7433545')

GO

INSERT ПОКУПАТЕЛИ

(Фамилия, Имя, Отчество, Адрес, Город, Телефон, Дата_регистрации)

VALUES

('Савиных','Викторович','Павлович','Труда 21а, 137','Киров','(912)3569385',DATEADD(DAY, -85, GETDATE())),

('Васильева','Алефтина','Генадьевна','Мопра 3а, 12','Пермь','(922)4622225',DATEADD(DAY, -35, GETDATE()))

GO

INSERT ТОВАРЫ

(Название)

VALUES

('Коптильня дымка'),

('Коптильня ханхи'),

('Пивоварня Золингер'),

('Шампура столичные'),

('Дымогенератор дымка')

GO

INSERT ДЕТАЛИТОВАРА

(ID, Цвет, материал, текстовоеописание)

VALUES

(1, 'Серый', 'Нержавеющая сталь','Коптильня преднозначена для горячего копчения'),

(2, 'Черный', 'Черная сталь','Коптильня преднозначена для горячего копчения'),

(3, 'Белый', 'Черная сталь','Пивоварня преднозначена для изготовления пивного сусла'),

(4, 'Стальной', 'Черная сталь','Шампура преднозначены для любителей отдыха на природе'),

(5, 'Стальной', 'Черная сталь','Дымогенератор преднозначен для производства дыма при холодном копчении для ')

GO

INSERT СКЛАД

(ТоварID, Количество)

VALUES

(1, 20),

(2, 10),

(3, 7),

(4, 8),

(5, 9)

GO

INSERT ЗАКАЗЫ

(СотрудникID, ПокупательID, Датаоформления)

VALUES

(1,2, DATEADD(DAY, -85, GETDATE())),

(2,1, DATEADD(DAY, -85, GETDATE()))

GO

INSERT ДЕТАЛИЗАКАЗА

(ЗаказID, Количествопозиций, ТоварID, Количествопродонного, Цена)

VALUES

(4,2,1,1,295)...