Проектирование баз данных с помощью Microsoft Access

МИНИСТРЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ ГОУ НИЖЕГОРОДСКИЙ  ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

ИМ. Р.Е. АЛЕКСЕЕВА

 

ИНСТИТУТ  РАДИОЭЛЕКТРОНИКИ И ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ

КАФЕДРА "ВЫЧИСЛИТЕЛЬНЫЕ СИСТЕМЫ И ТЕХНОЛОГИИ"

Дисциплина "Информатика"

 

 

 

 

Отчет

 

По курсовой работе

Тема: Проектирование баз данных с помощью Microsoft Access.

 

 

 

Выполнил:

Кульнев Андрей Александрович,

 студент группы: 10-В-2

 

 

Проверил:

Панкратова А.З.

 

                                      

 

 

            

 

                                      Нижний Новгород 2011

Введение.

 

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

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

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

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

 

Краткое описание предметной области.

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

  1. список продавцов.
  2. список заказчиков.
  3. список поставщиков книг.
  4. список книг и информации о них.
  5. Список отделов магазина и разделов.
  6. список заказов.
  7. Список поставок.
  8. и т.д.

 

Разрабатываемая БД вымышленного книжного магазина должна выполнять следующие функции:

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

Краткая характеристика СУБД MS ACCESS .

Система управления базами данных Microsoft Access является одним из самых популярных приложений в семействе настольных СУБД. Все версии Access имеют в своем арсенале средства, значительно упрощающие ввод и обработку данных, поиск данных и предоставление информации в виде таблиц, графиков и отчетов. Начиная с версии Access 2000, появились также Web-страницы доступа к данным, которые пользователь может просматривать с помощью программы Internet Explorer. Помимо этого, Access позволяет использовать электронные таблицы и таблицы из других настольных и серверных баз данных для хранения информации, необходимой приложению. Присоединив внешние таблицы, пользователь Access будет работать с базами данных в этих таблицах так, как если бы это были таблицы Access. При этом и другие пользователи могут продолжать работать с этими данными в той среде, в которой они были созданы. Основу базы данных составляют хранящиеся в ней данные. Кроме того, в базе данных Access есть другие важные компоненты, которые называются объектами. Объектами Access являются:

Таблицы – содержат данные.

Запросы – позволяют задавать условия для отбора данных и вносить изменения в данные.

Формы – позволяют просматривать и редактировать информацию.

Отчеты – позволяют обобщать и распечатывать информацию.

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

 

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

 

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

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

Ниже приведены основные этапы  проектирования базы данных:

1 Определение цели создания базы данных.

2 Определение таблиц, которые должна содержать база данных.

3 Определение необходимых в таблице полей.

4 Задание индивидуального значения каждому полю.

5 Определение связей между таблицами.

6 Обновление структуры базы данных.

7 Добавление данных и создание других объектов базы данных.

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

Определение требований к операционной обстановке

Объём памяти, отводимой  под данные.

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

Авторы

Код Автора

Фамилия/Псевдоним

Имя Отчество

4 байта

30 байт

50 байт


 

Тогда размер под данные таблицы составляет:

Dавторы =(4+30+50)*40=3360 байт

Заказы Магазина

КодЗаказаУпоставщика

КодКниги

Название книги

Автор

Кол-во

ДатаОформленияЗаказа

КодПоставщика

Актуальность

4 байта

2 байта

50 байт 

30 байт

2 байта

8 байт

2 байта

0.125 байт


 

Dзаказы магазина=(4+2+50+30+2+8+2+0.125)*1=98.125 байт

Заказы Покупателей

КодЗаказа

КодПокупателя

КодКниги

Кол-во

КодПродавца

Дата Заказа

Актуальность

4 байта

2 байта

2 байта

2 байта

2 байта

8 байт

0.125 байт


 

Dзакзы покупателей=(4+2+2+2+2+8+0.125)*14=281.75 байт

 

Книги

Код Книги

Название

Раздел

Код Автора

Код Поставщика

Год издания

Количество

Цена

ДатаПоставки

4 байта

100 байт 

50 байт

2 байта

2 байта

2 байта

2 байта

8 байт

8 байт


 

Dкниги=(4+100+50+2+2+2+2+8+8)*45=8010 байт

Отделы

Код Отдела

Название отдела

4 байта

10 байт

   

 

Dотделы=(4+10)*4=56 байт

Покупатели

КодПокупателя

Фамилия

Имя

Отчество

Город

Адрес

Страна

Телефон

4 байта

20 байт

20 байт 

20 байт

20 байт 

50 байт 

20 байт

2 байта


 

Dпокупатели=(4+20+20+20+20+50+20+2)*9=1404 байта

