03__ create Ops db schema and tables .sql

USE [OpsDb]
GO

CREATE TABLE dbo.Contract
(
  Cnct_ID INT IDENTITY(1,1) NOT NULL,
  ContractName VARCHAR(50) NOT NULL,
  PRIMARY KEY (Cnct_ID)
);
GO

CREATE TABLE dbo.AOR_Type
(
  AT_ID INT IDENTITY(1,1) NOT NULL,
  AOR_Name VARCHAR(50) NOT NULL,
  PRIMARY KEY (AT_ID)
);
GO

CREATE TABLE dbo.Employees
(
  E_ID INT IDENTITY(1,1) NOT NULL,
  EmployeeName VARCHAR(250) NOT NULL,
  PRIMARY KEY (E_ID)
);
GO

CREATE TABLE dbo.OpsAOR
(
  OA_ID INT IDENTITY(1,1) NOT NULL,
  StartDt DATE NOT NULL,
  EndDt DATE NULL,
  Cnct_ID INT NOT NULL,
  AT_ID INT NOT NULL,
  E_ID INT NOT NULL,
  PRIMARY KEY (OA_ID),
  FOREIGN KEY (Cnct_ID) REFERENCES dbo.Contract(Cnct_ID),
  FOREIGN KEY (AT_ID) REFERENCES dbo.AOR_Type(AT_ID),
  FOREIGN KEY (E_ID) REFERENCES dbo.Employees(E_ID)
);
GO