Проектирование реляционной БД с помощью нормализации

 

Министерство образования и науки РФ

Хакасский Технический Институт - филиал ВГАОУ

ВПО «Сибирский Федеральный Университет»

Кафедра «Прикладная информатика и экономика»

Расчетно - пояснительная записка к курсовой работе по дисциплине «Базы данных»

Тема: «Проектирование реляционной БД с помощью нормализации»

Вариант № 3

Абакан 2013

Задание

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

Описание предметной области:

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

БД должна уметь отвечать на вопросы, подобные следующим:

Сколько в имеется в наличии программного обеспечения и какие из них поставляются поставщиком «1С»?

С кем были заключены договоры на поставки?

На какую сумму закуплена продукция?

Какая продукция дороже 1000 рублей?

План

Введение

Раздел 1. Логическое проектирование

Раздел 2. Физическое и даталогическое проектирование

Часть 1. Проектирование БД в СУБД Visual FoxPro

1.1 Создание проекта

1.2 Создание базы данных

.3 Создание форм

.4 Запросы и отчеты

.4 Главное меню

.5 Создание приложения

Часть 2. SQL

Заключение

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

Введение

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

Традиционно одной из наиболее распространенных в России и странах СНГ является СУБД «Visual FoxPro». «Visual FoxPro» предоставляет в распоряжение пользователя много самых разнообразных панелей инструментов для работы с базами данных, формами, отчетами и запросами. Эти панели инструментов содержат набор кнопок, причем этот набор зависит от назначения конкретной панели инструментов. Меню, панели инструментов, диалоговые окна Visual FoxPro характерны для среды Windows.

Язык SQL является стандартным реляционным языком и в настоящее время поддерживается практически всеми современными СУБД. Язык SQL является языком программирования, который применяется для организации взаимодействия с базой данных. SQL является декларативным языком, в нем нет операторов цикла или логических операторов, поэтому он обычно встраивается в какой-либо процедурный базовый язык.

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

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

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

Раздел 1. Логическое проектирование

Логическое проектирование основано на процессе нормализации.

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

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

БД должна содержать как минимум следующую информацию:

)Данные поставщиков, ФИО, адрес, юр. название;

)Название продукта, цену и название компании производителя:

)Тип, поставляемого цифрового носителя, его объем памяти;

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

Условие нахождения базы данных в первой нормальной форме:

·таблица не должна иметь повторяющихся записей;

·в таблице должны отсутствовать повторяющиеся группы полей [1].

Запись синоним слову кортеж.

Кортеж, соответствующий данной схеме отношения, - это множество пар {имя атрибута, значение}, которое содержит одно вхождение каждого имени атрибута, принадлежащего схеме отношения. «Значение» является допустимым значением домена данного атрибута (или типа данных, если понятие домена не поддерживается). Иначе говоря, кортеж - это набор именованных значений заданного типа [1].

Отношение - это множество кортежей, соответствующих одной схеме отношения [1].

Полученное отношение показано в таблице 1.

Таблица 1 - Необходимая информация о цифровых дисках

Код про-дуктаНазвание продуктаТип про-дуктаТип носителяОбъем памятиКод поста-вщикаНаз-вание поста-вщикаЦена про-дуктаПроиз-водитель продукта1Microsoft Windows XPПОDVD-R4,7 Гб11С1 200р.Microsoft2Microsoft OfficeПОCD-R700 Мб11С700р.Microsoft3Adobe PhotoShop CSПОDVD-R4,7 Гб11С900р.Adobe4Adobe After Effects CSПОDVD-R4,7 Гб11С900р.Adobe5Живая стальВидеоDVD-R4,7 Гб2CDPRO300р.Warner brosers6Кот в сапогахВидеоDVD-R4,7 Гб2CDPRO300р.Warner brosers7АрияМузыкаCD-R700 Мб2CDPRO100р.Ария рекордс8RihannaМузыкаCD-R700 Мб2CDPRO100р.Rihanna9Diablo 2ИгрыCD-R700 Мб3Акелла300р.Blizard10BioShock ИгрыDVD-R4,7 Гб3Акелла300р.2K Boston

Вторая нормальная форма требует удаления функциональных зависимостей [1].

Функциональная зависимость. В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными атрибутами) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y, что в символическом виде можно записать как {R.X} ® {R.Y} [1].

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

В таблице 1 поле «Код поставщика» однозначно определяет поля: «Название поставщика», «Регион поставщика», «Населенный пункт», «Улица», «Дом», «Корпус», «Квартира», «Фамилия», «Имя», «Отчество».

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

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

Таблица 2 - Поставщики

Код поставщикаНазвание постав-щикаРегион поставщикаНаселенный пунктУлицаДомКорпус11СКрасноярский крайКрасноярскТельмана56А2CDPROЛенинградская обл.Санкт-ПетербургЛенина120Б3АкеллаМосковская обл.МоскваПушкина174Г

