1. Изучение способов определения отношения между таблицами в реляционных базах данных.
2. Изучение способов установления ограничений на содержимое полей.
3. Элемент нумерованного списка. Изучение операторов манипулирования данными

Рассмотрим базу данных «Продажа цветов», состоящую из трех таблиц: «Цветок», «Продажа», «Продавец»:

Прежде всего, отметим, что таблица «Продажа» является главной. За сведениями о цветке она должна обращаться по внешнему ключу к таблице «Цветок» (поле «Код цветка» таблицы «Продажа» ссылается на одноименное поле таблицы «Цветок»), за сведениями о продавце необходимо по внешнему ключу обращаться к таблице «Продавец» (поле «Код продавца» таблицы «Продажа» ссылается на поле «Код продавца» таблицы «Продавец»). Первичным ключом таблицы «Продажа», очевидно, должно являться поле «Код продажи». Введем обозначения и выберем следующим образом типы полей таблицы «Продажа».

Создадим таблицу «Продажа» средствами среды MS SQL Server Management Studio или при помощи SQL-запроса:

-- создание таблицы «Продажа»
CREATE TABLE Purchase(
    code_of_sale INT PRIMARY KEY NOT NULL, -- первичный ключ
    code_flower INT NOT NULL,              -- код цветка
    date_of_sale SMALLDATETIME NOT NULL,   -- дата продажи
    price_of_sale MONEY NOT NULL,          -- цена
    code_of_seller INT NOT NULL            -- код продавца    
)

В списке таблиц выберите таблицу Purchase, в контекстном меню выберите пункт «Проект». Должно получиться что-то подобное изображенному на рисунке 1.

Рисунок 1

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

