Проектирование базы данных и формирование запросов на языке SQL

Реферат 
 

     Данная  курсовая работа посвящена созданию баз данных и приложений в среде  Microsoft Access. Приводятся основы проектирования реляционных баз данных. Дается краткая характеристика СУБД Microsoft Access и основных элементов приложения. Рассматриваются приемы создания простых приложений баз данных и их элементов (таблиц, запросов). Процесс проектирования базы данных и разработки приложения для наглядности иллюстрируется на едином примере создания базы данных СПЕЦОДЕЖДА. При описании интерфейса используется Microsoft Access 2003.

Содержание 
 

Введение

1 Проектирование  базы данных

1.1 Концептуальное  проектирование. Разработка ER-модели предметной области СПЕЦОДЕЖДА

1.2 Логическое  проектирование. Преобразование ER-модели в реляционную модель. Нормализация таблиц

1.3 Физическое  проектирование. Создание в СУБД  Access БД СПЕЦОДЕЖДА

2 Формирование  запросов на языке SQL

Заключение

Список  используемой литературы

      Введение 
 

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

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

     В данной курсовой работе рассматривается:

1. Проектирования базы данных
1. Концептуальное проектирование
2. Логическое проектирование
3. Физическое проектирование
2. Формирования запросов на языке SQL

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

      1 Проектирование базы данных 

     1.1 Концептуальное проектирование. Разработка ER-модели предметной области СПЕЦОДЕЖДА 
 

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

     Основными понятиями ER-диаграммы являются сущность, атрибут, связь.

     Сущность – это некоторый объект реального мира, который может существовать независимо. Сущность имеет экземпляры, отличающиеся друг от друга значениями атрибутов и допускающие однозначную идентификацию. Атрибут – это свойство сущности. Например, сущность “клиент” характеризуется такими атрибутами, как Ф.И.О. клиента, номер договора, дата покупки, телефон, адрес, код модели. Конкретные клиенты являются экземплярами сущности “клиент”. Они отличаются значениями указанных атрибутов и однозначно идентифицируются атрибутом «Ф.И.О. клиента». Атрибут, который уникальным образом идентифицирует экземпляры сущности, называется ключом. Может быть составной ключ, представляющий комбинацию нескольких атрибутов.

     Рассмотрим  проектирование базы данных предприятия, предназначенную для хранения информации, которая будет использоваться для получения оперативных сведений о наличии спецодежды у работников; формировании списка работников, нуждающихся в замене спецодежды; планировании закупок спецодежды и др. Это предприятие имеет цехи. В цехах работают работники, которые в свою очередь  участвуют в получении нескольких видов спецодежды: халаты, тапочки, комбинезоны и др. Описываемую предметную область назовем СПЕЦОДЕЖДА. В ней могут быть выделены четыре сущности: цех, работник, получение и спецодежда.

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

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

     В рассматриваемой предметной области  СПЕЦОДЕЖДА можно выделить три связи.

     1. цех – работает – работник

     2. работник – имеет – получение

     3. спецодежда – составляет –  получение

     На  ER-диаграмме связь изображается ромбом. Например,

     Важной  характеристикой связи является тип связи (кардинальность). Рассмотрим типы связей 1-3.

     Так как в цеху работает несколько  работников, то каждый экземпляр сущности “цех” может быть связан более чем с одним экземпляром сущности “работник”. В этом случае связь 1 имеет тип “один-ко-многим” (1:М). На рис. 1. представлена ER-диаграмма для связи типа 1:М.

     Рисунок 1- ER-диаграмма связи 1:М 

     Так как работник цеха участвует в получении нескольких видов спецодежды, а каждое получение имеет отношение только к одному работнику, то каждый экземпляр сущности “работник” может быть связан более чем с одним экземпляром сущности “получение”, а каждый экземпляр сущности “получение” может быть связан не более чем с одним экземпляром сущности “работник”. В этом случае связь 2 имеет тип “один-ко-многим” (1:М). На рис. 2. представлена ER-диаграмма для связи типа 1:М.

     Рисунок 2- ER-диаграмма связи 1:М 

     Так как один и тот же вид спецодежды поступает несколько раз для получения, а каждое получение относится к одному виду спецодежды, то каждый экземпляр сущности “получение” может быть связан не более чем с одним экземпляром сущности “спецодежда”, а каждый экземпляр сущности “спецодежда” может быть связан более чем с одним экземпляром сущности “получение”. В этом случае связь 3 имеет тип “многие-к-одному” (М:1). На рисунке 3 представлена ER-диаграмма для связи типа М:1.

     Рисунок 3- ER-диаграмма связи М:1 

     Рассмотрим  понятие класс принадлежности сущности.

     Если  каждый экземпляр сущности А связан с экземпляром сущности В, то класс принадлежности сущности А является обязательным. Этот факт отмечается на ER-диаграмме черным кружочком, помещенным в прямоугольник, смежный с прямоугольником сущности А.

     Если  не каждый экземпляр сущности А связан с экземпляром сущности В, то класс принадлежности сущности А является необязательным. Этот факт отмечается на ER-диаграмме черным кружочком, помещенным на линии связи возле прямоугольника сущности А.

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

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

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

     Тогда ER-модель предметной области СПЕЦОДЕЖДА будет иметь вид, представленный на рис. 4.

     Каждая  из четырех сущностей приведенной  ER-модели может быть описана своим набором атрибутов (рис. 5).

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

