Всё для Учёбы — студенческий файлообменник
1 монета
doc

Контрольная «Разработка базы данных для расчета стоимости заказа предприятия» по Базам данных (Марков А. А.)

Задание

Спроектировать базу данных, содержащую информацию, необходимую для расчета стоимости заказа полиграфического предприятия и клиенте типографии. Информация о заказе содержит стоимость, время исполнения, дату создания, а так же информацию о составных частях заказа таких, как продукт, краски, запечатываемый материал, печатные формы, брошюровочные работы. Информация о клиенте – фамилия, имя, отчество, номер телефона и e-mail, издательство, которым пользовался клиент для выполнения допечатной обработки издания и адрес этой типографии. Должна быть обеспечена возможность сохранять более, чем один заказ для одного владельца, использования более одной краски для печати и использования печатных форм определенного типа, запечатываемых материалов, брошюровочных работ в более, чем одном заказе. В базе данных должно быть не менее 8 множеств сущностей, 10 – 12 запросов, 1 пользовательского представления, 2 функций, 1 процедуры и 2 триггера.

База данных должна быть разработана в среде СУБД MySQL версии 5.0.15 или выше. Все пакетные sql–файлы должны быть подготовлены с использованием кодировки UTF-8 в среде простейших текстовых редакторов (таких, как MS Notepad или TextEdit) без использования команд дополнительного оформления и форматирования.

Отчет должен содержать:

- титульный лист, аннотацию, содержание, техническое задание;

- описание базы данных;

- графическую информацию:

• Модель ANSI-SPARC;

• ER – диаграмма;

• Алгоритмы хранимых процедур;

• Примеры выполнения запросов.

На титульном листе указывается название учебного заведения, в котором выполнена работа, кафедра и индекс группы, фамилия и инициалы студента и руководителя работы. Внизу титульного листа ставится надпись: «Москва 2010 г.» Титульный лист подписывается руководителем работы и исполнителем.

Отчет должен быть сброшюрован; использование скрепок, прозрачных папок недопустимо.[3]

2. Аннотация к отчету.

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

База данных содержит 15 таблиц, 3 триггера, 2 функции, 1 хранимую процедуру и 2 пользовательских представления. Она разработана с использованием языка запросов SQL и реализована в СУРБД версии 5.1.

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

3. Описание базы данных

Информационная система «My Printing House» предназначена для автоматизации работы по расчету стоимости заказа полиграфического предприятия. В основе системы лежит стандартная трехзвенная архитектура (под управлением СУРБД MySQL, сервер «Apache», клиентская часть реализована на языке PHP). В рамках курсового проекта разработана реляционная база данных под управлением СУРБД MySQL.[3]

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

База данных может быть использована в работе типографий.

4. ER модель

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

ER – модель представлена на рис. 1 и представляет собой графическую диаграмму. ER – диаграмма состоит из элементов трех основных типов: множеств сущностей, атрибутов сущностей и связей. Множество сущностей – набор однородных абстрактных объектов определенного типа. Атрибут – свойство объекта. Связь – соединение между двумя или большим числом множеств сущностей.[1]

База данных содержит восемь множеств сущностей.

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

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

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

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

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

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

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

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

Брошюровка

Издательство

Работает

Сшить Печатная форма

С чего печатать

Клиент

На чем печатать

Создает

Продукт

Запечатываемый материал

Красочность

Заказ

Краска

Принадлежит

Рис. 1. ER - диаграмма

5. Модель ANSI-SPARC

Модель ANSI-SPARC определяет три уровня абстракции: внешний, концептуальный и внутренний. На внешнем уровне пользователю предоставляется интерфейс на основе полученных данных. Внутренний уровень отвечает за восприятие данных операционной системой и СУБД. Концептуальный уровень представления данных необходим для обеспечения независимости внешнего и внутреннего уровней и представляет данные в необходимой для визуализации форме. На рисунке 2 представлена модель ANSI-SPARC.[3]

Информационная система

«MY PRINTING HOUSE».

Интернет ресурс.

Внешний уровень

Нормализованные таблицы базы данных

ID mail surname name patronymic Phone

Уникальный идент. почта фамилия имя отчество Тел.

ID cost time dataoforder accept Record

