Разработка базы данных для магазина цифровой техники

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

Количество атрибутов  в отношении называется степенью, или рангом, отношения.

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

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

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

 

3. Этапы проектирования баз данных

 

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

1. Концептуальное (инфологическое) проектирование — построение семантической модели предметной области, то есть информационной модели наиболее высокого уровня абстракции. На этом этапе осуществляется анализ предметной области (объекты и связи между ними), анализ потребностей пользователей, анализ существующих и будущих прикладных программ. После чего происходит построение информационной структуры.
2. Логическое (даталогическое) проектирование — создание схемы базы данных на основе конкретной модели данных. Преобразование концептуальной модели в логическую модель, как правило, осуществляется по формальным правилам. Этот этап может быть в значительной степени автоматизирован. На этапе логического проектирования учитывается специфика конкретной модели данных.
3. Физическое проектирование — создание схемы базы данных для конкретной СУБД. Специфика конкретной СУБД может включать в себя ограничения на именование объектов базы данных, ограничения на поддерживаемые типы данных и т.п. Кроме того, специфика конкретной СУБД при физическом проектировании включает выбор решений, связанных с физической средой хранения данных, создание индексов и т.д. [1].
 

4. Нормализация таблиц

 

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

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

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

Рассмотрим  нормальные формы:

1. Первая нормальная форма:

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

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

3. Чтобы таблица находилась в третьей нормальной форме, необходимо, чтобы не ключевые столбцы в ней не зависели от других не ключевых столбцов, а зависели только от первичного ключа.
4. Нормальная форма Бойса-Кодда требует, чтобы в таблице был только один потенциальный первичный ключ.
5. Для приведения таблицы, находящейся в нормальной форме Бойса-Кодда, к четвертой нормальной форме необходимо устранить имеющиеся в ней многозначные зависимости. То есть обеспечить, чтобы вставка / удаление любой строки таблицы не требовала бы вставки / удаления / модификации других строк этой же таблицы.
6. Таблицу, находящуюся в четвертой нормальной форме и, казалось бы, уже нормализованную до предела, в некоторых случаях еще можно бывает разбить на три или более (но не на две!) таблиц, соединив которые, мы получим исходную таблицу. Получившиеся в результате такой, как правило, весьма искусственной, декомпозиции таблицы и называют находящимися в пятой нормальной форме. Формальное определение пятой нормальной формы таково: это форма, в которой устранены зависимости соединения. В большинстве случаев практической пользы от нормализации таблиц до пятой нормальной формы не наблюдается.
 

2. Построение базы данных для сети магазинов цифровой техники

 

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

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

1. Посетители магазина
2. Продавцы-консультанты
3. Директор магазина

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

Пользователей и продавцов будет интересовать следующая информация:

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

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

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

Здесь можно  выделить 5 основных таблиц, которые  необходимо построить:

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

Для каждого  поля необходимо установить свой тип  данных. Для поля сенсорный выберем тип «логический». Так будет проще обозначать, сенсорный экран у телефона или нет. В поле группа сделаем подстановку со списком значений для удобства ввода. В источник строк введем: "сотовые телефоны";"смартфоны";"коммуникатор". В итоге, когда мы будем выбирать группу товара, нам будет предлагаться один из этих трех вариантов. Для поля память создадим маску ввода: 0AAaa. Эта запись означает, что мы обязаны ввести цифру, затем 2 раза обязаны внести либо букву, либо цифру и ещё 2 раза можем ввести букву или цифру. Данная маска ввода позволяет оптимально удовлетворить всем возможным вариантам значений памяти. Для поля память тоже создадим маску ввода: 99«Мпикс». Мы можем ввести до двух цифр, а после них запись Мпикс будет появляться автоматически. У поля гарантия маска вода будет выглядеть так: 00«мес».

Ключевым поем сделаем код товара.

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

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

Ключевым полем  будет являться товарный чек и  табельный номер продавца.

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

 

 

 

 

3. Необходимо  создать таблицу поставщиков, которая будет включать в себя следующие поля: наименование поставщика (это поле будет являться ключевым), его ИНН и КПП, адрес, телефон, инициалы директора по работе с магазинами и удаленность базы от магазина. Создаем маску ввода для поля телефон и удаленность от магазина.

4. Для того, чтобы  знать когда и кем была поставлена  партия определенного товара  нужно создать таблицу поставки. Она будет включать в себя  наименование поставщика, номер  накладной, код и количество  поставленного товара, дату поставки. Создадим маску ввода для поля номер товарной накладной: «000»9999. Это значит, что 000 будут фиксировано выводиться в начале номера накладной, а затем мы должны ввести ещё 4 цифры. Таблицу поставки товаров создаем отдельно для сотовых телефонов и ноутбуков. Номер накладной будет ключевым полем.

 

5. Таблица личные дела  продавцов. Здесь будут данные  обо всех продавцах магазина: ФИО, табельные номер (ключевое  поле) , адрес, з/п. 

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

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

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

В конструкторе запросов мы добавляем таблицы Ноутбуки и  нетбуки и Работа продавцов по ноутбукам. Выбираем поля наименование товара, количество и дата продажи. На дату продажи накладываем ограничение Between #01.03.2012# And #31.03.2012#. Это означает, что будут выводиться продажи  только за март.

Результат запроса:

Создадим аналогичный  запрос для сотовых телефонов:

 

 

 

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

 

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

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

 

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

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

Сотовые телефоны:

• Иванов И.И. продал сотовых телефонов на сумму 1 496 810,00р.
• Котова Т.В. продала сотовых телефонов на сумму 1 094 500,00р.
• Кукушкина П.И. продала сотовых телефонов на сумму 489 540,00р.
• Петров П.С. продал сотовых телефонов на сумму 521 200,00р.
• Сидоров А.Н. продал сотовых телефонов на сумму 566 580,00р.

Ноутбуки и нетбуки:

• Иванов И.И. продал ноутбуков на сумму 189 255,00р.
• Котова Т.В. продала ноутбуков на сумму  288 792,00р.
• Кукушкина П.И. продала ноутбуков на сумму 267 095,00р.
• Петров П.С. продал ноутбуков на сумму 180 839,00р.
• Сидоров А.Н. продал ноутбуков на сумму 180 696,00р.

Строим сводную диаграмму  по:

 

 Сотовым телефонам:

 

Ноутбукам:

 

Создаем перекрестные запросы  по продажам каждого продавца

1. Сколько телефонов  (ноутбуков) какой марки продал  каждый продавец за весь год

 

 

2. Сколько телефонов (ноутбуков) продал каждый продавец за каждый месяц

 

 

Для удобства покупателей  создадим запрос на среднюю оценку сотовых телефонов (ноутбуков). В  конструкторе запросов для графы  оценка введем обязательное условие Is not null.