Есть еще два зависимых поля: «Тип носителя» и «Объем памяти», но лицензионные DVD диски, содержащие дополнительные опции к фильмам, имеют размер отличный от стандартного DVD диска, поэтому поле «Тип носителя» не может быть ключевым. Для удобства введем новое уникальное поле «Код типа носителя», полученное отношение показано в таблице 3.

Таблица 3 - Тип носителя

Код типа носителяТип носителяОбъем памяти1DVD-R4,7 Гб2CD-R700 Мб

Оставшиеся поля: «Тип продукта», «Жанр», «Цена продукта», «Производитель продукта» функционально зависят от поля «Код продукта», которое имеет уникальные значения. Полученное отношение назовем «Продукция» (таблица 4).

Таблица 4 - Продукция

Код продуктаНазвание продуктаТип продуктаЖанрЦена продуктаПроизводитель продукта1Microsoft Windows XPПО-1 200р.Microsoft2Microsoft OfficeПО-700р.Microsoft3Adobe PhotoShop CSПО-900р.Adobe4Adobe After Effects CSПО-900р.Adobe5Живая стальВидеоФантастика300р.Warnerbrosers6Кот в сапогахВидеоМультфильм300р.Warnerbrosers7АрияМузыкаРок100р.Ариярекордс8RihannaМузыкаПоп100р.Rihanna9Diablo 2ИгрыRPG300р.Blizard10BioShock ИгрыShooter300р.2K Boston

Третья нормальная форма требует удаления транзитивных (производных) зависимостей.

Транзитивная зависимость определяется следующим образом: если атрибут Z зависит от атрибута X и атрибут Y зависит от атрибута Z, то, следовательно, атрибут Y зависит от атрибута X.

О таблице говорят, что она находится в третьей нормальной форме, если:

·она удовлетворяет условиям второй нормальной формы;

·ни одно из неключевых полей не идентифицируется с помощью другого неключевого поля [1].

В отношениях отсутствуют транзитивные зависимости, а это значит, что отношения находятся в третьей нормальной форме.

Поля: «Тип продукта», «Жанр», «Производитель продукта», а также «Регион поставщика», имеют ограниченное количество значений. Поэтому для удобства ввода записей в базу данных и для надежности, выделим эти поля в таблицы - справочники. Для каждого поля вводим уникальные поля. Для поля «Тип продукта» - «Код типа продукта», для поля «Жанр» - «Код жанра», для поля «Регион поставщика» - «Код региона». Полученные отношения показаны в таблицах 5, 6, 7, 8.

Таблица 5 - Тип продукта

Код типа продукцииТип продукта1ПО2Видео3Музыка4Игры

Таблица 6 - Жанр

Код жанраЖанр1Фантастика2Мультфильм3Рок4Поп5RPG6Shooter

Таблица 7 - Производитель продукта

Код производителяПроизводитель продукта1Microsoft2Adobe3Warnerbrosers4Ариярекордс5Rihanna6Blizard72K Boston

Таблица 8 - Регионы

Код регионаРегион1Красноярский край2Ленинградская обл.3Московская обл.

Спроектирована реляционная база данных, состоящая из семи таблиц:

.Продукция;

.Поставщики;

.Производители;

.Регионы;

.Жанр;

.Тип носителя;

.Тип продукции.

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

Таблица «Производители» связана с таблицей «Продукция» связью «Один-ко-многим» по ключевому полю «Код производителя».

Таблица «Носители» связана с таблицей «Продукция» связью «Один-ко-многим» по ключевому полю «Код типа носителя».

Таблица «Жанр» связана с таблицей «Продукция» связью «Один-ко-многим» по ключевому полю «Код жанра».

Таблица «Тип продукции» связана с таблицей «Продукция» связью «Один-ко-многим» по ключевому полю «Код типа продукции».

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

Схема данных представлена на рисунке 1.

Рисунок 1 - Схема данных

Раздел 2. Физическое и даталогическое проектирование

Часть 1. Проектирование БД в СУБД Visual FoxPro

1.1 Создание проекта

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

Чтобы создать проект, нужно выполнить следующую команду в главном меню: «File» - «New», появится окно создания как показано на рисунке 1, в нем нужно выбрать Project и нажать на кнопку «New File», появится окно сохранения проекта, в нем нужно ввести название проекта и нажать кнопку «Сохранить».

Рисунок 1 - Окно создания новых файлов

После этого появится окно созданного проекта, которое представлено на рисунке 2.

Рисунок 2 - Окно проекта «Магазин дисков

1.2 Создание базы данных

Для того чтобы создать базу данных, нужно выполнить следующие действия:

)В окне проекта на вкладке «Data», либо на вкладке «ALL» выбрать пункт «Databases» и нажать кнопку «New».

)В появившемся окне можно выбрать способ создания: либо с помощью мастера «Database Wizard», либо с помощью конструктора «New Database». Создадим базу данных с помощью конструктора, нажав кнопку «New Database» (рисунок 3).

Рисунок 3 - Окно выбора способа создания БД

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

Переходим к созданию таблиц.