Рисунок 5- Наборы атрибутов сущностей предметной области СПЕЦОДЕЖДА 

     1.2. Логическое проектирование. Преобразование ER-модели в реляционную модель. Нормализация таблиц 
 

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

     Для каждой сущности создается таблица. Причем каждому атрибуту сущности соответствует  столбец таблицы.

     Правила генерации таблиц из ER-диаграмм опираются на два основных фактора – тип связи и класс принадлежности сущности. Применим их.

     На  ER-диаграмме связи 1:М, представленной на рисунок 4, класс принадлежности сущностей “цех”, “работник” является обязательным. Тогда согласно правилу 4 должны быть сгенерированы две таблицы следующей структуры:

     Цех

     Работник - Цех

     Связь между указанными таблицами будет  иметь вид

     Так как класс принадлежности сущности на стороне 1 не влияет на выбор правил для связи 1:М, то  класс принадлежности сущности “получение” является обязательным, а  класс принадлежности сущности “работник” не учитывается. Тогда согласно правилу 4 должны быть сгенерированы две таблицы следующей структуры:

     Работник

     Получение - Работник

      Следовательно, связь между указанными таблицами будет иметь вид 

     Так как в таблице «Получение - Работник»  уже существует атрибут КР, то его повторно не добавляют.

     На  ER-диаграмме связи М:1, представленной на рис. 4, класс принадлежности сущностей “получение”, “спецодежда” является обязательным. Тогда согласно правилу 4 должны быть сгенерированы две таблицы следующей структуры:

     Спецодежда

     Получение - Спецодежда

      Связь между указанными таблицами  будет иметь вид

      Так как в таблице «Получение - Спецодежда» уже существует атрибут КС, то его повторно не добавляют.

     Анализ  состава атрибутов полученных таблиц A, B, C, D, E, F показывает, что таблица C является составной частью таблицы B, таблица F - составной частью таблицы D. Поэтому таблицы C, F можно исключить из рассмотрения. Оставшиеся таблицы A, B, D, E можно связать посредством связи первичных и вторичных ключей как на рис. 6. В результате получим реляционную модель для ER-модели предметной области СПЕЦОДЕЖДА. 

Рисунок 6 - Реляционная модель предметной области  СПЕЦОДЕЖДА

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

1. Минимизация избыточных данных;
2. Минимальное использование отсутствующих значений (Null-значений);
3. Предотвращение потери информации.

     Минимизировать  избыточность данных позволяет процесс, называемый нормализацией таблиц. Методику нормализации таблиц разработал американский ученый А.Ф.  Кодд в 1970 году. Ее суть сводится к приведению таблиц к той или иной нормальной форме. Были выделены три нормальные формы – 1НФ, 2НФ, 3НФ. Реляционная база данных считается эффективной, если все ее таблицы находятся как минимум в 3НФ. Приведение к 3НФ осуществляется, если есть для этого основания.

     Для пояснения этого процесса  будем исходить из описания предметной области СПЕЦОДЕЖДА и база данных, которая была разработана на ее основе. Очевидным является то, что все таблицы удовлетворяют 1НФ, так как все они содержат только простые неделимые значения. По определению таблица находится во 2НФ, если она удовлетворяет требованиям 1НФ и неключевые поля функционально полно зависят от первичного ключа. В полученной базе данных предметной области СПЕЦОДЕЖДА все поля каждой таблицы функционально полно зависят от своих первичных ключей. Это можно записать следующим образом:

     - для таблицы “Цех”:

     КЦ → НЦ, ФИО_Н

     - для таблицы “Работник”:

     КР → ФИО_Р, ДОЛЖ, СКИД, КЦ

     - для таблицы “Получение”:

     КР, КС → ДП, РОС

     - для таблицы “Спецодежда”:

     КС → ВС, СРОК, СТОИМ

     Таблица находится в 3НФ, если она удовлетворяет  требованиям 2НФ и не содержит транзитивных  зависимостей. Транзитивной  зависимостью называется функциональная зависимость между неключевыми полями. Очевидно, что все созданные таблицы находятся в 3НФ. Таким образом, реляционная модель предметной области СПЕЦОДЕЖДА после нормализации таблиц остается прежней (рис. 6). 
 

     1.3 Физическое проектирование 
 

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

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

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

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

     Рассмотрим  создание структуры таблицы “Цех” базы данных СПЕЦОДЕЖДА для хранения сведений о рабочих. Для этого сначала создадим базу данных:

• откроем СУБД (Пуск/Программы/Microsoft Access);
• в появившемся окне Microsoft Access нажмем Файл/Создать…/Новая база данных…;
• в окне Файл новой базы данных откроем свою рабочую папку, введем имя БД (СПЕЦОДЕЖДА) и нажмем кнопку Создать.

     Далее создаем саму таблицу “Цех”. Для этого:

• в окне СПЕЦОДЕЖДА выбираем вкладку Создание таблицы в режиме конструктора и нажимаем кнопку Enter (или двойное нажатие правой кнопки мыши);
• в окне конструктора задаем структуру таблицы “Цех”

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

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

     Рисунок 7 - Таблица “Цех” базы данных СПЕЦОДЕЖДА 

     Аналогичным способом создадим структуры таблиц “Работник” (рис. 8), “Спецодежда” (рис. 9). “Получение” (рис. 10).

     Рисунок 8 - Таблица “Работник” базы данных СПЕЦОДЕЖДА 

     Рисунок 9 - Таблица “Спецодежда” базы данных СПЕЦОДЕЖДА

     Рисунок 10 - Таблица “Получение” базы данных СПЕЦОДЕЖДА 

      2 Формирование запросов  на языке SQL