close

Вход

Забыли?

вход по аккаунту

?

Akochin lab2

код для вставкиСкачать
Нижегородский Государственный Технический Университет
им. Р. Е. Алексеева
Кафедра ВСТ
Отчет по лабораторной работе №2
Базы Данных
Выполнил:
Кочин А.О
Проверил:
Супруненко А.В.
Нижний Новгород
2012 г.
Данная лабораторная работа будет выполняться с помощью MySQL 5.5 Command Line Client, проверяться будет в графической оболочке HeidiSQL 7.0 и MySQL 5.5 Command Line Client.
1. Создаём новую базу данных MySQL, выполнив запрос:
CREATE DATABASE akochin_2; Результат запроса:
2,3 Выбираем созданную базу и при помощи оператора CREATE TABLE создадим таблицы, используя архитектуру InnoDB: * computers - компьютеры o id - идентификатор компьютера, первичный ключ; o netBiosName, ip - сетевое имя и ip-адрес соответственно; o cpu, ram, hdd, odd, displayScreenSize - частота процессора (MHz), объѐм ОЗУ (MB), объѐм HDD (GB), наличие оптического дисковода, диагональ монитора (in). * soft - программное обеспечение o id - идентификатор приложения, первичный ключ; o author - производитель ПО; o name - название приложения. * computerSoft - связка o compId - идентификатор компьютера; o softId - идентификатор приложения; o version - версия приложения. Используемые типы данных:
* SMALLINT - Малое целое число. Диапазон со знаком от -32768 до 32767. Диапазон без знака от 0 до 65535. * VARCHAR - cтрока переменной длины
* ENUM - Перечисляемый тип данных
* TINYINT - Очень малое целое число. Диапазон со знаком от -128 до 127. Диапазон без знака от 0 до 255. Для создания таблиц мною был создан SQL скрипт-файл, код которого приведён ниже:
createTables.sql
CREATE TABLE `computers` (
`id` SMALLINT UNSIGNED,
`netBiosName` VARCHAR(45),
`ip` VARCHAR(15),
`cpu` SMALLINT UNSIGNED,
`ram` SMALLINT UNSIGNED,
`hdd` SMALLINT UNSIGNED,
`odd` ENUM('yes','no'),
`displayScreenSize` TINYINT
)
COLLATE='utf8_general_ci'
ENGINE=InnoDb;
CREATE TABLE `soft` (
`id` SMALLINT UNSIGNED,
`author` VARCHAR(45),
`name` VARCHAR(45)
)
COLLATE='utf8_general_ci'
ENGINE=InnoBD;
CREATE TABLE `computerSoft` (
`compId` SMALLINT,
`softId` SMALLINT,
`version` VARCHAR(10)
)
COLLATE='utf8_general_ci'
ENGINE=InnoBD;
Просмотрим результат с помощью командной строки и графической оболочки:
Таблица computers
Таблица soft:
Таблица computersoft:
4. Заполним таблицы данными. Заполним таблицы разными способами: таблицу computers с использование текстового файла (команда LOAD DATA), таблицы soft и computersoft с добавлением одиночных записей(команда INSERT).
Создадим текстовый файл с именем `fillComputers.txt', содержащий по одной записи в каждой строке (значения столбцов разделим символами табуляции и расположим в том порядке, который был определен в команде CREATE TABLE). Просмотрим результат в HeidiSQL:
Таблицы soft и computersoft заполним, используя SQL скрипт-файл, содержащий команды INSERT:
fillOtherTables.sql
INSERT INTO soft VALUES
(0, "OPNET Technologies, Inc.", "OPNET IT Guru"),
(1, "Adobe Systems Incorporated", "Adobe Reader"),
(2, "Mozilla Public License owners", "Mozilla Firefox");
INSERT INTO computersoft VALUES
(0, 0, "9.1"),
(1, 1, "10.1.1.33"),
(2, 2, "8.7");
5. Обеспечим ссылочную целостность данных при помощи триггеров. Проверим вставкой, изменением и удалением связанных данных. Триггеры устанавливаются посредством запуска SQL скрипт-файла:
setUpTriggers.sql
CREATE TRIGGER deleteFromComp
AFTER
DELETE
ON computers
FOR EACH ROW
DELETE FROM computersoft WHERE compid=OLD.id;
CREATE TRIGGER updateComp
AFTER
UPDATE
ON computers
FOR EACH ROW
UPDATE computersoft SET compid=NEW.id WHERE compid=OLD.id;
CREATE TRIGGER deleteFromSoft
AFTER
DELETE
ON soft
FOR EACH ROW
DELETE FROM computersoft WHERE softid=OLD.id;
CREATE TRIGGER updateSoft
AFTER
UPDATE
ON soft
FOR EACH ROW
UPDATE computersoft SET softid=NEW.id WHERE softid=OLD.id;
deleteFromComp : при удаление записей в таблице computers происходит удаление записей в таблице computersoft.
updateComp: при обновлении записей в таблице computers происходит обновление записей в таблице computersoft.
deleteFromSoft : при удаление записей в таблице soft происходит удаление записей в таблице computersoft.
updateSoft: при обновлении записей в таблице soft происходит обновление записей в таблице computersoft.
Обновим БД в HeidiSQL:
Проверим работоспособность триггеров. Для этого добавим запись в таблицу soft , создадим запись в таблице computersoft, связанную с новой записью, обновим запись в таблице soft , удалим новую запись в таблице soft:
* Создание: INSERT INTO soft VALUES (3, "Microsoft ", "MS Outlook 2010");
* Создание: INSERT INTO soft VALUES (4, "Microsoft ", "MS Word 2010");
* Создание: INSERT INTO computersoft VALUES (2, 4, "9.8.3");
* Создание: INSERT INTO computersoft VALUES (0, 3, "9.0.1");
* Обновление: UPDATE soft SET id=5 WHERE id=3;
Просмотрев содержимое таблиц soft и computersoft, мы увидим, что в таблице soft была обновлена запись name="MS Outlook 2010": id=3 -> id=5, а в таблице computersoft запись, которой соответствовала запись с id=3 в таблице soft, обновилась самостоятельно, что означает работоспособность триггера на UPDATE таблицы soft.
* Удаление: DELETE FROM soft WHERE id=5;
Просмотрев содержимое таблиц soft и computersoft, мы увидим, что из таблицы soft нами была удалена запись id=5, а из таблицы computersoft запись, которой соответствовала запись с id=5 в таблице soft, удалилась самостоятельно, что означает работоспособность триггера на DELETE из таблицы soft.
Триггеры для таблицы computers выполнены аналогично триггерам для таблицы soft, поэтому проверка их работоспособности производиться не будет.
6. Оператором CREATE VIEW создайте представления следующих запросов выборки (для связи таблиц используйте JOIN):
6.1 Вывести список компьютеров, на которых не установлено ПО Microsoft, а также отдельной строкой число таких компьютеров.
6.2 Вывести список всех программ (вне зависимости от числа инсталляций) с указанием производителя, а также число компьютеров, на которых они установлены.
1. Вывести список компьютеров, на которых не установлено ПО Microsoft, а также отдельной строкой число таких компьютеров:
Ограничения представлений
1. нельзя повесить триггер на представление,
2. нельзя сделать представление на основе временных таблиц; 3. нельзя сделать временное представление;
4. в определении представления нельзя использовать подзапрос в части FROM;
5. в определении представления нельзя использовать системные и пользовательские переменные; внутри хранимых процедур нельзя в определении представления использовать локальные переменные или параметры процедуры;
6. в определении представления нельзя использовать параметры подготовленных выражений (PREPARE);
7. таблицы и представления, присутствующие в определении представления должны существовать;
8. только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT;
6.1 Согласно пункту 4 из ограничений в представлениях нет возможности использовать вложенные запросы во FROM, исходя из этого, необходимо инвертировать условие и использовать вложенный запрос в WHERE.
Для создания VIEW используем приготовленный скрипт-файл createView.sql:
createView.sql
# Print out all tables data
SELECT * FROM soft;
SELECT * FROM computers;
SELECT * FROM computersoft;
CREATE VIEW antiMs AS
(
SELECT author AS `Software author`, compId AS `Computer ID`
FROM soft LEFT JOIN computersoft ON softId=soft.id
WHERE author!="Microsoft" AND NOT(isNULL(compId))
GROUP BY compId
) # ( SELECT
UNION ALL
(
SELECT "-----------------------------", "-----------"
FROM soft
WHERE id=0
)
UNION ALL
(
SELECT 'Number of all comps:', COUNT(DISTINCT compId)
FROM soft LEFT JOIN computersoft ON softId=soft.id
WHERE author!="Microsoft" AND NOT(isNULL(compId))
) # ( SELECT
;
# Check out the created VIEW
SELECT * FROM antims;
Выполним обновление записи в таблице computersoft и проверим, изменится ли вывод запроса на выборку из antiMS.
Из приведённого выше лога видно, что результат выполнения запроса из VIEW под названием antiMS изменился: из него удалился один компьютер(id=3), и суммарное количество стало равно четырём.
6.2 Вывести список всех программ (вне зависимости от числа инсталляций) с указанием производителя, а также число компьютеров, на которых они установлены.
Создадим VIEW следующим образом:
CREATE VIEW countAllSoft AS
SELECT soft.name AS `SW name`, soft.author AS `SW author`,
COUNT(computersoft.softId) AS `NumOfCompsInstalled`
FROM (soft LEFT JOIN computersoft ON (soft.id=computersoft.softId))
GROUP BY computersoft.softId
ORDER BY soft.name
;
Проверим результат, используя командную строку и визуальную среду:
7. Выполните INSERT-запрос к любой из таблиц, заключив эти запросы в конструкцию START TRANSACTION; ... ROLLBACK; По умолчанию, в MySQL включен режим AUTOCOMMIT, который при выполнении обновления данных MySQL будет сразу записывать обновленные данные. Данный режим препятствует выполнению данного задания, поэтому необходимо его отключить при помощи следующей команды:
SET AUTOCOMMIT=0;
После этого, чтобы обновление данных выполнилось, необходимо применить команду COMMIT. Команда ROLLBACK позволяет игнорировать изменения, произведенные с начала транзакции.
Запустим созданный скрипт-файл для тестирования работы с транзакциями:
testTrans.sql
SELECT * FROM soft;
SELECT * FROM computersoft;
SELECT id, netbiosname, ip FROM computers;
START TRANSACTION;
INSERT INTO soft VALUES
(6, "Adobe Systems Incorporated", "Addobe Photoshop CSS"),
(7, "NERO, Inc.", "NERO Smart"),
(8, "WinRar, Inc.", "WinRAR");
INSERT INTO computersoft VALUES
(3, 6, "8.7"),
(4, 7, "10.1.1.33"),
(5, 8, "9.1");
INSERT INTO computers VALUES
(3, "workst4", "192.168.0.230", 1500, 1024, 320, "yes", 23),
(4, "workst5", "192.168.0.220", 1500, 1024, 320, "yes", 23),
(5, "workst5", "192.168.0.210", 1500, 1024, 320, "yes", 23);
ROLLBACK;
SELECT * FROM soft;
SELECT * FROM computersoft;
SELECT id, netbiosname, ip FROM computers;
8. Проверьте отсутствие вставки данных. Отсутствие вставки данных было проверено в предыдущем пункте при помощи командной строки. В данном пункте проверим при помощи визуальной среды:
Изменения, описанные в транзакции, в базу данных внесены не были.
9. Замените ROLLBACK на COMMIT. Выполните запрос снова. testTrans.sql
SELECT * FROM soft;
SELECT * FROM computersoft;
SELECT id, netbiosname, ip FROM computers;
START TRANSACTION;
INSERT INTO soft VALUES
(6, "Adobe Systems Incorporated", "Addobe Photoshop CSS"),
(7, "NERO, Inc.", "NERO Smart"),
(8, "WinRar, Inc.", "WinRAR");
INSERT INTO computersoft VALUES
(3, 6, "8.7"),
(4, 7, "10.1.1.33"),
(5, 8, "9.1");
INSERT INTO computers VALUES
(3, "workst4", "192.168.0.230", 1500, 1024, 320, "yes", 23),
(4, "workst5", "192.168.0.220", 1500, 1024, 320, "yes", 23),
(5, "workst5", "192.168.0.210", 1500, 1024, 320, "yes", 23);
COMMIT;
SELECT * FROM soft;
SELECT * FROM computersoft;
SELECT id, netbiosname, ip FROM computers;
10. Проверьте успешную вставку данных. Успешная вставка данных была проверена в предыдущем пункте при помощи командной строки. В данном пункте проверим при помощи визуальной среды:
Изменения, описанные в транзакции, были успешно внесены в базу данных.
2
Документ
Категория
Рефераты
Просмотров
35
Размер файла
503 Кб
Теги
lab2, akochin
1/--страниц
Пожаловаться на содержимое документа