Проектирование и реализация базы данных для бюро знакомств в SQL Server: Полное руководство для курсовой работы

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

Предстоящая курсовая работа посвящена комплексному изучению этих процессов на примере предметной области «Бюро знакомств» — динамичной и многогранной платформы, требующей тщательного подхода к организации информации. Эта область выбрана не случайно: она наглядно демонстрирует сложные взаимосвязи между пользовательскими профилями, предпочтениями, активностью и подписками, что позволяет охватить весь спектр задач по проектированию и реализации базы данных. Данное руководство призвано стать исчерпывающим планом, который проведет студента от концептуального осмысления предметной области до физической реализации базы данных в СУБД SQL Server, включая разработку всех необходимых объектов для эффективного управления данными и бизнес-логикой. Такой подход не только обеспечивает успешное выполнение курсовой, но и закладывает прочный фундамент для будущей профессиональной деятельности в сфере проектирования баз данных.

Обзор предметной области и общие принципы проектирования БД

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

Понятие базы данных и СУБД

База данных — это не просто хаотичный набор информации, а структурированный, организованный и взаимосвязанный набор данных, который хранится в электронной форме в компьютерной системе и предназначен для систематического хранения, накопления, изменения и обработки информации. Её ключевая задача — обеспечить эффективный доступ к данным, их целостность и безопасность для множества пользователей и приложений. Представьте себе огромную картотеку, где каждая карточка строго классифицирована, а вся система позволяет быстро найти нужную информацию или обновить её, не нарушая порядок в остальных карточках, что критически важно для поддержания актуальности и надежности любых данных.

Управление этой сложной структурой осуществляется с помощью Системы управления базами данных (СУБД). Это комплекс программных средств, предназначенный для создания баз данных, организации хранения данных, обеспечения доступа к ним, а также выполнения операций по их обработке и управлению. СУБД выступает посредником между пользователем (или приложением) и физическим хранилищем данных, абстрагируя пользователя от низкоуровневых деталей хранения информации. Примерами таких систем являются Microsoft SQL Server, MySQL, PostgreSQL, Oracle Database и другие. Они предоставляют инструментарий для выполнения DDL-операций (создание, изменение, удаление структуры БД) и DML-операций (манипуляции с данными), а также обеспечивают контроль целостности, безопасности и параллельного доступа.

Методология проектирования баз данных

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

1. Концептуальное проектирование. Это самый первый, высокоуровневый этап, на котором определяется, что должно быть в базе данных. Основная цель — создать абстрактную, независимую от конкретной СУБД модель предметной области, которая будет понятна как специалистам по базам данных, так и конечным пользователям. На этом этапе идентифицируются ключевые сущности (объекты реального мира, о которых необходимо хранить информацию), их атрибуты (характеристики сущностей) и связи между ними. Самым распространенным инструментом для концептуального моделирования является ER-модель (Entity-Relationship Model — модель «Сущность-Связь»). Для бюро знакомств это будет означать определение таких сущностей, как «Пользователь», «Анкета», «Сообщения» и связей между ними.
2. Логическое проектирование. После того как концептуальная модель сформирована, она преобразуется в логическую модель, которая уже более формализована и ближе к конкретной модели данных (например, реляционной). На этом этапе ER-диаграмма преобразуется в набор таблиц (отношений), определяются первичные и внешние ключи, и, что особенно важно, применяется процесс нормализации. Нормализация — это систематический подход к декомпозиции таблиц с целью уменьшения избыточности данных и устранения аномалий обновления, вставки и удаления. Результатом логического проектирования является реляционная схема базы данных, которая описывает все таблицы, их столбцы и зависимости между ними, но всё ещё не привязана к специфическим особенностям конкретной СУБД. Это позволяет создать гибкую и устойчивую к изменениям структуру.
3. Физическое проектирование. Это последний этап, на котором логическая модель адаптируется к особенностям выбранной СУБД (в нашем случае SQL Server) и физической среде хранения. Здесь определяются конкретные типы данных для каждого столбца (например, INT, VARCHAR(255), DATETIME), индексы для оптимизации запросов, параметры хранения (размер дискового пространства, расположение файлов), а также другие детали, влияющие на производительность и безопасность. В итоге создаются DDL-скрипты, которые непосредственно реализуют структуру базы данных в SQL Server.

Эти этапы не являются строго последовательными; часто они итеративны, и изменения, обнаруженные на более поздних этапах, могут вызвать необходимость пересмотра на более ранних.

Концептуальное проектирование: Модель «Сущность-Связь» для бюро знакомств

Концептуальное проектирование является фундаментом для всей будущей базы данных. Именно на этом этапе мы формируем общее видение, определяя ключевые компоненты системы и их взаимодействие. Для предметной области «Бюро знакомств» это означает глубокое погружение в бизнес-логику платформы, чтобы учесть все аспекты, от регистрации пользователя до системы совпадений и обмена сообщениями.

Идентификация сущностей и их атрибутов

Центральной идеей концептуального проектирования является выделение сущностей – объектов или событий, о которых необходимо хранить информацию. Каждая сущность обладает набором атрибутов – характеристик, которые её описывают. Для нашего «Бюро знакомств» мы можем выделить следующие ключевые сущности:

• Пользователь/Клиент: Это основной актор системы.
  • Атрибуты: ПользовательID (уникальный идентификатор, первичный ключ), Имя, Фамилия, ДатаРождения, Пол (м/ж/др.), ГородПроживания, Email (уникальный), Телефон (опционально, уникальный), ХэшПароля, URLФотоПрофиля (путь к основной фотографии).
• Анкета: Содержит подробную информацию о пользователе, которая не является строго идентификационной.
  • Атрибуты: АнкетаID (первичный ключ), ПользовательID (внешний ключ к сущности «Пользователь», связь один-к-одному), ОписаниеОСебе, Интересы (строка или JSON для списка), Хобби, Образование, Профессия, ВредныеПривычки, СемейноеПоложение.
• Предпочтения: Определяют критерии поиска партнера для каждого пользователя.
  • Атрибуты: ПредпочтенияID (первичный ключ), ПользовательID (внешний ключ к «Пользователь», связь один-к-одному), ПредпочитаемыйПол, МинимальныйВозраст, МаксимальныйВозраст, ПредпочитаемыйГород, ПредпочитаемыеИнтересы.
• Совпадения: Фиксируют факт проявления интереса между пользователями.
  • Атрибуты: СовпадениеID (первичный ключ), Пользователь1ID (внешний ключ к «Пользователь»), Пользователь2ID (внешний ключ к «Пользователь»), ДатаСовпадения, Статус (например, «лайк», «взаимный лайк», «отклонено»).
• Сообщения: Хранят переписку между пользователями.
  • Атрибуты: СообщениеID (первичный ключ), ОтправительID (внешний ключ к «Пользователь»), ПолучательID (внешний ключ к «Пользователь»), ТекстСообщения, ДатаВремяОтправки, Статус (например, «прочитано», «не прочитано»).
• Фотографии: Позволяют пользователям загружать несколько изображений.
  • Атрибуты: ФотографияID (первичный ключ), ПользовательID (внешний ключ к «Пользователь», связь один-ко-многим), URLФотографии, ОписаниеФотографии, ОсновноеФото (булево значение).
• Тарифы/Подписки: Описывают доступные типы платных подписок.
  • Атрибуты: ТарифID (первичный ключ), НазваниеТарифа, ОписаниеТарифа, Цена, СрокДействия (в днях/месяцах).
• ПользовательскиеПодписки: Связывает пользователя с активным тарифом.
  • Атрибуты: ПользовательскаяПодпискаID (первичный ключ), ПользовательID (внешний ключ к «Пользователь»), ТарифID (внешний ключ к «Тарифы»), ДатаНачалаПодписки, ДатаОкончанияПодписки, СтатусПодписки (активна/завершена/отменена).

Выбор этих сущностей и их атрибутов обоснован необходимостью охватить весь базовый функционал бюро знакомств: от создания профиля и описания себя, до поиска потенциальных партнеров, обмена сообщениями и управления платными услугами.

Определение связей между сущностями

