Databases MSSQL Server Exam - 21 Jun 2020_DDL

CREATE TABLE Cities(
    Id INT PRIMARY KEY IDENTITY,
    [Name] NVARCHAR(20) NOT NULL,
    CountryCode NVARCHAR(2) NOT NULL
)

CREATE TABLE Hotels(
    Id INT PRIMARY KEY IDENTITY,
    [Name] NVARCHAR(30) NOT NULL,
    CityId INT FOREIGN KEY REFERENCES Cities(Id) NOT NULL,
    EmployeeCount INT NOT NULL,
    BaseRate DECIMAL(5,2)
)

CREATE TABLE Rooms(
    Id INT PRIMARY KEY IDENTITY,
    Price DECIMAL(10,2) NOT NULL,
    Type NVARCHAR(20) NOT NULL,
    Beds INT  NOT NULL,
    HotelId INT FOREIGN KEY REFERENCES Hotels(Id) NOT NULL
)

CREATE TABLE Trips(
    Id INT PRIMARY KEY IDENTITY,
    RoomId INT FOREIGN KEY REFERENCES Rooms(Id) NOT NULL,
    BookDate DATE NOT NULL,
    ArrivalDate DATE NOT NULL,
    ReturnDate DATE NOT NULL,
    CancelDate DATE,
    CONSTRAINT Checked_BookDate_ArrivalDate CHECK(DATEDIFF(DAY, BookDate, ArrivalDate) > 0),
    CONSTRAINT Checked_ArrivalDate_ReturnDate CHECK(DATEDIFF(DAY, ArrivalDate, ReturnDate) > 0)
)

CREATE TABLE Accounts(
    Id INT PRIMARY KEY NOT NULL IDENTITY,
    FirstName NVARCHAR(50) NOT NULL,
    MiddleName NVARCHAR(20),
    LastName NVARCHAR(50) NOT NULL,
    CityId INT FOREIGN KEY REFERENCES Cities(Id) NOT NULL,
    BirthDate DATE NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL
)

CREATE TABLE AccountsTrips(
    AccountId INT FOREIGN KEY REFERENCES Accounts(Id) NOT NULL,
    TripId INT FOREIGN KEY REFERENCES Trips(Id) NOT NULL,
    Luggage INT NOT NULL
        CHECK(Luggage >= 0),
    CONSTRAINT PK_Accounts_Trips PRIMARY KEY (AccountId, TripId)
)