# MySQL-Front 3.1  (Build 14.13)


# Host: localhost    Database: prestiti
# ------------------------------------------------------
# Server version 4.1.8-nt

#
# Table structure for table genere
#

CREATE DATABASE prestiti;

USE prestiti;

CREATE TABLE `genere` (
  `Id_Genere` int(10) unsigned NOT NULL default '0',
  `Descrizione` varchar(80) NOT NULL default '',
  PRIMARY KEY  (`Id_Genere`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table genere
#

INSERT INTO `genere` VALUES (1,'Thriller');
INSERT INTO `genere` VALUES (2,'Scienza');
INSERT INTO `genere` VALUES (3,'Avventura');

#
# Table structure for table media
#

CREATE TABLE `media` (
  `Id_Media` int(10) unsigned NOT NULL default '0',
  `Titolo` varchar(100) NOT NULL default '',
  `Copie` smallint(6) NOT NULL default '0',
  `Id_Tipo` int(10) unsigned NOT NULL default '0',
  `Id_Genere` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Id_Media`),
  KEY `Id_Tipo` (`Id_Tipo`),
  KEY `Id_Genere` (`Id_Genere`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table media
#

INSERT INTO `media` VALUES (1,'La sfida dei ghiacci',4,2,3);
INSERT INTO `media` VALUES (2,'Supernovae',2,1,2);
INSERT INTO `media` VALUES (3,'Profondo rosso',4,4,1);
INSERT INTO `media` VALUES (4,'Morticia',1,4,1);
INSERT INTO `media` VALUES (5,'Colonna sonora fam. Adams',2,5,1);
INSERT INTO `media` VALUES (6,'Ennio Morricone - musiche',2,3,1);
INSERT INTO `media` VALUES (7,'La grande storia',1,1,2);

#
# Table structure for table prestiti
#

CREATE TABLE `prestiti` (
  `Id_Prestito` int(10) unsigned NOT NULL auto_increment,
  `DataInizio` date NOT NULL default '0000-00-00',
  `DataTermine` date default NULL,
  `Id_Media` int(10) unsigned NOT NULL default '0',
  `Matricola` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Id_Prestito`),
  KEY `Id_Media` (`Id_Media`),
  KEY `Matricola` (`Matricola`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table prestiti
#

INSERT INTO `prestiti` VALUES (2,'2008-03-04','2008-05-04',3,3);
INSERT INTO `prestiti` VALUES (3,'2008-02-10','2008-03-20',2,1);
INSERT INTO `prestiti` VALUES (4,'2007-09-02','2007-10-28',6,2);
INSERT INTO `prestiti` VALUES (5,'2007-10-17','2007-10-30',7,1);
INSERT INTO `prestiti` VALUES (6,'2008-01-20','2008-01-27',7,2);

#
# Table structure for table studenti
#

CREATE TABLE `studenti` (
  `Matricola` int(10) unsigned NOT NULL default '0',
  `Nome` varchar(40) NOT NULL default '',
  `Cognome` varchar(40) NOT NULL default '',
  `Via` varchar(60) NOT NULL default '',
  `Citta` varchar(30) NOT NULL default '',
  `Tel` varchar(15) default NULL,
  PRIMARY KEY  (`Matricola`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table studenti
#

INSERT INTO `studenti` VALUES (1,'Mario','Bianchi','Caracciolo','Milano','023934482');
INSERT INTO `studenti` VALUES (2,'Carla','Gialli','Rubens','Milano','3331020211');
INSERT INTO `studenti` VALUES (3,'Gianni','Neri','Torino','Rho','0220339215');

#
# Table structure for table tipo
#

CREATE TABLE `tipo` (
  `Id_Tipo` int(10) unsigned NOT NULL default '0',
  `Descrizione` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`Id_Tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table tipo
#

INSERT INTO `tipo` VALUES (1,'Libro');
INSERT INTO `tipo` VALUES (2,'DVD');
INSERT INTO `tipo` VALUES (3,'CD');
INSERT INTO `tipo` VALUES (4,'VHS');
INSERT INTO `tipo` VALUES (5,'Compact Cassette');

#
#  Foreign keys for table media
#

ALTER TABLE `media`
  ADD FOREIGN KEY (`Id_Tipo`) REFERENCES `tipo` (`Id_Tipo`),
  ADD FOREIGN KEY (`Id_Genere`) REFERENCES `genere` (`Id_Genere`);

#
#  Foreign keys for table prestiti
#

ALTER TABLE `prestiti`
  ADD FOREIGN KEY (`Id_Media`) REFERENCES `media` (`Id_Media`),
  ADD FOREIGN KEY (`Matricola`) REFERENCES `studenti` (`Matricola`);