Проектирование реляционной базы данных с использованием СУБД MS Access

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

Задание на курсовую работу

Разработать проект базы данных в соответствии с описанием предметной области. Создать контрольный экземпляр базы с помощью СУБД MS Access.

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

Фирма занимается продажей сотовых телефонов.

Обязательная информация об имеющихся на складе сотовых телефонах:

· Название

· Цена изготовителя

· Особые характеристики

· Транспортные издержки

· Предпродажные издержки

· Количество

Обязательная информация о клиенте и его заказах:

· Номер заказа

· ФИО

· Почтовый адрес

· Почтовый индекс

· Телефон

· Дата заказа

· Название товара

· Скидка

· Оформил менеджер

Обязательная информация о менеджере:

· Личный номер

· ФИО

· Номер офиса

Обязательная информация об офисах:

· Номер офиса

· Город

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

Процесс разработки должен включать следующие этапы:

1. Инфологическое проектирование:

· Определение сущностей и их атрибутов

· Определение ключевых атрибутов

· Определение типов связей

· Создание диаграммы «сущность-связь»

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

· Определение набора отношений исходя из структуры инфологической модели

· Нормализация отношений с приведением к форме Бойса-Кодда

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

· Вербальное описание бизнес-правил

3. Физическое проектирование контрольного экземпляра базы с помощью СУБД MS Access:

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

· Создание схемы базы данных

· Создание форм, учитывающих бизнес-правила

· Создание отчетов

Введение

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

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

1. Инфологическое проектирование

1.1 Анализ предметной области

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

Таблица 1.1

Анализ предметной области

Сущность	Атрибуты	Ключевые атрибуты
Офисы	Номер офиса Город	Номер офиса
Менеджеры	Личный номер ФИО Номер офиса Сумма продаж	Личный номер
Заказы	Номер заказа Код клиента Дата заказа Код телефона Изготовитель Количество Скидка Цена Сумма оплаты Личный номер (менеджера)	Номер заказа
Клиенты	Код клиента ФИО Почтовый адрес Почтовый индекс Телефон Скидка Общая сумма	Код клиента
Телефоны	Изготовитель Код телефона Цена изготовителя Особые характеристики Транспортные издержки Предпродажные издержки Цена Количество	Изготовитель Код телефона

Рисунок 1.1 Анализ предметной области

1.2 Создание диаграммы «сущность-связь»

Диаграмма «сущность-связь», созданная с помощью case-системы MS Visio, представлена на рисунке 1.2.



Рисунок 1.2 ER-диаграмма

1.3 Диаграмма потоков данных

Диаграмма потоков данных, на основе которой впоследствии будут составлены бизнес-правила, изображена на рисунке 1.3.



Рисунок 1.3 Диаграмма потоков данных

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

2.1 Нормализация отношений с приведением к форме Бойса-Кодда

база данный аccess

Отношения «Офисы», «Менеджеры», «Клиенты», «Телефоны» находятся в первой, второй, третьей форме и в форме Бойса-Кодда.

Отношение «Заказы» находится в первой и во второй нормальной форме, при его проверке на соответствие третьей нормальной форме обнаружена транзитивная зависимость:

Номер заказа > Код клиента >Скидка.

Для того, чтобы привести это отношение к 3NF, необходимо исключить атрибут «Скидка». Данный атрибут содержится в отношении «Клиенты», поэтому значение поля «Скидка» всегда можно выяснить, обратившись к этому отношению.

Отношение «Заказы» также находится в форме Бойса-Кодда.

Результат нормализации отражен в исправленной ER-диаграмме на рисунке 2.1



Рисунок 2.1 - ER-диаграмма после нормализации

2.2 Описание бизнес-правил

1. Каждый клиент обслуживается тем менеджером, который свободен в данный момент времени.

2. Скидка клиента зависит от общей суммы приобретенного им товара за все время работы с фирмой. При общей сумме заказов клиента

· от 10000 до 50000 руб. скидка 2%;

· от 50000 до 15000 руб. - 5 %;

· от 150000 до 500000 руб. - 10%;

· свыше 500000 руб. - 15%

1. В один заказ входит только одно наименование товара.

2. Цена телефона складывается из цены изготовителя, транспортных и предпродажных издержек, на полученную сумму идет надбавка 30%.

3. В таблице «Заказы» поле «Сумма оплаты» заполняется с учетом количества покупаемого товара и скидкой клиента, в поле «цена» вносится цена одной единицы товара.

4. Сумма продаж каждого менеджера складывается из суммы оплаты всех реализованных им заказов.

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

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

