ИГА. Понятие базы данных
 Скачать 0.77 Mb.
|
Физическое проектированиеСтадия физического проектирования БД в общем случае включает: выбор способа организации БД; разработку спецификации внутренней схемы средствами модели данных ее внутреннего уровня; описание отображения концептуальной схемы во внутреннюю. Многие современные СУБД не предоставляют разработчику какого-либо выбора на этой стадии. В действительности к вопросам проектирования физической модели можно отнести выбор схемы размещения данных (например, разделение по файлам) и определение числа и типа индексов (индекс – таблица, используемая для определения адреса записи). Способ хранения БД определяется механизмами СУБД автоматически «по умолчанию» на основе спецификаций концептуальной схемы БД, и внутренняя схема в явном виде в таких системах не используется. Универсальное отношение Дальнейшая задача проектирования БД включает сокращение избыточности хранимых данных с целью экономии объема используемой памяти, уменьшение затрат на многократные операции обновления избыточных копий и устранение возможности возникновения противоречий из-за хранения в разных местах сведений об одном и том же объекте. Таким образом, рациональный проект БД можно создать, используя методологию нормализации отношений. Рассмотрим задачу проектирования БД на базе сводной таблицы (табл. 3), отражающей результаты сдачи сессии (шкала оценок: 0 – незачет; 1 – зачет; 2, 3, 4, 5 – экзаменационная оценка). Этот вариант таблицы «Сессия» не является отношением, т. к. большинство ее столбцов не атомарны. Атомарны лишь значения столбцов «ФИО студента» и «Семестр». Остальные столбцы таблицы – множественные. Для преобразования данных в отношение необходимо реконструировать таблицу. Такое преобразование приводит к возникновению большого объема избыточных данных, однако таблица 4 представляет собой уже корректное отношение – универсальное отношение проектируемой БД. В одно универсальное отношение включаются все представляющие интерес атрибуты, и оно может содержать все данные, которые предполагается размещать в БД. При проектировании БД универсальное отношение может использоваться в качестве отправной точки. При использовании универсального отношения возникают две проблемы: Избыточность данных, при которой значения столбцов таблицы многократно повторяются. Потенциальная противоречивость. Если при вводе данных, например, количества часов для дисциплины «Английский язык», была допущена ошибка, то для ее исправления необходимо найти все строки, содержащие сведения об этой дисциплине, и во всех этих строках произвести изменения. Более того, при заполнении такой таблицы могут быть использованы различные формы записи одного и того же значения, например, «Англ. язык» и «Английский язык», «Мат. анализ» и «Математический анализ». Решение этих проблем состоит в разделении данных и связей, т.е. в выделении в отдельные таблицы сведений о студентах, преподавателях, дисциплинах и результатах сдачи экзаменов (рис. 23). При этом в полученных таблицах должны отсутствовать повторяющиеся строки (кортежи). Язык SQL – функции запросов и основные возможностиПонятие языка SQL. Инструкции и имена. Типы данных История возникновения языка SQL восходит к началу 1970-х гг. Тогда появился первый исследовательский прототип реляционной СУБД, для которого разрабатывались и опробовались различные языки запросов. Один из языков получил название SEQUEL (Structured English Query Language - SQL). С тех пор и до настоящего времени язык претерпел массу изменений, он развивался, совершенствовался. Его роль в компьютерной индустрии, связанной с созданием и эксплуатацией баз данных, постоянно росла. Официальный стандарт языка SQL был опубликован в 1986 г., в 1992 г. он был значительно расширен. Итак, язык SQL – структурированный язык запросов – применяется для общения пользователя с реляционной базой данных и состоит из трех частей: DDL (Data Definition Language) – язык определения данных. Предназначен для создания БД (создания таблиц, индексов и т. д.) и редактирования ее схемы. DCL (Data Control Language) – язык управления данными. Содержит операторы для разграничения доступа пользователей к объектам БД. DML (Data Manipulation Language) – язык обработки данных. Содержит операторы для внесения изменений в содержимое таблиц БД. SQL предоставляет пользователю относительно простой и понятный механизм доступа к данным, не связанный с конструированием алгоритма и его описанием на языке программирования высокого уровня. Вместо указания на то, как необходимо действовать, пользователь при помощи операторов SQL объясняет СУБД, что ему нужно сделать. Далее СУБД сама анализирует текст запроса и определяет, как именно его выполнять. В архитектуре «клиент-сервер» язык SQL занимает очень важное место. Он используется как язык общения клиентского программного обеспечения с серверной СУБД, расположенной на удаленном компьютере. Так, клиент посылает серверу запрос на языке SQL, а сервер интерпретирует его, выполняет запрос и отсылает клиенту результат. Таким образом, язык SQL используется для реализации всех функциональных возможностей, необходимых для управления БД, в том числе для: выборки данных– SQL позволяет получить доступ к данным, исходя из заданных условий выборки; организации данных– SQL позволяет определять и изменять структуру представления данных, а также устанавливать отношения; обработки данных– SQL позволяет изменять содержимое БД: добавлять, удалять или обновлять уже имеющиеся в ней данные; управления доступом– SQL позволяет ограничивать возможности пользователя по чтению и изменению данных (защита данных от несанкционированного доступа) и координировать их совместное использование пользователями, работающими параллельно. SQL не относится к полноценным языкам программирования, а может быть определен как подъязык, предназначенный для управления БД. SQL на сегодняшний день – единственныйязык для работы с реляционными БД. Операторы SQL встраиваютсяв тот или иной базовый язык программирования, например PASCAL или С++, и дают возможность получать доступ к БД из прикладных программ. SQL представлен множеством инструкций, каждая из которых предписывает СУБД выполнить определенное действие: создать таблицу, извлечь данные, добавить в таблицу новые данные и т. д. Инструкция SQL начинается с команды– ключевого слова, описывающего действие, выполняемое инструкцией. Типичные команды: CREATE (создать), INSERT (добавить), SELECT (выбрать), DELETE (удалить). Следом за командой указывается одно или несколько предложений. Предложение описывает данные, с которыми должна работать инструкция, или уточняет действие, выполняемое инструкцией. Предложения в инструкции делятся на обязательные и необязательные. Каждое предложение начинается с ключевого слова, например, WHERE (где), FROM (откуда), INTO (куда). Многие предложения в качестве параметров содержат имена таблиц или столбцов; некоторые из них могут содержать дополнительные ключевые слова, константы и выражения. Каждый объект в БД обладает уникальным именем. Имена используются в инструкциях SQL и указывают, над каким объектом базы данных инструкция должна выполнить действие. Имена в SQL могут содержать от 1 до 128 символов, начинаются с буквы и не должны включать пробелов или специальных символов пунктуации. В инструкциях SQL могут использоваться как полные имена объектов, так и короткие имена. Полное имя таблицы (в отличие от короткого) содержит имя пользователя и короткое имя таблицы, разделенные точкой: < Имя_пользователя>. <Имя_таблицы> При этом уникальность именования таблицы сохраняется в случае, если в рамках одной БД разные пользователи создают таблицы с одинаковыми именами. Полное имя столбца состоит из полного (или короткого) имени таблицы, которой принадлежит столбец, и короткого имени столбца, разделенных точкой: <Имя_пользователя>.<Имя_таблицы>.< Имя_столбца> или <Имя_таблицы>.< Имя_столбца> В рамках одной таблицы не может быть определено двух столбцов с одинаковыми именами, но в разных таблицах это возможно. При этом в инструкциях SQL необходимо использовать полное именование столбцов. Современные СУБД позволяют обрабатывать данные разнообразных типов, среди которых наиболее распространенными можно назвать следующие. Целые числа (INT, SМАLLINТ). В столбцах, имеющих такой тип данных, хранятся, например, данные о количестве чего-либо, идентификаторы. Десятичные числа (NUMERIC, DECIMAL). В столбцах данного типа хранятся числа, имеющие дробную часть с фиксированным количеством знаков после запятой. Числа с плавающей точкой (REAL, FLОАТ). Числа с плавающей точкой представляют больший диапазон действительных значений, чем десятичные числа. Строки символов постоянной длины (CHAR). В столбцах, имеющих этот тип данных, хранятся, например, имена и фамилии, географические названия, адреса и т. п. Строки символов переменной длины (VARCHAR). Столбцы этого типа позволяют хранить символьные строки, длина которых изменяется в заданном диапазоне. Денежные величины (MONEY, SMALLMONEY). Наличие отдельного типа данных для хранения денежных величин позволяет правильно форматировать их и снабжать признаком валюты перед выводом на экран. Дата и время (DATEТIME, SMALLDATEТIME). Поддержка особого типа данных для значений дата/время широко распространена в различных СУБД. Как правило, с этим типом данных связаны особые операции и процедуры обработки. Булевы величины (BIТ). Столбцы такого типа данных позволяют хранить логические значения True (1) и False (0). Длинный текст (ТЕХТ). Многие СУБД поддерживают хранение в столбцах текстовых строк длиной до 32Кб или 64Кб символов, а в некоторых случаях и больше. Это позволяет хранить в БД целые документы. Неструктурированные потоки байтов (ВINARY, VARBINARY, IMAGE). Современные СУБД позволяют хранить и извлекать неструктурированные потоки байтов переменной длины. Такой тип данных, обычно, используется для хранения графических и видеоизображений, исполняемых файлов и других данных. Встроенные функции. Значения NULL Язык SQL содержит встроенные функции, реализующие некоторые распространенные алгоритмы. Основная особенность этих функций – возможность их использования при построении выражений. Встроенные функции, доступные при работе с SQL, делятся на группы: математические функции; строковые функции; функции для работы с величинами типа дата/время; функции конфигурирования; системные функции; функции системы безопасности; функции управления метаданными; статистические функции. Таблица 6 Часто используемые встроенные функции языка SQL (математические, строковые для работы с величинами типа дата/время)
При заполнении таблиц БД отдельные элементы в них могут отсутствовать. Например, при заполнении таблиц «Студенты» и/или «Кадровый_состав» может быть не задан для некоторых строк номер телефона, но строка должна быть введена в таблицу и должна участвовать в запросах на выдачу информации. SQL поддерживает обработку неопределенных (не заданных) данных с помощью использования так называемого отсутствующего значения (NULL). Это значение показывает, что в строке тот или иной элемент данных отсутствует. При этом NULL не является значением данных и в связи с этим не имеет определенного типа, NULL – признак, показывающий, что значение элемента данных не задано. Первичный ключ таблицы. Внешний ключ таблицы Отношение (таблица) содержит, как правило, один или несколько столбцов, значение или совокупность значений которых уникально идентифицируюткаждую строку в таблице. Такой столбец (столбцы) называется первичным ключом(Primary Кеу - РК) таблицы. Если в первичный ключ входит более одного столбца, то значения в пределах одного столбца могут дублироваться, но любая совокупность значений всех столбцов первичного ключа при этом должна быть уникальна. Например, в таблице «Дисциплины» столбец ID_Дисциплинаопределен как первичный ключ, а для таблицы «Сводная ведомость» задан составной первичный ключ – в него входят значения столбцов ID_Студент и ID_Дисциплина. Таблица может иметь только один первичный ключ, причем никакой столбец, входящий в первичный ключ, не может хранить значение NULL. Еще одно назначение первичного ключа - обеспечение ccылочной целостности данных нескольких таблиц. Это может быть реализовано только при наличии соответствующих внешних ключей (Foreign Кey - FK) в других (дочерних) таблицах. Если по столбцу строится первичный ключ, то столбцу должен быть приписан атрибут PRIMARY КЕY (ограничение целостности на уровне столбца). Например, описание столбцаID_План для таблицы «Учебный_план» может выглядеть так: ID_Дисциплина INTEGER NOT NULL PRIМARY КЕY Первичный ключ может быть также построен с помощью отдельного предложения PRIMARY КЕY (ограничение целостности на уровне таблицы) – путем включения имени (имен) ключевого столбца (столбцов) в качестве параметров. Например, первичный ключ для таблицы «Сводная_ведомость» может быть задан следующим образом: PRIМARY КЕY (ID_Дисциплина, ID_Студент) Внешний ключ строится в дочерней (зависимой) таблице для соединения родительской (главной) и дочерних таблиц БД. Ограничение целостности предназначено для организации ссылочной целостности данных. Внешний ключ связывается с потенциальным первичным ключом в другой таблице. Внешний ключ при этом может ссылаться либо на столбец (столбцы) с ограничением целостности PRIMARY КЕY, либо на столбец (столбцы) с ограничением целостности UNIQUE. Таблицу, в которой определен внешний ключ, называют зависимой, а таблицу с первичным ключом –главной. Ссылочная целостность данных двух таблиц обеспечивается следующим образом: в зависимую таблицу нельзя вставить строку, если внешний ключ не имеет соответствующего значения в главной таблице, а из главной таблицы нельзя удалить строку, если значение первичного ключа используется в зависимой таблице. Например, если строка наименования дисциплины удалена из таблицы «Дисциплины», а идентификатор этой дисциплины (ID_Дисциплина)используется в таблице «Учебный_план», то относительная целостность между этими двумя таблицами будет нарушена – строки таблицы «Учебный_план» с удаленным идентификатором останутся «осиротевшими». Ограничение FOREIGN КЕY предотвращает возникновение подобных ситуаций – удаление строки первичного ключа не состоится. Столбцы внешнего ключа (в отличие от столбцов первичного ключа) могут содержать значения типа NULL, при этом проверка на ограничение FOREIGN КЕY будет пропускаться. Задать внешний ключ можно как при создании, так и при изменении таблиц. Синтаксис определения внешнего ключа следующий: FOREIGN КЕY (<список столбцов внешнего ключа>) REFERENCES <имя родительской таблицы> [[<список столбцов родительской таблицы>] [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] Список столбцов внешнего ключаопределяет столбцы дочерней таблицы, по которым строится внешний ключ. Имя родительской таблицыопределяет таблицу, в которой описан первичный ключ (или столбец с атрибутом UNIQUE). На этот ключ (столбец) должен ссылаться внешний ключ дочерней таблицы для обеспечения ссылочной целостности. Список столбцов родительской таблицы, определяющий ссылочную целостность, необязателен при ссылке на первичный ключ родительской таблицы. При ссылке в родительской таблице на столбец с атрибутом UNIQUE этот список лучше привести. Параметры ON DELETE и ON UPDAТЕ задают способы изменения подчиненных записей дочерней таблицы при удалении (ON DELETE) или изменении (ON UPDAТЕ) поля связи в записи родительской таблицы. Перечислим эти способы: NO ACТION – запрещает удаление/изменение родительской записи при наличии подчиненных записей в дочерней таблице; CASCADE – при удалении записи родительской таблицы (используется совместно с ONDELETE) происходит удаление всех подчиненных записей в дочерней таблице; при изменении поля связи в записи родительской таблицы (используется совместно с ON UPDATE) происходит изменение на то же значение поля внешнего ключа у всех подчиненных записей в дочерней таблице; SET DEFAULT– в поле внешнего ключа записей дочерней таблицы заносится значение этого поля по умолчанию, указанное при определении поля (параметр DEFAULT); SET NULL – в поле внешнего ключа записей дочерней таблицы заносится значение NULL. Установим связь между таблицами «Студенты», «Учебный_план» и «Сводная_ведомость»: ALTER TABLE Сводная_ведомость АDD FOREIGN КЕY (ID_План) REFERENCES Учебный_план ALTER TABLE Сводная_ведомость АDD FOREIGN КЕY (ID_Студент) REFERENCES Студенты Первичный ключ может быть определен для столбца с одним именем, а столбец, на который наложено ограничение FOREIGN КЕY, может иметь совершенно другое имя. Однако лучше давать таким столбцам идентичные названия, чтобы показать связь между ними. Определение уникального столбца. Определение проверочных ограничений. Определение значения по умолчанию. Ограничение целостности UNIQUE предназначено для того, чтобы обеспечить уникальность значений в столбце (или столбцах). Если столбцу приписан атрибут UNIQUE, то это означает, что в столбце не могут содержаться два одинаковых значения. Для ограничения целостности PRIMARY КЕYавтоматически гарантируется уникальность значений. Однако в каждой таблице может быть определен всего один первичный ключ. Если же необходимо дополнительно обеспечить уникальность значений еще в одном или более столбцах помимо первичного ключа, то нужно использовать ограничение целостности UNIQUE. Ограничение целостности UNIQUE, в отличие от PRIMARY КЕY, допускает существование значения NULL. При этом к значению NULL также предъявляется требование уникальности, поэтому в столбце с ограничением целостности UNIQUE допускается существование лишь единственного значения NULL. Таким образом, ограничение UNIQUE используется в том случае, когда столбец не входит в состав первичного ключа, но, тем не менее, его значение всегда должно быть уникальным. Например, для таблицы «Дисциплины» первичный ключ строится по номеру дисциплины ID_Дисциплина, введенному для сокращения объема первичного ключа и времени поиска по нему (объем ключа по столбцу типа INTEGER много меньше объема ключа по символьному полю). Однако и название дисциплины (столбец Наименование) должно быть уникальным, для чего ему приписан атрибут UNIQUE: CREATE TABLE Дисциплины (ID_Дисциплина INTEGER NOT NULL PRIMARY КЕY, Наименование VARCHAR(20) NOT NULL UNIQUE) Уникальность может быть определена и на уровне таблицы: CREATE TABLE Дисциплины (ID_Дисциплина INTEGER NOT NULL, Наименование VARCHAR(20) NOT NULL, PRIMARY КЕY (ID_Дисциплина), UNIQUE (Наименование)) Ограничение целостности СНЕСК задает диапазон возможных значений для столбца. Например, если в столбце хранится процентное значение, то необходимо гарантировать, что оно будет лежать в пределах от 0 до 100. Для этого можно использовать тип данных, допускающий хранение целых значений в диапазоне от 0 до 255, совместно с ограничением целостности СНЕСК, которое будет обеспечивать соответствующую проверку значений. Преимущество ограничения целостности СНЕСК заключается в возможности определения для одного столбца множества правил контроля значений. В основе ограничения целостности СНЕСК лежит проверка логического выражения, которое возвращает значение TRUE (истина) либо значение FALSE (ложь). Если возвращается значение TRUE, то ограничение целостности выполняется, и операция изменения или вставки данных разрешается, если возвращается FALSE, то операция изменения или вставки данных отменяется. Например, для обеспечения правильности задания значения для столбца Семестрв таблице «Учебный_план» (оно должно находиться в диапазоне от 1 до 10) можно использовать следующее логическое выражение: ((Семестр >= 1) OR (Семестр <= 10)) Ограничение целостности при этом может быть задано на уровне столбца: Семестр INTEGER NOT NULL СНЕСК ((Семестр >= 1) OR (Семестр <= 10)) или на уровне таблицы: СНЕСК ((Семестр >= 1) OR (Семестр <= 10)) Как уже было сказано, допускается применение нескольких ограничений СНЕСК к одному и тому же столбцу. В этом случае они будут применены в той последовательности, в какой они указаны в инструкции. При вводе записи (строки) в таблицу каждый столбец должен содержать какое-либо значение. Если значение для столбца не указано, то столбец заполняется пустыми значениями null(если для него разрешено хранение значений null). Однако это нежелательно. Наилучшим решением в подобных ситуациях может быть определение для столбца значений по умолчанию.Например, часто «ноль» (не путать с null) определяется как значение по умолчанию для числовых столбцов, а «n/а» (не определено) – как значение по умолчанию для символьных столбцов. Таким образом, определение для столбца значения по умолчанию гарантирует автоматическую подстановку этого значения, если при вставке новых строк значение для столбца не указано. Использование значений по умолчанию удобно, поскольку позволяет ускорить процесс ввода информации. Значительно расширяет область применения значений по умолчанию возможность вызова встроенных функций. Например, если в столбце необходимо указать дату поступления на работу, то по умолчанию можно воспользоваться функцией GETDATE( ). В этом случае, если не указана другая дата, при вводе строки в столбец дат поступления на работу будет помещаться текущая дата. |