# Connection: root@localhost:3306
# Host: localhost
# Saved: 2008-04-01 23:05:01
# 
# Connection: root@localhost:3306
# Host: localhost
# Saved: 2008-04-01 23:01:10
# 


USE Prestiti;

CREATE TABLE  Genere  (
      Id_Genere  INTEGER UNSIGNED PRIMARY KEY,
      Descrizione   VARCHAR(80) NOT NULL  );

CREATE TABLE  Tipo  (
      Id_Tipo  INTEGER UNSIGNED PRIMARY KEY,
      Descrizione  VARCHAR(30) NOT NULL  );

CREATE TABLE Media  (
      Id_Media   INTEGER UNSIGNED PRIMARY KEY,
      Titolo  VARCHAR(100) NOT NULL,
       Copie  SMALLINT NOT NULL,
       Id_Tipo INTEGER UNSIGNED NOT NULL,
       Id_Genere INTEGER UNSIGNED NOT NULL,  
       FOREIGN KEY(Id_Tipo) REFERENCES Tipo(Id_Tipo),
       FOREIGN KEY(Id_Genere) REFERENCES Genere(Id_Genere)  );

CREATE TABLE Studenti  (
      Matricola   INTEGER UNSIGNED PRIMARY KEY,
      Nome    VARCHAR(40)  NOT NULL,
      Cognome  VARCHAR(40)  NOT NULL,
      Via  VARCHAR(60)  NOT NULL,
     Citta  VARCHAR(30) NOT NULL,
     Tel  CHAR(15)    );

CREATE TABLE  Prestiti  (
       Id_Prestito INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
       DataInizio DATE NOT NULL,
       DataTermine DATE,
       Id_Media  INTEGER UNSIGNED NOT NULL,
       Matricola  INTEGER UNSIGNED NOT NULL,
       FOREIGN KEY(Id_Media) REFERENCES Media(Id_Media),
       FOREIGN KEY(Matricola) REFERENCES Studenti(Matricola)   );