Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
1 Цель работы
В данной лабораторной работе рассматривается
2 Рабочее задание
Выполнить Методические указания к выполнению упражнений по основам создания таблиц базы данных по пунктам.
3 Предварительная подготовка
1. Ознакомление с концепцией модели реляционных баз данных
Физическая модель базы данных включаетв себя такие вещи, как файлы и группы файлов. Физическая модель отражает, как данные хранятся и какой способ доступа к данным наиболее эффективен. Рассмотрим реализацию логической модели базы данных, которая включает в себя такие объекты, как таблицы и взаимоотношения между ними. Логическая модель только моделирует структуры реальных данных – она не заботится и не зависит от конкретной реализации Системы управления базами данных. Например, можно использовать одну и ту же логическую модель как для создания баз данных в сервере SQL, так и в программе Access и можно думать о них, как о совершенно одинаковых базах данных, настолько, насколько глубоко описана логическая модель. Однако физическая модель этих двух баз данных будет значительно отличаться – база данных программы Access может быть представлена в виде всего одного файла, в то время как база данных сервера SQL будет состоять из нескольких различных файлов, иногда распределенных между несколькими жесткими дисками.. Важно отметить здесь, что физическая реализация базы данных и логическая реализация представляют собой отдельные концепции, которые являются, в большой мере, независимыми друг от друга.
Сервер SQL является системой управления реляционными базами данных (RDBMS – Relational Database Management System). Поэтому базы данных, с которыми вы будет работать, это реляционные базы данных.
2. Ознакомление с типами данных сервера SQL
Для каждого столбца вашей базы данных должен быть назначен «тип данных», определяющий, какие данные хранятся в данном столбце. Кроме того, тип данных отвергает данные некорректного типа (т.е. попытка сохранить букву в столбце, содержащем числа, будет отклонена). Сервер SQL поддерживает 25 различных типов данных – некоторые из них имеют больше различных свойств, чем другие. Давайте посмотрим на типы данных и на свойства каждого из них (большая часть этой таблицы является копией из справочной системы сервера SQL – Books Online):
|
Тип данных |
Размер |
Описание |
|
Целочисленные |
|
|
|
bigint |
8 байтов |
Хранит численные (целые числа) данные в интервале от -2^63 (-9,223,372,036,854,775,808) до 2^63-1 (9,223,372,036,854,775,807). |
|
int |
4 байта |
Хранит численные (целые числа) данные в интервале от -2^31 (-2,147,483,648) до 2^31 - 1 (2,147,483,647). |
|
smallint |
2 байта |
Хранит численные данные в интервале от -2^15 (-32,768) до 2^15 - 1 (32,767). |
|
tinyint |
1 байт |
Хранит численные данные в интервале от 0 до 255. |
|
bit |
1 байт для 8-битного столбца |
Хранит численные данные со значением 1 или 0. Для хранения каждого набора из 8 бит требуется 1 байт. |
|
Десятичные |
|
|
|
decimal |
В пределах от 5 до 17 байт в зависимости от точности |
Хранит числа фиксированной длины и точности. Когда используется максимальная точность, допустимые значения лежат в диапазоне от - 10^38 +1 до 10^38 - 1. |
|
numeric |
- |
Такой же, как тип данных decimal |
|
Денежные |
|
|
|
money |
8 байтов |
Хранит денежные значения в диапазоне от -2^63 (-922,337,203,685,477.5808) до 2^63 - 1 (922,337,203,685,477.5807), с точностью до одной десятитысячной от значения денежной единицы |
|
smallmoney |
4 байта |
Хранит денежные значения в диапазоне от -2^63 (-922,337,203,685,477.5808) до 2^63 - 1 (922,337,203,685,477.5807), с точностью до одной десятитысячной от значения денежной единицы |
|
С плавающей запятой |
|
|
|
float(n) |
В пределах от 4 до 8 байтов в зависимости от точности |
Хранит численные данные с плавающей запятой в интервале от -1.79E + 308 до 1.79E + 308. Значение n является числом битов, используемых для хранения мантиссы и может иметь значение от 1 до 53 |
|
real |
4 байта |
Хранит численные данные с плавающей запятой в интервале от -3.40E + 38 до 3.40E + 38. Real – это то же самое, что и float(24). |
|
Дата и время |
|
|
|
datetime |
8 байтов |
Хранит данные о дате и времени от 1 января 1753 года до 31 декабря 9999 года с точностью до 1/300 секунды или 3.33 миллисекунды. |
|
smalldatetime |
4 байта |
Хранит данные о дате и времени от 1 января 1753 года до 31 декабря 9999 года с точностью до 1/300 секунды или 3.33 миллисекунды. |
|
Символьные - |
не-Unicode |
|
|
char(n) |
n байтов |
Хранит не-Unicode символьные данные фиксированного размера (длина – n символов), где n может принимать значения от 1 до 8000. Если длина записи меньше чем n символов, то все равно будет задействовано n байтов для того, чтобы запись была заданного (n байт) размера |
|
varchar(n) |
Зависит от размера введенных данных – 1 байт на один символ |
Хранит не-Unicode символьные данные нефиксированного размера (длина – n символов), где n может принимать значения от 1 до 8000. Хранимый размер равен действительной длине записи в байтах, а не n байтов. |
|
text |
16 байтов для указателя |
Хранит не-Unicode данные нефиксированного размера с максимальной длиной 2^31 - 1 (2,147,483,647) символов. Текстовая запись может содержать 2^31 - 1 символ. Указатель определяет расположение значения данных, данные хранятся отдельно от таблицы данных |
|
Символьные - |
Unicode |
|
|
nchar(n) |
2 байта * n |
Хранит Unicode данные фиксированного размера (длина – n символов), где n может принимать значения от 1 до 4000. Unicode использует 2 байта на символ и может поддерживать все международные символы. Если длина записи меньше чем n символов, то все равно будет задействовано n байтов для того, чтобы запись была заданного (n байт) размера |
|
nvarcher(n) |
Зависит от размера введенных данных – 2 байта на символ |
Хранит Unicode данные переменной длины (длина – n символов), где n может принимать значения от 1 до 4000. Unicode использует 2 байта на символ и может поддерживать все международные символы. Размер хранимых данных соответствует действительной длине записи * на 2, а не n байтов |
|
ntext |
16 байтов для указателя |
Хранит Unicode данные переменной длины с максимальным размером 2^30 - 1 (1,073,741,823) символов. Запись в столбце является указателем для ntext, определяющим расположение данных. Данные хранятся отдельно от таблицы данных |
|
Двоичные |
|
|
|
binary(n) |
n + 4 байта |
Хранит двоичные данные фиксированного размера в n байт, где n может принимать значения от 1 до 8000. Применяйте binary, когда записи данных в столбце согласуются по размеру |
|
varbinary(n) |
Зависит от размера введенных данных + 4 байта |
Хранит двоичные данные переменного размера в n байт, где n может принимать значения от 1 до 8000. Применяйте varbinary, когда записи данных в столбце не согласуются по размеру |
|
image |
16 байтов для указателя |
Используется для двоичных данных переменного размера, большего, чем 8000 байт с наибольшим размером 2^31 - 1 байт. Запись в столбце является указателем на расположение значения данных image. Данные хранятся отдельно от таблицы данных |
|
Специальные |
|
|
|
sql_variant |
размер меняется |
Столбец типа данных sql_variant может содержать строки или другие типы данных. Например, столбец, определенный как sql_variant может содержать значения данных int, binary, и char. Типы данных, которые не могут храниться при использовании sql_variant – это text, ntext, image, timestamp, и sql_variant. |
|
timestamp |
8 байтов |
Timestamp – это тип данных, представляющих автоматически генерируемые двоичные числа, гарантированно уникальные для данной базы данных. Обычно используется как механизм для метки версии строк таблицы. Каждая таблица может иметь только один столбец с типом данных timestamp |
|
uniqueidentifier |
16 байтов |
Хранит 16-байтные двоичные значения, которые являются глобальными уникальными идентификаторами (GUID). |
Эта таблица охватывает практически все типы данных. Однако есть два вида, это cursor и table, но они и не применяются на самом деле, так как вы не можете иметь столбец, образованный данными данного типа.
Максимальный размер строки в базе данных равен 8060 байтов. Для того чтобы посчитать, насколько большой может быть каждая строка в вашей базе данных и убедиться, что ее размер меньше 8060 байтов, вы можете просто сложить размеры всех столбцов и получить искомый ответ. И хотя это абсолютная правда, что строка не может занимать больше одной страницы памяти, все же есть путь обойти это 8060-байтное ограничение. Для этого можно использовать типы данных text, ntext и image (известные как “BLOB” или Binary Large Object (большие двоичные объекты). По умолчанию, типы данных text, ntext и image не хранят свои данные в тех строках, в которых они помещены, как это происходит с другими типами данных. Вместо этого строки содержат 16-битные указатели, которые перенаправляют сервер SQL на другие 8Кб страницы, где и размещаются данные. Используя в строке только указатели на хранящиеся данные, вы можете преодолеть 8060-байтное ограничение. Типы данных text, ntext и inage сами имеют некоторые ограничения, о которых мы поговорим позднее в данной серии. Одной из отрицательных сторон данного способа является то, что, так как сервер SQL должен находить две или даже более страниц (в отличие от всего одной страницы для строк, не содержащих данных text/image) – это может значительно замедлить его работу. Практически применимым является использование этих типов данных, только если это абсолютно необходимо.
Следующее замечание касается типов данных с фиксированной длиной и с переменной длинной. Если вы используете типы данных char или nchar, не имеет значение, как много символов вы введете, поле всегда будет использовать n байтов (или 2*n для nchar). Например, если установлен размер (т.е. n) столбца равным 10 и есть три строки “Dan”, “Mike” и “Jason”, то пространство, необходимое для хранения этих трех строк будет равно 30 байтам, несмотря на то, что они содержат только 12 символов данных. С другой стороны, если вы будете использовать версию типа данных char или nchar переменной длины, то пространство, требуемое для хранения этих трех строк, будет равно 12 байтам, т.о. вы сэкономите 18 байт. Это не выглядит такой уж большой цифрой, но если у вас таблица, состоящая из многих столбцов и 100,000 строк, то выгода становится более очевидной. На практике для хранения текстовых данных, размер которых не меняется от строки к строке, применяются типы данных char или nchar. Так для примера с тремя строками лучшим вариантом будет применение типа данных char – при работе с большей таблицей, в которой окажется большое количество имен, длиннее 5 знаков.
Когда вы выбираете, какие типы данных использовать, цель состоит в том, чтобы выбрать тот тип, который будет занимать наименьшее дисковое пространство и, в то же время, будет соответствовать всем возможным значениям, которые возникают (и будут возникать) за все время существования базы данных. Хорошей иллюстрацией к сказанному является выбор размера целочисленных значений. Если вы уверены, что будете хранить только значение ответа yes/no, то тип данных bit будет более чем достаточен и любой другой тип, например, smallint или int, будут только напрасно тратить дисковое пространство. Другим примером напрасного использования дискового пространства является использование типа datetime, в то время как вам достаточно диапазона и точности smalldatetime. Вы должны постараться установить типы данных так, чтобы они обеспечивали для вас максимальную поддержку целостности данных. Хорошим примером является использование типов данных datetime или smalldatetime для хранения информации о дате – эти типы данных могут занимать больше пространства и использовать больше процессорного времени, чем просто дата, записанная строкой из 6 символов, но при длительном использовании правильный тип данных для даты обязательно сэкономит ваше время.
4 Методические указания к выполнению упражнений по созданию таблиц
1. Создание таблиц баз данных и таблиц с использованием SQL Enterprise Manager. Когда есть логическая схема в качестве отправной точки, сам процесс создания вашей таблицы выглядит достаточно просто.
Запустите Диспетчер Предприятия, затем разверните ваш сервер и базу данных, в которую вы хотите добавить таблицу, затем нажмите правой кнопкой мыши на значке Tables и выбрать “New Table…” из появившегося меню.
Появится окно New Table и оно будет выглядеть вот так:
Каждая строка этого окна представляет отдельный столбец таблицы, которая создается/редактируется.
“Column Name” (имя столбца) – используется для того, чтобы ввести имя столбца.
“Data Type” (тип данных) – устанавливает тип данных, содержащихся в столбце.
“Length” (длина) – определяет длину (или ‘n’) столбца. Для таких типов данных, как text или ntext это значение может быть изменено и определяет максимальное число знаков, которое будет храниться в столбце. Для других типов данных, таких как int и decimal, не имеющих длины, “Length” показывает размер (в байтах) необходимый для столбца.
“Allow Nulls” (разрешить пустые значения) – определяет, может ли отдельный столбец содержать пустые значения. Поставленная «галочка» означает, что пустые значения в данном столбце допустимы. То есть, если вы добавляете/редактируете строку, она не обязательно должна содержать значение в данной ячейке. Если отметки нет, то любая строка, которая добавляется или редактируется, обязательно должна содержать значение в данной ячейке. Запомните, что 1, 0 и <NULL> (или ‘abc’,’ ‘, <NULL>) – это три совершенно разные вещи. Например, если 0 и <NULL> выглядят похоже, но 0 обычно означает “False” (Ложь), а <NULL> означает “No Value” (нет значения). Очень важно запомнить эти вещи.
Таблица, хранящая данные о студентах может выглядеть вот так:
Здесь есть несколько моментов, о которых стоит упомянуть:
Cлева от строки Stud_ID изображен ключ . Это показывает, что строка (строки) таблицы являются первичным ключом (т.е. используемый столбец(ы) уникально идентифицирует каждую строку в таблице). Для установки первичного ключа выберите соответствующую строку (вы можете нажать клавишу Ctrl для того, чтобы выбрать более одной строки одновременно) и затем нажмите иконку “Set primary key” в панели инструментов.
Далее, когда вы будете двигаться от строки к строке, вы заметите, что доступные опции на вкладке “Columns” меняются. Давайте рассмотрим каждую из этих опций индивидуально:
Description (описание) – эта строка предназначена для того, чтобы вы могли ввести любые комментарии, относящиеся к тому, для чего используется данных столбец.
Default Value (значение по умолчанию) – значение, которое будет присвоено, если никакое иное значение не будет введено при добавлении строки в таблицу. Вы также можете использовать функции. Очень часто используются функции GETDATE(), который возвращает текущую системную дату/время и NEWID(), который возвращает новый глобальный уникальный идентификатор. Заметьте, что если вы установите значение “Identity” (идентичность) для столбца Yes, то значение по умолчанию не будет иметь силы.
Precision\Scale (точность\точность дробной части) – используется для определения точности числа и точности дробной части числа типов данных decimal и numeric. Эти опции недоступны для других типов данных. Для получения информации о том, что такое точность и точность дробной части, обратитесь к типу данных decimal в таблице, приведенной в начале данной статьи.
Identity/Seed/Increment (Идентичность/Начальное значение/Инкремент) – похож на опцию AutoNumber в Access – если для столбца значение свойства IDENTITY установлено в значение Yes, то сервер SQL будет автоматически генерировать новое число для каждой вновь добавляемой в таблицу строки и записывать его в этот столбец. Каждая таблица может иметь только один столбец со свойством IDENTITY, установленным в значении Yes и этот столбец должен использовать типы данных decimal, int, numeric, smallint, bigint или tinyint. Исходное значение идентификатора (Identity Seed) определяет, с какого числа должен начать сервер SQL. Инкремент (Identity Increment) определяет, какое число должно добавляться к исходному значению для определения следующего идентификатора.
Is RowGuid (строка содержит GUID) – определяет, что данный столбец содержит GUID для строк таблицы. Только одна таблица может содержать значение свойства Is RowGuid со значением Yes и тип данных для этого столбца должен быть определен как uniqueidentifier. Кроме того, вы должны также установить в качестве значения по умолчанию для данного столбца функцию NEW(). В отличие от значений идентификатора, значения GUID являются (должно являться!) уникальными для каждой строки, в каждой таблице, в каждой базе данных на каждом компьютере в мире.
Formula – используется для ввода формулы, делающей этот столбец вычисляемым. В отличие от простого хранения данных, вычисляемый столбец берет формулу (например: LastName&’,’&FirstName) и генерирует значение, зависящее от введенной вами формулы. Кроме того, вычисляемые столбцы генерируются на «лету», так обновление в столбце, связанном с этой формулой вызовет автоматическое обновление вычисляемого столбца.
Collation (режим сопоставления) – дает возможность устанавливать режимы сопоставления для каждого отдельного столбца в таблице. Значение <database default> устанавливает для столбца тот же режим сопоставления, что и для всей базы данных, в которой расположена таблица.
Поскольку вы уже добавили все столбцы в таблицу, вы можете нажать на значок сохранения (самый левый, с изображение дискеты) в панели инструментов, после чего вам предложат ввести имя для таблицы:
После того, как вы введете имя и нажмете кнопку ОК, можете закрывать окно New Table, нажав расположенный во вложенном окне Х вверху справа. Также можете использовать меню “Window” для переключения окон или их упорядочивания.
После обновления списка таблиц (F5), созданная вами таблица появится в Диспетчере Предприятия сервера SQL:
Вы всегда можете вернуться назад и отредактировать таблицу, нажав правой кнопкой мыши на ее изображении в Диспетчере Предприятия и выбрав опцию “Design Table”:
Использование Диспетчера Предприятия не является единственным путем создания базы данных и таблиц.
2. Создание таблиц с использованием SQL Query Analyzer
В меню Tools выберите Query Analyzer.
Для создания таблиц можно использовать оператор CREATE TABLE.
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 и ALTER TABLE находятся в справочной системе сервера SQL Book Online.