Для того чтобы создать таблицу выполняем следующее:

)На вкладке «Data» в списке созданных баз данных «Databases», раскрываем список созданной базы данных. Выбираем пункт таблицы «Tables» и нажимаем кнопку «New». Появляется окно в котором можно выбрать способ создания таблицы: с помощью мастера «Table Wizard» и с помощью конструктора «New Table». Создадим таблицу с помощью конструктора, нажав кнопку «New Table». В следующем окне сохраняем таблицу, указав имя и путь, нажатием кнопки «Сохранить».

)Появится окно дизайнер таблицы «Table Designer». Создадим поля для таблицы. Для этого на вкладке «Fields» в поле «Name» вводим название поля на английском языке для СУБД Visual FoxPro, но для удобства пользователя, в поле «Caption» вводим название поля на русском языке. Поле «Caption» расположено справа от области создания полей в группе «Display». В поле «Type» выбираем тип данных, которые будет содержать данное поле. Список допустимых типов приведен в таблице 9.

Таблица 9 - Типы данных в Visual FoxPro[1]

ТипНаименованиеОтображаемые данныеТекстовый Character, Character(binary)Текстовые поля могут содержать буквы, цифры и специальные символы. Числовой Integer, Numeric, Float, Double Integer отображает целые числа. Числовые поля типа Numeric и Float отображают данные с фиксированной точкой. Тип данных Double используется для хранения данных с высокой точностью.Денежный Currency В поле денежного типа могут содержаться числа Дата Date В поле типа Date может содержаться любая дата от 01. 01. 0001 до 31. 12.9999 Дата и время DateTime В поле типа DateTime может содержаться любая дата и время от 00:00:00 а.m.. до 11:59:59 р.m. Логический Logical Содержит логическое значение True (.Т.) (Истина) или False (.F.) (Ложь) Текстовое поле произвольной длины Memo, Memo(binary) Memo-поле содержит символьные данные большого объема Двоичное поле произвольной длины General Поле данного типа предназначено для хранения в таблицах изображений и других двоичных данных СчетчикInteger (Autoinc)Число целого типа (с автоматическим увеличением на единицу)

В поле «Width» вводим количество символов. Для типов: Numeric, Float, Double, в поле «Decimal» указывается количество знаков после запятой. Если поле может быть пустым, то нужно поставить галочку в поле «Null». Чтобы сделать поле ключевым, необходимо его проиндексировать. Для этого в раскрывающемся списке «Index» нужно выбрать порядок, в котором будут указываться индексные поля: «Ascending» - обратный порядок, «Descending» - прямой порядок. На рисунке 4 представлена структура таблицы «Поставщики».

Рисунок 4 - Структура таблицы «Поставщики»

)Затем переходим на вкладку «Indexes». В поле «Order Name» можно сократить название поля для удобства просмотра таблиц в окне базы данных. Из списка «Type» выбираем тип индекса. Для первичного ключа выбираем «Primary». Все типы представлены в таблице 10.

Таблица 10 - Список типов индекса

Тип индексаОписаниеRegular(Обычный) Создается индекс, в котором для каждой записи таблицы хранится значение индексного выражения. Если несколько записей имеют одинаковое значение индексного выражения, то каждое значение хранится отдельно и содержит ссылку на связанную с ней запись Candidate(Кандидат) Создается уникальный индекс, который не содержит полей с пустыми значениями. Этот индекс обладает всеми качествами первичного ключа и не является им только по той причине, что таблица не может содержать более одного первичного ключа Primary(Первичный) Создается уникальный индекс, который используется для связывания таблиц и определения условий целостности данных. Поля, входящие в первичный ключ, не должны допускать ввода пустых значений. Таблица может иметь только один первичный ключ

Проиндексированные поля таблицы «Поставщики» показаны на рисунке 5. Первичный ключ имеет поле «kod_postavshika».

Рисунок 5 - Проиндексированные поля таблицы «Поставщики»

По умолчанию таблица в СУБД носит имя сохраненного файла, для удобства работы с базой данных можно поменять имя таблицы. Для этого переходим на вкладку «Table» и в поле «Name» вводим имя. Вкладка «Table» изображена на рисунке 6 на примере таблицы «Поставщики».

Рисунок 6 - Информация об имени таблицы «Поставщики»

Аналогичным путем создаем остальные таблицы. На рисунках 7-18 представлены структуры таблиц и их индексы.

Рисунок 7 - Структура таблицы «Жанр»

Рисунок 8 - Проиндексированное поле таблицы «Жанр»

Рисунок 9 - Структура таблицы «Носители»

Рисунок 10 - Проиндексированное поле таблицы «Носители»

Рисунок 11 - Структура таблицы «Продукция»

Рисунок 12 - Проиндексированные поля таблицы «Продукция»

Рисунок 13 - Структура таблицы «Производители»

Рисунок 14 - Проиндексированное поле таблицы «Производители»

Рисунок 15 - Структура таблицы «Регионы»

Рисунок 16 - Проиндексированное поле таблицы «Регионы»

Рисунок 17 - Структура таблицы «Тип продукции»

