Esercizio IV D SIA: esercizio SQL farmacie (create, insert into, alter table, Innodb)

CREATE DATABASE farmacia;
USE farmacia;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

CREATE TABLE IF NOT EXISTS `ditta` (
  `IDDitta` varchar(20) NOT NULL,
  `NomeDitta` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`IDDitta`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `ditta` (`IDDitta`, `NomeDitta`) VALUES
('140', 'FOURINIER PHARMA S.P'),
('168', 'HOSPIRE S.P.A.'),
('180', 'INDUSTRIA FARMACEUTI'),
('228', 'LABORATORIO FARMACEU'),
('310', 'PHARMACIA ITALIA S.P'),
('334', 'RECORDATI INDUSTRIA '),
('355', 'SCHWARZ PHARMA S.P.A'),
('377', 'TEOFARMA S.R.L.'),
('401', 'WYETH MEDICA IRELAND'),
('42 ', 'AVENTIS PHARMA S.P.A'),
('6', 'A.M.S.A. S.R.L.'),
('69 ', 'BRACCO S.P.A.'),
('70', 'BRISTOL MYERS SQUIBB');

CREATE TABLE IF NOT EXISTS `farmaco` (
  `Codice` varchar(20) NOT NULL,
  `Denominazione` varchar(40) DEFAULT NULL,
  `Descrizione` varchar(255) DEFAULT NULL,
  `Prezzo` decimal(12,0) DEFAULT NULL,
  `ATC` varchar(20) DEFAULT NULL,
  `IDDitta` varchar(20) DEFAULT NULL,
  `DataAutMin` date DEFAULT NULL,
  PRIMARY KEY (`Codice`),
  KEY `ATC` (`ATC`),
  KEY `farmaco_ibfk_2` (`IDDitta`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `farmaco` (`Codice`, `Denominazione`, `Descrizione`, `Prezzo`, `ATC`, `IDDitta`, `DataAutMin`) VALUES
('001537012', 'FARMOTAL', '500MG IN POLVERE PER SOLUZIONE PER INFUSIONE 1 FLACONE', '6', 'N01AF03', '310', '1996-08-03'),
('001801012', 'GLUCANTIM', '1.5 G/5 ML SOLUZIONE INIETTABILE PER USO INTRAMUSCOLARE', '4', 'P01CB01', '42', '1997-05-12'),
('002021069', 'TEFAMIN', 'ELISIR 1 FLAC. 200 ML 1.58%', '3', 'N03AB02', '334', '2002-03-15'),
('002129017', 'DINTONIA', '30 COMPRESSE RIVESTITE 100 MG', '2', 'R03DA04', '334', '2001-11-05'),
('002309033', 'FOLINA', '15 MG/2 SOLUZIONE INIETTABILI PER USO I.M 5 FIALETTE', '2', 'B03BB01', '355', '1998-04-14'),
('002309045', 'FOLINA', '5 MG CAPSULE RIGDE 20 CAPSULE', '2', 'B03BB01', '355', '2001-11-01'),
('002347019', 'PENTOTHAL SODIUM', '1 FLACONE 0.5G', '6', 'N01AF03', '168', '1999-08-11');

CREATE TABLE IF NOT EXISTS `principi` (
  `ATC` varchar(20) NOT NULL,
  `Principioattivo` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`ATC`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `principi` (`ATC`, `Principioattivo`) VALUES
('B03BB01', 'ACIDO FOLICO'),
('D06AX02', 'CLORAMFENICOLO'),
('G03BA03', 'TESTOSTERONE'),
('G03CA57', 'ESTROGENI CONIU'),
('G03GA01', 'GONADOTROPINA C'),
('J01GA01', 'STREPTOMICINA'),
('N01AF03', 'TIOPENTAL'),
('N03AA02', 'FENOBARBITAL'),
('N03AB02', 'FENITOINA'),
('N04AA01', 'TRIESIFENIDILE'),
('P01CB01', 'MEGLUMINA'),
('R03DA04', 'TEOFILLINA');

ALTER TABLE `farmaco`
  ADD CONSTRAINT `farmaco_ibfk_2` FOREIGN KEY (`IDDitta`) REFERENCES `ditta` (`IDDitta`),
  ADD CONSTRAINT `farmaco_ibfk_1` FOREIGN KEY (`ATC`) REFERENCES `principi` (`ATC`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;