TableCreation.sql

use UniversityReservationSystem;

CREATE TABLE Resources
(
  Res_ID INT IDENTITY(1,1) NOT NULL,
  Name VARCHAR(128) NOT NULL,
  Description VARCHAR(256) NOT NULL,
  PRIMARY KEY (Res_ID)
);

CREATE TABLE Priorities
(
  Priority_ID INT IDENTITY(1,1) NOT NULL,
  Level VARCHAR(50) NOT NULL,
  Value INT NOT NULL,
  PRIMARY KEY (Priority_ID)
);

CREATE TABLE States
(
  State_ID INT IDENTITY(1,1) NOT NULL,
  Name VARCHAR(50) NOT NULL UNIQUE,
  PRIMARY KEY (State_ID)
);

CREATE TABLE Users
(
  User_ID VARCHAR(10) NOT NULL,
  Email VARCHAR(128) NOT NULL UNIQUE,
  Phone VARCHAR(50) NOT NULL,
  CurrentPriority INT NOT NULL,
  BasePriority INT NOT NULL,
  PRIMARY KEY (Email),
  FOREIGN KEY (CurrentPriority) REFERENCES Priorities(Priority_ID),
  FOREIGN KEY (BasePriority) REFERENCES Priorities(Priority_ID)
);

CREATE TABLE Reservations
(
  Reserv_ID INT IDENTITY(1,1) NOT NULL,
  Created_At DATETIME NOT NULL,
  Started_At DATETIME NOT NULL,
  isEssential TINYINT NOT NULL CHECK(isEssential IN (0,1)),
  ReservPeriod INT NOT NULL,
  Status INT NOT NULL,
  Res_ID INT NOT NULL,
  Email VARCHAR(128) NOT NULL,
  PRIMARY KEY (Reserv_ID),
  UNIQUE(Res_ID, Started_At),
  FOREIGN KEY (Status) REFERENCES States(State_ID),
  FOREIGN KEY (Res_ID) REFERENCES Resources(Res_ID),
  FOREIGN KEY (Email) REFERENCES Users(Email)
);

CREATE TABLE Requisitions
(
  --Req_ID INT IDENTITY(1,1) NOT NULL,
  Req_ID VARCHAR(8) NOT NULL,
  Generated_At DATETIME NOT NULL,
  Reserv_ID INT NOT NULL,
  Status INT NOT NULL,
  PRIMARY KEY (Req_ID),
  FOREIGN KEY (Reserv_ID) REFERENCES Reservations(Reserv_ID),
  FOREIGN KEY (Status) REFERENCES States(State_ID)
);

CREATE TABLE Penalties
(
  Penalty_ID INT IDENTITY(1,1) NOT NULL,
  Reason VARCHAR(128) NOT NULL,
  Timestamp DATETIME NOT NULL,
  Email VARCHAR(128) NOT NULL,
  PRIMARY KEY (Penalty_ID),
  UNIQUE (Email, Reason, Timestamp),
  FOREIGN KEY (Email) REFERENCES Users(Email)
);