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

Создание тестовой базы данных SQL Server

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

-- Создание HRDatabase
USE master
GO

-- Команда удаления базы данных
DROP DATABASE IF EXISTS HRDatabase
GO

-- Команда создания базы данных SQL с файлами и свойствами по умолчанию
CREATE DATABASE HRDatabase
GO

-- Использование текущей базы данных
USE HRDatabase -- Имя базы данных
GO

Создание основных таблиц

Затем мы создадим две основные таблицы для нашей базы данных HR: Companies и Employees. В этих таблицах будет храниться информация о компаниях и их сотрудниках. Вот SQL-код для создания этих таблиц:

-- Создание двух основных таблиц Companies и Employees
DROP TABLE IF EXISTS Companies;
DROP TABLE IF EXISTS Employees;

CREATE TABLE Companies (
   id             INT CONSTRAINT PK_Companies PRIMARY KEY IDENTITY,
   CompanyName    VARCHAR(80) NOT NULL,
   CompAddress    VARCHAR(80) NOT NULL,
   CompContactNo  VARCHAR(20) NOT NULL,
   IsActive       BIT CONSTRAINT DF_IsActive_Companies DEFAULT(1),
   CreateDate     DATETIME NOT NULL DEFAULT getdate()
);

CREATE TABLE Employees (
   id             INT CONSTRAINT PK_Employees PRIMARY KEY IDENTITY,
   EmployeeName   VARCHAR(80) NOT NULL,
   ContactNo      VARCHAR(20) NOT NULL,
   Email          VARCHAR(80) NOT NULL,
   CompID         INT NOT NULL,
   AddressID      INT NOT NULL,
   TitleID        INT NOT NULL,
   GenderID       INT NOT NULL,
   IsActive       BIT CONSTRAINT DF_IsActive_Employees DEFAULT(1),
   CreateDate     DATETIME NOT NULL DEFAULT getdate()
);

Создание вспомогательных таблиц

Помимо основных таблиц, нам также необходимо создать вспомогательные таблицы, которые будут использоваться в нашем приложении. Эти таблицы включают Title, Gender, LeaveTypes и Addresses. Вот SQL-код для создания этих таблиц:

-- Создание вспомогательных таблиц
DROP TABLE IF EXISTS Title;
DROP TABLE IF EXISTS Gender;
DROP TABLE IF EXISTS LeaveTypes;
DROP TABLE IF EXISTS Leave;
DROP TABLE IF EXISTS Addresses;

CREATE TABLE Title (
   id          INT CONSTRAINT PK_Title PRIMARY KEY IDENTITY,
   Title       VARCHAR(80) NOT NULL,
   TitleDescr  VARCHAR(80) NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);

CREATE TABLE Gender (
   id          INT CONSTRAINT PK_Gender PRIMARY KEY IDENTITY,
   Gender      VARCHAR(80) NOT NULL,
   GenderDescr VARCHAR(80) NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);

CREATE TABLE LeaveTypes (
   id          INT CONSTRAINT PK_LeaveTypes PRIMARY KEY IDENTITY,
   LeaveType   VARCHAR(80) NOT NULL,
   LeaveDescr  VARCHAR(80) NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);

CREATE TABLE Addresses (
   id          INT CONSTRAINT PK_Addresses PRIMARY KEY IDENTITY,
   Address     VARCHAR(256) NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);

CREATE TABLE Leave (
   id          INT CONSTRAINT PK_Leave PRIMARY KEY IDENTITY,
   LeaveTypeID INT NOT NULL,
   StartDate   DATE NOT NULL,
   EndDate     DATE NOT NULL,
   LeaveNotes  VARCHAR(800) NOT NULL,
   EmpID       INT NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);

Добавление внешних ключей для создания отношений

Для установления отношений между таблицами нам нужно добавить внешние ключи. Внешний ключ – это столбец в одной таблице, который ссылается на первичный ключ в другой таблице. Добавляя внешние ключи, мы обеспечиваем ссылочную целостность между таблицами. Вот SQL-код для добавления внешних ключей:

