Логическая модель данных
Логическая модель является основой базы данных, она должна отображать взаимосвязи между реляционными таблицами.Междуреляционнымитаблицами могут быть следующие типы связей 1:1, 1:Б и М:М. Наиболее распространенной связью является связь 1:М. Связь 1:1 встречается реже, так как данные, между которыми существует такой тип связи в подавляющем большинстве случаев входят в состав однойреляционнойтаблицы.Связь М:М непосредственно не поддерживается в реляционными СУБД.Для реализации такой связи необходимо создавать дополнительную реляционную таблицу, которая будет играть роль связующей.Связующая таблица должна обязательно содержать первичные ключи таблиц, между которыми устанавливается связь.
В нашей базе данных один поставщик может поставлять различные товары, однако определенный товар может поставлять только один поставщик, поэтому между таблицами «Поставщик» и «Товар» тип связи один ко многим ( 1 : n ).
Группа товара показывает характеристики множество товаров, относящихся к одному типу, но определенный товар должен относиться только к одному типу товара и соответственно иметь только одну характеристику, следовательно между таблицами «Группа товара» и «Товар» тип связи один ко многим ( 1 : n ).
Следующий этап - этореализация продукции. Наверное, этот этап самый значимый для магазина, так как отражает основной вид деятельности – торговля. Договор может заключаться только на один вид товара, но на определенный товар может быть заключено множество договоров, следовательно между таблицами «Товар» и «Договор» тип связи один ко многим ( 1 : n ).
Покупатель может заключать множество договоров на покупку товара, но каждый договор должен соответствовать только одному покупателю. Таблицы «Договор» и «Покупатель» имеют тип связи один ко многим ( 1 :n ).
В договоре так же указывается код сотрудника, который заключает договор на продажу товара. Сотрудник может заключать множество договоров, но определенный договор должен содержать код только одного сотрудника, следовательно таблицы «Договор» и «Сотрудники» имеют тип связи один ко многим ( 1 : n ).
Логическая модель предметной области «Антикварный магазин» представлена на рисунке 2.
Рисунок 2 . Логическая модель данных.
Физическая модель данных
На еще более низком уровне находится физическая модель данных. Физическая модель данных описывает данные средствами конкретной СУБД. Отношения, разработанные на стадии формирования логической модели данных, преобразуются в таблицы, атрибуты становятся столбцами таблиц, для ключевых атрибутов создаются уникальные индексы, домены преображаются в типы данных, принятые в конкретной СУБД.
Ограничения, имеющиеся в логической модели данных, реализуются различными средствами СУБД, например, при помощи индексов, декларативных ограничений целостности, триггеров, хранимых процедур. При этом опять-таки решения, принятые на уровне логического моделирования определяют некоторые границы, в пределах которых можно развивать физическую модель данных. Точно также, в пределах этих границ можно принимать различные решения. Например, отношения, содержащиеся в логической модели данных, должны быть преобразованы в таблицы, но для каждой таблицы можно дополнительно объявить различные индексы, повышающие скорость обращения к данным. Многое тут зависит от конкретной СУБД.
При разработке физической модели данных возникают вопросы: хорошо ли спроектированы таблицы? Правильно ли выбраны индексы?
Ниже приведены структуры таблиц БД «Магазин по продаже антиквариата».
Таблица 1. «Договор»
Таблица 2. «Товар»
Таблица 3. «Сотрудники»
Таблица 4. «Поставщики»
Таблица 5. «Покупатель»
Таблица 6. «Группа товара»
SQL-код и запросы для работы с базой данных
SQL — «язык структурированных запросов») —универсальныйкомпьютерный язык, применяемый для создания, модификации и управления данными вреляционных базах данных. SQL основывается наисчислении кортежей.
Изначально, SQL был основным способом работы пользователя с базой данныхи позволял выполнять следующий набор операций:
создание в базе данных новой таблицы;
добавление в таблицу новых записей;
изменение записей;
удаление записей;
выборка записей из одной или нескольких таблиц (в соответствии с заданным условием);
изменение структур таблиц.
Со временем, SQL усложнился — обогатился новыми конструкциями, обеспечил возможность описания и управления новыми хранимыми объектами (например, индексы, представления, триггеры и хранимые процедуры) — и стал приобретать черты, свойственные языкам программирования.
При всех своих изменениях, SQL остаётся единственным механизмом связи между прикладным программным обеспечением и базой данных. В то же время, современные СУБД, а, также, информационные системы, использующие СУБД, предоставляют пользователю развитые средства визуального построения запросов.
Каждое предложение SQL — это либо запрос данных из базы, либо обращение к базе данных, которое приводит к изменению данных в базе. В соответствии с тем, какие изменения происходят в базе данных, различают следующие типы запросов:
запросы на создание или изменение в базе данных новых или существующих объектов (при этом в запросе описывается тип и структура создаваемого или изменяемого объекта);
запросы на получение данных;
запросы на добавление новых данных (записей)
запросы на удаление данных;
обращения к СУБД.
Основным объектом хранения реляционной базы данных является таблица, поэтому все SQL-запросы— это операции над таблицами. В соответствии с этим, запросы делятся на
запросы, оперирующие самими таблицами (создание и изменение таблиц);
запросы, оперирующие с отдельными записями (или строками таблиц) или наборами записей.
Каждая таблица описывается в виде перечисления своих полей (столбцов таблицы) с указанием
типа хранимых в каждом поле значений;
связей между таблицами (задание первичных и вторичных ключей);
информации, необходимой для построения индексов.
Запросы первого типа, в свою очередь, делятся на запросы, предназначенные для создания в базе данных новых таблиц, и на запросы, предназначенные для изменения уже существующих таблиц. Запросы второго типа оперируют со строками, и их можно разделить на запросы следующего вида:
вставка новой строки;
изменение значений полей строки или набора строк;
удаление строки или набора строк.
Самый главный вид запроса — это запрос, возвращающий (пользователю) некоторый набор строк, с которым можно осуществить одну из трёх операций:
просмотреть полученный набор;
изменить все записи набора;
удалить все записи набора.
Таким образом, использование SQL сводится, по сути, к формированию всевозможных выборок строк и совершению операций над всеми записями, входящими в набор.
Постоим запросы для нашей базы данных.
При работе с базой данных «Антикварный магазин» может понадобиться выборочная информация. Предположим, что нам необходимо вывесит информацию о договоре, номер которого равен 234.Тогда запрос на SQL – коде будет выглядеть следующим образом:
Select "ДОГОВОР".* From "ДОГОВОР" where ID_CONTRACT=234;
Результат запроса:
Рисунок 3 . Визуализациязапроса.
В случае форс-мажорных обстоятельствах – банкротства или грабежа, необходимо было знать сумму всех товаров в магазине, поэтому организуем следующий запрос: « Вывести сумму цен всех товаров в магазине».SQL – коде будет выглядеть следующим образом:
SELECT SUM("ТОВАР".PRICE ) SUM_OF_PRICE FROM "ТОВАР";
Результат запроса:
Рисунок 4. Визуализациязапроса.
Дляучета товара необходимо знать всю информацию о нем, наименование при этом должны сортироваться в алфавитном порядке:
select * from "ТОВАР" order by NAME_PRODUCTA ASC ;
Результат запроса:
Рисунок 5. Визуализациязапроса.
При заключении договора, сотрудник может посмотреть в базе данных информацию о покупателе, чтобы уменьшить время заключения договора на продажу товара. Предположим, что фамилия клиента начинается на букву «С»:
select "ПОКУПАТЕЛЬ".surnamefrom "ПОКУПАТЕЛЬ" whereSURNAMElike 'С%';
Результат запроса:
Рисунок 6. Визуализациязапроса.
В магазин иногда обращаются покупатели, которых не устраивает качество товара. Покупатель Светлана обратилась в магазин с претензией о ненадлежащем качестве товара и попросила вернуть ей стоимость товара - 20500. После принятие данной претензии мы составили акт возврата товара, в котором необходимо указать номер поставщика. Тогда запрос на SQL – коде будет выглядеть следующим образом:
select "ТОВАР".ID_SUPPLIER from "ТОВАР", "ПОКУПАТЕЛЬ" where "ТОВАР".price=20500 AND "ПОКУПАТЕЛЬ".NAME='СВЕТЛАНА';
Результат запроса:
Рисунок 7. Визуализациязапроса.
Часто в магазин обращаются покупатели, которые хотят приобрести товар в пределах определенной стоимости. Предположим, что диапазон цены находится от 100 до 10000 тысяч рублей. SQL – коде будет выглядеть следующим образом:
selectdistinct "ТОВАР".* FROM "ТОВАР" where "ТОВАР".PRICE between 100 and 10000;
Результат запроса:
Рисунок 8.Визуализациязапроса.
В любом магазине существует система премирования персонала. Премию получают сотрудники, которые оформляли заказы. Бухгалтерии необходимы списки сотрудников, которые в данном квартале не получат премию, то есть продавцов, которые не оформили ни одного заказа. Тогда запрос будет выглядеть таким образом:
select "СОТРУДНИКИ".* from "ДОГОВОР", "СОТРУДНИКИ" where "СОТРУДНИКИ".ID_EMPLOYEE notin (select "ДОГОВОР".ID_EMPLOYEE FROM "ДОГОВОР");
Результат запроса:
Рисунок 9.Визуализациязапроса.
Контактна информация в таблице «ПОКУПАТЕЛЬ» стала неактуальной, следовательно ее нужно удалить. Напишем следующий запрос:
ALTER TABLE "ПОКУПАТЕЛЬ" DROP CONTACT_INFORMATION;
Результат запроса:
Рисунок 10.Визуализациязапроса.
Для составления докладной нам необходим перечень поставщиков, которые доставляют больше одного товара. Тогда запрос на SQL – коде будет выглядеть следующим образом:
selectTITLEfrom "ПОСТАВЩИК" where 1<(selectcount(*) from "ТОВАР"
where "ПОСТАВЩИК".ID_SUPPLIER="ТОВАР".ID_SUPPLIER);
Результат запроса:
Рисунок 11.Визуализациязапроса.
Магазин существует 5000 дней. Премию получают сотрудники,заключившие договора на сумму 5000. Бухгалтерии необходимы списки сотрудников, которые в данном квартале заключили договора на эту сумму. Тогда запрос будет выглядеть таким образом:
selectdistinctSURNAMEfrom "СОТРУДНИКИ", "ДОГОВОР" where
500000<( select sum(PRICE) from "ТОВАР" where "СОТРУДНИКИ".ID_EMPLOYEE="ДОГОВОР".ID_EMPLOYEE
and "ДОГОВОР".ID_PRODUCT="ТОВАР".ID_PRODUCT group by ID_PRODUCT) ;
Результат запроса:
Рисунок 12.Визуализациязапроса.
Составить ведомость о продаже товара на данный момент, а именно вывести информацию о том какой товар заказал каждый покупатель:
selectdistinct "ПОКУПАТЕЛЬ".surname, "ТОВАР".name_productafrom "ТОВАР", "ПОКУПАТЕЛЬ", "ДОГОВОР"
where "ДОГОВОР".id_buyer="ПОКУПАТЕЛЬ".id_buyer AND "ТОВАР".id_product="ДОГОВОР".id_product;
Результат запроса:
Рисунок 13.Визуализациязапроса.
Заключение
В результате выполнения курсовой работы были выполнены все поставленные цели, была разработана база данных для антикварного магазина и изучены основные разделы реляционных баз данных, освоены методы проектирования баз данных и работы с базами данных в среде конкретной СУБД (ibExpert).