CREATE TABLE Employees ( Employee VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE Roles ( Role VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE AppSystemCategories ( Category VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE AppSystems ( AppSystem VARCHAR(30) NOT NULL PRIMARY KEY, Category VARCHAR(30) NOT NULL REFERENCES AppSystemCategories(Category) ); CREATE TABLE EmployeeAppSystems ( Employee VARCHAR(30) NOT NULL REFERENCES Employees(Employee), AppSystem VARCHAR(30) NOT NULL REFERENCES AppSystems(AppSystem), PRIMARY KEY (Employee, AppSystem) ); CREATE TABLE RoleAppSystems ( Role VARCHAR(30) NOT NULL REFERENCES Roles(Role), AppSystem VARCHAR(30) NOT NULL REFERENCES AppSystems(AppSystem), PRIMARY KEY (Role, AppSystem) ); INSERT INTO Employees(Employee) VALUES ('bibi'),('tee'),('mimi'),('lee'), ('min'); INSERT INTO Roles(Role) VALUES ('Data Analyst'), ('Engineer'), ('Business'); INSERT INTO AppSystemCategories(Category) VALUES ('Tech'), ('Biz'); INSERT INTO AppSystems(AppSystem, Category) VALUES ('Redshift','Tech'), ('Redash','Tech'), ('Jira','Tech'), ('Tableau','Tech'), ('Figma','Tech'), ('Office','Tech'), ('MoneyForward','Biz'), ('Salesforce','Biz'), ('Notion','Biz'); INSERT INTO RoleAppSystems(Role, AppSystem) VALUES ('Data Analyst','Redshift'), ('Data Analyst','Redash'), ('Data Analyst','Tableau'), ('Data Analyst','MoneyForward'), ('Data Analyst','Notion'), ('Engineer','Figma'), ('Engineer','Jira'), ('Engineer','MoneyForward'), ('Engineer','Notion'), ('Business','Notion'), ('Business','MoneyForward'), ('Business','Office'); -- MoneyForward and Notion are used by all roles INSERT INTO EmployeeAppSystems(Employee, AppSystem) VALUES ('bibi','Redshift'), ('bibi','Redash'), ('bibi','MoneyForward'), ('bibi','Notion'), -- Partial match for Data Analyst, match for Biz systems ('tee','Redshift'), ('tee','Redash'), ('tee','Tableau'), ('tee','MoneyForward'), ('tee','Notion'), -- Perfect match for Data Analyst ('mimi','Figma'), ('mimi','Jira'), ('mimi','MoneyForward'), ('mimi','Notion'), ('mimi','Tableau'), -- Over qualified for Engineer (uses extra systems) ('lee','Notion'), ('lee','Salesforce'), -- No tech skills ('min','Redshift'), ('min','Redash'), ('min','Jira'), ('min','Tableau'), ('min','Figma'), ('min','Office'), ('min','MoneyForward'), ('min','Salesforce'), ('min','Notion'); -- min Has it all -- all candidates use notion

SELECT * FROM AppSystems; SELECT * FROM AppSystemCategories; SELECT * FROM Employees; SELECT * FROM EmployeeAppSystems; SELECT * FROM Roles; SELECT * FROM RoleAppSystems;