Управление и автоматизация БД Практическая работа №9

Практическое занятие № 9

Создание диаграмм базы данных. Вставка, удаление и обновление данных

Цель работы: получение практических навыков создания и работы с диаграммами в SSMS. Получение практических навыков вставки, удаления и обновления данных

Краткие теоретические основания выполнения задания Создание БД помощью DDL-скрипта

Создать базу данных также можно с помощью DDL-скрипта. Для создания БД с помощью DDL-

скрипта необходимо выбрать пункт меню Файл Создать Запрос в текущем соединении, либо нажать кнопку Создать запрос (New Query) на панели инструментов. В рабочей области менеджера появиться пустое окно запроса, в которое необходимо вставить DDL-скрипт создания БД.

Задание 1

1. Создайте базу данных bookshop2, являющуюся копией базы данных bookshop, с помощью DDL- скрипта.

CREATE DATABASE bookshop2; GO

USE bookshop2;

CREATE TABLE catalogs (

cat_ID INT PRIMARY KEY IDENTITY, cat_name NVARCHAR (50) NOT NULL,

);

CREATE TABLE books (

book_ID INT NOT NULL IDENTITY, b_name NVARCHAR(200) NOT NULL, b_author NVARCHAR(200) NOT NULL,

b_year INT NOT NULL,

b_price DECIMAL(7,2) NULL default '0.00', b_count INT NULL DEFAULT '0',

b_cat_ID INT NOT NULL DEFAULT '0', PRIMARY KEY (book_ID),

FOREIGN KEY (b_cat_ID) REFERENCES catalogs (cat_ID) ON UPDATE CASCADE ON DELETE CASCADE

);

CREATE TABLE users (

user_ID INT NOT NULL IDENTITY, u_name NVARCHAR(50) NOT NULL,

u_patronymic NVARCHAR(50) NOT NULL, u_surname NVARCHAR(50) NOT NULL, u_phone NVARCHAR(12) NULL,

u_email NVARCHAR(50) NULL,

u_status NVARCHAR(10) CHECK (u_status='active'or u_status='passive' or u_status='lock' or u_status='gold') DEFAULT 'passive',

PRIMARY KEY (user_ID)

);

CREATE TABLE orders (

order_ID INT NOT NULL IDENTITY,

o_user_ID INT NOT NULL, o_book_ID INT NOT NULL,

o_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', o_number INT NOT NULL DEFAULT '0',

PRIMARY KEY (order_ID),

FOREIGN KEY (o_book_ID) REFERENCES books(book_ID) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (o_user_ID) REFERENCES users(user_ID) ON DELETE CASCADE ON UPDATE CASCADE

);

1. Обновите окно программы и убедитесь, что база bookshop2 существует и аналогична базе bookshop.

2. Удалите базу bookshop, все действия не забывайте заносить в отчёт.

Создание диаграммы

Для создания диаграммы необходимо в обозревателе объектов БД подвести указатель мыши на узел «Диаграммы баз данных», вызвать контекстное меню и в нем выбрать пункт «Создать диаграмму базы данных».

Рис. 1. Контекстное меню диаграмм баз данных

Далее необходимо поместить нужные таблицы на диаграмму и нажать кнопку Закрыть.

В результате получим диаграмму базы дынных bookshop.

Изменить степень детализации отображаемой информации по таблицам можно через контекстное меню, вызываемое при нажатии правой кнопки мыши на любой таблице.

Задание 2

1. Создайте диаграмму базы данных bookshop.

2. Измените степень детализации таблицы books на "стандартное".

Редактирование структуры БД и диаграмм

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

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

Изменение параметров столбцов. При выборе на диаграмме столбца таблицы в окне свойств объектов появится следующий список параметров.

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

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

Создание связи между таблицами. Для задания связи между таблицами следует выполнить следующую последовательность действий.

1. Выделить дочернюю таблицу.

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

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

1. Нажатие на кнопку «OK» приведет к появлению следующего окна, в котором задаются дополнительные параметры связи.