1 цена Время выполнения Дата создания согласие выполнение

концептуальный уровень

Реализация таблиц базы данных.

CREATE TABLE clients…

CREATE TABLE orders…

Внутренний уровень

База данных СУБД MySQL 5.1

Рис. 2. ANSI-SPARC модель

Каждому пользователю предоставлен интерфейс для работы с данными. В качестве внешнего уровня выступает web приложение. Пример пользовательского интерфейса представлен на рис. 2.

Рис. 2. Пример типового заказа.

Концептуальный уровень содержит нормализованные таблицы:

Clients (surname, name, patronymic, phone, e_mail, password);

Publishing (name, city);

Orders ( ID, cost, time, recordofmaked, accept, dataoforder);

Product (productID, type, run, pages, format, priceofone);

Inks (color, price, on1kpages, varnising);

Substrate (type, strength,price);

Stitching (type,price);

Plate (type, timeofmaking, price);

Ink2prod (productID, color);

Sub2prod (productID, type, strength);

Stitch2prod (productID, type);

Plate2prod (productID, type);

Clts2pub (e_mail, name);

CltsOrd (e_mail, OID);

Ord2prod (OID, PID).

Внутренний уровень содержит таблицы, реализованные в базе данных, описание которых представлено в разделе «Описание структуры таблиц» и в приложении.

Физическая организация данных реализована в СУРБД MySQL 5.1 на таблицах «InnoDB», что позволяет организовать ссылочную целостность при помощи внешних ключей, а при необходимости использовать транзакции.

Причинами использования данной модели являются:

- возможность обращения несколькими пользователями к одним и тем же данным;

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

- изменение структуры данных не должно оказывать влияние на пользовательский интерфейс;

- обеспечение независимости базы данных от аппаратных средств.

6. Описание структуры таблиц

Разработанная ER – диаграмма была преобразована в набор таблиц. В результате множество сущностей и связи были представлены отношениями. Каждой сущности приводится в соответствие таблица, всего таблиц 15.

При проектировании таблиц были использованы формы нормализации. Все таблицы представлены в третьей нормальной форме. В каждом поле хранится одно значение. Примером нормализации может служить разделение поля ФИО клиента на 3: фамилия, имя, отчество. Все неключевые поля являются зависимыми от первичного ключа. Что позволяет избежать излишних повторов данных. И все поля таблиц не зависят от других неключевых полей. Примером данной нормализации является выделение в отдельную таблицу информации об издательствах. В таблицах №1 и №2 приведены примеры результата нормализации.[4]

Таблица «clients» («Клиенты»)

Таблица №1

Surname Name Patronymic Phone e-mail Password

Фамилия Имя Отчество Тел. номер Почта Пароль

Таблица №1 содержит информацию о клиенте типографии – ФИО, телефонный номер, e-mail, пароль, для защиты данных.

Таблица «publishing»(«Издательство»)

Таблица №2

ID Name City № Название Адрес (город)

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

Таблица «orders» («Заказы»)

Таблица №3

ID Cost Recordofmaked Accept Time Dateoforder

№ Стоимость Выполнение Согласие Время выполнения Дата создания

Таблица содержит заказы клиентов – уникальный идентификатор заказа, стоимость, отметку выполнения, согласие на печать клиента, время выполнения заказа и дату создания заказа. В таблице №3 представлен окончательный вариант сущности «orders».

Таблица «product» («Изделие»)

Таблица №4

PID Type Run Pages Format Priceofone

№ Название изделия Тираж Кол-во печатных листов Формат изделия Стоимость

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

Таблица «inks» («Краски»)

Таблица №5

Color Price On1kpages Varnishing

Цвет Стоимость 1г Расход на 1000 страниц Стоимость лака

Таблица содержит информацию о краске, которая требуется в заказе – цвет, стоимость 1 грамма, расход на 1000 страниц граммов краски, стоимость лака для данного вида краски. Ее реализация представлена в табл. 5.

Таблица «substrate» («Запечатываемый материал»)

Таблица №6

Type Strength Price

Тип материала Плотность Стоимость

Таблица содержит информацию о запечатываемых материалах (бумаге) – тип, плотность и стоимость. Ее реализация представлена в табл. 6.