Поставщики

Код Поставщика

Название поставщика

Адрес поставщика

Телефон

4 байта

30 байт

50 байт

2 байта


 

Dпоставщики=(4+30+50+2)*7=602 байта

Продавцы

Код Продавца

Фамилия

Имя

Отчество

Название отдела

Дата приема

Контактный телефон

Семейное положение

Возраст

Хобби

4 байта

20 байт

20 байт 

20 байт

10 байт

8 байт

2 байта

20 байт

2 байта

30 байт


 

Dпродавцы=(4+20+20+20+10+8+2+20+2+30)*8=1088 байт

Разделы

Раздел

Код Отдела

Код Продавца

50 байт

2 байта

2 байта


 

Dразделы=(50+2+2)*10=540 байт

 

Тогда суммарный объём  памяти отводимый под данные на месяц:

Dmonth= 2*(Dавторы+ Dзаказы магазина+ Dзакзы покупателей +Dзакзы покупателей +Dкниги +Dотделы +Dпокупатели +Dпоставщики +Dпродавцы +Dразделы) =30880 байт=31Kб.

А теперь рассчитаем приблизительный  объем памяти отводимой под данные на год с учётом тог, что в каждом месяце может быть больше объем данных для хранения чем в выше рассчитанном(поэтому умножаем на 1,5):

Dyear=Dmonth*12*1,5=558 Кб

ER–диаграммa:

 Уточнённая ER–диаграмма книжного магазина.

 

 

 

Создание таблиц .

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

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

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

 

Таблица Авторы содержит информацию об авторах книг ,  книги которых есть в книжном магазине.

И т.д.

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

Заказы Магазина

КодЗаказаУпоставщика

КодКниги

Кол-во

ДатаОформленияЗаказа

КодПоставщика

Актуальность

1

28

20

01.05.2011

4

Да


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

 

Заказы Покупателей

КодЗаказа

КодПокупателя

КодКниги

Кол-во

КодПродавца

Дата Заказа

Актуальность

1

9

20

1

3

28.03.2011

Да

2

10

28

2

6

20.04.2011

Да

3

11

2

15

2

28.03.2011

Да

4

12

34

1

8

29.03.2011

Да

5

13

10

4

1

29.04.2011

Да

6

14

31

15

7

04.04.2011

Да

7

15

15

10

4

14.04.2011

Да

8

16

23

11

5

11.04.2011

Да

9

11

18

3

4

09.04.2011

Да

10

9

3

7

2

09.04.2011

Да

11

15

30

3

7

24.04.2011

Да

12

12

25

9

5

13.04.2011

Да

13

14

14

10

4

28.03.2011

Да

15

17

6

1

1

07.05.2011

Да


Таблица Книги содержит всю информацию о книгах в магазине.

Таблица очень большая поэтому приведу лишь часть скриншота

.

Книги

Код Книги

Название

Раздел

Код Автора

Код Поставщика

Год издания

Количество

Цена

ДатаПоставки

1

Атлас автодорог Подмосковья

Автомобили

1

1

2011

23

100,00р.

22.11.2005

2

Моя любовь - автомобиль

Автомобили

2

3

2010

12

160,00р.

22.11.2004

3

Дураки, дороги и другие особенности национального вождения

Автомобили

2

5

2010

21

255,00р.

22.11.2004

4

Правила дорожного движения 2010

Автомобили

3

3

2007

54

122,00р.

02.06.2006

5

Ошибки начинающих автомобилистов. Советы бывалых

Автомобили

4

2

2008

12

452,00р.

02.06.2006


Таблица Отделы хранит информацию об отделах магазина.

Таблица Поставщики хранит информацию о поставщиках , у которых магазин заказывает книги.

Таблица Покупатели хранит информацию о клиентах магазина.

Таблица Продавцы содержит информацию о продавцах, работающих в магазине.

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

 

Ключевые поля.

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

В Microsoft Access можно выделить три типа ключевых полей: счетчик, простой ключ и составной ключ.

Ключевые  поля счетчика

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

Простой ключ

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

Составной ключ

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

Примечание.   Если определить подходящий набор полей для составного ключа сложно, просто добавьте поле счетчика и сделайте его ключевым. Например, не рекомендуется определять ключ по полям «Имена» и «Фамилии», поскольку нельзя исключить повторения этой пары значений для разных людей.

 

Схема данных.

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

.

 

Целостность Базы Данных.

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

• Связанное поле главной таблицы является ключевым полем или имеет уникальный индекс.

• Связанные поля имеют один тип данных. Здесь существует два исключения. Поле счетчика может быть связано с числовым полем, если в последнем в свойстве Размер поля (FieldSize) указано значение «Длинное целое». А также поле счетчика можно связать с числовым полем, если и в обеих ячейках свойства Размер поля (FieldSize) задано значение «Код репликации».

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

