База данных «домашний мастер»

При создании таблиц разрабатываемой базы данных следует внести изменения в некоторые свойства полей таблицы. Например, для полей типа «Дата/время» для удобства работы с такими полями и проверки вводимых данных желательно установить формат поля «Краткий формат даты» и маску ввода «00.00.0000;0;_». Маска ввода позволит избежать ошибок при вводе даты и помочь пользователю.Для внешних ключей таблиц следует в свойствах подстановки указать тип элемента управления «Поле со списком» со ссылкой на поля главной таблицы. Это позволит пользователю вместо ввода значений кодов выбирать из списка названия соответствующих объектов, что является не только помощью пользователю при вводе данных, но и средством поддержания целостности данных при корректировке таблиц. Любая корректировка структуры таблицы выполняется в режиме «Конструктор».Разработанная базаданныхсостоитиз15таблиц,как показанонарисунке10.Все разработанные таблицы в режиме конструктора отображены в Приложении А на рисунках 11-25.Работать с данными удобно через формы. Чтобы вводить, просматривать и удалять данные из справочных таблиц, можно воспользоваться ленточными формами. Ленточные формы отображают каждую запись таблицы на одной строке на экране. Ввод данных при работе с ленточными формами выполняется всегда в последнюю строку формы, помеченную знаком «*». Ленточные формы показаны на рисунках 26-30 Приложения А.Для таблиц оперативной информации, которые хранят связанную данные, удобно использовать подчиненные (составные) и связанные формы. Составные формы состоят из двух или трех частей. В первой главной части формы отображаются данные из главной таблицы, а в подчиненных формах отображаются данные из подчиненных таблиц. Составные формы для оперативных таблиц показаны на рисунках 31-33Приложения А.Для обеспечения информационных потребностей пользователей в СУБД MS Access можно воспользоваться отчетами. Отчеты позволяют создавать аккуратно оформленные документы. В отчетах можно формировать заголовки, вычислять промежуточные и общие итоги, настраивать колонтитулы. Отчеты в СУБД MS Accessудобно создавать в режиме мастера. Обычно источником данных отчета являются таблицы или запросы. Если источником данных отчета являются запросы, то их предварительно необходимо создать. Построенные отчеты показаны на рисунках 34-38 Приложения А.Важнымобъектомработы с даннымив MSAccessявляютсязапросы.В рассматриваемой СУБД выделяютследующиевидызапросов:запроснавыборку,прикоторомпроисходитизвлечениеданныхпоуказанномуусловию.Врамкахданногозапросаможет выполнятьсягруппировказаписей и вычисления вполяхтаблицы;запроснаизменение,прикоторомизменяютсяданныевпервоначальныхтаблицах.Спомощьюнихданныемогутподвергатьсякорректировке;запросспараметрами,прикоторомвводятсяопределенныеусловияилиданные;перекрестныезапросы,предназначенныедлярасчетовипредоставленияданных,какправило,вформеэлектронныхтаблицсцельюоблегченияанализа;SQL-запросыпредставляютсобойзапросынаполучениеопределеннойинформации,ихпостроениеоснованонасоблюденииопределенныхправилсиспользованиемопределенногосинтаксиса.Вбазеданныхдлявозможности оперативного получения информациибылосоздано19запросов,результат их работы показан вПриложении А на рисунках 39-57.Текст команд SQLзапросов представлены в Приложении А.Первыйзапрос«Список договоров с клиентами»отображает информацию и заключенных с клиентами договорах.Запрос«Список договоров с поставщиками» показывает данные о договорах, заключенных с поставщиками, в запросе кроме общей информации о договоре отображается наименования комплектующих, их цены, количество, стоимость.Перекрестный запрос «Сертификаты сотрудников» позволяет увидеть, по каким специализациям сотрудники получили сертификаты и, следовательно, к каким работам их можно допускать.Запрос «Выполненные работы» отображает информацию о выполненных специалистами работах, объемах и стоимости.Запрос «Расход комплектующих» позволяет увидеть в натуральном выражении, сколько различных комплектующих было израсходовано (установлено заказчику) на каждую дату.Запрос «Количество специализаций у сотрудников»отображает информацию о количестве специализаций, по которым сотрудники прошли обучение.Запрос «Возраст сотрудников» показывает возраст каждого сотрудника, для этого используется формула: Возраст: Int((Date()-[Дата_рождения])/365).Запрос «Поставки комплектующих» позволяет узнать даты и количество поставок комплектующих каждого вида.Запрос «Поставщики розеток» отображает информацию о тех поставщиках, которые поставили предприятию розетки.Запрос «Работы по ул.Портовой»отображает информацию о работах, которые сотрудники предприятия проводили у клиентов по улице Портовой.Запрос «Поставщики из Краснодара» позволяет найти информацию о всех поставщиках из Краснодара.Запрос «Поставки из Владивостока» позволяет узнать, какие комплектующие были доставлены на предприятие поставщиками из Владивостока.Запрос «Работы по адресам» отображает данные о работах, выполняемых по каждому адресу, указанному в договорах с клиентами.Запрос «Поставщики без договоров» позволяет увидеть, с какими поставщиками предприятие еще не оформило ни одного договора. Запрос «Открытые договоры с клиентами» отображает открытые договоры с клиентами, по которым или еще не выполнены работы, или не получена оплата.Запрос «Открытые договоры с поставщиками» отображает незавершенные договоры с поставщиками.Запрос «Количество видов работ в договоре» позволяет узнать, сколько различных видов работ оформлено в каждом договоре с клиентом.Запрос «Стоимость комплектующих по договору» отображает данные о комплектующих, которые были установлены у клиентов в соответствии с выполненными договорами.Запрос «Оплата клиентов по месяцам» с группировкой по месяцам показывает суммы, которые поступили от клиентов в качестве оплаты выполненных работ.ЗаключениеСегоднямногие предприятия и организации в различной мере используютв своей деятельности информационные системы, в которыхинформация имеет сложную структуру и хранится в базахданных. Актуальность автоматизации заключается во-первых, в необходимости повышения оперативности обработки данных, во-вторых, необходимостью получения структурированных отчетов, в-третьих, в достаточно больших объемах хранимых и обрабатываемых данных. В процессе выполнения работы были пройдены следующие этапы:– проведен анализ предметной областина примере работы предприятия, выполняющего мелкий ремонт квартир;построена и описана организационная структура предприятия;созданафункциональнаямодельпредприятия;– определены основные сущности анализируемой предметной области;– выполнено логическое проектирование базы данных. Результатом явилась построенная в нотации IDEF1Х логическая модель данных;– выполнено физическое проектирование в реляционной СУБД MS Access;– построен макет интерфейса пользователя с разработкой необходимых запросов, форм и отчетов.Разработанная база данных позволяет автоматизировать ввод, поиск и обработкуинформации,которая необходима для оперативного управления предприятием.Для развития данной системы можно предложить использовать возможности MS Officeпри слиянии документов, что может упростить и ускорить процесс формирования договоров как с поставщиками, так и с клиентами.СписокИсточниковАблязов, В. И. Проектирование баз данных в среде Microsoft Office Access 2003, 2007 и 2010: учебное пособие / В.И. Аблязов – СПб.: Изд-во Политехн. ун-та, 2014. – 107 с. Бекаревич, Ю. Б. Самоучитель Microsoft Access 2013 / Ю.Б.Бекаревич, Н.В.Пушкина. – БХВ-Петербург, 2014. –465с.Гурвиц, Г. А. Microsoft Access 2010. Разработка приложений на реальном примере / Г.А.Гурвиц. – М.: ООО «Вильямс», 2010. – 462сКарчевский, Е. М. Access 2010 в примерах: Учебное пособие. / Е.М.Карчевский, И.Е. Филиппов, И.А. Филиппова.–Казанский университет, 2012. – 140с.Кузин, А. В. Базы данных: учебное пособие / А.В.Кузин, С.В.Левонисова. – М.: Издательский центр «Академия», 2012. – 320с.Маклаков, С.В. Создание информационных систем с AllFusion Modeling Suite / С.В. Маклаков. – М.: ДИАЛОГ – МИФИ, 2002. – 256с.Ткаченко, Н. А. Моделирование бизнес-процессов: учебное пособие / Н.А.Ткаченко, Кемеровский институт (филиал) РГТЭУ. – Кемерово: РГТЭУ Кемеровский институт (филиал), 2013.Фуфаев, Э. В. Базы данных : учеб. пособие для студ. учреждений сред, проф. образования / Э.В.Фуфаев, Д.Э.Фуфаев. – 7-е изд., стер. – М.: Издательский центр «Академия», 2012. – 320с.Хомоненко, А. Д. Базы данных: Учебник для вузов / А.Д.Хомоненко, В.М.Цыганков, М.Г.Мальцев; Под ред. А.Д.Хомоненко. – 3-е изд., доп. и перераб. – СПб. : КОРОНА принт, 2009. – 736с. ПриложениеAРисунок – Контекстная диаграмма моделиРисунок – Диаграмм процессов верхнего уровняРисунок – Процесс «Закупка комплектующих»Рисунок – Процесс «Прием и отпуск комплектующих»Рисунок – Процесс «Заключение договора с заказчиком»Рисунок – Процесс «Оказание услуги»Рисунок – Связи между сущностямиРисунок – Логическая модельРисунок 10 – Схема данных Рисунок11–Виды работРисунок 12 – Договор с клиентомРисунок 13 – Договор с поставщикомРисунок 14 – КлиентыРисунок 15 – Комплектующие на складеРисунок 16 – КомплектующиеРисунок 17 – Оплата по договору поставщикамРисунок 18 – Оплата по договору клиентомРисунок 19 – ПоставщикиРисунок 20 – Расход комплектующихРисунок 21 – СотрудникиРисунок 22 – Специализации сотрудниковРисунок 23 – СпециализацииРисунок 24 – Спецификация договора клиентаРисунок 25 – Спецификация договора с поставщикомРисунок 26 – Форма «Комплектующие»Рисунок 27 – Форма «Виды работ»Рисунок 28 – Форма «Специализации»Рисунок 29 – Форма «Поставщики»Рисунок 30 – Форма «Клиенты»Рисунок 31 – Форма «Сотрудники»Рисунок 32 – Форма «Договор с клиентом»Рисунок 33 – Форма «Договор с поставщиком»Рисунок34–Отчет«Договорысклиентами»Рисунок 35 – Отчет «Договоры с поставщиками»Рисунок 36 – Отчет «Сертификаты сотрудников»Рисунок 37 – Отчет «Выполненные работы»Рисунок 38 – Отчет «Расход комплектующих»Рисунок39–Запрос «Список договоров с клиентами»Рисунок 40 – Запрос «Список договоров с поставщиками»Рисунок 41 – Запрос «Сертификаты сотрудников»Рисунок 42 – Запрос «Выполненные работы»Рисунок 43 – Запрос «Расход комплектующих»Рисунок 44 – Запрос «Количество специализаций у сотрудников»Рисунок 45 – Запрос «Возраст сотрудников»Рисунок 46 – Запрос «Поставки комплектующих»Рисунок 47 – Запрос «Поставщики розеток»Рисунок 48 – Запрос «Работы по ул.Портовой»Рисунок 49 – Запрос «Поставщики из Краснодара»Рисунок 50 – Запрос «Поставки из Владивостока»Рисунок 51 – Запрос «Работы по адресам»Рисунок 52 – Запрос «Поставщики без договоров»Рисунок 53 – Запрос «Открытые договоры с клиентами»Рисунок 54 – Запрос «Открытые договоры с поставщиками»Рисунок 55 – Запрос «Количество видов работ в договоре»Рисунок 56 – Запрос «Стоимость комплектующих по договору»Рисунок 57 – Запрос «Оплата клиентов по месяцам»SELECT Клиенты.Фамилия_Клиента, Клиенты.Имя_клиента, Клиенты.Отчество_клиента, Договор_клиент.Номер_договор, Договор_клиент.Дата_договор, Договор_клиент.Состояние, Оплата_работ.Дата_оплата, Оплата_работ.Сумма_оплатаFROM (Клиенты INNER JOIN Договор_клиент ON Клиенты.Код_клиента = Договор_клиент.Код_клиента) LEFT JOIN Оплата_работ ON Договор_клиент.Номер_договор = Оплата_работ.Номер_договор;SELECT Поставщики.Наимен_поставщ, Договоры_постав.Номер_догов_постав, Договоры_постав.Дата_догов_постав, Договоры_постав.Состояние, Комплектующие.Наимен_комплект, Комплектующие.Цена_комплект, Специф_догов_постав.Количество_прих, [Цена_комплект]*[Количество_прих] AS СтоимостьFROM Комплектующие INNER JOIN ((Поставщики INNER JOIN Договоры_постав ON Поставщики.Код_поставщ = Договоры_постав.Код_поставщ) INNER JOIN Специф_догов_постав ON Договоры_постав.Номер_догов_постав = Специф_догов_постав.Номер_догов_постав) ON Комплектующие.Код_комплект = Специф_догов_постав.Код_комплект;TRANSFORM Last(Спец_сотрудн.Дата_сертификата) AS [Last-Дата_сертификата]SELECT Сотрудники.Фамилия_сотрудника, Сотрудники.Имя_сотрудника, Сотрудники.Отчество_сотрудникаFROM Специализации INNER JOIN (Сотрудники INNER JOIN Спец_сотрудн ON Сотрудники.Код_сотрудника = Спец_сотрудн.Код_сотрудника) ON Специализации.Код_спец = Спец_сотрудн.Код_спецGROUP BY Сотрудники.Фамилия_сотрудника, Сотрудники.Имя_сотрудника, Сотрудники.Отчество_сотрудникаORDER BY Сотрудники.Фамилия_сотрудникаPIVOT Специализации.Наимен_спец;SELECT Year([Дата_договор]) AS Год, Сотрудники.Фамилия_сотрудника, Сотрудники.Имя_сотрудника, Сотрудники.Отчество_сотрудника, Договор_клиент.Дата_договор, Виды_работ.Наимен_работы, Специф_догов.Объем, [Расценка]*[Объем] AS СтоимостьFROM Сотрудники INNER JOIN (Договор_клиент INNER JOIN (Виды_работ INNER JOIN Специф_догов ON Виды_работ.Код_работы = Специф_догов.Код_работы) ON Договор_клиент.Номер_договор = Специф_догов.Номер_договор) ON Сотрудники.Код_сотрудника = Специф_догов.Код_сотрудникаORDER BY Договор_клиент.Дата_договор;SELECT Договор_клиент.Дата_договор, Комплектующие.Наимен_комплект, Комплектующие.Цена_комплект, Расход_комплект.Количество_расх, [Цена_комплект]*[Количество_расх] AS СтоимостьFROM Договор_клиент INNER JOIN (Комплектующие INNER JOIN Расход_комплект ON Комплектующие.Код_комплект = Расход_комплект.Код_комплект) ON Договор_клиент.Номер_договор = Расход_комплект.Номер_договор;SELECT Сотрудники.Фамилия_сотрудника, Сотрудники.Имя_сотрудника, Сотрудники.Отчество_сотрудника, Сотрудники.Дата_рождения, Count(Спец_сотрудн.Код_спец) AS [Количество специализаций]FROM Сотрудники INNER JOIN Спец_сотрудн ON Сотрудники.Код_сотрудника = Спец_сотрудн.Код_сотрудникаGROUP BY Сотрудники.Фамилия_сотрудника, Сотрудники.Имя_сотрудника, Сотрудники.Отчество_сотрудника, Сотрудники.Дата_рождения;SELECT Сотрудники.Фамилия_сотрудника, Сотрудники.Имя_сотрудника, Сотрудники.Отчество_сотрудника, Сотрудники.Дата_рождения, Int((Date()-[Дата_рождения])/365) AS ВозрастFROM Сотрудники;SELECT Комплектующие.Наимен_комплект, Договоры_постав.Дата_догов_постав, Комплектующие.Цена_комплект, Специф_догов_постав.Количество_прих, [Цена_комплект]*[Количество_прих] AS СтоимостьFROM Договоры_постав INNER JOIN (Комплектующие INNER JOIN Специф_догов_постав ON Комплектующие.Код_комплект = Специф_догов_постав.Код_комплект) ON Договоры_постав.Номер_догов_постав = Специф_догов_постав.Номер_догов_поставORDER BY Комплектующие.Наимен_комплект, Договоры_постав.Дата_догов_постав;SELECT Поставщики.Наимен_поставщ, Поставщики.Адрес_поставщFROM Комплектующие INNER JOIN ((Поставщики INNER JOIN Договоры_постав ON Поставщики.Код_поставщ = Договоры_постав.Код_поставщ) INNER JOIN Специф_догов_постав ON Договоры_постав.Номер_догов_постав = Специф_догов_постав.Номер_догов_постав) ON Комплектующие.Код_комплект = Специф_догов_постав.Код_комплектWHERE (((Комплектующие.Наимен_комплект) Like "*розетка*"))GROUP BY Поставщики.Наимен_поставщ, Поставщики.Адрес_поставщ;SELECT Клиенты.Фамилия_Клиента, Клиенты.Имя_клиента, Клиенты.Отчество_клиента, Договор_клиент.Адрес_работ, Договор_клиент.Дата_договор, Договор_клиент.СостояниеFROM Клиенты INNER JOIN Договор_клиент ON Клиенты.Код_клиента = Договор_клиент.Код_клиентаWHERE (((Договор_клиент.Адрес_работ) Like "*Портовая*"));SELECT Поставщики.Наимен_поставщ, Поставщики.Адрес_поставщ, Поставщики.Банк_поставщ, Поставщики.Контакт_поставщFROM ПоставщикиWHERE (((Поставщики.Адрес_поставщ) Like "*Краснодар*"));SELECT Комплектующие.Наимен_комплект, Договоры_постав.Дата_догов_постав, Комплектующие.Цена_комплект, Специф_догов_постав.Количество_прих, Поставщики.Наимен_поставщ, Поставщики.Адрес_поставщFROM Комплектующие INNER JOIN (Поставщики INNER JOIN (Договоры_постав INNER JOIN Специф_догов_постав ON Договоры_постав.Номер_догов_постав = Специф_догов_постав.Номер_догов_постав) ON Поставщики.Код_поставщ = Договоры_постав.Код_поставщ) ON Комплектующие.Код_комплект = Специф_догов_постав.Код_комплектWHERE (((Поставщики.Адрес_поставщ) Like "*Владивосток*"));TRANSFORM Last(Специф_догов.Объем) AS [Last-Объем]SELECT Клиенты.Фамилия_Клиента, Договор_клиент.Адрес_работ, Sum([Объем]*[Расценка]) AS [Стоимость работ]FROM Клиенты INNER JOIN (Договор_клиент INNER JOIN (Виды_работ INNER JOIN Специф_догов ON Виды_работ.Код_работы = Специф_догов.Код_работы) ON Договор_клиент.Номер_договор = Специф_догов.Номер_договор) ON Клиенты.Код_клиента = Договор_клиент.Код_клиентаGROUP BY Клиенты.Фамилия_Клиента, Договор_клиент.Адрес_работPIVOT Виды_работ.Наимен_работы;SELECT Поставщики.Наимен_поставщ, Поставщики.Адрес_поставщ, Поставщики.Контакт_поставщ, Договоры_постав.Код_поставщFROM Поставщики LEFT JOIN Договоры_постав ON Поставщики.Код_поставщ = Договоры_постав.Код_поставщWHERE (((Договоры_постав.Код_поставщ) Is Null));SELECT Клиенты.Фамилия_Клиента, Клиенты.Адрес_клиента, Договор_клиент.Дата_договор, Договор_клиент.Состояние, Виды_работ.Наимен_работы, Специф_догов.ОбъемFROM Виды_работ INNER JOIN ((Клиенты INNER JOIN Договор_клиент ON Клиенты.Код_клиента = Договор_клиент.Код_клиента) INNER JOIN Специф_догов ON Договор_клиент.Номер_договор = Специф_догов.Номер_договор) ON Виды_работ.Код_работы = Специф_догов.Код_работыWHERE (((Договор_клиент.Состояние)=False));SELECT Поставщики.Наимен_поставщ, Поставщики.Адрес_поставщ, Договоры_постав.Дата_догов_постав, Договоры_постав.Состояние, Комплектующие.Наимен_комплект, Специф_догов_постав.Количество_прихFROM Комплектующие INNER JOIN (Поставщики INNER JOIN (Договоры_постав INNER JOIN Специф_догов_постав ON Договоры_постав.Номер_догов_постав = Специф_догов_постав.Номер_догов_постав) ON Поставщики.Код_поставщ = Договоры_постав.Код_поставщ) ON Комплектующие.Код_комплект = Специф_догов_постав.Код_комплектWHERE (((Договоры_постав.Состояние)=False));SELECT Договор_клиент.Номер_договор, Клиенты.Фамилия_Клиента, Договор_клиент.Адрес_работ, Count(Специф_догов.Код_работы) AS [Count-Код_работы]FROM (Клиенты INNER JOIN Договор_клиент ON Клиенты.Код_клиента = Договор_клиент.Код_клиента) INNER JOIN Специф_догов ON Договор_клиент.Номер_договор = Специф_догов.Номер_договорGROUP BY Договор_клиент.Номер_договор, Клиенты.Фамилия_Клиента, Договор_клиент.Адрес_работ;SELECT Договор_клиент.Номер_договор, Клиенты.Фамилия_Клиента, Договор_клиент.Адрес_работ, Договор_клиент.Дата_договор, Sum([Цена_комплект]*[Количество_расх]) AS СтоимостьFROM ((Клиенты INNER JOIN Договор_клиент ON Клиенты.Код_клиента = Договор_клиент.Код_клиента) INNER JOIN Расход_комплект ON Договор_клиент.Номер_договор = Расход_комплект.Номер_договор) INNER JOIN Комплектующие ON Расход_комплект.Код_комплект = Комплектующие.Код_комплектGROUP BY Договор_клиент.Номер_договор, Клиенты.Фамилия_Клиента, Договор_клиент.Адрес_работ, Договор_клиент.Дата_договор;SELECT Format([Дата_оплата],"yyyy - mm") AS Выражение1, Sum(Оплата_работ.Сумма_оплата) AS [Sum-Сумма_оплата]FROM Оплата_работGROUP BY Format([Дата_оплата],"yyyy - mm");