Створення та наповнення таблиць БД

Вступ

В наслідок розвитку інформаційних технологій, розповсюдження мереж Internet/ intranet суттєво змінюється роль баз даних (БД) ─ зростає об’єм інформації та кількість одночасно працюючих користувачів, ускладнюється структура даних, зростає кількість різноманітних функцій. Змінюється уява про будову та керування додатками, які працюють з БД ─ вони містять бізнес-правила та опис логіки у вигляді сценарних мов, відіграють значну роль при формуванні активних та діагностичних Web-сторінок.

Відповідно змінюються вимоги до спеціалізованих систем, складовою частиною якої є БД. Такі вимоги можна реалізувати за допомогою систем керування базами даних (СКБД) яка істотно збільшує можливості і полегшує каталогізацію і ведення великих обсягів інформації, що зберігається в численних таблицях.

Сучасна система керування базами даних являє собою потужне інтегруюче середовище, яке потребує для роботи значних апаратних ресурсів та кваліфікованих спеціалістів.

Серед найбільш розповсюджених представників реляційних СКБД, що дуже широко використовуються в даний час у середніх і дрібних компаніях, можна відзначити MS SQL Server ─ це закінчене рішення для керування та аналізу даних, що дозволяє оперативно розгортати масштабуємі Web-додатки нового покоління, інтерактивні ділові додатки та сховища даних, забезпечує масштабуємість, необхідну для підтримки зростаючих, динамічних інформаційних середовищ.

Посібник містить у собі практичний матеріал, який забезпечує можливість отримання навиків роботі с СКБД MS SQL Server Server 2005. Розглянуто питання інсталяції и створення БД, що є необхідною складовою з автоматизації їх обробки.

-

Зміст

Лабораторна робота № 1.

Інсталяція та інтерфейс Microsoft SQL Server 2005………………………

Лабораторна робота № 1

Інсталяція та інтерфейс Microsoft SQL Server

Ціль роботи:ознайомитися з можливостями і супутнім програмним забезпеченням Microsoft SQL Server 2005; одержати базові навички адміністрування.

В SQL Server 2005 інформація зберігається в базах даних (БД). Вона організована в доступні користувачу логічні компоненти, а сама БД фізично реалізована у вигляді двох або більш файлів на диску. Варіант Microsoft SQL Server 2005 Express Edition SP2 має ряд обмежень, основні з яких представлені у табл. 1.

Табліця 1

Розходження між Microsoft SQL Server 2005 Enterprise Edition і Express Edition

* - існує додатковий сервіс (служба), що дозволяє забезпечити підтримку повнотекстового пошуку для Microsoft SQL Server 2005 Express Edition.

Для Microsoft SQL Server 2005 Enterprise Edition передбачена наявність великої кількості додаткових сервісів (наприклад, OLAP і т.п.) і програмного інструментарію, чого не передбачається для Microsoft SQL Server 2005 Express Edition.

При установці Microsoft SQL Server 2005 Express Edition здійснюється перевірка відповідності наявної апаратної й програмної частини мінімальним вимогам. Мінімальні апаратні вимоги для Microsoft SQL Server 2005 Express Edition наступні: Pentium III або сумісний процесор із частотою не менш 500 MHz, 192 MB ОЗУ (доступної під час інсталяції), наявність 2 GB вільного дискового простору на системному диску (хоча необхідний обсяг вільного дискового простору істотно менше). При невідповідності типу процесора мінімальним вимогам інсталяція буде заблокована, у той час як невідповідність інших вимог приведе лише до попередження. Вимоги до встановленого програмного забезпечення для інсталяції: NT-подібна операційна система (крім деяких 64-бітних ОС і Windows Vista Starter Edition), Internet Explorer 6.0 SP1, Microsoft Installer 3.1, Microsoft Data Access Components (MDAC) 2.8 SP1.

