Создание базы данных

Размещено на http://www.allbest.ru/

Введение

Проектирование баз данных - одна из наиболее сложных и ответственных задач, связанных с созданием информационной системы. В результате её решения должны быть определены содержание базы данных, эффективный для всех её будущих пользователей способ организации данных и инструментальные средства управления данными. Процесс проектирования базы данных представляет собой последовательность переходов от неформального словесного описания информационной структуры предметной области к формализованному описанию объектов предметной области в терминах некоторой модели.

В реальном проектировании структуры базы данных применяется метод семантического моделирования, который представляет собой моделирование структуры данных, опираясь на смысл этих данных. В качестве инструмента семантического моделирования используются различные варианты диаграмм «сущность-связь» (Entity-Relationship - ER). Все варианты диаграмм «сущность-связь» используют графическое изображение сущностей (объектов) предметной области, их свойств и взаимосвязей между сущностями. Результатом проектирования базы данных является концептуальная схема (ER-диаграмма) моделируемой предметной области.

После разработки проекта базы данных можно приступать к его реализации в конкретной системе управления базами данных (СУБД). Современная волна информационных технологий управления данными основывается на использовании систем управления реляционными базами данных.

Все языки манипулирования данными, созданные до появления реляционных баз данных, были ориентированы на операции с данными, представленными в виде логических записей файлов. Это требовало от пользователей детального знания организации хранения данных и достаточных усилий для указания не только того, какие данные нужны, но и того, где они размещены и как шаг за шагом получить их. Рассматриваемый в методических указаниях непроцедурный язык SQL (Structured Query Language - структурированный язык запросов) ориентирован на операции с данными, представленными в виде логически взаимосвязанных совокупностей таблиц. Особенность предложений этого языка состоит в том, что они ориентированы в большей степени на конечный результат обработки данных, чем на процедуру этой обработки. SQL сам определяет, где находятся данные, какие индексы и даже наиболее эффективные последовательности операций следует использовать для их получения: не надо указывать эти детали в запросе к базе данных.

Спроектировать и создать базу данных еще недостаточно для успешной работы с данными: у нас должна быть возможность как-то обращаться к этим данным, дополнять и изменять их. В этом и состоит цель клиентского приложения - специальной программы, позволяющей работать с данными в удобной для пользователя форме. В общем случае с одной базой данных могут работать множество различных приложений. При рассмотрении приложений, работающих с одной базой данных, предполагается, что они могут работать параллельно и независимо друг от друга, и именно СУБД призвана обеспечить работу множества приложений с единой базой данных таким образом, чтобы каждое из них выполнялось корректно, но учитывало все изменения в базе данных, вносимые другими приложениями.

В методических указаниях рассматриваются вопросы разработки проекта баз данных и его реализации в выбранной системе управления базами данных: от создания объектов базы данных, манипуляции данными - до конструирования сложных запросов по поиску информации, а также средства профессионального пользователя - процедуры, триггеры, вопросы разработки приложений пользователей. Для реализации проекта предлагается система управления базами данных Microsoft SQL Server 2005, которая ориентирована на создание и ведение базы данных на уровне предприятия. Стандартные средства для доступа к данным различных баз данных предоставляет язык запросов SQL. Большинство баз данных имеют свою собственную версию этого языка. Но, несмотря на это, понимание основ SQL поможет работать со многими базами данных на самых разных платформах.

Продукция на рынке информационных услуг обновляется каждые 4-6 месяцев. В связи с этим вполне вероятно, что версия Microsoft SQL Server, используемая на лабораторных занятиях в настоящий момент, может измениться. В помощь студентам для освоения основных возможностей языка SQL на кафедре «Инженерная кибернетика» была разработана виртуальная обучающая система SQL_Education по проектированию баз данных и изучению языка структурированных запросов. Эта программа имеет наглядный интерфейс, снабжена средствами видеоанимации, сопровождается множеством примеров. При подготовке к лабораторным занятиям студент должен изучить соответствующий раздел этой программы (согласно методическим указаниям) и использовать полученные навыки для выполнения заданий.

Ориентированный на работу с таблицами, SQL не имеет достаточных средств для создания сложных прикладных программ. Поэтому в большинстве СУБД он используется вместе с языками программирования высокого уровня. В лабораторной работе № 7 рассматриваются вопросы разработки приложения пользователя. Для иллюстрации того, как должен выглядеть конечный результат разработки, методические указания к этой работе снабжены exe-файлом, представляющим собой пример разработки.

Последняя лабораторная работа посвящена вопросам шифрования данных в MS SQL Server. Эта работа демонстрирует результат применения алгоритмов, рассмотренных в курсе «Защита информации» к защите данных в базе данных.

Кафедра выражает благодарность студентам специальности «Автоматизация и управление» Алтурмесову Е. и Цой М. (гр. БАУ-05) за помощь в реализации пользовательского интерфейса обучающей программы.

1. Лабораторная работа №1. Создание базы данных и определение ее структуры

Цель работы: освоение процедуры создания, удаления, резервирования и восстановления базы данных в среде MS SQL Server 2008; освоение языка определения данных.

1.1 Знакомство с MS SQL Server 2005

1.1.1 Логические компоненты базы данных

MS SQL Server является системой управления реляционными базами данных. Реляционная база данных - это база данных, разделенная на логически цельные сегменты, называемые таблицами, и внутри базы данных эти таблицы связаны между собой посредством ключевых полей. Таблицы являются основной формой хранения данных в базе данных. Реляционная база данных позволяет разделить данные на логически более мелкие и более управляемые сегменты, что обеспечивает оптимальное представление данных и возможность организации нескольких уровней доступа к данным. Вследствие наличия у таблиц общих ключей оказывается возможным объединить данные из нескольких таблиц в одно результирующее множество. Это является одним из основных достоинств реляционных баз данных. Набор действий, выполняемых по отношению к базе данных и рассматриваемый как единое целое, называется транзакцией. Транзакция представляет собой внесение в базу данных некоторых изменений. Каждая база данных имеет соответствующий ей журнал транзакций - место, куда SQL Server записывает все выполняемые транзакции перед тем, как записать их в базу данных.

Данные в SQL Server организованы в нескольких различных объектах, которые пользователь видит при создании базы данных. К ним относятся:

- пользователи базы данных Database Users);

- роли базы данных (Database Roles);

