{"Apartment_Buildings": {"columns": [[0, "building_id", "INTEGER", 1, null, 1], [1, "building_short_name", "CHAR(15)", 0, null, 0], [2, "building_full_name", "VARCHAR(80)", 0, null, 0], [3, "building_description", "VARCHAR(255)", 0, null, 0], [4, "building_address", "VARCHAR(255)", 0, null, 0], [5, "building_manager", "VARCHAR(50)", 0, null, 0], [6, "building_phone", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["building_id"], "foreign_keys": [], "unique_constraints": [[[0, 0, "building_id"]]], "check_constraints": []}, "Apartments": {"columns": [[0, "apt_id", "INTEGER", 1, null, 1], [1, "building_id", "INTEGER", 1, null, 0], [2, "apt_type_code", "CHAR(15)", 0, null, 0], [3, "apt_number", "CHAR(10)", 0, null, 0], [4, "bathroom_count", "INTEGER", 0, null, 0], [5, "bedroom_count", "INTEGER", 0, null, 0], [6, "room_count", "CHAR(5)", 0, null, 0]], "primary_keys": ["apt_id"], "foreign_keys": [[0, 0, "Apartment_Buildings", "building_id", "building_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "apt_id"]]], "check_constraints": []}, "Apartment_Facilities": {"columns": [[0, "apt_id", "INTEGER", 1, null, 1], [1, "facility_code", "CHAR(15)", 1, null, 2]], "primary_keys": ["apt_id", "facility_code"], "foreign_keys": [[0, 0, "Apartments", "apt_id", "apt_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "apt_id"], [1, 1, "facility_code"]]], "check_constraints": []}, "Guests": {"columns": [[0, "guest_id", "INTEGER", 1, null, 1], [1, "gender_code", "CHAR(1)", 0, null, 0], [2, "guest_first_name", "VARCHAR(80)", 0, null, 0], [3, "guest_last_name", "VARCHAR(80)", 0, null, 0], [4, "date_of_birth", "DATETIME", 0, null, 0]], "primary_keys": ["guest_id"], "foreign_keys": [], "unique_constraints": [[[0, 0, "guest_id"]]], "check_constraints": []}, "Apartment_Bookings": {"columns": [[0, "apt_booking_id", "INTEGER", 1, null, 1], [1, "apt_id", "INTEGER", 0, null, 0], [2, "guest_id", "INTEGER", 1, null, 0], [3, "booking_status_code", "CHAR(15)", 1, null, 0], [4, "booking_start_date", "DATETIME", 0, null, 0], [5, "booking_end_date", "DATETIME", 0, null, 0]], "primary_keys": ["apt_booking_id"], "foreign_keys": [[0, 0, "Guests", "guest_id", "guest_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Apartments", "apt_id", "apt_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "apt_booking_id"]]], "check_constraints": []}, "View_Unit_Status": {"columns": [[0, "apt_id", "INTEGER", 0, null, 0], [1, "apt_booking_id", "INTEGER", 0, null, 0], [2, "status_date", "DATETIME", 1, null, 1], [3, "available_yn", "BIT", 0, null, 0]], "primary_keys": ["status_date"], "foreign_keys": [[0, 0, "Apartment_Bookings", "apt_booking_id", "apt_booking_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Apartments", "apt_id", "apt_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 2, "status_date"]]], "check_constraints": []}}
CREATE TABLE Apartment_Buildings (
    building_id INTEGER NOT NULL  PRIMARY KEY,
    building_short_name CHAR(15),
    building_full_name VARCHAR(80),
    building_description VARCHAR(255),
    building_address VARCHAR(255),
    building_manager VARCHAR(50),
    building_phone VARCHAR(80),
    UNIQUE (building_id)
);

CREATE TABLE Apartments (
    apt_id INTEGER NOT NULL  PRIMARY KEY,
    building_id INTEGER NOT NULL,
    apt_type_code CHAR(15),
    apt_number CHAR(10),
    bathroom_count INTEGER,
    bedroom_count INTEGER,
    room_count CHAR(5),
    UNIQUE (apt_id),
    FOREIGN KEY (building_id) REFERENCES Apartment_Buildings(building_id)
);

CREATE TABLE Apartment_Facilities (
    apt_id INTEGER NOT NULL  PRIMARY KEY,
    facility_code CHAR(15) NOT NULL  PRIMARY KEY,
    PRIMARY KEY (apt_id, facility_code),
    UNIQUE (apt_id, facility_code),
    FOREIGN KEY (apt_id) REFERENCES Apartments(apt_id)
);

CREATE TABLE Guests (
    guest_id INTEGER NOT NULL  PRIMARY KEY,
    gender_code CHAR(1),
    guest_first_name VARCHAR(80),
    guest_last_name VARCHAR(80),
    date_of_birth DATETIME,
    UNIQUE (guest_id)
);

CREATE TABLE Apartment_Bookings (
    apt_booking_id INTEGER NOT NULL  PRIMARY KEY,
    apt_id INTEGER,
    guest_id INTEGER NOT NULL,
    booking_status_code CHAR(15) NOT NULL,
    booking_start_date DATETIME,
    booking_end_date DATETIME,
    UNIQUE (apt_booking_id),
    FOREIGN KEY (guest_id) REFERENCES Guests(guest_id),
    FOREIGN KEY (apt_id) REFERENCES Apartments(apt_id)
);

CREATE TABLE View_Unit_Status (
    apt_id INTEGER,
    apt_booking_id INTEGER,
    status_date DATETIME NOT NULL  PRIMARY KEY,
    available_yn BIT,
    UNIQUE (status_date),
    FOREIGN KEY (apt_booking_id) REFERENCES Apartment_Bookings(apt_booking_id),
    FOREIGN KEY (apt_id) REFERENCES Apartments(apt_id)
);