db_All_Tables.txt

CREATE DATABASE ProjectDB;

USE ProjectDB;

CREATE TABLE Client
(
  VATNUMBER VARCHAR(25) NOT NULL,
  COMPANYNAME VARCHAR(50),
  PHONENUMMER INT NOT NULL,
  EMAIL VARCHAR(50),
  PRIMARY KEY (VATNUMBER)
);

CREATE TABLE Employees
(
  CPR INT NOT NULL,
  FIRSTNAME VARCHAR(25) NOT NULL,
  LASTNAME VARCHAR(50) NOT NULL,
  PHONENUMBER INT NOT NULL,
  EMAIL VARCHAR(50),
  PRIMARY KEY(CPR)
);

CREATE TABLE Allocation_Project
(
  EVENTID INT NOT NULL,
  CPR INT NOT NULL,
  VATNUMBER VARCHAR(25) NOT NULL,
  DATEFROM DATE NOT NULL,
  DATETO DATE NOT NULL,
  NOTES VARCHAR(400),
  PRIMARY KEY (EVENTID),
  FOREIGN KEY (CPR) REFERENCES Employees(CPR),
  FOREIGN KEY (VATNUMBER) REFERENCES Client(VATNUMBER)
);

CREATE TABLE Allocation_OtherActivity
(
  EVENTID INT NOT NULL,
  OTHERACTIVITY ENUM ('Ferie', 'Skoleopholdet', 'Sygdom', 'Øvrigt', ''),
  DATEFROM DATE NOT NULL,
  DATETO DATE NOT NULL,
  PRIMARY KEY (EVENTID),
  FOREIGN KEY (EVENTID) REFERENCES Allocation_Project(EVENTID)
);