Кафедра информатики и математики
Факультет экономики и управления
Кафедра информатики и математики
Курсовая работа
по дисциплине «Базы данных»
Проектирование базы данных интернет-магазина для ООО «Приволжской Механический Завод» средствами SQL Server
Специальность: прикладная
информатика в экономике
Выполнил:
студент V курса группы ПРИ-В-12
Перминов Алексей Владимирович
Научный руководитель:
кандидат педагогических наук, доцент
Исупова Наталья Ивановна
г. Киров
2017 г.
СОДЕРЖАНИЕ
ВВЕДЕНИЕ. 3
1. КОНЦЕПТУАЛЬНОЕ ПРОЕКТИРОВАНИЕ. 5
2. ЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ.. 8
2.1 Модель «сущность-связь». 8
2.2 Классификация связей. 9
2.3 Определение связей. 9
2.4 Нормализация отношений. 11
3. ДАТАЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ. 16
3.1 Состав таблиц базы банных. 16
3.2 Средства поддержания целостности. 16
4. ПРОГРАММНАЯ РЕАЛИЗАЦИЯ БАЗЫ ДАННЫХ ИНТЕРНЕТ МАГАЗИНА ДЛЯ ООО «ПМЗ» СРЕДСТВАМИ SQL SERVER.. 22
4.1 Создание базы данных. 22
4.2 Установка связей между таблицами. 25
4.3 Создание пользовательских ограничений. 29
4.4 Заполнение базы данных тестовыми данными. 31
4.5 Создание логических триггеров. 35
4.6 Создание запросов. 36
4.7 Создание представлений. 37
ЗАКЛЮЧЕНИЕ. 41
БИБЛИОГРАФИЧЕСКИЙ СПИСОК.. 42
ПРИЛОЖЕНИЕ. 43
ВВЕДЕНИЕ
Данные представляют собой информацию, имеющую определенный вид, который позволяет автоматизировано собирать сами данные, хранить их и обрабатывать в дальнейшем человеку либо информационному системе.
База данных (БД) представляет собой совокупность именованных данных, показывающих состояние сущностей и их отношений между собой в определенных предметных областях и их отношений между собой в определенных предметных областях [1]. Подобная совокупность данных при небольшой избыточности позволяет использовать информацию наиболее оптимально для одного или более приложений в рассматриваемых предметных областях.
Система управления базой данных – СУБД – это множество средств (языковых и программных), используемых для создания, управления и многопользовательского использования баз данных [2].
SQL (structures query language) – универсальный логический язык, позволяющий создавать, изменять и управлять данными в реляционных БД. Диалекты языка SQL являются полноценными языками программирования. Операторы SQL делятся на 4 группы:
· операторы определения данных;
· операторы манипуляции данными;
· операторы определения доступа к данным;
· операторы управления транзакциями.
Преимущества SQL – независимость от конкретных СУБД (запросы незатруднительно перенести из одной СУБД в другую); стандарты SQL; декларативность (СУБД сама определяет порядок выполнения запросов пользователя).
Современные интернет магазины невозможно представить без использования баз данных для хранения информации. Такие базы данных настолько широко и глубоко внедрились в бизнес процессы, что очень трудно назвать какую-либо сферу бизнеса, где бы они ни использовались.
Предметная область проектируемой базы данных – интернет-магазин. Назначение базы данных – обеспечение пользователя базы данных необходимой ему информацией о товарах, клиентах и о заказанных ими товарах.
База данных является основой для принятия маркетинговых решений, анализа и оценки спроса на рынке товаров, так как фактически отражает организацию деятельности интернет магазина.
Используя и анализируя информацию, хранящуюся в БД, руководство интернет-магазина может принимать определенные решения влияющее на дальнейшее развитие интернет-магазина, а также регулировать стоимости товаров и оценивать эффективность вложений.
Целью данной курсовой работы является проектирование и разработка базы данных интернет-магазина для ООО «Приволжский Механический Завод».
В рамках курсовой работы были поставлены следующие задачи:
· проанализировать данные, описанные в предметной области. Определить таблицы, необходимые для представления предметной области в рамках реляционной модели данных;
· спроектировать логическую структуру базы данных интернет-магазина;
· провести программную реализацию базы данных средствами SQL SERVER для интернет-магазина ООО «Приволжский Механический Завод».
ЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ
Модель «сущность-связь»
Модель сущность-связь (ER-модель) − модель данных, позволяющая описывать концептуальные схемы предметной области [4]. ER-модель используется при высокоуровневом (концептуальном) проектировании баз данных. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями.
ER-модель представляет собой формальную конструкцию, которая сама по себе не предписывает никаких графических средств её визуализации.
Ниже приведена модель «сущность-связь» интернет-магазина. Она включает в себя 8 сущностей (Покупатель, Сотрудники, Отдел, Товар, Описание Товаров, Складские запасы, Заказы, Детали заказа).
Атрибуты: «Фамилия», «Имя», «Отчество», «Адрес», «Город», «Телефон», «Дата регистрации», принадлежат сущности «Попупатели».
Атрибуты: «Фамилия», «Имя», «Отчество», «Должность», «Зарплата», «Премия», «Семейное положение», «Дата рождения», «Дата приема на работу», «Адрес», «Телефон», «Отдел» принадлежат сущности «Сотрудники».
Атрибуты: «Название отдела» принадлежат сущности «Отдел».
Атрибуты: «Название Товара» принадлежат сущности «Товар».
Атрибуты: «Цвет», «Материал», «Текстовое описание» принадлежат сущности «Описание товара».
Атрибуты: «Количество» принадлежат сущности «Склад».
Атрибуты: «Идентификатор покупателя», «Идентификатор сотрудника», «Дата заказа» принадлежат сущности «Заказы».
Атрибуты: «Количество заказанных позиций», «Идентификатор продукта», «Количество заказанных единиц», «Цена товара», «Итоговая сумма заказа принадлежат сущности «Детали заказа».
Классификация связей
Связь позволяет моделировать отношения между объектами предметной области. Наименование связи должно быть уникально во всей модели.
Связь «один ко многим» самая распространенная. В этом типе связей у строки таблицы А может быть несколько совпадающих строк таблицы Б, но каждой строке таблицы Б может соответствовать только одна строка из А.
Связь «один к одному» создается, если для обоих связанных ключей определены ограничения первичного ключа или уникальности.
Таблица 2. Классификация связей
| Сущность А | Сущность В | Тип связи |
| Отдел | Сотрудник | «Один-к-одному» |
| Детали заказа | Заказы | «Один-ко-многим» |
| Покупатель | ||
| Сотрудники | ||
| Детали товара | Товары | «Один-к-одному» |
| Склад | ||
| Детали заказа | Товары | «Один-ко-многим» |
Определение связей
Для того чтобы правильно построить модель базы данных, необходимо определить какие связи будут иметь таблицы.
1. Связи таблиц: «Сотрудники» и «Отдел».
2. Таблицы «Сотрудники» и «Отдел» должны быть связаны связью «один к одному» по ключу «ID «из таблицы Сотрудники и ключом «ID» из таблицы «Отдел».
3. Связи таблиц: «Товар» и «Детали товара».
Таблицы «Товар» и «Детали товара» c должны быть связаны связью «один к одному» по ключу «ID» из таблицы «Товар» и ключу «ID» из таблицы «Детали товара».
4. Связи таблиц: «Товары» и «Склад».
Таблицы «Товары» и «Склад» должны быть связаны связью «один к одному» по ключу «ID» из таблицы «Товары» и ключу «ТоварID» из таблицы Склад.
5. Связи таблиц: «Покупатели» и «Заказы».
Таблицы «Покупатели» и «Заказы» должны быть связаны связью «один ко многим» по ключу «ID» из таблицы «Покупатели» и ключу «ПокупателиID» из таблицы «Заказы».
6. Связи таблиц: «Сотрудники» и «Заказы».
Таблицы «Сотрудники» и «Заказы». c должны быть связаны связью «один ко многим» по ключу «ID» из таблицы «Сотрудники» и ключу «СотрудникиID» из таблицы «Заказы».
7. Связи таблиц: Товары и Детали заказа.
Таблицы «Товары» и «Детали заказа» c должны быть связаны связью «один ко многим» по ключу «ID» из таблицы «Товары» и ключу «ТоварID» из таблицы «Детали заказа».
После проведённого анализа необходимых связей таблиц можно построить схему модели сущности-связь (см. рис. 1).
Рис. 1. Модель «сущность-связь»
Нормализация отношений
Нормальная форма − требование, предъявляемое к структуре таблиц в теории реляционных баз данных для устранения из базы избыточных функциональных зависимостей между атрибутами (полями таблиц) [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НФ
| Код | Сотрудник | Отдел | Дата рождения |
| Перминов Алексей Владимирович | Отдел продаж, Отдел поддержки. | 08.12.1990 | |
| Савиных Олег Анатольевич | Отдел продаж | 27.01.1989 |
В 1НФ атрибуты не должны иметь несколько значений, например, если в столбце «Отдел», прописано два значение «Отдел поддержки» и «Отдел поддержки» то необходимо разбить эти значения.
Таблица 4. Пример неправильной 1НФ
| Код | Сотрудник | Отдел | Дата рождения |
| Перминов Алексей Владимирович | Отдел продаж | 08.12.1990 | |
| Туманов Георгий Викторович | Отдел продаж | 27.01.1989 | |
| Перминов Алексей Владимирович | Отдел поддержки. | 08.12.1990 |
После изменений таблица 5 находится в первой нормальной форме, поскольку в каждой ячейке содержится только одно логическое значение, то есть было соблюдено свойство атомарности. Добились этого путем дублирования данных о сотруднике с кодом 1, следовательно, мы внесли избыточность в базу данных, но привели ее к первой нормальной форме. Для избавления от избыточности в базе данных необходимо перевести её во вторую нормальную форму.
Чтобы база данных находилась во второй нормальной форме, должны быть соблюдены требования первой нормальной формы. Вторая нормальная форма, в отличие от первой, требует, чтобы у сущностей обязательно были ключевые атрибуты, и чтобы не было избыточных данных.
В данной таблице собрана информация о сотрудниках и товарах, которые они реализовали. Перед нами стоит задача: избавиться от избыточности путем приведения базы данных ко второй нормальной форме.
Таблица 5. Пример неправильной 2НФ
| ID | Сотрудник | Товар | Клиент | Отдел | Тел. клиент |
| Перминов | Пивоварня Золингер | Савиных | Отдел продаж | 22-43-77 | |
| Туманов | Коптильня ханхи | Вараксин | Отдел продаж | 23-44-33 | |
| Суворов | Коптильня дымка | Жарков | Отдел продаж | 11-33-35 |
После выявления функциональных связей, можно создать таблицы-справочники на основе функциональных связей. Для этого разобьем таблицу на три таблицы-справочника.
Создаём таблицы на основе функциональных связей:
Таблица 6. Таблица-справочников «Продажи» во 2НФ
| ID | Товар | Сотрудник | Клиент |
| Пивоварня Золингер | |||
| Коптильня ханхи | |||
| Коптильня дымка |
Таблица 7. Таблица-справочников «Сотрудники» во 2НФ
| ID | Сотрудник | Д.Р. сотрудника |
| Перминов | 08.12.1990 | |
| Туманов | 27.01.1989 | |
| Суворов | 17.03.1991 |
Таблица 8. Таблица-справочников «Сотрудники» во 2НФ
| ID | Клиент | Телефон клиента |
| Савиных | 22-43-77 | |
| Вараксин | 23-44-33 | |
| Жарков | 11-33-35 |
Выполнив преобразования, получаем отношения, находящееся во второй нормальной форме, без избыточности данных.
Переменная отношения находится в третьей нормальной форме тогда и только тогда, когда она находится во 2НФ, и отсутствуют транзитивные функциональные зависимости неключевых атрибутов от ключевых [8].
Если база данных находится в третьей нормальной форме, то в ней должны быть соблюдены требования второй нормальной формы, а соответственно и первой. Область оптимизации третьей нормальной формы – таблица, третья нормальная форма избавляет нас от транзитивных зависимостей: любой столбец таблицы должен зависеть только от ключевого столбца.
Таблица 9. Пример таблицы не находящейся в 3НФ
| Модель | Материал |
| Коптильня дымка | Нержавеющая сталь |
| Коптильня ханхи | Чёрная сталь |
| Пивоварня Золингер | Чёрная сталь |
В таблице 9 ключом является имя название товара, а неключевым полем – Материал. Логично предположить, что в этой таблице поле «Материал» может быть одинаковыми для нескольких записей. И для того, чтобы эта таблица находилась в третей нормальной форме, не обходимо ее разделить на две.
Таблица 10. Таблицы «Материал» находящейся в 3НФ
| ID | Поставщик |
| Нержавеющая сталь | |
| Чёрная сталь |
Таблица 11. Пример таблицы не находящейся в 3НФ
| Товар | Материал |
| Пивоварня Золингер | |
| Коптильня ханхи | |
| Коптильня дымка |
Для правильного функционирования базы данных все таблицы необходимо преобразовать во 2НФ или 3НФ.
Состав таблиц базы банных
Физические модели баз данных определяют способы размещения данных в среде хранения и способы доступа к этим данным, которые поддерживаются на физическом уровне.
Даталогическое проектирование − определение особенностей хранения данных, методов доступа и т.д.[9]
Для реляционной модели данных даталогическая модель − набор схем отношений, обычно с указанием первичных ключей, а также «связей» между отношениями, представляющих собой внешние ключи.
Каждому типу модели данных и каждой разновидности модели, поддерживаемой конкретной СУБД, присущи свои специфические особенности.
Выбранная СУБД – MS SQL Server.
Определяем типы данных для атрибутов сущностей. Для всех уникальных полей – тип integer. Для текстовых полей (фамилия, Имя, Отчество и т.д.) – char с указанием количества символов.
Создание базы данных
Создадим новую базу данных при помощи универсальной среды для доступа, настройки и администрирования всех компонентов 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
Создание запросов
Запросы − это объект базы данных, который служит для извлечения данных из таблиц и предоставления их пользователю в удобном виде. Особенность запросов состоит в том, что они черпают данные из базовых таблиц и создают на их основе временную таблицу. Применение запросов позволяет избежать дублирования данных в таблицах и обеспечивает максимальную гибкость при поиске и отображении данных в базе данных [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 Датаоформления;
Эта команда создаст запрос, который выводит информацию о продажах за последние два месяца в хронологическом порядке.
Создание представлений
Представление – это виртуальная (логическая) таблица, представляющая собой поименованный запрос (синоним к запросу), который будет подставлен как подзапрос при использовании представления [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, название, остаток товара на складе.
4.8 Создание процедур
Хранимая процедура − объект базы данных, представляющий собой набор 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','Кир