Перед созданием таблиц в MS Access были подготовлены таблицы в MS Excel. Каждая из них должна находиться на отдельной странице рабочей книги, первые строки должны содержать заголовки. Далее в Access с помощью команды Файл > Внешние данные > Импорт запускается мастер, следуя инструкциям которого можно перенести данные из Excel. Таким образом были созданы таблицы проектируемой базы данных: «Офисы», «Менеджеры», «Заказы», «Клиенты», «Телефоны». Для описания таблиц, типов данных используется режим конструктора. Таблицы «Телефоны» и «Заказы» в режиме конструктора представлены на рисунке 3.1, таблицы «Офисы», «Менеджеры» и «Клиенты» - на рисунке 3.2. В нижней части рисунков находится окно свойств выделенного поля. Каждому полю таблиц соответствует свой формат, например, если поле «Сумма оплаты» должно хранить значение денежной суммы, то устанавливаем денежный формат этого поля. Также, если хранится обычный текст («ФИО») или простое число («Количество»), устанавливаем соответственно текстовый и числовой формат.

Таблицы приведены в приложении А.



Рисунок 3.1 - Таблицы «Телефоны» и «Заказы» в режиме конструктора



Рисунок 3.2 - Таблицы «Офисы», «Менеджеры» и «Клиенты» в режиме конструктора

3.2 Создание схемы базы данных

После описания всех таблиц создается схема базы данных. Она отражает связи таблиц и обеспечивает целостность данных. Схема проектируемой базы данных представлена на рисунке 3.3, связь между таблицами «Телефоны и Заказы» (в качестве примера) - на рисунке 3.4.



Рисунок 3.3. Схема данных

Рисунок 3.4. Связь между таблицами «Телефоны» и «Заказы»

После включения таблиц в связь, обеспечивающую целостность данных, можно увидеть для каждой отдельной записи одной таблицы все связанные с ней записи других таблиц. Например, на рисунке 3.5 видно, что телефон марки Alcatel 535 был куплен и оформлен в заказах с номерами 22 и 29. Рисунок 3.6 демонстрирует, что в офисе №573 в г. Хабаровске работают менеджеры с личными номерами 5701 и 5702. Менеджер с личным номером 5701 по фамилии Кривоносенко оформил заказы с номерами 4 и 8.



Рисунок 3.5 - Демонстрация связи между таблицами «Телефоны» - «Заказы»

Рисунок 3.6 - Демонстрация связей между таблицами «Офисы» - «Менеджеры» - «Заказы»

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

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

1. Форма «Телефоны» предназначена для регистрации поступления новых моделей телефонов на склад, занесения всех необходимых записей, а также изменения данных об уже существующих моделях, например, при изменении цены или количества. На рисунке 3.7 изображена форма «Телефоны» в режиме формы, а на рисунке 3.8 - процесс корректировки формы в режиме конструктора.

Рисунок 3.7 - Форма «Телефоны»

В форме сведено к минимуму количество заполняемых вручную полей, такого заполнения требуют лишь поля «Код телефона», «Цена изготовителя» и «Количество». Значения полей «Изготовитель» и «Особые характеристики» выбираются из списков. При получении фокусов полями «Трансиздержки», «Предприздержки», «Цена», эти поля заполняются автоматически с учетом бизнес-правил и при условии заполненного поля «Цена изготовителя». Ниже приведен текст процедур, с помощью которых происходит заполнение названых полей:

Option Compare Database

Private Sub Предприздержки_GotFocus() //получение фокуса полем Предприздержки

Dim pr

pr = Forms![Телефоны]![Цена изготовителя] * Forms![Главная]![предпр]

Forms![Телефоны]![Предприздержки] = pr

End Sub

Private Sub Трансиздержки_GotFocus()

Dim tr

tr = Forms![Телефоны]![Цена изготовителя] * Forms![Главная]![Поле24]

Forms![Телефоны]![Трансиздержки] = tr

End Sub

Private Sub Цена_GotFocus()

Dim zena

zena = (Forms![Телефоны]![Цкна изготовителя] + +Forms![Телефоны]![Предприздержки] + Forms![Телефоны]![Трансиздержки]) * 1.3

Forms![Телефоны]![Цена] = zena

End Sub



Рисунок 3.8 - Создание формы «Телефоны» в режиме конструктора

2. Формы «Клиенты», а также «Офисы и менеджеры» идентичны по составу: они имеют в своем составе подчиненную форму. Форма «Офисы и менеджеры» (рисунок 3.9) предназначена для ввода и корректировки данных в таблицы «Офисы» и «Менеджеры», например, можно открыть новый офис, принять на работу или уволить очередного менеджера. Эта форма удобна тем, что при выборе определенного офиса сразу видны данные о каждом менеджере, который в нем работает.



Рисунок 3.9 - Форма «Офисы и менеджеры в режиме формы и в режиме конструктора»

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