Установив целостность данных, необходимо следовать следующим  правилам.

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

Создание запроса.

Часто запросы в Microsoft Access создаются автоматически, и пользователю не приходится самостоятельно их создавать.

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

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

Запросы на выборку и их использование.

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

Например, в нашем проекте  используется запрос на выборку: объем продаж по месяцам. Вот результат работы этого запроса:

Объем продаж по Месяцам

Месяц

Sum-Кол-во

3

27

4

64

5

1


 

Параметрические запросы и их использование.

Как правило, запросы с  параметром создаются в тех случаях, когда предполагается выполнять  этот запрос многократно, изменяя лишь условия отбора. В отличии от запроса на выборку, где для каждого условия отбора создается свой запрос и все эти запросы хранятся в БД, параметрический запрос позволяет создать и хранить один единственный запрос и вводить условие отбора (значение параметра) при запуске этого запроса, каждый раз получая новый результат. В качестве параметра может быть любой текст, смысл которого определяет значение данных, которые будут выведены в запросе. Значение параметра задается в специальном диалоговом окне. В случае, когда значение выводимых данных должно быть больше или меньше указываемого значения параметра, в поле «Условие отбора» бланка запроса перед параметром, заключенным в квадратные скобки ставится соответствующий знак. Можно также создавать запрос с несколькими параметрами, которые связанны друг с другом логическими операциями И и ИЛИ. В момент запуска на выполнение MS Access отобразит на экране диалоговое окно для каждого из параметров. Помимо определения параметра в бланке запроса, необходимо указать с помощью команды Запрос - Параметры соответствующий ему тип данных:

Примером параметрического запроса в нашем проекте может быть запрос Код_Автор. При выполнении запроса access просит ввести код автора. От этого кода зависит и результат который выведется на экран. Вот скриншоты.

 

Запросы на изменение записей. Удаление.

Эти запросы позволяют удалять  записи из таблиц.

Рассмотрим пример этого запроса в нашей базе данных.

Например, может понадобиться удаление записей  из таблицы заказы покупателей,

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

Вот скриншоты:

 Выбираем из списка запросов Удаление выполненных заказов.

И вот результат:

 

Всего же в моей БД 18  запросов и  каждый относится к какому-то определенному  типу. Вот их список:

1) График Заказов – запрос, на основе которого строится график заказов.

2) График Поставок – запрос, на основе которого строится график поставок.

3) Заказы покупателей со стоимостью – запрос выводящий информацию о заказе покупателей и считающий стоимость заказа.

4) Код_Автор – запрос, позволяющий получить информацию об авторе по его коду.

5) Код_Поставщик – запрос, позволяющий получить информацию о поставщике по его коду.

6) Объем продаж по месяцам – отображает информацию о количестве проданных магазином книг за каждый месяц.

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

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

9) Подведение итогов по отделам за день – сколько продал отдел за конкретный день.

10)  Подсчет остатков по отделам - выводит информацию о книгах которые заканчиваются.

11) Поиск литературы по Автору – ищет книги по фамилии/псевдониму автора.

12) Поиск литературы по названию Книги – выдает информацию о книге по названию.

13)  Поиск литературы по Отделам – ищет книги содержащиеся в введенном отделе.

14) Поиск Литературы по разделам – ищет книги по введенному разделу.

15) Продавцы-кондидаты  на премию – выдает список продавцов на премию и назначает премию.

16) Учет продаж по отделам – выдает список заказов отдела.

17)Удаление выполненных заказов – удаляет не актуальные заказы.

18)Удаление выполненных поставок – удаляет не актуальные поставки.

 

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

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

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

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

Каждая вкладка объединяет в себе набор схожих действий над БД. Каждая кнопку при нажатие выполняет какое-то действие. Чаще всего это встроенный макрос открытия формы или отчета.

 

С помощью формы заказы покупателей можно посмотреть интересующую информацию о заказах покупателей.

С помощью формы Заказы магазина можно посмотреть информацию о заказах магазина.

 

 

Форма поиск литературы по автору:

 

С помощью формы книги можно изменить информацию о книгах. Но только поле «Цена» и «Количество» т.к. Эти параметры могут меняться со временем. Остальные поля были защищены от изменений.

Всего же в моем проекте 26 форм. Вот их список:

1) Авторы – отображает информацию об авторах книг.

2) Главная – форма, запускающаяся при запуске базы данных благодаря которой можно работать с БД пользователю, не “влезая вовнутрь” БД.  

Проектирование баз данных с помощью Microsoft Access