DROP TABLE IF EXISTS ProjectMembers;
DROP TABLE IF EXISTS Projects;
DROP TABLE IF EXISTS Employees;

GO

CREATE TABLE Employees (
    EmployeeId      int IDENTITY PRIMARY KEY,
    FirstName       nvarchar(255),
    LastName        nvarchar(255),
    DateHired       date,
    SupervisorId    int REFERENCES Employees(EmployeeId)
);

INSERT INTO Employees (FirstName, LastName, DateHired, SupervisorId) VALUES
    (N'John', N'Doe', '2015-01-10', NULL);
INSERT INTO Employees (FirstName, LastName, DateHired, SupervisorId) VALUES
    (N'Jane', N'Doe', '2015-02-20', 1);

SET IDENTITY_INSERT Employees ON;
INSERT INTO Employees (EmployeeId, FirstName, LastName, DateHired, SupervisorId) VALUES
    (1000, N'Tom', N'Smith', '2016-06-19', 2);
SET IDENTITY_INSERT Employees OFF;

INSERT INTO Employees (FirstName, LastName, DateHired, SupervisorId) VALUES
    (N'Bob', N'Lee', '2016-06-20', 2);

GO

CREATE TABLE Projects (
    ProjectId   int IDENTITY PRIMARY KEY,
    Name        nvarchar(255) NOT NULL,
    LeaderId    int REFERENCES Employees(EmployeeId)
);

INSERT INTO Projects (Name, LeaderId) VALUES (N'Firestone', 1);
INSERT INTO Projects (Name, LeaderId) VALUES (N'Blue', 2);

GO

CREATE TABLE ProjectMembers (
    ProjectId   int NOT NULL REFERENCES Projects(ProjectId),
    EmployeeId  int NOT NULL REFERENCES Employees(EmployeeId)
);

INSERT INTO ProjectMembers VALUES (1, 1);
INSERT INTO ProjectMembers VALUES (2, 1);
INSERT INTO PRojectMembers VALUES (2, 2);

GO