Таблица «plate» («Печатная форма»)

Таблица №7

Type Timeofmaking Price

Тип Время производства Стоимость

Таблица содержит информацию о печатных формах – типе, времени изготовления, стоимости. Ее реализация представлена в табл.7.

Таблица «stitching» («Брошюровочные работы»)

Таблица №8

Type Price Тип сшивания Стоимость

Таблица содержит информацию о типе и стоимости брошюровочных работ (сшивания). Ее реализация представлена в табл. 8.

Таблица «clts2pub» («Клиент издательства»)

Таблица №9

e_mail Name Почта Название

Таблица определяет, каким издательством пользовался клиент для выполнения допечатной обработки изделия. Ее реализация представлена в табл. 9.

Таблица «cltsOrd» («Заказ клиента»)

Таблица №10

e_mail OID Почта Номер заказа

Таблица определяет принадлежность заказа клиенту. Ее реализация представлена в табл. 10.

Таблица «ord2prod» («Что печать»)

Таблица №11

OID PID № заказа № изделия

Таблица определяет принадлежность изделия заказу.

Таблица «ink2prod» («Красочность изделия»). Ее реализация представлена в табл. 11.

Таблица №12

productID Color № продукта Цвет краски

Таблица определяет, какими красками необходимо печатать заказ. Ее реализация представлена в табл. 12.

Таблица «sub2prod» («На чем печатать»)

Таблица №13

productID Type Strength

№ изделия Тип материала Плотность

Таблица определяет, на каком материале необходимо печатать изделие. Ее реализация представлена в табл. 13.

Таблица «stitch2prod» («Сшить»)

Таблица №14

productID Type № изделия Тип

Таблица определяет, как сшивать изделие. Ее реализация представлена в табл. 14.

Таблица «plate2prod» («С чего печатать»)

Таблица №15

productID Type № изделия Тип

Таблица определяет, какие печатные формы используются для печати заказа. Ее реализация представлена в табл. 15.

Исходные коды для создания приведены в приложении A (листинг 1).

7. Функциональные зависимости

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

Как было сказано ранее, все таблицы нормализованы по третьей форме.

Ключевым полем (первичный ключ) в таблице «clients» является поле e_mail так, как при регистрации электронной почты требуется уникальность e-mail. Остальные поля отношения не могут быть уникальными и определять остальные.

В таблице «publishing» вводится специальное поле ID – уникальный идентификатор как уникальное так, как названия издательств могут повторяться и в одном городе может находиться несколько издательств.

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

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

В таблице «inks» уникальным полем является color. От цвета краски зависит ее стоимость, расход, и цена лакировки.

В таблице «substrate» уникальным будет сочетание type, strength именно от этих параметров зависит стоимость материала.

В таблице «plate» уникальным является поле type. Все печатные формы разделяются по типу печати. И для каждого типа формы ставится в соответствие цена и время на изготовление.

В таблице «stitching» уникальным атрибутом является тип брошюровочных работ. Стоимость определятся типом сшивания.

8. Начальное заполнение таблиц

Для демонстрации особенностей базы данных, работы типовых запросов, триггеров и хранимых процедур созданные таблицы заполняются тестовыми данными. Ниже приведены заполненные таблицы. Исходные коды заполнения базы данных приведены в приложении A листинг 2.

На рис. 3 изображено заполнение таблицы «clients» тестовыми данными.

Рис. 3. Данные таблицы “clients”.

На рис. 4 изображено заполнение таблицы «publishing» тестовыми данными.

Рис. 4. Данные таблицы “publishing”.

На рис. 5 изображено заполнение таблицы «orders» тестовыми данными.

Рис. 5. Данные таблицы “orders”.

На рис. 6 изображено заполнение таблицы «product» тестовыми данными.

Рис. 6. Данные таблицы “product”.

На рис. 7 изображено заполнение таблицы «inks» тестовыми данными.

Рис. 7. Данные таблицы “inks”.

На рис. 8 изображено заполнение таблицы «plate» тестовыми данными.

Рис. 8. Данные таблицы “plate”.

На рис. 9 изображено заполнение таблицы «substrate» тестовыми данными.

Рис. 9. Данные таблицы “substrate”.