Сущности в реальном мире не существуют изолированно; они взаимодействуют друг с другом. В концептуальной модели эти взаимодействия выражаются через связи. Типы связей определяют кардинальность, то есть количество экземпляров одной сущности, которые могут быть связаны с экземплярами другой сущности:

• Один-к-одному (1:1): Каждый экземпляр одной сущности связан ровно с одним экземпляром другой сущности.
  • Пример: Пользователь и Анкета. Каждый пользователь имеет ровно одну анкету, и каждая анкета принадлежит ровно одному пользователю. То же самое для Пользователь и Предпочтения.
• Один-ко-многим (1:M): Каждый экземпляр одной сущности может быть связан с несколькими экземплярами другой сущности, но каждый экземпляр второй сущности связан только с одним экземпляром первой.
  • Пример: Пользователь и Фотографии. Один пользователь может иметь много фотографий, но каждая фотография принадлежит только одному пользователю.
  • Пример: Пользователь и ПользовательскиеПодписки. Один пользователь может иметь несколько подписок (история подписок), но каждая конкретная запись о подписке относится к одному пользователю.
  • Пример: Тарифы и ПользовательскиеПодписки. Один тариф может быть выбран множеством пользователей, но каждая пользовательская подписка ссылается на один конкретный тариф.
• Многие-ко-многим (M:M): Каждый экземпляр одной сущности может быть связан с несколькими экземплярами другой сущности, и наоборот. Такие связи обычно разрешаются путём создания промежуточной сущности (таблицы связи).
  • Пример: Пользователь и Совпадения. Один пользователь может иметь множество совпадений (как инициатор, так и получатель), и одно совпадение всегда включает двух пользователей. Эта связь естественным образом разрешается таблицей Совпадения, которая содержит два внешних ключа к сущности Пользователь.
  • Пример: Пользователь и Сообщения. Аналогично, один пользователь может отправлять и получать множество сообщений. Таблица Сообщения также содержит два внешних ключа к Пользователь (ОтправительID, ПолучательID).

Построение ER-диаграммы

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

ER-диаграмма для базы данных «Бюро знакомств»

Структура ER-диаграммы:

Сущности (прямоугольники):

• Пользователь/Клиент
  • ПользовательID
  • Имя
  • Фамилия
  • ДатаРождения
  • Пол
  • ГородПроживания
  • Email
  • Телефон
  • ХэшПароля
  • URLФотоПрофиля
• Анкета
  • АнкетаID
  • ПользовательID
  • ОписаниеОСебе
  • Интересы
  • Хобби
  • Образование
  • Профессия
  • ВредныеПривычки
  • СемейноеПоложение
• Предпочтения
  • ПредпочтенияID
  • ПользовательID
  • ПредпочитаемыйПол
  • МинимальныйВозраст
  • МаксимальныйВозраст
  • ПредпочитаемыйГород
  • ПредпочитаемыеИнтересы
• Совпадения
  • СовпадениеID
  • Пользователь1ID
  • Пользователь2ID
  • ДатаСовпадения
  • Статус
• Сообщения
  • СообщениеID
  • ОтправительID
  • ПолучательID
  • ТекстСообщения
  • ДатаВремяОтправки
  • Статус
• Фотографии
  • ФотографияID
  • ПользовательID
  • URLФотографии
  • ОписаниеФотографии
  • ОсновноеФото
• Тарифы
  • ТарифID
  • НазваниеТарифа
  • ОписаниеТарифа
  • Цена
  • СрокДействия
• ПользовательскиеПодписки
  • ПользовательскаяПодпискаID
  • ПользовательID
  • ТарифID
  • ДатаНачалаПодписки
  • ДатаОкончанияПодписки
  • СтатусПодписки

Связи (ромбы):

• Связь «Имеет» (Пользователь — Анкета): 1:1, обязательная с обеих сторон (или опциональная, если анкета может быть не заполнена сразу).
• Связь «Определяет» (Пользователь — Предпочтения): 1:1, опциональная с обеих сторон (или обязательная, если предпочтения должны быть указаны).
• Связь «Загружает» (Пользователь — Фотографии): 1:M.
• Связь «Инициирует/Получает» (Пользователь — Совпадения): M:M, разрешенная сущностью Совпадения.
• Связь «Отправляет/Получает» (Пользователь — Сообщения): M:M, разрешенная сущностью Сообщения.
• Связь «Выбирает» (Пользователь — ПользовательскиеПодписки — Тарифы): Пользователь выбирает тариф, и этот факт фиксируется в ПользовательскиеПодписки. Связь M:M между Пользователь и Тарифы разрешается сущностью ПользовательскиеПодписки.

В курсовой работе здесь необходимо вставить изображение ER-диаграммы, созданной с помощью специализированных инструментов, таких как Lucidchart, draw.io, Visio или SQL Server Management Studio (SSMS) с использованием Database Diagrams.

Логическое проектирование: Нормализация реляционной модели

После того как концептуальная модель определена, следующим шагом является её преобразование в логическую модель, основанную на реляционном подходе. Этот этап включает в себя перевод сущностей и связей в таблицы и обеспечение их оптимальной структуры с помощью процесса нормализации. Нормализация — это не просто теоретическое упражнение, а краеугольный камень в создании баз данных, которые будут устойчивы к ошибкам, легки в поддержке и эффективны в работе, что непосредственно влияет на долгосрочную жизнеспособность и управляемость информационной системы.

Основы нормализации

В основе нормализации лежит понятие функциональной зависимости (ФЗ): если значения атрибутов X однозначно определяют значения атрибутов Y (X → Y), то Y функционально зависит от X. Например, в таблице клиентов, ИНН → НазваниеКомпании, потому что каждый ИНН соответствует только одной компании.

Целью нормализации является устранение так называемых аномалий:

• Аномалии вставки: Невозможность добавить данные об одном объекте без наличия данных о другом. Например, нельзя добавить информацию о новом тарифе, если для него обязательно нужно указать пользователя, который его ещё не купил.
• Аномалии удаления: Потеря данных об одном объекте при удалении данных о другом. Например, при удалении последнего пользователя, купившего определенный тариф, информация о самом тарифе также исчезает.
• Аномалии обновления: Необходимость изменять одно и то же значение в нескольких местах, что может привести к несогласованности данных. Например, изменение города проживания пользователя, которое хранится в нескольких таблицах, может привести к ошибкам, если будет обновлено не везде.

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

Первая нормальная форма (1НФ)

Определение: Отношение (таблица) находится в 1НФ, если все его атрибуты являются простыми (атомарными), все используемые домены должны содержать только скалярные значения, и не должно быть повторяющихся групп атрибутов или строк. Это означает, что каждая ячейка таблицы должна содержать одно атомарное значение.

Пример для «Бюро знакомств»: Рассмотрим сущность «Анкета», где атрибут Интересы изначально может представлять собой список интересов, разделённых запятыми (например, «чтение, спорт, музыка»). Это нарушает 1НФ, так как значение не атомарно.

До 1НФ (нарушает атомарность):

АнкетаID	ПользовательID	ОписаниеОСебе	Интересы	…
1	101	Привет!	чтение, спорт, музыка	…

После приведения к 1НФ (декомпозиция):
Чтобы привести это к 1НФ, мы выделим интересы в отдельную таблицу и свяжем её с анкетой через дополнительную промежуточную таблицу для отношения «многие-ко-многим».

Таблица Анкеты:

АнкетаID	ПользовательID	ОписаниеОСебе	…
1	101	Привет!	…

Таблица Интересы:

ИнтересID	НазваниеИнтереса
1	чтение
2	спорт
3	музыка

Таблица АнкетаИнтересы (для связи M:M):

АнкетаID	ИнтересID
1	1
1	2
1	3

Теперь каждая ячейка содержит атомарное значение, и нет повторяющихся групп.

Вторая нормальная форма (2НФ)

Определение: Отношение находится во 2НФ, если оно находится в 1НФ и каждый неключевой атрибут неприводимо зависит от Первичного Ключа (ПК). Неприводимость означает, что ни один атрибут не может быть опущен из детерминанта без нарушения зависимости. Проще говоря, неключевые атрибуты не должны зависеть только от части составного первичного ключа.

