|
18 Октября 2010
Posted in
Анализ данных
Петрович протрезвел первым и долго ругался. Мы думали, что Алекс выпил все пиво.
Оказалось хуже: мы забыли про фундамент. В проекте он, конечно, описан
, но ведь документацию читают только ламеры.
Хроники лаборатории. На стройке.
Данная лекция носит исключительно теоретический характер, однако необходима для понимания основных принципов работы с базами данных.
Основные понятия и определения.
Прежде чем перейти к заявленной теме, вспомним определение базы данных.
База данных (БД) – совокупность данных, организованная по определенным правилам, определяющим общие принципы описания, хранения и обработки данных.
Для обработки БД используются системы управления базами данных (СУБД).
Система управления базами данных (СУБД) – программное обеспечение, обеспечивающее управление доступом к БД.
В само определение БД не вносится каких-либо требований по организации и способу хранения. В ходе разработки теоретических основ, предлагались различные способы организации БД, однако, на текущий момент, основная часть рынка занята реляционными базами данных.
Реляционная база данных – база данных, в которой данные представимы в виде таблиц.
На самом деле, данное определение является вульгаризацией точного определения, понимание которого, к сожалению, требует знания основ математического анализа, а точнее, элементов теории множеств.
Для обработки реляционной БД используются реляционные системы управления базами данных (СУБД).
Реляционная система управления базой
данных - система управления реляционной базой
данных, содержащая:
- командный язык;
- язык программирования
с ориентацией на обработку таблиц;
- интерпретирующую и/или
компилирующую систему; и
- пользовательскую оболочку.
Каждая таблица реляционной БД состоит из столбцов (полей) и строк (записей). Соответственно, таблица задана, если задано её имя, набор полей, причем каждому из полей присвоен тип. Типы полей зависят от реализации БД, но, построены они на следующих базовых типах:
Числовой целочисленный тип
Числовой вещественный тип
Строковый тип
Бинарный тип (так называемые BLOB-поля, предназначенные для хранения произвольных данных, в основном, используются для хранения графических материалов)
При этом, таблицы базы данных имеют следующие свойства:
Каждая таблица БД имеет свое уникальное имя
Недопустимы одинаковые имена полей в пределах одной таблицы
Расположение строк в таблице имеет неупорядоченный характер
Таблица может иметь ключевое поле (ключ). В большинстве случаев слово «может» должно заменяться на «должна», так как приложения, опирающиеся на БД могут работать некорректно с таблицами, не имеющими ключа.
Ключ– это столбец (столбцы), в котором (которых) находятся значения однозначно идентифицирующие запись.
- Вот пример созданной таким образом таблицы:
- В данном случае роль ключа выполняет поле «Code» - счетчик, специально созданный для этой цели.
- При этом стоит сразу сказать что поля, по которым предполагается осуществлять поиск, либо же, поля по которым будут связываться таблицы, должны быть индексированы.
Нормальные формы. Уменьшение размера БД.
- Может показаться, что информация в таблице носит избыточный
характер, с точки зрения разделения отдельных характеристик по
полям. К примеру, зачем разбивать адрес на отдельные поля –
город, улица, дом и т.п.? Это является следствием требований,
позволяющих максимально эффективно использовать возможности баз
данных.
- Для того, чтобы работа с базами данных была эффективна, таблицы должны соответствовать требованиям нормальных форм. Всего насчитывается пять нормальных форм, однако, на практике достаточно привести таблицы БД к третьей нормальной форме, чтобы она была максимально эффективна.
- Итак, нормальные формы.
- Таблица находится в первой нормальной форме, если каждый её атрибут (поле) атомарен, то есть может содержать только одно значение.
- Это требование исключает напрочь поля типа «Адрес», «ФИО» и подобные им.
- Таблица находится во второй нормальной форме, если она отвечает критерию первой нормальной формы, а значения ее любого неключевого поля зависят от значений всех ключевых полей.
- То есть, нельзя смешивать в одной таблице описания различных объектов. Так, не стоит в таблицу, которая сделана для описания характеристик компании вносить фамилию директора.
- Таблица находится в третьей нормальной форме, если значение каждого неключевого поля таблицы представляет собой факт, не зависящий от значений никаких других неключевых полей.
- То есть, не допускается смешивание и по неключевым полям.
- Исходя из этих правил становится понятным, почему база данных обычно состоит из большого числа таблиц.
- Если предыдущее описание поставило вас в тупик, представьте, что речь идет об описании какого-то составного, сложного процесса (объекта) и для описания каждой его составляющей создается отдельная таблица. Естественно, как и везде, все хорошо в меру и излишняя нормализация может не только помочь, но и навредить.
- С точки зрения размера БД – следует следить за размерами полей. Так, если есть выбор между наличием в таблице поля длиной 50 символов или его «заменителя» - кода, длиной в 4 символа, а то и численного значения, стоит выбрать второй путь. Именно поэтому, в числе других причин, вместо полного именования региона в головной таблице нашей БД используется код региона, а коды регионов и их названия перемещены в отдельную таблицу.
- Для того, чтобы работа с базами данных была эффективна, таблицы должны соответствовать требованиям нормальных форм. Всего насчитывается пять нормальных форм, однако, на практике достаточно привести таблицы БД к третьей нормальной форме, чтобы она была максимально эффективна.
Связи между таблицами.
Ключ таблицы важен еще и потому что он обеспечивает связь данной таблицы с другими. Так, если создается БД, описывающая структуру предприятий, созданный код предприятия будет использоваться для связи с другими таблицами.
Связи может быть нескольких типов: «один к одному» (предприятие может находиться только в одном регионе), «один ко многим» (у одного предприятия может быть несколько руководящих лиц), «многие ко многим».
Как пример, рассмотрим структуру учебной БД.
Задание 1. Проанализировать структуру каждой из таблиц с точки зрения нормализации и предложить пути решения проблем. Пути решения изложить в графическом виде с использованием графического редактора Draw, входящего в состав OpenOffice.
Все связи в данной структуре имеют вид «один ко многим». После нормализации некоторых из них в схеме появятся связи «один к одному».
Язык SQL.
Для обращения к БД и получения выборок в пригодном для анализа виде в реляцоннных БД служит язык SQL(Structured Query Language — язык структурированных запросов). Может возникнуть вопрос о целесообразности рассмотрения его здесь и сейчас, исходя из наличия изысков в виде конструкторов запросов. Ответ простой: конструктор запросов показывает далеко не все операторы запроса, вернее — смотреть надо в некоторых удаленных от «первого взгляда» местах (можно, конечно, свойства запроса посмотреть, да кто ж их смотрит :( ), а проблема, которую нужно решить, может крыться именно в той его части, которая скрыта от глаз. К тому же, сложность вопроса надумана, так как основу любого языка программирования составляет упрощенный английский язык (опа, а SQL — вообще-то ЯП не считается). В качестве совета – просто читайте выражения и подбирайте их аналоги на русском.
В общих вариантах запроса – квадратные скобки означают, что данная конструкция может присутствовать, а может и отсутствовать, вертикальная черта – варианты, а конструкция [,...n] повторение какое-то количество раз.
Из всего многообразия операторов мы затронем те, которые позволяют отбирать данные, удалять данные, добавлять данные из одной таблицы в другую и обновлять данные.
Отбор данных
Для отбора данных используются запросы типа:
1 SELECT [ALL | DISTINCT ] {*|[имя_столбца
2 [AS новое_имя]]} [,...n]
3 FROM имя_таблицы [[AS] псевдоним] [,...n]
4 [WHERE ]
5 [GROUP BY имя_столбца [,...n]]
6 [HAVING ]
7 [ORDER BY имя_столбца [,...n]]
-
В строках 1-3 определяются отбираемые поля и таблица из которой
отбираются данные. Если указывается слово Distinct, то будут
отобраны только неповторяющиеся записи. Если при отборе вместо имен
полей указывается «*», то будут отобраны все поля. После
слова Where указывается условие отбора. Условие отбора формируется
на основе правил формальной логики. Из всех операторов остановимся
на Like. Этот оператор применяется для отбора записей по частичному
совпадению. Так, если задается выражение вида:
(address2.Line_of_bus) Like
"*железо*", то будут отобраны только те
записи, в которых в поле Line_of_bus присутствует слово «железо».
Если условие отбора изменить следующим образом:
(address2.Line_of_bus) Like
"железо*", то будут отобраны только те
записи, в которых слово «железо» находится в начале
строки, аналогично, отсутствие символа «*» в конце
указывает на то, что данное слово находится в конце. Для отбора
пустых полей используется конструкция Is Null, заполненных –
Is Not Null. Если присутствует раздел 5, значит, по данным полям
будет организована группировка, то есть, записи, одинаковые по
набору полей, указанному в разделе, будут объединены. Раздел 6
появляется только в случае наличия раздела 5. Критерии выбора
строятся так же, как и для раздела 4. Раздел 7 служит для
упорядочивания значений по убыванию (DESC) или по возрастанию (ASC).
К примеру, конструкция ORDER
BY address2.oborot_2004 DESC упорядочит
выборку по убыванию по полю Oborot_2004.
- Запрос может выглядеть так:
- Запрос может выглядеть так:
1 SELECT address2.comp_okpo, address2.name_rus, address2.State, 2 address2.comp_soato, address2.contact_date, address2.Line_of_bus 3 FROM address2 4 WHERE (((address2.Line_of_bus) Like "*железо*" And (address2.Line_of_bus) Like 5 "*бетон*"));
-
Сразу после оператора SELECT в
строке 1 идет список полей, отбираемых в ходе запроса, после
ключевого слова FROM
в строке 2 имя таблицы, используемой в запросе и, наконец, после
ключевого слова WHERE
в строках 4
и 5 –
условие отбора.
- В сущности, в виде конструктора Access запрос выглядит так:
- Более сложный вариант:
- В сущности, в виде конструктора Access запрос выглядит так:
1 SELECT TOP 10 address2.comp_okpo, address2.name_rus, SOATO_Main.NAIM, 2 address2.Line_of_bus, address2.oborot_2004 3 FROM address2 INNER JOIN SOATO_Main ON address2.comp_soato = SOATO_Main.KOD 4 WHERE (((address2.Line_of_bus) Like "*железо*" And (address2.Line_of_bus) Like 5 "*бетон*")) 6 ORDER BY address2.oborot_2004 DESC;
-
В строке 1, после оператора SELECT
стоит ключевое TOP 10,
что говорит о том, что будут отобраны только десять значений сверху,
определяющихся разделом ORDER
BY, который,
в данном случае, построен для упорядочивания записей по обороту 2004
года в порядке убывания (DESC). В строке 2 читаем, что отбор
производится из двух таблиц: address2 и SOATO_Main, объединенных по
полям comp_soato и KOD. В конструкторе Access этот запрос выглядит
так:
Обратите внимание, что в конструкторе нет никаких упоминаний о том, что отбирается только 10 значений. Гм... Упоминания-то есть, но они сокрыты в меню, а вот косяки из-за использования запросов сделанных кем-то и когда-то не редкость. Мораль: бди! То бишь — SQL — это наше все.
Задание 2. Разобраться с запросами:
SELECT address2.comp_okpo, address2.State, address2.comp_soato, address2.Line_of_bus, address2.contact_date FROM address2 WHERE (((address2.Line_of_bus) Like "*окон*" Or (address2.Line_of_bus) Like "*стеклопак*") AND ((([address2].[Line_of_bus]) Like "*волоко*" Or ([address2].[Line_of_bus]) Like "*оборудов*")=False)); SELECT products.Of_txt, address2.name_rus, address2.contact_date, address2.State FROM products RIGHT JOIN address2 ON products.Of_txt = address2.comp_okpo WHERE (((products.KOD_NOM_txt) Like "81*") AND ((products.year)=2003)) OR (((address2.name_rus) Like "*мяс*")) GROUP BY products.Of_txt, address2.name_rus, address2.contact_date, address2.State; SELECT address2.comp_okpo, First(address2.name_rus) AS [First-name_rus], First(address2.comp_soato) AS [First-comp_soato], First(address2.oborot_2004) AS [First-oborot_2004] FROM address2 INNER JOIN products ON address2.comp_okpo = products.Of_txt WHERE (((address2.comp_soato)=1140 Or (address2.comp_soato)=1141 Or (address2.comp_soato)=1145 Or (address2.comp_soato)=1146 Or (address2.comp_soato)=1136) AND ((products.KOD_NOM_txt) Like "11*" Or (products.KOD_NOM_txt) Like "12*" Or (products.KOD_NOM_txt) Like "13*" Or (products.KOD_NOM_txt) Like "14*" Or (products.KOD_NOM_txt) Like "15*" Or (products.KOD_NOM_txt) Like "16*" Or (products.KOD_NOM_txt) Like "8*" Or (products.KOD_NOM_txt) Like "95*" Or (products.KOD_NOM_txt) Like "6*" Or (products.KOD_NOM_txt) Like "0388*" Or (products.KOD_NOM_txt) Like "71*" Or (products.KOD_NOM_txt) Like "72*" Or (products.KOD_NOM_txt) Like "73*" Or (products.KOD_NOM_txt) Like "2*" Or (products.KOD_NOM_txt) Like "3*" Or (products.KOD_NOM_txt) Like "4*" Or (products.KOD_NOM_txt) Like "5*" Or (products.KOD_NOM_txt) Like "0*") AND ((products.year)=2004) AND ((products.value)>0) AND ((address2.oborot_2004)>75000)) GROUP BY address2.comp_okpo;
Вставка данных
Для вставки данных в таблицу используются запросы типа:
INSERT INTO
[(имя_столбца [,...n])]
{VALUES (значение[,...n])|
}
-
Запрос может выглядеть так:
1 INSERT INTO tblKolhoz ( comp_okpo, name_rus, Line_of_bus, State ) 2 SELECT address2.comp_okpo, address2.name_rus, address2.Line_of_bus, 3 address2.State 4 FROM address2 INNER JOIN SICTable ON address2.Код = SICTable.Код 5 WHERE (((SICTable.comp_sic) Like "01*")) 6 GROUP BY address2.comp_okpo, address2.name_rus, address2.Line_of_bus, 7 address2.State;
- Из нового в этом запросе – строки 1, 6, 7. В строке 1 указываются название таблицы и поля в которые осуществляется вставка данных. Значения, которые будут вставляться – указаны в строке 2 и 3, при этом порядок совпадает с порядком в строке 1. В строках 6 и 7 находится раздел группировки. То есть, если в результате выполнения запроса будут получены одинаковые строки – они будут объединены в одну.
Задание 3. Разобраться с запросом:
INSERT INTO [tblMyaso-2] ( Code, OKPO, CodeName, [Value], [Year] ) SELECT products.KOD_NOM_txt, products.Of_txt, nomen.name, Sum(products.value) AS Sum_VALUE, products.year FROM [tblMyaso-2] RIGHT JOIN (products INNER JOIN nomen ON products.kod_nom = nomen.kod_nom) ON [tblMyaso-2].OKPO = products.Of_txt WHERE (((products.KOD_NOM_txt)="817000" Or (products.KOD_NOM_txt)="817100" Or (products.KOD_NOM_txt)="817200" Or (products.KOD_NOM_txt)="817300" Or (products.KOD_NOM_txt)="817400" Or (products.KOD_NOM_txt)="817500" Or (products.KOD_NOM_txt)="817690" Or (products.KOD_NOM_txt)="818000" Or (products.KOD_NOM_txt)="818100" Or (products.KOD_NOM_txt)="818200" Or (products.KOD_NOM_txt)="818213" Or (products.KOD_NOM_txt)="818300" Or (products.KOD_NOM_txt)="818400" Or (products.KOD_NOM_txt)="818422" Or (products.KOD_NOM_txt)="818500" Or (products.KOD_NOM_txt)="818532" Or (products.KOD_NOM_txt)="818535" Or (products.KOD_NOM_txt)="818690" Or (products.KOD_NOM_txt)="818735" Or (products.KOD_NOM_txt)="818750" Or (products.KOD_NOM_txt)="818760" Or (products.KOD_NOM_txt)="818761" Or (products.KOD_NOM_txt)="819030" Or (products.KOD_NOM_txt)="819031" Or (products.KOD_NOM_txt)="819065" Or (products.KOD_NOM_txt)="819072" Or (products.KOD_NOM_txt)="819081" Or (products.KOD_NOM_txt)="819082" Or (products.KOD_NOM_txt)="819087" Or (products.KOD_NOM_txt)="819088" Or (products.KOD_NOM_txt)="819089" Or (products.KOD_NOM_txt)="819091" Or (products.KOD_NOM_txt)="819092") AND ((products.year)=2001 Or (products.year)=2002 Or (products.year)=2003) AND (([tblMyaso-2].OKPO) Is Null)) GROUP BY products.KOD_NOM_txt, products.Of_txt, nomen.name, products.year;
Удаление данных
- Запрос позволяет удалить данные из таблицы. При этом может быть
указано условие удаления.
DELETE
FROM [WHERE ]
-
Пример запроса:
DELETE select_vid_transport.KOD, select_vid_transport.Eng_naim FROM select_vid_transport WHERE (((select_vid_transport.KOD)="10"));
Обновление данных
- Запрос обновляет данные, удовлетворяющие заданному условию:
UPDATE имя_таблицы SET имя_столбца=
[,...n]
[WHERE ]
-
Пример запроса:
UPDATE tblSpb0405 INNER JOIN SICTable ON tblSpb0405.Код = SICTable.Код SET tblSpb0405.Sic = "5", tblSpb0405.SicDetail = "3" WHERE (((SICTable.priority)=1));
В продолжении темы про работу с базами данных будет рассказано про работу в MS Access
Перейти к продолжению Основы реляционных баз данных. Часть вторая.