- таблицы (Tables);

- представления (SQL Server Views);

- хранимые процедуры (Stored Procedures);

- правила (Rules);

- значения по умолчанию (Defaults);

- типы данных, определенные пользователем (User Defined Datatypes);

- диаграммы базы данных (Database Diagrams).

Помимо этих видимых объектов в каждой базе данных имеются еще некоторые:

- условия на значения (Constraints);

- индексы (Indexes);

- ключи (Keys);

- триггеры (Triggers).

1.1.2 Типы команд SQL

Основные категории команд, реализующих в SQL выполнение различных функций:

- DDL (Data Definition Language - язык определения данных);

- DML (Data Manipulation Language - язык манипуляций данными);

- DQL (Data Query Language - язык запросов к данным);

- DCL (Data Control Language - язык управления данными);

- команды администрирования данных;

- команды управления транзакциями.

Среди таких функций - построение объектов базы данных, управление объектами, пополнение таблиц базы данных новыми данными, обновление данных, уже имеющихся в таблицах, выполнение запросов, управление доступом пользователей к базе данных, а также осуществление общего администрирования базы данных.



1.1.3 Основные типы данных

Типы данных позволяют хранить в базе данных различные по своей природе данные от любых символов до десятичных чисел, значений дат и времени. Подход к разделению данных на типы во всех языках одинаков - и при работе с переменными в языках третьего поколения типа С, и при работе с реляционными базами данных с помощью SQL. Хотя в каждой реализации SQL для стандартных типов данных используются разные имена, работают они практически одинаково. И при краткосрочном планировании, и с точки зрения перспективы нужно с особой тщательностью выбирать типы данных, их длину, масштаб и точность. При этом нужно принять во внимание и сложившиеся правила соответствующего бизнеса, и то, каким образом должны предоставляться данные конечному пользователю. Для этого вы должны понимать природу самих данных и то, как эти данные связаны внутри базы данных. Типы данных являются характеристиками самих данных, чьи атрибуты размещаются прямо в соответствующих полях таблицы. Например, можно указать, что некоторое поле должно содержать только числовые значения, и это не позволит вводить буквенно-числовые значения, когда, например, вы не хотите, чтобы последние появлялись в поле, предназначенном для хранения денежных значений.

Самыми общими типами в SQL, как и в большинстве других языков, являются: символьные строки; числовые строки; значения даты и времени.

Символьные (character) типы данных позволяют хранить буквенные, числовые и специальные (например, ? или >) символы. При загрузке в область хранения (такую, как столбец таблицы) символьные данные вводятся в одинарных или двойных кавычках.

Тип char(n). При хранении данных этого типа для каждого символа используется один байт. Число n определяет размер области хранения максимального количества символов данного столбца. Если вводится значение, меньшее n, SQL Server добавит пробелы после последнего символа, чтобы общая длина равнялась n.

Для экономии дискового пространства, когда хранящиеся в столбце значения имеют разную длину, можно использовать тип varchar(n). В отличие от предыдущего типа данных, размер области хранения для данных этого типа меняется в соответствии с фактическим количеством символов, хранящихся в каждом столбце таблицы, пробелы в конце введенного значения не добавляются.

Тип text. Используется для хранения больших объемов текстовой информации. Для вставки данных в столбец, определенный для данных этого типа, они должны быть заключены в одинарные кавычки.

Числовые (numeric) типы данных. Стандартными для SQL являются следующие типы: integer, smallint - для хранения целых чисел; real - для хранения положительных или отрицательных дробей с точностью до семи цифр; float(n) - для хранения положительных или отрицательных дробей с точностью до пятнадцати цифр.

Типы данных datetime и smalldatetime. Они используются для хранения даты и времени. Гораздо удобнее хранить дату и время в формате одного из предназначенных для этого типов данных, а не в виде строки символов. В этом случае дата и время выводятся на экран в привычном формате. Тип datetime позволяет определить дату и время, начиная с 1/1/1753 и заканчивая 12/31/9999; а тип smalldatetime - с 1/1/1900 по 6/6/2079.

Перечисленные типы данных позволяют хранить до 90% информации. Кроме этих типов, Transact SQL содержит набор специальных типов данных. Можно определить собственный тип данных - пользовательский, который затем будет использоваться для сохраняемых структур.

При назначении типа данных столбцу таблицы возможно использование ограничений NULL/NOT NULL, которые позволяют указать, какие из столбцов должны обязательно иметь значения во всех строках таблицы.



1.2 Работа с базой данных

1.2.1 Создание базы данных

Для создания любого объекта SQL Server и, в частности, базы данных существует несколько способов, базирующихся на выполнении определенной команды.

Работа начинается с создания базы данных. Команда создания базы данных Create Database имеет следующий синтаксис:

CREATE DATABASE имя_базы_данных

ON [PRIMARY]

(NAME = имя_базы_данных_data,

FILENAME='…\имя_базы_данных _data.mdf ', size = размер,

maxsize = максимальный размер, filegrowth = приращение)

LOG ON

(NAME = имя_базы_данных _log,

FILENAME='…\имя_базы_данных _log.ldf ', size = размер,

maxsize = максимальный размер, filegrowth = приращение)

Здесь и далее при описании общего вида команды, размещение опции в квадратных скобках означает, что этот параметр не всегда обязателен. Например, в данном случае параметр PRIMARY определяет файл, содержащий логическое начало базы данных и системных таблиц. В базе данных может быть только один первичный (PRIMARY) файл. Если этот параметр пропущен, то первичным считается первый файл в списке. По умолчанию файлам типа primary присваивается расширение .mdf. Опции разделены вертикальной чертой - это означает возможность выбора из двух альтернативных вариантов. Многоточие означает путь.



1.2.2 Удаление базы данных

Удаление базы данных приводит к освобождению всего занимаемого ею пространства во всех файлах, где эта база данных находилась, а также к удалению всех содержащихся в ней объектов:

а) удаление базы данных в графическом режиме предполагает выполнение следующих действий:

- щелкните мышью имя базы данных, которую хотите удалить;

- выберите в контекстном меню команду Delete; в появившемся окне сообщений подтвердите необходимость удаления базы данных.

б) для удаления базы данных с помощью Transact-SQL достаточно выполнить команду

DROP DATABASE имя_базы_данных

1.2.3 Создание резервной копии базы данных

