Лабораторна робота №10
СТВОРЕННЯ БАЗИ ДАНИХ В SQL SERVER MANAGEMENT
STUDIO
Мета роботи: навчитися створювати бази даних в середовищі SQL Server Management Studio.
Теоретичні відомості
Родоначальником серії SQL Server і його основою є мова запитів SQL. Ця мова була запропонована співробітником компанії IBM Едгаром Коддом на початку 1970-х р. Спочатку вона називалася SEQUEL (Structured English Query Language, структурована англійська мова для запитів), але згодом з юридичних міркувань була перейменована в SQL (Structured Query Language, структурована мова запитів). Офіційною вимовою стало [es kju :' el] ‒ ес-кью-ель. Незважаючи на це, навіть англомовні фахівці як і раніше часто називають SQL ‒ сіквел, замість ес-кью-ель. Метою розробки було створення простої непроцедурної мови, якою міг скористатися будь-який користувач, що навіть не має навичок програмування. На початок 1980-х років SQL завоювала популярність як мова реляційних систем керування базами даних (СКБД) і притягнула увагу Американського національного інституту по стандартизації (American National Standards Institute, ANSI), який в 1986, 1989, 1992, 1999 і 2003 роках випустив стандарти мови SQL. У 1989 році SQL був включений в стандарти міжнародної організації із стандартизації ISO (SQL:1989), а потім були прийняті і опубліковані стандарти SQL:1992, SQL:1999, SQL:2003 і SQL:2008. Нині усі виробники поширених реляційних СКБД підтримують з різною мірою відповідності стандарт SQL:2008. В основу мови SQL, що використовується в SQL Server, ліг різновид мови T ‒ SQL (Transact ‒ SQL).
На початку 1980-х р. компанією IBM і, зокрема, у той час її підрозділами Microsoft і Sybase, була створена перша версія мережевої СКБД, яка називалася SQL Server версія 1.0, для операційної системи IBM OS/2. Після цього під цю операційну систему було випущено ще 3 версії SQL Server. В середині 1980-х р. компанії Microsoft і Sybase відділяються від компанії IBM, і Microsoft починає роботу над своєю операційною системою Windows, і разом з компанією Sybase продовжує розвиток SQL Server. В середині 1990-х р. Microsoft створила операційну систему Windows NT і разом з компанією Sybase випустила першу версію SQL Server для Windows версії 4.1.
Надалі компанія Sybase розірвала свої відносини з Microsoft, і Microsoft сама продовжила роботу над Microsoft SQL Server 6.0. Ця версія була призначена для роботи в операційній системі Windows NT, 95 і 98. У 1999 р. виходить версія Microsoft SQL Server 7.0, яка стала однією з найпопулярніших серверних СКБД у світі. У 2000 р. виходить 8-а версія Micrsoft SQL Server 2000. У 2005 році з'являється нова версія сервера, заснована на новій технології .NET, а в 2008 році ‒ її поліпшена версія ‒ Microsoft SQL Server 2008.
У СКБД Microsoft SQL Server нову базу даних (БД) можна створити, використовуючи стандартні команди мови T - SQL.
Для створення нової БД за допомогою T - SQL необхідно спочатку перейти у БД «Master». Це можна зробити або вибором її з випадного списку баз даних на панелі інструментів, або набором команди USE Master на вкладці нового запиту.
Усі команди мови T - SQL набираються на вкладці
нового запиту (SQLQuery). Для того, щоб створити новий запит на панелі
інструментів необхідно натиснути кнопку 
. Для виконання команд мови T - SQL на панелі інструментів
необхідно натиснути кнопку 
або на вкладці нового запиту набрати команду GO.
У Microsoft SQL Server БД складається з двох частин:
- файл даних ‒ файл, що має розширення .mdf, де знаходяться усі таблиці та запити;
- файл журналу транзакцій ‒ файл, що має розширення .ldf, містить журнал, де фіксуються усі дії з БД. Цей файл призначений для відновлення БД у разі її виходу з ладу.
Для створення нового файлу даних використовується SQL-команда
CREATE DATABASE, яка має наступний синтаксис:
CREATE DATABASE <Ім'я БД>
(Name=<Логічне ім'я>,
FileName=<Ім'я файлу>
[Size=<Нач. розмір>,]
[Maxsixe=<Макс. розмір>,]
[FileGrowth=<Крок>])
[LOG ON
(Name=<Логічне ім'я>,
FileName=<Ім'я файлу>
[Size=<Поч. розмір>,]
[Maxsixe=<Макс. розмір >,]
[FileGrowth=<Крок>])
Тут Ім'я БД ‒ ім'я створюваної БД; Логічне ім'я ‒ визначає логічне ім'я файлу даних БД, по якому відбувається звернення до файлу даних; Ім'я файлу ‒ визначає повний шлях до файлу даних; Поч. розмір ‒ початковий розмір файлу даних в МБ; Макс. розмір ‒ максимальний розмір файлу даних в МБ; Крок ‒ крок збільшення файлу даних, або в МБ або в %.
Параметри в розділі LOG ON аналогічні параметрам в розділі CREATE DATABASE. Проте вони визначають параметри журналу транзакцій.
Приклад: Створити БД «Artworks», розташовану у файлі «D:\Artworks.mdf» і яка має початковий розмір файлу даних ‒ 3 МБ, максимальний розмір файлу даних ‒ 100 МБ і крок збільшення файлу даних, рівний 1 МБ. Файл журналу транзакцій цієї БД має ім'я «ArtworksLog» і розташований у файлі «D:\Artworks.ldf». Цей файл має початковий розмір, рівний 1 МБ, максимальний розмір, рівний 20 МБ і крок збільшення, рівний 1 МБ.
CREATE DATABASE Artworks
або
Name = Artworks
FileName = 'D :\ Artworks.mdf
Size = 3MB
Maxsixe = 100MB
FileGrowth= 1MB
або (створення log файлу)
Name = Artworks Log
FileName = 'D:\Artworks.ldf
Size = 1MB
Maxsixe = 20MB
FileGrowth = 1MB
У мові запитів T - SQL з БД можливі наступні дії:
1. Відображення відомостей про БД: EXEC SP_HELPDB <Ім'я БД>;
2. Зміна параметрів БД: EXEC SP_DBOPTION <Ім'я БД>, <Параметр>, <Значення>;
3. Додавання нових файлів, видалення файлів і перейменування файлів, що входять у БД :
ALTER DATABASE <Ім'я БД>
ADD FILE (<Параметри>)|
REMOVE FILE <Логічне ім'я файлу>|
MODIFY FILE (<Параметри>)
де ADD FILE – додає файл, REMOVE FILE – видаляє, а розділ MODIFY FILE – змінює параметри файлу;
4. Стискання усієї БД: DBCC SHRINKDATABASE <Ім'я БД>;
5. Стискання конкретного файлу БД: DBCC SHRINKFILE <Логічне ім'я файлу>;
6. Перейменування БД: EXEC SP_RENAMEDB <Ім'я БД>,<Нове ім'я БД>;
7. Видалення БД: DROP DATABASE <Ім'я БД>.
Вищеперелічені команди використовують наступні параметри:
- <Ім'я БД> – ім'я БД з якою проводиться дія;
- <Параметр> – змінюваний параметр;
- <Значення> – нове значення змінюваного параметра;
- <Параметри> – параметри файлу БД, аналогічні параметрам, які використовуються в команді CREATE DATABASE;
- <Логічне ім'я файлу> – логічне ім'я файлу, що входить в БД;
- <Нове ім'я БД> – нове ім'я БД.
Приклад виконання лабораторної роботи
Спершу запустимо середовище розробки «SQL Server Management Studio». Для цього в меню «Пуск» вибираємо пункт «Усі програми – Microsoft SQL Server 2008 – Cередовище SQL Server Management Studio» (рис. 1)
Рис. 1.
Після запуску середовища розробки з'явиться вікно підключення до сервера «З'єднання з сервером» (рис. 2).
Мал. 2.
У цьому вікні необхідно натиснути кнопку «З'єднати».
Після натиснення кнопки «З'єднати» з'явиться вікно середовища розробки «SQL Server Management Studio» (мал. 3).
Рис. 3.
Це вікно має наступну структуру:
1. Віконне меню – містить повний набір команд для керування сервером і виконання різних операцій.
2. Панель інструментів – містить кнопки для виконання найбільш часто виконуваних операцій. Зовнішній вигляд цієї панелі залежить від виконуваної операції.
3. Панель «Оглядач об'єктів» – оглядач об'єктів. Оглядач об'єктів – це панель з деревовидною структурою, що відображає усі об'єкти сервера, а також дозволяє проводити різні операції, як з самим сервером, так і з БД. Оглядач об'єктів є основним інструментом для розробки БД.
4. Робоча область. У робочій області виконуються усі дії з БД, а також відображається її вміст.
У оглядачі об'єктів самі об'єкти знаходяться в теках. Щоб відкрити теку необхідно клікнути по знаку «+» зліва від зображення теки.
Натиснення кнопки 
призводить до відкриття
вікна запитів (рис. 4).
Рис. 4
Для виконання запиту, введеного в цьому вікні,
треба натиснути на кнопку 
.
Виконаємо створення бази даних «Artworks» в середовищі розробки SQL Server Management Studio. У новому вікні запитів введемо запит на створення БД:
IF DB_ID('Artworks') IS NULL
CREATE DATABASE Artworks;
Якщо бази даних з ім'ям Artworks не існує, наведений код створить нову БД. Функція DB_ID приймає ім'я бази даних як вхідний параметр і повертає внутрішній ID бази даних. Якщо БД з ім'ям вхідного параметра не існує, функція повертає значення NULL. Це простий спосіб перевірити наявність заданої БД.
У інструкції CREATE DATABASE використовуються настановні параметри файлу для завдання його місця розташування і початкового розміру.
Після виконання цього запиту у вікні середовища розробки SQL Server Management Studio на панелі оглядача об'єктів в теці «Бази даних» з'явиться нова БД Artworks (рис. 5).
Рис.5
Уся інформація у базі даних зберігається в таблицях, які є засобом зберігання даних. Таблиці складаються з рядків або записів і стовпців або полів. Кожне поле має три характеристики:
1. Ім'я поля – використовується для звернення до поля;
2. Значення поля – визначає інформацію, що зберігається в полі;
3. Тип даних поля – визначає, який вид інформації можна зберігати в полі.
У Microsoft SQL Server використовуються наступні типи даних:
- Двійкові типи даних, які містять послідовності нулів і одиниць: 1) binary(n) – двійковий тип фіксованої довжини розміром в n байт, де n – значення від 1 до 8000; розмір при зберіганні складає n байт; 2) varbinary(n) – двійковий тип зі змінною довжиною, n може мати значення від 1 до 8000, max вказує, що максимальний розмір при зберіганні складає 231-1 байт;
- Цілочисельні типи даних – типи даних для зберігання цілих чисел (у дужках вказаний діапазон значень типу даних): tinyint (0..255), smallint (-32768..+32767), int (-231.. + (231-1)), bigint
(-263.. + (263-1));
- Типи даних для зберігання чисел з плаваючою комою: real займає в пам'яті 4 байти; float(n), де n – ця кількість бітів, використовуваних для зберігання мантиси числа у форматі float при експоненціальному представленні, визначає точність даних і розмір для зберігання; значення параметра n повинне лежати в межах від 1 до 53; значення за замовчуванням для параметра n є 53;
- Типи даних для зберігання чисел з фіксованою точністю і масштабом: decimal (p, s) і numeric (p, s), де p (точність) – максимальна кількість десяткових розрядів числа (як ліворуч, так і праворуч від десяткової коми). Точність має бути значенням в діапазоні від 1 до 38. За замовчуванням – це значення дорівнює 18. s (масштаб) – максимальна кількість десяткових розрядів числа праворуч від десяткової коми. Масштаб може набувати значення від 0 до p і може бути вказаний тільки спільно з точністю. За замовчуванням масштаб набуває значення 0; тому 0 ≤ s ≤ p;
- Символьні типи даних: char(n) – cтрічкові дані фіксованої довжини не в Юнікоді, аргумент n визначає довжину рядка і повинен мати значення від 1 до 8000, розмір при зберіганні складає n байт; varchar (n | max) – стрічкові дані змінної довжини не в Юнікоді, аргумент n визначає довжину стрічки і повинен мати значення від 1 до 8000, значення max вказує, що максимальний розмір при зберіганні складає 231-1 байт (2 ГБ); text – дані змінної довжини не в Юнікоді в кодовій сторінці сервера і з максимальною довжиною стрічки 231-1;
- Спеціальні типи даних: bit – цілочисельний тип даних, який може набувати значень 1, 0 або NULL; image – тип даних для зберігання малюнка розміром до 2ГБ;
- Типи даних дати і часу: date (від 01.01.0001 до 31.12.9999); datetime (діапазон дати – від 01.01.1753 до 31.12.1999, діапазон часу – від 00:00:00 до 23:59:590, 997); smalldatetime (діапазон дати – від 01.01.1900 до 6.06.2079, діапазон часу – від 00:00:00 до 23:59:59); time (від 00:00:00.0000000 до 23:59:59.9999999);
- Грошові типи даних для зберігання фінансової інформації: money (8 байт) і smallmoney (4 байти) – типи даних, що представляють грошові (валютні) значення.
Для створення таблиць в SQL Server в першу чергу необхідно зробити активною ту БД, в якій створюється таблиця. Для цього в новому запиті можна набрати команду: USE <Ім'я БД>, або на панелі інструментів необхідно вибрати у випадаючому списку робочу БД. Після вибору БД можна створювати таблиці.
Таблиці створюються командою
CREATE TABLE table_name
( { <column_definition> } )
[ ; ]
<column_definition> ::= column_name <data_type> [ NULL
| NOT NULL ] [DEFAULT constant_expression ] | [
IDENTITY [ ( seed, increment )] ] [ <column_constraint>
[ ...n ] ]
<column_constraint> ::= [ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
Тут table_name – ім'я таблиці; column_name – ім'я стовпця в таблиці; data_type – тип даних для стовпця; IDENTITY вказує, що новий стовпець є стовпцем ідентифікаторів, при цьому seed – значення, яке використовується для найпершого рядка, що завантажується в таблицю, increment – значення приросту, що додається до значення ідентифікатора попереднього завантаженого рядка; CONSTRAINT – необов'язкове ключове слово, що вказує на початок визначення обмеження, constraint_name – ім'я обмеження; NULL | NOT NULL визначає, чи допустимі для стовпця значення NULL; PRIMARY KEY – обмеження, яке робить стовпець первинним ключем таблиці.
Якщо ім'я поля містить пропуск, то воно береться в квадратні дужки.
Приклад: Створити таблицю «Artworks», що містить поля:
а) код твору (ArtworkId);
б) назва твору (Title);
в) жанр (Genre);
г) засоби створення (Tools);
д) код автора (AuthorId);
е) дата створення (CreatDate);
ж) ціна (Price);
з) відділ зберігання (DepId).
SQL-запит для створення цієї таблиці має наступний вигляд:
USE Artworks;
IF OBJECT_ID('dbo.Artworks', 'U') IS NOT NULL
DROP TABLE dbo.Artworks;
CREATE TABLE dbo.Artworks
(
ArtworkId BIGINT IDENTITY(1,1) CONSTRAINT PK_Artworks PRIMARY KEY,
Title VARCHAR(100) NULL,
Genre VARCHAR(50) NULL,
Tools VARCHAR(50) NULL,
AuthorId BIGINT NULL,
CreatDate DATE NULL,
Price MONEY NULL,
DepId INT NOT NULL
);
Інструкція USE змінює поточний зв'язок з БД на зв'язок з Artworks. Включення цієї інструкції в сценарії створення об'єктів дуже важливе, оскільки гарантує створення об'єктів в необхідній БД.
Інструкція IF запускає функцію OBJECT_ID, яка в якості вхідних параметрів приймає ім'я об'єкту і його тип. Тип 'U' представляє призначені для користувача таблиці. Ця функція повертає внутрішній ID об'єкту, якщо об'єкт із заданим ім'ям і типом вже існує, і значення NULL в іншому випадку.
При створенні таблиці використовується схема з ім'ям dbo, яка створюється автоматично в кожній базі даних і використовується як схема за замовчуванням. Якщо опустити ім'я схеми при створенні таблиці, то SQL Server зв'яже з таблицею схему, що використовується за замовчуванням для імені користувача, що виконує програмний код.
Для кожного атрибуту суті Artworks задається його ім'я, тип і допустимість значень NULL.
Для стовпця ArtworkId визначено обмеження у вигляді первинного ключа (PK_Artworks), при цьому значення ArtworkId починатимуться з 1 і збільшуватимуться при кожному додаванні нових рядків в таблицю теж на 1 (IDENTITY(1,1)).
Аналогічно створюються і інші таблиці БД Artworks: Authors, Employees, Departments. SQL –запити для створення цих таблиць наведені нижче.
USE Artworks;
IF OBJECT_ID('dbo.Authors ', 'U') IS NOT NULL
DROP TABLE dbo.Authors;
CREATE TABLE dbo.Authors
(
AuthorId BIGINT IDENTITY(1,1) CONSTRAINT PK_Authors PRIMARY KEY,
Lastname VARCHAR(25) NOT NULL,
Firstname VARCHAR (25) NOT NULL,
Middlename VARCHAR (25) NULL,
DateOfBirth DATE NULL,
DateOfDeath DATE NULL,
Country VARCHAR(25) NULL
);
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
EmpId BIGINT IDENTITY(1,1) CONSTRAINT PK_Employees PRIMARY KEY,
Lastname VARCHAR(25) NOT NULL,
Firstname VARCHAR (25) NOT NULL,
Middlename VARCHAR (25) NOT NULL,
Position VARCHAR (25) NULL,
Salary MONEY NULL,
BeginDate DATE NOT NULL,
EndDate DATE NULL,
DepId INT NULL
);
IF OBJECT_ID('dbo.Departments ','U') IS NOT NULL
DROP TABLE dbo.Departments;
CREATE TABLE dbo.Departments
(
DepId INT IDENTITY(1,1) CONSTRAINT PK_Departments PRIMARY KEY,
Name VARCHAR(25) NOT NULL
);
Для того, щоб забезпечити посилальну цілісність у БД Artworks треба додати в створені таблиці обмеження по зовнішніх ключах.
Таблиці Artworks і Authors треба зв'язати по стовпцю AuthorId, а таблиці Artworks і Departments – по стовпцю DepId.
Аналогічно мають бути пов'язані між собою таблиці Employees і Departments.
USE Artworks;
ALTER TABLE dbo.Artworks
ADD CONSTRAINT FK_Artw_Auth
FOREIGN KEY (AuthorId)
REFERENCES Authors (AuthorId);
ALTER TABLE dbo.Artworks
ADD CONSTRAINT FK_Artw_Dep
FOREIGN KEY (DepId)
REFERENCES Departments (DepId);
ALTER TABLE dbo.Employees
ADD CONSTRAINT FK_Emp_Dep
FOREIGN KEY (DepId)
REFERENCES Departments (DepId);
Тепер, коли встановлені зв'язки між усіма таблицями, можна створити діаграму, що описує ці взаємозв'язки.
Для створення діаграми треба клікнути правою кнопкою миші на елементі БД «Діаграми баз даних» і в контекстному меню, що відкрилося, вибрати пункт «Створити діаграму бази даних» (рис. 6).
Рис.6
У діалоговому вікні, що відкрилося, треба вибрати таблиці, які будуть додані на діаграму (рис. 7).
Рис.7
В результаті буде отримана діаграма наступного виду:
Рис.8
Завдання
1) Створити базу даних і її таблиці відповідно до варіанту. Виконати
завдання за допомогою стандартних команд мови T - SQL.
2) Створити діаграму БД засобами середовища SQL Server Management Studio.