В частности, в данном окне можно задать триггеры. Триггеры со стороны родительской таблицы указываются в разделе «Спецификация INSERT и UPDATE»

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

Задание 3

1. Удалите в редакторе диаграмм таблицу catalogs.

2. Сохраните изменения в диаграмме. Убедитесь, что таблица catalogs осталась в базе данных.

3. Удалите таблицу catalogs из базы данных, используя контекстное меню

1. Сохраните изменения, подтвердив удаление таблицы из базы данных.

2. Обновите список таблиц в обозревателе и убедитесь, что таблица удалена.

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

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

целостности.

Генерация DDL-скрипта для отдельной таблицы

Для генерации DDL-скрипта таблицы необходимо в обозревателе объектов БД подвести указатель мыши на узел с наименованием таблицы, вызвать контекстное меню и в нем выбрать пункт Создать скрипт для таблицы Используя CREATE Новое окно редактора.

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

USE [bookshop] GO

/****** Object: Table [dbo].[catalogs] Script Date: 11.12.2020 14:12:59 ******/ SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[catalogs](

[cat_ID] [int] IDENTITY(1,1) NOT NULL,

[cat_name] [nvarchar](50) NOT NULL, CONSTRAINT [PK_catalogs] PRIMARY KEY CLUSTERED (

[cat_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] GO

Задание 4

1. Создайте DDL-скрипта для таблицы catalogs.

Добавление данных. Команда Insert

Для добавления данных применяется команда INSERT, которая имеет следующий формальный синтаксис:

INSERT [INTO] имя_таблицы [(список_столбцов)] VALUES (значение1, значение2, ... значениеN)

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

Например, пусть ранее была создана следующая база данных:

CREATE DATABASE productsdb; GO

USE productsdb; CREATE TABLE Products (

Id INT IDENTITY PRIMARY KEY,

ProductName NVARCHAR(30) NOT NULL,

Manufacturer NVARCHAR(20) NOT NULL,

ProductCount INT DEFAULT 0, Price MONEY NOT NULL

)

Добавим в нее одну строку с помощью команды INSERT:

INSERT Products VALUES ('iPhone 7', 'Apple', 5, 52000)

После удачного выполнения в SQL Server Management Studio в поле сообщений должно появиться сообщение "1 row(s) affected":

Стоит учитывать, что значения для столбцов в скобках после ключевого слова VALUES передаются по порядку их объявления. Например, в выражении CREATE TABLE выше можно увидеть, что первым столбцом идет Id. Но так как для него задан атрибут IDENTITY, то значение этого столбца автоматически генерируется, и его можно не указывать. Второй столбец представляет ProductName, поэтому первое значение - строка "iPhone 7" будет передано именно этому столбцу.

Второе значение - строка "Apple" будет передана третьему столбцу Manufacturer и так далее. То есть значения передаются столбцам следующим образом:

• ProductName: 'iPhone 7'

• Manufacturer: 'Apple'

• ProductCount: 5

• Price: 52000

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

INSERT INTO Products (ProductName, Price, Manufacturer) VALUES ('iPhone 6S', 41000, 'Apple')

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

• ProductName: 'iPhone 6S'

• Manufacturer: 'Apple'

• Price: 41000

Для неуказанных столбцов (в данном случае ProductCount) будет добавляться значение по умолчанию, если задан атрибут DEFAULT, или значение NULL. При этом неуказанные столбцы должны допускать значение NULL или иметь атрибут DEFAULT.

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

INSERT INTO Products

VALUES

('iPhone 6', 'Apple', 3, 36000),

('Galaxy S8', 'Samsung', 2, 46000),

('Galaxy S8 Plus', 'Samsung', 1, 56000)

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

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

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ('Mi6', 'Xiaomi', DEFAULT, 28000)

В данном случае для столбца ProductCount будет использовано значение по умолчанию (если оно установлено, если его нет - то NULL).

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

INSERT INTO Products

DEFAULT VALUES

Но если брать таблицу Products, то подобная команда завершится с ошибкой, так как несколько полей не имеют атрибута DEFAULT и при этом не допускают значение NULL.

Обновление данных. Команда UPDATE

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

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

SET столбец1 = значение1, столбец2 = значение2, ... столбецN = значениеN

[FROM выборка AS псевдоним_выборки] [WHERE условие_обновления]

Например, увеличим у всех товаров цену на 5000:

UPDATE Products

SET Price = Price + 5000

Используем критерий, и изменим название производителя с "Samsung" на "Samsung Inc.":

UPDATE Products

SET Manufacturer = 'Samsung Inc.'

WHERE Manufacturer = 'Samsung'

Более сложный запрос - заменим у поля Manufacturer значение "Apple" на "Apple Inc." в первых 2 строках:

UPDATE Products

SET Manufacturer = 'Apple Inc.'

FROM

(SELECT TOP 2 FROM Products WHERE Manufacturer='Apple') AS Selected

WHERE Products.Id = Selected.Id

С помощью подзапроса после ключевого слова FROM производится выборка первых двух строк, в которых Manufacturer='Apple'. Для этой выборки будет определен псевдоним Selected. Псевдоним указывается после оператора AS.

Далее идет условие обновления Products.Id = Selected.Id. То есть фактически мы имеем дело с двумя таблицами - Products и Selected (которая является производной от Products). В Selected находится две первых строки, в которых Manufacturer='Apple'. В Products - вообще все строки. И обновление производится только для тех строк, которые есть в выборке Selected. То есть если в таблице Products десятки товаров с производителем Apple, то обновление коснется только двух первых из них.

Удаление данных. Команда DELETE

Для удаления применяется команда DELETE:

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

WHERE условие_удаления

Например, удалим строки, у которых id равен 9:

DELETE Products

WHERE Id=9

Или удалим все товары, производителем которых является Xiaomi и которые имеют цену меньше 15000:

DELETE Products

WHERE Manufacturer='Xiaomi' AND Price 15000

Более сложный пример - удалим первые два товара, у которых производитель - Apple:

DELETE Products FROM

(SELECT TOP 2 * FROM Products

WHERE Manufacturer='Apple]') AS Selected

WHERE Products.Id = Selected.Id

После первого оператора FROM идет выборка двух строк из таблицы Products. Этой выборке

назначается псевдоним Selected с помощью оператора AS. Далее устанавливаем условие, что если Id в таблице Products имеет то же значение, что и Id в выборке Selected, то строка удаляется.

Если необходимо вовсе удалить все строки вне зависимости от условия, то условие можно не указывать:

DELETE Products

Оборудование, материалы

Персональный компьютер с установленной ОС Windows. Браузер с доступом в интернет. MS SQL Server. SQL Server Management Studio.

Порядок выполнения задания

1. Ознакомьтесь с краткими теоретическими основаниями выполнения задания.

2. Выполните задания в тексте.

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

1. Заполните следующими данными таблицу catalogs

cat_ID	cat_name
1	Программирование
2	Интернет
3	Базы данных
4	Сети
5	Мультимедиа

Для этого в контекстном меню таблицы в обозревателе объектов выберите Изменить первые 200 строк.

И заполните данные. Столбец cat_ID заполнять не нужно, поскольку он идентификатор.

1. Заполните следующими данными таблицу users

user_ID	u_name	u_patronymic	u_surname	u_phone	u_email	u_status
1	Александр	Валерьевич	Иванов	58-98-78	[email protected]	active
2	Сергей	Иванович	Лосев	90-57-77	[email protected]	passive
3	Игорь	Николаевич	Симонов	95-66-61	[email protected]	active
4	Максим	Петрович	Кузнецов	NULL	[email protected]	active
5	Анатолий	Юрьевич	Петров	NULL	NULL	lock
6	Александр	Александрович	Корнеев	89-78-36	[email protected]	gold

Для этого используйте следующий скрипт:

INSERT INTO users VALUES ('Александр','Валерьевич','Иванов','58-98-78', '[email protected]', 'active'),

('Сергей','Иванович','Лосев','90-57-77', '[email protected]', 'passive'),

('Игорь','Николаевич','Симонов','95-66-61', '[email protected]', 'active'), ('Максим','Петрович','Кузнецов',NULL, '[email protected]', 'active'), ('Анатолий','Юрьевич','Петров', NULL, NULL, 'lock'), ('Александр','Александрович','Корнеев','89-78-36', '[email protected]', 'gold')

Убедитесь, что таблица заполнена корректными значениями.

1. Заполните следующими данными таблицу books

   book_ID	b_name	b_author	b_year	b_price	b_count	b_cat_ID
   1	JavaScript в кармане	Рева О.Н.	2008	42.00	10	1
   2	Visual FoxPro 9.0	Клепинин В.Б.	2007	660.00	2	1
   3	C++ Как он есть	Тимофеев В.В.	2009	218.00	4	1
   4	Создание приложений с помощью C#	Фаронов В.В.	2008	169.00	1	1
   5	Delphi. Народные советы	Шкрыль А.А.	2007	243.00	6	1
   6	Delphi. Полное руководство	Сухарев М.	2008	500.00	6	1
   7	Профессиональное программирование на PHP	Шлосснейгл Дж.	2006	309.00	5	1
   8	Совершенный код	Макконнелл С.	2007	771.00	1	1
   9	Практика программирования	Керниган Б.	2004	214.00	12	1
   10	Принципы маршрутизации в Internet	Хелеби С.	2001	428.00	4	2
   11	Поиск в Internet	Гусев В.С.	2004	107.00	2	2
   12	Web-конструирование	Дуванов А.А.	2003	177.00	6	2
   13	Самоучитель Интернет	Константинов Ю.П.	2009	121.00	4	2
   14	Популярные интернет- браузеры	Маринин С.А.	2007	82.00	6	2
   15	Общение в Интернете	Экслер А.	2006	85.00	5	2
   16	Базы данных	Малыхина М.П.	2006	326.00	2	3
   17	Базы данных. Разработка приложений	Рудикова Л.В.	2006	189.00	6	3
   18	Раскрытие тайн SQL	Оппель Э.	2007	200.00	3	3
   19	Практикум по Access	Золотова С.И.	2007	87.00	6	3
   20	Компьютерные сети	Танненбаум Э.	2007	630.00	6	4
   21	Сети. Поиск неисправностей	Бигелоу С.	2005	434.00	4	4
   22	Безопасность сетей	Брегг Р.	2006	462.00	5	4
   23	Анализ и диагностика компьютерных сетей	Хогдал Дж.	2001	344.00	3	4
   24	Локальные вычислительные сети	Епанешников А.	2005	82.00	8	4
   25	Цифровая фотография	Надеждин Н.	2004	149.00	20	5
   26	Музыкальный компьютер для гитариста	Петелин Р.Ю.	2004	217.00	15	5
   27	Видео на ПК	Федорова А.	2003	231.00	10	5
   28	Мультипликация во Flash	Киркпатрик Г.	2006	211.00	20	5
   29	Запись CD и DVD	Гультяев А.К.	2003	167.00	12	5
   30	Запись и обработка звука на компьютере	Лоянич А.А.	2008	51.00	8	5

2. Заполните следующими данными таблицу orders

   order_ID	o_user_ID	o_book_ID	o_time	o_number
   1	3	8	2009-04-01 10:39:38	1
   2	6	10	2009-10-02 09:40:29	2
   3	1	20	2009-18-02 13:41:05	4
   4	4	20	2009-10-03 18:20:00	1
   5	3	20	2009-17-03 19:15:36	1

3. Выполните отчёт о проделанной работе.

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

1. Что такое диаграмма базы данных и для чего она используется?

2. Как добавить таблицы на диаграмму базы данных?