Охрана труда. Практическое занятие 4. Занятие 4 Разработка базы данных Учет средств пожаробезопасности в среде Microsoft Access
 Скачать 1.16 Mb.
|
Практическое занятие № 4Разработка базы данных «Учет средств пожаробезопасности» в среде Microsoft Access Цель любой информационной системы - обработка данных об объектах реального мира. В широком смысле слова БД - совокупность сведений о конкретных объектах реального мира в какой-либо предметной области. Создавая БД, пользователь стремится упорядочить информацию по различным признакам и быстро извлекать выборку с произвольным сочетанием признаков. Сделать это можно лишь в том случае, если данные структурированы. Структурирование - это введение соглашений о способах представления данных. В современной технологии БД предполагается, что создание БД, ее поддержка и обеспечение доступа пользователей к ней осуществляются централизованно с помощью специального программного инструментария — СУБД [11]. Цель работы: освоить навыки построения реляционных баз данных и выполнения запросов к базе данных. Основные понятия баз данных (БД)БД - поименованная совокупность структурированных данных, относящихся к определенной предметной области. СУБД - это комплекс программных и языковых средств, необходимых для создания БД, поддержания их в актуальном состоянии и организации поиска в них нужной информации. Под предметной областью подразумевают модель определенной части реального мира. Описание предметной области включает в себя описание объектов (сущностей), их свойств и взаимоотношений. Информационный объект (ИО) - описание некоторой сущности (реального объекта, явления, процесса, события) в виде совокупности логически связанных реквизитов (атрибутов). Под реквизитом понимается некоторая характеристика объекта, позволяющая устанавливать его сходство и различие по отношению к другим объектам. Состав реквизитов ИО определяет его структуру. ИО имеет множество реализаций - экземпляров, каждый из которых представлен совокупностью конкретных значений реквизитов и идентифицируется (однозначно определяется) значением ключа. Структурные связи ИО - бинарные отношения между парами ИО. Они характеризуются реальными отношениями между экземплярами разных ИО. Понятие «база данных» тесно связано с такими понятиями структурных элементов, как поле и запись. Поле - элементарная единица логической организации данных, которая соответствует неделимой единице информации - реквизиту. Для описания поля используются следующие характеристики: имя; тип; длина; точность. Запись - совокупность логически связанных полей. Экземпляр записи - отдельная реализация записи, содержащая конкретные значения ее полей. Ядром любой БД является модель данных. Модель данных - совокупность структур данных и операций их обработки. СУБД основывается на использовании иерархической, сетевой или реляционной модели, на комбинации этих моделей или некотором их подмножестве. Иерархическая модель данных. К основным понятиям иерархической структуры относятся: уровень, элемент (узел), связь. Узел - это совокупность атрибутов данных, описывающих некоторый объект. На схеме иерархического дерева узлы представляются вершинами графа. Каждый узел на более низком уровне связан только с одним узлом, находящимся на более высоком уровне. Иерархическое дерево имеет только одну вершину (корень дерева), не подчиненную никакой другой вершине и находящуюся на самом верхнем (первом) уровне. К каждой записи БД существует только один путь от корневой записи [5]. Сетевая модель данных. В сетевой структуре каждый элемент может быть связан с любым другим элементом. Примером такой сетевой структуры может быть БД, содержащая сведения о студентах, участвующих в научно-исследовательских работах (НИРС). Возможно участие одного студента в нескольких НИРС, а также нескольких студентов в разработке одной НИРС. Реляционная модель данных. Эта модель характеризуется простотой структуры данных: удобным для пользователя представлением и возможностью использования формального аппарата алгебры отношений. Реляционная модель ориентирована на организацию данных в виде двухмерных таблиц. Каждая реляционная таблица (отношение) представляет собой двухмерный массив и обладает следующими свойствами: каждый элемент таблицы - один элемент данных; все столбцы в таблице однородны, т. е. все элементы в столбце имеют одинаковые тип (числовой, символьный и т. д.) и длину; у каждого столбца - уникальное имя; одинаковые строки в таблице отсутствуют; порядок следования строк и столбцов может быть произвольным. Поле, каждое значение которого однозначно определяет соответствующую запись, называется простым ключом (ключевым полем). Если записи однозначно определяются значениями нескольких полей, то такая таблица БД имеет составной ключ. Чтобы связать две реляционные таблицы, необходимо ключ первой таблицы ввести в состав ключа второй таблицы (возможно совпадение ключей); в противном случае нужно ввести в структуру первой таблицы внешний ключ – ключ второй таблицы. Создание БД необходимо начинать с ее проектирования. В результате проектирования должны быть определены: состав реляционных таблиц; их структура (состав столбцов, тип данных каждого столбца, их размер, ключ таблицы); логические связи. Проектирование БД осуществляется в три этапа: разработка информационно-логической модели предметной области; определение логической структуры БД; разработка физической структуры БД. Все ИО предметной области связаны между собой. Различаются связи нескольких типов: один к одному (1:1) - каждому экземпляру одного ИО соответствует только один экземпляр другого ИО; один ко многим (1: N ) - каждому экземпляру одного ИО могут соответствовать несколько экземпляров другого ИО; многие ко многим (N:M) - каждому экземпляру одного ИО могут соответствовать несколько экземпляров другого ИО, и наоборот. Одни и те же данные могут группироваться в таблицы различными способами. Группировка атрибутов в таблицах должна быть рациональной, т. е. минимизирующей дублирование данных и упрощающей процедуры их обработки. После определения логической структуры приступают к физической реализации БД средствами СУБД. СУБД Access поддерживает реляционную модель данных; ориентирована на работу с объектами, к которым относятся таблицы БД, формы, запросы, отчеты, макросы и модули. Таблицы создаются пользователем для хранения данных по одному объекту модели данных предметной области. Запросы формируются пользователем для выборки нужных данных из одной или нескольких связанных таблиц. Запрос может формироваться с помощью запросов по образцу (QBE) или посредством языка структурирования запросов SQL. Исходя из запроса, можно также обновить, удалить или добавить данные в таблицы или создать новые таблицы на основе существующих. Формы предназначены для ввода, просмотра и корректировки взаимосвязанных данных базы на экране в удобном виде, который может соответствовать привычному для пользователя документу. Формы также могут служить для создания панелей управления в приложении пользователя. Отчеты нужны для формирования выходного документа, предназначенного для вывода на печать. Макросы содержат описание действий, которые должны быть выполнены в ответ на некоторое событие. Каждое действие реализуется макрокомандой. Выбор макрокоманд и задание параметров, используемых ими при выполнении, является простой автоматической операцией. Макрос позволяет объединить разрозненные операции обработки данных в приложения. Модули включают в себя программы на языке Visual Basic, которые могут разрабатываться пользователем для реализации нестандартных процедур при создании приложения. Реляционная БД создается в соответствии с ее структурой, полученной в результате проектирования. Создание реляционной БД с помощью СУБД начинается с задания структуры таблиц. Затем формируется cхема данных, в которой устанавливаются связи между таблицами. Создание таблицы БД состоит из двух этапов. На первом этапе определяется ее структура: состав полей (атрибутов), их имена, последовательность размещения в таблице, тип данных, размер, ключевые поля и другие свойства полей, на втором создаются записи таблицы и идет заполнение их данными. Создание таблицы можно проводить тремя основными способами: с помощью мастера таблиц; в режиме таблицы; посредством конструктора таблиц. Создание таблиц в окне конструктора предоставляет более широкие возможности по определению параметров формируемой таблицы, чем с помощью мастера или в режиме таблицы. При этом необходимо определить наименование поля и тип данных. При задании наименований полей нужно подчиняться ряду правил: наименование поля может содержать до 64 символов, но нежелательно давать слишком короткие имена; нельзя употреблять точки, восклицательный знак, прямые скобки; не следует начинать наименование поля с пробела; два поля в одной таблице не могут иметь одинаковое название. Наименование поля используется для ссылки на данные таблицы. Для определения хранимых данных используется тип данных. В СУБД Access допустимы данные следующих типов: текстовый; числовой; денежный; счетчик; даты/времени; логический; поле MEMO; поле объекта OLE; мастер подстановок. В Access существует два близких понятия: «индекс» и «ключ». Индексы служат для ускорения поиска данных в таблицах, например, при регулярном поиске однофамильцев (Иванов) в таблице Клиенты. В зависимости от числа полей, используемых в индексе, различают простые и составные индексы, например менеджер + Иванов. Индексы создаются при сохранении макета таблицы и автоматически обновляются при вводе и изменении записей. Индексы могут быть неуникальными. Первичный ключ рассчитан на однозначную идентификацию записи. Его значение для каждой записи должно быть уникально. Значение ключа запрашивается при создании таблицы и может быть изменено при ее модификации. Первичный ключ служит также для установления связей между таблицами. Чаще всего связывается первичный ключ одной таблицы с совпадающими полями другой. В отношении «один ко многим» главной является таблица, которая содержит первичный ключ и составляет часть «один» в отношении. Внешний ключ - это поле или поля, содержащие такой же тип информации в таблице со стороны «много». Эту вторую таблицу называют подчиненной. Порядок выполнения работыВ ходе выполнения работы необходимо разработать базу данных, предназначенную для учета средств пожаробезопасности на предприятии. Для этого необходимо проанализировать предметную область и определиться с функциями, возлагаемыми на базу данных. «На предприятии есть несколько помещений, в каждом из которых могут находиться средства пожаробезопасности. За каждое помещение предприятия отвечает один из сотрудников». В данной постановке задачи требуется выделить объекты, сведения о которых будут храниться. Правила построения баз данных требуют выделения каждого объекта в отдельную таблицу. Можно явно выделить следующие объекты: помещение, средство пожаробезопасности, сотрудник. Далее необходимо в каждом объекте выделить существенные для рассматриваемых объектов свойства - это и будет та информация, которую сохранит база данных. Для «помещения» необходимо знать: номер, этаж, площадь, тип помещения (к разным помещениям могут применяться разные меры оценки безопасности; это может быть кабинет, жилое помещение, подсобное помещения и т.д.) и ответственного за это помещение сотрудника. О «сотруднике» необходимо знать следующее: табельный номер, фамилию, имя, отчество, должность. Важной информацией о средстве пожаробезопасности является: инвентарный номер, помещение, в котором оно располагается, и его тип. Следует отметить, что типы помещения, средства пожаробезопасности, а также должность сотрудника являются самостоятельными, хоть и небольшими объектами. Для начала необходимо запустить MS Access и создать новую базу данных, как на рисунке 13.  Рисунок 13 – Создание базы данных На вкладке Создание необходимо выбрать пункт Таблица (рисунок 14), назвать таблицу, например «Должность».  Рисунок 14 – Создание таблицы На панели инструментов выбрать режим Конструктор, после чего отобразится таблица, как на рисунке 15.  Рисунок 15 – Конструктор таблиц В этом режиме каждая запись таблицы соответствует полю создаваемой таблицы. Для каждого поля следует указать тип данных, определяющий, какие значения будет хранить данное поле, и его имя. В конструкторе необходимо набрать все свойства каждого из объектов. При этом сохранить каждую таблицу под соответствующим именем. Среди набора полей (свойств) таблицы необходимо выделить одно «ключевое» поле. Ключевое поле – это поле, значения которого будут однозначно определять объект таблицы. В одной таблице не могут существовать две записи с одинаковым значением ключевого поля. Так, к примеру, для хранения сведений об ответственности сотрудника за пожарную безопасность помещений необходимо знать его имя, фамилию и отчество, но что делать, если вдруг на предприятии работают два человека с одинаковыми фамилией, именем и отчеством, но разными степенями ответственности. С кого спрашивать, если случится пожар? Конечно, в масштабах предприятия вероятность такого случая не очень большая, но в масштабах страны, определенно, можно встретить такие совпадения. Чтобы строго разграничить одного человека от другого, необходимо дать ему такое свойство, которое будет однозначно отличаться. Для этих целей часто применяется «номер». Так у каждого человека есть паспорт, номер которого не повторяется никогда, и зная который, можно однозначно определить этого человека. Такое свойство и будет называться «ключевым». Сначала необходимо создать самостоятельные таблицы, это таблицы типов, представленные на рисунках 16,17,18.  Рисунок 16 – Таблица «Должность»  Рисунок 17 – Таблица «Тип помещений»  Рисунок 18 – Таблица «Средство» В приведенных таблицах первым полем является поле уникального идентификатора (ID, Код), которое имеет тип «счетчик». Данный тип является числовым и автоматически присваивает записям не повторяющиеся (уникальные) значения. Это поле установлено в качестве ключевого, что видно по пиктограмме ключа слева от имени поля. Далее создадим таблицу «Сотрудник» (рисунок 19).  Рисунок 19 – Таблица «Сотрудник» В этой таблице будут храниться сведения о сотрудниках организации. Известно, что сотрудникам в пределах одного предприятия назначают табельные номера, которые индивидуальны для каждого человека, такое поле можно использовать в качестве ключа, так как при этом учитываются данные только одного предприятия. Следует отметить, что поле «должность» имеет тип «числовой», поскольку в этой таблице мы не будет храниться напрямую название занимаемой человеком должности. Для этого есть таблица типов (должностей), только в ней необходимо указать какой код (ID) у этой должности. В нижней части конструктора есть окно свойств поля, где можно указать особенности этого поля в таблице (рисунок 20).  Рисунок 20 – Свойства поля Чтобы обеспечить защиту базы данных от неполных данных, необходимо в большинстве случаев выставить значение «да» в «обязательное поле», это не позволит сохранить в базе запись с незаполненным (пустым) полем. Следует оставить значение этого свойства «нет» только в том случае, если данные сведения не обязательны для заполнения. Так можно сделать необязательными поля «площадь» и «ответственный» таблицы «Помещение» (рисунок 21).  Рисунок 21 – Таблица «Помещение» Здесь поля «тип» и «ответственный» будут также являться ссылками на другие таблицы, а следовательно, будут хранить код типа и иметь «числовой» тип. Аналогичным образом создаются оставшиеся таблицы. Описание структуры таблицы «Средства безопасности» представлено на рисунке 22.  Рисунок 22 – Таблица «Средства безопасности»  После того, как все таблицы созданы, необходимо указать, какие поля являются ссылками на другие таблицы и на какие именно. Для этого необходимо в меню Работа с базами данных выбрать пункт Схема данных, как на рисунке 23.Рисунок 23 – Схема данных В появившемся окне Добавление таблицы необходимо попеременно выделить каждую таблицу и нажать на кнопку Добавить, как на рисунке 24.  Рисунок 24 – Добавление таблиц На схеме будут отображены все таблицы базы данных. Для организации связей между таблицами необходимо щелкнуть по полю таблицы, содержащему ссылку, левой кнопкой мыши и, не отпуская ее, перетащить на соответствующее поле кода (ID) той таблицы, на которую оно ссылается. В результате будет открыто окно Изменение связей, в котором нужно убедиться в правильности указанных полей и выставить галочки, как показано на рисунке 25.  Рисунок 25 – Создание связи  После выполнения вышеперечисленных действий для всех полей-ссылок, получится следующая схема данных, представленная на рисунке 26.Рисунок 26 - Схема данных После того, как структура базы данных создана, можно приступать к заполнению таблиц. Необходимо учесть, что при заполнении таблиц, имеющих поле-ссылку на другую таблицу, в подчиненной таблице уже должны быть данные, чтобы главной было на что ссылаться. В противном случае Access просто не даст сохранить такое значение и выдаст ошибку. Итак, первыми заполним таблицы «Должность», «Средство», «Тип помещений». Эти таблицы наиболее простые и не содержат ссылок на другие таблицы. При заполнении всего лишь нужно перечислить возможные варианты (существующие должности на предприятии, типы помещений и т.д.). Для заполнения таблицы необходимо выбрать её из списка созданных таблиц. Пример заполнения представлен на рисунках 27, 28, 29.  Рисунок 27 – Заполнение таблицы «Средство»  Рисунок 28 – Заполнение таблицы «Тип помещений»  Рисунок 29 – Заполнение таблицы «Должность» Поля ID, соответствующих таблиц изменять не нужно, так как им присваиваются номера автоматически. Следующей таблицей будет «Сотрудник» (рисунок 30).  Рисунок 30 – Заполнение таблицы «Сотрудник» В этой таблице поле «Должность» хранит значение соответствующего ID таблицы «Должность». Теперь обратите внимание на знак «+», стоящий слева от каждой записи. Этот знак позволяет добавлять значения в связанную таблицу по ключевому полю. В рассматриваемом примере таблица «Помещение» имеет ссылку на таблицу «Сотрудник», это ответственный за помещение. Если кликнуть по знаку «+» в таблице «Сотрудник», то откроется связанная таблица «Помещение», в которой будет отсутствовать поле-ссылка. Все записи, которые добавляются в эту таблицу, будут автоматически связываться с текущим сотрудником. Пример добавления связанных данных показан на рисунке 31.  Рисунок 31 – Добавление связанных данных Открыв таблицу «Помещение», можно убедиться в существовании введенных данных (рисунок 32).  Рисунок 32 – Проверка данных Также можно воспользоваться непосредственным добавлением данных в эту таблицу, в случае если представленный до этого способ сложен или непонятен. Имея сведения о помещениях, ответственных за эти помещения, остается только заполнить таблицу средств пожаробезопасности. Для этого можно воспользоваться двумя описанными способами: непосредственным добавлением в таблицу, либо добавлением через связанную таблицу «Помещение». Пример заполнения через связанную таблицу показан на рисунке 33, а непосредственного заполнения - на рисунке 34.  Рисунок 33 – Заполнение через связанную таблицу  Рисунок 34 – Непосредственное заполнение При заполнении всех таблиц базы данных, получится структура данных, удобная для хранения, но не удобная для просмотра. Достаточно сложно воспринимать данные в полях-ссылках, так как они содержат числовые идентификаторы и не несут в себе информации. Для организации просмотра данных из базы данных существует инструмент построения запросов. Запрос на выборку – это команда системе управления базами данных выбрать из всего набора данных только существенные (нужные), удовлетворяющие заданным условиям отбора. Для составления запроса следует воспользоваться мастером запросов (для простых выборок из одной таблицы) или конструктором запросов (для более сложных выборок). Панель создания запросов показана на рисунке 35.  Рисунок 35 – Панель создания запросов При нажатии на кнопку Конструктор запросов откроется окно, в котором требуется выбрать таблицы, значения которых будут использоваться в запросе. Для простоты следует выбрать все таблицы. В результате получится схема, представленная на рисунке 36.  Рисунок 36 – Конструктор запросов В нижней части конструктора есть форма, в которой следует указать интересующий набор полей, как показано на рисунке 37.  Рисунок 37 – Форма запроса Результат запроса будет содержать только те поля всех таблиц, которые указаны в форме. При этом следует знать, что несмотря на разделения данных по таблицам, данные являются целостными за счет связей в таблицах. Можно считать, что база данных - это большая таблица, которая содержит в себе все поля всех таблиц в базе, при этом строки одной таблицы «склеиваются» со строками связанной таблицы по одинаковым значениям связанных полей. Из всего набора следует выбрать интересующие сведения (поля), при этом следует избегать включения в выборку полей-ссылок. В приведенном на рисунке 37 примере выбран список всех помещений (номер, этаж и тип), закрепленные за ними сотрудники (фамилия и должность), а также наименования средств пожаробезопасности в этих помещениях. Результат представлен на рисунке 38.  Рисунок 38 – Результат запроса Такой вид представления данных гораздо удобнее. Подобным образом можно выполнить запрос любой необходимой информации. Зачастую, даже после выбора необходимого набора полей, на экран выводится избыточная информация за счет большого количества строк. Эту информацию можно отсеять, задав условие отбора. К примеру, требуется получить список средств пожаробезопасности только на первом этаже (только в кабинетах, только тех помещений, за которые отвечает Сидоров и т.п.), для этого следует в форме выборки в столбце соответствующего поля задать условие отбора, как показано на рисунке 39.  Рисунок 39 – Условие отбора Для выполнения запроса необходимо нажать на кнопку Выполнить, как на рисунке 40. Результат выполненного запроса представлен на рисунке 41.  Рисунок 40 –Выполнение запроса  Рисунок 41 – Результат отбора Созданные запросы можно сохранить в базе данных для дальнейшего использования. Еще одним мощным инструментом являются групповые операции, которые позволяют получать данные, которые напрямую не содержатся в базе данных. К таким операциям относятся: нахождение максимального или минимального значения, среднего арифметического, подсчета количества и др. Например, требуется подсчитать количество средств пожаробезопасности в каждом помещении. Для этого создадим новый запрос и добавим в него две таблицы, как на рисунке 42. Пример заполнения формы на рисунке 43.  Рисунок 42 – Создание запроса При этом из таблицы «Помещение» выбираются почти все поля, а из связанной таблицы «Средства безопасности» одно любое поле, какое поле – не имеет значения, так как важна не информация о средствах, а только их количество.  Рисунок 43 – Заполнение формы После этого нужно включить режим групповых операций, воспользовавшись кнопкой на панели инструментов (рисунок 44).  Рисунок 44 – Кнопка групповых операций При этом в форму запроса добавится строка групповых операций, как на рисунке 45.  Рисунок 45 – Форма запроса  В поле «Средство» необходимо выбрать пункт «Count», который производит подсчет строк, как на рисунке 46.Рисунок 46 – Выбор операции Остальные поля оставить без изменения. При выполнении запроса получим следующие данные, представленные на рисунке 47.  Рисунок 47 – Результат запроса Принцип работы таких запросов следующий: производится выборка всех данных из всех таблиц по установленным полям формы, при этом, если для одной записи таблицы существуют несколько записей в связанной таблице, то значение этой таблицы будет дублироваться, и в результате будет столько строк, сколько связанных записей будет в дочерней таблице. После выборки к данным применяется групповая операция. Стандартная операция «группировка» заставляет вместо одинаковых значений нескольких строк выводить одну – сгруппированную строку, т.е. строке соответствует группа однотипных объектов. Функция Count выполняет подсчет количества строк (элементов) в группе. Создание базы данныхСоздайте, руководствуясь п. 4.2 собственную базу данных, в которой будут учитываться различные средства пожаробезопасности (не менее 5 наименований). |