Repair Times 2020

Repair Times 2020

Car repairs and maintenance time. Over 5,000,000 time limits for 10,781 vehicle types. For ease of integration, the database consists of two tables - a catalog of cars and a catalog of time standards. Included with two tables - autoNorms2020, autoNorms2020Data, and the SQL file with examples of basic database queries.

Weight of files in distribution:

• AutoNorms2020.sql.zip - 266Kb
• AutoNorms2020Data.sql.zip - 90Mb

Weight of unpacked ZIPs:

• AutoNorms2020.sql - 4Mb
• AutoNorms2020Data.sql - 1.8Gb

Approximate weight of mySQL database in deployed form together with indexes: 2Gb.

Kind of works: removing and installing, disassembling and assembling, checking, checking and adjusting, pumping, draining and filling, removing and replacing, separating, replacing, disconnecting, refilling, partial removal, draining, performing other works;

1. DDL of AutoNorms2020 table

74 car manufacturers, 2245 models, 10781 vehicle modifications. List of car brands, models of which are presented in the database "Repair Times 2020": ALFA ROMEO, ASIA, AUDI, AUSTIN, BEDFORD, BMW, CADILLAC, CARBODIES, CHEVROLET, CHRYSLER, CITROEN, DACIA, DAEWOO, DAF, DAIHATSU, DAIMLER, DODGE, DS, FIAT, FORD, FSO, GREAT WALL MOTORS, HONDA, HYUNDAI, INFINITI, INNOCENTI, ISUZU, IVECO, JAGUAR, JEEP, KIA, LADA, LANCIA, LAND ROVER, LDV, LEXUS, LOTUS, MARUTI, MAXUS, MAZDA, MCC/SMART, MERCEDES-BENZ, MG (-05), MG MOTOR UK (08-), MINI, MITSUBISHI, MORRIS, MOSKVICH, NISSAN, NSU, OPEL/VAUXHALL, PERODUA, PEUGEOT, PIAGGIO, PORSCHE, PROTON, RENAULT, ROVER, SAAB, SAO, SEAT, SKODA, SSANGYONG, SUBARU, SUZUKI, TALBOT (HILLMAN), TATA, TOYOTA, TRABANT, TRIUMPH, VOLKSWAGEN, VOLVO, WARTBURG, ZASTAVA (YUGO).

CREATE TABLE `AutoNorms2020` (
  `motorId` int(11) unsigned NOT NULL COMMENT 'Motor ID', 
  `modelId` int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'Vehicle model ID',
  `carId` int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'Car type ID',
  `typeId` int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'Vehicle model ID',
  `make` varchar(100) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Make',
  `model` varchar(100) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Model',
  `litres` varchar(20) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Engine volume, liters',
  `fuel` varchar(10) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Fuel type',
  `silovoyAgregat` varchar(100) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Power unit',
  `module` varchar(10) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Module code',
  `engineSalesName` varchar(100) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Motor sales name',
  `engineType` varchar(50) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Motor type',
  `engineCode` varchar(100) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Motor code',
  `modification_name` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Modification name',
  `year` varchar(50) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Years of production of the vehicle',
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`,`modelId`,`motorId`,`carId`,`typeId`),
  UNIQUE KEY `motorId` (`motorId`) USING BTREE,
  KEY `modelId` (`modelId`,`carId`,`typeId`),
  KEY `make` (`make`),
  KEY `make_2` (`make`,`model`),
  KEY `engineCode` (`engineCode`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2. DDL of autoNorms2020Data table

This table stores 5 040 141 unique record of time. The connection to the "Vehicles" table (#1) is made via the motorId field.

CREATE TABLE `autoNorms2020Data` (
  `motorId` int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'Motor ID',
  `workId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Work ID',
  `TopCatId` int(11) NOT NULL DEFAULT 0 COMMENT 'Top category ID',
  `SubCatId` int(11) NOT NULL DEFAULT 0 COMMENT 'Sub category ID',
  `TopCat` varchar(350) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Top category name',
  `SubCat` varchar(350) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Sub category name',
  `workName` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Work title',
  `workTarget` varchar(500) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Serviced part',
  `workTime` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'Repair time',
  `workPrice` int(1) NOT NULL DEFAULT 0 COMMENT 'Price per time unit',
  `parent` int(11) DEFAULT 0 COMMENT 'ID of parent category',
  PRIMARY KEY (`workId`,`motorId`),
  KEY `motorId` (`motorId`),
  KEY `motorId_2` (`motorId`,`workId`,`TopCatId`,`SubCatId`),
  KEY `workName` (`workName`),
  KEY `workTarget` (`workTarget`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;