4	Building DataBase
-- Versione MySQL: 5.0.26
-- Database: MyCustomers
-- 
-- 
 
    CREATE DATABASE `MyCustomers` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

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

-- 
-- Table structure `town`
-- 

CREATE TABLE `town` (
  `zip` varchar(15) NOT NULL,
  `town` varchar(25) default NULL,
  PRIMARY KEY  (`zip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `town` (`zip`, `town`) VALUES 
('12340', 'Milano'),
('12341', 'Roma'),
('12342', 'Napoli'),
('12343', 'Bari'),
('12344', 'Bologna');

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

-- 
-- Table structure `customer`
-- 

CREATE TABLE `customer` (
  `code` varchar(5) NOT NULL,
  `firstname` varchar(50) default NULL,
  `name` varchar(50) default NULL,
  `date` date default NULL,
  `zip` varchar(15) default NULL,
  `balance` float default NULL,
  `photo` blob,
  PRIMARY KEY  (`code`),
  KEY `Icap` (`zip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `customer` (`code`, `firstname`, `name`, `date`, `zip`, 
`balance`, `photo`) VALUES 
('AAA00', 'Rossi', 'Paolo', '2007-11-12', '12340', 123.5, NULL),
('AAA01', 'Bianchi', 'Mariaja', '2007-01-01', '12341', 321, NULL),
('AAA02', 'Bianchi', 'Laura', '2007-01-01', '12342', 321, NULL),
('AAA03', 'Galli', 'Egidio', '2007-01-10', '12343', 0, NULL),
('AAA04', 'Rossi', 'Andrea', '2007-05-05', '12344', 568, NULL),
('AAA05', 'McJoy', 'Bill', '2007-04-01', '12340', 145, NULL),
('AAA06', 'Skorpy', 'John', '2007-01-21', '12340', 0, NULL),
('AAA07', 'Verdi', 'Mario', '2007-01-01', '12344', 125, NULL),
('AAA08', 'Mullen', 'Jane', '2007-02-03', '12342', 45, NULL),
('AAA09', 'Viola', 'Paola', '2007-04-16', '12341', 574, NULL);


-- 
-- Constraints 
-- 
ALTER TABLE `customer`
  ADD CONSTRAINT FOREIGN KEY (`zip`) REFERENCES `town` (`zip`) 
  ON DELETE CASCADE ON UPDATE CASCADE;


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

-- 
-- Table structure `computers`
-- 

CREATE TABLE `computers` (
  `codArticle` varchar(5) NOT NULL,
  `description` varchar(50) default NULL,
  `price` float default NULL,
  PRIMARY KEY  (`codArticle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `computers` (`codArticle`, `description`, `price`) VALUES 
('KTB00', 'PC Tower IBM Intel', 850.5),
('KTB01', 'PC Dell AMD', 500),
('KTB02', 'Notebook Acer Aspire1510 ', 1500);




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

-- 
-- Table structure `reserve`
-- 

CREATE TABLE `reserve` (
  `codCust` varchar(5) NOT NULL,
  `codArticle` varchar(5) NOT NULL,
  `dateReserve` date NOT NULL,
  `riduction` float default NULL,
  `transportExp` float default NULL,
  PRIMARY KEY  (`codCust`,`codArticle`,`dateReserve`),
  KEY `codArticle` (`codArticle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


 
INSERT INTO `reserve` (`codCust`, `codArticle`, `dateReserve`, 
`riduction`, `transportExp`) VALUES 
('AAA00', 'KTB01', '2007-01-01', 3, 40),
('AAA00', 'KTB01', '2007-01-05', 2, 35),
('AAA01', 'KTB00', '2007-01-03', 3, 70),
('AAA01', 'KTB00', '2007-01-05', 3, 70),
('AAA02', 'KTB02', '2007-02-01', 0, 0);




 
-- 
-- Constraints
-- 
ALTER TABLE `reserve`
  ADD CONSTRAINT FOREIGN KEY (`codCust`) REFERENCES `customer` 
(`code`) ON DELETE CASCADE ON UPDATE CASCADE,  
ADD CONSTRAINT FOREIGN KEY (`codArticle`) REFERENCES `computers` (`codArticle`) 
ON DELETE CASCADE ON UPDATE CASCADE;