Пример для «Бюро знакомств»: Представим, что у нас была бы таблица Совпадения со составным ключом (Пользователь1ID, Пользователь2ID) и атрибутами ДатаСовпадения, Статус, а также ИмяПользователя1.

До 2НФ (нарушает неприводимую зависимость):

Пользователь1ID	Пользователь2ID	ДатаСовпадения	Статус	ИмяПользователя1
101	102	2025-10-28	взаимный лайк	Иван
101	103	2025-10-27	лайк	Иван

Здесь ИмяПользователя1 зависит только от Пользователь1ID, но не от всего составного ключа (Пользователь1ID, Пользователь2ID). Это транзитивная зависимость от части ключа.

После приведения к 2НФ: Мы уже выделили информацию о пользователях в отдельную таблицу Пользователи.

Таблица Пользователи:

ПользовательID	Имя	…
101	Иван	…
102	Анна	…

Таблица Совпадения (первичный ключ СовпадениеID):

СовпадениеID	Пользователь1ID	Пользователь2ID	ДатаСовпадения	Статус
1	101	102	2025-10-28	взаимный лайк
2	101	103	2025-10-27	лайк

Теперь все неключевые атрибуты (ДатаСовпадения, Статус) зависят от всего первичного ключа таблицы Совпадения (если бы он был составным (Пользователь1ID, Пользователь2ID), или от синтетического ключа СовпадениеID).

Третья нормальная форма (3НФ)

Определение: Отношение находится в 3НФ, если оно находится во 2НФ и все неключевые атрибуты нетранзитивно зависят от первичного ключа. Транзитивная зависимость возникает, когда неключевой атрибут зависит от другого неключевого атрибута, который, в свою очередь, зависит от первичного ключа.

Пример для «Бюро знакомств»: Представим, что в таблице Анкеты мы по ошибке добавили НазваниеГородаПроживания в дополнение к ГородПроживанияID (который является внешним ключом к таблице Города).

До 3НФ (транзитивная зависимость):

АнкетаID	ПользовательID	ГородПроживанияID	НазваниеГородаПроживания	…
1	101	5	Москва	…
2	102	12	Санкт-Петербург	…

Здесь НазваниеГородаПроживания зависит от ГородПроживанияID, который, в свою очередь, зависит от АнкетаID (через ПользовательID как часть ключа, или просто АнкетаID). Это транзитивная зависимость.

После приведения к 3НФ: Создаем отдельную таблицу Города.

Таблица Анкеты:

АнкетаID	ПользовательID	ГородПроживанияID	…
1	101	5	…
2	102	12	…

Таблица Города:

ГородID	НазваниеГородаПроживания
5	Москва
12	Санкт-Петербург

Теперь НазваниеГородаПроживания не находится в таблице Анкеты, и транзитивная зависимость устранена.

Нормальная форма Бойса-Кодда (НФБК)

Определение: НФБК является более строгой версией 3НФ. Отношение находится в НФБК тогда и только тогда, когда детерминанты всех её нетривиальных функциональных зависимостей являются потенциальными ключами. Это означает, что если X → Y, то X должен быть потенциальным ключом (суперключом) отношения.

Ситуация, когда отношение находится в 3НФ, но не в BCNF, возникает, например, если отношение имеет два или более составных потенциальных ключа, которые имеют общий атрибут.

Пример для «Бюро знакомств»: Предположим, у нас есть гипотетическая таблица ЭкспертыПоИнтересам, где ПользовательID может быть экспертом в НазваниеИнтереса, и у каждого НазваниеИнтереса есть КатегорияИнтереса. Пусть (ПользовательID, НазваниеИнтереса) — это составной первичный ключ.

До НФБК (3НФ, но не НФБК):

ПользовательID	НазваниеИнтереса	КатегорияИнтереса
101	Спорт	Фитнес
101	Футбол	Спорт
102	Музыка	Искусство

Здесь (ПользовательID, НазваниеИнтереса) — первичный ключ. Существует функциональная зависимость: НазваниеИнтереса → КатегорияИнтереса. НазваниеИнтереса не является потенциальным ключом этой таблицы. Это нарушает НФБК.

После приведения к НФБК: Выделим КатегорияИнтереса в отдельную таблицу ИнтересыКатегории.

Таблица ПользовательИнтерес:

ПользовательID	ИнтересID
101	1
101	2
102	3

Таблица ИнтересыКатегории:

ИнтересID	НазваниеИнтереса	КатегорияИнтереса
1	Спорт	Фитнес
2	Футбол	Спорт
3	Музыка	Искусство

В данном случае, если НазваниеИнтереса является потенциальным ключом таблицы ИнтересыКатегории, НФБК будет соблюдена.
Важно отметить, что на практике для большинства бизнес-приложений достаточно достигнуть 3НФ, и переход к НФБК часто применяется в случаях, когда требуется максимальная строгость и минимизация аномалий в специфических сценариях.

Реляционная схема базы данных

Итоговая реляционная схема представляет собой набор таблиц с указанием их столбцов, типов данных, первичных и внешних ключей, отражая логическую структуру базы данных после нормализации.

Таблица 1: Users (Пользователи)

• UserID (INT, PRIMARY KEY, IDENTITY)
• FirstName (VARCHAR(50), NOT NULL)
• LastName (VARCHAR(50), NOT NULL)
• DateOfBirth (DATE, NOT NULL)
• Gender (VARCHAR(10), NOT NULL)
• City (VARCHAR(100), NOT NULL)
• Email (VARCHAR(100), NOT NULL, UNIQUE)
• PhoneNumber (VARCHAR(15), UNIQUE, NULL)
• PasswordHash (VARCHAR(255), NOT NULL)
• ProfilePictureURL (VARCHAR(255), NULL)

Таблица 2: Profiles (Анкеты)

• ProfileID (INT, PRIMARY KEY, IDENTITY)
• UserID (INT, FOREIGN KEY REFERENCES Users(UserID), UNIQUE)
• AboutMe (TEXT, NULL)
• Hobbies (VARCHAR(255), NULL)
• Education (VARCHAR(100), NULL)
• Profession (VARCHAR(100), NULL)
• BadHabits (VARCHAR(255), NULL)
• MaritalStatus (VARCHAR(50), NULL)

Таблица 3: Interests (Интересы)

• InterestID (INT, PRIMARY KEY, IDENTITY)
• InterestName (VARCHAR(100), NOT NULL, UNIQUE)

Таблица 4: ProfileInterests (Интересы Анкет)

• ProfileID (INT, FOREIGN KEY REFERENCES Profiles(ProfileID))
• InterestID (INT, FOREIGN KEY REFERENCES Interests(InterestID))
• PRIMARY KEY (ProfileID, InterestID)

Таблица 5: Preferences (Предпочтения)

• PreferenceID (INT, PRIMARY KEY, IDENTITY)
• UserID (INT, FOREIGN KEY REFERENCES Users(UserID), UNIQUE)
• PreferredGender (VARCHAR(10), NULL)
• MinAge (INT, NULL)
• MaxAge (INT, NULL)
• PreferredCity (VARCHAR(100), NULL)

Таблица 6: Matches (Совпадения)

• MatchID (INT, PRIMARY KEY, IDENTITY)
• User1ID (INT, FOREIGN KEY REFERENCES Users(UserID))
• User2ID (INT, FOREIGN KEY REFERENCES Users(UserID))
• MatchDate (DATETIME2, NOT NULL)
• Status (VARCHAR(50), NOT NULL)

Таблица 7: Messages (Сообщения)

• MessageID (INT, PRIMARY KEY, IDENTITY)
• SenderID (INT, FOREIGN KEY REFERENCES Users(UserID))
• ReceiverID (INT, FOREIGN KEY REFERENCES Users(UserID))
• MessageText (TEXT, NOT NULL)
• Timestamp (DATETIME2, NOT NULL)
• IsRead (BIT, DEFAULT 0)

Таблица 8: Photos (Фотографии)

