-- phpMyAdmin SQL Dump
-- version 4.9.3
-- https://www.phpmyadmin.net/
--
-- Host: localhost:8889
-- Generation Time: May 08, 2021 at 10:35 PM
-- Server version: 5.7.26
-- PHP Version: 7.4.2

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

--
-- Database: `mysitedb`
--
CREATE DATABASE IF NOT EXISTS `mysitedb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `mysitedb`;

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

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` int(11) NOT NULL,
  `created` date NOT NULL,
  `author` varchar(20) NOT NULL,
  `comment` varchar(256) NOT NULL,
  `art_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `comments`
--

INSERT INTO `comments` (`id`, `created`, `author`, `comment`, `art_id`) VALUES
(2, '2020-10-21', 'fgdfg', 'dfgfd', 2),
(3, '2020-10-21', '555', '555', 4),
(5, '2020-10-29', 'hgjhg', 'jhgj', 3),
(7, '2020-10-29', 'gfhfgh', 'gfh', 5);

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

--
-- Table structure for table `notes`
--

CREATE TABLE `notes` (
  `id` int(11) NOT NULL,
  `created` date DEFAULT NULL,
  `title` varchar(20) DEFAULT NULL,
  `article` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `notes`
--

INSERT INTO `notes` (`id`, `created`, `title`, `article`) VALUES
(2, '2020-10-21', '', ''),
(3, '2020-10-21', '', ''),
(4, '2020-10-21', '4444', '4444'),
(5, '2020-10-29', '', '');

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

--
-- Table structure for table `privileges`
--

CREATE TABLE `privileges` (
  `id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `group` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `privileges`
--

INSERT INTO `privileges` (`id`, `username`, `password`, `group`) VALUES
(5, 'alexx876', '0aca988c4ed2cb55f9b48b186eff895a', 1),
(8, 'user', 'ee11cbb19052e40b07aac0ca060c23ee', 0);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `comments`
--
ALTER TABLE `comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `art_id` (`art_id`);

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

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `comments`
--
ALTER TABLE `comments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `notes`
--
ALTER TABLE `notes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `privileges`
--
ALTER TABLE `privileges`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `comments`
--
ALTER TABLE `comments`
  ADD CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`art_id`) REFERENCES `notes` (`id`);
--
-- Database: `ta_system`
--
CREATE DATABASE IF NOT EXISTS `ta_system` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `ta_system`;

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

--
-- Table structure for table `accidentInformatiom`
--

CREATE TABLE `accidentInformatiom` (
  `id` int(11) NOT NULL,
  `cardType` int(1) NOT NULL,
  `uvdNumber` int(6) NOT NULL,
  `go-rovdNumber` int(6) NOT NULL,
  `addCardNumber` int(11) NOT NULL,
  `accidentDate` date NOT NULL,
  `accidentTime` time NOT NULL,
  `deadNumber` int(2) NOT NULL,
  `injuredNumber` int(2) NOT NULL,
  `roadAccidentCategory` int(1) NOT NULL,
  `roadAccidentType` int(1) NOT NULL,
  `totalMaterialDamage` int(7) NOT NULL,
  `accidentArea` varchar(45) NOT NULL,
  `locality` varchar(60) NOT NULL,
  `street` varchar(60) NOT NULL,
  `houseNumber` int(3) NOT NULL,
  `blockNumber` int(3) NOT NULL,
  `intersectedStreet` varchar(60) NOT NULL,
  `highwayValue` int(1) NOT NULL,
  `highwayName` varchar(45) NOT NULL,
  `mileageAndMeters` varchar(15) NOT NULL,
  `roadBeloning` int(1) NOT NULL,
  `roadOrganization` varchar(30) NOT NULL,
  `typeOfCoating` int(1) NOT NULL,
  `roadwayState` int(2) NOT NULL,
  `illumination` int(1) NOT NULL,
  `planAndProfileElements` int(1) NOT NULL,
  `weatherCondition` int(1) NOT NULL,
  `trafficLanesNumber` int(1) NOT NULL,
  `roadWidth` float NOT NULL,
  `shoulderWidth` float NOT NULL,
  `roadsideWidth` float NOT NULL,
  `divingStripWidth` float NOT NULL,
  `sidewalkWidth` float NOT NULL,
  `unsatisfactoryRoadConditions` int(2) NOT NULL,
  `otherRoadElements` int(2) NOT NULL,
  `schemaFileName` varchar(30) NOT NULL,
  `employeeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `departments`
--

CREATE TABLE `departments` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `departments`
--

INSERT INTO `departments` (`id`, `name`) VALUES
(1, 'ОГАИ Фрунзенского РУВД');

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

--
-- Table structure for table `employee`
--

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `firstName` varchar(45) NOT NULL,
  `lastName` varchar(45) NOT NULL,
  `patronymic` varchar(45) NOT NULL,
  `login` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `departmentID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`id`, `firstName`, `lastName`, `patronymic`, `login`, `password`, `departmentID`) VALUES
(1, 'Петров', 'Владимир', 'Андреевич', 'petrov.v', '3e66f7aaad4294225b5d14e513278d3a', 1);

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

--
-- Table structure for table `participantsGeneralInformation`
--

CREATE TABLE `participantsGeneralInformation` (
  `id` int(11) NOT NULL,
  `vehicleSerialNumber` int(2) NOT NULL,
  `movementMemberNumber` int(2) NOT NULL,
  `memberCategory` int(1) NOT NULL,
  `beloningToVehicle` int(2) NOT NULL,
  `memberStatus` varchar(5) NOT NULL,
  `dateDeath` date NOT NULL,
  `severityOfInjuries` int(3) NOT NULL,
  `medicalInstitutiionWhereIsPlaced` varchar(45) NOT NULL,
  `trafficViolation` int(2) NOT NULL,
  `beltOrHemlet` int(1) NOT NULL,
  `measuresTaken` int(1) NOT NULL,
  `accidentInformatiomID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `partisipantsPersonalData`