Рисунок 18 - Проиндексированное поле таблицы «Тип продукции»

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

Рисунок 18.1 - Окно редактирования связи

Нажимаем кнопку «Referential Integrity», появляется окно настройки целостности данных: для обновления «Rules for Updating», для удаления «Rules for Deleting», для вставки «Rules for Inserting». На вкладках обновления и удаления устанавливаем «Cascade», т.е. каскадное обновления данных, для удаления «Restrict» так как изображено на рисунке 18.2.

Рисунок 18.2 - Окно настройки целостности данных

Полученная схема данных представлена на рисунке 1.

1.3 Создание форм

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

Чтобы создать форму в окне проекта на вкладке «Documents» или «ALL» выбираем пункт «Forms» нажимаем кнопку «New». Создадим форму с помощью мастера, нажатием кнопки «Form Wizard». Появится окно, в котором нужно выбрать: либо «Form Wizard» - форма будет содержать информацию одной таблицы, либо «One-to-Many Form Wizard» - создание многотабличной формы на основе связанных таблиц (рисунок 19).

Рисунок 19 - Окно выбора типа формы.

Создадим форму для таблицы «Поставщики», форма будет многотабличной, так как в таблице информация о регионах берется из другой таблицы «Регионы». Выбираем «One-to-Many Form Wizard». Далее выбираем родительскую таблицу, выбираем таблицу «Поставщики», переносим все поля (рисунок 20).

Рисунок 20 - Окно выбора родительской таблицы

Нажимаем кнопку «Next», в следующем окне выбираем связанную таблицу «Регионы» (рисунок 21).

Рисунок 21 - Выбор таблицы для многотабличной формы

В следующем окне выбираем поля по которым таблицы связаны, жмем далее (рисунок 22).

Рисунок 22 - Окно выбора полей, по которым связаны таблицы. Для многотабличной формы.

В следующем окне выбираем стиль формы как показано на рисунке 23, жмем «Next».

Рисунок 23 - Настройка внешнего вида формы.

В следующем окне выбираем поле, по которому будет произведена сортировка. Переносим поле «kod_postavshika» и выбираем сортировку: «Descending» (рисунок 24).

Рисунок 24 - Выбор поля для сортировки данных

Нажимаем «Next» появляется окно завершения создания, в котором представлены варианты, что сделать после сохранения формы, нажимаем «Finish». Появляется окно сохранения формы, вводим название и нажимаем «Cохранить».

Таблица «Регионы» на форме добавлена в виде таблице, что не удобно, да и нарушает визуальный стиль формы. Заменим таблицу компонентом «Combo Box». Для этого выделим в окне проекта созданную форму «Поставщики» и откроем ее в режиме конструктора нажав «Modify». Удалим вложенную таблицу, выделив ее и нажав на клавиатуре клавишу «Delete». В окне «Form Controls» выберем компонент «Combo Box» (рисунок 25).

Рисунок 25 - Панель компонентов «Form Controls»

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

Расположим его на форме, чтобы выводилась информация, необходима подключить к компоненту «Combo Box» необходимое поле. Для этого выделяем компонент, в окне свойств «Properties» в строке «Row Source Type» выбираем, что будет источником, выбираем поле «Fields». Далее выбираем сам источник, в поле «Row Source» выбираем поле таблицы регионы «name_regiona» как показано на рисунке 26.

Рисунок 26 - Окно свойств «Properties»

И для отображения выбираем это же поле в строке «Control Source» (рисунок 27).

Рисунок 27 - Окно свойств «Properties»

Запускаем форму кнопкой «Run» на панели инструментов. Аналогично создаем формы для остальных таблиц. На форму можно добавлять изображения. Для этого размещаем на форме компонент «Image» и в окне свойств выбираем пункт «Picture», в котором указываем путь к изображению. Если изображение большое, не влезает на форму нужно в свойствах в поле «Stretch» выбрать: «Stretch»-растянуть по размеру компонента «Image», «Isometric» - подогнать под размер компонента, но сохраняя пропорции.

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

На рисунках 28-33 изображены формы в режиме выполнения.

Рисунок 28 - Форма «Поставщики»

Рисунок 29 - Форма «Регионы»

Рисунок 28 - Форма «Продукция»

Рисунок 30 - Форма «Жанр»

Рисунок 31 - Форма «Носители»

Рисунок 32 - Форма «Производители»

Рисунок 33 - Форма «Тип_продукции»

На рисунках 34-40 изображены таблицы в режиме «Browse»

Рисунок 34 - Таблица в режиме «Browse» «Жанр»

Рисунок 35 - Таблица в режиме «Browse» «Носители»

Рисунок 36 - Таблица в режиме «Browse» «Поставщики»

Рисунок 37 - Таблица в режиме «Browse» «Продукция»

Рисунок 38 - Таблица в режиме «Browse» «Производители»

Рисунок 39 - Таблица в режиме «Browse» «Регионы»

Рисунок 40 - Таблица в режиме «Browse» «Тип_продукта»

