Анатолий Мотев СанктПетербург бхвпетербург 2006 удк 681 06 ббк 32. 973. 26018. 2 М85
 Скачать 4.25 Mb.
|
|
Âòîðàÿ íîðìàëüíàÿ ôîðìà (2ÍÔ) Прочно связав таблицы, мы можем продолжить нормализацию. Реляционная таблица приведена ко второй нормальной форме (2НФ), если она приведена к первой нормальной форме и ее неключевые поля полностью зависят от первичного ключа. Таблица "Покупатель" (см. табл. 2.5) приведена ко второй нормальной фор- ме. Но этого недостаточно — в этой таблице есть дополнительные зависимо- сти. Например, если в таблице есть несколько покупателей с одним адресом (члены одной семьи), то при смене этого адреса потребуется изменить не- сколько записей. Òðåòüÿ íîðìàëüíàÿ ôîðìà (3ÍÔ) Таблица приведена к третьей нормальной форме (3НФ), если она приведена ко второй нормальной форме и ни одно неключевое поле не зависит от дру- гих неключевых полей. Чтобы перейти от второй нормальной формы к третьей, нужно выполнить следующие шаги: 1. Определить все поля (или группы полей), от которых зависят другие поля. 2. Создать новую таблицу для каждого такого поля (или группы полей) и пе- реместить группы зависящих от него полей в эту таблицу. Поле (или группа полей), от которого зависят все остальные перемещенные поля, станет при этом первичным ключом новой таблицы. Óðîê 2. Ôèçè÷åñêîå ïðîåêòèðîâàíèå òàáëèö, âèäû ñâÿçåé, íîðìàëèçàöèÿ 29 3. Удалить перемещенные поля из исходной таблицы, оставив лишь те из них, которые станут внешними ключами. Мы должны создать для адреса покупателя новую таблицу и переместить в нее поля из исходной таблицы (табл. 2.6 и 2.7). Òàáëèöà 2.6. Ñóùíîñòü "Ïîêóïàòåëü" â 3ÍÔ Покупатель Номер покупателя ФИО покупателя Номер адреса 1 Петров Иван Иванович 1 2 Сидоров Андрей Анатольевич 2 3 Ванин Алексей Сергеевич 3 4 Ванин Иван Алексеевич 3 Òàáëèöà 2.7. Ñóùíîñòü "Àäðåñ ïîêóïàòåëÿ" â 3ÍÔ Адрес покупателя Номер адреса Адрес Телефон 1 ул. Мира, д. 34, кв. 40 954-87-23 2 ул. Ленина, д. 123, кв. 23 941-85-25 3 ул. Московская, д. 12 654-78-22 Получившиеся таблицы связаны по полю "Номер адреса" (рис. 2.2). У нас есть два покупателя, живущих по одному адресу (например, отец и сын). Если их адрес изменится, то сразу у обоих. Итак, теперь мы знаем, как проектируются таблицы и устанавливаются связи между ними. Настало время для создания реальной БД. В качестве предметной области возьмем "библиотеку". Нам необходимо про- вести анализ данной предметной области и разработать БД, которую мы бу- дем использовать на протяжении всей книги. Для начала давайте подумаем, что нам необходимо хранить в нашей базе. Во-первых, мы должны хранить сведения о книге (табл. 2.8). ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 30 Рис. 2.2. Связь между таблицами "Покупатель" и "Адрес покупателя" Òàáëèöà 2.8. Ñóùíîñòü "Êíèãà" Книга Номер книги Название Год издания Номер автора Номер издательства Номер раздела Номер книги будет уникально определять совокупность всех остальных ее атрибутов. Например, в нашу библиотеку поступила партия книг: автор: Томас Уилтон; название: "HTML 4.0. Справочник программиста"; год издания: 2004; издательство: "Мир книг"; раздел: "Компьютерная литература". Допустим, партия включает 25 книг. Если бы номер присваивался каждому экземпляру книги, то нам пришлось бы вводить в таблицу "Книга" все эти данные двадцать пять раз. Поэтому номер книги будет определен непосред- ственно для партии этих книг. Но когда книга будет выдаваться на руки чита- телю, необходимо фиксировать номер конкретного экземпляра книги, а не номер партии. Поэтому необходимо хранить сведения о каждом экземпляре каждой книги (табл. 2.9). Связь между этими таблицами будет осуществляться по номеру книги. То есть, зная номер экземпляра, мы сможем определить все остальные атрибуты книги. Óðîê 2. Ôèçè÷åñêîå ïðîåêòèðîâàíèå òàáëèö, âèäû ñâÿçåé, íîðìàëèçàöèÿ 31 Òàáëèöà 2.9. Ñóùíîñòü "Ýêçåìïëÿð" Экземпляр Номер экземпляра Номер книги Поскольку наши писатели и поэты обычно не ограничиваются одним произ- ведением, нам пришлось бы вводить сведения об авторе в таблицу "Книга" столько раз, сколько его книг есть в библиотеке. Поэтому в таблице "Книга" в качестве сведений об авторе зафиксирован номер автора (это куда лучше, чем вводить множество раз "Пушкин Александр Сергеевич"), а для хранения све- дений об авторе мы создадим отдельную таблицу (табл. 2.10). Òàáëèöà 2.10. Ñóùíîñòü "Àâòîð" Автор Номер автора ФИО автора Поле "ФИО автора" будет включать в себя его фамилию, имя и отчество. С таблицей "Книга" связь будет осуществляться по полю "Номер автора". Кроме этого, нам нужно создать еще две таблицы, в которых будут храниться сведения об издательствах (табл. 2.11) и разделах (жанрах) (табл. 2.12). Òàáëèöà 2.11. Ñóùíîñòü "Èçäàòåëüñòâî" Издательство Номер издательства Название издательства Òàáëèöà 2.12. Ñóùíîñòü "Ðàçäåë" Раздел Номер раздела Название раздела Таблицы "Издательство" и "Раздел" будут связаны с таблицей "Книга" по по- лям "Номер издательства" и "Номер раздела" соответственно. ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 32 Итак, чтобы полностью описать книгу, нам потребовалось создать пять таб- лиц. Благодаря этому мы избежим избыточности данных. Но это еще не все. Нам нужно знать данные о читателях, посещающих библиотеку. Давайте соз- дадим таблицу "Читатель" (табл. 2.13). Òàáëèöà 2.13. Ñóùíîñòü "×èòàòåëü" Читатель Номер читателя ФИО читателя Номер адреса Также нам понадобится таблица с адресами читателей, чтобы избежать про- блем в том случае, если будет несколько читателей, живущих по одному ад- ресу (табл. 2.14). Òàáëèöà 2.14. Ñóùíîñòü "Àäðåñ ÷èòàòåëÿ" Адрес читателя Номер адреса Адрес Телефон И, наконец, мы должны знать, какие книги находятся на руках у читателей. Для этого создадим новую таблицу и назовем ее "Абонемент" (табл. 2.15). Òàáëèöà 2.15. Ñóùíîñòü "Àáîíåìåíò" Абонемент Номер записи Номер читателя Номер экземпляра Дата выдачи Дата возврата Итак, мы полностью описали нашу БД, осталось только проставить связи между всеми таблицами (рис. 2.3). Óðîê 2. Ôèçè÷åñêîå ïðîåêòèðîâàíèå òàáëèö, âèäû ñâÿçåé, íîðìàëèçàöèÿ 33 Рис. 2.3. Связи между сущностями в БД "Библиотека" ÓÐÎÊ 3 Òèïû äàííûõ è òèïû òàáëèö В таблицах, которые мы создали на предыдущем уроке, будут храниться раз- личные данные (числа, строки, даты и т. д.). Пришло время поговорить о ти- пах данных, используемых при создании таблиц. Для каждого поля таблицы необходимо описать тип данных, который оно будет содержать. Вы должны выбрать правильный (корректный) тип данных для каждого поля таблицы. Например, если вы введете числовое значение в текстовое поле, то не сможе- те в дальнейшем использовать это значение для математических вычислений. Типы данных, поддерживаемые СУБД MySQL, можно разделить на четыре группы: числа (Numbers); текст (Text); дата и время (Date and Time); списки (Defined group). В табл. 3.1 приведены все типы данных для каждой из четырех групп. Òàáëèöà 3.1. Òèïû äàííûõ â MySQL Группа типов Типы Числа TINYINT , SMALLINT, MEDIUMINT, INT, BIGINT FLOAT , DOUBLE NUMERIC , DECIMAL Текст CHAR VARCHAR TINYTEXT , MEDIUMTEXT, TEXT, LONGTEXT TINYBLOB , MEDIUMBLOB, BLOB, LONGBLOB Óðîê 3. Òèïû äàííûõ è òèïû òàáëèö 35 Òàáëèöà 3.1 (îêîí÷àíèå) Группа типов Типы Дата и время DATETIME TIMESTAMP DATE TIME YEAR Списки ENUM SET ×èñëà СУБД MySQL различает целые числа (например 5 и 46) и вещественные чис- ла (с дробной частью, например 123,5). Целые числа можно представить в десятичном или шестнадцатеричном формате. Также СУБД может работать с числами в экспоненциальной форме (например 1,34Е+14 или 2,53е–3). Чи- словые типы данных ограничиваются диапазоном представляемых величин и размером в байтах. Вас не должно пугать их разнообразие. Я постараюсь максимально подробно описать каждый из типов данных. Öåëûå ÷èñëà (òèïû TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT) Поля типов TINYINT , SMALLINT , MEDIUMINT , INT , BIGINT могут содержать поло- жительные или отрицательные целые числа, а также ноль (0). Таким образом, любой из этих типов может быть знаковым (signed) или беззнаковым (unsigned). Знаковое поле может содержать положительные и отрицательные числа, а также ноль. Для обозначения отрицательных значений числа предваряют- ся знаком "минус" ( – ). Беззнаковое поле может содержать только положительные числа и ноль. По умолчанию поле будет знаковым. Если вы хотите, чтобы поле содержало только положительные числа и ноль, необходимо добавить атрибут UNSIGNED после описания типа поля. field_name INT(length) UNSIGNED ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 36 Параметры: field_name — имя создаваемого поля, а length — размер поля в символах (количество знакомест). Если при объявлении типа параметр length не указан, будет использовано значение по умолчанию. Например, объявив field_name INT мы получим field_name int(11) Но, помните, что параметр length влияет лишь на количество отображаемых символов, но никоим образом не влияет на объем памяти, необходимый для хранения данных этого типа. Давайте рассмотрим целые типы более подробно. TINYINT(length) — целое число от 0 до 255 для беззнаковых и от –128 до 127 для знаковых. Таким образом, максимальный размер данных, вно- симых в поле такого типа, будет равен одному байту. Параметр length , как вы помните, ограничивает размер поля в символах. Если, например, для поля объявлен тип TINYINT(2) , то данное поле будет отображать значения от –9 до 99 для беззнаковых и от 0 до 99 для знаковых. SMALLINT(length) — целое число от 0 до 65 535 для знаковых и от –32 768 до 32 767 для беззнаковых. MEDIUMINT(length) — целое число от 0 до 16 777 215 для беззнаковых и от –8 338 608 до 8 338 607 для знаковых. INT(length) — целое число от 0 до 4 294 967 295 для беззнаковых и от –2 147 483 648 до 2 147 483 647 для знаковых. BIGINT(length) — целое число в пределах от 0 до 18 446 744 073 709 551 615 для беззнаковых и от –9 223 372 036 854 775 808 до 9 223 372 036 854 775 808 для знаковых. Под поле с таким типом будет выделено 8 байт. Это все целочисленные типы данных, поддерживаемые СУБД MySQL. Если в числовое поле записать величину, превышающую максимальное возможное значение для данного типа, то MySQL ограничит значение до соответствую- щей граничной точки допустимого интервала и сохранит результат вместо исходного значения. То есть если, например, в поле, тип которого TINYINT , записать число –300, то в результате в него будет занесено значение –128 (предельное для данного типа). Аналогично, если попытаться записать в та- кое поле число 300, то в итоге получим 127. Если поле TINYINT задано как беззнаковое, то при попытке записать значения –300 и 300 мы получим 0 и 255 соответственно. Óðîê 3. Òèïû äàííûõ è òèïû òàáëèö 37 ×èñëà ñ ïëàâàþùåé òî÷êîé (òèïû DOUBLE è FLOAT) Эти типы очень удобны для хранения всевозможных научных данных. Тип FLOAT(length, decimal) — число с плавающей точкой обычной (одинар- ной) точности. Значения допустимы в пределах от –3.402823466Е+38 до –1.175494351Е–38 и от 1.175494351Е–38 до 3.402823466Е+38. Аргумент length задает число выводимых на экран (для пользователя) знаков, а decimal — количество разрядов, следующих за десятичной точкой. Например, поле типа FLOAT(9,2) будет отображать значения как, например: 999999.99 Если указан атрибут UNSIGNED , то отрицательные значения недопустимы. Также следует отметить, что использование типа данных FLOAT может при- вести к неожиданным проблемам, поскольку все вычисления в MySQL вы- полняются с удвоенной точностью. Тип DOUBLE(length, decimal) — число с плавающей точкой удвоенной точности (8 байт). Допустимы значения от –1.7976931348623157E+308 до –2.2250738585072014E–308, 0 и от 2.2250738585072014E–308 до 1.7976931348623157E+308. Если указан атрибут UNSIGNED , то отрицательные значения недопустимы. Для типа DOUBLE есть синонимы — DOUBLE PRECISION и REAL Òèï NUMERIC (DECIMAL) Тип NUMERIC(length, decimal) или DECIMAL(length, decimal) — число с пла- вающей точкой, хранящееся в виде строки. Это может понадобиться для со- хранения точности представления этих величин в десятичном виде. Макси- мальный интервал значений типа DECIMAL тот же, что и для типа DOUBLE , но действительный интервал для конкретного поля DECIMAL может быть ограни- чен выбором значений атрибутов length и decimal Если указан атрибут UNSIGNED , отрицательные значения недопустимы. Если параметр decimal не указан, его значение по умолчанию равно 0. Если не ука- зан параметр length , его значение по умолчанию равно 10. Требования к памяти для числовых типов приведены в табл. 3.2. При выборе числового типа для поля подумайте о том, из какого диапазона оно может принимать значения. Обязательно выбирайте наименьший из при- емлемых типов. Если выбирать большие типы, можно получить таблицу до- вольно большого размера. Например, для хранения возраста человека опти- мальным будет использование типа TINYINT ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 38 Òàáëèöà 3.2. Òðåáîâàíèÿ ê ïàìÿòè äëÿ ÷èñëîâûõ òèïîâ Тип Число байт, необходимых для хранения значения TINYINT 1 SMALLINT 2 MEDIUMINT 3 INT 4 BIGINT 8 FLOAT 4 DOUBLE 8 DECIMAL(length, decimal) NUMERIC(length, decimal) length + 2 (при decimal > 0) length + 1 (при decimal = 0) decimal + 2 (при length < decimal) Òåêñò Текстовое поле может хранить любые символы. Чаще всего используются буквы, но символы пунктуации и числа также допустимы (например, в значе- нии адреса — 'ул. Мира, д. 35, кв. 40' ). Но если вы введете в текстовое поле число, то в дальнейшем его нельзя будет использовать в математических функциях. MySQL поддерживает четыре основных текстовых типа — CHAR , VARCHAR , TEXT и BLOB Òèï CHAR Тип CHAR(length) — строка фиксированной длины. Длина (параметр length ) может принимать любое значение от 1 до 255 (в MySQL версии 3.23 — от 0 до 255). Любой текст меньшей длины будет дополнен пробелами в конце строки. Все конечные пробелы, добавленные MySQL или пользователями, будут удалены при выводе значения. Любой текст большей длины будет об- резан до заданной. MySQL по умолчанию считает текст нечувствительным к регистру букв. Ес- ли требуется сделать текст чувствительным к регистру букв, то при описании поля нужно добавить атрибут BINARY : field_name CHAR(length) BINARY В этом случае значения в поле сортируются и сравниваются с учетом регист- ра в соответствии с порядком символов в ASCII-таблице на том компьютере, где работает сервер MySQL. Óðîê 3. Òèïû äàííûõ è òèïû òàáëèö 39 Òèï VARCHAR Тип VARCHAR(length) — строка переменной длины. Длина (параметр length ) может принимать значение от 1 до 255 символов. Значение типа VARCHAR за- нимает столько байт, сколько символов ввел пользователь, плюс один байт для записи длины (но не больше 255). Концевые пробелы при сохранении удаляются. Строка, хранимая в поле типа VARCHAR , также нечувствительна к регистру букв. Здесь, если требуется сделать строку чувствительной к реги- стру букв, тоже необходимо применять атрибут BINARY , как в случае с типом CHAR В принципе, типы данных CHAR и VARCHAR очень схожи между собой, разница заключается в способе хранения и извлечения (табл. 3.3). Òàáëèöà 3.3. Îòëè÷èÿ òèïîâ CHAR è VARCHAR Исходное значение CHAR(4) VARCHAR(4) Значение Требуется байт Значение Требуется байт '' ' ' 4 '' 1 'ab' 'ab ' 4 'ab' 3 'abcd' 'abcd' 4 'abcd' 5 'abcdef' 'abcd' 4 'abcd' 5 Тип CHAR предпочтительнее использовать в том случае, если хранимые зна- чения имеют одинаковую длину ( VARCHAR требует дополнительный байт для хранения длины) или длина отличается незначительно. В таблицах типа MyISAM (тип таблиц по умолчанию) эффективнее обрабатываются строки фик- сированной длины. Извлеченные из столбцов CHAR(4) и VARCHAR(4) величины в каждом случае будут одними и теми же, поскольку при извлечении концевые пробелы из поля типа CHAR удаляются. В некоторых случаях СУБД MySQL без уведомления изменяет тип поля при создании таблицы или изменении ее структуры. Поле типа VARCHAR меньше четырех символов длиной преобразуется в поле типа CHAR В случае если таблица содержит любые поля переменной длины ( VARCHAR , TEXT или BLOB ), то все поля типа CHAR больше трех символов длиной преоб- разуются в поля типа VARCHAR . Это в любом случае не повлияет на исполь- зование полей. В MySQL поле типа VARCHAR представляет собой просто ×àñòü I. Ïðîåêòèðîâàíèå áàçû äàííûõ 40 другой способ хранения данных. MySQL выполняет данное преобразова- ние, потому что оно позволяет сэкономить память и сделать табличные операции более быстрыми. Òèïû TEXT è BLOB Тип BLOB представляет собой двоичную строку переменной длины, чувстви- тельную к регистру букв. У данного типа есть четыре модификации ( TINYBLOB , BLOB , MEDIUMBLOB , LONGBLOB ), которые отличаются максимальной длиной хранимого значения. Тип TEXT — текстовая строка, нечувствительная к регистру букв. У данного типа тоже есть четыре модификации, соответствующие модификациям BLOB и имеющие ту же максимальную длину и требования к памяти. Требуемая память для текстовых типов указана в табл. 3.4. Òàáëèöà 3.4. Òðåáóåìàÿ ïàìÿòü äëÿ òåêñòîâûõ òèïîâ |