Databases MSSQL Server Exam - 29 Aug 2018 Problem 01

CREATE TABLE Employees
(
Id INT PRIMARY KEY IDENTITY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Phone CHAR(12) NOT NULL,
Salary DECIMAL(18,2) NOT NULL
)

CREATE TABLE Shifts
(
Id INT IDENTITY,
EmployeeId INT NOT NULL FOREIGN KEY REFERENCES Employees(Id),
CheckIn DATETIME NOT NULL,
CheckOut DATETIME NOT NULL,
CHECK (CheckIn < CheckOut),
PRIMARY KEY(Id, EmployeeId)
)

CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
[DateTime] DATETIME NOT NULL,
EmployeeId INT NOT NULL FOREIGN KEY REFERENCES Employees(Id)
)

CREATE TABLE Categories
(
Id INT PRIMARY KEY IDENTITY,
[Name] VARCHAR(30) NOT NULL
)

CREATE TABLE Items
(
Id INT PRIMARY KEY IDENTITY,
[Name] VARCHAR(30) NOT NULL,
Price DECIMAL(15,2) NOT NULL,
CategoryId INT NOT NULL FOREIGN KEY REFERENCES Categories(Id)
)

CREATE TABLE OrderItems
(
OrderId INT NOT NULL FOREIGN KEY REFERENCES Orders(Id),
ItemId INT NOT NULL FOREIGN KEY REFERENCES Items(Id),
Quantity INT NOT NULL,
CHECK (Quantity >=1)
)