Для сохранения копии базы данных, переноса ее на съемный носитель, вначале необходимо создать резервную копию базы данных посредством специальных возможностей MS SQL Server 2005:

- выберите свою базу данных из списка Databases и в контекстном меню строку Tasks-Back Up;

- в появившемся окне создания резервной копии вначале очистите список названий баз данных, готовых к копированию;

- кнопкой «Обзор» откройте следующее окно для выбора места на диске, введите имя резервной копии базы данных, подтвердите операцию копирования. Система должна дать сообщение об успешном копировании.

Надо отметить, что под резервной копией понимается сохранение данных базы данных. Если же появилась необходимость переноса всей базы данных полностью, то это можно выполнить, скопировав .mdf и .ldf файлы, которые располагаются по адресу C:\Program Files\...\Ms SQL\Data\имя_файла.



1.2.4 Восстановление базы данных

Если появилась необходимость перенести сохраненную резервную копию на компьютер, это тоже выполняется посредством специальных возможностей MS SQL Server 2005:

- выберите базу данных для проведения операции восстановления;

- в контекстном меню выберите строку Tasks-Restore;

- появится страница с общими параметрами восстановления; укажите источник и местоположение резервной копии;

- выберите страницу «Параметры», отметьте флажок «Перезаписать существующую базу данных» и первый переключатель в разделе «Состояние восстановления»;

- подтвердите выбранные операции.

Система сообщит об успешном восстановлении. Если появится сообщение о невозможности восстановления базы данных и система предложит изменить скрипт, необходимо выполнить это требование (в отдельном окне со скриптом).

1.3 Определение структуры базы данных

1.3.1 Описание базы данных, используемой в лабораторной работе

В дальнейшем для освоения работы в среде SQL Server будет рассматриваться база данных компании, занимающейся продажами. Структура базы данных определяется на этапе проектирования. Вопросы проектирования базы данных подробно рассматриваются в расчетно-графической работе №1. Назовем эту базу TradeCompany.

База данных TradeCompany должна содержать данные о деятельности некоторой торговой фирмы. Фирма продает разнообразные виды товаров. На фирме имеется информация о клиентах фирмы (юридические лица). Для каждой сделки по продажам выписывается счет, в котором отражаются: номер счета, данные о клиенте, перечень и количество проданных товаров, дата продажи.

Предположим, что вся эта информация хранится в таблицах Товары, Клиенты и Счета. Любая таблица имеет структуру и хранимые в таблице данные. Структура таблицы определяется ее столбцами: их количеством, именем каждого столбца, типом данных, которые хранятся в столбце, и шириной столбца.

Структура таблиц базы данных TradeCompany приведена ниже.

Таблица 1.1 - Товары

Код товара	Описание товара	Цена товара

Таблица 1.2 - Клиенты

Код клиента	Название клиента	Адрес клиента	Телефон клиента

Таблица 1.3 - Счета

Номер счета	Код клиента	Код товара	Количество	Дата счета

Данные определяются содержимым строк таблицы. Столбцы в таблице называют полями, а строки - записями.

Ключевое поле (primary key) - это столбец, данные в котором однозначно идентифицируют каждую строку данных в таблице реляционной базы данных. Задачей ключевого поля является обеспечение уникальности каждой записи. Обычно ключ задается одним столбцом в таблице, но можно задать и сложный ключ на основе комбинации значений нескольких столбцов. Ключ таблице назначается при ее создании. Внешний ключ (foreign key) - это столбец в дочерней таблице, ссылающийся на ключ родительской таблицы. Столбец, назначенный внешним ключом, используется для ссылок на столбец, определенный как ключ в другой таблице.

В соответствии с требованиями используемого программного обеспечения для объектов рассматриваемой базы данных будем использовать названия полей на английском языке (сами данные можно вводить в кириллице). Что касается назначения имен таблицам, следует отметить следующее. В базе данных, кроме таблиц, может содержаться и множество других объектов. Поэтому за стандарт принято наличие суффикса _TBL в именах таблиц (а, например, суффикс _IDX используется для индексов таблиц). Желательно не только следовать правилам назначения имен, но и правилам, принятым внутри соответствующей области деятельности, чтобы имена носили описательный характер и соответствовали тем данным, на которые эти имена указывают. Также в именах столбцов, состоящих из нескольких слов, в качестве разделителя обычно используется символ подчеркивания. Использование суффиксов при назначении имен объектам базы данных не является обязательным. Выберем названия для таблиц создаваемой базы данных: CUSTOMER_TBL, PRODUCTS_TBL, ORDERS_TBL.

С учетом изложенного, схему связей между таблицами рассматриваемой базы данных можно представить в следующем виде (см. рисунок 1.1).

Размещено на http://www.allbest.ru/

Рисунок 1.1 - Связи между таблицами базы данных TradeCompany

Линии, связывающие таблицы, указывают на связи таблиц посредством общего ключевого поля. В представленном варианте в таблице ORDERS_TBL не определен первичный ключ, так как в счете с одним номером может быть несколько товаров (номер счета используется в бухгалтерских документах).

1.3.2 Определение структур базы данных (DDL)

Язык определения данных (DDL) является частью SQL, дающей пользователю возможность создавать различные объекты базы данных и переопределять их структуру, например, создавать или удалять таблицы.

Рассмотрим следующие команды DDL: CREATE TABLE, ALTER TABLE, DROP TABLE.

Чтобы создавать таблицы, используется оператор CREATE TABLE.

Синтаксис оператора для создания таблиц:

CREATE TABLE имя_таблицы

(ПОЛЕ1 ТИП ДАННЫХ [NOT NULL],

ПОЛЕ2 ТИП ДАННЫХ [NOT NULL],

ПОЛЕЗ ТИП ДАННЫХ [NOT NULL],

ПОЛЕ4 ТИП ДАННЫХ [NOT NULL],

ПОЛЕ5 ТИП ДАННЫХ [NOT NULL] )

Ключ таблице назначается при ее создании с помощью опции PRIMARY KEY одному или нескольким полям и является по своей сути ограничивающим условием:

CREATE TABLE имя_таблицы PRIMARY KEY

(ПОЛЕ1 ТИП ДАННЫХ [NOT NULL], …)

Можно создать ключ и непосредственно как ограничивающее условие через запятую после определения всех столбцов таблицы, причем, если ключ является составным, перечисляются все его компоненты:



CREATE TABLE имя_таблицы

(ПОЛЕ1 ТИП ДАННЫХ [NOT NULL],

ПОЛЕ2 ТИП ДАННЫХ [NOT NULL], …

PRIMARY KEY (ПОЛЕ1, ПОЛЕ2) )

Внешний ключ создается с помощью опции FOREIGN KEY. Внешний ключ создается после определения всех столбцов таблицы следующим образом:

CREATE TABLE имя_таблицы_1

(ПОЛЕ1_1 ТИП ДАННЫХ [NOT NULL],

ПОЛЕ1_2 ТИП ДАННЫХ [NOT NULL],

…,

ПОЛЕ2_1 ТИП ДАННЫХ [NOT NULL],

CONSTRAINT ПОЛЕ2_1_FK FOREIGN KEY (ПОЛЕ2_1)

REFERENCES имя_таблицы_2 (ПОЛЕ2_1))

Столбец ПОЛЕ2_1 здесь назначается внешним ключом таблицы имя_таблицы_1. Этот внешний ключ ссылается на столбец ПОЛЕ2_1 таблицы имя_таблицы_2.

1.4 Модификация таблицы

После создания таблицу можно модифицировать и с помощью команды ALTER TABLE. С помощью этой команды можно добавлять и удалять столбцы, менять определения столбцов, добавлять и удалять ограничения.

Стандартный синтаксис команды ALTER TABLE следующий:



ALTER TABLE имя_таблицы [MODIFY] [COLUMN имя_столбца]

[ТИП ДАННЫХ|NULL NOT NULL] [RESTRICT|CASCADE]

[DROP] [CONSTRAINT имя_ограничения]

[ADD] [COLUMN] определение столбца

а) модификация элементов таблицы. Атрибуты столбца задают правила представления данных в столбце. С помощью команды ALTER TABLE можно менять атрибуты столбца. Под атрибутами здесь понимается следующее: тип данных в столбце; длина, точность или масштаб данных в столбце; разрешение или запрет иметь в столбце значение NULL.

При добавлении столбца в уже существующую таблицу с имеющимися в ней данными новому столбцу нельзя назначить атрибут NOT NULL. NOT NULL означает, что столбец должен содержать значения для каждой строки в таблице, так что, если добавляемый столбец получит атрибут NOT NULL, вы сразу же получите противоречие с этим ограничением, поскольку имеющиеся в таблице столбцы не имеют значений для нового столбца. И все же имеется возможность добавить столбец, требующий обязательного ввода данных, следующим образом:

- добавьте столбец, задав ему атрибут NULL (это значит, что в столбце не обязательно должны присутствовать данные);

- введите данные в каждую строку нового столбца таблицы;

- убедившись, что столбец содержит значение в каждой из строк таблицы, можно изменить атрибут столбца на NOT NULL;

б) изменение столбцов. При изменении столбцов таблиц нужно учитывать целый ряд моментов. Общие правила следующие:

- ширина столбца может быть увеличена до максимальной длины, разрешенной для соответствующего типа данных;

- ширину столбца можно уменьшить только до наибольшей длины имеющихся в этом столбце значений;

- для столбцов с числовыми данными ширину всегда можно увеличить;

- для столбцов с числовыми данными ширину можно уменьшить только тогда, когда нового числа знаков будет достаточно для размещения любого из имеющихся в столбце значений;

- для числовых данных можно увеличивать или уменьшать число десятичных знаков;

- тип данных в столбце обычно можно изменить.

В некоторых реализациях использование определенных опций оператора ALTER TABLE может быть запрещено. Например, вам могут не позволить удалять столбцы из таблиц. Вместо этого вам нужно будет удалить таблицу и создать новую с нужным числом столбцов. Могут возникнуть проблемы с удалением столбцов из таблицы, зависящей от столбца из другой таблицы, или с удалением столбца, на который ссылается другая таблица. По этому поводу внимательно просмотрите документацию, предлагаемую той реализацией SQL, с которой вы работаете;

в) добавление ограничений. Эта ситуация может возникнуть, например, в случае, когда при создании таблицы не были определены ключевые поля:

ALTER TABLE имя_таблицы

ADD CONSTRAINT имя_таблицы_PK

PRIMARY KEY (имя_поля1, имя_поля2)

г) внешние ключи можно назначить таблице следующим образом:

ALTER TABLE имя_таблицы

ADD CONSTRAINT ID_FK FOREIGN REY (имя_поля)

REFERENCES имя_таблицы (имя_поля)

д) удаление таблиц является, пожалуй, самым простым делом. Синтаксис оператора, используемого для удаления таблиц, следующий:



DROP TABLE имя_таблицы [RESTRICT|CASCADE]

Если используется опция RESTRICT, либо на таблицу ссылается представление или ограничение, используемый для удаления оператор DROP возвратит ошибку. При использовании опции CASCADE будет выполнено удаление не только самой таблицы, но и всех ссылающиеся на таблицу представлений и ограничений.

1.5 Задание на лабораторную работу

Прежде чем приступить к выполнению задания, следует ознакомиться с соответствующими разделами виртуальной обучающей системы SQL_Education.

Создание базы данных. TradeCompany

а) создание базы данных в графическом режиме.

При этом способе выполняются следующие действия:

- запустите программу MS SQL Server 2005 посредством основного меню: Пуск-Все программы- MS SQL Server 2005-SQL Server Management Studio;

- в появившемся диалоговом окне предлагается ввести имя сервера, оно установлено по умолчанию, щелкните на Connect;

- в следующем окне Object Explorer на строке Databases из контекстного меню выберите New Database;

- появится окно, в котором надо ввести имя создаваемой базы данных и ОК; в нижней части этого окна в таблице располагается информация о файлах базы данных (разверните окно, чтобы видеть все колонки таблицы); здесь кнопка Add предназначена для случая, если база данных располагается не в одном файле;

- закройте окно; убедитесь, что ваша база появилась в списке Database;

- удалите базу данных (команда Delete из контекстного меню);

б) создание базы данных посредством команды Transact-SQL.

В окне Object Explorer щелкните на кнопку New Query. В правой части экрана появится окно редактора запросов. Введите в него команду на создание базы данных. Если команда выполнилась успешно, в нижней части окна должно появиться сообщение: Command (s) completed successfully.