USE [books]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Purchase1](
	[code_of_sale] [INT] NOT NULL,
	[code_flower] [INT] NOT NULL,
	[date_of_sale] [smalldatetime] NOT NULL,
	[price_of_sale] [money] NOT NULL,
	[code_of_seller] [INT] NOT NULL,
PRIMARY KEY CLUSTERED 
(	[code_of_sale] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Аналогичным образом создадим таблицы «Цветок» и «Продавец». Выпишем названия полей и типы данных.

Теперь необходимо задать внешние ключи для таблиц. Выбираем в «Обозревателе объектов» таблицу Purchase, в раскрывающемся списке — «Ключи», в контектстном меню — «Создать первичный ключ…» (рисунок 2).

Рисунок 2

Создадим ссылку по внешнему ключу из таблицы Purchase (поле Code_flower) на таблицу Flower (поле Code_flower). Для этого в раскрывающемся окне (рисунок 3) в ячейке «Идентификация (Имя)» записываем FK_Purchase_Flower, затем открываем для редактирования пункт «Идентификация таблиц и столбцов» (после щелчка мышью по этому полю справа появляется маленький квадратик с точками; нажав на него, переходим на окно, изображенное на рисунке 4). В качестве таблицы первичного ключа выбираем Flower, поле code_flower, в качестве таблицы внешнего ключа уже выбрана таблица Purchase, надо также здесь выбрать поле code_flower. Не забываем нажать ОК. Теперь после обновления (возможно, потребуется отключиться и заново поключиться к базе данных) среди ключей таблицы Purchase должен появиться только что созданный FK_Purchase_Flower (рисунок 5). Аналогичным образом создадим ссылку по внешнему ключу из таблицы Purchase (поле Code_seller) на таблицу Seller (поле Code_seller).

Рисунок 3

Рисунок 4

Рисунок 5

После того, как ссылки по внешним ключам созданы, создадим диаграмму для базы данных (в «Обозревателе объектов» выбираем нашу базу данных, ниже «Диаграммы баз данных», и в контекстном меню - «Создать диаграмму базы данных»; в открывшемся окне включаем в диаграмму все таблицы). Должна получиться диаграмма, подобная изображенной на рисунке 6.

Рисунок 6

Создайте такую же базу данных (под другим именем) при помощи SQL-запроса

CREATE DATABASE purchase_flowers
USE purchase_flowers
-- создание таблицы "Продажа"
CREATE TABLE Purchase(
    code_of_sale INT PRIMARY KEY NOT NULL, -- код продажи
    code_flower INT NOT NULL,              -- код цветка
    date_of_sale SMALLDATETIME NOT NULL,   -- дата продажи
    price_of_sale MONEY NOT NULL,          -- цена
    code_of_seller INT NOT NULL            -- код продавца
)
-- создание таблицы "Цветок"
CREATE TABLE Flower(
    code_flower INT PRIMARY KEY NOT NULL, -- код цветка
    name_flower VARCHAR(40) NOT NULL,     -- название цветка
    kind_flower VARCHAR(40),              -- сорт цветка
    mean_height INT NOT NULL,             -- средняя высота
    kind_leaf VARCHAR(40),                -- тип листа
    if_flowering BIT,                     -- цветущий
    additional_inf VARCHAR(40)            -- дополнительная информация
)
-- создание таблицы "Продавец"
CREATE TABLE Seller(
    code_seller INT PRIMARY KEY NOT NULL, -- код продавца
    lastname VARCHAR(40) NOT NULL,        -- фамилия
    firstname VARCHAR(40) NOT NULL,       -- имя
    middlename VARCHAR(40),               -- отчество
    category INT,                         -- разряд
    pay MONEY,                            -- оклад
    startdate SMALLDATETIME               -- дата приема на работу
) 

Зададим ссылки по внешнему ключу при помощи SQL-запроса. Как и ранее, свяжем таблицы Purchase (поле code_flower) с таблицей Flower (поле code_flower). Для этого используем синтаксис команды ALTER TABLE. Синтаксис следующий:

ALTER TABLE <имя таблицы>
ADD CONSTRAINT <имя ограничения> FOREIGN KEY (<имя столбца дочерней таблицы>) 
REFERENCES <имя родительской таблицы> (имя столбца родительской таблицы)

В нашем случае, поскольку мы создаем ссылку из таблицы Purchase, имя этой таблицы должны писать после ALTER TABLE. Имя ограничения можно брать любое, но лучше назвать нашу ссылку по внешнему ключу осмысленно, например, FK_Purchase_Flower. Таблица Purchase в данном случае является дочерней (или, по-другому, таблицей внешнего ключа), столбец, который используется для связи — code_flower. Родительской таблицей (или таблицей первичного ключа), то есть той, на которую ссылаются, является Flower, столбец code_flower. Таким образом, запрос для создания внешней ссылки имеет вид:

ALTER TABLE Purchase
ADD CONSTRAINT FK_Purchase1_Flower FOREIGN KEY (code_flower) REFERENCES Flower (code_flower)

После выполнения запроса найдите созданный внешний ключ в Обозревателе объектов, посмотрите спецификацию таблиц и столбцов, сравните с тем, что ранее было сделано при помощи MS SQL Management Studio. Связь по внешнему ключу для таблиц Purchase и Seller создайте самостоятельно. Создайте диаграмму базы данных. Эта диаграмма должна иметь вид, изображенный на рисунке 6.

Команда ALTER TABLE может быть использована для изменения структуры таблицы — добавления и удаления строк, установления ограничений на вводимые данные. Например, добавим столбец Цвет (Color) в таблицу Flowers:

USE purchase_flowers
ALTER TABLE Flower
ADD color VARCHAR(40) NOT NULL

Удалить созданный столбец можно командой DROP:

USE purchase_flowers
ALTER TABLE Flower
DROP COLUMN color

Часто необходимо создавать ограничения на значения данных. Рассмотрим следующие:

1) ограничение NOT NULL;

2) проверочное ограничение;

3) ограничение уникальности;

4) ограничение первичного ключа;

5) ограничение внешнего ключа (рассмотрено выше).

Ограничение NOT NULL Для создания этого ограничения можно воспользоваться проверкой (CHECK):

USE purchase_flowers
ALTER TABLE Flower
ADD CONSTRAINT add_nnull CHECK (if_flowering IS NOT NULL)

Задание: Проверьте в таблицах БД, созданной в ходе лабораторной работы №1, ограничения NOT NULL в соответствии с предложенной структурой данных (см. графу «Возможность содержать NULL»). При необходимости добавьте указанное ограничение.

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

ALTER TABLE <имя таблицы> 
ADD CONSTRAINT <имя ограничения> CHECK (<условие проверки>)

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

USE purchase_flowers
ALTER TABLE Flower
ADD CONSTRAINT leaf_det CHECK (kind_leaf IN ('простой', 'сложный'))

Если таблица не создана, то оператор CONSTRAINT указывается в конструкции CREATE TABLE после перечисления всех столбцов создаваемой таблицы:

CREATE TABLE Flower(
    code_flower INT PRIMARY KEY NOT NULL, -- код цветка
    name_flower VARCHAR(40) NOT NULL,     -- название цветка
    kind_flower VARCHAR(40),              -- сорт цветка
    mean_height INT NOT NULL,             -- средняя высота
    kind_leaf VARCHAR(40),                -- тип листа
    if_flowering BIT,                     -- цветущий
    additional_inf VARCHAR(40),                         -- дополнительная информация
    CONSTRAINT leaf_det CHECK (kind_leaf IN ('простой', 'сложный'))
)

