scr

CREATE DATABASE IF NOT EXISTS CarLoan;

USE CarLoan;

-- -----------------------------------------------------
-- Table `CarLoan`.`sede`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CarLoan`.`sede` (
  `id_sede` INT NOT NULL,
  `nome` VARCHAR(45) NOT NULL,
  `citta` VARCHAR(45) NOT NULL,
  `via` VARCHAR(45) NOT NULL,
  `telefono` VARCHAR(9) NULL,
  PRIMARY KEY (`id_sede`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CarLoan`.`impiegato`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CarLoan`.`impiegato` (
  `cf` CHAR(16) NOT NULL,
  `nome` VARCHAR(45) NOT NULL,
  `cognome` VARCHAR(45) NOT NULL,
  `id_sede` INT NOT NULL,
  `mail` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `telefono` VARCHAR(9) NULL,
  PRIMARY KEY (`cf`),
  INDEX `id_sede_idx` (`id_sede` ASC),
      FOREIGN KEY (`id_sede`)
    REFERENCES `CarLoan`.`sede` (`id_sede`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `CarLoan`.`direttore`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `CarLoan`.`direttore` (
  `cf` CHAR(16) NOT NULL,
  `nome` VARCHAR(45) NOT NULL,
  `cognome` VARCHAR(45) NOT NULL,
  `id_sede` INT NOT NULL,
  `mail` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`cf`),
  INDEX `id_sede_idx` (`id_sede` ASC),
    FOREIGN KEY (`id_sede`)
    REFERENCES `CarLoan`.`sede` (`id_sede`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `CarLoan`.`amministratore`
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS `amministratore` (
  `Username` varchar(16) NOT NULL,
  `Password` varchar(20) NOT NULL,
  PRIMARY KEY(`Username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- -----------------------------------------------------
-- Table `CarLoan`.`categoria`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `categoria` (
  `IdCategoria` int(1) NOT NULL AUTO_INCREMENT,
  `nome` varchar(20) NOT NULL,
  `costo_base` float NOT NULL,
  `costoKm` float NOT NULL,
  `costo_giornaliero` float NOT NULL,
  PRIMARY KEY (`IdCategoria`),
  UNIQUE KEY `nome_UNIQUE` (`nome`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `cliente` (
  `cf` varchar(16) NOT NULL,
  `Cognome` varchar(30) NOT NULL,
  `Nome` varchar(30) NOT NULL,
  `DataNascitaCli` date DEFAULT NULL,
  `TelefonoCli` varchar(20) NOT NULL,
  `EmailCli` varchar(30) NOT NULL,
  PRIMARY KEY (`cf`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `veicolo` (
  `targa` VARCHAR(7) NOT NULL,
  `modello` VARCHAR(30) NOT NULL,
  `Chilometri` int NOT NULL,
  `SedeV` INT(3) NOT NULL,
  `Stato` VARCHAR(30) NOT NULL,
  `Categoria` INT(1) NOT NULL,
  `Alimentazione` INT(1) NOT NULL,
  `Porte` INT(1) NOT NULL,
  `nPosti` INT(1) NOT NULL,
  PRIMARY KEY (`targa`),
  INDEX `Sede_idx` (`SedeV` ASC),
  INDEX `categoria_idx` (`Categoria` ASC),
  CONSTRAINT `sedeV`
    FOREIGN KEY (`SedeV`)
    REFERENCES `carloan`.`sede` (`id_sede`)
  ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `categoria`
    FOREIGN KEY (`Categoria`)
    REFERENCES `carloan`.`categoria` (`IdCategoria`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE=InnoDB DEFAULT CHARSET=latin1;




CREATE TABLE IF NOT EXISTS `contratto` (
  `IdContratto` int(6) NOT NULL AUTO_INCREMENT,
  `DataInizioCont` date NOT NULL,
  `DataFineCont` date NOT NULL,
  `DataConsegna` date NULL,
  `Penale` float NULL,
  `Acconto` float NOT NULL,
  `Saldo` float NULL,
  `TotaleImporto` float NULL,
  `ChilometriPercorsi` INT NULL,
  `SedeInizio` int(3) NOT NULL,
  `SedeFine` int(3) NULL,
  `targa` varchar(7) NOT NULL,
  `Cliente` varchar(16) NOT NULL,
  `tipo_contratto` varchar(16) NOT NULL,
  PRIMARY KEY (`IdContratto`),
  KEY `SedeInizio_idx` (`SedeInizio`),
  KEY `Cliente_idx` (`Cliente`),
  KEY `SedeFine_idx` (`SedeFine`),
  CONSTRAINT `Cliente` FOREIGN KEY (`Cliente`) REFERENCES `cliente` (`cf`),
  CONSTRAINT `SedeFine` FOREIGN KEY (`SedeFine`) REFERENCES `sede` (`id_sede`),
  CONSTRAINT `SedeInizio` FOREIGN KEY (`SedeInizio`) REFERENCES `sede` (`id_sede`),
  CONSTRAINT `targa` FOREIGN KEY (`targa`) REFERENCES `veicolo` (`targa`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO sede VALUES(1,'CIAO','ciao,'ciao','ciao','ciao');

INSERT INTO impiegato VALUES('pcrdnc','abba','abba',1,'abba@an.it','abba','abba');