В отличие от графического режима, чтобы увидеть в списке баз данных только что созданную базу, необходимо на строке Database из контекстного меню выбрать Refresh.

Создание и модификация таблиц базы данных TradeCompany

Таблицы тоже можно создавать несколькими способами:

а) для создания таблицы в графическом режиме необходимо выполнить следующее (создайте таким способом таблицу CUSTOMER_TBL.):

- откройте папку своей базы данных; отметьте строку Tables и в контекстном меню выберите New Table;

- в появившемся окне введите наименования столбцов таблицы, выберите тип данных, установите (или уберите) флажок Allows Null; внизу окна установите свойства столбцов;

- закройте текущее окно, подтвердите сохранение изменений, введите имя таблицы;

б) нажмите в окне Object Explorer кнопку New Query, откроется окно редактора команд, вводя в это окно команды создания таблицы (создайте таким способом таблицы PRODUCTS_TBL, ORDERS_TBL), нажимая кнопку Execute (выполнение команды), создайте еще одну таблицу;

в) чтобы изменить структуру таблицы, выбираем из контекстного меню Modify Table;

г) для быстрого изучения команд SQL, выделите таблицу базы данных, выберите в контекстном меню Script Table As/ … /New Query Editor Window (вместо многоточия выберите требуемую команду), появится окно редактора команд, в котором можно просмотреть заготовки изучаемых скриптов на создание, удаление, обновление и т.д.

Просмотрите диаграмму связей созданной базы данных (раздел Diagrams)

1.6 Требования к отчету

Отчет по работе выполняется на бумажном носителе и должен содержать:

- описание вариантов создания базы данных;

- обоснование выбора типов данных для полей таблиц;

- определение первичных и внешних ключей таблиц;

- команды создания структур таблиц.

1.7 Контрольные вопросы

Что означает аббревиатура SQL?

Перечислите типы команд SQL.

Какие способы создания базы данных вы знаете, в чем их отличия?

Сколько файлов используется для базы данных?

Для чего предназначен журнал транзакций?

Объясните создание резервной копии и восстановление базы данных.

Объясните способы удаления базы данных.

Дайте определение реляционной базы данных.

В каких объектах хранится исходная информация базы данных?

Что называется полем таблицы, записью таблицы?

Какая команда определяет структуру таблицы?

Дайте определение первичного ключа таблицы.

Объясните назначение внешних ключей.

С помощью какой команды можно модифицировать структуру таблицы?



2. Лабораторная работа № 2. Язык манипуляции данными

Цель работы: изучение языка манипуляции данными.

Язык манипуляций данными (DML) является частью SQL, обеспечивающей пользователю базы данных возможность вносить реальные изменения в данные реляционной базы данных. С помощью DML пользователь может пополнять таблицы новыми данными, обновлять уже имеющиеся данные и удалять их из таблиц.

В SQL имеется три основных команды DML: INSERT, UPDATE, DELETE.

2.1 Заполнение таблиц данными

Заполнение таблицы данными - это процесс ввода новых данных в таблицу либо вручную с помощью отдельных команд, либо автоматически с помощью программ или каким-либо иным способом. То, какие данные и в каком количестве можно будет при этом вводить в таблицу, зависит от многих факторов, основными из которых являются ограничения, заданные при определении таблицы, физические размеры таблицы, типы данных ее столбцов, ширина столбцов, требования целостности в виде ключей и внешних ключей.

Возможны следующие ситуации:

а) ввод новых данных в таблицу. Для ввода новых данных в таблицу используется оператор INSERT. Он имеет вид:

INSERT INTO имя_таблицы

VALUES ('значение1', 'значение2', [NULL] )

Согласно представленному здесь синтаксису оператора INSERT, в список VALUES необходимо поместить значения для всех столбцов соответствующей таблицы. Значения в списке разделяются запятыми. Символьные значения и значения дат должны быть заключены в кавычки. Для числовых значений и пустых значений, задаваемых ключевым словом NULL, кавычки не нужны. Должны быть указаны значения для всех столбцов таблицы;

б) ввод данных в определенные столбцы таблицы. Имеется возможность ввести данные не во все, а только в определенные столбцы. В этом случае в операторе INSERT вместе со списком значений VALUES нужно указать и список соответствующих им столбцов:

INSERT INTO имя_таблицы ('СТОЛБЕЦ1', 'СТОЛБЕЦ2')

VALUES ('ЗНАЧЕНИЕ1', 'ЗНАЧЕНИЕ2');

Порядок в списке значений должен соответствовать порядку ввода значений в таблицу, задаваемому списком столбцов. Список столбцов в операторе INSERT не обязательно должен соответствовать списку столбцов в определении соответствующей таблицы, а вот список вводимых значений должен обязательно соответствовать списку избранных столбцов;

в) ввод значений NULL. Ввести значение NULL в таблицу просто. Это бывает нужно, в частности, когда значение соответствующего столбца не известно. Синтаксис оператора для ввода значения NULL следующий.

INSERT INTO имя_схемы.имя_таблицы

VALUES ('ЗНАЧЕНИЕ!', NULL, 'ЗНАЧЕНИЕЗ'}

2.2 Обновление уже имеющихся данных

Уже существующие в таблице данные можно изменить с помощью команды UPDATE. Команда UPDATE не добавляет новых записей в таблицу и не удаляет их, а только дает возможность изменить данные. С помощью одной такой команды можно изменить данные только одной таблицы, но одновременно можно менять данные нескольких столбцов. Одним таким оператором можно изменить и одну строку данных и целый набор строк:

а) обновление значений одного столбца. В своей простейшей форме оператор UPDATE изменяет один столбец таблицы. При изменении одного столбца можно изменить только одну запись или сразу несколько Синтаксис оператора для изменения данных в одном столбце следующий

UPDATE имя_таблицы

SET ИМЯ_СТОЛБЦА = 'ЗНАЧЕНИЕ'

[WHERE УСЛОВИЕ]

б) обновление с помощью оператора UPDATE нескольких столбцов сразу:

UPDATE имя_таблицы

SET СТОЛБЕЦ1 = 'ЗНАЧЕНИЕ'

[, СТОЛБЕЦ2 = 'ЗНАЧЕНИЕ']

[, СТОЛБЕЦ3 = 'ЗНАЧЕНИЕ']

[WHERE УСЛОВИЕ]

