Лабораторная работа №2. Импорт данных

Цель работы: приобрести практические навыки по импорту данных из одной таблицы в другую MS SQL server и из внешних источников в базу данных, а также по обработке импортируемых данных.

 

В таблице SourceForImport хранятся реализации десяти функций. Таблица содержит целочисленное поле RealizationType (тип INT) – номер функции, поле DT (тип DATETIME) – задающее значение времени, V (тип REAL) – значение функции в момент времени DT. Все поля таблицы могут иметь значение NULL, первичный ключ в таблице не определен.

Задания для самостоятельного решения

Задания 1. Импорт данных из таблицы MS SQL.

1.1. Создать таблицу ForImport1_NN (где NN – номер варианта). Таблица должна содержать поля DT (тип DATETIME), V1 (тип REAL), V2 (тип REAL). Разработать команду Insert для массовой вставки данных из таблицы SourceForImport в таблицу ForImport1_NN. В конечную таблицу должны попасть только записи с номером функции, совпадающим с номером варианта. Поля DT и V1 конечной таблицы равны соответственно полям DT и V исходной таблицы, а поле V2 конечной таблицы формируется как квадрат от значения поля V исходной таблицы.

1.2. Создать таблицу ForImport2_NN (где NN – номер варианта). Таблица должна содержать поля DT (тип DATETIME), V (тип REAL). Разработать запрос для вставки в таблицу ForImport2_NN средние за сутки значения, сформированные по данным из таблицы SourceForImport с номером функции, совпадающим с номером варианта.

Задание №2 Импорт данных из внешнего источника

1.1.1.Создать CSV (разделитель запятая, пробел, двоеточие, табуляция – варианты или), Создать таблицу ForImport1_NN и импортировать данные из файла. Создать файл в Excel, сохранить его с разделителями.

1.2. Создать таблицу ForImport2_NN (идентичную исходной, но поле которое было NULL, должно стать NOT NULL) (где NN – номер варианта). Разработать команду Insert для массовой вставки данных из таблицы … в таблицу ForImport2_NN. При вставке пустые значения поля … должны быть заданы значением 0.

1.3.

 

 

bulk insert ForImport2_11

From 'c:\1\1.txt'

with

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n',

KEEPNULLS

 

)

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

 

Задание 2.

Создать таблицу, состоящую из двух целочисленных полей и строкового поля. Целочисленные поля характеризуют координаты точки на плоскости, строковое поле название точки. Первичный ключ таблицы - составной, пара координат. Ввести в таблицу 10 произвольных строк.

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

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

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

 

Задание 3.

В таблице Realization хранятся реализации случайной величины. Таблица содержит целочисленное поле R_ID – номер реализации, целочисленное поле C_ID – номер отсчета реализации и поле Y – значение реализации R_ID в момент времени (отсчет) C_ID. Первичный ключ - составной (R_ID, C_ID).

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

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

 

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

1. Основные типы пользовательских функций?

2. Какой тип функции используется в качестве источника данных в запросе?

3. Входные параметры пользовательской функции?

4. Скалярные пользовательские функции, область применения?

 

Содержание отчета

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

 

 

Глава 5 Курсовая работа

“Проектирование базы данных по универсальному отношению“

 

Исходные данные: универсальное соотношение.

Задание:

1. Создать инфологическую модель базы данных.

2. Создать реляционную базу данных, приведя ее к НФ3 + «нумерация». Классифицировать сущности полученной реляционной базы данных (стержневая, ассоциативная и т.д.).

3. Разработать PDM модель базы данных в MS Visio.

4. Разработать скрипты создания объектов структуры (первичные и внешние ключи обязательно)

5. Разработать скрипты, заполняющие базу исходными данными (скрипты).

6. Написать запрос, формирующий исходное универсальное отношение (скрипты).

7. Разработать представление (View) выводящее универсальное отношение (скрипты).

8. Написать запросы по вариантам (скрипты).

9. Разработать табличную функцию по запросу №1 из задания 8.

10. Разработать систему протоколирования по вариантам (скрипты). Система должна корректно работать при одновременном изменении нескольких записей.

 

 

Для защиты курсового представляется:

1. Отчет.

2. Два файла скриптов.

3. Чертежи формата А3.

 

Отчет должен включать в себя:

1. Титульный лист.

2. Инфологическую модель.

3. Ход разработки базы данных, оформленный согласно примеру в разделе 2.7.

4. Все разработанные скрипты.

 

 

Первый файл скриптов должен содержать все команды для создания таблиц, заполнения их первичными данными, создания представления и объектов для системы протоколирования. Файл должен быть пригоден для создания структуры базы данных путем последовательного исполнения приведенных в нем команд. (команды в файле должны быть расположены в правильной последовательности)

 

Второй файл скриптов должен содержать все остальные разработанные команды.

 