Рисунок 3.10 - Форма «Клиенты» в режиме конструктора и в режиме формы

3. Подробно рассмотрим форму «Заказы» (рисунок 3.11, 3.12). Через эту форму производится оформление заказов, изменяются записи сразу в четырех таблицах.

Поле «Номер заказа» заполняется автоматически по счетчику. В поле «Дата» выставляется текущая дата:

Рисунок 3.12 - Свойства поля «Дата» на форме «Заказы»

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



Рисунок 3.13 - Свойства поля со списком для выбора существующего клиента

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



Рисунок 3.14 - Кнопка «Добавить нового клиента» и форма «Новый клиент»

Под надписью «Выбрать телефон» находится список, который показывает данные об имеющихся на складе телефонах. Данные для списка формирует запрос «Телефоны для заказов». При выборе мышью определенной строки списка заполняются поля формы «Код телефона», «Изготовитель», «Цена» при помощи процедуры обработки событий. Процесс создания списка показан на рисунке 3.15.

Значение поля «Количество» вводится вручную.

Рисунок 3.15 - Свойства списка для выбора телефонов

Значение поля «Сумма оплаты» (рисунок3.16) заполняется автоматически при получении этим полем фокуса с помощью процедуры.

Рисунок 3.16 - Поле «Сумма оплаты»

Текст процедуры:

Private Sub Сумма_оплаты_GotFocus()

Dim sym

sym = Forms!Заказы![Количество] * Forms!Заказы!Список20.Column(2) - (Forms!Заказы![Количество] * Forms!Заказы!Список20.Column(2)) * Forms!Заказы!ПолеСоСписком18.Column(2) * 0.01

Forms!Заказы![Сумма оплаты] = sym

End Sub

Менеджер, который оформляет заказ, выбирается из списка (рисунок 3.17)

Рисунок 3.17 - Поле со списком для выбора менеджера, оформляющего заказ

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

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

Описание работы кнопки представлено на рисунке 3.18

Рисунок 3.18 - Кнопка «Принять заказ»

4. «Главная» форма (рисунок 3.19) предназначена для управления всеми необходимыми пользователю объектами базы. На форме имеется поле, в котором по умолчанию устанавливается текущая дата. На поле после надписи «Предпродажные издержки» по умолчанию устанавливается значение 0,4, на поле после надписи «Транспортные издержки» - по умолчанию значение 0,2. Значение этих полей можно изменять, т.е. вводить вручную. Тогда при открытии формы «Телефоны» и при внесении записей о новых моделях издержки будут считаться с учетом введенных значений на главной форме. Форма «Телефоны» открывается при нажатии кнопки «Открыть склад» с помощью макроса (Рисунок 3.20). Точно также, только с помощью своих макросов, открываются формы «Заказы», «Клиенты», «Офисы и менеджеры», «Выбор отчета» по нажатию кнопок «Оформить заказ», «Данные о клиентах», «Менеджеры и офисы», «Отчеты» соответственно.



Рисунок 3.19 - Форма «Главная»

Рисунок 3.20 - Кнопка «Открыть склад»

С главной формы можно открывать таблицы, для этого также предусмотрены кнопки и макросы. Например, при щелчке мышью на кнопке «Офисы» с помощью макроса «Открыть табл Офисы» откроется таблица «Офисы» (Рисунок 3.21)

Рисунок 3.21 - Кнопка «Офисы»

Форма «Главная» запускается при открытии базы. Для этого создан макрос с именем AUTOEXEC (Рисунок 3.22). Этот макрос также запускает запросы на обновление поля «Скидка» во всей таблице «Клиенты».



Рисунок 3.22 - Макрос «AUTOEXEC»

Все макросы и запросы, используемые в формах, хранятся в базе.

3.4 Создание отчетов

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

1. На рисунке 4.1 представлен отчет «Остаток на складе», созданный с помощью мастера, откорректированный в режиме конструктора.

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

2. Отчет «Продажи менеджеров» представлен на рисунке 4.2. Сортировка и группировка была произведена по возрастанию с полями “Номер офиса” и ”Личный номер”. Источником данных для этого отчета был запрос, который хранится в базе:

И в конце подсчитывалась сумма оплаты по каждому офису.

3. Отчет «Продажи менеджеров» изображен на рисунке 4.3. Источником данных этого отчета была таблица “Заказы”. Сортировка и группировка произведены по полям “Изготовитель” и ”Код телефона”. В конце подсчитывалась общая сумма продаж и количество телефонов.

4. На рисунке 4.4 представлен отчет «Скидки клиентов», созданный в режиме конструктора. Источник данных - таблица “Клиенты”. Сортировка произведена по полю “Скидка”.

Размещено на Allbest.ru