create a sample db's

[irvnet]

create sample databases to identify proposed structure used for training

• capture data in sqlite
• capture relevant table structures and sample data as sql
• after validating data structure can identify how much data needs to be generated and what format/location to store it

Target

1. open-roles database

   • HR database that identifies headcount for new products including roles with associated skills and experience.
   • Data includes roles, skills, skill levels and recommended experience

2. transitioning-employees database

   • employees (some of which will transition to new roles).
   • associated skills, skill levels and experience

[irvnet]

@jamar0813 lmk if you'd prefer i just check in sql scripts or just create a sqllitedb and attach to the ticket

[irvnet]

for simplicity will likely consolidate to a single db

[irvnet]

sql for single db

-- Creating Roles Table CREATE TABLE Roles ( RoleID INTEGER PRIMARY KEY AUTOINCREMENT, Title TEXT NOT NULL, Department TEXT NOT NULL, ExperienceRequired TEXT CHECK (ExperienceRequired IN ('Junior', 'Mid-level', 'Senior')), IsOpen INTEGER NOT NULL DEFAULT 0 -- 0 for closed roles, 1 for open roles );

-- Creating Skills Table CREATE TABLE Skills ( SkillID INTEGER PRIMARY KEY AUTOINCREMENT, SkillName TEXT NOT NULL, Category TEXT );

-- Creating Employees Table CREATE TABLE Employees ( EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Department TEXT NOT NULL, CurrentRoleID INTEGER, ExperienceLevel TEXT CHECK (ExperienceLevel IN ('Junior', 'Mid-level', 'Senior')), IsWillingToLearn INTEGER NOT NULL DEFAULT 0, PerformanceRating TEXT CHECK (PerformanceRating IN ('Low', 'Medium', 'High')) );

-- Creating EmployeeSkills Table CREATE TABLE EmployeeSkills ( EmployeeSkillID INTEGER PRIMARY KEY AUTOINCREMENT, EmployeeID INTEGER, SkillID INTEGER, ProficiencyLevel TEXT CHECK (ProficiencyLevel IN ('Beginner', 'Intermediate', 'Expert')) );

-- Creating RoleSkills Table CREATE TABLE RoleSkills ( RoleSkillID INTEGER PRIMARY KEY AUTOINCREMENT, RoleID INTEGER, SkillID INTEGER );

-- Creating EmployeePreferences Table CREATE TABLE EmployeePreferences ( PreferenceID INTEGER PRIMARY KEY AUTOINCREMENT, EmployeeID INTEGER, RoleID INTEGER );

[irvnet]

sample data

-- Inserting 10 open roles with simplified assumptions INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('Software Developer', 'IT', 'Mid-level', 1); INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('Data Analyst', 'Data Science', 'Senior', 1); INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('System Administrator', 'IT', 'Junior', 1); INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('Customer Support Specialist', 'Customer Service', 'Mid-level', 1); INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('Product Manager', 'Product Development', 'Senior', 1); INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('Frontend Developer', 'IT', 'Junior', 1); INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('Backend Developer', 'IT', 'Senior', 1); INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('UX/UI Designer', 'Design', 'Mid-level', 1); INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('DevOps Engineer', 'IT Infrastructure', 'Mid-level', 1); INSERT INTO Roles (Title, Department, ExperienceRequired, IsOpen) VALUES ('Cloud Solutions Architect', 'IT', 'Senior', 1);

-- Inserting a set of skills associated with the roles INSERT INTO Skills (SkillName, Category) VALUES ('Python Programming', 'Technical'); INSERT INTO Skills (SkillName, Category) VALUES ('Data Analysis with SQL', 'Technical'); INSERT INTO Skills (SkillName, Category) VALUES ('Linux System Administration', 'Technical'); INSERT INTO Skills (SkillName, Category) VALUES ('Customer Relationship Management', 'Administrative'); INSERT INTO Skills (SkillName, Category) VALUES ('Agile Project Management', 'Administrative');

-- Inserting sample data for 20 employees INSERT INTO Employees (Name, Department, CurrentRoleID, ExperienceLevel, IsWillingToLearn, PerformanceRating) VALUES ('Alice Johnson', 'IT', NULL, 'Senior', 1, 'High'); INSERT INTO Employees (Name, Department, CurrentRoleID, ExperienceLevel, IsWillingToLearn, PerformanceRating) VALUES ('Bob Smith', 'Data Science', NULL, 'Mid-level', 1, 'Medium');

-- Associating employees with skills INSERT INTO EmployeeSkills (EmployeeID, SkillID, ProficiencyLevel) VALUES (1, 1, 'Expert'); -- Assuming EmployeeID 1 has an Expert level in SkillID 1 INSERT INTO EmployeeSkills (EmployeeID, SkillID, ProficiencyLevel) VALUES (2, 2, 'Intermediate');

-- Indicating roles of interest for transitioning employees INSERT INTO EmployeePreferences (EmployeeID, RoleID) VALUES (1, 2); -- Assuming EmployeeID 1 is interested in transitioning to RoleID 2