Чертежи формата А3:

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

2. PDM модель с учетом таблиц для системы протоколирования.

 

 

Вариант 1. Склад (поставки)

Тип товара	Название товара	Дата поставки	Кол–во	Ед. изм	Цена, руб	Назва­ние пос­тавщика	Страна	Город	Адрес
Бумага	KYM LUX A4, 80 г, 500 л.	10.10.1998		шт		ЦВНТР	Россия	Самара	Галактионовская, 279
Бумага	SVETOCOPY A4, 80 г, 500 л.	20.01.1999		шт		ЦВНТР	Россия	Самара	Галактионовская, 279
Чернила	EPSON Stylus 800/1000 (100 ml, LG) черн	10.10.1998		шт		ЦВНТР	Россия	Самара	Галактионовская, 279
Чернила	EPSON Stylus 800/1000 (100 ml, LG) черн	10.10.1998		шт		ООО «1»	Россия	Самара	Ленина, 12
Бумага	SVETOCOPY A4, 80 г, 500 л.	12.05.1999		шт		ЦВНТР	Россия	Самара	Галактионовская, 279
Бумага	SVETOCOPY A4, 80 г, 500 л.	13.05.1999		шт		ООО «2»	Россия	Ульяновск	Галактионовская, 279
Бумага	«Снегурочка» A4, 80 г, 500 л.	13.05.1999		шт		ООО «2»	Россия	Ульяновск	Галактионовская, 279

1. Вывести на экран записей о поставках бумаги, завезенной российскими поставщиками за последние 10 месяцев.

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

 

 

Вариант 2. Видео прокат

Жанр	Актеры	Название	Год	Студия	Дата выдачи	Дата возврата	Залог, руб.	Клиент	Паспорт­ные данные	Место жительства
Экшен	А. Джоли, Дж. Войт	Лара Крофт: Расхитительница гробниц		Paramount Pictures	10.10.10	20.10.10		Иванов А.А.	серия 3600 № 321970, выдан...	Пр Кирова 245-5
Экшен	А. Джоли, Дж. Войт	Лара Крофт: Расхитительница гробниц		Paramount Pictures	20.11.10	20.12.10		Петрова А.С.	серия XII‑ЕР № 213433, выдан...	Ул. Вольская 134-2
Боевик	Б.Пит, А. Джоли	Мистер и миссис Смит		Summit Entertainment	10.10.11	30.10.11		Иванов А.А.	серия 3600 № 321970, выдан...	Пр Кирова 245-5
Драма	Том Хенкс, Робин Райт	Форрест Гамп		Paramount Pictures	23.06.12			Кузнецов П.А.	серия XIII‑ЕР № 365490, выдан...	Ул. Новая 13-2
Боевик	Б. Пит, Дж. Клуни, М. Дэймон	Одиннадцать друзей Оушен		Warner Bros. Pictures	22.07.12			Петрова А.С.	серия XII‑ЕР № 213433, выдан...	Ул. Вольская 134-2
Экшен	А. Джоли, Дж. Войт	Лара Крофт: Расхитительница гробниц		Paramount Pictures	22.07.12	22.08.12		Петрова А.С.	серия XII‑ЕР № 213433, выдан...	Ул. Вольская 134-2

 

1. Вывести на экран список всех граждан, имеющих на руках фильмы. Вывести на экран список всех граждан, которые продержали фильмы на руках больше 15 дней.

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

 

Вариант 3. ВЛАДЕЛЬЦЫ КВАРТИР

Название улицы	№ дома	№ квартиры	Фамилия, Имя Отчество	Год рождения	Паспортные данные	Отв. квар­тиро­съем­щик	Горячая вода	Газ	Мусоро­провод
ул. Невская			Петров Василий Павлович		Серия 3600 № 318723, выдан...	+	+	+	+
ул. Невская			Петрова Василиса Владимировна		Серия 3600 № 987354, выдан...	–	+	+	+
ул. Невская			Иванова Ирина Владимировна		Серия 3607 № 988905, выдан...	+	+	+	+
ул. Ленина			Кирпичев Петр Петрович		Серия 3607 № 944505, выдан...	+	+	–	+
ул. Ленина			Воронина Алла Петровна		Серия 3607 № 888805, выдан...	+	+	–	+
Волжский проспект			Боброва Евгения Павловна		Серия 3600 № 432890, выдан...	+	–	+	–
Волжский проспект			Петров Василий Павлович		Серия 3600 № 318723, выдан...	–	–	+	–
...

1. Вывести на экран список всех граждан, которые не пользуются газом и мусоропроводом.

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

 

Вариант 4. ГИБДД