1.4 Запросы и отчеты

Для создания запроса в окне проекта, на вкладке «Data» выбираем пункт «Queries» нажимаем «New», «New Query» откроется конструктор запросов. Добавляем необходимые таблицы, выполнив команду «Query», «Add Table», или в поле окна конструктора вызвать контекстное меню и выбрать «Add Table». В появившемся окне выбираем таблицу и нажимаем «Add» рисунок 41.

Рисунок 41 - Окно добавления таблиц

На вкладке «Fields» выбираем поля выводимые в отчете (рисунок 42). На вкладке «Join» задается условие объединения таблиц. На вкладке «Filter» задаем условие выборки данных (рисунок 43).

Рисунок 42 - Окно конструктора запросов

Рисунок 43 - Вкладка «Filter». Условие запроса

Создать запрос: выбрать продукцию, поставленную поставщиком «1С».

Результат запрос на продукцию, поставленную поставщиком «1C» представлен на рисунке 44. SQL код запроса:

Продукция.name_producta, Тип_продукции.name_type,;

Поставщики.name_postavshika, Поставщики.f, Поставщики.i, Поставщики.o;

FROM ;

диски!тип_продукции ;

INNER JOIN диски!продукция ;

ON Тип_продукции.type_producta = Продукция.type_producta ;

INNER JOIN диски!поставщики ;

ON Поставщики.kod_postavshika = Продукция.kod_postavshika;

WHERE Поставщики.name_postavshika = ( "1С" );

HAVING Тип_продукции.name_type = ( "ПО" )

Рисунок 44 - Результат выполнения запроса

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

Результат запроса на выборку продукции поставленной на DVD носителях представлен на рисунке 45. SQL код запроса:

Продукция.name_producta, Тип_продукции.name_type, Жанр.janr,;

Поставщики.name_postavshika, Продукция.cena, Носители.name;

FROM ;

диски!тип_продукции ;

INNER JOIN диски!продукция ;

ON Тип_продукции.type_producta = Продукция.type_producta ;

INNER JOIN диски!носители ;

ON Носители.type_nositela = Продукция.type_nositela ;

INNER JOIN диски!поставщики ;

ON Поставщики.kod_postavshika = Продукция.kod_postavshika ;

INNER JOIN диски!жанр ;

ON Жанр.kod_janra = Продукция.kod_janra;

WHERE Носители.name = ( "DVD" )

Рисунок 45 - Результат выполнения запроса

Рисунок 45.1 - Условие запроса

Создать запрос: выбрать из всей продукции программное обеспечение, цена которого больше 700 рублей.

Результат запроса на выборку только программного обеспечения, цена которого больше 700 рублей представлен на рисунке 46. SQL код запроса:

Продукция.name_producta, Носители.name, Носители.razmer,;

Поставщики.name_postavshika, Продукция.cena;

FROM ;

диски!носители ;

INNER JOIN диски!продукция ;

ON Носители.type_nositela = Продукция.type_nositela ;

INNER JOIN диски!тип_продукции ;

ON Тип_продукции.type_producta = Продукция.type_producta ;

INNER JOIN диски!поставщики ;

ON Поставщики.kod_postavshika = Продукция.kod_postavshika;

WHERE Тип_продукции.name_type = ( "ПО" );

AND Продукция.cena > ( 700 )

Рисунок 46 - Результат выполнения запроса

Рисунок 46.1 - Условие запроса

Создание отчета.

Для того чтобы создать отчет, нужно на вкладке «Documents» выделить пункт «Reports» и нажать клавишу «New». Если отчет создается с помощью мастера, то необходимо выбрать однотабличный или многотабличный, в этом случае отчет будет автоматически сформирован. Если создается с помощью конструктора, то необходимо выполнить следующее:

)Добавить необходимые таблицы, в среду окружения, нажав кнопку на панели инструментов «Data Environment».

)После чего разместить поля. Для этого на панели инструментов «Report Controls» выбираем компонент «Fields». Разместив его на форме, появится окно, в котором в поле «Expression» указываем поле таблицы, данные которой необходимо включить в отчет (рисунок 47).

Рисунок 47 - Окно свойств компонента поле

Многотабличный отчет о поставщиках показан на рисунке 48 в режиме просмотра.

Рисунок 48 - Отчет о поставщиках в режиме просмотра

Рисунок 48.1 - Отчет о поставщиках в режиме конструктора

На рисунке 49 изображен многотабличный отчет о имеющейся продукции. Используются три таблицы:

)«Продукция»;

)«Поставщики»;

)«Тип продукции»

Рисунок 49 - Отчет о продукции в режиме просмотра

На рисунках 50 и 51 представлен отчет, в котором подсчитывается сумма, на которую закуплена продукция.

Рисунок 50 - Фрагмент отчета. Начало

Рисунок 51 - Фрагмент отчета. Окончани

Рисунок 51.1 - Вычисляемое поле. Вычисляет стоимость закупки

1.4 Главное меню