• PhotoID (INT, PRIMARY KEY, IDENTITY)
• UserID (INT, FOREIGN KEY REFERENCES Users(UserID))
• PhotoURL (VARCHAR(255), NOT NULL)
• Description (VARCHAR(255), NULL)
• IsMainPhoto (BIT, DEFAULT 0)

Таблица 9: Tariffs (Тарифы)

• TariffID (INT, PRIMARY KEY, IDENTITY)
• TariffName (VARCHAR(100), NOT NULL, UNIQUE)
• Description (TEXT, NULL)
• Price (DECIMAL(10, 2), NOT NULL)
• DurationDays (INT, NOT NULL)

Таблица 10: UserSubscriptions (Пользовательские Подписки)

• UserSubscriptionID (INT, PRIMARY KEY, IDENTITY)
• UserID (INT, FOREIGN KEY REFERENCES Users(UserID))
• TariffID (INT, FOREIGN KEY REFERENCES Tariffs(TariffID))
• StartDate (DATETIME2, NOT NULL)
• EndDate (DATETIME2, NOT NULL)
• Status (VARCHAR(50), NOT NULL)

Эта схема является результатом логического проектирования и готова к физической реализации в конкретной СУБД.

Физическая реализация базы данных в SQL Server: DDL

Физическая реализация — это этап, на котором абстрактная логическая модель превращается в реальную структуру базы данных, функционирующую в выбранной СУБД. В нашем случае это Microsoft SQL Server, мощная и гибкая платформа, предоставляющая широкий спектр инструментов для создания и управления базами данных. Ключевым языком на этом этапе является DDL (Data Definition Language).

Язык определения данных (DDL)

DDL, или язык определения данных, представляет собой набор операторов SQL, предназначенных для создания, изменения и удаления объектов базы данных, таких как таблицы, индексы, представления, схемы, триггеры и другие. Он позволяет определить структуру базы данных, её схему и ограничения, которые обеспечивают целостность и согласованность хранимых данных. Основные команды DDL включают:

• CREATE: Используется для создания новых объектов базы данных. Например, CREATE TABLE для создания таблицы, CREATE INDEX для создания индекса.
• ALTER: Применяется для изменения существующей структуры объекта базы данных. Например, ALTER TABLE ADD COLUMN для добавления нового столбца в таблицу.
• DROP: Предназначен для удаления объектов базы данных. Например, DROP TABLE для удаления таблицы.

Эти команды являются основой для построения любой реляционной базы данных.

Создание таблиц (CREATE TABLE)

Команда CREATE TABLE — это центральный оператор DDL для физического создания структуры таблицы в базе данных SQL Server. Она определяет имя таблицы, список её столбцов, тип данных для каждого столбца, а также различные ограничения целостности данных.

Синтаксис CREATE TABLE:

CREATE TABLE TableName (
    Column1 DataType [CONSTRAINT constraint_name] [NULL | NOT NULL] [DEFAULT default_value] [UNIQUE] [PRIMARY KEY] [CHECK condition] [IDENTITY(seed, increment)],
    Column2 DataType [CONSTRAINT constraint_name] [NULL | NOT NULL] [DEFAULT default_value] [UNIQUE] [CHECK condition],
    ...
    [CONSTRAINT PK_TableName PRIMARY KEY (ColumnA, ColumnB)],
    [CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY (ColumnX) REFERENCES ParentTable(ColumnY) [ON DELETE action] [ON UPDATE action]]
);

Типы данных SQL Server:
Выбор правильного типа данных критически важен для эффективности хранения, производительности запросов и целостности данных. SQL Server предлагает богатый набор типов данных:

• INT: Целые числа (от -2³¹ до 2³¹-1). Идеально подходит для идентификаторов.
• BIGINT: Большие целые числа (от -2⁶³ до 2⁶³-1). Для очень больших ID или счетчиков.
• DECIMAL(P, S) или NUMERIC(P, S): Точные числовые значения. P — общая точность (количество цифр), S — масштаб (количество цифр после десятичной точки). Например, DECIMAL(10, 2) для цен.
• VARCHAR(N): Строки переменной длины до N символов. VARCHAR(255) для имен, городов, VARCHAR(MAX) для очень длинных текстов.
• NVARCHAR(N): Строки переменной длины с поддержкой Unicode (для многоязычного текста).
• TEXT или NTEXT: Для очень больших текстовых полей (устаревшие, предпочтительнее VARCHAR(MAX) или NVARCHAR(MAX)).
• DATE: Дата без времени (ГГГГ-ММ-ДД).
• DATETIME или DATETIME2: Дата и время. DATETIME2 предлагает большую точность и диапазон.
• BIT: Булевы значения (0 или 1).
• UNIQUEIDENTIFIER: Глобально уникальный идентификатор (GUID).
• IDENTITY(seed, increment): Автоматически генерирует числовые значения для столбца, начиная с seed и увеличиваясь на increment при каждой новой вставке. Часто используется для первичных ключей.

Ограничения целостности:

• NOT NULL: Гарантирует, что столбец не может содержать пустые (NULL) значения.
• UNIQUE: Гарантирует, что все значения в столбце (или группе столбцов) уникальны в пределах таблицы. Может быть несколько UNIQUE ограничений на таблицу.
• DEFAULT value: Устанавливает значение по умолчанию для столбца, если при вставке строки значение для этого столбца не указано.
• CHECK condition: Проверяет данные на соответствие заданному условию. Например, CHECK (Age ≥ 18).
• COLLATE collation_name: Определяет правила сортировки и сравнения для символьных данных, что важно для региональных особенностей языка.

Определение первичных ключей (PRIMARY KEY)

Первичный ключ (PRIMARY KEY) — это фундаментальное ограничение в реляционных базах данных. Он служит для уникальной идентификации каждой строки в таблице. Столбец или комбинация столбцов, объявленные первичным ключом, должны удовлетворять двум условиям:

1. Уникальность: Каждое значение первичного ключа должно быть уникальным в таблице.
2. Неизменность (NOT NULL): Первичный ключ не может содержать NULL-значения.

Таблица может иметь только один первичный ключ.
При создании первичного ключа SQL Server автоматически создает уникальный индекс для этого столбца (или столбцов). Это обеспечивает быстрый поиск данных по первичному ключу. По умолчанию, если не указано иное, для первичного ключа создается кластеризованный индекс. Кластеризованный индекс определяет физический порядок хранения строк в таблице, что может значительно ускорить операции поиска, но таблица может иметь только один кластеризованный индекс. Если кластеризованный индекс уже определен на другом столбце, то для первичного ключа будет создан некластеризованный индекс, который представляет собой отдельную структуру данных, содержащую указатели на фактические строки таблицы.

Пример определения первичного ключа:

CREATE TABLE Users (
    UserID INT NOT NULL IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    -- ... другие столбцы
    CONSTRAINT PK_Users PRIMARY KEY (UserID)
);

Установка внешних ключей (FOREIGN KEY)

Внешний ключ (FOREIGN KEY) — это столбец или набор столбцов в одной таблице (дочерней), которые ссылаются на первичный ключ другой таблицы (родительской). Внешний ключ используется для установления связей между таблицами и обеспечения ссылочной целостности. Ссылочная целостность гарантирует, что связь между таблицами остаётся корректной: невозможно добавить строку в дочернюю таблицу, если соответствующего значения не существует в родительской таблице, и невозможно удалить или обновить строку в родительской таблице, если на неё есть ссылки из дочерней.

Синтаксис для определения внешнего ключа:

ALTER TABLE ChildTable
ADD CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY (ChildColumn)
REFERENCES ParentTable (ParentColumn)
[ON DELETE action]
[ON UPDATE action];

Опции ON DELETE и ON UPDATE:
Эти опции определяют поведение СУБД при попытке удаления или обновления строки в родительской таблице, на которую ссылается внешний ключ:

• NO ACTION (по умолчанию): Операция удаления/обновления родительской строки отменяется, если существуют ссылающиеся на неё дочерние строки.
• CASCADE: При удалении/обновлении родительской строки, соответствующие дочерние строки также удаляются/обновляются. (Например, удаляем пользователя — удаляются все его анкеты, фото, сообщения).
• SET NULL: При удалении/обновлении родительской строки, значения внешнего ключа в дочерних строках устанавливаются в NULL (требует, чтобы столбец внешнего ключа в дочерней таблице допускал NULL-значения).
• SET DEFAULT: При удалении/обновлении родительской строки, значения внешнего ключа в дочерних строках устанавливаются в значения по умолчанию (если таковые определены для столбца внешнего ключа).

Пример внешнего ключа д��я «Бюро знакомств»:

ALTER TABLE Profiles
ADD CONSTRAINT FK_Profiles_Users FOREIGN KEY (UserID)
REFERENCES Users (UserID)
ON DELETE CASCADE
ON UPDATE CASCADE;

Этот пример означает, что если пользователь удаляется из таблицы Users, его профиль в таблице Profiles также будет автоматически удален. Аналогично, при изменении UserID в Users, соответствующий UserID в Profiles также обновится.

Скрипты DDL для базы данных «Бюро знакомств»

Ниже представлены полные SQL-скрипты для создания всех таблиц базы данных «Бюро знакомств» в SQL Server, включая первичные и внешние ключи, типы данных и другие ограничения.

-- Создание базы данных
USE master;
GO

IF DB_ID('DatingAppDB') IS NOT NULL
DROP DATABASE DatingAppDB;
GO

CREATE DATABASE DatingAppDB;
GO

USE DatingAppDB;
GO

-- 1. Таблица Users (Пользователи)
CREATE TABLE Users (
    UserID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender NVARCHAR(10) NOT NULL CHECK (Gender IN ('Мужской', 'Женский', 'Другой')),
    City NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE,
    PhoneNumber NVARCHAR(15) UNIQUE NULL,
    PasswordHash NVARCHAR(255) NOT NULL,
    ProfilePictureURL NVARCHAR(255) NULL,
    RegistrationDate DATETIME2 DEFAULT GETDATE()
);
GO