Регистр. №	Дата постановки на учет	Дата снятия с учета	Утиль	VIN	Марка	Цвет	Год выпуска	Фамилия	Паспортные данные
С102ОК 63	10.02.2000	10.04.2005			ВАЗ 21099	черный		Петров Д.И	серия 3600 № 321970, выдан...
К942ОК 63	11.04.2005				ВАЗ 21099	черный		Сидоров К.Р.	серия 3604 № 300070, выдан...
У145ОН 63	09.04.2005				BMW 520	серый		Петров Д.И	серия 3600 № 321970, выдан...
E345EP 63	13.02.2009				ВАЗ 21106	синий		Петров Д.И.	серия 3601 № 321970, выдан...
М142ОР 63	11.04.2001	11.04.2011	+		ВАЗ 21099	черный		Сидоров К.Р.	серия 3604 № 300070, выдан...
A934YX 77	23.08.2007	23.08.2012			ВАЗ 21011	красный		Иванов В.П.	серия 3600 № 213433, выдан...
...

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

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

 

Вариант 5. Технико-экономические параметры оборудования

Название оборудования	Дата монтажа	Ресурс, час	Название параметра	Размерность	Номинал	Дата	Период	Значение
Энергетический котёл №1	10.12.75	250 000	Производительность пара	т/час		01.09.98	1 месяц
Энергетический котёл №1	10.12.75	250 000	Температура пара на выходе	°С		01.09.98	1 месяц	559.8
Турбина №1	20.12.85	150 000	Рабочая мощность	МВт		01.09.98	1 сутки
Турбина №1	20.12.85	150 000	Выработка электроэнергии	МВт·час		01.09.98	1 сутки	1057.3
Энергетический котёл №1	10.12.75	250 000	Температура пара на выходе	°С		01.10.98	1 месяц	555.8
Энергетический котёл №1	10.12.75	250 000	Производительность пара	т/час		01.10.98	1 месяц
Турбина №1	20.12.85	150 000	Рабочая мощность	МВт		01.10.98	1 сутки
Турбина №1	20.12.85	150 000	Выработка электроэнергии	МВт·час		01.10.98	1 сутки	1061.3
Турбина №1	20.12.85	150 000	Рабочая мощность	МВт		01.09.98	1 месяц	44.2
Энергетический котёл №2	01.02.95	270 000	Производительность пара	т/час		01.09.98	1 месяц
Энергетический котёл №2	01.02.95	270 000	Производительность пара	т/час		01.10.98	1 месяц
Турбина №2	21.03.85	150 000	Рабочая мощность	МВт		01.09.98	1 сутки
Турбина №2	21.03.85	150 000	Выработка электроэнергии	МВт·час		01.09.98	1 сутки	1057.3
...

1. Написать запрос, выводящий на экран значения суточных параметров энергетического котла №1 в период с 01.01.98 по 10.01.98.

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

 

Вариант 6. Программное обеспечение компьютерных классов

Таксист - телефон	Таксист ФИО	Тип авто	Наценка, %	Клиент - телефон	Клиент имя	Откуда	Куда	Когда	Скидка, %
	Петров	VIP			Сергей	Гагарина 6	Ленина 4	01.01.15 10:30
	Петров	VIP				Кирова 16	Ленина 24	01.01.15 14:30
	Петров	VIP			Сергей	Лесная 16	Кирова 24	02.01.15 8:10
	Сидоров	Эконом			Сергей	Лесная 16	Кирова 24	03.01.15 8:20
	Сидоров	Эконом			Сергей	Лесная 16	Кирова 24	13.01.15 8:20
	Иванов	Стандарт			Сергей	Лесная 16	Кирова 24	23.01.15 8:20
	Иванов	Стандарт			Иван	Кирова 56	Карьерная 4	4.01.15 18:40
	Иванов	Стандарт				Лунная 26	Лесная 4	4.01.15 23:20
	Петров	VIP			Иван	Гагарина 3	Кирова 124	08.01.15 12:10
	Петров	VIP			Сергей	Лесная 16	Кирова 24	02.02.15 8:10
…

1. Написать запрос, выводящий на экран телефон клиента и количество его заказов за январь 2015.

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

 

Вариант 7. Библиотека

Шифр	Автор	Название	Год издания	Место издания	Дата выдачи	Дата возврата	№ чит. билета	Фами­лия И.О.	Паспорт­ные данные
31.26 А86	Артамонов Б.И., Бокуняев А.А.	Источники электропита­ния и радиоустройств		М.: Энергоатом­из­дат	10.10.15	20.10.95		Иванов А.А.	серия IX‑ЕР № 321970, выдан...
31.26 А86	Артамонов Б.И., Бокуняев А.А.	Источники электропита­ния и радиоустройств		М.: Энергоатом­издат	20.11.15	20.12.95		Петрова А.С.	серия XII‑ЕР № 213433, выдан...
84.7 Х 37	Хэмингуэй Э.	Праздник, который всегда с тобой		М.: Правда	10.10.15	30.10.95		Иванов А.А.	серия IX‑ЕР № 321970, выдан...
32.85 И28	Игумнов Д.В., Королев Г.В.	Основы микроэлектроники		М.: Высшая школа	23.03.16			Шама­ров П.А.	серия XIII‑ЕР № 365490, выдан...

 

