Файл: Создание базы данных в sql server 2022 Основные объекты структуры базы данных sql сервера.pdf

РАЗДЕЛ 1. СОЗДАНИЕ БАЗЫ ДАННЫХ В SQL SERVER 2022
1.1. Основные объекты структуры базы данных SQL - сервера
Основные объекты базы данных SQL Server представлены в таблице 1:
Таблица 1. Объекты базы данных SQL Server
Tables
Таблицы базы данных, в которых хранятся собственно данные
Views
Просмотры (виртуальные таблицы) для отображения данных из таблиц
Stored
Procedures
Хранимые процедуры
Triggers
Триггеры – специальные хранимые процедуры, вызываемые при изменении данных в таблице
User Defined
function
Создаваемые пользователем функции
Indexes
Индексы – дополнительные структуры, призванные повысить производительность работы с данными
User Defined
Data Types
Определяемые пользователем типы данных
Keys
Ключи – один из видов ограничений целостности данных
Constraints
Ограничение целостности – объекты для обеспечения логической целостности данных
Users
Пользователи, обладающие доступом к базе данных
Roles
Роли, позволяющие объединять пользователей в группы
Rules
Правила базы данных, позволяющие контролировать логическую целостность данных
Defaults
Умолчания или стандартные установки базы данных
Таблицы
Все данные содержатся в объектах, называемых таблицами. Таблицы представляют собой совокупность каких-либо сведений об объектах, явлениях, процессах реального мира. Никакие другие объекты не хранят данные, но они могут обращаться к данным в таблице. Таблицы в SQL имеют такую же структуру, что и таблицы всех других реляционных СУБД и содержат:
− cтроки; каждая строка (или запись) представляет собой совокупность атрибутов (свойств) конкретного экземпляра объекта;
− cтолбцы; каждый столбец (поле) представляет собой атрибут или совокупность атрибутов. Поле строки является минимальным элементом таблицы. Каждый столбец в таблице имеет определенное имя, тип данных и размер.
Представлениями (просмотрами) называют виртуальные таблицы, содержимое которых определяется запросом. Подобно реальным таблицам, представления содержат именованные столбцы и строки с данными. Для конечных пользователей представление выглядит как таблица, но в действительности оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Информация, которую видит пользователь через представление, не сохраняется в базе данных как самостоятельный объект.

Хранимые процедуры представляют собой группу команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое.
Триггерами называется специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении или удалении данных из таблицы.
Функции в языках программирования – это конструкции, содержащие часто исполняемый код. Функция выполняет какие-либо действия над данными и возвращает некоторое значение.
Индекс – структура, связанная с таблицей или представлением и предназначенная для ускорения поиска информации в них. Индекс определяется для одного или нескольких столбцов, называемых индексированными столбцами. Он содержит отсортированные значения индексированного столбца или столбцов со ссылками на соответствующую строку исходной таблицы или представления. Повышение производительности достигается за счет сортировки данных.
Использование индексов может существенно повысить производительность поиска, однако для хранения индексов необходимо дополнительное пространство в базе данных.
Пользовательские типы данных – это типы данных, которые создает пользователь на основе системных типов данных, когда в нескольких таблицах необходимо хранить однотипные значения; причем нужно гарантировать, что столбцы в таблице будут иметь одинаковый размер, тип данных и чувствительность к значениям NULL.
Ограничения целостности – механизм, обеспечивающий автоматический контроль соответствия данных установленным условиям (или ограничениям).
Ограничения целостности имеют приоритет над триггерами, правилами и значениями по умолчанию. К ограничениям целостности относятся: ограничение на значение NULL, проверочные ограничения, ограничение уникальности
(уникальный ключ), ограничение первичного ключа и ограничение внешнего ключа. Последние три ограничения тесно связаны с понятием ключей.
Правила используются для ограничения значений, хранимых в столбце таблицы или в пользовательском типе данных. Они существуют как самостоятельные объекты базы данных, которые связываются со столбцами таблиц и пользовательскими типами данных. Контроль значений данных может быть реализован и с помощью ограничений целостности.
Умолчания – самостоятельный объект базы данных, представляющий значение, которое будет присвоено элементу таблицы при вставке строки, если в команде вставки явно не указано значение для этого столбца.
1.2. Создание базы данных. Создание и заполнение таблиц
Создание БД Успеваемость
Создание самой базы данных и таблиц осуществляется с помощью программы SQL Server 2022, которая запускается командой: Пуск-Microsoft

