# Connection: root@localhost:3306
# Host: localhost
# Saved: 2008-04-29 15:02:41
# 
# Connection: root@localhost:3306
# Host: localhost
# Saved: 2008-04-29 13:17:04
# 

USE multiproprieta;


CREATE TABLE Localita  (
     Id_Localita INTEGER UNSIGNED PRIMARY KEY,
     Nome_Localita VARCHAR(50) NOT NULL  );

CREATE TABLE Clienti  (
     Id_Cliente  INTEGER UNSIGNED PRIMARY KEY,
     Nome  VARCHAR(40) NOT NULL,
     Cognome VARCHAR(40)  NOT NULL  );

CREATE TABLE Periodi  (
     Id_Periodo INTEGER UNSIGNED PRIMARY KEY,
     Periodo ENUM('inverno', 'primavera','estate','autunno')  );

CREATE TABLE Prezzi  (
     Id_Prezzo INTEGER UNSIGNED PRIMARY KEY,
     Coefficiente_Prezzo FLOAT NOT NULL,
     Id_Localita INTEGER UNSIGNED NOT NULL,
     Id_Periodo   INTEGER UNSIGNED NOT NULL,
     FOREIGN KEY (Id_Localita) REFERENCES Localita(Id_Localita),
     FOREIGN KEY (Id_Periodo) REFERENCES Periodi(Id_Periodo)  );


CREATE TABLE Insediamenti (
     Id_Insediamento  INTEGER UNSIGNED PRIMARY KEY,
     Id_Localita  INTEGER UNSIGNED NOT NULL,
     FOREIGN KEY (Id_Localita) REFERENCES Localita(Id_Localita)  );

CREATE TABLE Immobili  (
     Id_Immobile  INTEGER UNSIGNED PRIMARY KEY,
     Metri_quadrati SMALLINT NOT NULL,
     Tipologia ENUM('lusso','medio','spartano'),
     Id_Insediamento INTEGER UNSIGNED NOT NULL,
     FOREIGN KEY (Id_Insediamento) REFERENCES Insediamenti(Id_Insediamento)  );

CREATE TABLE Settimane (
     Id_Settimana  INTEGER UNSIGNED PRIMARY KEY,
     Id_Periodo  INTEGER UNSIGNED NOT NULL,
     FOREIGN KEY (Id_Periodo) REFERENCES Periodi(Id_Periodo)  );

CREATE TABLE Proprieta  (
     Id_Proprieta  INTEGER UNSIGNED PRIMARY KEY,
     Prezzo_Vendita DECIMAL(8,2),
     Prezzo_Affitto DECIMAL(8,2),
     Id_Immobile  INTEGER UNSIGNED NOT NULL,     
     Id_Cliente_Proprieta  INTEGER UNSIGNED NOT NULL,     
     Id_Cliente_Affitto INTEGER UNSIGNED,
     FOREIGN KEY (Id_Immobile) REFERENCES Immobili(Id_Immobile),
     FOREIGN KEY (Id_Cliente_Proprieta) REFERENCES Clienti(Id_Cliente),
     FOREIGN KEY (Id_Cliente_Affitto) REFERENCES Clienti(Id_Cliente)  );

CREATE TABLE Scambi  (
      Id_Scambi  INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
      Id_Proprieta1  INTEGER UNSIGNED NOT NULL,
      Id_Proprieta2  INTEGER UNSIGNED NOT NULL,
     FOREIGN KEY (Id_Proprieta1) REFERENCES Proprieta(Id_Proprieta),      
     FOREIGN KEY (Id_Proprieta2) REFERENCES Proprieta(Id_Proprieta)  );

CREATE TABLE Settimane_Proprieta  (
       Id_Settimane  INTEGER UNSIGNED NOT NULL,
       Id_Proprieta   INTEGER UNSIGNED NOT NULL,
       PRIMARY KEY(Id_Settimane, Id_Proprieta)   );



#  DROP DATABASE multiproprieta;