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

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РОССИЙСКОЙ ФЕДЕРАЦИИ

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГ ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«КРАСНОЯРСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ»

Институт менеджмента и информатики

Кафедра Информационных систем и технологий в экономике

К У Р С О В О Й П Р О Е К Т

Дисциплина: «Базы данных»

Тема: «Проектирование базы данных на примере ООО Садовая техника»

Выполнила студентка

группы ПИ-25 С.А. Прибыткова

Руководитель доцент к.т.н. Н.В. Титовская

Красноярск 2015



Содержание

Введение

1. Теоретические основы проектирования и разработки баз данных

1.1 Основные принципы проектирования реляционных баз данных

1.2Этапы физической реализации проектируемой базы данных

2. Существующая организация бизнес-процессов и процессов обработки данных исследуемого объекта по теме курсового проекта

3. Проектирование системы

3.1 Определение сущностей, атрибутов, взаимосвязей между сущностями, ключей

3.2 Построение ER - модели

3.2 Проектирование ER-модели в реляционную модель

3.4 Схема проектируемой базы данных

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

3.6 Формирование простых и сложных запросов к базе данных

3.7 Способы повышения производительности доступа к данным

3.8 Процедуры и функции

3.9 Курсоры

4. Декомпозиция проектируемой системы

4.1 Проектирование и реализация подсистем

4.1.1 Подсистема хранения

4.1.2 Подсистема ввода и вывода

Заключение

Приложение А

база данные проектирование запрос



Введение

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

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

Автоматизация производственных процессов позволяет:

· уменьшить численность персонала, обслуживающего оборудование;

· увеличить выпуск и улучшить качество продукции, снизить ее себестоимость;

· оптимизировать загрузку оборудования;

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

· улучшить условия труда и безопасность производства.

Объект курсового проекта: ООО «Садовая техника»

Предмет исследования: деятельность ООО «Садовая техника».

Цель проектирования баз данных:

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

2. получение практических навыков создания БД.

Задачи проектирования баз данных:

1. анализ и разработка моделей данных;

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

3. Проектирование на физическом уровне

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



1. Теоретические основы проектирования и разработки баз данных

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

1.1 Основные принципы проектирования реляционных баз данных

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

Существуют следующие основные модели данных:

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

Иерархические модели данных - БД, основанная на иерархической модели, состоит из упорядоченного набора деревьев. Каждое дерево из одного «корневого» и упорядоченного набора из нуля или более связанных между ними поддеревьев (потомки). Целостность связи между ними поддерживается автоматически.

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

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

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

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

Отношение содержит две части - заголовок и собственную содержательную часть. Заголовок содержит конечное множество атрибутов, а содержательная часть (тело отношения) - множество имени атрибута и его значения.

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

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

1. Выборка отношения;

2. Проекция отношения;

3. Объединение отношений;

4. Пересечение отношений;

5. Вычитание отношений;

6. Произведение отношений;

7. Соединение отношений;

8. Деление отношений;

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

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

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

На логическом уровне можно установить связи:

1. один-к-одному;

2. один-ко-многим;

3. многие-ко-многим;

4. многие-к-одному;

1.2 Этапы физической реализации проектируемой базы данных

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

1. Выбор и приобретение СУБД.

2. Преобразование концептуальной модели в физическую модель.

3. Построение словаря.

4. Заполнение базы данных.

5. Создание прикладных программ.

6. Обучение пользователей.



2. Существующая организация бизнес-процессов и процессов обработки данных исследуемого объекта по теме курсового проекта

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

ООО «Садовая техника» специализируется на продаже одежды от известных марок.



3. Проектирование системы

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

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

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

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

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

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

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

1. объединения отношений (UNION);

2. пересечения отношений (INTERSECT);

3. взятия разности отношений (MINUS);

4. декартово произведение (TIMES).

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

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

1. моделирование данных;

2. особенности архитектуры и функциональные возможности;

3. контроль работы системы;

4. особенности разработки приложений;

5. производительность;

6. надежность;

7. требования к рабочей среде;

8. смешанные критерии.

3.1 Определение сущностей, атрибутов, взаимосвязей между сущностями, ключей

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

Сущность имеет следующие признаки:

1. Она имеет имя в описании.

2. Она представляет класс, а не единичный экземпляр абстракции.

3. Ее конкретные представители могут быть уникально идентифицированы.

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

