Цель работы– изучение команд и системных хранимых процедур для создания, изменения структуры, просмотра свойств и удаления таблиц, а также приобретения навыков применения указанных команд и процедур, средств утилиты Enterprise Manager и мастера Create Database Diagram Wizard для создания и просмотра диаграммы баз данных.
Данные баз данных хранятся в таблицах. Таблица представляет собой совокупность столбцов (полей), в которых хранятся атрибуты объектов предметной области. Все значения атрибутов какого-либо объекта образуют строку (запись). Любая пользовательская таблица создается либо командой Transact-SQL CREATE TABLE, либо с помощью графической утилиты Enterprise Manager. И в том, и в другом случае необходимо задать имя таблицы, перечислить имена столбцов, задать тип данных для каждого столбца, упорядоченность символов для сортировки символьных данных, значения по умолчанию, а также ограничения на столбцы или таблицу в целом. Типы данных для таблиц и виды ограничений рассмотрены в лабораторной работе №2. При открытии папки Tables базы данных в правом окне Enterprise Manager появляется список таблиц со следующими атрибутами: имя таблицы, владелец (обычно владелец базы данных data base owner-dbo), тип (пользовательская или системная) и дата создания таблицы. Для просмотра самой таблицы следует дважды щелкнуть по ней или в ее контекстном меню исполнить команду Properties. В открывшемся окне представлены все свойства таблицы и ее столбцов. Кнопка Permission позволяет просмотреть и отредактировать права доступа к таблице: SELECT, INSERT, UPDATE, DELETE, EXES и DRI. Щелкнув по столбцу таблицы, можно просмотреть и отредактировать права доступа к столбцу. Если установлена на сервере служба MSSearch, то с помощью вкладки Full-text Indexing можно отредактировать параметры этой службы.
При проектировании таблиц часто бывает необходимо обеспечить автоматическое генерирование уникальных значений. Для этих целей можно использовать один из следующих способов:
а) для столбца задать ограничение целостности IDENTITY с двумя параметрами: начальное значение и шаг приращения; это обеспечит автоматическое создание нового значения при каждой очередной вставке строки;
б) использовать столбцы со свойством timestamp, которое обеспечит для столбца генерирование значений, уникальных в пределах базы данных;
в) задать для столбца свойство rowquidcol, что обеспечит генерирование для каждой новой строки глобального уникального идентификатора, занимающего 16
байт.
Изменить структуру таблицы можно либо с помощью команды ALTER TABLE, либо с помощью утилиты Enterprise Manager, при этом следует соблюдать ряд ограничений: нельзя удалять столбцы с типом данных image, text, ntext, timestamp, rowquidcol, вычисляемые столбцы, индексные столбцы и т.д. Командой DELETE TABLE и той же утилитой можно удалить любую таблицу. Но прежде, чем это сделать, необходимо удалить все объекты базы данных, которые ссылаются на данную таблицу, либо изменить их таким образом, чтобы они не ссылались на удаляемую таблицу. Для удаления таблицы средствами утилиты Enterprise Manager необходимо в контекстном меню таблицы, которую надо удалить, исполнить команду Delete, убедиться, что удаляемая таблица не имеет связей с какими-либо другими объектами базы данных, и затем удалить таблицу.
Чтобы получить информацию о таблице, необходимо выполнить следующую хранимую процедуру:
sp_help имя таблицы.
После исполнения этой команды на экране появляется целый ряд информационных таблиц: таблица с общей информацией, таблица со свойствами колонок, таблица с ограничением IDENTITY, таблица с информацией о размещении на файлах, таблица с информацией об индексах, таблица с данными об ограничениях, таблица с информацией о ссылающихся таблицах (см. sp_depends и sp_keys).
Задание 1.С помощью утилиты Enterprise Manager создать таблицу требуемо структуры, выполнив следующие действия:
1. Выберите в левом окне Enterprise Manager базу данных, в которой планируется создание новой таблицы, дважды щелкнув по ней с помощью левой клавиши мыши.
2. Выберите элемент Table в указанной базе данных.
3. Один раз щелкните правой клавишей мыши по выделенному элементу или на свободном пространстве правого окна.
4. В контекстном меню выберите элемент New Table. (Вместо пунктов 2,3 и 4 можно было исполнить команду Action/New Table).
5. В открывшемся окне для создания новой таблицы необходимо указать названия столбцов, тип данных из раскрывающегося списка, размер для типов char, nvarchar, binary,varchar и nchar, а также задать флажок в графе Allow Nulls для тех колонок, в которых допускаются значения NULL.
6. Выбирая по очереди столбцы в верхней части окна, задайте в нижней части окна дополнительные параметры для этих столбцов:
Description – текстовое описание соответствующего столбца;
Default Value – значение по умолчанию;
Precision – точность p для типа numeric или decimal: Scale – масштаб, т.е. число цифр после точки; Identity = Yes – для столбца – счетчика;
Identity Seed – начальное значение счетчика;
Identity Increment – шаг приращения;
Is RowGuid = Yes – для колонки с глобальными идентификаторами;
Formula – формула для вычисления значений столбца;
Collation – сопоставление для столбца.
7. Сохранить новую таблицу в базе данных, щелкнув по кнопке Save и задав ей требуемое имя.
Задание 2.Показать сценарий создания новой таблицы, исполнив команду All
Tasks/Generate SQL Scripts ее контекстного меню, и сохранить в файле …Abc\Tabl Script .sql.
Задание 3.Для демонстрационных баз данных Pubs и Northwind создать их диаграммы,
используя мастер создания диаграмм Create Database Diagram Wizard:
1. Выберите базу данных Pubs (гостиницы).
2. Откройте контекстное меню базы данных.
3. Исполните команду New Database Diagrams.
4. Ознакомьтесь с порядком создания диаграмм:
а) создать новую диаграмму и включить в нее требуемые
таблицы в соответствии с поставленными целями просмотра базы данных;
б) добавить в диаграмму таблицы, связанные с выбранными;
в) упорядочить таблицы на диаграмме, что делается автоматически.
5. Щелкните по кнопке Next.
6. В левой части окна по очереди выбирайте требуемые таблицы из списка всех таблиц базы данных Pubs и щелкайте по кнопке Add. Кнопка Remove позволяет исключить таблицу из выбранного списка.
7. Установите флажок Add related tables automatically для автоматического добавления связанных таблиц.
8. Щелкните по кнопке Next.
9. Просмотрите список выбранных и связанных таблиц и если требуется его корректировка, то возвратитесь назад.
10. Щелкните по кнопке Next.
11. Созданная диаграмма заносится в папку Diagrams базы данных Pubs.
12. Откройте папку Diagrams базы данных Pubs.
13. Выберите новую диаграмму и для ее просмотра дважды щелкните на ней.
14. Убедитесь, что таблицы можно просматривать в различных режимах (Show), редактировать структуру (Column Property), отношения, ограничения целостности, индексы и ключи (Properties), модифицировать триггеры (Task/Manage Trigger), управлять доступом к таблице (Task/Manage Permissions), выяснять зависимости (Task/Display Dependencies), сгенерировать сценарий создания таблицы (Task/Generate SQL Scripts).
15. Выполните пункты 1-14 для базы данных Northwind.
Задание 4.Создать три взаимосвязанные таблицы jobs (работы), employee (служащий) и publishers (издатели), используя необходимые ограничения, исполнив следующие Transact-SQL команды:
CREATE TABLE jobs
(job_id smallint IDENTITY (1,1) PRIMARY KEY CLUSTERED, job_desc varchar (so) NOTT NULL
DEFAULT ‘New Position – title not formalized jet’, min_lul tinyint NOT NULL CHECK (min_lul > = 10), max_lul tinyint NOT NULL CHECK (max_lul > = 250)
)
CREATE TABLE employee
(emp_id CONSTRAINT PK_emp_id PRIMARY KEY NOTICLUSTERED
CONSTRAINT PK_emp_id CHECK (emp_id LIKE
‘[A-Z][A-Z][A-Z][1-9][0-9][0-9][FM]’ or
‘[A-Z]-[A-Z][1-9][0-9][0-9][0-9][FM]’, fname varchar (20) NOT NULL,
minit char (1) NULL,
lname varchare (30) NOT NULL,
job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs (job.id), job_lul tinyint DEFAULT 10,
pub_id char (4) NOT NULL DEFAULT(‘9952’)
REFERENCES publishers (pub_id),
hire_date datetime NOT NULL varchar (40) NULL, (getdate ())
)
CREATE TABLE publishers
(pub_id char (4) NOT NULL
CONSTRAINT OPKL_pubid PRIMARY KEY CLOSTERD CHEC (pub_id IN (‘1389’, ‘0736’, ‘0877’, ‘1622’, ‘1756’
OR pub_id LIKE ‘99[0-9][0-9]’), pub_name varchar (40) NULL,
city varchar (20) NULL, state char (2) NULL,
country varchar (30) NULL DEFAULT (‘OSA’)
)
Задание 5.Создать таблицу с глобальным идентификатором, выполнив команду: CREATE TABLE GUIDTAB
(guid uniqueidentifier CONSTRAINT GuidDefault DEFAULT NEWID (), Emploec_Name varchar (60) CONSTRAINT Guid_PK PRIMARY KEY (guid)
)
Задание 6.Создать таблицу с вычисляемым столбцом, исполнив команду: CREATE TABLE My Table
(low int, high int,
myavg AS (low + hvgh)/2
)--myuser-name AS USER_NAME()
Задание 7.Создать временную локальную и временную глобальные таблицы и найти их в системной базе tempdb.
Задание 8.Создать программную таблицу, объявив переменную типа Table, и заполнить ее данными. Создать аналогичную таблицу в базе данных и переписать в нее содержимое программной таблицы. Написать пакет команд для сравнительного анализа эффективности работы с этими двумя таблицами.
Задание 9.Получить информацию о свойствах таблицы: ее название, сведения о владельце, дата создания, колонки-счетчики с ограничением IDENTITY, колонки с глобальными идентификаторами, группа файлов для хранения данных, сведения об индексах, сведения об
ограничениях, сведения о таблицах, на которые ссылается данная таблица с помощью ограничения
FOREIGN KEY. Для получения указанных свойств исполнить команду:
EXEC sp_help@objname = Имя таблицы (например job)
Задание 10.Получить информацию об объектах, зависимых от таблицы orders базы данных Northwind, исполнив команду:
EXEC sp_depends orders
Задание 11.Используя хранимую системную процедуру sp_fkeys, получить информацию о таблицах, связанных с данной через ограничения PRIMARY KEY и FOREIGN KEY.
Задание 12.Дополнить таблицу AllTypes базы данных Proba новыми колонками и занести в них значения по умолчанию.