На рис. 10 изображено заполнение таблицы «stitching» тестовыми данными.

Рис. 10. Данные таблицы “stitching”.

На рис. 11 изображено заполнение таблицы «clts2pub» тестовыми данными.

Рис. 11. Данные таблицы “clts2pub”.

На рис. 12 изображено заполнение таблицы «clts2Ord» тестовыми данными.

Рис. 12. Данные таблицы “clts2Ord”.

На рис. 13 изображено заполнение таблицы «ord2prod» тестовыми данными.

Рис. 13. Данные таблицы “ord2prod”.

На рис. 14 изображено заполнение таблицы «ink2prod» тестовыми данными.

Рис. 14. Данные таблицы “ink2prod”.

На рис. 15 изображено заполнение таблицы «sub2prod» тестовыми данными.

Рис. 15. Данные таблицы “sub2prod”.

На рис. 16 изображено заполнение таблицы «stitch2prod» тестовыми данными.

Рис. 16. Данные таблицы “stitch2prod”.

На рис. 17 изображено заполнение таблицы «plate2prod» тестовыми данными.

Рис. 17. Данные таблицы “plate2prod”.

9. Запросы

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

• Заказы, сделанные определенным клиентом

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

Рис.18. Поиск заказов сделанных определенным клиентом.

• Все заказы, на печать которых клиенты дали согласие.

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

Рис. 19. Представление списка заказов

• Полная информация о заказе.

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

Рис. 20. Полная информация о заказе.

• Краски, используемые при печати определенного заказа.

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

Рис. 21. Краски, используемые для печати определенного заказа.

• Полный список красок

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

Рис. 22. Краски.

• Полная информация о клиенте.

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

Рис. 23. Полная информация о клиенте.

• Изменение стоимости брошюровочных работ.

Повышение стоимости брошюровочных работ по клеевому скреплению. На рис. 24 представлена стоимость клеевого скрепления до изменения.

Рис. 24. Стоимость клеевого скрепления до изменения его стоимости.

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

Рис. 25. Новая стоимость клеевого скрепления.

• Удаление запечатываемого материала.

Удалить запечатываемы материал определенного типа и плотности. Таблица в исходном состоянии представлена на рис. 9. После выполнения запроса таблица примет вид, изображенный на рис. 26. Удален тип бумаги design с плотностью 140.

Рис. 26 удаление запечатываемого материала

• Изменение расхода краски.

Изменить расход желтой краски на 1000 печатных листов материала. До изменения запись представлена на рис. 27.

Рис. 27. Расход желтой краски до изменения.

На рис. 28 изображен результат выполнения запроса.

Рис. 28. Изменение расхода желтой краски.

• Удаление всех заказов, не имеющих согласия на сотрудничество.

Удалить все заказы, не устраивающие клиентов. Исходная таблица изображена на рис.5.Результат представлен на рис. 29.

Рис. 29. Удаление заказов.

10. Пользовательские представления

SQL поддерживает отношения, которые не создаются на физическом уровне. Эти отношения называют пользовательскими представлениями. Для представления возможностей работы с базой данной были разработаны 2 пользовательских представления. Исходные коды представлений содержатся в приложении A листинг 4.[2]

Представление FullOrder

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

Рис. 30. Представление FullOrder.

Представление inkOrder

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

Рис. 31. Представление inkOrder.

11. Хранимые процедуры и функции.

В курсовой работе реализовано 2 функции и 1 процедура. Исходный код представлен в приложении A листинг 5.

11.1 Функции

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

11.1.1 Функция qtyOrder()

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

Пример выполнения изображен на рис. 32.

Рис. 32. Функция qtyOrder().

11.1.2 Функция profit()

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

Рис. 33. Функция profit().

11.2 . Процедуры

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

11.2.1 Процедура paper().

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

начало Выборка всех данных из таблицы substrate.

Вывод списка материалов

конец

Рис. 34. Блок-схема процедуры paper().

Результат работы процедуры показан на рис. 35.

Рис. 35. Процедура paper().

12 . Триггеры.

Триггер – это последовательность команд, поставленная в соответствие определенному событию и выполняемая в ответ на это событие. Триггеры предназначены для проверки выполнения ограничений на данные, находящиеся в базе. Для проверки изменений данных было разработано 3 триггера. Исходные коды триггеров находятся в приложении A листинг 6.[2]

• Триггер delclient

Триггер срабатывает при удалении данных из таблицы «clients» и удаляет данные об использовании клиентом издательства из таблицы «clts2pub». Блок схема триггера приведена на рис.36.

начало

конец Удаление записи из таблицы clients

Удаление данных из таблицы clts2pub.

Рис. 36. Блок-схема триггера delclient.

Результат работы триггера представлен на рис. 37.

Рис. 37. Триггер delclient.

2 . Триггер delorder

Срабатывает после удаления записи из таблицы «orders». Удаляет данные из таблиц «product», «ord2prod», «ink2prod», «stitch2prod»,«plate2prod», «sub2prod». Блок- схема триггера представлена на рис. 38.

Удаление записи из таблицы orders

начало

Удаление данных из таблиц “product”,”ord2prod”,”ink2prod”,”stitch2prod”,”sub2prod”,”plate2prod”.

конец

Рис. 38. Блок-схема триггера delorder

Пример работы триггера представлен на рис. 39.

Рис. 39. Триггер delorder.

3 . Триггер clntord

Триггер срабатывает перед удалением клиента из базы данных и удаляет информацию о заказах, которые создавал данный клиент. Удаление данных происходит из таблиц «orders» и «clts2Ord». Блок схема триггера приведена на рис. 40.

Удаление записи из таблицы clients

Удаление данных из таблиц “orders” и “cltsord”.

конец начало

Рис. 40. Блок-схема триггера clntord.

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

Результат работы триггера приведен на рис. 41.

Рис. 41. Триггер clntord.

13. Заключение

В результате выполнения курсовой работы была разработана реляционная база данных, реализуемая на СУРБД MySQL 5.1. База данных хранит информацию о заказах и клиентах полиграфического предприятия. Структура базы данных нормализована согласно 3 формы нормализации.

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

База данных удовлетворяет модели ANSI_SPARC.

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

• Базы данных: проектирование, реализация, сопровождение. Второе издание. Т. Коннели, К. Берг, 2005 г.

• Системы баз данных. Г. Гарсия – Молина, 2003 г.

• Методические указания по выполнению курсовой работы, Лазутченко О. В. 2007 г.

• Практическое введение в базы данных, часть 1-3. А. А. Марков, Москва 2004 г.

15 . Приложение A.

Листинг 1. Исходный код создания таблиц.

drop database if exists exams_work;

create database exams_work character set 'utf8' collate 'utf8_general_ci';

use exams_work;

drop table if exists clients;

create table clients

( name char(20) not null,

patronymic char(20) not null,

surname char(20) not null,

phone char(10) not null,

e_mail char(40) not null primary key,

password char(20) not null

); drop table if exists publishing;

create table publishing

( id int auto_increment primary key,

name char(20) not null,

city char(20) not null

); drop table if exists orders;

create table orders

( ID int unsigned not null auto_increment primary key,

cost float unsigned not null,

time float unsigned not null,

dateoforder date not null,

recordofmaked bool,

accept bool );

drop table if exists product;

create table product

( PID int unsigned auto_increment not null primary key,

type char(40) not null,

run int unsigned not null,

pages int not null,

format char(2) not null,

timeofmaking int unsigned not null,

priceofone float unsigned not null

); drop table if exists sub2prod;

create table sub2prod

( productID int unsigned not null,

type char(15) not null,

strength float unsigned not null,

primary key(type,strength,productID)

); drop table if exists substrate;

create table substrate

( type char(50) not null,

strength float unsigned not null,

price float unsigned not null,

primary key(type,strength)

); drop table if exists plate2prod;

create table plate2prod

( productID int unsigned not null,

type char(20) not null,

primary key(productID, type)

);

drop table if exists plate;

create table plate

( type char(20) not null primary key,

timeofmaking float unsigned not null,

price float unsigned not null

); drop table if exists stitching;

create table stitching

( type char(20) not null primary key,

price float unsigned not null

); drop table if exists stitch2prod;

create table stitch2prod

( productID int unsigned not null primary key,

type char(20) not null

); drop table if exists inks;

create table inks

( color char(20) not null primary key,

price float unsigned not null,

on1kpages int unsigned not null,

varnishing float unsigned not null

);

drop table if exists ink2prod;

create table ink2prod

( productID int unsigned not null,

color char(20) not null,

primary key(productID,color)

); drop table if exists clts2pub;

create table clts2pub

( e_mail char(40) not null,

ID int unsigned not null,

primary key (e_mail, ID)

); drop table if exists clts2Ord;

create table clts2Ord

( e_mail char(40) not null,

OID int unsigned not null,

primary key (e_mail, OID)

); drop table if exists ord2prod;

create table ord2prod

( OID int unsigned not null,

PID int unsigned not null,

primary key (OID,PID)

); Листинг 2. Исходный код занесения данных

insert into inks values

("purple",0.96,190,0.6),

("yellow",0.96,300,0.6),

("blue",0.96,250,0.6),

("black",0.80,140,0.5),

("pantone",1.00,200,0.7);

insert into substrate values

("coated",90,0.070),

("coated",105,0.075),

("coated",115,0.080),

("coated",130,0.090),

("coated",150,0.095),

("stamping",100,0.11),

("stamping",200,0.12),

("glue",80,0.12),

("glossy",90,0.11),

("glossy",105,0.12),

("glossy",120,0.115),

("newspapers",35,0.025),

("newspapers",45,0.027),

("newspapers",65,0.029),

("design",80,0.11),

("design",90,0.115),

("design",105,0.12),

("design",115,0.125),

("design",120,0.13),

("design",130,0.135),

("design",140,0.14),

("design",150,0.145),

("offset",90,0.08),

("offset",105,0.085),

("offset",115,0.09);

insert into stitching values

("spring",80),

("glue",300),

("staple",10);

insert into plate values

("offset",0.3,360.0);

insert into orders values

(1,20000,48,"2010-01-22",1,1),

(2,25000,56,"2010-02-13",1,1),

(3,12000,24,"2010-03-12",1,0),

(4,50000,36,"2010-05-20",0,1),

(5,100000,120,"2010-05-25",0,1);

insert into product values

(1,"book",100,50,"A5",200),

(2,"book",200,30,"A5",125),

(3,"magazine",1000,10,"A4",12),

(4,"newspaper",5000,5,"A2",10),

(5,"book",10000,100,"A5",100);

insert into clients values

("Nemcev","Vladislav","Vladimirovich","9032769341","fetboy@mail.ru","fet"),

("Danilov","Alesander","Michailovich","9090120203","sanek@mail.ru","qwerty"),

("Danilov","Semen","Vladimirovich","9168812345","monster@mail.ru","asdfg"),

("Nemcev","Vladislav","Vladimirivich","1234567890","client@mail.ru","zxcvbn");

insert into publishing values

(1,"Buka","Moscow"),

(2,"Timer","London"),

(3,"Booker","Moscow");

insert into clts2pub values

("fetboy@mail.ru",1),

("sanek@mail.ru",2),

("monster@mail.ru",3),

("client@mail.ru",3);

insert into clts2Ord values

("fetboy@mail.ru",1),

("sanek@mail.ru",2),

("monster@mail.ru",3),

("monster@mail.ru",4),

("client@mail.ru",5);

insert into ord2prod values

(1,1),

(2,2), (3,3), (4,4),

(5,5); insert into ink2prod values

(1,"black"),

(2,"black"),

(2,"purple"),

(3,"yellow"),

(3,"black"),

(3,"pantone"),

(4,"black"),

(5,"blue");

insert into sub2prod values

(1,"offset",90),

(2,"offset",105),

(3,"newspapers",45),

(4,"offset",90),

(5,"coated",105);

insert into stitch2prod values

(1,"glue"),

(2,"glue"),

(3,"staple"),

(4,"staple"),

(5,"glue");

insert into plate2prod values

(1,"offset"),

(2,"offset"),

(3,"offset"),

(4,"offset"),

(5,"offset");

Листинг 3. Исходный код запросов.

SELECT e_mail, ID, cost, time, recordofmaked

FROM clts2Ord JOIN orders ON ID=OID

WHERE e_mail="client@mail.ru";

SELECT * FROM orders

WHERE accept=1 GROUP BY dataoforder;

SELECT ID, cost, time, dateoforder, product.type, run, pages, format, sub2prod.type, strength, stitch2prod.type

FROM orders

JOIN product ON PID= (SELECT PID FROM ord2prod WHERE OID=2)

JOIN sub2prod ON sub2prod.productID= (SELECT PID FROM ord2prod WHERE OID=2)

JOIN stitch2prod ON stitch2prod.productID= (SELECT PID FROM ord2prod WHERE OID=2)

WHERE ID=2;

SELECT color FROM ink2prod

WHERE productID=(SELECT PID FROM ord2prod WHERE OID=3);

SELECT * FROM clients

JOIN publishing ON ID=(SELECT ID FROM clts2pub WHERE clts2pub.e_mail=clients.e_mail)

WHERE e_mail="client@mail.ru";

UPDATE stitching

SET price=305 WHERE type="glue";

DELETE FROM substrate WHERE type='design' AND strength=140;

UPDATE inks SET on1kpages=290

WHERE color='yellow';

DELETE FROM orders WHERE accept=0;

Листинг 4. Исходные коды представлений

CREATE VIEW FullOrder AS

SELECT ID, ord2prod.PID, product.type, run, pages, sub2prod.type AS paper, strength, stitch2prod.type AS stitching, cost, time

FROM orders, product, ord2prod, sub2prod, stitch2prod

WHERE ord2prod.OID=orders.ID AND sub2prod.productID=(SELECT PID FROM ord2prod WHERE ord2prod.OID=orders.ID)

AND stitch2prod.productID=(SELECT PID FROM ord2prod WHERE ord2prod.OID=orders.ID)

AND ord2prod.PID=product.PID;

CREATE VIEW inkOrder AS

SELECT ID, PID, color, varnishing cost, time

FROM orders, ord2prod, ink2prod, inks

WHERE ink2prod.productID=ord2prod.PID AND OID=ID AND ink2prod.color=inks.color

GROUP BY ID; Листинг 5. Исходные коды хранимых процедур и функций

DELIMITER | create function qtyOrder()

returns int

begin DECLARE qty int;

SELECT count(ID) INTO qty FROM orders WHERE accept=1;

RETURN qty; end

| DELIMITER;

DELIMITER | CREATE FUNCTION profit()

RETURNS real BEGIN

DECLARE prof real;

SELECT sum(cost) INTO prof FROM orders WHERE accept=1;

RETURN prof; END

| DELIMITER; DELIMITER |

CREATE PROCEDURE paper()

BEGIN SELECT *

FROM substrate

WHERE 1; END;

| DELIMITER; Листинг 6. Исходный код создания триггеров.

DELIMITER |

create trigger delorder AFTER DELETE

ON orders FOR EACH ROW

BEGIN DElETE FROM ink2prod WHERE ink2prod.productID=(SELECT PID FROM ord2prod WHERE OID=old.ID);

DELETE FROM sub2prod WHERE sub2prod.productID=(SELECT PID FROM ord2prod WHERE OID=old.ID);

DELETE FROM stitch2prod WHERE stitch2prod.productID=(SELECT PID FROM ord2prod WHERE OID=old.ID);

DELETE FROM plate2prod WHERE plate2prod.productID=(SELECT PID FROM ord2prod WHERE OID=old.ID);

DELETE FROM product WHERE product.PID=(SELECT PID FROM ord2prod WHERE OID=old.ID);

DELETE FROM ord2prod WHERE OID=old.ID;

END | DELIMITER;

delimiter | CREATE TRIGGER delclient AFTER DELETE

ON clients FOR EACH ROW BEGIN

DELETE FROM clts2pub WHERE e_mail=old.e_mail;

END | delimiter;

delimiter | CREATE TRIGGER clntord BEFORE DELETE

ON clients FOR EACH ROW BEGIN

DELETE FROM orders WHERE ID=(SELECT OID FROM clts2Ord WHERE e_mail=old.e_mail);

DELETE FROM clts2Ord WHERE e_mail=old.e_mail;

END | delimiter;

Показать полностью…
Похожие документы в приложении