Чтобы удалить ограничение, используйте конструкцию:

ALTER TABLE Flower DROP leaf_det

Задание: добавьте в таблицы БД, созданной в ходе лабораторной работы №1, проверочные ограничения:

1. Таблица Course

 (YearEntry ≤ YearFinal) 

2. Таблица People

 (Male IN (0, 1)) 

3. Таблица SemesterResults

 ((Semester > 0) AND (Semester < 13)) 

.

Ограничение уникальности гарантирует, что значения в наборе столбцов уникальны и непусты для всех строк в таблице. Пример: обычное ограничение уникальности в таблице Student может гарантировать, что номер студенческого билета будет уникальным и непустым в рамках всей таблицы. Синтаксис:

ALTER TABLE <имя таблицы> 
ADD CONSTRAINT <имя ограничения> UNIQUE (<имя столбца1>, <имя столбца2>, …) 

Например:

ALTER TABLE Flower 
ADD CONSTRAINT name_flower_unique UNIQUE (name_flower) 

Задание: добавьте в таблицы БД, созданной в ходе лабораторной работы №1, ограничения уникальности значений поля (полей):

1. Таблица Course (поля YearEntry, Speciality)

2. Таблица Mark (поле Value)

3. Таблица Student (поле StudNum)

4. Таблица SemesterResults (поля Student, Semester, Discipline).

Добавление записей в таблицу (INSERT). Упрощенный синтаксис:

INSERT INTO <имя таблицы> (<имя столбца1>, <имя столбца2>, …) 
VALUES (<значение столбца1>,<значение столбца2>, …)  

Например: Мы хотим добавить автора в таблицу Authors. Для этого:

INSERT INTO Authors(Code_author, name_author, Birthday)
VALUES (26, 'Джек Лондон', '12.01.1876');

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

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

DELETE FROM <имя таблицы> 

Обычно нужно удалить только некоторые определенные строки из таблицы. Чтобы определить какие строки будут удалены, следует использовать предложение WHERE. Синтаксис:

DELETE FROM <имя таблицы> 
WHERE <критерий поиска строк> 

Удалим созданную ранее запись:

DELETE FROM Authors
WHERE (Code_author = 26);

Задание: Удалите из таблицы несколько строк с помощью оператора DELETE. Проверьте получение ожидаемого результата.

Изменение значений полей (UPDATE) Синтаксис для изменения значений определенных полей всех записей таблицы:

UPDATE <имя таблицы>  
SET (<имя столбца1>, <имя столбца2>, …) =  
        (<новое значение столбца1>, <новое значение столбца2>, …) 

Пример:

UPDATE Authors
SET name_author = 'Толстой';

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

UPDATE Authors
SET name_author = 'Гоголь'
WHERE Code_author = 23;

1. Как определить отношения между таблицами в реляционных базах данных?
2. Какие существуют ограничения на содержимое полей?
3. Какие существуют операторы манипулирования данными?
4. Чем отличаются типы Smalldatetime от smallint, char от varchar?
5. Для чего нужна диаграмма базы данных?
6. Как редактировать таблицы с помощью SQL- запросов?
7. Как задать ссылки по внешнему ключу при помощи SQL-запроса?
8. Какие существуют ограничения на значения данных? Опишите их.

Вариант 1. БД «Учет выданных подарков несовершеннолетним детям сотрудников предприятия»

Вариант 2. БД «Учет выполненных ремонтных работ»

Вариант 3. БД «Продажа цветов»

Вариант 4. БД «Поступление лекарственных средств»

Вариант 5. БД «Списание оборудования»

Вариант 6. БД «Поваренная книга»

Вариант 7. БД «Регистрация входящей документации»

Вариант 8. БД «Увольнение сотрудника»

Вариант 9. БД «Приказ на отпуск»

Вариант 10. БД «Регистрация выходящей документации»

Вариант 11. БД «Назначение на должность»

Вариант 12. БД «Выдача оборудования в прокат»

Вариант 13. БД «Списание оборудования из проката»

Вариант 14. БД «Прием цветов в магазин»

Вариант 15. БД «Регистрация клиентов гостиницы»

Вариант 16. БД «Возврат оборудования в службу проката»

Вариант 17. БД «Учет материальных ценностей на предприятии»

Вариант 18. БД «Состав ремонтных работ»

Вариант 19. БД «Продажа лекарственных средств»

Вариант 20. БД «Учет исполнения по входящей документации»

Назад: Лабораторная работа № 2