22.10.2017

CREATE TABLE Users
(
Id INT NOT NULL IDENTITY PRIMARY KEY,
UserName NVARCHAR(30) NOT NULL UNIQUE,
Password NVARCHAR(50) NOT NULL,
Name NVARCHAR(50),
Gender CHAR(1) CHECK(Gender='M' OR Gender='F'),
BirthDate DateTime,
Age INT,
Email NVARCHAR(50) NOT NULL
);
CREATE TABLE Departments
(
Id INT NOT NULL IDENTITY PRIMARY KEY,
Name NVARCHAR(50) NOT NULL
);
CREATE TABLE Employees
(
Id INT NOT NULL IDENTITY PRIMARY KEY,
FirstName NVARCHAR(25),
LastName NVARCHAR(25),
Gender CHAR(1) CHECK(Gender='M' OR Gender='F'),
BirthDate DateTime,
Age INT,
DepartmentId INT NOT NULL
CONSTRAINT FK_departEmp FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
);
CREATE TABLE Categories
(
Id INT NOT NULL IDENTITY PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
DepartmentId INT NOT NULL
CONSTRAINT FK_department FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
);
CREATE TABLE STATUS
(
Id INT NOT NULL IDENTITY PRIMARY KEY,
Label VARCHAR(30) NOT NULL
);
CREATE TABLE Reports
(
Id INT NOT NULL IDENTITY PRIMARY KEY,
CategoryId INT NOT NULL
CONSTRAINT FK_category FOREIGN KEY (CategoryId) REFERENCES Categories(Id),
StatusId INT NOT NULL
CONSTRAINT FK_status FOREIGN KEY (statusId) REFERENCES STATUS(Id),
OpenDate DateTime NOT NULL,
CloseDate DateTime,
Description VARCHAR(200),
UserId INT NOT NULL
CONSTRAINT FK_user FOREIGN KEY (UserId) REFERENCES Users(Id),
EmployeeId INT NOT NULL
CONSTRAINT FK_employee FOREIGN KEY (EmployeeId) REFERENCES Employees(Id),
);