Обратите внимание на использование ключевого слова SET: оно одно, а описаний столбцов - несколько. Описания столбцов разделяются запятыми.

2.3 Удаление данных из таблиц

Для удаления данных из таблиц используется команда DELETE. Команда DELETE предназначена не для того, чтобы удалять значения отдельных столбцов, а для того, чтобы удалять целые записи. Оператор DELETE следует применять с осторожностью - слишком уж безотказно он работает. Чтобы удалить одну или несколько записей из таблицы, используйте следующий синтаксис оператора DELETE.

DELETE [FROM] имя_таблицы

[WHERE УСЛОВИЕ];

При удалении строк из таблицы ключевое слово WHERE представляет собой очень важную часть оператора DELETE. Если ключевое слово WHERE в операторе DELETE опущено, будут удалены все строки таблицы. Поэтому примите за правило всегда использовать ключевое слово WHERE в операторе DELETE.

2.4 Задание на лабораторную работу

Прежде чем приступить к выполнению задания, следует ознакомиться с соответствующими разделами виртуальной обучающей системы SQL_Education.

Измените структуру таблицы ORDERS_TBL - добавьте в нее ключевое поле ORDERS_ID (это идентификатор строки в таблице). Это связано с тем, что иногда необходимо однозначно определять каждую сделку по проданному товар).

Заполните данными все таблицы вашей базы данных. используйте все возможности ввода данных:

- графический режим: выделить таблицу, в контекстном меню выбрать Open Table;

- выделить таблицу и из контекстного меню выбрать Insert To-New Query Editor Window:

- выбрать меню New Query.

В таблицах CUSTOMER_TBL и PRODUCT_TBL должно быть не менее пяти записей, в таблице ORDERS_TBL - не менее 15-ти (покупка всевозможных товаров всеми клиентами в различные дни).

Добавьте в таблицу ORDERS_TBL столбец TOTAL_COST (в этом столбце будет храниться стоимость проданного товара). Заполните этот столбец значениями NULL.

Используя команду UPDATE, рассчитайте значения поля TOTAL_COST как произведение цены товара (PRODUCT_TBL.COST) на количество проданного товара (ORDERS_TBL.QTY).

Измените цену некоторых товаров в таблице PRODUCT_TBL. Измените количество проданного товара. Убедитесь, что данные столбца ORDERS_TBL.TOTAL_COST изменяются.

Создайте таблицу MANUFACTURER_TBL, в которой будут храниться данные о производителях товаров (поля таблицы выберите по своему усмотрению). В соответствии с этим изменится структура таблицы PRODUCT_TBL: первичный ключ таблицы MANUFACTURER_TBL будет внешним ключом таблицы PRODUCT_TBL. Внесите эти изменения.

таблицу SELLER_TBL, в которой будут храниться данные о торговых агентах (продавцах). В соответствии с этим изменится таблица ORDERS_TBL - в нее надо добавить первичный ключ таблицы SELLER_TBL (надо учитывать, кем совершена сделка по продаже).

Просмотрите диаграмму связей преобразованной реляционной схемы базы данных.

Заполните созданные таблицы и поля данными.

Удалите записи о продажах, совершенных в марте текущего года.

Требования к отчету

Отчет по работе выполняется на бумажном носителе и должен содержать:

- команды добавления данных в таблицы базы данных;

- все команды изменения реляционной схемы данных;

- команды обновления данных в таблицах базы данных;

- диаграмму связей базы данных;

- листинги таблиц с данными.

2.5 Контрольные вопросы

Что такое DML?

Какие команды манипуляции данными вы знаете?

Что означает определение поля NOT NULL?

Что указывается в списке VALUES команды INSERT?

Можно ли вводить данные только в определенные столбцы таблицы?

Можно ли командой UPDATE менять данные в нескольких таблицах?

Можно ли командой UPDATE менять данные в нескольких столбцах одной таблицы?

Всегда ли необходимо использовать условие в операторе удаления записей таблицы?

В чем отличие DDL от DML?

В чем отличие структуры таблицы и данных таблицы?



3. Лабораторная работа №3 Отбор данных (DQL)

Цель работы: освоение языка запросов к базе данных и способов формирования простых и сложных запросов.

3.1 Оператор SELECT

Этот раздел языка представлен только одной командой, однако для пользователя реляционной базы данных язык запросов к данным (DQL) является самой главной частью SQL. Этой командой является команда SELECT. Команда, имеющая множество опций и необязательных параметров, используется для построения запросов к реляционным базам данных. С ее помощью можно конструировать запросы любой сложности - от самых общих до специальных и от самых простых до невероятно сложных.

Запрос - это требование на получение информации из базы данных. Запросы используются для того, чтобы извлечь данные в том виде, который удобен пользователю.

Оператор SELECT используется для составления запросов к базе данных. Оператор SELECT не используется сам по себе, а требует указания некоторых параметров с помощью ключевых слов. Кроме обязательных, у этого оператора имеется несколько необязательных ключевых слов, расширяющих его возможности.

В операторе SELECT ключевое слово SELECT используется в совокупности с ключевым словом FROM для того, чтобы организовать извлечение данных из базы данных в удобном для чтения формате. Часть запроса, заданная ключевым словом SELECT, определяет источник отбора данных. Синтаксис простого оператора SELECT следующий:



SELECT * | ALL | DISTINCT [СТОЛБЕЦ1, СТОЛБЕЦ2]

FROM ТАБЛИЦА1 [, ТАБЛИЦА2]

За ключевым словом SELECT в запросе следует список столбцов, значения которых вы хотели бы видеть в результате запроса. За ключевым словом FROM следует список таблиц, из которых должны извлекаться данные. Звездочка (*) используется для указания того, что в результате запроса должны быть показаны значения всех столбцов таблицы. Опция ALL используется тогда, когда нужно показать все значения столбца, включая и повторяющиеся. Опция DISTINCT используется для того, чтобы повторения исключить. Из этих опций используемой по умолчанию опцией является ALL, которую поэтому указывать не обязательно. Обратите внимание на то, что имена столбцов в списке, следующем за ключевым словом SELECT, разделяются запятыми, точно так же, как имена таблиц, следующие за ключевым словом FROM.

3.2 Операции в условиях для отбора данных