Microsoft SQL Server 2005 підтримує 2 способи аутентифікації: аутентифікація Windows, яка заснована на механізмі trusted connections і працююча тільки в NT подібних ОС, та SQL аутентифікація, що функціонує в будь-який ОС. За замовчуванням активна тільки аутентифікація Windows. Для того щоб зробити доступними обидва способи аутентифікації необхідно або в ключі реєстру

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode установити значення 0 або 2 і перезавантажитися; або в Microsoft SQL Server Management Studio Express викликати властивості з'єднання (рис. 1) на вкладці Security і вибрати там змішаний спосіб аутентифікації (SQL Server and Windows Authentication mode).

Пакет установки Microsoft SQL Server 2005 Express Edition не містить ніяких утиліт для адміністрування й написання SQL скриптів. Для цього необхідно встановити Microsoft SQL Server Management Studio Express. Цей програмний продукт містить у собі як можливості по адмініструванню, так і можливості за написанням SQL скриптів. З її допомогою можна набудовувати параметри самого SQL Server (з'єднання, використовувані ресурси, безпека й т.д. - рис. 1), адмініструвати / створювати / видаляти БД (рис. 2, 3, 4); змінювати привілеї та паролі існуючих користувачів / створювати нових користувачів / видаляти існуючих користувачів (рис. 5, 6, 7, 8); розробляти й виконувати SQL запити (рис. 9) та ін.

Рис. 1. Вкладка властивості поточного з'єднання з Microsoft SQL Server.

Розглянемо створення нової бази даних і створення нового користувача.

У кожного екземпляра SQL Server 2000 є чотири системних БД (master, tempdb, msdb і model) яки створюються відразу після інсталяції автоматично (видні у вікні Object Explorer у вкладці System Databases).і одна або декілько БД, призначених для користувача.

Для створення нової БД потрібно виконати наступні дії.

1. На вкладці Database (рис. 2) нажати праву кнопку миші й вибрати в спливаючому меню пункт New Database.

Рис. 2. Вкладка Database. Її розкриття приводить до виводу всіх баз даних, доступних Microsoft SQL Server.

2. У результаті вибору цього пункту меню з'являється діалогове вікно, що містить 3 вкладки, одна з яких зображена на рис. 3.

Рис. 3. Створення нової бази даних. Вкладка Data Files, що визначає ім'я файлу, що містить всі таблиці БД.

3. У першій вкладці міститься вікно, у яке потрібно ввести ім'я нової БД. У наступному вікні задається ім'я власника створюваної БД (за замовчуванням - default - це поточний активний користувач). Нижче перебуває вікно, у якому задаються імена файлу, що містить БД, і логу транзакцій, а також їхній початковий розмір. За замовчуванням імена збігаються з ім'ям БД, а їх розширення .mdf і .ldf, відповідно.

4. На 2-ій вкладці задаються властивості створюваної БД (рис. 4). У першому вікні знаходиться випадний список collation names, що представляє собою мовні розкладки з описом правил упорядкування символів у них. За замовчуванням обрана Server default (розкладка за замовчуванням). У наступному вікні задається режим (модель) відновлення даних. За замовчуванням − simple, що означає відсутність резервного копіювання логу транзакцій. У результаті вибору такої моделі, відновлення даних можливо тільки з використанням останньої резервної копії файлів даних. Такий режим відновлення корисний у випадку, якщо дані в базі використовуються тільки для читання, а також на етапі розробки й тестування БД, тому що мінімізуються витрати по керуванню резервним копіюванням логу транзакцій (його резервне копіювання взагалі не виробляється). У наступному вікні вибирається режим сумісності. За замовчуванням − 90, що означає сумісність із Microsoft SQL Server 2005. Режим сумісності означає підтримку синтаксичних можливостей тієї або іншої версії MS SQL Server. Нижче йде перелік параметрів БД, що набудовуються, та їх значення за замовчуванням, які можна змінювати.

5. На останній вкладці Filegroups вибираються файлові групи, до яких відносяться файли БД (якщо для БД визначене більше одного файлу даних).

Рис. 4. Створення нової бази даних. Вкладка Options, що визначає

властивості створюваної БД.

Створення нового користувача. Ця операція виконується в такий спосіб.

1. Розкривається вкладка Security (рис. 2) і вибирається пункт Logins.

2. Натискається права кнопка миші й вибирається пункт меню New Login...

3. При виборі цього пункту з'являється діалогове вікно, що складається із трьох вкладок, перша з яких представлена на рис. 5.

Рис. 5. Діалогове вікно для створення нового користувача. Вкладка General.

4. На вкладці General вибирається ім'я користувача (login); тип аутентифікаціи (аутентифікація Windows побудована на механізмі trusted connections і використовує користувачів операційної системи, або аутентифікація SQL Server (потрібно вказати пароль)); поточна база даних при підключенні до SQL Server (за замовчуванням - master); мова. Якщо при першому підключенні для створюваного логіна немає необхідності зміни пароля, то необхідно прибрати галочку з опції Enforce password expiration.

5. Наступна вкладка Server Roles(рис. 6) дозволяє комплексно вибрати привілею для нового користувача.

6. Вкладка User Mapping (рис. 7) дозволяє виконувати тонке настроювання привілеїв нового користувача для кожної з існуючих БД. Для того, щоб користувач мав доступ до БД потрібно біля відповідного імені БД у верхнім вікні поставити галочку, що автоматично дає користувачеві привілей public (сама по собі не дає можливості виконувати SQL команди), інакше не буде можливості вибрати БД як поточну або звертатися до її об'єктів. Варто звернути увагу, що до БД master користувач має доступ у кожному разі. Крім привілею public, що не може бути скинута, розглянемо наступні привілеї (ролі):

· db_datareader − дозволяє виконувати команду SELECT;

· db_datawriter − дозволяє виконувати команди INSERT, UPDATE, DELETE;

· db_ddladmin − дозволяє виконувати команди DDL (Data definition Language) : CREATE, ALTER, DROP, TRUNCATE;

· db_owner − поєднує всі перераховані вище привілеї (ролі).

Рис. 6. Діалогове вікно для створення нового користувача. Вкладка Server Roles.

Рис. 7. Діалогове вікно для створення нового користувача.

Вкладка User Mapping.

7. Вкладка Status (рис. 8) дозволяє дозволити або заборонити для створюваного користувача підключатися до БД, а також активувати/деактивувати створюваний логін.

8. Після натискання кнопки OK створення нового користувача вважається завершеним.

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

Після інсталяції Microsoft SQL Server 2005 створюється користувач, що має ім'я (login) sa і обраний при інсталяції пароль, що не може бути «порожнім», він має права адміністратора.

Рис. 8. Діалогове вікно для створення нового користувача. Вкладка Status.

Виконання SQL запитів здійснюеться за допомогою Microsoft SQL Server Management Studio Express. Для цього необхідно нажати на елемент New Query, у результаті з'явиться вікно, зображене на рис. 9. У цьому вікні можна написати SQL запит, який виконається шляхом натискання на елемент Execute на панелі інструментів або на клавішу F5.

Рис. 9. Вікно для створення SQL запитів.

Завдання для самостійної роботи

1. Установити Microsoft SQL Server 2005 Express Edition і Microsoft SQL Server Management Studio Express.

2. Настроїти змішану (Windows + SQL Server) аутентифікацію.

3. Створити базу даних, що буде називатися Student_Name, де замість Name буде написане прізвище латинськими буквами.

4. Створити користувача, ім'я (login) якого збігається із прізвищем студента, написаної латинськими буквами. Цей користувач повинен мати привілей (роль) для доступу до однойменного БД або db_owner, або зв'язування db_datareader, db_datawriter, db_ddladmin і одноіменну базу за замовчуванням.

Перевірити можливість з'єднання з Microsoft SQL Server 2005 Express Edition, використовуючи створеного користувача.

Лабораторна робота № 2

Розробка реляційної бази даних

Ціль роботи:розробити логічну структуру реляційної БД, що зберігає інформацію про студентів.

Завдання

Створити логічну структуру БД, що, буде складатися з декількох таблиць, які містять наступну інформацію про студентів:

1. Прізвище.

2. Ім'я.

3. По батькові.

4. Стать.

5. Дата народження.

6. Адреса (повна).

7. Телефон.

8. Навчальна група (може бути декілька).

9. Вид оплати за навчання (бюджетне або контрактне).

10. Вид навчання (очне, заочне, денне, вечірнє, екстерн).

11. Для військовозобов'язаних - інформація про придатність / непридатність до служби в армії.

12. Список вивчених предметів з оцінками по цих предметах.

13. Список наукових статей (у кожної наукової статті може бути кілька авторів).

14. Список тез, опублікованих у збірниках тез наукових конференцій (у тез доповідей на конференції може бути кілька авторів).

15. Інформація про викладачів, які є співавторами статей і тез студентів. Ця інформація повинна містити в собі: прізвище, ім'я, по батькові, ступінь, звання, посаду.

При створенні БД, слід враховувати, що одна і та сама людина може бути студентом 2-ох (або більше) навчальних груп.

Вищевказана інформація в БД повинна бути представлена у вигляді набору таблиць (реляційна БД), кожна з яких повинна задовольняти нормальним формам (обов'язково 4-рьом першим: 1-ій, 2-ій, 3-їй, Бойса-Кодда).

Дані, що зберігаються в одній таблиці, групуються разом по сукупності значеннєвих ознак з погляду мінімізації витрат на зберігання інформації та гнучкості при подальшій модифікації структури даних. Типи даних вибираються на підставі оптимуму між надмірністю і можливістю зберігання необхідної інформації.

Таблиці, якщо буде потреба, повинні бути пов'язані з використанням зовнішніх ключів.

Кількість таблиць не обмежується, вона вибирається виходячи з вимог до реляційних баз даних і з погляду подальшого розширення переліку збереженої інформації про студентів. Побудова таблиць повинна здійснюватися з урахуванням можливості подальшої модифікації структури даних.

Лабораторна робота № 3

Створення та наповнення таблиць БД

Ціль роботи:створення таблиць, що утворюють БД та наповнення їх даними.

Теоретична частина

/*

Приклад створює й заповнює даними частину таблиць, необхідних для створення БД, структуру якої необхідно було розробити в лабораторній роботі № 2.

Представлені таблиці мають спрощену структуру, тобто містять не всі дані, які необхідно зберігати відповідно до завдання л.р. №2.

Слід зазначити, що великі та маленькі букви в ключових словах, назвах таблиць і стовпців не різняться.

Приклад створений у припущенні, що існує тільки один простір імен у поточної БД, що може містити таблиці з використовуваними іменами.

*/

if NOT exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SUBJECTS')

/*

Перевіряється: чи існує вибірка з подання TABLES, власником якого є користувач INFORMATION_SCHEMA, поля TABLE_NAME, значення якого повинне рівнятися SUBJECTS. Для одержання потрібного значення поля TABLE_NAME використовується команда DML SELECT. Для її використання користувач повинен мати привілей dm_datareader доступу до бази даних, що містить шукану таблицю.

Зміст цього оператора мови TSQL полягає в перевірці того, чи існує таблиця з ім'ям SUBJECTS. Якщо така таблиця не існує, то виконується її створення і наповнення даними.

Якби існувало кілька просторів імен, то варто було б використовувати такий оператор if:

if not exists(select NAME from SYS.OBJECTS where NAME = 'SUBJECTS'

and TYPE = 'U'

and SCHEMA_ID = (select SCHEMA_ID from SYS.SCHEMAS

where NAME = 'name_of_schema'

)

)

деname_of_schema– простір імен, що цікавить.

*/

BEGIN

/*

Використовуються операторні дужки BEGIN...END, тому що, цілий блок операторів повинен розглядатися як єдина група операторів.

*/