SQL Server Management Studio
. После установки соединения (рис 1) с текущим сервером на экране появится окно программы, показанное на рис. 2.
Рис.1 Подключение к серверу
Рис.2. Окно Обозревателя объектов программы Microsoft SQL Server Management Studio
В левой части в окне Обозревателя объектов можно увидеть содержимое текущего сервера, а именно базы данных и другие объекты. На одном компьютере-сервере можно расположить несколько баз данных. Весь список БД

доступных на сервере можно просмотреть, открыв папку Базы данных (см. рис.3).
Рис.3. Содержимое папки Базы данных
Процесс создания базы данных в системе SQL-сервера состоит из двух этапов: сначала организуется сама база данных, а затем принадлежащий ей журнал транзакций. Информация размещается в соответствующих файлах, имеющих расширения *.mdf (для базы данных) и *.ldf. (для журнала транзакций).
В файле базы данных записываются сведения об основных объектах (таблицах, индексах, просмотрах и т.д.), а в файле журнала транзакций – о процессе работы с транзакциями (контроль целостности данных, состояния базы данных до и после выполнения транзакций).
Итак, для создания новой базы данных необходимо щелкнуть правой кнопкой мыши по значку папки Базы данных и выбрать команду контекстного меню Создать базу данных (см. рис.4). После чего на экране появится диалоговое окно Создание базы данных, в котором необходимо указать в поле
Имя базы данных имя создаваемой БД. Имя базы данных должно быть уникальным в пределах сервера и не может превышать 128 символов. Например, укажем название - Успеваемость (см. рис.5).
В разделе Файлы базы данных можно указать дополнительную информацию о файлах базы данных. Здесь можно изменить Путь, Начальный
размер файла в мегабайтах, Авторасширение (см. рис.6).

Рис.4. Команда создания новой БД.
Рис.5. Создание БД Успеваемость.
Имя файла для данных генерируется на основе имени базы данных, в нашем случае Успеваемость, тип файла Rows data (Данные). В поле Путь можно поменять расположение файла данных. По умолчанию указывается
C
:\
Program
Files\Microsoft
SQL
Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\. В поле Начальный размер
можно определить первоначальный размер файла, по умолчанию принимается 8
Мб.
В поле Авторасширение нажав соответствующую кнопку можно в дополнительном окне изменить параметры авторасширения для создаваемой базы данных (см. рис.6), а именно, разрешить или запретить автоматический рост его размера Разрешить авторасширение и указать следующие параметры: 1)
Увеличение размера файлов - приращение с помощью абсолютной величины
в мегабайтах или в процентном соотношении В процентах; 2) Максимальный
размер файла. При выборе параметра Неограниченный рост размера файлов
- максимальный размер базы данных ограничивается только свободным местом

на диске, при выборе Ограничение размера файла нужно указать максимальный размер файла в Мб.
Рис.6. Создание БД Успеваемость. Изменение параметра Авторасширение
Такие же параметры можно настроить у файла журнала транзакций. Имя файла для журнала транзакций генерируется на основе имени базы данных, и в конце к нему добавляются символы _log. Файл журнала транзакций должен находиться в одной папке с файлом базы данных.
После настройки всех параметров можно нажать кнопку ОК, в результате чего будет создан файл базы данных с выбранными параметрами (рис.7)
Рис.7. База данных Успеваемость
Создание таблиц. Типы данных. Нулевой статус.
После того как создана база данных можно приступить к созданию таблиц, необходимых для учета успеваемости студентов. Для создания таблицы необходимо открыть БД Успеваемость и перейти к объекту Tables. Здесьможно