-- 2. Таблица Profiles (Анкеты)
CREATE TABLE Profiles (
    ProfileID INT IDENTITY(1,1) PRIMARY KEY,
    UserID INT NOT NULL UNIQUE,
    AboutMe NVARCHAR(MAX) NULL,
    Hobbies NVARCHAR(255) NULL,
    Education NVARCHAR(100) NULL,
    Profession NVARCHAR(100) NULL,
    BadHabits NVARCHAR(255) NULL,
    MaritalStatus NVARCHAR(50) NULL CHECK (MaritalStatus IN ('Не женат/не замужем', 'В отношениях', 'В браке', 'Разведен/разведена', 'Вдовец/вдова', 'Всё сложно')),
    CONSTRAINT FK_Profiles_Users FOREIGN KEY (UserID) REFERENCES Users(UserID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
GO

-- 3. Таблица Interests (Интересы)
CREATE TABLE Interests (
    InterestID INT IDENTITY(1,1) PRIMARY KEY,
    InterestName NVARCHAR(100) NOT NULL UNIQUE
);
GO

-- 4. Таблица ProfileInterests (Интересы Анкет) - для реализации связи M:M между Profiles и Interests
CREATE TABLE ProfileInterests (
    ProfileID INT NOT NULL,
    InterestID INT NOT NULL,
    PRIMARY KEY (ProfileID, InterestID),
    CONSTRAINT FK_ProfileInterests_Profiles FOREIGN KEY (ProfileID) REFERENCES Profiles(ProfileID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT FK_ProfileInterests_Interests FOREIGN KEY (InterestID) REFERENCES Interests(InterestID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
GO

-- 5. Таблица Preferences (Предпочтения)
CREATE TABLE Preferences (
    PreferenceID INT IDENTITY(1,1) PRIMARY KEY,
    UserID INT NOT NULL UNIQUE,
    PreferredGender NVARCHAR(10) NULL CHECK (PreferredGender IN ('Мужской', 'Женский', 'Другой')),
    MinAge INT NULL CHECK (MinAge ≥ 18 AND MinAge ≤ MaxAge),
    MaxAge INT NULL CHECK (MaxAge ≥ 18 AND MaxAge ≥ MinAge),
    PreferredCity NVARCHAR(100) NULL,
    CONSTRAINT FK_Preferences_Users FOREIGN KEY (UserID) REFERENCES Users(UserID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
GO

-- 6. Таблица Matches (Совпадения)
CREATE TABLE Matches (
    MatchID INT IDENTITY(1,1) PRIMARY KEY,
    User1ID INT NOT NULL,
    User2ID INT NOT NULL,
    MatchDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    Status NVARCHAR(50) NOT NULL CHECK (Status IN ('Лайк', 'Взаимный лайк', 'Отклонено', 'Просмотрено')),
    CONSTRAINT FK_Matches_User1 FOREIGN KEY (User1ID) REFERENCES Users(UserID),
    CONSTRAINT FK_Matches_User2 FOREIGN KEY (User2ID) REFERENCES Users(UserID),
    CONSTRAINT UQ_Matches_Users UNIQUE (User1ID, User2ID) -- Предотвращает дублирование пар совпадений
);
GO

-- 7. Таблица Messages (Сообщения)
CREATE TABLE Messages (
    MessageID INT IDENTITY(1,1) PRIMARY KEY,
    SenderID INT NOT NULL,
    ReceiverID INT NOT NULL,
    MessageText NVARCHAR(MAX) NOT NULL,
    Timestamp DATETIME2 NOT NULL DEFAULT GETDATE(),
    IsRead BIT DEFAULT 0,
    CONSTRAINT FK_Messages_Sender FOREIGN KEY (SenderID) REFERENCES Users(UserID),
    CONSTRAINT FK_Messages_Receiver FOREIGN KEY (ReceiverID) REFERENCES Users(UserID)
);
GO

-- 8. Таблица Photos (Фотографии)
CREATE TABLE Photos (
    PhotoID INT IDENTITY(1,1) PRIMARY KEY,
    UserID INT NOT NULL,
    PhotoURL NVARCHAR(255) NOT NULL,
    Description NVARCHAR(255) NULL,
    IsMainPhoto BIT DEFAULT 0,
    CONSTRAINT FK_Photos_Users FOREIGN KEY (UserID) REFERENCES Users(UserID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
GO

-- 9. Таблица Tariffs (Тарифы/Подписки)
CREATE TABLE Tariffs (
    TariffID INT IDENTITY(1,1) PRIMARY KEY,
    TariffName NVARCHAR(100) NOT NULL UNIQUE,
    Description NVARCHAR(MAX) NULL,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price ≥ 0),
    DurationDays INT NOT NULL CHECK (DurationDays > 0)
);
GO

-- 10. Таблица UserSubscriptions (Пользовательские Подписки)
CREATE TABLE UserSubscriptions (
    UserSubscriptionID INT IDENTITY(1,1) PRIMARY KEY,
    UserID INT NOT NULL,
    TariffID INT NOT NULL,
    StartDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    EndDate DATETIME2 NOT NULL,
    Status NVARCHAR(50) NOT NULL CHECK (Status IN ('Активна', 'Завершена', 'Отменена', 'Приостановлена')),
    CONSTRAINT FK_UserSubscriptions_Users FOREIGN KEY (UserID) REFERENCES Users(UserID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT FK_UserSubscriptions_Tariffs FOREIGN KEY (TariffID) REFERENCES Tariffs(TariffID)
    ON DELETE NO ACTION -- Тарифы не должны удаляться, если на них ссылаются подписки
    ON UPDATE CASCADE
);
GO

Эти скрипты создают полную структуру базы данных «Бюро знакомств» в SQL Server, обеспечивая все необходимые связи и ограничения для поддержания целостности данных.

Манипулирование данными и объекты базы данных в SQL Server

После создания структуры базы данных настает очередь работы с самими данными. На этом этапе в игру вступает DML (Data Manipulation Language), который позволяет вставлять, обновлять, удалять и извлекать информацию. Однако современные базы данных — это не просто хранилища; они являются активными компонентами систем, реализующими сложную бизнес-логику. Для этого SQL Server предоставляет богатый набор программных объектов: хранимые процедуры, представления, триггеры и, в некоторых случаях, курсоры.

Язык манипулирования данными (DML)

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

• SELECT: Самый часто используемый оператор, предназначенный для извлечения данных из одной или нескольких таблиц. Позволяет фильтровать, сортировать, группировать и агрегировать данные.
  • Пример: SELECT FirstName, LastName, City FROM Users WHERE Gender = 'Женский';
• INSERT: Используется для добавления новых строк данных в таблицу.
  • Пример: INSERT INTO Users (FirstName, LastName, DateOfBirth, Gender, City, Email, PasswordHash) VALUES ('Анна', 'Смирнова', '1995-03-15', 'Женский', 'Москва', 'anna.smirnova@example.com', 'hashed_password_123');
• UPDATE: Применяется для изменения существующих данных в одной или нескольких строках таблицы.
  • Пример: UPDATE Users SET City = 'Санкт-Петербург' WHERE UserID = 1;
• DELETE: Используется для удаления строк данных из таблицы.
  • Пример: DELETE FROM Users WHERE UserID = 5;

Эти операторы являются базисом для всех операций с данными в «Бюро знакомств».

Хранимые процедуры (Stored Procedures)

Хранимая процедура в SQL Server — это заранее скомпилированный набор одной или нескольких инструкций Transact-SQL (или ссылка на метод .NET Framework CLR), который хранится в базе данных и может быть вызван по имени. Они являются мощным инструментом для инкапсуляции бизнес-логики и автоматизации рутинных операций.

Преимущества хранимых процедур:

• Производительность: Инструкции SQL компилируются один раз при первом выполнении и кэшируются. Это сокращает время обработки при последующих вызовах, так как СУБД не нужно каждый раз анализировать и оптимизировать запрос.
• Снижение сетевых издержек: Вместо отправки множества отдельных SQL-инструкций по сети, приложение отправляет один вызов хранимой процедуры, что уменьшает трафик.
• Повышение безопасности: Пользователям можно предоставлять разрешения на выполнение хранимых процедур, не давая прямого доступа к базовым таблицам.
• Согласованность и точность: Сложная бизнес-логика реализуется один раз в процедуре, обеспечивая её единообразное применение.
• Модульность и переиспользование: Процедуры легко вызывать из разных приложений, что упрощает разработку и поддержку.
• Поддержка условных и циклических конструкций: Позволяют реализовывать сложную логику, такую как IF/ELSE, WHILE, TRY/CATCH.

Примеры хранимых процедур для «Бюро знакомств»:

1. usp_RegisterUser: Для регистрации нового пользователя.

CREATE PROCEDURE usp_RegisterUser
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @DateOfBirth DATE,
    @Gender NVARCHAR(10),
    @City NVARCHAR(100),
    @Email NVARCHAR(100),
    @PasswordHash NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        INSERT INTO Users (FirstName, LastName, DateOfBirth, Gender, City, Email, PasswordHash)
        VALUES (@FirstName, @LastName, @DateOfBirth, @Gender, @City, @Email, @PasswordHash);

        DECLARE @NewUserID INT = SCOPE_IDENTITY();

        -- Автоматически создать пустой профиль и предпочтения для нового пользователя
        INSERT INTO Profiles (UserID) VALUES (@NewUserID);
        INSERT INTO Preferences (UserID) VALUES (@NewUserID);

        SELECT @NewUserID AS NewUserID;
    END TRY
    BEGIN CATCH
        -- Обработка ошибок, например, дубликат email
        DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
        RAISERROR (@ErrorMessage, 16, 1);
    END CATCH
END;
GO

2. usp_FindMatches: Для поиска потенциальных совпадений на основе предпочтений пользователя.

CREATE PROCEDURE usp_FindMatches
    @UserID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        u.UserID,
        u.FirstName,
        u.LastName,
        u.DateOfBirth,
        u.Gender,
        u.City,
        u.ProfilePictureURL,
        p.AboutMe,
        p.Hobbies,
        pi_agg.InterestsList
    FROM Users u
    JOIN Profiles p ON u.UserID = p.UserID
    LEFT JOIN (
        SELECT
            pi.ProfileID,
            STRING_AGG(i.InterestName, ', ') WITHIN GROUP (ORDER BY i.InterestName) AS InterestsList
        FROM ProfileInterests pi
        JOIN Interests i ON pi.InterestID = i.InterestID
        GROUP BY pi.ProfileID
    ) AS pi_agg ON p.ProfileID = pi_agg.ProfileID
    WHERE u.UserID <> @UserID -- Не показывать самого пользователя
      AND NOT EXISTS ( -- Исключить уже существующие совпадения
          SELECT 1 FROM Matches
          WHERE (User1ID = @UserID AND User2ID = u.UserID)
             OR (User1ID = u.UserID AND User2ID = @UserID)
      )
      AND u.Gender = (SELECT PreferredGender FROM Preferences WHERE UserID = @UserID) -- Соответствие по полу
      AND (YEAR(GETDATE()) - YEAR(u.DateOfBirth) BETWEEN (SELECT MinAge FROM Preferences WHERE UserID = @UserID) AND (SELECT MaxAge FROM Preferences WHERE UserID = @UserID)) -- Соответствие по возрасту
      AND u.City = (SELECT PreferredCity FROM Preferences WHERE UserID = @UserID) -- Соответствие по городу
    ORDER BY u.RegistrationDate DESC; -- Сначала новые пользователи
END;
GO

3. usp_SendMessage: Для отправки сообщения от одного пользователя другому.

CREATE PROCEDURE usp_SendMessage
    @SenderID INT,
    @ReceiverID INT,
    @MessageText NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Messages (SenderID, ReceiverID, MessageText, Timestamp)
    VALUES (@SenderID, @ReceiverID, @MessageText, GETDATE());
    SELECT SCOPE_IDENTITY() AS NewMessageID;
END;
GO

Представления (Views)

Представление в SQL — это виртуальная таблица, основанная на результате запроса. Представления не хранят данные сами по себе (за исключением индексированных представлений в SQL Server), а представляют собой сохраненный запрос, который выполняется каждый раз при обращении к представлению.

Назначение представлений:

• Упрощение сложных запросов: Позволяют инкапсулировать сложную логику JOIN-ов и фильтрации, предоставляя пользователю или приложению простую «таблицу».
• Безопасность: Можно предоставлять пользователям доступ к представлению, не давая им прямого доступа к базовым таблицам, что позволяет скрывать конфиденциальные столбцы или строки.
• Агрегация данных: Могут использоваться для предоставления агрегированных данных (например, для отчетов), таких как количество активных пользователей или средний возраст.
• Совместимость: Позволяют сохранять совместимость со старым кодом или приложениями при изменении схемы базовых таблиц.
• Индексированные представления (в SQL Server): В MS SQL Server можно создавать индексированные (материализованные) представления, которые физически хранят данные и индексы, значительно ускоряя выполнение запросов к ним.

Примеры представлений для «Бюро знакомств»:

1. vw_ActiveSubscriptions: Показывает всех пользователей с активными подписками.

CREATE VIEW vw_ActiveSubscriptions
AS
SELECT
    u.UserID,
    u.FirstName,
    u.LastName,
    u.Email,
    t.TariffName,
    us.StartDate,
    us.EndDate
FROM Users u
JOIN UserSubscriptions us ON u.UserID = us.UserID
JOIN Tariffs t ON us.TariffID = t.TariffID
WHERE us.Status = 'Активна' AND us.EndDate ≥ GETDATE();
GO

2. vw_UserProfilesWithPreferences: Объединенная информация о пользователе, его анкете и предпочтениях.

CREATE VIEW vw_UserProfilesWithPreferences
AS
SELECT
    u.UserID,
    u.FirstName,
    u.LastName,
    u.DateOfBirth,
    u.Gender,
    u.City,
    u.Email,
    u.ProfilePictureURL,
    p.AboutMe,
    p.Hobbies,
    p.Education,
    p.Profession,
    p.BadHabits,
    p.MaritalStatus,
    pref.PreferredGender,
    pref.MinAge,
    pref.MaxAge,
    pref.PreferredCity,
    STRING_AGG(i.InterestName, ', ') WITHIN GROUP (ORDER BY i.InterestName) AS Interests
FROM Users u
LEFT JOIN Profiles p ON u.UserID = p.UserID
LEFT JOIN Preferences pref ON u.UserID = pref.UserID
LEFT JOIN ProfileInterests pi ON p.ProfileID = pi.ProfileID
LEFT JOIN Interests i ON pi.InterestID = i.InterestID
GROUP BY
    u.UserID, u.FirstName, u.LastName, u.DateOfBirth, u.Gender, u.City, u.Email, u.ProfilePictureURL,
    p.AboutMe, p.Hobbies, p.Education, p.Profession, p.BadHabits, p.MaritalStatus,
    pref.PreferredGender, pref.MinAge, pref.MaxAge, pref.PreferredCity;
GO

Триггеры (Triggers)

Триггер — это специальный тип хранимой процедуры, который автоматически выполняется (срабатывает) в ответ на определенные события в базе данных. В SQL Server существуют два основных типа триггеров:

• DML-триггеры: Срабатывают в ответ на операции языка манипулирования данными (INSERT, UPDATE, DELETE) над таблицами. Они могут быть определены как AFTER (после выполнения операции) или INSTEAD OF (вместо выполнения операции, перехватывая её).
• DDL-триггеры: Срабатывают в ответ на события языка определения данных (CREATE, ALTER, DROP) или системные события (LOGON, STARTUP).

Применение триггеров:

• Аудит и ведение журнала изменений: Автоматическая запись всех изменений данных в специальные таблицы аудита.
• Обеспечение сложных бизнес-правил: Реализация правил, которые не могут быть выражены с помощью стандартных ограничений (например, проверка сложной взаимосвязи между несколькими таблицами).
• Поддержание сложной ссылочной целостности: Действия, которые выходят за рамки ON DELETE/UPDATE CASCADE/SET NULL/SET DEFAULT.
• Запрет определенных операций: Блокировка попыток удаления или изменения данных при определенных условиях.

Триггеры используют две специальные временные таблицы: inserted (содержит новые данные после INSERT или UPDATE) и deleted (содержит старые данные перед DELETE или UPDATE).

Примеры триггеров для «Бюро знакомств»:

1. trg_LogMessageActivity: Триггер для записи всех отправленных сообщений в журнал аудита.

-- Создадим таблицу для аудита сообщений
CREATE TABLE MessageAuditLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    MessageID INT,
    SenderID INT,
    ReceiverID INT,
    MessageText NVARCHAR(MAX),
    Timestamp DATETIME2,
    ActionType NVARCHAR(50), -- 'INSERT', 'UPDATE', 'DELETE'
    AuditDate DATETIME2 DEFAULT GETDATE()
);
GO

CREATE TRIGGER trg_LogMessageActivity
ON Messages
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Логирование INSERT
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO MessageAuditLog (MessageID, SenderID, ReceiverID, MessageText, Timestamp, ActionType)
        SELECT MessageID, SenderID, ReceiverID, MessageText, Timestamp, 'INSERT'
        FROM inserted;
    END

    -- Логирование UPDATE (для простоты, логируем как изменения)
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO MessageAuditLog (MessageID, SenderID, ReceiverID, MessageText, Timestamp, ActionType)
        SELECT MessageID, SenderID, ReceiverID, MessageText, Timestamp, 'UPDATE'
        FROM inserted; -- Можно также логировать старые значения из deleted
    END

    -- Логирование DELETE
    IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO MessageAuditLog (MessageID, SenderID, ReceiverID, MessageText, Timestamp, ActionType)
        SELECT MessageID, SenderID, ReceiverID, MessageText, Timestamp, 'DELETE'
        FROM deleted;
    END
END;
GO

2. trg_PreventDuplicateProfile: Триггер для предотвращения создания дублирующих профилей для одного пользователя, если это не было обеспечено UNIQUE ограничением на UserID в таблице Profiles (но в нашем DDL это уже есть, так что это скорее демонстрационный пример).

-- Этот триггер избыточен, так как UNIQUE на UserID в Profiles уже обеспечивает это.
-- Он демонстрирует логику INSTEAD OF триггера.
CREATE TRIGGER trg_PreventDuplicateProfile
ON Profiles
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM Users u JOIN inserted i ON u.UserID = i.UserID)
    BEGIN
        -- Проверяем, существует ли уже профиль для данного UserID
        IF EXISTS (SELECT 1 FROM Profiles p JOIN inserted i ON p.UserID = i.UserID)
        BEGIN
            RAISERROR('Профиль для данного пользователя уже существует.', 16, 1);
            ROLLBACK TRANSACTION; -- Откатить операцию вставки
        END
        ELSE
        BEGIN
            -- Если профиля нет, выполняем вставку
            INSERT INTO Profiles (UserID, AboutMe, Hobbies, Education, Profession, BadHabits, MaritalStatus)
            SELECT UserID, AboutMe, Hobbies, Education, Profession, BadHabits, MaritalStatus
            FROM inserted;
        END
    END
    ELSE
    BEGIN
        RAISERROR('Пользователь с указанным UserID не существует.', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;
GO

Курсоры (Cursors)

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

Применение курсоров:
Курсоры используются в ситуациях, когда наборная логика SQL не подходит, например, когда требуется выполнить сложную логику обработки для каждой отдельной строки, которая зависит от состояния предыдущих обработанных строк, или когда нужно выполнить операции, не поддерживаемые напрямую наборными командами.

Потенциальные проблемы с производительностью:
Использование курсоров может значительно снизить производительность по сравнению с наборными операциями. Это происходит из-за:

• Настроек и накладных расходов: Для каждого курсора СУБД должна поддерживать состояние, блокировки и контекст, что требует ресурсов.
• Построчная обработка: Вместо эффективной обработки больших объемов данных за одну операцию, курсор обрабатывает их по одной строке, что приводит к большему количеству операций ввода-вывода и переключений контекста.
• Блокировки: Курсоры могут дольше удерживать блокировки на строках, что снижает параллелизм и может приводить к взаимоблокировкам.

В большинстве случаев, когда это возможно, следует отдавать предпочтение наборным операциям (например, UPDATE с JOIN, подзапросы) вместо курсоров, так как они гораздо эффективнее.
Тем не менее, для демонстрации концепции, вот краткий пример использования курсора:

-- Пример использования курсора для вычисления возраста каждого пользователя
-- (На практике это лучше делать через SELECT с функцией DATEDIFF или YEAR(GETDATE()) - YEAR(DateOfBirth))
DECLARE @UserID INT, @DateOfBirth DATE, @Age INT;

DECLARE UserCursor CURSOR FOR
FOR SELECT UserID, DateOfBirth FROM Users;

OPEN UserCursor;

FETCH NEXT FROM UserCursor INTO @UserID, @DateOfBirth;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Age = DATEDIFF(year, @DateOfBirth, GETDATE());
    -- Здесь можно было бы выполнить какую-то логику для каждого пользователя
    PRINT 'Пользователь ID: ' + CAST(@UserID AS NVARCHAR(10)) + ', Возраст: ' + CAST(@Age AS NVARCHAR(10));

    FETCH NEXT FROM UserCursor INTO @UserID, @DateOfBirth;
END;

CLOSE UserCursor;
DEALLOCATE UserCursor;
GO

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

Заключение

Проделанная работа по проектированию и реализации базы данных для «Бюро знакомств» демонстрирует полный цикл создания сложной информационной системы, начиная от высокоуровневого концептуального осмысления и заканчивая физической имплементацией в СУБД SQL Server. Мы успешно прошли все ключевые этапы: от идентификации сущностей и их атрибутов в рамках ER-модели, до логической трансформации в нормализованную реляционную схему, и, наконец, до физической реализации с использованием DDL-скриптов и создания функциональных объектов базы данных.

Разработанная концептуальная модель охватывает все основные аспекты предметной области «Бюро знакомств», включая управление пользователями, их анкетами, предпочтениями, совпадениями, сообщениями, фотографиями и системой подписок. Процесс нормализации, доведенный до 3НФ и НФБК, обеспечил минимизацию избыточности данных и устранение аномалий, гарантируя высокую степень целостности и надежности. Физическая реализация в SQL Server с использованием DDL-команд для создания таблиц, определения первичных и внешних ключей, а также других ограничений, подтверждает практическую применимость разработанной модели. Кроме того, создание хранимых процедур, представлений и триггеров демонстрирует возможности SQL Server по реализации сложной бизнес-логики, автоматизации операций, повышению безопасности и улучшению производительности. Это подтверждает, что комплексное проектирование баз данных — это не просто набор технических операций, а искусство трансформации бизнес-требований в эффективную и масштабируемую информационную структуру, что является основой для создания устойчивых и функциональных программных решений.

Список использованной литературы

1. Базы данных : интеллектуал. обраб. информ. / В. В. Корнеев [и др.]. 2-е изд. Москва : Изд-во Молгачева С. В., 2001. 494 с.
2. Грабер М. Справочное руководство по SQL. Москва : Лори, 1998. 374 с.
3. Дейт К. Дж. Введение в системы баз данных: пер. с англ. 7-е изд. Москва : Вильямс, 2001. 1071 с.
4. Диго С.М. Базы данных : проектирование и использование : учеб. для вузов по спец. «Прикладная информатика (по областям)» . Москва : Финансы и статистика, 2005. 591 с.
5. Коннолли Т., Бегг К. Базы данных: Проектирование, реализация и сопровождение: теория и практика: пер. с англ. 2-е изд. Москва : Вильямс, 2000. 1111 с.
6. Малыхина М.П. Базы данных: основы, проектирование, использование : учеб. пособие. Санкт-Петербург : БХВ-Петербург, 2004. 499 с.
7. Марков А.С., Лисовский К.Ю. Базы данных: Введение в теорию и методологию: Учебник. Москва : Финансы и статистика, 2004. 511 с.
8. Смирнов С.Н. Безопасность систем баз данных: учеб. пособие для высш. учеб. заведений по специальностям в обл. информ. безопасности. Москва : Гелиос АРВ, 2007. 351 с.
9. Смирнова Г. Н., Сорокин А. А., Тельнов Ю. Ф. Проектирование экономических информационных систем : учеб. для экон. вузов. Москва : Финансы и статистика, 2005. 510 с.
10. Ульман Джеффри Д. Введение в системы баз данных : перевод. Москва : Лори, 2000. 374 с.
11. Создание таблиц в MS SQL Server. CREATE TABLE. OTUS. URL: https://otus.ru/journal/sozdanie-tablits-v-ms-sql-server-create-table/ (дата обращения: 29.10.2025).
12. MS SQL Server и T-SQL | Создание и удаление таблиц. METANIT.COM. URL: https://metanit.com/sql/sqlserver/2.1.php (дата обращения: 29.10.2025).
13. Инструкция CREATE TABLE (Transact-SQL) — SQL Server. Microsoft Learn. URL: https://learn.microsoft.com/ru-ru/sql/t-sql/statements/create-table-transact-sql (дата обращения: 29.10.2025).
14. Создаем таблицу в SQL. CodeChick. URL: https://codechick.io/sql/create-table/ (дата обращения: 29.10.2025).
15. Создание таблиц (ядро СУБД) — SQL Server. Microsoft Learn. URL: https://learn.microsoft.com/ru-ru/sql/relational-databases/tables/create-tables-database-engine (дата обращения: 29.10.2025).
16. Нормальная форма Бойса-Кодда (BCNF). AppMaster. URL: https://appmaster.io/ru/blog/normalnaya-forma-boysa-kodda-bcnf (дата обращения: 29.10.2025).
17. Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть первая. URL: http://www.info-comp.ru/obucheniest/710-uchebnik-po-yazyku-sql-ddl-dml-na-primere-dialekta-ms-sql-server-chast-pervaya.html (дата обращения: 29.10.2025).
18. Ограничения первичных и внешних ключей — SQL Server. Microsoft Learn. URL: https://learn.microsoft.com/ru-ru/sql/relational-databases/tables/primary-and-foreign-key-constraints (дата обращения: 29.10.2025).
19. Хранимые процедуры (ядро СУБД) — SQL Server. Learn Microsoft. URL: https://learn.microsoft.com/ru-ru/sql/relational-databases/stored-procedures/stored-procedures-database-engine (дата обращения: 29.10.2025).
20. Представления в SQL: общее понятие и примеры. FB.ru. URL: https://fb.ru/article/480820/predstavleniya-v-sql-obschee-ponyatie-i-primeryi (дата обращения: 29.10.2025).
21. Нормализация отношений. Шесть нормальных форм. Habr. URL: https://habr.com/ru/articles/255013/ (дата обращения: 29.10.2025).
22. SQL Commands: DML and DDL in SQL. AlmaBetter. URL: https://www.almabetter.com/resources/blog/sql-commands-ddl-dml-dcl-tcl (дата обращения: 29.10.2025).
23. CREATE TRIGGER (Transact-SQL) — SQL Server. Microsoft Learn. URL: https://learn.microsoft.com/ru-ru/sql/t-sql/statements/create-trigger-transact-sql (дата обращения: 29.10.2025).
24. Базы данных. Высшая школа экономики. URL: https://www.hse.ru/data/2010/09/13/1220461821/%D0%91%D0%B0%D0%B7%D1%8B%20%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85.pdf (дата обращения: 29.10.2025).
25. Триггеры DDL — SQL Server. Microsoft Learn. URL: https://learn.microsoft.com/ru-ru/sql/relational-databases/triggers/ddl-triggers (дата обращения: 29.10.2025).
26. Выполнение хранимых процедур — SQL Server. Microsoft Learn. URL: https://learn.microsoft.com/ru-ru/sql/relational-databases/stored-procedures/execute-a-stored-procedure (дата обращения: 29.10.2025).
27. MS SQL Server и T-SQL | Определение триггеров. METANIT.COM. URL: https://metanit.com/sql/sqlserver/5.2.php (дата обращения: 29.10.2025).
28. Что такое DDL, DML, DCL и TCL в языке SQL. Info-Comp.ru — IT-блог для начинающих. URL: http://www.info-comp.ru/obucheniest/1018-chto-takoe-ddl-dml-dcl-i-tcl-v-yazyke-sql.html (дата обращения: 29.10.2025).
29. DDL, DML, DCL и TCL: что это такое, за что отвечают операторы языка SQL и какие команды они включают. Skypro. URL: https://sky.pro/media/ddl-dml-dcl-i-tcl-chto-ehto-takoe-za-chto-otvechayut-operatory-yazyka-sql-i-kakie-komandy-oni-vklyuchayut/ (дата обращения: 29.10.2025).
30. Difference Between DDL & DML | Data Definition Language, Data Manipulation Language. PNTutorial.org. URL: https://www.pntutorial.org/ddl-vs-dml (дата обращения: 29.10.2025).
31. Triggers in SQL Server. SQLShack. URL: https://www.sqlshack.com/triggers-in-sql-server/ (дата обращения: 29.10.2025).
32. MS SQL Server и T-SQL | Внешние ключи. METANIT.COM. URL: https://metanit.com/sql/sqlserver/2.2.php (дата обращения: 29.10.2025).
33. Взаимные блокировки и внешние ключи в SQL Server. Habr. URL: https://habr.com/ru/articles/573534/ (дата обращения: 29.10.2025).
34. Базы данных. Проектирование, реализация и сопровождение (Томас Коннолли, Каролин Бегг). Softline. URL: https://www.softline.ru/books/39908 (дата обращения: 29.10.2025).
35. Первичный ключ и внешний ключ: 9 важных отличий. Astera Software. URL: https://www.asterasoftware.com/ru/blog/primary-key-vs-foreign-key (дата обращения: 29.10.2025).
36. Видеокурс по SQL Essential. Урок 8. Хранимые процедуры. Пользовательские функции. ITVDN. URL: http://itvdn.com/ru/video/sql-essential (дата обращения: 29.10.2025).