IF DB_ID('InventoryDB') IS NOT NULL
    DROP DATABASE InventoryDB
GO
CREATE DATABASE InventoryDB;
GO
USE InventoryDB;
GO

CREATE TABLE TOOLS(
    TOOL_ID INT NOT NULL PRIMARY KEY,
    INVENTORY_NUMBER NVARCHAR(20) NOT NULL,
    YEAR NVARCHAR(4),
    NAME NVARCHAR(50),
    VALUE INT NOT NULL
);

CREATE TABLE JOBS(
    JOB_ID INT NOT NULL PRIMARY KEY,
    NAME NVARCHAR(20)
);

CREATE TABLE ROOMS(
    ROOM_ID INT NOT NULL PRIMARY KEY,
    ROOM_NUMBER NVARCHAR(20),
    SLOTS INT
);

CREATE TABLE STATUSES(
    STATUS_ID INT NOT NULL PRIMARY KEY,
    STATUS NVARCHAR(10)
);

CREATE TABLE EMPLOYEES(
    EMPLOYEE_ID INT NOT NULL PRIMARY KEY,
    NAME NVARCHAR(30) NOT NULL,
    ID NVARCHAR(10) NOT NULL,
    JOB_ID NVARCHAR(30) NOT NULL,
    ROOM_ID INT NOT NULL,
    CONSTRAINT JOB_ID FOREIGN KEY REFERENCES JOBS.JOB_ID,
    CONSTRAINT ROOM_ID FOREIGN KEY REFERENCES ROOMS.ROOM_ID
);

CREATE TABLE INVENTORY(
    INVENTORY_ID INT NOT NULL PRIMARY KEY,
    DATE DATETIME NOT NULL PRIMARY KEY,
    STATUS_ID INT NOT NULL,
    ROOM_ID INT NOT NULL,
    OWNER_ID INT NOT NULL,
    CONSTRAINT STATUS_ID FOREIGN KEY REFERENCES STATUSES.STATUS_ID,
    CONSTRAINT ROOM_ID FOREIGN KEY REFERENCES ROOMS.ROOM_ID,
    CONSTRAINT OWNER_ID FOREIGN KEY REFERENCES EMPLOYEES.EMPLOYEE_ID
);