-- Добавление внешних ключей, создающих связи между соответствующими таблицами
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Companies
  FOREIGN KEY(CompID) REFERENCES Companies(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Addresses
  FOREIGN KEY(AddressID) REFERENCES Addresses(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Titles
  FOREIGN KEY(TitleID) REFERENCES Title(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Genders
  FOREIGN KEY(GenderID) REFERENCES Gender(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE dbo.Leave ADD CONSTRAINT FK_Leave_Employees 
  FOREIGN KEY(EmpID) REFERENCES Employees(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE dbo.Leave ADD CONSTRAINT FK_Leave_LeaveTypes
  FOREIGN KEY(LeaveTypeID)REFERENCES LeaveTypes(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

Вставка образцовых данных

Теперь, когда наши таблицы и отношения настроены, мы можем вставить образцовые данные в таблицы. Это позволит нам протестировать наши запросы и увидеть, как данные связаны. Вот SQL-код для вставки образцовых данных:

-- Вставка компаний
INSERT INTO Companies (CompanyName, CompAddress, CompContactNo) 
VALUES
  ('Alpha Company', '123 North Street, Garsfontein, Pretoria', '091 523 6987' ),
  ('Bravo Company', '456 South Street, Brooklyn, Pretoria', '091 523 4789' ),
  ('Charlie Company', '987 West Street, Lynnwood, Pretoria', '091 523 1235' ),
  ('Delta Company', '258 East Street, The Meadows, Pretoria', '091 523 7414' ),
  ('Echo Company', '100 Amber Street, Hatfield, Pretoria', '091 523 9685' )

-- Вставка должностей
INSERT INTO Title (Title, TitleDescr) 
VALUES
  ('Mr', 'Мистер'),
  ('Mrs', 'Миссис'),
  ('Miss', 'Мисс')

-- Вставка пола
INSERT INTO Gender (Gender, GenderDescr) 
VALUES 
  ('Мужской' , 'Мужской'),
  ('Женский' , 'Женский')

-- Вставка типов отпусков
INSERT INTO LeaveTypes (LeaveType, LeaveDescr) 
VALUES
  ('Годовой', 'Годовой отпуск'),
  ('Учебный', 'Учебный отпуск'),
  ('Больничный', 'Больничный отпуск'),
  ('Неоплачиваемый', 'Неоплачиваемый отпуск')

-- Вставка адресов
INSERT INTO Addresses (Address) 
VALUES
  ('123 North Street, Garsfontein, Pretoria') ,
  ('456 South Street, Brooklyn, Pretoria'),
  ('987 West Street, Lynnwood, Pretoria'),
  ('258 East Street, The Meadows, Pretoria'),
  ('100 Amber Street, Hatfield, Pretoria')

-- Вставка сотрудников
INSERT INTO Employees (EmployeeName, ContactNo, Email, CompID, AddressID, TitleID, GenderID) 
VALUES
  ('Джо Блогс', '012 365 4789', 'joe@example.com', 1 ,1, 1, 1) ,
  ('Джейн Доу', '012 365 4789', 'jane@example.com', 2 ,1, 2, 1) ,
  ('Джон Смит', '012 365 4789', 'john@example.com', 1 ,1, 1, 1) ,
  ('Эдди Джонс', '012 365 4789', 'eddy@example.com', 4 ,1, 1, 1) ,
  ('Мэри Поппинс', '012 365 4789', 'mary@example.com', 5 ,1, 3, 1)

Запрос данных

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

SELECT  c.CompanyName      
      , e.EmployeeName
      , a.Address
      , lt.LeaveType
      , t.Title
      , g.Gender
      , l.StartDate, l.EndDate
      , DATEDIFF(day, l.StartDate, l.EndDate)+1 'Leave Days'
FROM dbo.Companies c 
JOIN dbo.Employees e on e.CompID = c.id
JOIN dbo.Addresses a on a.id = e.AddressID
JOIN dbo.Title t on t.id = e.TitleID
JOIN dbo.Gender g on g.id = e.GenderID
JOIN dbo.Leave l on l.EmpID = e.id
JOIN dbo.LeaveTypes lt on lt.id = l.LeaveTypeID

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

Очистка

Наконец, когда вы закончите работу с базой данных, вы можете очистить ее, удалив HRDatabase. Обратите внимание, что это не следует делать в рабочей среде. Вот SQL-код для удаления базы данных:

USE master;
GO

-- Удаление базы данных, если она существует
DROP DATABASE IF EXISTS HRDatabase;
GO

Следуя этим шагам, вы можете создавать таблицы, устанавливать отношения, вставлять данные и запрашивать данные в вашей базе данных SQL Server. Это поможет вам создавать надежные и эффективные приложения баз данных.