Лабораторная работа №3 «создание физической модели данных»

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

Постановка задачи

• Создать базу данных в Microsoft SQL Server

• Создайте таблицы: две таблицы с помощью sql, остальные таблицы в среде Management Studio.

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

• Создать схемы базы данных

• Заполнить таблицы данными (минимум 10 записей в таблице)

• Создать ограничения целостности: 3 домена, 3 правила и 5 значений по умолчанию.

Методические указания

1. Создание и администрирование базы данных

Запустите Microsoft SQL Server Management Studio и авторизуйтесь.

Рисунок 3.1 - Microsoft SQL Server Management Studio

Создание базы данных с помощью sql кода в среде Management Studio

Оператор CREATE DATABASE

Оператор имеет следующий синтаксис:

CREATE DATABASE database_name [ ON     [ < filespec > [ ,...n ] ]     [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] [ COLLATE collation_name ] [ FOR LOAD | FOR ATTACH ]

< filespec > ::=

[ PRIMARY ] ( [ NAME = logical_file_name , ]     FILENAME = 'os_file_name'     [ , SIZE = size ]     [ , MAXSIZE = { max_size | UNLIMITED } ]     [ , FILEGROWTH = growth_increment ] ) [ ,...n ]

< filegroup > ::=

FILEGROUP filegroup_name < filespec > [ ,...n ]

Параметры, используемые в синтаксисе описания CREATE DATABASE:

1. database_name

Общее название базы данных

2. ON PRIMARY

Задает группу файлов, к которой относится файл базы данных.

3. NAME

Определяет логическое имя для физического файла базы данных на жестком диске

4. FILENAME

Определяет адрес и имя файла, которые содержат данные базы данных на жестком диске.

5. SIZE

Определяет размер файла базы данных. Вводимое значение определяется как в килобайтах так и в мегабайтах. По умолчанию используется размер базы данных model. Для указания размерности после введенного числа следует добавить MB или KB.

6. MAXSIZE

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

7. FILEGROWTH

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

8. LOG ON.

Задает расположение файла журнала транзакций и его размер

9. COLLATE

Определяет способ сортировки в базе данных.

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

На практике рекомендуется использовать следующий набор файлов:

NAME

FILENAME для файлов данных

SIZE

FILENAME для файлов журнала транзакций

SIZE

Пример: Создание базы данных объемом 15 Мб

USE master

GO

CREATE DATABASE SampleBD

ON

( NAME = SampleBD_data,

FILENAME = 'C:\Users\Администратор\Documents\SampleBD.mdf',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

( NAME = SampleBD_log,

FILENAME = 'C:\Users\Администратор\Documents\SampleBD.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB )

GO

Комментарий:

База данных master содержит специальный набор таблиц (системные таблицы), которые содержат информацию о системе. Создаваемая база имеет имя SampleBD и состоит из файла данных SampleBD.mdf и файла журнала транзакций SampleBD.ldf.

Создадим базу данных SampleBD в SQL Server Management Studio. Для этого новый запрос (New Query) рисунок 3.2.

Выполнение кода

Проверка кода

Рисунок 3.2 - New Query

• Для проверки кода на наличие ошибок нажатием на значок ;

• Для того чтобы выполнить код нажмем .

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

Рисунок 3.3 – Проверка sql кода создания базы данных

Для того чтобы созданная база данных (SampleBD) появилась в списке баз данных нужно перезагрузить сервер.

Рисунок 3.4 – Выполнение sql кода создания базы данных

Найдите только что созданную базу данных в списке баз данных.

Создание базы данных в среде Management Studio

Правой кнопкой по объекту Databases, пункт меню New Database… (Рисунок 3.5)

Рисунок 3.5 – Пункт меню “Новая база данных”

Рисунок 3.6 – Создание новой базы данных

С основными настройками базы данных разобраться самостоятельно.

Создание таблиц sql-скриптом

Синтаксис

CREATE [TEMPORARY] TABLE [IF NOT EXISTS]

имя_таблицы [(определение_столбца,...)]

[опции_таблицы] [select_выражение]

Определение столбца

имя_столбца тип [NOT NULL | NULL]

[DEFAULT значение_по_умолчанию]

[AUTO_INCREMENT][PRIMARY KEY]

[reference_definition]

Пример sql кода создания таблицы Автор

CREATE TABLE dbo.author

(

id_author int NOT NULL IDENTITY (1, 1) PRIMARY KEY,

first_name nvarchar(50) NOT NULL,

second_name nvarchar(50) NULL,

last_name nvarchar(50) NOT NULL,

birthday date NULL

) ON [PRIMARY]

GO

Проверим написанный код

Рисунок 3.7 – Проверка sql кода создания таблицы

Ошибок не обнаружено, выполним sql код. Появилось сообщение, что команда выполнена. После того как мы сделали Refresh у нас появилась новая таблица dbo.author

Создание таблиц в среде Management Studio

В базе данных SampleBD создадим таблицу

Рисунок 3.8 – Пункт меню “Новая таблица”

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

Для столбца код автора (id_author) укажем, что оно ключевое и не может быть пустым. Так же укажем что столбец код автора автоинкрементный для этого в свойствах столбца (Column Properties) укажем в Identity Specification YES, Identity Increment 1.

Рисунок 3.9 – Создание столбцов таблицы

Сохраним таблицу указам ее имя author. После того как мы сделали Refresh у нас появилась новая таблица dbo.author

На основании созданной таблицы dbo.author, Management Studio дает возможность сгенерировать sql код для работы с данной таблицей:

Рисунок 10 – Пункт меню “Создание скрипта

Рисунок 3.11 – Сгенерированный sql код

Создание схемы базы данных в среде Management Studio

Создадим схему базы данных

Выберем таблицы

Рисунок 13 – Выбор таблиц для связи

Рисунок 3.12 – Пункт меню “Создание схемы базы данных”

Рисунок 3.14 – Схема базы данных с несвязанными таблицами

Для того чтобы связать таблицы book и author связью один ко многим, в таблице book должен быть внешний ключ (id_author) для осуществления связи с таблицей author (его ключом id_book).

Указателем мыши захватим ключ id_autor в таблице autor и перетащим его на внешний ключ id_author в таблице book.

После этого система нам сообщит что связь создана между двумя таблицами.

Рисунок 3.15 – Сообщение о установке связи между таблицами

В окне Foreign Key Relationship можем произвести настройки связи. Установим правила для добавления и удаления записей, а именно каскадное удаление и добавление записей.

Рисунок 3.16 – Окно с настройками связи

Посмотрим на получившуюся схему данных.

Рисунок 3.17 – Схема базы данных

Заполнение таблиц данными

Заполним созданные таблицы данными

Рисунок 3.18 –Редактирование данных таблицы