CREATE DATABASE IF NOT EXISTS DB_Geografia;
USE DB_Geografia;

DROP TABLE IF EXISTS `citta_attraversate`;
CREATE TABLE `citta_attraversate` (
  `CodFiume` int(10) unsigned NOT NULL auto_increment,
  `CodCitta` int(10) unsigned NOT NULL,
  PRIMARY KEY  USING BTREE (`CodFiume`,`CodCitta`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `citta_attraversate` (`CodFiume`,`CodCitta`) VALUES 
 (1,23),
 (1,24),
 (2,1),
 (2,17),
 (2,18),
 (2,22),
 (2,25),
 (3,26),
 (4,21),
 (5,27),
 (6,29),
 (6,30),
 (6,31),
 (7,32),
 (7,33),
 (7,34),
 (8,12),
 (8,35),
 (9,36),
 (9,37),
 (10,13),
 (10,38),
 (11,39),
 (12,40),
 (12,41),
 (13,42),
 (13,43),
 (13,44),
 (14,6),
 (14,45),
 (15,46),
 (15,47),
 (16,48),
 (17,49),
 (17,50);

DROP TABLE IF EXISTS `citta`;
CREATE TABLE `citta` (
  `CodCitta` int(10) unsigned NOT NULL auto_increment,
  `Nome` varchar(15) NOT NULL,
  `Popolazione` int(10) unsigned default NULL,
  `CodStato` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`CodCitta`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `citta` (`CodCitta`,`Nome`,`Popolazione`,`CodStato`) VALUES 
 (1,'Vienna',1631082,4),
 (2,'Bruxelles',145000,22),
 (3,'Minsk',1780000,13),
 (4,'Sofia',1241878,10),
 (5,'Zagabria',879145,7),
 (6,'Parigi',2153600,16),
 (7,'Berlino',3406780,3),
 (8,'Roma',2708395,23),
 (9,'Vaduz',5014,15),
 (10,'Chisinau',717900,12),
 (11,'Amsterdam',739295,17),
 (12,'Varsavia',1701447,19),
 (13,'Lisbona',564657,21),
 (14,'Praga',1372975,18),
 (15,'Bucarest',2082000,9),
 (16,'Mosca',14472629,1),
 (17,'Belgrado',1576124,8),
 (18,'Bratislava',426091,5),
 (19,'Madrid',3092759,20),
 (20,'Berna',122178,14),
 (21,'Kiev',2700000,11),
 (22,'Budapest',1698106,6),
 (23,'Niznyj Novgorod',1311252,1),
 (24,'Volgograd',1011417,1),
 (25,'Linz',189343,4),
 (26,'Orenburg',549361,1),
 (27,'Rostov sul Don',1068267,1),
 (28,'Basilea',166563,14),
 (29,'Magonza',196102,3),
 (30,'Coblenza',107950,3),
 (31,'Utrecht',290529,17),
 (32,'Amburgo',1754317,3),
 (33,'Magdeburgo',229320,3),
 (34,'Dresda',506337,3),
 (35,'Cracovia',756267,19),
 (36,'Orleans',113126,16);
INSERT INTO `citta` (`CodCitta`,`Nome`,`Popolazione`,`CodStato`) VALUES 
 (37,'Nantes',287200,16),
 (38,'Toledo',75578,20),
 (39,'Saragozza',667034,20),
 (40,'Liegi',185000,22),
 (41,'Maastricht',118378,17),
 (42,'Ginevra',185862,14),
 (43,'Lione',466400,16),
 (44,'Avignone',85935,16),
 (45,'Rouen',389862,16),
 (46,'Siviglia',699145,20),
 (47,'Cordoba',321164,20),
 (48,'Torino',905209,23),
 (49,'Tolosa',390350,16),
 (50,'Bordeaux',229500,16);

DROP TABLE IF EXISTS `confini`;
CREATE TABLE `confini` (
  `CodStato_1` int(10) unsigned NOT NULL auto_increment,
  `CodStato_2` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`CodStato_1`,`CodStato_2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `confini` (`CodStato_1`,`CodStato_2`) VALUES 
 (1,2),
 (1,11),
 (1,13),
 (1,19),
 (3,4),
 (3,14),
 (3,16),
 (3,17),
 (3,18),
 (3,19),
 (3,22),
 (4,5),
 (4,6),
 (4,14),
 (4,15),
 (4,18),
 (4,23),
 (5,6),
 (5,11),
 (5,18),
 (5,19),
 (6,7),
 (6,8),
 (6,9),
 (6,11),
 (7,8),
 (8,9),
 (8,10),
 (9,10),
 (9,11),
 (9,12),
 (11,12),
 (11,13),
 (11,19),
 (13,19),
 (14,15),
 (14,16),
 (14,23),
 (16,22),
 (16,23),
 (17,22),
 (18,19),
 (20,21);

DROP TABLE IF EXISTS `fiumi`;
CREATE TABLE `fiumi` (
  `CodFiume` int(10) unsigned NOT NULL auto_increment,
  `Nome` varchar(20) NOT NULL,
  `Lunghezza` int(10) unsigned default NULL,
  `CodStatoSorgente` int(10) unsigned default NULL,
  `Foce` varchar(20) default NULL,
  `CodStatoFoce` int(10) unsigned default NULL,
  PRIMARY KEY  (`CodFiume`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `fiumi` (`CodFiume`,`Nome`,`Lunghezza`,`CodStatoSorgente`,`Foce`,`CodStatoFoce`) VALUES 
 (1,'Volga',3692,1,'Mar Caspio',2),
 (2,'Danubio',2860,3,'Mar Nero',12),
 (3,'Ural',2428,1,'Mar Caspio',2),
 (4,'Dnepr',2290,1,'Mar Nero',11),
 (5,'Don',1950,1,'Mar Nero',1),
 (6,'Reno',1320,14,'Mare del Nord',17),
 (7,'Elba',1091,18,'Mare del Nord',3),
 (8,'Vistola',1047,19,'Mar Baltico',19),
 (9,'Loira',1012,16,'Oceano Atalntico',16),
 (10,'Tago',1038,20,'Oceano Atlantico',21),
 (11,'Ebro',927,20,'Mar Mediterraneo',20),
 (12,'Mosa',925,16,'Fiume Reno',17),
 (13,'Rodano',812,14,'Mar Mediterraneo',16),
 (14,'Senna',776,16,'Oceano Atlantico',16),
 (15,'Guadalquivir',657,20,'Oceano Atlantico',20),
 (16,'Po',652,23,'Mar Adriatico',23),
 (17,'Garonna',647,16,'Oceano Atlantico',20);

DROP TABLE IF EXISTS `stati_attraversati`;
CREATE TABLE `stati_attraversati` (
  `CodFiume` int(10) unsigned NOT NULL auto_increment,
  `CodStato` int(10) unsigned NOT NULL,
  `Km` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`CodFiume`,`CodStato`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `stati_attraversati` (`CodFiume`,`CodStato`,`Km`) VALUES 
 (1,1,3531),
 (2,3,647),
 (2,4,350),
 (2,5,NULL),
 (2,6,420),
 (2,7,188),
 (2,8,588),
 (2,9,NULL),
 (2,10,520),
 (2,11,NULL),
 (2,12,NULL),
 (3,1,NULL),
 (4,1,NULL),
 (4,11,1121),
 (4,13,690),
 (5,1,1870),
 (6,3,865),
 (6,4,NULL),
 (6,14,375),
 (6,15,41),
 (6,16,NULL),
 (6,17,NULL),
 (7,3,700),
 (7,18,370),
 (8,19,1047),
 (9,16,1020),
 (10,20,790),
 (10,21,275),
 (11,20,927),
 (12,16,450),
 (12,17,NULL),
 (12,22,183),
 (13,14,264),
 (13,16,522),
 (14,16,776),
 (15,20,657),
 (16,23,652),
 (17,16,553),
 (17,20,NULL);

DROP TABLE IF EXISTS `stati`;
CREATE TABLE `stati` (
  `CodStato` int(10) unsigned NOT NULL auto_increment,
  `Nome` varchar(15) NOT NULL,
  `Popolazione` int(10) unsigned default NULL,
  `CodCapitale` int(10) unsigned default NULL,
  `Superficie` int(10) unsigned default NULL,
  PRIMARY KEY  (`CodStato`),
  KEY `Capitale` (`CodCapitale`),
  CONSTRAINT `Capitale` FOREIGN KEY (`CodCapitale`) REFERENCES `citta` (`CodCitta`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `stati` (`CodStato`,`Nome`,`Popolazione`,`CodCapitale`,`Superficie`) VALUES 
 (1,'Russia',144978573,16,17075200),
 (3,'Germania',83251851,7,357021),
 (4,'Austria',8169929,1,83858),
 (5,'Slovacchia',5422366,18,48845),
 (6,'Ungheria',10075034,22,93030),
 (7,'Croazia',4390751,5,56542),
 (8,'Serbia',9780000,17,88361),
 (9,'Romania',21698181,15,238391),
 (10,'Bulgaria',7621337,4,110910),
 (11,'Ucraina',48396470,21,603700),
 (12,'Moldavia',4434547,10,33843),
 (13,'Bielorussia',10335382,3,207600),
 (14,'Svizzera',7301994,20,41290),
 (15,'Liechtenstein',32842,9,160),
 (16,'Francia',59765983,6,547030),
 (17,'Paesi Bassi',16318199,11,41526),
 (18,'Repubblica Ceca',10256760,14,78866),
 (19,'Polonia',38625478,12,312685),
 (20,'Spagna',40077100,19,504782),
 (21,'Portogallo',10084245,13,92391),
 (22,'Belgio',10274595,2,30510),
 (23,'Italia',57715625,8,301230);