7. Физическое проектирование базы данных

Скрипт создания базы данных

-- MySQL Script generated by MySQL Workbench

-- Wed Jan 11 14:19:31 2023

-- Model: New Model Version: 1.0

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

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

-- Schema Used_Car_Selling

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

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

-- Schema Used_Car_Selling

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

CREATE SCHEMA IF NOT EXISTS `Used_Car_Selling` DEFAULT CHARACTER SET utf8 ;

USE `Used_Car_Selling` ;

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

-- Table `Used_Car_Selling`.`Diler`

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

CREATE TABLE IF NOT EXISTS `Used_Car_Selling`.`Diler` (

`idDiler` INT UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(45) NOT NULL,

`patronymic` VARCHAR(45) NULL,

`surname` VARCHAR(45) NOT NULL,

`phone` VARCHAR(12) NOT NULL,

`address` VARCHAR(100) NOT NULL,

`login` VARCHAR(45) NOT NULL,

`password` VARCHAR(45) NOT NULL,

`image` BLOB NOT NULL,

PRIMARY KEY (`idDiler`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb4

COLLATE = utf8mb4_0900_ai_ci;

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

-- Table `Used_Car_Selling`.`Clients`

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

CREATE TABLE IF NOT EXISTS `Used_Car_Selling`.`Clients` (

`idClients` INT UNSIGNED NOT NULL AUTO_INCREMENT,

`Name` VARCHAR(45) NOT NULL,

`Patronymic` VARCHAR(45) NULL,

`Surname` VARCHAR(45) NOT NULL,

`act_address` VARCHAR(100) NULL,

`phone` VARCHAR(11) NULL,

PRIMARY KEY (`idClients`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb4

COLLATE = utf8mb4_0900_ai_ci;

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

-- Table `Used_Car_Selling`.`Cars`

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

CREATE TABLE IF NOT EXISTS `Used_Car_Selling`.`Cars` (

`idCars` INT UNSIGNED NOT NULL AUTO_INCREMENT,

`brand` VARCHAR(45) NOT NULL,

`model` VARCHAR(45) NOT NULL,

`year_creation` VARCHAR(45) NOT NULL,

`run` VARCHAR(45) NOT NULL,

`price` DECIMAL(19,4) UNSIGNED NOT NULL,

`date_sale` VARCHAR(45) NULL,

`notes` MEDIUMTEXT NULL,

`image` BLOB NULL,

PRIMARY KEY (`idCars`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb4

COLLATE = utf8mb4_0900_ai_ci;

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

-- Table `Used_Car_Selling`.`Contracts`

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

CREATE TABLE IF NOT EXISTS `Used_Car_Selling`.`Contracts` (

`idContracts` INT UNSIGNED NOT NULL AUTO_INCREMENT,

`date` VARCHAR(45) NOT NULL,

`commission` DECIMAL(19,4) UNSIGNED NOT NULL,

`Clients_idClients` INT UNSIGNED NOT NULL,

`Cars_idCars` INT UNSIGNED NOT NULL,

`Diler_idDiler` INT UNSIGNED NOT NULL,

PRIMARY KEY (`idContracts`),

INDEX `fk_Contracts_Clients_idx` (`Clients_idClients` ASC) VISIBLE,

INDEX `fk_Contracts_Cars1_idx` (`Cars_idCars` ASC) VISIBLE,

INDEX `fk_Contracts_Diler1_idx` (`Diler_idDiler` ASC) VISIBLE,

CONSTRAINT `fk_Contracts_Clients`

FOREIGN KEY (`Clients_idClients`)

REFERENCES `Used_Car_Selling`.`Clients` (`idClients`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_Contracts_Cars1`

FOREIGN KEY (`Cars_idCars`)

REFERENCES `Used_Car_Selling`.`Cars` (`idCars`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_Contracts_Diler1`

FOREIGN KEY (`Diler_idDiler`)

REFERENCES `Used_Car_Selling`.`Diler` (`idDiler`)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb4

COLLATE = utf8mb4_0900_ai_ci;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;