Для отбора данных используются условия. Условие - это часть запроса, содержащая информацию, на основе которой отбираются данные. Условие может принимать либо значение TRUE, либо значение FALSE, что и используется для отбора.

Синтаксис оператора SELECT, использующего выражение WHERE, следующий:

SELECT [ALL | * | DISTINCT СТОЛБЕЦ1, СТОЛБЕЦ2]

FROM ТАБЛИЦА1 [, ТАБЛИЦА2]

WHERE [УСЛОВИЕ1|ВЫРАЖЕНИЕ1]

[AND УСЛОВИЕ2 | ВЫРАЖЕНИЕ2]



В выражении WHERE может содержаться несколько условий.

Условия задаются с помощью операций. Операция - это символ или ключевое слово SQL, использующееся для связывания элементов в операторе SQL.

В условиях для отбора данных используются следующие типы операций: операции сравнения, логические операции, арифметические операции, специальные операции.

Операции сравнения представляются знаками =, <>, <, >, <=, >=. Эти операции предназначены соответственно для проверки равенства и неравенства значений, проверки выполнения отношений "меньше", "больше", "меньше или равно", "больше или равно" между ними.

Логические операции в SQL задаются ключевыми словами, а не символами. Используются следующие логические операции:

- AND (логическое "И") сравнивает два логических значения и возвращает TRUE (истина), если оба значения истинны (т.е. равны TRUE), в остальных случаях - FALSE (ложь);

- OR (логическое "ИЛИ") сравнивает два логических значения и возвращает TRUE, если хотя бы один из аргументов равен TRUE;

- NOT (логическое отрицание) возвращает TRUE, если его аргумент равен FALSE и наоборот.

Арифметические операции используются в SQL точно так же, как и в большинстве других языков. Таких операций четыре: + (сложение), * (умножение), - (вычитание), / (деление). Арифметические операции можно комбинировать. Пользователь может управлять порядком выполнения операций в выражении только с помощью скобок. Заключенное в скобки выражение означают необходимость рассматривать выражение как единый блок, порядок выполнения операций (приоритет операций) задает порядок, в котором обрабатываются выражения в математических выражениях или встроенных функциях SQL.



3.3 Отбор данных из нескольких таблиц

Одной из самых полезных возможностей SQL является возможность отбора данных из нескольких таблиц. Для связывания таблиц используются ключевые поля.

При связывании таблиц необходимым элементом оператора SQL является ключевое слово WHERE. Имена таблиц для связывания указываются в списке ключевого слова FROM. Связь определяется в выражении ключевого слова WHERE. Есть несколько типов связывания:

а) связвание по равенству. Это самый полезный тип связывания. Его также называют внутреним связыванием (INNER JOIN). При связывании по равенству таблицы связываются по общему столбцу, который в каждой из таблиц обычно является ключевым. Синтаксис оператора, задающего связывание по равенству, имеет вид:

SELECT таблица1.столбец1, таблица2.столбец2…

FROM таблица1, таблица 2 [, таблица3]

WHERE таблица1.имя_столбца = таблица2.имя_столбца

[AND таблица1.имя_столбца = таблица3.имя_столбца]

Обратите внимание, что в списке оператора SELECT вместе с именами каждого из столбцов указываются имена соответствующих таблиц. Это назвается подробным определением столбца в запросе. Подробные определения требуются только для тех столбцов, которые присутствуют в нескольких из указанных в запросе таблиц. Но в операторе обычно указываются подробные определения для всех столбцов;

б) естественное связывание. Естественное связывание почти эквивалентно связыванию по равенству, но при естественном связывании таблиц повторения эквивалентных столбцов исключаются. Условие связывания оказывается таким же, но столбцы выбираются иначе. Синтаксис оператора:

SELECT таблица1.*, таблица2.имя_столбца

[, таблица3.имя_столбца]

FROM таблица1, таблица 2 [, таблица3]

WHERE таблица1.имя_столбца = таблица2.имя_столбца

[AND таблица1.имя_столбца = таблица3.имя_столбца]

в) связывание по неравенству. При связывании по неравенству две или несколько таблиц объединяются по условию неравенства значения столбца таблицы значению из столбца другой таблицы. Синтаксис соответствующей части оператора:

FROM таблица1, таблица 2 [, таблица3]

WHERE таблица1.имя_столбца != таблица2.имя_столбца

[AND таблица1.имя_столбца != таблица3.имя_столбца]

При связывании по неравенству обычно в выводе присутствует ряд строк, которые на самом деле оказываются ненужными, так что результаты запроса, в котором используется связывание по неравенству, всегда требуют дополнительной проверки;

г) рекурсивное связывание (использование псевдонимов). Отметим, что с целью сокращения объема печатания таблицам могут назначаться псевдонимы - временное переименование таблиц. Особенно часто псевдонимы используются при рекурсивном связывании.

Рекурсивное связывание (SELF JOIN) предполагает связывание таблицы с ней же самой, применяя временное переименование таблицы в операторе SQL. Синтаксис оператора:



SELECT А.имя_столбца, В.имя_столбца, [, С.имя_столбца]

FROM таблица1 А, таблица 2 В [, таблица3 С]

WHERE А.имя_столбца = В.имя_столбца

[AND А.имя_столбца = С.имя_столбца]

Здесь А, В, С - псевдонимы таблиц.

д) связывание по нескольким ключам. В зависимости от структуры базы данных таблицы могут иметь не простые, а составные ключи, то есть состоящие из нескольких столбцов. Из нескольких столбцов может состоять и внешний ключ. В этом случае в предложении WHERE надо указать сравнение каждой составляющей ключа.

3.4 Задание на лабораторную работу

Прежде, чем приступить к выполнению задания, следует ознакомиться с соответствующими разделами виртуальной обучающей системы SQL_Education.

Выполните поиск информации в отдельных таблицах базы данных TradeCompany:

- список всех клиентов фирмы;

- список всех товаров, имеющихся на фирме;

- список товаров, цена которых не превышает указанную сумму;

- список счетов, выданных в текущем месяце;

- список товаров определенного наименования.

Выполните поиск информации в нескольких таблицах базы данных:

- список клиентов фирмы, приобретавших товар определенного вида;

- список всех клиентов и их счетов;

- список счетов, выставленных определенному клиенту;

- список клиентов и наименование товаров, приобретенных ими в определенную дату;

- список товаров и их количество, приобретенных определенным клиентом;

