| CREATE DATABASE SportStore
|
| GO
|
|
|
| USE SportStore
|
| GO
|
|
|
|
|
| -- Товары
|
| CREATE TABLE Products (
|
| ID int IDENTITY
|
| CONSTRAINT PK_Products PRIMARY KEY,
|
| ProductType nvarchar(20) NOT NULL,
|
| ProductName nvarchar(30) NOT NULL,
|
| ManufacturerName nvarchar(30) NOT NULL,
|
| Price money NOT NULL
|
| CONSTRAINT CK_Products_Price CHECK(Price >= 0)
|
| CONSTRAINT DF_Products_Price DEFAULT 1000,
|
| Amount int NOT NULL
|
| CONSTRAINT CK_Products_Amount CHECK(Amount >= 0)
|
| CONSTRAINT DF_Products_Amount DEFAULT 1
|
| );
|
|
|
|
|
| -- Последние еденицы товаров
|
| CREATE TABLE RecentProducts (
|
| ID int IDENTITY
|
| CONSTRAINT PK_RecentProducts PRIMARY KEY,
|
| ProductID int NOT NULL
|
| CONSTRAINT FK_RecentProducts_Products FOREIGN KEY REFERENCES Products(ID)
|
| CONSTRAINT UK_RecentProducts_ProductID UNIQUE
|
| );
|
|
|
|
|
| -- Архив
|
| CREATE TABLE Archive (
|
| ID int IDENTITY
|
| CONSTRAINT PK_Archive PRIMARY KEY,
|
| ProductID int NOT NULL
|
| CONSTRAINT FK_Archive_Products FOREIGN KEY REFERENCES Products(ID)
|
| CONSTRAINT UK_Archive_ProductID UNIQUE,
|
| ArchivingDate datetime NOT NULL
|
| CONSTRAINT CK_Archive_ArchivingDate CHECK(ArchivingDate <= GETDATE())
|
| CONSTRAINT DF_Archive_ArchivingDate DEFAULT GETDATE()
|
| );
|
|
|
|
|
| -- Сотрудники
|
| CREATE TABLE Employees (
|
| ID int IDENTITY
|
| CONSTRAINT PK_Employees PRIMARY KEY,
|
| Lastname nvarchar(20) NOT NULL,
|
| Firstname nvarchar(20) NOT NULL,
|
| Patronymic nvarchar(20) NULL
|
| CONSTRAINT DF_Employees_Patronymic DEFAULT NULL,
|
| Position nvarchar(30) NOT NULL,
|
| EmploymentDate date NOT NULL
|
| CONSTRAINT CK_Employees_EmploymentDate CHECK(EmploymentDate <= GETDATE())
|
| CONSTRAINT DF_Employees_EmploymentDate DEFAULT GETDATE(),
|
| Salary money NOT NULL
|
| CONSTRAINT CK_Employees_Salary CHECK(Salary >= 50000)
|
| CONSTRAINT DF_Employees_Salary DEFAULT 50000
|
| );
|
|
|
|
|
| -- Клиенты
|
| CREATE TABLE Clients (
|
| ID int IDENTITY
|
| CONSTRAINT PK_Clients PRIMARY KEY,
|
| Lastname nvarchar(20) NOT NULL,
|
| Firstname nvarchar(20) NOT NULL,
|
| Patronymic nvarchar(20) NULL
|
| CONSTRAINT DF_Clients_Patronymic DEFAULT NULL,
|
| Email nvarchar(30) NOT NULL,
|
| Phone nvarchar(12) NOT NULL,
|
| Subscription bit NOT NULL
|
| CONSTRAINT DF_Clients_Subscription DEFAULT 0
|
|
|
| -- CONSTRAINT UK_Clients_Email UNIQUE(Email) -- Ограничение сформировано с помощью триггера
|
| -- CONSTRAINT UK_Clients_Phone UNIQUE(Phone) -- Ограничение сформировано с помощью триггера
|
| );
|
|
|
|
|
| -- Продажи
|
| CREATE TABLE Sales (
|
| ID int IDENTITY
|
| CONSTRAINT PK_Sales PRIMARY KEY,
|
| SaleDate date NOT NULL
|
| CONSTRAINT CK_Sales_SaleDate CHECK(SaleDate <= GETDATE())
|
| CONSTRAINT DF_Sales_SaleDate DEFAULT GETDATE(),
|
| EmployeeID int NOT NULL
|
| CONSTRAINT FK_Sales_Employees FOREIGN KEY REFERENCES Employees(ID),
|
| ClientID int NOT NULL
|
| CONSTRAINT FK_Sales_Clients FOREIGN KEY REFERENCES Clients(ID),
|
| ProductID int NOT NULL
|
| CONSTRAINT FK_Sales_Products FOREIGN KEY REFERENCES Products(ID),
|
| Quantity int NOT NULL
|
| CONSTRAINT CK_Sales_Quantity CHECK(Quantity > 0)
|
| CONSTRAINT DF_Sales_Quantity DEFAULT 1
|
| );
|
|
|
|
|
| -- История
|
| CREATE TABLE History (
|
| ID int IDENTITY
|
| CONSTRAINT PK_History PRIMARY KEY,
|
| SaleDate date NOT NULL
|
| CONSTRAINT CK_History_SaleDate CHECK(SaleDate <= GETDATE())
|
| CONSTRAINT DF_History_SaleDate DEFAULT GETDATE(),
|
| EmployeeID int NOT NULL
|
| CONSTRAINT FK_History_Employees FOREIGN KEY REFERENCES Employees(ID),
|
| ClientID int NOT NULL
|
| CONSTRAINT FK_History_Clients FOREIGN KEY REFERENCES Clients(ID),
|
| ProductID int NOT NULL
|
| CONSTRAINT FK_History_Products FOREIGN KEY REFERENCES Products(ID),
|
| Price money NOT NULL
|
| CONSTRAINT CK_History_Price CHECK(Price >= 1000)
|
| CONSTRAINT DF_History_Price DEFAULT 1000,
|
| Quantity int NOT NULL
|
| CONSTRAINT CK_History_Quantity CHECK(Quantity > 0)
|
| CONSTRAINT DF_History_Quantity DEFAULT 1,
|
| Discount int NOT NULL
|
| CONSTRAINT CK_History_Discount CHECK(Discount >= 0)
|
| CONSTRAINT DF_History_Discount DEFAULT 0,
|
| Summ money NOT NULL
|
| CONSTRAINT CK_History_Summ CHECK(Summ >= 0)
|
| CONSTRAINT DF_History_Summ DEFAULT 0
|
| );
|