В окне конструктора меню в поле «Prompt» нужно ввести наименования пунктов меню. Раскрывающийся список «Result» используется для указания типа пункта меню. В списке «Menu level» указывается уровень текущего меню. В таблице 11 описаны типы пунктов меню.

Таблица 11 - Типы меню [1]

Тип менюНазначениеCommand (Команда) При выборе пункта меню данного типа будет выполняться связанная с ним команда Pad Name (Наименование строки меню) При выборе пункта меню никаких действий выполняться не будет. Как правило, используется в качестве дополнительного пояснения к меню Submenu (Подменю) При выборе пункта меню раскрывается связанное с данным пунктом ниспадающее меню Procedure (Процедура) При выборе пункта меню вызывается процедура, определенная для данного пункта меню

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

Рисунок 52 - Окно создание главного меню. Перечень выпадающих пунктов

Рисунок 52.1 - Окно создание главного меню. Перечень пунктов команд

Команды вызова запросов:

)«1С» - DO "d:\файлы и программы\бд\query1.qpr";

)«DVD» - DO "d:\файлы и программы\бд\query2.qpr";

)«Сумма» - DO "d:\файлы и программы\бд\query3.qpr";

Рисунок 52.2 - Окно создание главного меню. Перечень пунктов команд

Команды вызова отчетов:

)«Поставщики» - REPORT FORM "d:\файлы и программы\бд\бд\поставщики.frx" PREVIEW

)«Продукция» - REPORT FORM "d:\файлы и программы\бд\бд\продукция.frx" PREVIEW

)«Сумма закупки» - REPORT FORM "d:\файлы и программы\бд\бд\сумма.frx" PREVIEW

После того как созданы все необходимые пункты меню, меню нужно сгенерировать. В меню «Menu» нужно выбрать команду «Generate». Откроется диалоговое окно «Generate Menu». В поле «Output File» вводится имя файла, который будет создан в результате генерации. Для запуска генерации описания меню нажимается кнопка «Generate».

Рисунок 52.3 - Главное меню

Рисунок 52.4 - Главное меню

1.5 Создание приложения

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

Рисунок 53 - Главная кнопочная форма

Рисунок 53.1 - Форма с закладками, на которых расположены таблицы

Рисунок 54 - Форма запуска запросов

Рисунок 55 - Форма запуска отчетов

Перед построением приложения зададим главную форму, выделив ее и в пункте главного меню «Project» ставим галочку у пункта «Set Main». В окне проекта нажимаем «Build» в окне выбираем с расширением «App» (рисунок 56).

Рисунок 56 - Форма построения приложения

Сохраняем, приложение автоматически запустится если был выбран пункт «Run After Build»

Часть 2. SQL

Язык SQL является стандартным реляционным языком и в настоящее время поддерживается практически всеми современными СУБД. SQL - это сокращенное название языка Structured Query Language. Язык SQL является языком программирования, который применяется для организации взаимодействия с базой данных. Он сочетает средства SDL и DML, т.е. позволяет определять схему реляционной БД и манипулировать данными. SQL является декларативным языком, в нем нет операторов цикла или логических операторов, поэтому он обычно встраивается в какой-либо процедурный базовый язык.[1]

SQL реализует все функциональные возможности, которые СУБД предоставляет пользователю, а именно:

·Организация данных. SQL дает пользователю возможность изменять структуру представления данных.

·Чтение данных. SQL дает пользователю или приложению возможность выбирать из базы данных содержащиеся в ней данные и пользоваться ими.

·Обработка данных. SQL дает возможность пользователю или приложению возможность изменять базу данных, т.е. добавлять в нее новые данные, а также удалять или обновлять уже имеющиеся в ней данные.

·Управление доступом. С помощью SQL можно ограничить возможности пользователя по чтению или изменению данных и защитить их от несанкционированного доступа.

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

·Целостность данных. SQL позволяет обеспечить целостность базы данных.[1]

Создадим базу данных с названием «CDSHOP»:

CREATE DATABASE CDSHOP;

Рисунок 57 - Создание базы данных «CDSHOP»

Теперь создаем таблицы:

Создаем таблицу поставщики «postavshiki»:

CREATE TABLE CDSHOP.postavshiki(kod_postavshika INTEGER(11) NOT NULL, nazvanie VARCHAR(255) NOT NULL, familia VARCHAR(255) NOT NULL, imya VARCHAR(255) NOT NULL, otchestvo VARCHAR(255) NOT NULL, strana VARCHAR(255) NOT NULL, kod_regiona INTEGER(11) NOT NULL, PRIMARY KEY (kod_postavshika), UNIQUE INDEX (kod_postavshika), INDEX (kod_regiona)); NULL - означает что поле не может быть пустым. Если при добавлении данных в таблицу, такое поле оставить пустым, программа сообщит об ошибке.

PRIMARY KEY - задает первичный ключ таблицы.

UNIQUE INDEX - задает уникальное поле, значения которого не могут повторяться.

INDEX - задает поля, которые будут проиндексированы.

Индексация полей полезна для ускорения работы команды SELECT

