DROP DATABASE IF EXISTS Prokat_car;
CREATE DATABASE Prokat_car;
USE Prokat_car;
CREATE TABLE Car (
	ID_car int(10) NOT NULL AUTO_INCREMENT,
	c_title varchar(40) NOT NULL,
                   c_mark varchar(40) NOT NULL,
                   c_type varchar(20) NOT NULL,
                   c_year year NOT NULL,
	PRIMARY KEY  (ID_car)
) TYPE=InnoDB;
CREATE TABLE Client (
	ID_client int(10) NOT NULL AUTO_INCREMENT,
	name varchar(40) NOT NULL,
	address varchar(40) NOT NULL,
	tel int(10) NOT NULL,
	num_deal int(10) NOT NULL,
	PRIMARY KEY  (ID_client)
) TYPE=InnoDB;
CREATE TABLE Discount (
	ID_disc int(10) NOT NULL AUTO_INCREMENT,
	disc int(10) NOT NULL,
	num_deal int(10) NOT NULL,
	PRIMARY KEY  (ID_disc)
) TYPE=InnoDB;
CREATE TABLE Forfeit (
                  ID_forfeit int(10) NOT NULL AUTO_INCREMENT,
                  type_f varchar(40) NOT NULL,
                  forfeit int(10) NOT NULL,
	PRIMARY KEY (ID_forfeit)
) TYPE=InnoDB;
CREATE TABLE Deal (
	ID_deal int(10) NOT NULL AUTO_INCREMENT,
	ID_client int(10) NOT NULL,
	ID_car int(10) NOT NULL,
	ID_disc int(10) NOT NULL default '0',
	ID_forfeit int(10),
                  date_st date NOT NULL default '0000-00-00',
                  date_end date  NOT NULL default '0000-00-00',
	price decimal(10,2)  NULL default '0.00',
	PRIMARY KEY  (ID_deal),
	FOREIGN KEY (ID_client) REFERENCES Client(ID_client) ON DELETE
CASCADE ON UPDATE CASCADE,
                  FOREIGN KEY (ID_car) REFERENCES Car(ID_car) ON DELETE
CASCADE ON UPDATE CASCADE,
                  FOREIGN KEY (ID_disc) REFERENCES Discount(ID_disc) ON DELETE
CASCADE ON UPDATE CASCADE,
                  FOREIGN KEY (ID_forfeit) REFERENCES Forfeit(ID_forfeit) ON DELETE
CASCADE ON UPDATE CASCADE) 
TYPE=InnoDB;



show tables;