Связь (relationship) - это функциональная зависимость между двумя сущностями (возможна связь сущности с самой собой). Связь должна иметь имя, выражаемая глаголом.

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

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

Каждый признак сущности описывается ровно одним атрибутом в своей сущности.

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

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

Сущность ГАЗОНОКОСИЛКИ с атрибутами kod_g, id_proizvoditel, model, god_vipuska, id_cvet, id_konstrukcia, id_tip_dvigatela, cena. Первичный ключ kod_g. Показывает, какие товары имеются на продаже у компании.

Сущность КЛИЕНТЫ с атрибутами kod_k, id_fio, telefon, adres, pasport. Первичный ключ kod_k. Показывает, какие клиенты есть в базе у организации.

Сущность КОНСТРУКЦИИ с атрибутами kod_kon, konstrukcia. Первичный ключ kod_kon. Показывает данные о доступных конструкциях техники.

Сущность ПРОИЗВОДИТЕЛЬ с атрибутами kod_p, proizvoditel. Первичный ключ kod_p. Показывает информацию о производителях техники.

Сущность ТИП ДВИГАТЕЛЯ с атрибутами kod_td, tip_dvigatela. Первичный ключ kod_td. Показывает доступные типы двигателей для техники.

Сущность ЦВЕТ с атрибутами kod_c, cvet. Первичный ключ kod_c. Показывает информацию о оступных цветах.

Сущность ЖУРНАЛ ПРОДАЖ с атрибутами kod_jp, name_tovara, fio, d_cena. Первичный ключ kod_jp. Показывает информацию о совершенных сделках.

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

3.2 Построение ER - модели

Подсистема вывода предприятия ООО «Садовая техника» осуществляет:

· вывод справочника и информации о газонокосилках;

· вывод справочника и информации о клиентах;

· вывод справочника и информации о комплектации техники;

· вывод справочника и информации о совершенных продажах;

Подсистема обработки предприятия ООО «Садовая техника» осуществляет функции обработки данных, таких как:

· выборка данных во всех таблицах;

· редактирование данных во всех таблицах;

· сортировка данных по товару, комплектации, клиенту, дате;

· добавление данных во все таблицы;

· удаление данных из всех таблиц.

3.2 Проектирование ER-модели в реляционную модель

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

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

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

3. Первичный ключ сущности становится первичным ключом соответствующего отношения.

4. В каждое отношение, соответствующее подчиненной сущности добавляется набор атрибутов основной сущности, являющийся первичным ключом основной сущности. В отношении, соответствующим подчиненной сущности этот набор атрибутов становится внешним ключом.

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

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

7. При другом варианте для каждого подтипа и для супертипа создаются свои отдельные отношения.

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

3.4 Схема проектируемой базы данных

Название сущности	Атрибуты	Наименование	Домен
Газонокосилки (GAZONOKOSILKI)	Номер (PK)	KOD_G	Integer
	Производитель (FK)	ID_PROIZVODITEL	Integer
	Модель	MODEL	VarChar2 (30)
	Год выпуска	GOD_VIPUSKA	VarChar2 (30)
	Цвет (FK)	ID_CVET	Integer
	Конструкция (FK)	ID_KONSTRUKCIA	Integer
	Тип двигателя (FK)	ID_TIP_DVIGATELA	Integer
	Цена	CENA	Integer
Клиенты (KLIENT)	Номер клиента (PR)	KOD_K	Integer
	Ф.И.О	ID_FIO	VarChar2 (30)
	Телефон	TELEFON	Integer
	Адрес	ADRES	VarChar2 (30)
	Паспорт	PASPORT	VarChar2 (30)
Журнал продаж (JURNAL_PRODAJ)	Номер (PK)	KOD_JP	Integer
	Наименование товара (FK)	NAME_TOVARA	Integer
	Клиент (FK)	FIO	Integer
	Стоимость	D_CENA	Integer
Цвет (CVET)	Номер (PK)	KOD_C	Integer
	Наименование	CVET	VarChar2 (30)
Конструкция (KONSTRUKCIA)	Номер (PK)	KOD_KON	Integer
	Наименование	KONSTRUKCIA	VarChar2 (30)
Производитель (PROIZVODITEL)	Номер (PK)	KOD_P	Integer
	Название	PROIZVODITEL	VarChar2 (30)