--

CREATE TABLE `partisipantsPersonalData` (
  `id` int(11) NOT NULL,
  `passportNumber` varchar(45) NOT NULL,
  `firstName` varchar(45) NOT NULL,
  `lastName` varchar(45) NOT NULL,
  `patronymic` varchar(45) NOT NULL,
  `sex` varchar(1) NOT NULL,
  `birthday` date NOT NULL,
  `driversLisence` varchar(45) NOT NULL,
  `lisenceIssueDate` date NOT NULL,
  `drivingExperience` varchar(10) NOT NULL,
  `timeBehindTheWheel` varchar(10) NOT NULL,
  `state` varchar(45) NOT NULL,
  `area` varchar(60) NOT NULL,
  `district` varchar(45) NOT NULL,
  `locality` varchar(60) NOT NULL,
  `street` varchar(60) NOT NULL,
  `houseNumber` int(3) NOT NULL,
  `blockNumber` int(3) NOT NULL,
  `flatNumber` int(3) NOT NULL,
  `plaseOfWork` varchar(60) NOT NULL,
  `participantsGeneralInformationID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `vehicleData`
--

CREATE TABLE `vehicleData` (
  `id` int(11) NOT NULL,
  `brandName` varchar(45) NOT NULL,
  `vehicleType` int(3) NOT NULL,
  `licensePlate` varchar(45) NOT NULL,
  `engineNumber` varchar(45) NOT NULL,
  `bodyNumber` varchar(45) NOT NULL,
  `state` varchar(45) NOT NULL,
  `area` varchar(60) NOT NULL,
  `district` varchar(45) NOT NULL,
  `locality` varchar(60) NOT NULL,
  `street` varchar(60) NOT NULL,
  `houseNumber` int(3) NOT NULL,
  `blockNumber` int(3) NOT NULL,
  `flatNumber` int(3) NOT NULL,
  `nameOfCompanyOrMan` varchar(60) NOT NULL,
  `driveType` int(1) NOT NULL,
  `loadType` int(1) NOT NULL,
  `steeringLocation` varchar(6) NOT NULL,
  `color` int(2) NOT NULL,
  `trailerPresence` varchar(12) NOT NULL,
  `vehicleProblems` int(2) NOT NULL,
  `participantsGeneralInformationID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `accidentInformatiom`
--
ALTER TABLE `accidentInformatiom`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employeeID` (`employeeID`);

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

--
-- Indexes for table `employee`
--
ALTER TABLE `employee`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id` (`id`),
  ADD KEY `departmentID` (`departmentID`);

--
-- Indexes for table `participantsGeneralInformation`
--
ALTER TABLE `participantsGeneralInformation`
  ADD PRIMARY KEY (`id`),
  ADD KEY `accidentInformatiomID` (`accidentInformatiomID`);

--
-- Indexes for table `partisipantsPersonalData`
--
ALTER TABLE `partisipantsPersonalData`
  ADD PRIMARY KEY (`id`),
  ADD KEY `participantsGeneralInformationID` (`participantsGeneralInformationID`);

--
-- Indexes for table `vehicleData`
--
ALTER TABLE `vehicleData`
  ADD PRIMARY KEY (`id`),
  ADD KEY `participantsGeneralInformationID` (`participantsGeneralInformationID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `accidentInformatiom`
--
ALTER TABLE `accidentInformatiom`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `departments`
--
ALTER TABLE `departments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `employee`
--
ALTER TABLE `employee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `participantsGeneralInformation`
--
ALTER TABLE `participantsGeneralInformation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `partisipantsPersonalData`
--
ALTER TABLE `partisipantsPersonalData`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `vehicleData`
--
ALTER TABLE `vehicleData`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `accidentInformatiom`
--
ALTER TABLE `accidentInformatiom`
  ADD CONSTRAINT `accidentinformatiom_ibfk_1` FOREIGN KEY (`employeeID`) REFERENCES `employee` (`id`);

--
-- Constraints for table `employee`
--
ALTER TABLE `employee`
  ADD CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`departmentID`) REFERENCES `departments` (`id`);

--
-- Constraints for table `partisipantsPersonalData`
--
ALTER TABLE `partisipantsPersonalData`
  ADD CONSTRAINT `partisipantspersonaldata_ibfk_1` FOREIGN KEY (`participantsGeneralInformationID`) REFERENCES `participantsGeneralInformation` (`id`);

--
-- Constraints for table `vehicleData`
--
ALTER TABLE `vehicleData`
  ADD CONSTRAINT `vehicledata_ibfk_1` FOREIGN KEY (`participantsGeneralInformationID`) REFERENCES `participantsGeneralInformation` (`id`);