Вывести на экран список всех граждан, имеющих на руках книги.

Вывести на экран список всех граждан, которые продержали книги на руках больше 15 дней.

 

Библиографический список

1. ГОСТ 24.104. Единая система стандартов автоматизированных систем управления. Автоматизированные системы управления системы управления. Общие требования. – Вед. 1987-01-01 – М.: Госстандарт России: Изд-во стандартов. 1986. – 17 с.

2. CITForum [Электронный ресурс] = Основы проектирования реляционных баз данных: учебное пособие / В.В. Кириллов – СПб.: Санкт-Петербургский Государственный институт
точной механики и оптики, 2008 – Режим доступа: http://citforum.ru/database/dbguide/index.shtml, свободный. – Загл. с экрана

3. Дейт К.Дж. Введение в системы баз данных/ К.Дж. Дейт – 8-е изд. – пер. с англ. – М.: Издательский дом "Вильямс", 2008. - 1328с. – ISBN – 978-5-8459-0788-2, 0-321-19784-4

4. CITForum [Электронный ресурс] = Базы данных. Вводный курс: курс лекций / C.М. Кузнецов – М.?: Центр Информационных Технологий, 2008 – Режим доступа: http://citforum.ru/database/advanced_intro, свободный. – Загл. с экрана

5. Chen, P. P.-S. The Entity-Relationship Model-Toward a Unified View of Data/ P. P.-S. Chen // ACM Transactions on Database Systems – 1 (1)– 1976. p. 9-36

6. CITForum [Электронный ресурс] = Модель "сущность-связь" – шаг к единому представлению о данных / Петер Пин-Шен Чен, пер. М.Р. Когаловский, ред. C.М. Кузнецов – М.?: Центр Информационных Технологий, 2009 – Режим доступа: http://citforum.ru/database/classics/chen/#part_6, свободный. – Загл. с экрана

7. Codd, E.F. A Relational Model of Data for Large Shared Data Banks / E.F Codd // Communications of the ACM (Association for Computing Machinery) – 13 (6) – p. 377–387

8. CITForum [Электронный ресурс] = Реляционная модель данных для больших совместно используемых банков данных / Е.Ф. Кодд, пер. М.Р. Когаловский, ред. C.М. Кузнецов – М.?: Центр Информационных Технологий, 2009 – Режим доступа: http://citforum.ru/database/classics/codd, свободный. – Загл. с экрана

9. CITForum [Электронный ресурс] = Расширение реляционной модели для лучшего отражения семантики / Е.Ф. Кодд, пер. М.Р. Когаловский, ред. C.М. Кузнецов – М.?: Центр Информационных Технологий, 2009 – Режим доступа: http://citforum.ru/database/classics/codd_2, свободный. – Загл. с экрана

10. Пушников А.Ю. Введение в системы управления базами данных. Часть 1. Реляционная модель данных: Учебное пособие/Изд-е Башкирского ун-та. - Уфа, 1999. - 108 с. - ISBN 5-7477-0350-1.

11. Пушников А.Ю. Введение в системы управления базами данных. Часть 2. Нормальные формы отношений и транзакции: Учебное пособие/Изд-е Башкирского ун-та. - Уфа, 1999. - 138 с. - ISBN 5-7477-0351-X.

12. CITForum [Электронный ресурс] = Основы современных баз данных: информационно-аналитические материалы / C.М. Кузнецов – М.?: Центр Информационных Технологий, 2008 – Режим доступа: http://citforum.ru/database/osbd/contents.shtml, свободный. – Загл. с экрана

13. Электронная документация по SQL Server [Электронный ресурс] – Режим доступа: http://msdn.microsoft.com/ru-ru/library/ms130214.aspx, свободный. – Загл. с экрана

Учебное издание КОЛПАЩИКОВ Сергей Александрович Теория и практика разработки реляционных баз данных   Редактор Т.Г. Трубина Компьютерная верстка И.О. Миняева Выпускающий редактор Н.В. Беганова   Подписано в печать 1.12.10. Формат 60х84 1/16. Бумага офсетная. Усл. п. л. 7,72 Уч.-изд. л. 7,69. Тираж 100 экз. Рег. № 93/09.

Государственное образовательное учреждение высшего профессионального образования «Самарский государственный технический университет» 443100, г. Самара, ул. Молодогвардейская, 244. Главный корпус   Отпечатано в типографии Самарского государственного технического университета 443100, г. Самара, ул. Молодогвардейская, 244. Корпус № 8