Рисунок 58 - Создание таблицы «postavshiki»

Создаем таблицу «regiony»:

CREATE TABLE CDSHOP.regiony(kod_regiona INTEGER(11) NOT NULL, nazvanie_regiona VARCHAR(255) NOT NULL, PRIMARY KEY (kod_regiona));

Рисунок 59 - Создание таблицы « regiony»

Создаем таблицу «proizvoditely»:

CREATE TABLE CDSHOP.proizvoditely( kod_proizvoditela INTEGER(11) NOT NULL, nazvanie_proizvoditela VARCHAR(255) NOT NULL, PRIMARY KEY (kod_proizvoditela));

Рисунок 60 - Создание таблицы « proizvoditely »

Создаем таблицу « nositely »:

CREATE TABLE CDSHOP.nositely( kod_nositela INTEGER(11) NOT NULL, tip_nositela VARCHAR(255) NOT NULL, razmer FLOAT(6, 3) NOT NULL, PRIMARY KEY (kod_nositela));

Рисунок 61 - Создание таблицы « nositely »

Создаем таблицу «janr»:

CREATE TABLE CDSHOP.janr( kod_janra INTEGER(11) NOT NULL, janr VARCHAR(255) NOT NULL, PRIMARY KEY (kod_janra));

Рисунок 62 - Создание таблицы «janr»

Создаем таблицу «tip_produkcii»:

CREATE TABLE CDSHOP.tip_produkcii( kod_tipa INTEGER(11) NOT NULL, name_tipa VARCHAR(255) NOT NULL, PRIMARY KEY (kod_tipa));

Рисунок 63 - Создание таблицы «tip_produkcii»

Создаем таблицу « produkt »:

CREATE TABLE CDSHOP.produkt( kod_produkta INTEGER(4) NOT NULL, nazvanie VARCHAR(50) NOT NULL, tip_produkta INTEGER(4) NOT NULL, kod_janra INTEGER(4) NOT NULL, kod_nositela INTEGER(4) NOT NULL, cena FLOAT(8,3) NOT NULL, kod_postavshika INTEGER(4) NOT NULL, kod_proizvoditela INTEGER(4) NOT NULL, PRIMARY KEY (kod_produkta), INDEX (kod_janra), INDEX (kod_postavshika), UNIQUE INDEX (kod_produkta), INDEX (kod_proizvoditela), INDEX (kod_nositela));

Рисунок 64 - Создание таблицы «produkt»

Заполнение таблиц данными.

INSERT INTO CDSHOP.postavshiki (

kod_postavshika, nazvanie, familia, imya, otchestvo, strana, kod_regiona

)VALUES(

,'1C', Petrov, Aleksey, Ivanovich, Krasnoyarsk, 3

), (

,'CDPro', Ivanov, Petr, Ivanovich, Sankt-Peterburg, 2

), (

,'Akella', Kozlov, Ivan, Alekseevich, Moskva, 1); - производит вставку новых строк в таблицу.

Рисунок 65 - Вставка данных в таблицу «postavshiki»

Заполним таблицу «regiony».

INSERT INTO CDSHOP. regiony (

kod_regiona , nazvanie_regiona )VALUES(

1, Moskovskaya oblast

), (

, Leningradskaya oblast

), (

, Krasnoyarskiy kray);

Рисунок 66 - Вставка данных в таблицу «regiony»

Заполним таблицу «proizvoditely».

INSERT INTO CDSHOP. proizvoditely (_proizvoditela, nazvanie_proizvoditela

)VALUES(

1, Microsoft

), (

, Adobe

), (

, Blizard

), (

, 2K Boston );

Рисунок 66 - Вставка данных в таблицу «proizvoditely»

Заполним таблицу « nositely ».

INSERT INTO CDSHOP.nositely (_nositela, tip_nositela, razmer

)VALUES(

1, DVD, 4.7

), (

, CD, 0.7);

Рисунок 67 - Вставка данных в таблицу «nositely»

Заполним таблицу « janr».INTO CDSHOP. janr (_janra, janr)VALUES(

1, Fantastika), (

, Multfilm), (

, Rock), (

, Pop), (

, RPG), (

, Action),(

, -);

Рисунок 68 - Вставка данных в таблицу «janr»

Заполним таблицу « tip_produkcii».INTO CDSHOP.tip_produkcii (_tipa, name_tipa)VALUES(

1, PO), (

, Video), (

, Musika), (

, Game);

Рисунок 69 - Вставка данных в таблицу «tip_produkcii»

Заполним таблицу « produkt ».INTO CDSHOP. produkt (_produkta, nazvanie, tip_produkta, kod_janra, _nositela, cena, kod_postavshika, kod_proizvoditela

)VALUES(

1, Microsoft Windows XP, 1, 7, 2, 1200, 0, 1), (

, Adobe PhotoShop CS5, 1, 7, 1, 900, 0, 2), (

, Jivaya Stal, 2, 1, 1, 300, 1, 1), (

, Ariya, 3, 3, 2, 100, 1, 1), (

, Diablo 2, 5, 5, 2, 200, 2, 3);

