--
-- Host: localhost    Database: car_database
-- ------------------------------------------------------


--
-- Table structure for table `Brands`
--

CREATE TABLE `Brands` (
  `brand_id` int unsigned NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(50) NOT NULL,
  PRIMARY KEY (`brand_id`),
  UNIQUE KEY `brand_name` (`brand_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `Brands`
--

INSERT INTO `Brands` VALUES (3,'BMW'),(2,'Ford'),(1,'Toyota');

--
-- Table structure for table `Cars`
--

CREATE TABLE `Cars` (
  `car_number` varchar(15) NOT NULL,
  `manufacture_year` year NOT NULL,
  `brand_id` int unsigned NOT NULL,
  `model_id` int unsigned NOT NULL,
  `color_id` int unsigned NOT NULL,
  `condition_id` int unsigned NOT NULL,
  `owner_id` int unsigned NOT NULL,
  PRIMARY KEY (`car_number`),
  KEY `brand_id` (`brand_id`),
  KEY `color_id` (`color_id`),
  KEY `condition_id` (`condition_id`),
  KEY `owner_id` (`owner_id`),
  KEY `model_id` (`model_id`),
  CONSTRAINT `Cars_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `Brands` (`brand_id`),
  CONSTRAINT `Cars_ibfk_2` FOREIGN KEY (`color_id`) REFERENCES `Colors` (`color_id`),
  CONSTRAINT `Cars_ibfk_3` FOREIGN KEY (`condition_id`) REFERENCES `Conditions` (`condition_id`),
  CONSTRAINT `Cars_ibfk_4` FOREIGN KEY (`owner_id`) REFERENCES `Owners` (`owner_id`),
  CONSTRAINT `Cars_ibfk_5` FOREIGN KEY (`model_id`) REFERENCES `Models` (`model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `Cars`
--

INSERT INTO `Cars` VALUES ('А123АА77',2015,1,1,1,2,1),('А123ХА122',2005,3,5,1,2,1),('А123ХА123',1995,3,6,1,3,1),('В456ВВ78',2020,1,2,3,1,2);

--
-- Table structure for table `Colors`
--

CREATE TABLE `Colors` (
  `color_id` int unsigned NOT NULL AUTO_INCREMENT,
  `color_name` varchar(30) NOT NULL,
  PRIMARY KEY (`color_id`),
  UNIQUE KEY `color_name` (`color_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `Colors`
--

INSERT INTO `Colors` VALUES (1,'Красный'),(2,'Синий'),(3,'Чёрный');

--
-- Table structure for table `Conditions`
--

CREATE TABLE `Conditions` (
  `condition_id` int unsigned NOT NULL AUTO_INCREMENT,
  `condition_name` varchar(30) NOT NULL,
  PRIMARY KEY (`condition_id`),
  UNIQUE KEY `condition_name` (`condition_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `Conditions`
--

INSERT INTO `Conditions` VALUES (3,'Аварийный'),(2,'Б/у'),(1,'Новый');

--
-- Table structure for table `Models`
--

CREATE TABLE `Models` (
  `model_id` int unsigned NOT NULL AUTO_INCREMENT,
  `brand_id` int unsigned NOT NULL,
  `model_name` varchar(50) NOT NULL,
  PRIMARY KEY (`model_id`),
  UNIQUE KEY `model_name` (`model_name`),
  KEY `Models_ibfk_1` (`brand_id`),
  CONSTRAINT `Models_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `Brands` (`brand_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `Models`
--

INSERT INTO `Models` VALUES (1,1,'Camry'),(2,1,'Corolla'),(3,2,'Focus'),(4,2,'Fiesta'),(5,3,'X5'),(6,3,'i8'),(7,3,'x6');

--
-- Table structure for table `Owners`
--

CREATE TABLE `Owners` (
  `owner_id` int unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(50) NOT NULL,
  `address` varchar(255) NOT NULL,
  PRIMARY KEY (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `Owners`
--

INSERT INTO `Owners` VALUES (1,'Иванов','ул. Ленина, 10'),(2,'Петров','пр. Мира, 5');


