Hotel DB Working

CREATE TABLE Employees (
    Id INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(30) NOT NULL,
    LastName NVARCHAR(30) NOT NULL,
    Title NVARCHAR(50),
    Notes NVARCHAR(500)
)

CREATE TABLE Customers (
    AccountNumber INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(30) NOT NULL,
    LastName NVARCHAR(30) NOT NULL,
    PhoneNumber NVARCHAR(30),
    EmergencyName NVARCHAR(30),
    EmergencyNumber NVARCHAR(30),
    Notes NVARCHAR(500)
)

CREATE TABLE RoomStatus (
    RoomStatus NVARCHAR(50) PRIMARY KEY NOT NULL,
    Notes NVARCHAR(500)
)

CREATE TABLE RoomTypes (
    RoomType NVARCHAR(50) PRIMARY KEY NOT NULL,
    Notes NVARCHAR(500)
)

CREATE TABLE BedTypes (
    BedType NVARCHAR(50) PRIMARY KEY NOT NULL,
    Notes NVARCHAR(500)
)

CREATE TABLE Rooms (
    RoomNumber INT PRIMARY KEY NOT NULL,
    RoomType NVARCHAR(50) FOREIGN KEY REFERENCES RoomTypes(RoomType) NOT NULL,
    BedType NVARCHAR(50) FOREIGN KEY REFERENCES BedTypes(BedType) NOT NULL,
    Rate DECIMAL(6,2) NOT NULL,
    RoomStatus BIT NOT NULL,
    Notes NVARCHAR(1000)
)

CREATE TABLE Payments (
    Id INT PRIMARY KEY IDENTITY,
    EmployeeId INT FOREIGN KEY REFERENCES Employees(Id) NOT NULL,
    PaymentDate DATETIME NOT NULL,
    AccountNumber INT FOREIGN KEY REFERENCES Customers(AccountNumber) NOT NULL,
    FirstDateOccupied DATE NOT NULL,
    LastDateOccupied DATE NOT NULL,
    TotalDays AS DATEDIFF(DAY, FirstDateOccupied, LastDateOccupied),
    AmountCharged DECIMAL(7, 2) NOT NULL,
    TaxRate DECIMAL(6,2) NOT NULL,
    TaxAmount AS AmountCharged * TaxRate,
    PaymentTotal AS AmountCharged + AmountCharged * TaxRate,
    Notes NVARCHAR(1500)
)

CREATE TABLE Occupancies (
    Id INT PRIMARY KEY IDENTITY,
    EmployeeId INT FOREIGN KEY REFERENCES Employees(Id) NOT NULL,
    DateOccupied DATE NOT NULL,
    AccountNumber INT FOREIGN KEY REFERENCES Customers(AccountNumber) NOT NULL,
    RoomNumber INT FOREIGN KEY REFERENCES Rooms(RoomNumber) NOT NULL,
    RateApplied DECIMAL(7, 2) NOT NULL,
    PhoneCharge DECIMAL(8, 2) NOT NULL,
    Notes NVARCHAR(1000)
)

INSERT INTO Employees(FirstName, LastNAme) VALUES
('Galin', 'Zhelev'),
('Stoyan', 'Ivanov'),
('Petar', 'Ikonomov')

INSERT INTO Customers(FirstName, LastName, PhoneNumber) VALUES
('Monio', 'Ushev', '+359888666555'),
('Gancho', 'Stoykov', '+359866444222'),
('Genadi', 'Dimchov', '+35977555333')

INSERT INTO RoomStatus(RoomStatus) VALUES
('occupied'),
('non occupied'),
('repairs')

INSERT INTO RoomTypes(RoomType) VALUES
('single'),
('double'),
('appartment')

INSERT INTO BedTypes(BedType) VALUES
('single'),
('double'),
('couch')

INSERT INTO Rooms(RoomNumber, RoomType, BedType, Rate, RoomStatus) VALUES
(201, 'single', 'single', 40.0, 1),
(205, 'double', 'double', 70.0, 0),
(208, 'appartment', 'double', 110.0, 1)

INSERT INTO Payments(EmployeeId, PaymentDate, AccountNumber, FirstDateOccupied, LastDateOccupied, AmountCharged, TaxRate) VALUES
(1, '2011-11-25', 2, '2017-11-30', '2017-12-04', 250.0, 0.2),
(3, '2014-06-03', 3, '2014-06-06', '2014-06-09', 340.0, 0.2),
(3, '2016-02-25', 2, '2016-02-27', '2016-03-04', 500.0, 0.2)

INSERT INTO Occupancies(EmployeeId, DateOccupied, AccountNumber, RoomNumber, RateApplied, PhoneCharge) VALUES
(2, '2011-02-04', 3, 205, 70.0, 12.54),
(2, '2015-04-09', 1, 201, 40.0, 11.22),
(3, '2012-06-08', 2, 208, 110.0, 10.05)