Тип двигателя (TIP_DVIGATELA)	Номер (PK)	KOD_TD	Integer
	Наименование	TIP_DVIGATELA	VarChar2 (30)

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

Все данные в БД SQL Server хранятся в таблицах. Таблицы состоят из колонок, объединяющих значение одного типа, и строк - записей в таблице. В одной БД может быть до 2 миллиардов таблиц, в таблице - 1024 колонок, в одной строке 8060 байтов.

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

Синтаксис создания таблиц:

CREATE TABLE <имя таблицы>

(<column name> <data type> [(size)],

(<column name> <data type> [(size)], …);

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

Ниже приведены созданные таблицы БД ООО «Садовая техника»:

CREATE TABLE GAZONOKOSILKI (

KOD_G INTEGER NOT NULL,

ID_PROIZVODITEL INTEGER NOT NULL,

MODEL VARCHAR(25) NOT NULL,

GOD_VIPUSKA VARCHAR(20) NOT NULL,

ID_CVET INTEGER NOT NULL,

ID_KONSTRUKCIA INTEGER NOT NULL,

ID_TIP_DVIGATELA INTEGER NOT NULL,

CENA INTEGER NOT NULL,

PRIMARY KEY (KOD_G),

FOREIGN KEY (ID_PROIZVODITEL) REFERENCES PROIZVODITEL,

FOREIGN KEY (ID_CVET) REFERENCES CVET,

FOREIGN KEY (ID_KONSTRUKCIA) REFERENCES KONSTRUKCIA,

FOREIGN KEY (ID_TIP_DVIGATELA) REFERENCES TIP_DVIGATELA

);

CREATE TABLE JURNAL_PRODAJ (

KOD_JP INTEGER NOT NULL,

NAME_TOVARA INTEGER NOT NULL,

FIO INTEGER NOT NULL,

D_CENA INTEGER,

PRIMARY KEY (KOD_JP),

FOREIGN KEY (NAME_TOVARA) REFERENCES GAZONOKOSILKI,

FOREIGN KEY (FIO) REFERENCES KLIENTY

);

CREATE TABLE KLIENTY (

KOD_K INTEGER NOT NULL,

ID_FIO VARCHAR(50) NOT NULL,

TELEFON INTEGER NOT NULL,

ADRES VARCHAR(50) NOT NULL,

PASPORT VARCHAR(30) NOT NULL,

PRIMARY KEY (KOD_K)

);

CREATE TABLE CVET (

KOD_C INTEGER NOT NULL,

CVET VARCHAR(30) NOT NULL,

PRIMARY KEY (KOD_C),

);

CREATE TABLE KONSTRUKCIA (

KOD_KON INTEGER NOT NULL,

KONSTRUKCIA VARCHAR(31) NOT NULL,

PRIMARY KEY (KOD_KON)

);

CREATE TABLE PROIZVODITEL (

KOD_P INTEGER NOT NULL,

PROIZVODITEL VARCHAR(30) NOT NULL,

PRIMARY KEY (KOD_P)

);

CREATE TABLE TIP_DVIGATELA (

KOD_TD INTEGER NOT NULL,

TIP_DVIGATELA VARCHAR(30) NOT NULL,

PRIMARY KEY (KOD_TD)

);

Для добавления в таблицу строк используется команда INSERT:

INSERT INTO <Имя таблицы> VALUES (содержание полей);

Заполнение данных:

insert into KLIENTY values (1, `Иконенко Виталий', `892347488', `Высотная 147-54', `457985' );

insert into KLIENTY values (2, `Дьяченко Ирина, `821432', `Свободный 78-12', `125877' );

insert into KLIENTY values (3, `Денисов Станислав, `8565439', `Тотмина 25-54', `024587');

insert into KLIENTY values (4, `Доброденко Григорий', `743215', `Елены Стасовой 8', `544789');

insert into KLIENTY values (5, `Мищенко Валерий', `7698775', `Крас.Московская77-1', `554789' );

insert into CVET values (1, `Красный' );

insert into CVET values (2, `Желтый' );

insert into CVET values (3, `Зеленый' );

insert into CVET values (4, `Черный' );

insert into KONSTRUKCIA values (1, `Триммер');

insert into KONSTRUKCIA values (2, `Минитрактор');

insert into KONSTRUKCIA values (3, `Газонокосилка');

insert into PROIZVODITEL values (1, `Shtil');

insert into PROIZVODITEL values (2, `Makita');

insert into PROIZVODITEL values (3, `Husqvarna');

insert into PROIZVODITEL values (4, `EFCO');

insert into TIP_DVIGATELA values (1, `Бензиновый');

insert into TIP_DVIGATELA values (2, `Электрический');

insert into TIP_DVIGATELA values (3, `Дизельный');

insert into TIP_DVIGATELA values (4, `Нет');

3.6 Формирование простых и сложных запросов к базе данных

SELЕСТ -- дает пользователю возможность получить информацию из базы данных.

Синтаксис запроса:

SELECT (<имя столбца> <имя столбца>)

FROM (<имя таблицы> <имя таблицы>) [WHERE <Условие выборки>] [ORDER BY <Условие сортировки>]

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

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

Если необходим поиск, удовлетворяющий нескольким требованиям, то используют:

1. Логические операции: AND - обе операции истины, OR - хотя бы одна истина, NOT - поиск по критерию, который не должен быть выполнен.

2.0перации сравнения: =, >, <, >=, <=, < >, !=.

3. Другие: BETWEEN -- операция с явным указанием диапазоном поиска, IN - поиск данных принадлежащих определенному списку, LIKE - поиск по шаблону, ORDER BY -- вслед за командой указывается условие сортировки выданной информации, DESCENDING - сортировать по убыванию, ASCENDING -- сортировать по возрастанию. По умолчанию сортировка осуществляется по возрастанию.

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

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

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

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

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

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

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

1. Показать все данные таблицы «Производитель».

select * from PROIZVODITEL

2. Показать все цвета в таблице «Цвет».

select cvet from cvet

3. Показать фамилию, телефон, адрес и паспортные данные клиента в таблице «Клиенты».

select id_fio, telefon, adres, pasport from klienty

4. Показать модель газонокосилки, год выпуска которой 2014

select model from gazonokosilki where god_vipuska='2014'

5. Вывести фамилию, номер телефона, адрес и паспортные данные клиента, чья фамилия начинается на букву «В».

select id_fio, telefon, adres, pasport from klienty where id_fio like 'В%'

6. Отсортировать клиентов по убыванию

select * from klienty order BY id_fio DESC

7. Вывести все газонокосилки, которые работают на электричестве.

select kod_G, PROIZVODITEL, MODEL, GOD_VIPUSKA, CVET, KONSTRUKCIA, tip_dvigatela, CENA from ((((GAZONOKOSILKI inner join PROIZVODITEL on gazonokosilki.id_proizvoditel =proizvoditel.kod_P) inner join cvet on gazonokosilki.ID_cvet =CVET.kod_c) inner join konstrukcia on gazonokosilki.id_konstrukcia=konstrukcia.kod_kon) inner join tip_dvigatela on gazonokosilki.id_tip_dvigatela=tip_dvigatela.kod_td) where tip_dvigatela=(select tip_dvigatela from tip_dvigatela where tip_dvigatela='Электрический')

8. Вывести всю отсортированную по возрастанию информацию по дате из таблицы «Газонокосилки».

select kod_G, PROIZVODITEL, MODEL, GOD_VIPUSKA, CVET, KONSTRUKCIA, tip_dvigatela, CENA from ((((GAZONOKOSILKI inner join PROIZVODITEL on gazonokosilki.id_proizvoditel = proizvoditel.kod_P) inner join cvet on gazonokosilki.ID_cvet = CVET.kod_c) inner join konstrukcia on gazonokosilki.id_konstrukcia = konstrukcia.kod_kon) inner join tip_dvigatela on gazonokosilki.id_tip_dvigatela = tip_dvigatela.kod_td) ORDER BY god_vipuska ASC

9. Вывести имя клиента и наименование товара купленного по максимальной цене за все время.

select model, id_FIO, CENA from ((JURNAL_PRODAJ inner join gazonokosilki on JURNAL_PRODAJ.name_tovara = gazonokosilki.kod_g) inner join klienty on jurnal_prodaj.fio=klienty.kod_k) where cena=(select max(cena) from gazonokosilki)

10. Вывести информацию о газонокосилках, цена которых ниже 5000.

select kod_G, PROIZVODITEL, MODEL, GOD_VIPUSKA, CVET, KONSTRUKCIA, tip_dvigatela, CENA from ((((GAZONOKOSILKI inner join PROIZVODITEL on gazonokosilki.id_proizvoditel =proizvoditel.kod_P) inner join cvet on gazonokosilki.ID_cvet =CVET.kod_c) inner join konstrukcia on gazonokosilki.id_konstrukcia=konstrukcia.kod_kon) inner join tip_dvigatela on gazonokosilki.id_tip_dvigatela=tip_dvigatela.kod_td) where cena<5000

11. Вывести товар, цена которого выше 5000 и год выпуска не больше чем 2013 год.

select kod_G, PROIZVODITEL, MODEL, GOD_VIPUSKA, CVET, KONSTRUKCIA, tip_dvigatela, CENA from ((((GAZONOKOSILKI inner join PROIZVODITEL on gazonokosilki.id_proizvoditel =proizvoditel.kod_P) inner join cvet on gazonokosilki.ID_cvet =CVET.kod_c) inner join konstrukcia on gazonokosilki.id_konstrukcia=konstrukcia.kod_kon) inner join tip_dvigatela on gazonokosilki.id_tip_dvigatela=tip_dvigatela.kod_td) where cena>5000 and god_vipuska>2013

3.7 Способы повышения производительности доступа к данным

Способы повышения производительности доступа к данным.

Индекс - это упорядоченный (буквенный или числовой) список столбцов или групп столбцов в таблице.

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

Индексы это средство SQL, которое породил сам рынок, а не ANSI. Поэтому сам по себе стандарт ANSI в настоящее время не поддерживает индексы. хотя они очень полезны и широко применяются.

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

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

Триггер - это именованный блок PL/SQL с разделами:

1. Объявлений,

2. Выполняемым разделом,

3. Разделом исключительным ситуации

Триггер выполняется неявно, Всякий раз, когда происходит событие, запускается этот триггер, акт выполнения триггера называется активизация. Запускается триггер операциями DML (insert, update, delete), выполняемых на базе данных СУБД.

Для каждой таблицы можно определить три типа триггеров:

1. три пер ввода;

2. триггер добавления;

3. триггер обновления.

Триггеры можно использовать:

1. для отслеживания модификации данных;

2. для журнализации, регистрации событий;

3. для реализации ряда комплексных организационных правил;

4. для автоматического вычисление столбцов

5. для осуществления сложных процедур защиты данных.

Список создание триггера:

Or replace - пересоздает триггер, если он уже существует,то есть можно заменять триггер без удаления или создания нового

BEFORE - инициализация триггера, перед его исполнением

AFTER - СУБД возбуждает триггер, после исполнения предложения триггера.

DELETE - СУБД возбуждает триггер, каждый раз когда предложение удаляет строку из таблицы

INSERT - СУБД возбуждает триггер, каждый раз когда в предложение вставляется новая строка

UPDATE - указывает, что СУБД возбуждает триггер каждый раз когда выполняется предложение update.

OF - изменение столбца

ON определяет имя триггера, по которому создается триггер. Можно использовать корреляционные имена в блоке PL/SQL и фраза when, чтобы обращаться конкретно к старому или новому значениям столбца текущей строки.

FOR EACH ROW - указывает что триггер представляет собой триггер строк,

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

WHEN - ограничение триггера. Содержит условие SQL, которое должно быть удовлетворено, чтобы СУБД возбудил триггер (только для триггера строк).

1. Предложение триггера. Определение предложения триггера указывает, какие предложение SQL будут заставлять СУБД возбуждать этот триггер.

2. Ограничение триггера. When - дополнительное «условие», которое должно быть удовлетворено для возбуждения строк триггера строк.

3. Действие триггера (тело). Его описывает блок PL/SQL. который СУБД исполняет при возбуждении триггера. Каждый раз, когда выдается предложение триггера, СУБД вычисляет условие ограничения триггера. Если оно удовлетворено, то СУБД возбуждает триггер, исполняя действие триггера.

Создание триггера для проектируемой базы данных:

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

CREATE OR ALTER TRIGGER KLIENTY_BI FOR KLIENTY

ACTIVE BEFORE INSERT POSITION 0

as

begin

if (new.kod_k is null) then

new. kod_k = gen_id(gen_klienty_id,1);

end

2) Так же для других таблиц.

CREATE OR ALTER TRIGGER CVET_BI FOR CVET

ACTIVE BEFORE INSERT POSITION 0

as

begin

if (new.kod_c is null) then

new.kod_c = gen_id(gen_cvet_id,1);

end

CREATE OR ALTER TRIGGER KONSTRUKCIA_BI FOR KONSTRUKCIA

ACTIVE BEFORE INSERT POSITION 0

as

begin

if (new.kod_kon is null) then

new. kod_kon= gen_id(gen_konstrukcia_id,1);

end

CREATE OR ALTER TRIGGER PROIZVODITEL_BI FOR PROIZVODITEL

ACTIVE BEFORE INSERT POSITION 0

as

begin

if (new.kod_p is null) then

new. kod_p= gen_id(gen_proizvoditel_id,1);

end

Триггер отслеживает изменения в таблице СОТРУДНИК и записывает их в таблицу СОТРУДНИК_ЖУРНАЛ.

Сначала создадим новую таблицу, аналогичную исходной, но с добавлением дополнительного столбца, где будет отражаться тип выполненой операции (insert, update, delete).

CREATE TABLE KLIENTY_GURNAL (

KOD_K NUMBER,

ID_FIO VARCHAR2 (30),

TELEFON INTEREG,

ADRES VARCHAR2 (30),

PASPORT NUMBER

create or replace trigger sotr_trigger

before insert or delete or update on KLIENTY for each row

declare

rw1 number;

rw2 varchar2 (30);

rw3 number;

rw4 varchar2 (30);

rw5 number;

begin

if inserting then

begin

rw1:=:new.kod_k;

rw2:=:new.id_fio;

rw3:=:new.telefon;

rw4:=:new.adres;

rw5:=:new.pasport;

rw6:='добавление';

insert into KLIENTY _GURNAL values(rw1,rw2,rw3,rw4,rw5,rw6);

end;

elsif deleting then

begin

rw1:=:old.kod_k;

rw2:=:old.id_fio;

rw3:=:old.telefon;

rw4:=:old.adres;

rw5:=:old.pasport;

rw6:='удаление';

insert into KLIENTY _GURNAL values(rw1,rw2,rw3,rw4,rw5,rw6);

end;

elsif updating then

begin

rw1:=:old.kod_k;

rw2:=:old.id_fio;

rw3:=:old.telefon;

rw4:=:old.adres;

rw5:=:old.pasport;

rw6:='изменение';

insert into KLIENTY _GURNAL values(rw1,rw2,rw3,rw4,rw5,rw6);

end;

end if;

end;

/

commit;

3.8 Процедуры и функции

Процедуры - это блок PL/SQL, в состав которого входят:

Выполняемый раздел;

Раздел исключительных ситуаций.

Процедура сначала:

1. Создается с помощью команды;

2. Компилируется;

3. Сохраняется в компилированном виде в базе данных. С компилированный код можно в последствии выполнить из любого блока PL SQL.

При вызове процедуры, ей можно передать параметры. Вызовом процедуры является оператор PL/SQL и он не может быть частью выражений. При вызове процедуры управление передается одному исполняемому оператору этой процедуры. Когда процедура заканчивается, управление возвращается оператору, следующему за вызовом этой процедуры.

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

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

Формальные параметры бывают трех видов:

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

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

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

Функция -- это блок PL/SQL, в состав которого входят:

1. Раздел объявлений;

2. Выполняемый раздел;

3. Раздел исключительных ситуаций.

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

Синтаксис создания функции:

CREATE [or replace] FUNCTION <имя функции>

[(аргумент 1 [{IN| OUT| IN OUT}] <тип аргументам...

аргумент n [{IN| OUT| IN OUT}] <тип аргумента>)]

RETURN <возвращаемый тип> {IS| AS}

ТЕЛО ФУНКЦИИ.

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

1) Процедура для проектируемой бд:

Процедура NEWTOVAR вставляет строку в таблицу TOVARS.

CREATE OR REPLACE PROCEDURA NEW_PRODAJA (

KOD_JP IN JURNAL_PRODAJ. KOD_JP%TYPE,

NAME_TOVARA IN JURNAL_PRODAJ.NAME_TOVARA %TYPE,

FIO IN JURNAL_PRODAJ. FIO %TYPE,

D_CENA IN JURNAL_PRODAJ.D_CENA %TYPE,

)

as

BEGIN

INSERT INTO JURNAL_PRODAJ (KOD_JP, NAME_TOVARA, FIO, D_CENA)

VALUES (KOD_JP, NAME_TOVARA, FIO, D_CENA)

END;

3.9 Курсоры

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

Открытие курсора: OPEN <имя курсора>;

При открытии курсора:

1. Анализируются значения переменных привязки;

2. На основе значений переменных привязки определяется активный набор, то есть набор строк, возвращаемый запросом;

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

Считывание строк из курсора:

Выполняется с помощью оператора FETCH.

FETCH <имя курсора> INTO <список переменных>;

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

FETCH <имя курсора> INTO < запись PL/SQL >;

В случае, если считывание строки происходит в запись PL/SQL, а переменная типа «запись», мы можем получить одну строку, но содержащие раздородные данные. Закрытие курсора: CLOSE <имя курсора>;

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

Курсорные атрибуты добавляются к имени курсора в блоке PL/SQL подобно атрибутам % type и % rowtype.



4. Декомпозиция проектируемой системы

Проектируемая система ООО «Садовая техника» состоит из следующих подсистем:

Рис. 1 Структура проектируемой системы ООО «Садовая техника»

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

Проектируемая система ООО «Садовая техника» должна содержать подсистему ввода, которая включает:

1. модуль приветствия;

2. модуль идентификации;

3. модуль авторизации;

4. модуль главного меню.

Подсистема хранения данных -- это непосредственно сама БД под управлением Oracle 10g Express Edition (Oracle Database XE).

Подсистема вывода -- это система, которая отображает всю базу данных или часть ее в табличной, диаграммной или картографической форме. Подсистема вывода ООО «Садовая техника» содержит:

· справочник клиентов;

· список комплектующих

· справочник техники;

· операции покупок;

4.1 Проектирование и реализация подсистем

4.1.1 Подсистема хранения

Учитывая тот факт, что база данных по предприятию разрабатывается с точки зрения руководителя и, проанализировав предметную область предприятия ООО «Садовая техника», были определены и созданы следующие сущности базы данных.

· Сущность Газонокосилки - содержит информацию о техники на продажу.

· Сущность Клиент - содержит информацию о заказчиках, с которыми сотрудничает предприятие;

· Сущность Журнал продаж - содержит информацию о заказах, которые выполняет предприятие;

· Сущность Цвет - содержит информацию о возможных цветовых решениях техники;

· Сущность Конструкция - содержит перечень возможных конструкций техники.

· Сущность Тип двигателя - содержит перечень возможных двигателей техники.

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

После определения атрибутов необходимо определить первичные (РК) и внешние ключи (FK):

1. Сущность Клиент - № Клиента (РК);

2. Сущность Газонокосилка - № Газонокосилки (РК);

3. Сущность Заказ - № Заказа (РК),

4. Сущность Цвет - № Цвета (РК).

5. Сущность Конструкция - № Конструкции (РК).

6. Сущность Производитель - № Производителя (РК).

7. Сущность Тип двигателя - № Двигателя (РК).

проектирование ER - модели в реляционную происходит разбиение связи многие-ко-многим:

Сущность Заказ Клиент -- содержит информацию о заказе, который произвел клиент;

После проектирование ER-модели в реляционную необходимо провести процесс нормализации и денормализации:

Нормализация: 1НФ

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

Сущности все соответствуют 1НФ, так как имеют первичные ключи и не имеют повторяющихся групп.

Нормализация: 2НФ

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

Сущности все соответствуют 2НФ, так как представлены в 1НФ и имеют простой первичный ключ.

Нормализация: ЗНФ

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

Сущности все соответствуют ЗНФ, так как представлены во 2НФ и между не ключевыми атрибутами нет взаимосвязей.

Нормализация: 4НФ (НФ Бойса - Кодда)

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

Сущности все соответствуют 4НФ, так как представлены в ЗНФ и ни первичный ключ, ни какая-либо его часть не зависят от не ключевого атрибута.

* Проводить процесс денормализации не следует, так как для этого не существует веских причин.

Для базы данных ООО «Садовая техника» были реализованы таблицы, представления, индексы, триггеры, курсоры, процедуры, выполнено заполнение таблиц.

4.1.2 Подсистема ввода и вывода

Функциональная структура подсистемы ввода приведена на рис. 2. Реализация подсистемы ввода информации осуществлена при помощи delphi RAD studio XE5 - средства разработки приложений на основе объектно-ориентированного программирования.

Для информационной системы ООО «Садовая техника» были разработаны:

Модуль приветствия (UnitHello) появляется при запуске приложения