Лабораторная работа №4. Создание базы данных и ее объектов с помощью команд языка transact-sql

 

Цель работы

1. Ознакомиться с процессом создания базы данных и ее объектов

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

Задачи

1. Создание новой базы данных.

2. Создание правил, умолчаний, пользовательских типов данных.

3. Создание таблиц базы данных, индексов и представлений.

4. Ввод данных в таблицы базы данных.

5. Предоставление доступа к серверу учетным записям Windows.

6. Создание новых пользователей и пользовательских ролей.

7. Предоставление привилегий доступа к объектам базы данных.

 

Методические указания и задания к выполнению

 

Раздел I. Создание базы данных и ее объектов

 

Предварительно при помощи пользовательского меню Windows запустите утилиту SQL Server Management Studio, после чего на панели Object Explorer в древовидной структуре раскройте папку Databases.

Далее на панели инструментов нажмите кнопку New Query, что вызовет появление на экране окна создания запросов к базе данных. Это окно, называемое окном Query, мы будем использовать для последовательного формирования в нем сценария (скрипта), который далее сохраним в файле на диске. Сценарий представляет из себя последовательность команд языка Transact-SQL, с помощью которых можно будет впоследствии автоматически сгенерировать базу данных со всеми ее объектами, а также наполнить ее таблицы данными и выполнить ряд других действий. С помощью сценария мы будем создавать такую же базу данных, что была создана средствами графического интерфейса утилиты в лабораторной работе 5.

Создание сценария заключается в том, что каждый раз мы будем добавлять в окно Query очередную команду языка Transact-SQL (или группу команд, образующих пакет), проверять ее синтаксис и затем выполнять, нажимая на панели инструментов кнопку Execute (или клавишу F5). При этом необходимо, чтобы данная команда (или пакет) была выделена подсветкой, иначе будут выполнены все без исключения команды, содержащиеся в окне Query. Если очередная команда (или пакет) будет успешно выполнена, то в окно Query можно дописать новую команду (или пакет), выделить подсветкой и далее повторить процесс, описанный выше. В конечном итоге, путем поочередного выполнения команд, будет создана база данных со всеми ее объектами и параллельно в окне Query будет сформирован сценарий, который можно сохранить в файле на диске. Теперь с помощью этого сценария можно на любом компьютере, где установлен MS SQL Server, выполнить процедуру создания базы данных.

Примечание. Готовые к выполнению команды языка Transact-SQL, с помощью которых будет создаваться база данных, и из которых будет формироваться сценарий, выделены ниже при помощи стрелок É и Ç.

 

1. Создание новой базы данных. Синтаксис команды (в нотации Бэкуса-Наура):

 

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 ]

 

На русском языке подробное разъяснение синтаксических конструкций этой команды приведено в [1], стр. 892.

Конкретная команда создания базы данных Склад_ХХХ, которую нужно вставить в окно Query, имеет следующий вид:

É

CREATE DATABASE Склад_ХХХ -- Вместо ХХХ подставьте свою комбинацию цифр

ON PRIMARY -- Путь D:\Work\X7230XXX\ к файлам

(NAME = Склад_Data, -- базы данных уже должен существовать

FILENAME = 'D:\Work\X7230XXX\Склад_XXX_Data.mdf',

SIZE = 3MB,

MAXSIZE = 50MB,

FILEGROWTH = 2MB),

FILEGROUP Secondary

(NAME = Склад2_Data,

FILENAME = 'D:\Work\X7230XXX\Склад_XXX_Data2.ndf',

SIZE = 2MB,

MAXSIZE = 70MB,

FILEGROWTH = 20%),

(NAME = Склад3_Data,

FILENAME = 'D:\Work\X7230XXX\Склад_XXX_Data3.ndf',

SIZE = 2MB,

FILEGROWTH = 5MB)

LOG ON

(NAME = Склад_Log,

FILENAME = 'D:\Work\X7230XXX\Склад_XXX_Log.ldf',

SIZE = 1MB,

MAXSIZE = 10MB,

FILEGROWTH = 15%),

(NAME = Склад2_Log,

FILENAME = 'D:\Work\X7230XXX\Склад_XXX_Log2.ldf',

SIZE = 512KB,

MAXSIZE = 5MB,

FILEGROWTH = 10%)

GO

Ç

Примечание. Команда GO используется для указания конца пакета (см. [1], стр. 1087). Команды, образующие пакет, обрабатываются сервером за один раз, после чего он возвращает клиенту (в нашем случае – пользователю SQL Server Management Studio) результат. После этого клиент может отправить следующий пакет и т.д. В данном случае пакет содержит всего одну команду, предназначенную для создания базы данных.

 

2. Подключение к базе данных. Синтаксис команды (см. [1], стр. 1086):

 

USE { database }

 

É

USE Склад_ХХХ

GO

Ç

Примечание. При обращении к объектам текущей базы данных (т.е. выбранной с помощью команды USE) не требуется указание ее имени. Но когда работа ведется в контексте иной базы данных, то необходимо указывать также имя базы данных.

 

3. Создание правила. Синтаксис команды (см. [1], стр. 836):

 

CREATE RULE rule AS condition_expression

 

É

CREATE RULE Logical_Rule AS @value IN ('Нет', 'Да')

GO

Ç

 

4. Создание умолчания. Синтаксис команды (см. [1], стр. 828):

 

CREATE DEFAULT default AS constant_expression

 

É

CREATE DEFAULT Logical_Default AS 'Нет'

GO

Ç

 

5. Создание пользовательского типа данных. Синтаксис системной хранимой процедуры (см. [1], стр. 840):

 

sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[, [ @nulltype = ] ' null_type ' ]
[, [ @owner = ] ' owner_name ' ]

 

É

EXEC sp_addtype Logical, 'char(3)', 'NOT NULL'

GO

Ç

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

 

[ [ EXEC [ UTE ] ]
{
[ @return_status = ]
{ procedure_name [; number ] | @procedure_name_var
}
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] [,... n ]
[ WITH RECOMPILE ]

 

6. Связывание правила с пользовательским типом данных. Синтаксис системной хранимой процедуры (см. [1], стр. 837):

 

sp_bindrule [ @rulename = ] ' rule ',
[ @objname = ] ' object_name '
[, [ @futureonly = ] ' futureonly_flag ' ]

 

É

EXEC sp_bindrule 'Logical_Rule', 'Logical'

GO

Ç

 

7. Связывание умолчания с пользовательским типом данных. Синтаксис системной хранимой процедуры (см. [1], стр. 829):

 

sp_bindefault [ @defname = ] ' default ',
[ @objname = ] ' object_name '
[, [ @futureonly = ] ' futureonly_flag ' ]

 

É

EXEC sp_bindefault 'Logical_Default', 'Logical'

GO

Ç

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

 

8. Создание таблицы базы данных. Синтаксис команды (см. [1], стр. 958):

 

CREATE TABLE
[ database_name. [ owner ]. | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint >::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [,... n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition >::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ (seed, increment) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [... n ]

< column_constraint >::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON { filegroup | DEFAULT} ] ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}

 

< table_constraint >::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [,... n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [,... n ] ) ]
REFERENCES ref_table [ ( ref_column [,... n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}

 

É

/* Регион */

CREATE TABLE Регион ( /* первая команда пакета */

КодРегиона INT PRIMARY KEY,