NO:User
READ:@ALL
EDIT:@ALL
====== Лабораторная работа № 2 ======
====== Редактирование таблиц ======
====== Цели: ======
- Изучение способов определения отношения между таблицами в реляционных базах данных.
- Изучение способов установления ограничений на содержимое полей.
- Элемент нумерованного списка. Изучение операторов манипулирования данными
====== 1) Задание внешних ключей в среде Microsoft SQL Server Management Studio. ======
Рассмотрим базу данных «Продажа цветов», состоящую из трех таблиц: «Цветок», «Продажа», «Продавец»:
^ Цветок ^ Продажа ^ Продавец ^
| Код цветка | Код цветка | Код продавца |
| Название цветка | Дата продажи | Фамилия |
|Сорт цветка | Цена продажи | Имя |
|Средняя высота | Код продавца | Отчество |
|Тип листа | Код продажи | Разряд |
|Цветущий | | Оклад |
|Дополнительные сведения | | Дата приема на работу |
Прежде всего, отметим, что таблица «Продажа» является главной. За сведениями о цветке она должна обращаться по внешнему ключу к таблице «Цветок» (поле «Код цветка» таблицы «Продажа» ссылается на одноименное поле таблицы «Цветок»), за сведениями о продавце необходимо по внешнему ключу обращаться к таблице «Продавец» (поле «Код продавца» таблицы «Продажа» ссылается на поле «Код продавца» таблицы «Продавец»). Первичным ключом таблицы «Продажа», очевидно, должно являться поле «Код продажи». Введем обозначения и выберем следующим образом типы полей таблицы «Продажа».
^ Продажа (Purchase) ^^^^
| Код продажи | code_of_sale | int | Первичный ключ |
| Код цветка | code_of_flower | int | Ссылка на Flower(code_flower) |
| Дата продажи | date_of_sale | smalldatetime |
|Цена продажи | price_of_sale | money |
|Код продавца | code_of_seller | int | Ссылка на Seller(code_seller) |
Создадим таблицу «Продажа» средствами среды 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.
{{ :users:drmastemah:my_project:Lab2_1.png |Рисунок 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]
Аналогичным образом создадим таблицы «Цветок» и «Продавец». Выпишем названия полей и типы данных.
^ Цветок(Flower) ^^^^
| Код цветка | code_flower | int | Первичный ключ |
| Название цветка| 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) | |
^ Продавец(Seller) ^^^^
| Фамилия | lastname | varchar(40) | NOT NULL |
| Имя | firstnam | varchar(40) | NOT NULL |
| Отчество | middlename | varchar(40) | |
| Разряд | category | int | |
| Оклад | pay | money | |
| Дата прием на работу | startdate | smalldatetime | |
Теперь необходимо задать внешние ключи для таблиц. Выбираем в «Обозревателе объектов» таблицу Purchase, в раскрывающемся списке — «Ключи», в контектстном меню — «Создать первичный ключ...» (рисунок 2).
{{ :users:drmastemah:my_project:Lab2_2.png |Рисунок 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).
{{ :users:drmastemah:my_project:Lab2_3.png |Рисунок 3}}
Рисунок 3
{{ :users:drmastemah:my_project:Lab2_4.png |Рисунок 4}}
Рисунок 4
{{ :users:drmastemah:my_project:Lab2_5.png |Рисунок 5}}
Рисунок 5
После того, как ссылки по внешним ключам созданы, создадим диаграмму для базы данных (в «Обозревателе объектов» выбираем нашу базу данных, ниже «Диаграммы баз данных», и в контекстном меню - «Создать диаграмму базы данных»; в открывшемся окне включаем в диаграмму все таблицы). Должна получиться диаграмма, подобная изображенной на рисунке 6.
{{ :users:drmastemah:my_project:Lab2_6.png |Рисунок 6}}
Рисунок 6
====== 2. Редактирование таблицы при помощи SQL-запросов. ======
Создайте такую же базу данных (под другим именем) при помощи 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).
====== 3. Операторы манипулирования данными (DML) ======
Добавление записей в таблицу (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;
====== Контрольные вопросы ======
- Как определить отношения между таблицами в реляционных базах данных?
- Какие существуют ограничения на содержимое полей?
- Какие существуют операторы манипулирования данными?
- Чем отличаются типы Smalldatetime от smallint, char от varchar?
- Для чего нужна диаграмма базы данных?
- Как редактировать таблицы с помощью SQL- запросов?
- Как задать ссылки по внешнему ключу при помощи SQL-запроса?
- Какие существуют ограничения на значения данных? Опишите их.
====== Приложение ======
Вариант 1. БД «Учет выданных подарков несовершеннолетним детям сотрудников предприятия»
^ Код сотрудника ^ Код сотрудника ^ Код ребенка ^
| Фамилия | Имя ребенка | Стоимость подарка |
| Имя | Дата рождения | Дата выдачи подарка |
| Отчество | Код ребенка | Код выдачи |
| Должность | | |
| Подразделение | | |
| Дата приема на работу | | |
Вариант 2. БД «Учет выполненных ремонтных работ»
^ Код прибора в ремонте ^ Код прибора ^ Код мастера ^
| Название прибора | Код мастера | Фамилия мастера |
| Тип прибора | ФИО владельца прибора | Имя мастера |
| Дата производства | Дата приема в ремонт | Отчество мастера |
| | Вид поломки | Разряд мастера |
| | Стоимость ремонта | Дата приема на работу |
| | код ремонта | |
Вариант 3. БД «Продажа цветов»
^ Код цветка ^ Код цветка ^ Код продавца ^
| Название цветка | Дата продажи | Фамилия |
| Сорт цветка | Цена продажи | Имя |
| Средняя высота | Код продавца | Отчество |
| Тип листа | Код продажи | Разряд |
| Цветущий | | Оклад |
| Дополнительные сведения | | Дата приема на работу |
Вариант 4. БД «Поступление лекарственных средств»
^ Код лекарства ^ Код лекарства ^ Код поставщика ^
| Название лекарства | Код поставщика | Сокращенное название |
| Показания к применению | Дата поставки | Полное название |
| Единица измерения | Цена за единицу | Юридический адрес |
| Количество в упаковке | Количество | Телефон |
| Название производителя | Код поступления | ФИО руководителя |
Вариант 5. БД «Списание оборудования»
^ Код оборудования ^ Код оборудования ^ Код сотрудника ^
| Название оборудования | Причина списания | Фамилия |
| Тип оборудования | Дата списания | Имя |
| Дата поступления | Код сотрудника | Отчество |
| ФИО ответственного | Код списания | Должность |
| Место установки | | Подразделение |
| | | Дата приема на работу |
Вариант 6. БД «Поваренная книга»
^ Код блюда ^ Код блюда ^ Код продукта ^
| Тип блюда | Код продукта | Название продукта |
| Вес блюда | Объем продукта | Ед измерения |
| Порядок приготовления | | |
| Количество калорий | | |
| Количество углеводов | | |
Вариант 7. БД «Регистрация входящей документации»
^ Код регистратора ^ Код документа ^ Код организации-отправителя ^
| Фамилия | Номер документа | Сокращенное название |
| Имя | Дата регистрации | Полное название |
| Отчество | Краткое содержание документа | Юридический адрес |
| Должность | Тип документа | Телефон |
| Дата приема на работу | Код организации-отправителя | ФИО руководителя |
| | Код регистратора | |
Вариант 8. БД «Увольнение сотрудника»
^ Код сотрудника ^ Код документа ^ Код статьи увольнения ^
| Фамилия | Номер документа | Название статьи увольнения |
| Имя | Дата регистрации | Причина увольнения |
| Отчество | Дата увольнения | Номер статьи увольнения |
| Должность | Код статьи увольнения | Номер пункта/подпункта увольнения |
| Подразделение | Код сотрудника | |
| Дата приема на работу | Денежная компенсация | |
Вариант 9. БД «Приказ на отпуск»
^ Код сотрудника ^ Код документа ^ Код отпуска ^
| Фамилия | Номер документа | Тип отпуска |
| Имя | Дата регистрации | Оплата отпуска |
| Отчество | Дата начала отпуска | Льготы по опуску |
| Должность | Дата окончания отпуска | |
| Подразделение | Код сотрудника | |
| Дата приема на работу | Код отпуска | |
Вариант 10. БД «Регистрация выходящей документации»
^ Код отправителя ^ Код документа ^ Код организации-получателя ^
| Фамилия | Номер документа | Сокращенное название |
| Имя | Дата регистрации | Полное название |
| Отчество | Краткое содержание документа | Юридический адрес |
| Должность | Тип документа | Телефон |
| Дата приема на работу | Код организации-получателя | ФИО руководителя |
| | Код отправителя | |
Вариант 11. БД «Назначение на должность»
^ Код сотрудника ^ Код документа ^ Код должности ^
| Фамилия | Номер документа | Название должности |
| Имя | Дата регистрации | Льготы по должности |
| Отчество | Дата назначения | Требования к квалификации |
| Дата приема на работу | Код сотрудника | |
| Дата рождения | Код должности | |
| Пол | | |
Вариант 12. БД «Выдача оборудования в прокат»
^ Код клиента ^ Код выдачи ^ Код оборудования ^
| Фамилия | Номер документа | Название оборудования |
| Имя | Дата начала проката | Тип оборудования |
| Отчество | Дата окончания проката | Дата поступления в прокат |
| Адрес | Код оборудования | |
| Телефон | Код клиента | |
| Серия и номер паспорта | Стоимость | |
Вариант 13. БД «Списание оборудования из проката»
^ Код оборудования ^ Код оборудования ^ Код сотрудника ^
| Название оборудования | Причина списания | Фамилия |
| Тип оборудования | Дата списания | Имя |
| Дата поступления в прокат | Код сотрудника | Отчество |
| | Номер документа | Должность |
| | Дата регистрации | Дата приема на работу |
| | Код списания | |
Вариант 14. БД «Прием цветов в магазин»
^ Код цветка ^ Код цветка ^ Код поставщика ^
| Название цветка | Дата поступления | Сокращенное название |
| Сорт цветка | Цена за единицу | Полное название |
| Средняя высота | Код поставщика | Юридический адрес |
| Тип листа | Код поступления | Телефон |
| Цветущий | Количество | ФИО руководителя |
| Дополнительные сведения | | |
Вариант 15. БД «Регистрация клиентов гостиницы»
^Код номера ^ Код регистрации ^ Код клиента ^
|Тип номера | Код номера | Фамилия |
| Перечень удобств | Дата заезда | Имя |
| Цена за сутки | Дата выезда | Отчество |
| | Стоимость | Адрес |
| | Код клиента | Телефон |
| | | Серия и номер паспорта |
Вариант 16. БД «Возврат оборудования в службу проката»
^ Код клиента ^ Код возврата ^ Код оборудования ^
| Фамилия | Номер документа | Название оборудования |
| Имя | Дата возврата | Тип оборудования |
| Отчество | Состояние оборудования | Дата поступления в прокат |
| Адрес | Код оборудования | |
| Телефон | Код клиента | |
| Серия и номер паспорта | Штраф | |
Вариант 17. БД «Учет материальных ценностей на предприятии»
^ Код ценности ^ Код постановки на учет ^ Код материально ответственного ^
| Название ценности | Код ценности | Фамилия |
| Тип ценности | Код материально ответственного | Имя |
| Закупочная стоимость | Дата постановки на учет | Отчество |
| Срок гарантии | Место нахождения ценности | Должность |
| Дата начала гарантии | | Дата приема на работу |
| | | Подразделение |
Вариант 18. БД «Состав ремонтных работ»
^ Код ремонтной работы ^ Код ремонтной работы ^ Код мастера ^
| Код этапа работы | Код мастера | Фамилия мастера |
| Название этапа работы | Стоимость ремонта | Имя мастера |
| Стоимость этапа | Количество дней ремонта | Отчество мастера |
| | Название ремонтной работы | Разряд мастера |
| | | Дата приема на работу |
Вариант 19. БД «Продажа лекарственных средств»
^ Код лекарства ^ Номер чека ^ Номер чека ^
| Название лекарства | Цена за единицу | Дата продажи |
| Показания к применению | Количество | Сумма |
| Единица измерения | Код лекарства | ФИО кассира |
| Количество в упаковке | Код записи в чеках | |
| Название производителя | | |
Вариант 20. БД «Учет исполнения по входящей документации»
^ Код исполнителя ^ Код документа ^ Код документа ^
| Фамилия | Дата назначения на исполнения | Номер документа |
| Имя | Срок выполнения в днях | Дата регистрации |
| Отчество | Тип результата | Краткое содержание документа |
| Должность | Код исполнителя | Тип документа |
| Подразделение | Факт исполнения | Организация-отправитель |
| Дата приема на работу | | Код исполнителя |
\
Назад: [[users:drmastemah:my_project:index]]
{{tag>}}