- найдите дату счета, выписанного определенному клиенту за приобретение определенного товара;

- список всех клиентов, которые не приобретали определенный товар.

3.5 Требования к отчету

Отчет по лабораторной работе содержит листинги команд и результаты выполнения запросов.

3.6 Контрольные вопросы

К какому разделу SQL относится оператор SELECT?

Назовите обязательные составляющие оператора SELECT.

Для всех ли данных в выражении ключевого слова WHERE обязательно нужно использовать кавычки?

Можно ли в выражении для ключевого слова WHERE задать несколько условий?

Допустимы ли кавычки для значений числовых полей?

Можно ли оператор SELECT использовать без ключевого слова FROM?

Что такое псевдоним таблицы?

При связывании таблиц должны ли они связываться в том же порядке, в каком они указаны в выражении ключевого слова FROM?

При использовании в операторе запроса таблицы-связки обязательно ли выбирать в запросе ее столбцы?

Можно ли связывать в запросе не один, а несколько столбцов таблиц? Какая часть оператора SQL задает условия связывания таблиц?

Что будет, если в запросе указать выборку из двух таблиц, но не связать их?

Что такое рекурсивное связывание?



4. Лабораторная работа № 4. Использование агрегатных функций и специальных операторов в условиях отбора

Цель работы: освоение операторов специального вида и возможностей подведения итогов по данным запроса.

4.1 Операторы специального вида

Под операторами специального вида мы будем понимать использование различных ключевых слов в операторах выборки:

- IN определяет список значений, в который должно входить значение поля. Набор значений для оператора IN заключается в круглые скобки, значения разделяются запятыми. Например, предложение WHERE ADDRESS IN (`Samal', `Tastak') выберет из списка сотрудников, проживающих в микрорайонах Самал и Тастак;

- BETWEEN в отличие от списка допустимых значений BETWEEN, определяет диапазон значений. В запросе необходимо указать слово BETWEEN, затем начальное значение, ключевое слово AND и конечное значение;

- LIKE применим только к символьным полям, с которыми он используется, чтобы находить подстроки. В качестве условия он использует специальные символы: символ подчеркивания _ - замещает любой одиночный символ; знак процента % - замещает последовательность любого числа символов. LIKE удобен при поиске значений - можно использовать ту часть значения, которую помните;

- IS NULL - используется для проверки равенства данного значения значению NULL. Например, WHERE PHONE IS NULL - поиск сотрудников, не имеющих телефонов.

Для всех этих операций можно построить их отрицания (ключевое слово NOT), чтобы рассмотреть противоположные условия. Ключевое слово NOT используется с операциями следующим образом: NOT BETWEEN, IS NOT NULL, NOT IN, NOT LIKE.

4.2 Сортировка вывода

Обычно требуется, чтобы выводимые данные были как-то упорядочены. Выводимые данные можно упорядочить с помощью выражения, связанного с ключевым словом ORDER BY. Упорядочение, задаваемое с помощью ключевого слова ORDER BY, по умолчанию будет упорядочением по возрастанию, обозначается A-Z (А-Я) в случае сортировки имен. Алфавитное упорядочение по убыванию соответствует порядку Z-А (Я-А). Для числовых значений между 1 и 9 упорядочение по возрастанию обозначается 1-9, а по убыванию - 9-1. Синтаксис оператора SELECT, использующего выражение ORDER BY, следующий:

SELECT [ALL | * | DISTINCT СТОЛБЕЦ1, СТОЛБЕЦ2]

FROM ТАБЛИЦА1 [, ТАБЛИЦА2]

WHERE [УСЛОВИЕ1 | ВЫРАЖЕНИЕ1]

[AND УСЛОВИЕ2 | ВЫРАЖЕНИЕ2]

ORDER BY СТОЛБЕЦ1|ЦЕЛОЕ_ЗНАЧЕНИЕ [ASC|DESC];

Ввиду того, что порядок по возрастанию является порядком, принимаемым по умолчанию, нет необходимости указывать ASC вообще.

В SQL предлагаются и некоторые сокращения. Столбец, указанный в списке ключевого слова ORDER BY, можно заменить числом. ЦЕЛОЕ_ЗНАЧЕНИЕ является значением, замещающим действительное имя столбца и соответствующим порядку столбца в списке после ключевого слова SELECT.



4.3 Использование агрегатных функций

Агрегатные функции используются для обобщения данных. SQL Server предоставляет несколько агрегатных функций:

- COUNT производит подсчет строк, удовлетворяющих условию запроса. Синтаксис оператора:

COUNT [(*) ¦(DISTINCT¦ALL)] (имя_столбца)

- SUM вычисляет арифметическую сумму всех значений колонки:

SUM ([DISTINCT] имя_столбца)

- AVG вычисляет среднее арифметическое всех значений:

AVG ([DISTINCT] имя_столбца)

- MAX определяет наибольшее из всех выбранных значений:

MAX([DISTINCT] имя_столбца)

- MIN определяет наименьшее из всех выбранных значений:

MIN ([DISTINCT] имя_столбца)

Функции SUM и AVG применимы только к числовым полям. С функциями COUNT, MAX, MIN могут использоваться числовые или символьные поля. При применении к символьным полям MAX, MIN сравнивают значения в алфавитном порядке. Агрегатные функции при своей работе игнорируют значения NULL. Функция COUNT несколько отличается от остальных. Она подсчитывает число значений в данной колонке или число строк в таблице.

Ключевое слово GROUP BY в условии WHERE позволяет задавать подмножество значений, для которых применяется агрегатная функция и упорядочить результат вывода запроса.

4.4 Задание на лабораторную работу

Прежде, чем приступить к выполнению задания, следует ознакомиться с соответствующими разделами виртуальной обучающей системы SQL_Education.

Выберите для работы базу данных TradeCompany.

Получите список клиентов, адреса которых начинаются на определенную букву.

Подсчитайте количество клиентов фирмы.

Подсчитайте количество наименований товаров.

Получите список счетов, выданных в течение последних трех месяцев.

Получите список клиентов, общая сумма счетов которых превышает указанную сумму.

Получите список клиентов и наименование товаров, приобретенных ими в течение двух последних месяцев.

Подсчитайте общую сумму товара, приобретенного определенными клиентами.

Подсчитайте количество всех счетов, выписанных определенному клиенту.