Рисунок 70 - Вставка данных в таблицу «produkt»

Просмотрим полученные таблицы.

Выберем все записи таблицы «postavshiki»:* FROM CDSHOP.postavshiki; - выборка всех полей из таблицы «postavshiki».

Рисунок 71 - Таблица «postavshiki»

Выберем все записи таблицы «regiony»:

SELECT * FROM CDSHOP. regiony;

Рисунок 72 - Таблица «regiony»

Выберем все записи таблицы «proizvoditely»:

SELECT * FROM CDSHOP. proizvoditely;

Рисунок 73 - Таблица «proizvoditely»

Выберем все записи таблицы «nositely»:

SELECT * FROM CDSHOP. nositely;

Рисунок 74 - Таблица «nositely»

Выберем все записи таблицы «janr»:

SELECT * FROM CDSHOP. janr;

Рисунок 75 - Таблица «janr»

Выберем все записи таблицы «tip_produkcii»:

SELECT * FROM CDSHOP. tip_produkcii;

Рисунок 76 - Таблица «tip_produkcii»

Выберем все записи таблицы «produkt»:

SELECT * FROM CDSHOP.produkt;

Рисунок 77 - Таблица «produkt»

Запросы.

Создать запрос: выбрать продукцию, поставленную поставщиком «1С».

В запросе участвуют таблицы: «produkt», «tip_produkcii», «postavshiki».

SELECT CDSHOP.produkt.nazvanie, CDSHOP.tip_produkcii.name_tipa, CDSHOP.postavshiki.nazvanie

FROM CDSHOP.produkt, CDSHOP.tip_produkcii, CDSHOP.postavshikiCDSHOP.produkt.tip_produkta= CDSHOP.tip_produkcii.kod_tipa AND CDSHOP.produkt.kod_postavshika=CDSHOP.postavshiki.kod_postavshika AND CDSHOP.postavshiki.nazvanie=1C;

Условиями: «CDSHOP.produkt.tip_produkta= CDSHOP.tip_produkcii.kod_tipa» и «CDSHOP.produkt.kod_postavshika=CDSHOP.postavshiki.kod_postavshika» ищем совпадения значений данных таблиц, для определения какие записи одной таблицы соответствуют записям другой;.postavshiki.nazvanie=1C - ищем значения поля «nazvanie» равное «1С»

Рисунок 78 - Результат выполнения запроса. Выбрать поставки от «1С»

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

SELECT CDSHOP.produkt.nazvanie, CDSHOP.tip_produkcii.name_tipa, CDSHOP.postavshiki.nazvanie, CDSHOP.nositely. tip_nositela, CDSHOP.nositely.razmer

FROM CDSHOP.produkt, CDSHOP.tip_produkcii, CDSHOP.postavshiki, CDSHOP.nositely

WHERE CDSHOP.produkt.tip_produkta= CDSHOP.tip_produkcii.kod_tipa AND CDSHOP.produkt.kod_postavshika=CDSHOP.postavshiki.kod_postavshika AND CDSHOP.produkt.kod_nositela=CDSHOP.nositely.kod_nositela AND CDSHOP.nositely.tip_nositela = DVD;

Рисунок 79 - Результат выполнения запроса. Выбрать продукцию на DVD носителях

Создать запрос: выбрать из всей продукции программное обеспечение, цена которого больше 700 рублей.

SELECT CDSHOP.produkt.nazvanie, CDSHOP.produkt.cena, CDSHOP.tip_produkcii.name_tipa, CDSHOP.postavshiki.nazvanie, CDSHOP.nositely. tip_nositela, CDSHOP.nositely.razmer

FROM CDSHOP.produkt, CDSHOP.tip_produkcii, CDSHOP.postavshiki, CDSHOP.nositely

WHERE CDSHOP.produkt.tip_produkta= CDSHOP.tip_produkcii.kod_tipa CDSHOP.produkt.kod_postavshika=CDSHOP.postavshiki.kod_postavshika CDSHOP.produkt.kod_nositela=CDSHOP.nositely.kod_nositela CDSHOP. tip_produkcii.name_tipa = PO CDSHOP.produkt.cena>700;

Рисунок 80 - Результат выполнения запроса. Выбрать программное обеспечение по цене дороже 700 руб.

Заключение

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

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

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

Эта же база данных была создана в «MySQL Command Line Client». В ходе создания базы данных были получены навыки использования запросов структурного языка SQL.

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

1.Кокова В.И. Базы данных [Текст]: учебное пособие / В.И. Кокова. -Красноярск.: КГТУ, 2005. - 164 c.

2.FoxPro Club [Электронный ресурс] - Режим доступа: http://www.foxclub.ru/

3.SQL.ru [Электронный ресурс] - Режим доступа: <http://www.sql.ru/>

Министерство образования и науки РФ Хакасский Технический Институт - филиал ВГАОУ ВПО «Сибирский Федеральный Университет» Кафедра «Прикл

Больше работ по теме: