Проектирование логической модели базы данных

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

Содержание

база материал информация

Введение

1. Проектирование логической модели базы данных

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

1.2 Разработка концептуальной модели базы данных

1.3 Разработка логической модели базы данных

2. Создание физической структуры базы данных

2.1 Разработка физической модели базы данных

2.2 Манипулирование данными

2.3 Организация выборки информации из базы данных

2.4 Разработка представлений для отображения результатов выборки

3. Расширенные возможности управления данных

3.1 Управление транзакциями и блокировками

3.2 Проектирование хранимых процедур

3.3 Разработка механизмов управления данными в базе при помощи триггеров

3.4 Администрирование базы данных

Заключение

Список использованных источников

Приложения



Введение

Постановка задач: проектирование базы данных с помощью языка SQL и с использованием СУБД. Разработка БД (Интернет - магазина) по продаже «Канцелярские товары» с использованием базы данных MySQL.

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

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

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

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

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

Курсовая работа состоит из 3 основных глав где рассматриваются такие темы как: 1. Проектирование логической модели базы данных, 2. Создание физической структуры базы данных и 3. Расширенные возможности управления данных.

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

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

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



1. Проектирование логической модели базы данных

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

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

Пользователями базы данных “Канцелярские товары” будет являться:

· Администратор;

· Глава магазина;

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

· Клиенты;

Определение функции каждого пользователя:

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

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

· Сотрудники - могут так же просмотреть данные о товарах, добавить новые данные, изменить введенные данные, а также оформить заказ или отправить письмо администратору сайта (БД) по каким-либо вопросам.

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

Определение всевозможных вопросов который могут возникнут у пользователей БД:

1) Администратор:

· Как найти необходимую информацию?

· Как оставить комментарий?

· Почему не рассматриваются заявки с жалобами в случае нарушение прав потребителя?

2) Глава магазина:

· Как получить данные о сотрудниках и клиентах?

· Как узнать о нарушениях и жалоб?

· Как узнать об обслуживание сотрудниках?

· Как продвигается деятельность магазина?

3) Сотрудники:

· Как получить заказ?

· Как предоставить товар?

4) Клиенты:

· Как оформить заказ?

· Как получить информация о товарах?

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

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

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

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

Задачами автоматизированной системы являются:

- Запись нового товара

- Поиск товара из существующих и их заменителей

- Удаление товара

- Отображение поставщиков, поставляющих данный товар

- Отображение цен поставщиков, поставляющих товар

- Отображение кратких сведений о товаре

- Сортировка товаров по типу

- Подготовка сведений о поставщиках

- Удаление поставщика

- Добавление поставщика

БД «Канцелярские товары» имеет несколько видов входящих и исходящих документов такие как:

· Список товаров

· Список VIP клиентов

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

· Список поставщиков

· Чеки

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

· Накладные

· Гарантийный талон

· Руководства об использования

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

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

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

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

· обязательные данные;

· ограничения для доменов полей;

· целостность сущностей;

· ссылочная целостность;

· требования конкретного предприятия.

Большая часть перечисленных ограничений задается в операторах CREATE TABLE и ALTER TABLE.

1.2 Разработка концептуальной модели базы данных

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

Сущность - это важная вещь или объект, сведения о котором нужно сохранить.

В базе данных «Канцелярские товары» находится несколько видов сущностей такие как:

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

· Товары - эта таблица которая имеет все необходимая информация о товарах.

· Заказ - будет содержать все заказы.

· Доставка - имеет все информация о доставках.

· Клиенты - хранить все данные клиенты.

· Производители - будет содержать данные о поставщиках.

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

1) Каталог товаров:

· ID_каталог - ключевое поле.

· Название каталог - это список существующих названия каталогов.

2) Товары:

· ID_товара - ключевое поля.

· ID_категория - внешний ключ.

· ID_производства - внешний ключ.

· Название - список наименования товаров.

· Цена - стоимость товара.

· Количество - количества товара.

· Статус - список присутствующих или отсутствующих товаров.

3) Заказ:

· ID_заказ - ключевое поля.

· ID_товара - внешний ключ.

· ID_клиента - внешний ключ.

· Название - наименование товара.

· Количество - количество товаров на заказ.

· Цена - стоимость товара.

· Общая стоимость - общая сумма заказа.

4) Доставка:

· ID_доставка - ключевое поля.

· ID_заказ - ключевое поля.

· ID_товара - внешний ключ.

· ID_клиента - внешний ключ.

· Адрес - адрес доставки.

· Статус - статус доставки.

5) Клиенты:

· ID_клиента - ключевое поля.

· ФИО - фамилия, имя, отчество клиента.

· Телефон - номер телефона клиентов.

· Адрес - адрес клиентов.

Модель сущность-связь (ER-модель) -- модель данных, позволяющая описывать концептуальные схемы предметной области.

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

На рисунке 1.1 предоставлена ER-модель, в которой показаны сущности и ее атрибуты. Каждая из сущностей взаимосвязаны друг с другом определенными действиями, которые указаны в ромбиках.

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

· Связи «один ко многим»;

· Связи «многие ко многим»;

· Связи «один к одному».

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

Рис 1.1. ER-диаграмма базы данных

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

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

Связи «многие ко многим». В связи «многие ко многим» строке таблицы А может сопоставляться несколько строк таблицы Б, и наоборот. Такие связи создаются определением третьей таблицы, которая называется таблицей соединения, чей первичный ключ состоит из внешних ключей А и Б., например, между таблицами authors и titles связь «многие ко многим» определена через связи «один ко многим» каждой из этих таблиц с таблицей titleauthors. Первичный ключ таблицы titleauthors представляет собой сочетание столбца au_id (первичный ключ таблицы authors) и столбца title_id (первичный ключ таблицы titles).

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

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

1.3 Разработка логической модели базы данных

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

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

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

Название таблиц и первичных ключей: katalog_tov.'id_katalog', tovari.'id_tovara', proizvoditeli.'id_proizvoditeli', zakaz.'id_zakaza', dostavka.'id_dostavka', klient.'id_klienta'.

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

Название таблиц и внешних ключей: dostavka.'id_tovara', dostavka.'id_klienta', dostavka.'id_zakaza', zakaz.'id_tovara', zakaz.'id_klient', tovari.'id_kategoria', tovari.'id_proizvod'.

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

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

· избыточность данных;

· потенциальная противоречивость (аномалии).

Под избыточностью понимают повторение данных в разных строках одной таблицы или в разных таблицах БД. 

Рис 1.2. Схема данных базы данных «Выборы».

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

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



2. Создание физической структуры базы данных

2.1 Разработка физической модели базы данных

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

Для создания база данных в СУБД MySQL используется оператор CREATE DATABASE.

Листинг 2.1. SQL-код для создания базы данных «Выборы».

CREATE DATABASE `kans_tovari` CHARACTER SET utf8 COLLATE utf8_bin;

На листинге 2.1. предоставлен код для создания базы данных «Канцелярские товары» в коде также указан язык кодировки utf8.

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

Листинг 2.2. SQL-код для создания таблиц

1. CREATE TABLE tovari (id_tovara INT NOT NULL, id_kategoria INT, id_proizvod INT, naz_tovara VARCHAR(40), sena VARCHAR(10), kol INT, status_t VARCHHAR(30); ALTER TABLE `tovari`.`id_kategoria` ADD CONSTRAINT `FK_tovari` FOREIGN KEY (`id_tovara`) REFERENCES `zakaz` (`id_tovara`);

Рис 2.1. Структура таблицы «Товары».

2. СREATE TABLE katalog_tov (id_katalog INT NOT NULL, naz_katalog VARCHAR(40). ALTER TABLE `katalog_tov` ADD CONSTRAINT `FK_katalog_tov` FOREIGN KEY (`id_katalog`) REFERENCES `tovari` (`id_kategoia`);

Рис 2.2. Структура таблицы «Каталог товаров».

3. СREATE TABLE zakaz (id_zakaz INT NOT NULL, id_tovar INT, id_klient INT, zakaz VARCHAR(40), kol INT, sena INT, ob_summa INT; ALTER TABLE `zakaz` ADD CONSTRAINT `FK_zakaz` FOREIGN KEY (`id_zakaz`) REFERENCES `dostavka` (`id_zakaz`);

Рис 2.3. Структура таблицы «Заказы».

4. СREATE TABLE dostavka (id_dostavka INT NOT NULL, id_tovar INT, id_zakaz INT, id_klient INT, adres_dost VARCHAR(40),status_dost VARCHAR(40);

Рис 2.4. Структура таблицы «Доставка».

5. СREATE TABLE klient (id_klient INT NOT NULL, fio VARCHAR(40), tel INT, adres VARCHAR(40); ALTER TABLE `klient` ADD CONSTRAINT `FK_klient` FOREIGN KEY (`id_klient`) REFERENCES `zakaz` (`id_klient`);

Рис 2.5. Структура таблицы «Клиенты». 

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

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

Листинг 2.3. SQL-код для индексирования полей таблиц.

1. ALTER TABLE `kans_tovari`.`tovari` ADD INDEX `1` (`id_tovara`);

2. ALTER TABLE `kans_tovari`.`katalog_tov` ADD INDEX `2` (`id_katalog`);

3. ALTER TABLE `kans_tovari`.`zakaz` ADD INDEX `3` (`id_zakaz`);

4. ALTER TABLE `kans_tovari`.`dostavka` ADD INDEX `4` (`id_dostavka`);

5. ALTER TABLE `kans_tovari`.`klient` ADD INDEX `5` (`id_klient`);

6. ALTER TABLE `kans_tovari`.`proizvoditeli` ADD INDEX `6` (`id_proizvoditel`);

На листинге выше предоставлен код для создания индекса для полей таблиц в данном случае индексирование было проведено для всех таблиц базы данных «Канцелярские товары».

В данной под главе было описано, как организовывается создание БД, создание таблиц и проведена индексация полей таблиц.

2.2 Манипулирование данными

Data Manipulation Language (DML) (язык управления ( или манипулирования) данными) -- это семейство компьютерных языков, используемых в компьютерных программах или пользователями баз данных для получения, вставки, удаления или изменения данных в базах данных. Операции ввода, изменения и удаления данных являются командами языка DML. Одна или несколько подряд идущих операций DML называются транзакциями.

К базовым средствам манипулирования данными языка SQL относятся "поисковые" варианты операторов UPDATE и DELETE. Эти варианты называются поисковыми, потому что при задании соответствующей операции задается логическое условие, налагаемое на строки адресуемой оператором таблицы, которые должны быть подвергнуты модификации или удалению. Кроме того, в такую категорию языковых средств входит оператор INSERT, позволяющий добавлять строки в существующие таблицы.

Команда INSERT используется для ввода строк в таблицу. При использовании синтаксиса, указанного ниже в один момент сможете ввести только одну строку:

Листинг 2.4. SQL-код для добавления данных в таблицу.

INSERT INTO kans_tovari.katalog_tov(id_katalog,naz_katalog) VALUES ( '1021','pismennie_prinad');

Рис 2.6. Результат добавления данных.

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

Листинг 2.5. SQL-код для удаления данных из таблицы.

DELETE FROM kans_tovari.tovari WHERE id_tovara=4081;

DML есть команда, которая может изменять данные. Так вот это команда UPDATE, которая изменяет данные при этом она может за одну команду изменить как одну, так и все строки таблицы, все зависит от тех условиях, которые указываем в коде.

Листинг 2.6. SQL-код для обновления данных в таблице.

UPDATE kans_tovari.tovari SET status_t='prisutstvuet' WHERE naz_tovara='Ruchka';

Рис 2.7. Внешний вид таблицы до запуска SQL-кода.

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

Рис 2.8. Результат обновления данных.



Как видно по рисунку 2.8. статус товара была изменена или так можно сказать обновлена с помощью SQL-кода, указанного в листинге 2.6.

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

2.3 Организация выборки информации из базы данных

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

SELECT --это оператор DML языка SQL, возвращающий набор данных (выборку) из базы данных, удовлетворяющих заданному условию.

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

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

Листинг 2.7. SQL-код простой выборки данных.

SELECT tovari.naz_tovara, tovari.sena FROM tovari WHERE tovari.kol<20;

Рис 2.9. Результат простой выборки.

На рисунке 2.9. показано называние и цена товара которая количество меньше 20.

SELECT klient.`fio`, klient.`tel` FROM klient WHERE klient.`adres`='n.J Rasulov';

Рис 2.10. Результат простой выборки.

На рисунке 2.10. отображено ФИО и телефонный номер клиентов, которые живут в Дж. Расуловскому району.

Листинг 2.8. SQL-код многотабличной выборки данных.

SELECT tovari.`naz_tovara`, tovari.`kol`, proizvoditeli.`naz_proizvod` FROM tovari, proizvoditeli WHERE tovari.`id_proizvod`=proizvoditeli.`id_proizvoditel` AND

proizvoditeli.`strana_proiz`='Russia';

Рис 2.11. Результат выборки из двух таблиц.

На рисунке предоставлены данные товары, которые произведено только в России.

Листинг 2.9. SQL-команда для создания вложенного запроса.

SELECT naz_tovara, sena FROM tovari WHERE id_proizvod=(SELECT id_proizvoditel FROM proizvoditeli WHERE naz_proizvod='Macma');

Рис 2.12. Результат вложенного запроса.

На рисунке показан данные товары которые произведено в фирме “Macma”.

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

2.4 Разработка представлений для отображения результатов выборки

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

Листинг 2.9. SQL-код для создания представлений.

CREATE VIEW `Iran` AS

(SELECT`tovari`.`naz_tovara` AS `naz_tovara`, `tovari`.`sena` AS `sena` FROM `tovari` WHERE (`tovari`.`id_proizvod` = (SELECT `proizvoditeli`.`id_proizvoditel` FROM `proizvoditeli` WHERE (`proizvoditeli`.`naz_proizvod` = 'Persia'))))

Рис 2.13. Результат представления «Iran»

На рисунке представлено товары которые произведено в фирме “Persia”.

Листинг 2.10. SQL-код для создания представлений.

CREATE VIEW `kans_tovari`.`No exist` AS

(SELECT naz_proizvod FROM proizvoditeli WHERE EXISTS(SELECT * FROM tovari WHERE proizvoditeli.`id_proizvoditel`=tovari.`id_proizvod`))

Рис 2.14. Результат представления «no exsist»

На рисунке 2.14 показано название фирмы, которые их товары отсутствует в базе.

В данной под главе были созданы и описаны представления для различных таблиц из базы данных «Канцелярские товары».



3. Расширенные возможности управления данных

3.1 Управление транзакциями и блокировками

Транзакция - это последовательность операторов SQL, выполняющихся как единая операция, которая не прерывается другими клиентами. То есть, пока происходит работа с записями таблицы (их обновление или удаление), никто другой не может получить доступ к этим записям. Транзакции позволяют объединять операторы в группу и гарантировать, что все операции внутри группы будут выполнены успешно. Если часть транзакции выполняется со сбоем, результаты выполнения всех операторов транзакции до места сбоя отменяются, приводя базу данных к виду, в котором она была до выполнения транзакции. Следует обратить внимание, что транзакции имеют смысл только в случае с типами таблиц, которые их поддерживают: InnoDB и BDB. Если существующие таблицы имеют другой тип, например, MyISAM, для работы с транзакциями его следует изменить. Системы, поддерживающие возможности транзакций, часто еще характеризуют как обеспечивающие свойства ACID (ACID является сокращением Atomatic(атомарный), Consistent (целостный), Isolated(изолированный), Durable(длительный)).

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

- Целостность. БД является целостной до и после выполнения транзакции

- Изоляция. Отдельные транзакции не влияют на работу друг друга.

- Длительность. При выполнении транзакции все ее результаты сохраняются в БД.

Листинг 3.1. SQL-команда для создания транзакции.

SET Autocommit = 0

INSERT INTO tovari VALUES(4073, 1026, 3041, 'Plastilin', 4, 20, 'prisutstvuet');

INSERT INTO tovari VALUES(4074, 1025, 3045, 'Bumaga A3', 22, 10, 'prisutstvuet')

Рис 3.1. Результат запуска транзакций.

На рисунке 3.1. видно, что данные добавились по очереди после выполнение команда Auto commit.

Листинг 3.2. SQL-команда для создания транзакции.

START TRANSACTION;

INSERT INTO klient VALUES(2037, 'Sharipov S', 905015960, 'g.Khujand');

INSERT INTO klient VALUES(2038, 'Yusupov F', 987718880, 'n.Spitamen');

ROLLBACK;

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



Рис 3.2. Результат запуска транзакций.

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

Листинг 3.3. SQL-код для блокировки таблиц.

LOCK TABLES tovari_info WRITE;

На листинге 3.3. приведен SQL команда который блокирует таблица tovari_info для ввода и изменения данных.

Листинг 3.4. SQL-код для разблокировки таблиц.

UNLOCK TABLES tovari_info READ;

На листинге 3.4 приведено SQL - код который разблокирует таблица tovari_info только для чтения данных.

В данной под главе были созданы два вида транзакций, и организована блокировка и разблокировка для некоторых из таблиц.

3.2 Проектирование хранимых процедур

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

Листинг 3.5. SQL-команда для создания процедуры.

DELIMITER $$

USE `kans_tovari`$$

DROP PROCEDURE IF EXISTS `procedure1`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure1`()

BEGIN

SELECT proizvoditeli.`naz_proizvod`, tovari.`naz_tovara`, tovari.`sena`, tovari.`kol` FROM tovari, proizvoditeli

WHERE tovari.`id_proizvod`=proizvoditeli.`id_proizvoditel`;

END$$

DELIMITER;

CALL procedure1;

В этой процедуре имеются Select запрос из двух таблиц и результат вызывается с помощью команда `Call procedure1'.

Рис 3.3. Результат запуска процедура

На рисунке 3.3. приведено результат листинга 3.5.

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

Листинг 3.6. Создание процедуры

DELIMITER $$

USE `kans_tovari`$$

DROP PROCEDURE IF EXISTS `new1`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `new1`(IN id_tov INT)

BEGIN

DROP TABLE IF EXISTS `selectproc`;

CREATE TABLE selectproc(id_t INT, naz_t VARCHAR(40), naz_k VARCHAR(40), naz_p VARCHAR(40), sena_t INT, kol_t INT);

INSERT INTO selectproc SELECT tovari.`id_tovara`, tovari.`naz_tovara`, katalog_tov.`naz_katalog`, proizvoditeli.`naz_proizvod`, tovari.`sena`, tovari.`kol`

FROM tovari, proizvoditeli, katalog_tov

WHERE tovari.`id_kategoria`=katalog_tov.`id_katalog` AND tovari.`id_proizvod`=proizvoditeli.`id_proizvoditel`;

END$$

DELIMITER;

Рис 3.4. Результат запуска процедура.

На рисунке 3.4. показано результат листинга 3.6.



3.3 Разработка механизмов управления данными в базе при помощи триггеров

При модификации, удалении или добавлении данных рабочей единицей является ряд (row, строка, запись). Курсор - это указатель на ряд. Курсор в SQL - это область в памяти БД, которая предназначена для хранения последовательности оператора SQL.

Работа с курсорами происходит по следующему алгоритму:

· при помощи инструкции DECLARE CURSOR связывается имя курсора с выполняемым запросом;

· оператор OPEN выполняет запрос, связанный с курсором, и устанавливает курсор перед первой записью результирующей таблицы;

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

· оператор CLOSE прекращает доступ к результирующей таблице и ликвидирует связь между курсором и результирующей таблицей.

А теперь попробуем создать триггеры.

Листинг 3.7. Триггер на обновление данных

DELIMITER $$

USE `kans_tovari`$$

DROP TRIGGER IF EXISTS `trig_dost`$$

CREATE

DEFINER = 'root'@'localhost'

TRIGGER `trig_dost` BEFORE UPDATE ON `dostavka`

FOR EACH ROW BEGIN

IF dostavka.status='nD' THEN UPDATE dostavka SET dostavka.status='nedostavleno';

ELSE UPDATE dostavka SET dostavka.status='Dostavleno';

END IF;

END$$

DELIMITER;

Это триггер с использованием условия результатом которая является при обновления таблица доставка если статус доставка равно “nD” то будет не доставлено в обратном случае будет доставлено.

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

Листинг 3.8. Триггер для хранения удаленных данных

DELIMITER $$

USE `kans_tovari`$$

DROP TRIGGER IF EXISTS `tr2`$$

CREATE

DEFINER = 'root'@'localhost'

TRIGGER `tr2` AFTER UPDATE ON `tovari`

FOR EACH ROW BEGIN

INSERT INTO tovari_info VALUES(id_tov = tovari.`id_tovara`, naz_tovara = tovari.`naz_tovara`, 'otsutstvuet');

END$$

DELIMITER;

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

3.4 Администрирование базы данных

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

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

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

Листинг 3.9. Создание учетных записей

CREATE USER 'admin'@'localhost' IDENTIFIED BY 's34dfr57j';

CREATE USER 'fornewuser'@'localhost' IDENTIFIED BY '12345';

CREATE USER 'vipuser'@'localhost' IDENTIFIED BY '0000';

Для того, чтобы разные пользователи имели различные полномочия при роботе с базами данных, с конкретной базой данных, с отдельно взятой таблице или даже с полями таблицы, в MySQL существует понятие ПРИВИЛЕГИЙ. Назначаются такие привилегии оператором GRANT.

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

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

Листинг 3.10. Привилегии для VIP пользователья

GRANT ALL PRIVILEGES ON *.* TO 'vipuser'@'localhost';

Список привилегий для VIP пользователья:



Рис 3.5. Привилегия VIP пользователя.

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

GRANT SELECT ON kans_tovari.`tovari` TO 'fornewuser'@'localhost';

GRANT SELECT ON kans_tovari.`dostavka` TO'fornewuser'@'localhost';

GRANT SELECT ON kans_tovari.`zakaz` TO 'fornewuser'@'localhost';

В списке таблиц для этого пользователя будут только те, которые были разрешены для доступа в коде:

Рис 3.6. Доступ к таблицам для новых пользователей

Листинг 3.11. Привилегии для VIP пользователей

GRANT SELECT, INSERT, DELETE, UPDATE ON kans_tovari.`tovari` TO 'vipuser'@'localhost';

GRANT SELECT, INSERT, DELETE, UPDATE ON kans_tovari.`dostavka` TO 'vipuser'@'localhost';

GRANT SELECT, INSERT, DELETE, UPDATE ON kans_tovari.`zakaz` TO 'vipuser'@'localhost';

GRANT SELECT, INSERT, DELETE, UPDATE ON kans_tovari.`katalog_tov` TO 'vipuser'@'localhost';

Рис 3.7. Доступ к таблицам для VIP пользователей

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



Рис 3.8. Создание резервной копии данных

Здесь данные будут экспортироваться в указанное место под введенным именем в формате .sql.

Если после заполнения всех полей таблиц, необходимо узнать занятую память можно использовать следующую запись (листинг 3.12.)

Листинг 3.12. Определение занятой памяти

CHECKSUM TABLE kans_tovari.tovari;

Рис 3.9. Занятая память таблицы «товары»

Так же можно проанализировать таблицу, оператор ANALYZE TABLE анализирует и сохраняет распределение индексов таблицы (листинг 3.13.).

Листинг 3.13. Анализ таблицы

ANALYZE TABLE tovari;

Выводится результат анализированные таблицы:

Рис 3.10. Результат анализа

В таблице говорится, что таблица «Товары» проанализирована, информация имеет целостность.

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



Заключение

В результате выполнения данной работы была создана база данных `Канцелярские товары'.

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

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

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

· создание физической структуры базы данных;

· расширенные возможности управления данных.

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

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

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

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

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



Список использованных источников

1. Коггзолл Джон. PHP 5. М.: Издательский дом «Вильямс», 2006 -752 стр.

2. Мотев А.А. Уроки MySQL. Самоучитель. СП.: БХВ-Петербург, 2006 -208 стр.

3. Кузнецов М. В., Симдянов И. В. Самоучитель MySQL СПб.: БХВ - Петербург, 2007 - 560 стр.

4. Конверс Тим, Парк Джойс, Морган Кларк. PHP 5 и MySQL. Библия пользователя. М.: Издательский дом «Вильямс», 2006 - стр. 1216.



Приложение

SELECT naz_tovara, sena FROM tovari WHERE id_proizvod=(SELECT id_proizvoditel FROM proizvoditeli WHERE naz_proizvod='Macma');

SELECT naz_proizvod FROM proizvoditeli WHERE EXISTS(SELECT * FROM tovari WHERE proizvoditeli.`id_proizvoditel`=tovari.`id_proizvod`);

SELECT naz_proizvod FROM proizvoditeli WHERE NOT EXISTS(SELECT * FROM tovari WHERE proizvoditeli.`id_proizvoditel`=tovari.`id_proizvod`);

SELECT naz, AVG(SUM) FROM (SELECT katalog_tov.`naz_katalog` AS naz, SUM(kol) AS SUM FROM tovari, katalog_tov WHERE

katalog_tov.`id_katalog`=tovari.`id_kategoria` GROUP BY tovari.`id_kategoria`) AS sumtbl GROUP BY naz;

CREATE TABLE tovari_info(

id_tovar INT PRIMARY KEY AUTO_INCREMENT NOT NULL,

katalog TINYTEXT,

proizvoditel TINYTEXT,

naz_tovar TINYTEXT,

sena VARCHAR(10),

kol VARCHAR(10))

SELECT t.id_tovara AS id_tovar, k.naz_katalog AS katalog, p.naz_proizvod AS proizvoditel,

t.naz_tovara AS naz_tovar, t.sena AS sena, t.kol AS kol FROM

tovari AS t, proizvoditeli AS p, katalog_tov AS k WHERE

t.id_kategoria=k.id_katalog AND

t.id_proizvod=p.id_proizvoditel;

TRUNCATE TABLE tovari_info;

DROP TABLE tovari_info;

INSERT INTO tovari_info(

id_tovar INT PRIMARY KEY AUTO_INCREMENT NOT NULL,

katalog TINYTEXT,

proizvoditel TINYTEXT,

naz_tovar TINYTEXT,

sena VARCHAR(10),

kol VARCHAR(10))

SELECT t.id_tovara AS id_tovar, k.naz_katalog AS katalog, p.naz_proizvod AS proizvoditel,

t.naz_tovara AS naz_tovar, t.sena AS sena, t.kol AS kol FROM

tovari AS t, proizvoditeli AS p, katalog_tov AS k WHERE

t.id_kategoria=k.id_katalog AND

t.id_proizvod=p.id_proizvoditel;

Размещено на Allbest.ru

...