-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/

-- Generation Time: Nov 07, 2024 at 09:02 AM
-- Server version: 8.0.39
-- PHP Version: 8.1.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------

--
-- Table structure for table `colors`
--

CREATE TABLE `colors` (
  `id` int NOT NULL,
  `degrees` int DEFAULT NULL,
  `red` int DEFAULT NULL,
  `green` int DEFAULT NULL,
  `blue` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `colors`
--

INSERT INTO `colors` (`id`, `degrees`, `red`, `green`, `blue`) VALUES
(1, 0, 255, 0, 0),
(2, 12, 255, 51, 0),
(3, 24, 255, 102, 0),
(4, 36, 255, 153, 0),
(5, 48, 255, 204, 0),
(6, 60, 255, 255, 0),
(7, 72, 204, 255, 0),
(8, 84, 153, 255, 0),
(9, 96, 102, 255, 0),
(10, 108, 51, 255, 0),
(11, 120, 0, 255, 0),
(12, 132, 0, 255, 51),
(13, 144, 0, 255, 102),
(14, 156, 0, 255, 153),
(15, 168, 0, 255, 204),
(16, 180, 0, 255, 255),
(17, 192, 0, 204, 255),
(18, 204, 0, 153, 255),
(19, 216, 0, 102, 255),
(20, 228, 0, 51, 255),
(21, 240, 0, 0, 255),
(22, 252, 51, 0, 255),
(23, 264, 102, 0, 255),
(24, 276, 153, 0, 255),
(25, 288, 204, 0, 255),
(26, 300, 255, 0, 255),
(27, 312, 255, 0, 204),
(28, 324, 255, 0, 153),
(29, 336, 255, 0, 102),
(30, 348, 255, 0, 51);

-- --------------------------------------------------------

--
-- Table structure for table `companies`
--

CREATE TABLE `companies` (
  `company_id` int NOT NULL,
  `company_name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `companies`
--

INSERT INTO `companies` (`company_id`, `company_name`) VALUES
(2, 'Code Solutions LLC'),
(3, 'Creative Tech Co.'),
(4, 'Global Tech Services'),
(5, 'Innovatech Solutions'),
(1, 'Tech Innovations Inc.');

-- --------------------------------------------------------

--
-- Table structure for table `employment`
--

CREATE TABLE `employment` (
  `employment_id` int NOT NULL,
  `programmer_id` int DEFAULT NULL,
  `company_id` int DEFAULT NULL,
  `position_id` int DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `level_id` int DEFAULT NULL,
  `is_current` tinyint(1) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employment`
--

INSERT INTO `employment` (`employment_id`, `programmer_id`, `company_id`, `position_id`, `start_date`, `end_date`, `level_id`, `is_current`) VALUES
(1, 1, 1, 1, '2020-01-15', '2022-05-20', 3, 0),
(2, 2, 2, 2, '2021-06-01', NULL, 1, 1),
(3, 3, 3, 3, '2019-08-15', '2023-03-30', 2, 0),
(4, 4, 4, 4, '2018-11-01', '2021-12-31', 3, 0),
(5, 1, 5, 5, '2022-02-10', NULL, 4, 1);

-- --------------------------------------------------------

--
-- Table structure for table `positions`
--

CREATE TABLE `positions` (
  `position_id` int NOT NULL,
  `position_name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `positions`
--

INSERT INTO `positions` (`position_id`, `position_name`) VALUES
(2, 'Junior Developer'),
(3, 'Mid-Level Developer'),
(4, 'Project Manager'),
(5, 'Software Developer'),
(1, 'Software Engineer');

-- --------------------------------------------------------

--
-- Table structure for table `programmers`
--

CREATE TABLE `programmers` (
  `programmer_id` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `programmers`
--

INSERT INTO `programmers` (`programmer_id`, `name`, `email`, `phone`) VALUES
(1, 'Jan Kowalski', 'jan.kowalski@example.com', '123-456-789'),
(2, 'Anna Nowak', 'anna.nowak@example.com', '987-654-321'),
(3, 'Marek Wi?niewski', 'marek.wisniewski@example.com', '456-789-123'),
(4, 'Katarzyna Zawadzka', 'katarzyna.zawadzka@example.com', '321-654-987');

-- --------------------------------------------------------

--
-- Table structure for table `programmer_skills`
--

CREATE TABLE `programmer_skills` (
  `programmer_id` int NOT NULL,
  `language_id` int NOT NULL,
  `level_id` int NOT NULL,
  `years_of_experience` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `programmer_skills`
--

INSERT INTO `programmer_skills` (`programmer_id`, `language_id`, `level_id`, `years_of_experience`) VALUES
(1, 1, 3, 3),
(1, 2, 2, 2),
(2, 2, 1, 1),
(3, 3, 2, 5),
(4, 4, 4, 10);

-- --------------------------------------------------------

--
-- Table structure for table `programming_languages`
--

CREATE TABLE `programming_languages` (
  `language_id` int NOT NULL,
  `language_name` varchar(50) NOT NULL,
  `description` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `programming_languages`
--

INSERT INTO `programming_languages` (`language_id`, `language_name`, `description`) VALUES
(1, 'Python', 'J?zyk programowania ogólnego przeznaczenia'),
(2, 'JavaScript', 'J?zyk skryptowy u?ywany w aplikacjach webowych'),
(3, 'Java', 'J?zyk programowania u?ywany w aplikacjach biznesowych'),
(4, 'C#', 'J?zyk programowania opracowany przez Microsoft');

-- --------------------------------------------------------

--
-- Table structure for table `skill_levels`
--

CREATE TABLE `skill_levels` (
  `level_id` int NOT NULL,
  `level_name` varchar(50) NOT NULL,
  `description` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `skill_levels`
--

INSERT INTO `skill_levels` (`level_id`, `level_name`, `description`) VALUES
(1, 'Junior', 'Poziom dla pocz?tkuj?cych programistów'),
(2, 'Mid', 'Poziom dla ?redniozaawansowanych programistów'),
(3, 'Senior', 'Poziom dla zaawansowanych programistów'),
(4, 'Master', 'Ekspert w danej dziedzinie');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `colors`
--
ALTER TABLE `colors`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `companies`
--
ALTER TABLE `companies`
  ADD PRIMARY KEY (`company_id`),
  ADD UNIQUE KEY `company_name` (`company_name`);

--
-- Indexes for table `employment`
--
ALTER TABLE `employment`
  ADD PRIMARY KEY (`employment_id`);

--
-- Indexes for table `positions`
--
ALTER TABLE `positions`
  ADD PRIMARY KEY (`position_id`),
  ADD UNIQUE KEY `position_name` (`position_name`);

--
-- Indexes for table `programmers`
--
ALTER TABLE `programmers`
  ADD PRIMARY KEY (`programmer_id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `programmer_skills`
--
ALTER TABLE `programmer_skills`
  ADD PRIMARY KEY (`programmer_id`,`language_id`,`level_id`);

--
-- Indexes for table `programming_languages`
--
ALTER TABLE `programming_languages`
  ADD PRIMARY KEY (`language_id`);

--
-- Indexes for table `skill_levels`
--
ALTER TABLE `skill_levels`
  ADD PRIMARY KEY (`level_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `colors`
--
ALTER TABLE `colors`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT for table `companies`
--
ALTER TABLE `companies`
  MODIFY `company_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `employment`
--
ALTER TABLE `employment`
  MODIFY `employment_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `positions`
--
ALTER TABLE `positions`
  MODIFY `position_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `programmers`
--
ALTER TABLE `programmers`
  MODIFY `programmer_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `programming_languages`
--
ALTER TABLE `programming_languages`
  MODIFY `language_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `skill_levels`
--
ALTER TABLE `skill_levels`
  MODIFY `level_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `programmers`
--
ALTER TABLE `programmers`
  ADD CONSTRAINT `programmers_ibfk_1` FOREIGN KEY (`programmer_id`) REFERENCES `programmer_skills` (`programmer_id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
