
CREATE TABLE Staff (
    StaffID CHAR(10) PRIMARY KEY AUTOINCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    EmailAddress VARCHAR(100),
    Role VARCHAR(15),
    PasswordHash CHAR(30),
        FOREIGN KEY (Role) REFERENCES Role(Role)
);

CREATE TABLE Role (
    Role VARCHAR(15) PRIMARY KEY,
    LevelofAccess INTEGER
);

CREATE TABLE RoleRequirements (
    Role VARCHAR(15),
    ListName VARCHAR(50),
    Status VARCHAR(20),
    constraint pk_RoleRequirements primary key (Role,ListName),
        FOREIGN KEY (Role) REFERENCES Role(Role),
        FOREIGN KEY (ListName) REFERENCES WarningList(ListName)   
);

CREATE TABLE WarningList (
    ListName VARCHAR(50) PRIMARY KEY,
    Type VARCHAR(25),
    Duration INTEGER,
    AmberWarning INTEGER,
    RedWarning INTEGER,
    AdditionalNotes VARCHAR(255)
);

CREATE TABLE StaffRecords (
    StaffID CHAR(10),
    ListName VARCHAR(50),
    StartDate DATE,
    ExpiryDate DATE,
    FileAddress FILE,
    constraint pk_StaffRecords primary key (StaffID,ListName),
        FOREIGN KEY (StaffID) REFERENCES Staff(StaffID),
        FOREIGN KEY (ListName) REFERENCES WarningList(ListName)
);

DROP TABLE StaffRecords;

CREATE TABLE ExternalFractionalStaff (
    StaffName VARCHAR(100),
    MonthStartDate DATE,
    NoOfHours INTEGER,
    constraint pk_StaffRecords primary key (StaffName,MonthStartDate)   
);

Insert Into Staff Values('2','John','Thmas','23456@qq.com','2','123456');

Insert Into Role Values('2','1');

Insert Into RoleRequirements Values('2','ListName1','Status1');

Insert Into WarningList Values('ListName1','Type1','1','1','2','AdditionanNotes');

Insert Into StaffRecords Values('2','ListName1','','','');