увидеть стандартный набор системных таблиц. Таблицы, создаваемые пользователем для хранения информации, являются пользовательскими.
Таблица создается с помощью команды контекстного меню Создать таблицу
(рис.8). Сначала нужно описать структуру таблицы, т.е. указать имена полей, типы данных, нулевой статус, ключевые поля и другие свойства (рис.9).
Рис.8. Создание новой таблицы
Рис.9. Окно создания новой таблицы
Названия полей должны быть четко продуманными и должны соответствовать данным, которые будут вводиться в это поле. Имена полей не должны содержать пробелов и буквы русского алфавита.
Один из основных моментов процесса создания таблицы – определение типов данных для ее полей. Тип данных поля таблицы определяет тип информации, которая будет размещаться в этом поле. SQL-сервер поддерживает большое число различных типов данных. Полный перечень представлен в таблице 2.
Таблица 2. Список типов данных
Для хранения символьной информации:
char (длина)
текстовые значения имеют фиксированную длину от 1 до 8000 символов. Если во вводимом в поле текстовом значении

фактическое число символов меньше числа, определенного параметром длина, то значение поля автоматически дополняется справа пробелами до заданного числа символов.
varchar (длина)
Текстовые значения имеют переменную длину от 1 до 8000 символов. В отличие от char при вводе строки, фактическая длина которой меньше заданной, не производится ее дополнение пробелами до заданного максимального значения. По сравнению с char тип данных varchar позволяет более экономно использовать память, выделяемую для хранения текстовых значений, и оказывается более удобным при сравнении текстовых констант.
nchar (длина)
для хранения до 4000 символов Unicode.
nvarchar (длина)
text
для хранения символьных данных большого объема (до 2 Гб)
ntext
К целочисленным типам данных относятся:
int
4 байта (диапазон хранимых данных от -2 31
(-2 147 483 648) до
2 31
-1 (2 147 483 647))
smallint
2 байта (диапазон от -2 15
(-32 768)до 2 15
-1 (32767))
tinyint
1 байт (диапазон от 0 до 255)
bigint
8 байт (диапазон от -2 63
(-9223372036854775808) до 2 63
-1
(9223372036854775807))
Числа, в составе которых есть десятичная точка, называются нецелочисленными.
Нецелочисленные данные разделяются на два типа – десятичные и приблизительные.
Десятичные:
decimal
[(точность[,масштаб])]
Позволяют самостоятельно определить формат точности числа с плавающей запятой. Параметр точность указывает максимальное количество цифр вводимых данных этого типа (до и после десятичной точки в сумме), а параметр масштаб – максимальное количество цифр, расположенных после десятичной точки. В обычном режиме сервер позволяет вводить не более 28 цифр, используемых в типах DECIMAL и NUMERIC (от 2 до 17 байт).
numeric
[(точность[,масштаб])]
Приблизительные:
float
FLOAT (точность до 15 цифр, 8 байт) и REAL (точность до 7 цифр,
4 байта). Эти типы представляют данные в формате с плавающей запятой, т.е. для представления чисел используется мантисса и порядок, что обеспечивает одинаковую точность вычислений независимо от того, насколько мало или велико значение.
real
Для хранения информации о дате и времени предназначены такие типы данных, как:
smalldatetime
4 байт для хранения дат в пределах от 1 января 1900 до 6 июня 2079
datetime
8 байт с 1 января 1753 до 31 December 9999
Для хранения информации денежного типа
money
обеспечивают точность значений до 4 знаков после запятой и используют 8 и 4 байта соответственно.
smallmoney
Логический тип данных
bit
Для хранения значений 0 или 1
Другие типы данных
timestamp
применяется в качестве индикатора изменения версии строки в пределах базы данных

