|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
|
|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
Отношение находится во второй нормальной форме (2НФ), если оно находится в 1НФ и не содержит неполных функциональных зависимостей.
Следовательно, для приведения БД ко 2НФ, необходимо описание атрибутов входящих в неполные функциональные зависимости выделить в отдельные таблицы.
Тема 2.3. СХЕМЫ БАЗ ДАННЫХ 2.3.1. Логические связи в РБД Приведенную выше таблицу ПЕРЕВОЗКИ (рис. 1) можно рассматривать как БД состоящую из одного отношения. Однако, эта таблица содержит несколько неполных f-зависимостей, что порождает упоминавшихся ранее аномалии обновления и избыточности данных. Кроме того, анализ таблицы показывает, что в столбцах {Водитель}, {Пункт_назначения}, {Марка_АМ}, {Гос_номер}, {Расстояние} одни и те же значения часто повторяются, т.е. БД накапливает дублирующую информацию (избыточность). Кроме того, при вводе одних и тех же значений оператор может допустить незначительную на первый взгляд ошибку - например, набрать лишний пробел или запятую вместо точки и т.п. (слд. 34). В этом случае, при автоматизированной обработке, эти значения будут рассматриваться как несовпадающие (аномалии обновления). Для устранения нежелательных свойств рассматриваемая БД д.б. приведена ко 2НФ. Для устранения указанных недостатков преобразуем БД к другому виду, включающему не одно, а несколько отношений (рис.4) (слд 35 с ЭФ): (там где жирный шрифт сначала оставить место). Описание днотипныех объектов выполняется в отдельных таблицах:
· ПУНКТ_НАЗНАЧЕНИЯ = { КОД_ПН, Имя_ПН, Расстояние} · ВОДИТЕЛИ = { КОД_ВОД, Фамиля_И_О} · АТ = { КОД_АТ, КОД_М, ГН} · МАРКА_АТ = { КОД_М, М_АМ} · ПЕРЕВОЗКИ = {Nр, Дата, КОД_ПН, КОД_ВОД, КОД_АТ, Вес}
При таком преобразовании необходимо установить соответствие между информацией размещаемой в разных таблицах (отношениях). Для установления логической связи между двумя отношениями в РБД, одно из отношений должно содержать первичный ключ, а другое внешний (вторичный) ключ. В рассматриваемом примере (слд 35 с ЭФ), в таблицы АТ, ПН, ВОДИТЕЛИ, МАРКА_АМ в качестве первичных ключей соответственно введены поля КОД_АТ, КОД_ПН, КОД_ВОД, КОД_АТ, КОД_М. Кроме того, в качестве вторичных ключей, в таблицу АТ введено поле КОД_М, а таблицу ПЕРЕВОЗКИ введены поля КОД_ПН, КОД_ВОД, КОД_АТ. Такое увеличение количества таблиц в БД приводит к уменьшению требуемого объема памяти для хранения той же информации и снижению вероятности ошибок при ведении БД. Рассмотрим это на примере двух вышеприведенных вариантов БД. Как правило, предприятие на протяжении некоторого периода имеет постоянные штат водителей, парк автомобилей и маршруты, а количество выполненных за это время рейсов значительно превышает указанное в таблицах ПЕРЕВОЗКИ (рис. 1 и 4). Предположим, что количество выполненных рейсов равно 1000. В следующих разделах будут рассматриваться различные свойства таблиц и их полей, которые устанавливаются на стадии разработки БД. Одним из таких свойств является размер поля (столбца) таблицы. Это свойство задает максимальный размер данных, для хранения которых предназначено данное поле. Для простоты пока условимся, что в рассматриваемом примере все данные являются текстовыми (в т.ч. коды, вес, расстояние). Допустим, что установлены следующие размеры полей в первом варианте БД (слд 36 с ЭФ). Общий объем памяти требуемый для хранения 1 варианта БД – 63000 байт. При втором варианте (слд 36 с ЭФ) объем памяти – 25397 байт. С учетом логических связей между таблицами схема второго варианта БД будет иметь следующий вид (слд 37). Отношение, содержащее первичный ключ называется главным, отношение содержащее внешний ключ - подчиненным. Т.о., внешний ключ подчиненного отношения является его вторичным ключом, который в то же время является первичным для главного отношения. По этим ключевым полям устанавливается связь между отношениями. Одна подчиненная таблица может содержать несколько вторичных (внешних) ключей и быть связана с несколькими главными таблицами. Если одной строке в главном отношении можно поставить в соответствие несколько строк (более одной) в подчиненном отношении, между ними устанавливается связь ОДИН-ко-МНОГИМ. Если одной строке в одном отношении можно поставить в соответствие только одну строку в другом отношении, они являются отношениями одного уровня и между ними устанавливается связь ОДИН-к-ОДНОМУ.
Для хранения кодов и поля “Расстояние” - 3 символа; полей “Фамиля_И_О” и “Наименование” – 15; для хранения данных о марках и номерах госрегистрации автомобилей - по 6 символов. Для остальных полей размер не оговаривается, т.к. и в первом и во втором вариантах БД эта часть Таким образом, для регистрации одного рейса в первом варианте для одной записи потребуется выделить объем памяти для хранения информации занимает одинаковый объем. ПН Водитель Марка_АМ ГН РСТ 15 + 15 + 6 + 6 + 3 = 45 символов,
при регистрации 100 рейсов - 45 * 100 = 4500 символов. Во втором варианте, данные хранятся в разных таблицах описывающих однотипные объекты. Для регистрации данных о водителях, работающих в настоящее время, необходим объем памяти для хранения КОД_ВОД Фамиля_И_О (3 + 15) * 4 = 72 символов. Аналогично подсчитаем объем для остальных таблиц второго варианта БД. Таблица МАРКА_АТ - (3 + 6)*3 = 54. Таблица АТ (3 + 3 + 6) * 5 =60. Таблица ПН - (3 + 15 + 3) * 4 = 84. В таблице ПЕРЕВОЗКИ учитываются только те поля, которых нет в первом варианте: КОД_ПН КОД_ВОД КОД_АТ (3 + 3 + 3) * 100 = 900. Суммарный объем во втором варианте составит: 72 + 54 + 60 + 84 + 900 = 1170 символов. Таким образом, даже при незначительном количестве зарегистрированных рейсов, очевидно, что второй вариант БД гораздо экономичней. Кроме того, при повторном вводе значения большой длины (например, наименования пункта назначения или любого другого) вероятность ошибки (например, ввод лишнего пробела) существенно выше, чем при повторной ссылке на короткий код.
ТИПЫ ДАННЫХ в ACCESS – (самостоятельно!!!!! )
4. РАБОТА С ДАННЫМИ ПРИ ПОМОЩИ ЗАПРОСОВ Характерной чертой современных БД (даже на локальном уровне) является очень большой объем хранимой информации. Анализ таких объемов информации (поиск, сопоставление, отбор и т.д.) невозможен без специальных средств - запросов. Запрос - это операция над отношениями, результатом которой также является отношение (таблица). Одним из преимуществ использования запросов является то, что они позволяют достаточно просто найти и выбрать нужные данные из нескольких (или одной) связанных таблиц с большим количеством записей. Однако, в отличие от реальной таблицы, результирующая таблица запроса существует только во время выполнения запроса, но сформированный таким образом набор записей не хранится в БД.
4.1. Средства описания запросов Для описания запросов разработаны специальные языки (слд 38) (QUEL, SQL, QBE и др.) применяемые в различных системах управления базами данных. Структурированный язык запросов (слд 38 с ЭФ) SQL принят в качестве стандарта и поддерживается большинством современных СУБД. В некоторых СУБД предлагается несколько средств описания запросов. В частности, процедура создания запроса м.б. реализована средствами языка запросов по образцу (слд 38 с ЭФ) (QBE), обладающим двумерным синтаксисом. В этом случае запрос формулируется путем заполнения табличной формы (бланка). Каждый столбец бланка QBE относится к одному полю запроса. Формат результирующей таблицы запроса определяется набором полей включенных в запрос. Рассмотрим пример заполнения табличной формы запроса QBE реализованный в MS ACCESS (слд 39). Перед началом заполнения бланка запроса необходимо четко сформулировать цель запроса, что бы представить, какие данные из БД пользователь хочет получить в результате. Например, оформить сводную таблицу о выполненных рейсах, в которой необходимо указать фамилию водителя, пункт назначения, вес груза и марку автотранспортного средства. Исходя из анализа схемы БД (рис. 3), для получения этой информации необходимо использовать данные из таблиц: " ПУНКТ_НАЗНАЧЕНИЯ" - атрибут " Наименование"; " ВОДИТЕЛИ" - атрибут " Фамилия_И_О"; " МАРКА_АТ" - атрибут " Марка" и " ПЕРЕВОЗКИ" - атрибут " Вес". В соответствии с целью в запрос включены указанные поля (слд 40) (рис. 4).
Рис. 4. Бланк запроса QBE (в СУБД MS Access) При текущем состоянии БД (рис. 3) в результате выполнения запроса на выборку " Рейсы" будет сформирован набор записей (слд 41) (рис.5)
Рис. 5. Результат выполнения запроса на выборку " Рейсы"
4.2 Условия отбора данных Очень часто при анализе данных выполняются различного рода выборки – отбор строк удовлетворяющих некоторым условиям. Условия отбора данных в запросе могут отсутствовать или м.б. заданы для одного или нескольких полей. Если условий больше одного, то условия отбора записанные в одной строке объединяются логическим оператором AND (И), условия записанные в разных строках - оператором OR (ИЛИ). Если строки условий отбора табличной формы QBE пусты, то такому " условию" удовлетворяют любые хранимые значения атрибутов включенных в запрос. Условия отбора, по какому либо атрибуту включенному в запрос, м.б. сформулированы: в виде равенства или неравенства для числовых переменных; в виде тождества или подобия для текстовых переменных. Рассмотрим пример с объявлением простого условия отбора. Цель запроса - выбрать все рейсы выполненные водителем Ивановым. В этом случае в поле " Фамилия_И_О" запроса " Рейсы" в разделе " Условия отбора" необходимо указать значение по которому будут отбираться записи в БД. Бланк запроса и результат его выполнения приведены на рис. 6 (слд 42).
а) бланк запроса; (слд 43)
б) результат выполнения запроса " Рейсы Иванова С.П."
Рис. 6. Бланк и таблица запроса " Рейсы Иванова С.П."
Выше уже отмечалось, что при использовании условий отбора более чем по одному полю, условия записанные в одной строке объединяются логическим оператором AND (И), записанные в разных строках - оператором OR (ИЛИ). Рассмотрим пример. Цель запроса - составить справку об объеме работ (вес груза и расстояние) выполненных водителем Ивановым С.П. на автомашинах марки КАМАЗ. В соответствии с поставленной целью из БД должны отбираться только те записи, в которых И значение атрибута " Фамилия_И_О" равно " Иванов С.П." И значение атрибута " Марка" равно " КАМАЗ". Следовательно, эти условия в бланке QBE должны быть записаны в одной строке (вариант 1, рис. 7). (слд 44, 45) Если эти же критерии записать в разных строках (вариант 2, рис. 8), то результат выполнения запроса не будет соответствовать поставленной цели, т.к. в этом случае будут отбираться записи, для которых ИЛИ значение атрибута " Фамилия_И_О" равно " Иванов С.П.", ИЛИ значение атрибута " Марка" равно " КАМАЗ". (слд 46, 47)
а) бланк запроса;
б) результат выполнения запроса Рис. 7. Бланк и таблица запроса (первый вариант).
а) бланк запроса;
б) результат выполнения запроса
Рис. 8. Бланк и таблица запроса (второй вариант)
Для текстовых данных критерии выбора могут формулироваться в виде маски. Например (слд 48), для отбора данных по начальным символам фамилий водителей в разделе условий отбора в поле " Фамилия_И_О" можно указать маску " Ив*" (MS Access преобразует это условие к виду Like " Ив*”). По такому условию отбираются записи в которых значения атрибута " Фамилия_И_О" начинаются с символов " Ив", остальные символы могут быть любыми или могут отсутствовать. Для числовых переменных допустимы условия на равенство или неравенство. Например, в рассмотренных выше запросах в разделе условий в поле " Расстояние" м.б. записано: 254 - будут обираться только те строки, в которых значение атрибута " Расстояние" равно 254; < 250 - этому условию удовлетворяют все строки в которых значение атрибута " Расстояние" меньше 250; Between 100 And 250 - будут обираться те строки в которых значение атрибута " Расстояние" находится в интервале от 100 до 250. Если выполнять отбор данных по полю " Дата" за некоторый период времени, условие можно сформулировать следующим образом Between #16.01.99# And #31.01.99#. 4.3. Вычисляемые поля в запросах Для получения расчетных данных, в запросах используются вычисляемые поля. Рассмотрим пример (рис. 9). (слд 49 с ЭФ). Цель запроса - определить выполненные Ивановым С. П. в период с 16.01.99 по 31.01.99 объемы работ в тоннокилометрах (ткм). Т.к., в таком виде данные в БД не хранятся, для их расчета в запросе формируется вычисляемое поле " Объем". Расчетное выражение отделяется от имени поля двоеточием. Операндами расчетного выражения м.б. константы и ссылки на имена полей таблиц БД или имена других вычисляемых полей. Ссылки указываются в квадратных скобках. Ссылки на имена полей должны точно соответствовать оригиналам (вплоть до регистра). Даже пробел, вставленный между открывающей скобкой и первым символом имени поля, приведет к неправильной ссылке. В выражениях расчетных полей могут использоваться все арифметические операции и круглые скобки, в т.ч. вложенные. Следует иметь в виду, что к вычисляемым полям применимы условия выбора.
а) бланк запроса;
б) результат выполнения запроса " Объем работ "
Рис. 9. Бланк и таблица запроса " Объем работ"
4.4. Итоговые запросы Итоговые запросы выполняются тогда, когда пользователя интересует не каждая отдельная запись в БД, а итоговые значения по группам данных. В этом случае в бланке запроса указывается поле (или поля) по которым выбираемые данные должны объединяться в группы и поля, в которых необходимо выполнить групповую функцию. Признак группировки и групповая функция указываются в строке " Групповые операции" бланка QBE (который переводится в режим итогового запроса щелчком по кнопке S ). Групповые функции могут применяться и для вычисляемых полей. Рассмотрим пример (рис. 10) (слд 50 с ЭФ). Цель запроса - подсчитать объемы работ выполненные каждым водителем отдельно за весь период ведения БД (рис. 3). Т.е., необходимо выбрать все рейсы каждого водителя, найти объемы работы в тоннокилометрах по каждому рейсу и просуммировать их. Поэтому в бланке запроса (рис. 10) для поля " Фамилия_И_О" указан признак " Группировка", а для поля " Итого_объем" указана групповая функция суммирования данных по сгруппированным записям. В результате (в таблице запроса) для каждого водителя выведена только одна строка с суммарным объемом выполненных работ.
а) бланк запроса
б) результат выполнения итогового запроса Рис. 10. Бланк и таблица запроса " Итого объем "
Если признак группировки указан более чем в одном поле итогового запроса, то приоритет группировок устанавливается слева направо. В результате группировки по первому (левому) признаку образуются группы записей (например, все рейсы Иванова, все рейсы Ермакова и т.д.), затем внутри образованных групп выполняется группировка по второму признаку (например, для группы записей относящихся к одному водителю м.б. сформированы подгруппы по маркам автомобилей на которых он выполнял рейсы) и т.д. Т.о., можно выполнять многоступенчатую группировку записей с подсчетом групповых функций. В MS Access в список групповых функций входят следующие: (слд 51) Avg - вычисляет среднее арифметическое всех значений заданного поля в каждой группе записей (только для типов данных - числовой, денежный); Count - возвращает число записей, в которых значение заданного поля отличны от Null; First - возвращает первое значение этого поля в группе; Last - возвращает последнее значение этого поля в группе; Max - возвращает наибольшее значение, найденное в этом поле внутри каждой группы; Min - возвращает наименьшее значение, найденное в этом поле внутри каждой группы; StDev - подсчитывает статистическое стандартное отклонение для всех значений данного поля в каждой группе; Sum - вычисляет сумму всех значений заданного поля в каждой группе (только для типов данных - числовой, денежный); Var - подсчитывает статистическую дисперсию для всех значе- ний данного поля в каждой группе.
4.5. Влияние характера межобъектных связей на результат запроса При проектировании запросов необходимо учитывать, что включение в схему запроса атрибутов из таблиц (или других запросов) не связанных между собой приводит к формированию набора строк образованных комбинированием каждой строки одной таблицы с каждой строкой другой таблицы. Пример. Цель запроса: вывести список содержащий сведения о сформировавшемся закреплении автотранспорта за водителями. Рассмотрим два варианта (слд 52). В первом в схему запроса включаются атрибуты только из двух таблиц, не имеющих прямой связи, “ВОДИТЕЛИ” и “АВТОТРАНСПОРТ” (рис. 3). Во втором к указанным источникам данных добавляется атрибут из таблицы “ПЕРЕВОЗКИ”, и все три источника данных становятся связанными. В обоих вариантах используется итоговая форма запроса. Бланки запросов и результаты их выполнения приведены на рис. 11 (слд 53). Как видно из результатов выполнения запросов, в первом случае сформирован список из 20 строк, содержащий все возможные комбинации пар “водитель-автомашина”. Реально же, после выполнения семи рейсов
а) бланк запроса (вариант 1)
б) бланк запроса (вариант 2)
в) таблица запроса (вариант 1) было зарегистрировано только шесть сочетаний “водитель-автомашина” (вариант 2). При использовании несвязанных объектов в качестве источников данных для запросов, в СУБД MS Access между объектами может устанавливаться временная связь. Эта связь объявляется в окне запроса с одним из трех типов объединения двух таблиц (слд 54). Первый – объединение только тех записей, в которых связанные поля обеих таблиц совпадают. Второй – объединение ВСЕХ записей из первой таблицы и только тех записей из второй, в которых связан ные поля совпадают. Третий – объединение ВСЕХ записей из второй таблицы и только тех записей из первой, в которых связанные поля совпадают. Не менее важным является случай использования в качестве источников данных объектов, между которыми через промежуточные таблицы возникают связи типа “многие-ко-многим” (рис. 12а). (слд 55)
а) схема связей
б) таблица запроса Рис. 12. Пример возникновения связи типа “многие-ко-многим” через промежуточную таблицу и результат запроса на выборку
“Особенность” таких объектов-источников данных заключается в том, что данные в таблицах “ЦЕНЫ” и “ПРОДАЖИ” связаны, но простое формирование записей в запросе на основе этих таблиц создаст как “правильные”, так и “неправильные” сочетания (рис. 12б). (слд 56) При использовании таких объектов в качестве источников приходится создавать запросы либо с очень сложными условиями отбора, либо последовательность запросов с условиями отбора для того, чтобы отфильтровать и согласовать данные между собой. Так в приведенном примере (рис. 12), необходимо отобрать только те записи, в которых дата установления цены на товар является ближайшей слева от даты продажи (в табл. 12б записи выделены серым фоном).
4.6. Запрос с параметром В некоторых случаях, при проектировании запросов целесообразно условия отбора (или компоненты расчетных выражений вычисляемых полей) задавать в общем виде. Применение параметров позволяет создать гибкую систему запросов, обеспечивающую обработку хранимых данных при разных условиях, формируемых пользователем. Например (слд 57), при отборе записей по атрибуту “Дата”, принадлежащих некоторому периоду времени условие отбора может иметь вид “Between [Дата_Н] And [Дата_К]”, где параметры “Дата_Н” и “Дата_К” не принадлежат схеме ни одного из объектов БД. В этом случае, значения этих параметров будут вводиться пользователем в специальном окне при каждом выполнении данного запроса. Это позволяет один и тот же запрос использовать для формирования наборов записей принадлежащих разным периодам. Включение параметров в расчетные выражения вычисляемых полей позволяет получать расчетные значения с использованием, например, различных значений некоторого коэффициента. Значения коэффициента задаются пользователем при выполнении запроса. Значения параметров будут запрашиваться и в том случае, когда запрос с параметром является промежуточным в системе запросов и выполняется автоматически в скрытом режиме.
Лекция. Жизненный цикл ЭИС. Проектирование, эксплуатация, модификация. Роль и место специалиста экономического профиля на стадиях создания и эксплуатации информационных систем. Обследование предметной области. Обоснование и выбор состава автоматизируемых задач. Постановка задач. Жизненный цикл ЭИС. Понятие, содержание и методика выполнения проектов. Проектирование, эксплуатация, модификация. Роль и место специалиста экономического профиля экономического профиля на стадиях создания и эксплуатации информационных систем. Обследование предметной области. Обоснование и выбор состава автоматизируемых задач.
Понятие, содержание и методика проектирования ЭИС (слд 58) Популярное:
|
Последнее изменение этой страницы: 2016-05-28; Просмотров: 559; Нарушение авторского права страницы