uniqueidentifier
используется для хранения глобальных уникальных идентификационных номеров
sysname
предназначен для идентификаторов объектов
sql_variant
позволяет хранить значения любого из поддерживаемых SQL
Server типов данных за исключением TEXT, NTEXT, IMAGE и
TIMESTAMP
image
Variable-length binary data with a maximum length of 2^31 - 1
(2,147,483,647) bytes.
Дополнительные свойства полей:
− Размер поля (Длина). После выбора типа данных можно указать дополнительные свойства полей в нижней части окна Свойства столбцов.
Значение длина – размер поля можно редактировать только у полей имеющих символьный тип данных: char (длина), varchar (длина), nchar (длина),
nvarchar (длина).
− Нулевой статус (Разрешить значения Null). Для обозначения отсутствующих, пропущенных или неизвестных значений поля в SQL используется ключевое слово NULL. NULL не является значением 0 в обычном понимании, а используется для обозначения того факта, что действительное значение поля на самом деле пропущено или неизвестно. При описании полей таблицы проектировщик в столбце Разрешить значения Null разрешает ( ) или запрещает ввод значений NULL в то или иное поле. В ключевые поля автоматически запрещается ввод неизвестных значений. Например, при описании поля Фамилия_студента проектировщик запретил ввод значений
NULL в это поле. Следовательно, при заполнении таблицы реальными данными нельзя будет оставить поле Фамилия_студента незаполненным, т.е. если пользователь не будет знать фамилию или ошибочно пропустит это поле, то при переходе к следующей строке SQL Server выдаст предупреждение о том, что поле Фамилия_студента является обязательным для заполнения.
Задание 1. Рассмотрим процесс создания таблицы на примере таблицы
STUDENT. Изучите и выполните все действия, описанные ниже.
Таблица Студент предназначена для хранения сведений о студентах личного характера, т.е. персональный код, имя и фамилию, дату рождения, город проживания, курс, стипендию и код университета в котором учится студент. Т.е. структура полей таблицы будет иметь следующий вид:
Свойство Название
поля
Назначение
PK
(Primary key, ключевое поле)
Student_id
Числовой код, однозначно идентифицирующий студента см. описание типов полей ниже
Surname
Фамилия студента
Name
Имя студента
Stipend
Стипендия, которую получает студент

Kurs
Курс, на котором учится студент
City
Город, в котором живет студент
Birthday
Дата рождения студента
Univ_id
Числовой код, идентифицирующий университет, в котором учится студент
Создадим таблицу Student, определив тип данных и нулевой статус столбцов.
− Поле Student_id предназначено для хранения идентификационных номеров студентов. Каждый студент будет иметь свой персональный неповторяющийся числовой код. Предполагаем, что количество студентов, а, следовательно, количество разных идентификационных номеров, будет больше
255, но не больше 32767, в связи с этим целесообразнее выбрать тип данных –
smallint,чем tinyint (значения см. в таб.2). Если число студентов будет больше чем 32767, то нужно выбрать тип данных – int.
− Из таких же рассуждений дляполя Univ_id выбран тип данных smallint.
− Поля Surname, Name, City с целью экономии памяти определяем как текстовые поля переменной длины – varchar. Предполагаем, что фамилия и город будет не больше 20 символов, поэтому длину полей Surname и City установим равными 20 символам, а Name - 15 символов (см. рис.11).
− Поле Stipend будет содержать нецелочисленные значения стипендий, поэтому выбираем тип данных – real.
− Поле Kurs будет содержать значения от 1 до 5, поэтому целесообразнее выбрать самый малый целочисленный тип данных – tinyint.
− Поле Birthday предназначено для хранения дат рождения, поэтому выбираем тип данных smalldatetime (описание типа данных см. в таб.2)
− Нулевой статус полей. Прежде чем определять нулевой статус полей, проектировщик должен разделить все поля таблицы на 2 категории: главные и второстепенные. Главные (или основные) поля таблицы содержат основную информацию, они зачастую предназначены для персонификации или идентификации субъекта/объекта, поэтому не должны содержать пустые значения - Null значения. Второстепенные поля содержат дополнительные характеристики субъекта/объекта. Право отнесения поля к той или иной категории принадлежит проектировщику исходя из постановки задачи.
− Итак, ввод значений Null запретим в поля Student_id, Surname и Name.
Почему именно эти поля не могут быть пропущены при заполнении строки? Это объясняется тем, что код студента является главным полем данной таблицы, кроме того, оно является ключевым полем, поэтому в него автоматически запрещается вводить значения Null. А поскольку код обозначает определенного студента, то мы должны ввести его фамилию и имя, чтобы точно знать, кому принадлежит введенный код. Иначе, если мы сделаем эти 2 поля необязательными для заполнения, то возникает вопрос: «Кого (что) мы идентифицируем (однозначно обозначаем) с помощью поля КодСтудента»? В остальные поля (стипендия, город проживания и.т.д.) можно разрешить ввод значений Null. Т.е. в момент заполнения сведений о текущем студенте, если не