X6WxKmnp

1CREATE DATABASE IF NOT EXISTS Exercici2;
2USE Exercici2;
3CREATE TABLE IF NOT EXISTS Projecte(
4    ID_projecte INTEGER NOT NULL AUTO_INCREMENT,
5    nom VARCHAR(20) NOT NULL,
6    durada TIME NOT NULL,
7    data_inici DATE NOT NULL,
8    CONSTRAINT pk_projecte PRIMARY KEY(ID_projecte)
9    );
10CREATE TABLE IF NOT EXISTS Empleat(
11	ID_empleat INTEGER(20) NOT NULL AUTO_INCREMENT,
12    nom VARCHAR(20) NOT NULL,
13    cognom1 VARCHAR(20) NOT NULL,
14    adreca VARCHAR(20) NOT NULL,
15    num_mobil INTEGER NOT NULL,
16    ID_projecte INTEGER NOT NULL,
17    data_ini DATE NOT NULL,
18    CONSTRAINT pk_empleat PRIMARY KEY(ID_empleat),
19    CONSTRAINT fk_empleat FOREIGN KEY(ID_projecte) REFERENCES Projecte(ID_projecte)
20    
21);
22CREATE TABLE IF NOT EXISTS Recurs(
23    ID_recurs INTEGER NOT NULL AUTO_INCREMENT,
24    nom VARCHAR(20),
25    descripcio VARCHAR(100),
26    CONSTRAINT pk_recurs PRIMARY KEY(ID_recurs)
27    );
28CREATE TABLE IF NOT EXISTS Consumeix(
29	ID_projecte INTEGER NOT NULL,
30    ID_recurs INTEGER NOT NULL,
31    CONSTRAINT pk_consumeix PRIMARY KEY(ID_projecte, ID_recurs),
32    CONSTRAINT fk_projecte FOREIGN KEY(ID_projecte) REFERENCES Projecte(ID_projecte),
33    CONSTRAINT fk_recurs FOREIGN KEY(ID_recurs) REFERENCES Recurs(ID_recurs)
34);
35CREATE TABLE IF NOT EXISTS Supervisa(
36	ID_supervisor INTEGER NOT NULL,
37    ID_supervisat INTEGER NOT NULL,
38   	CONSTRAINT pk_supervisa PRIMARY KEY(ID_supervisor, ID_supervisat),
39    CONSTRAINT fk_supervisor FOREIGN KEY(ID_supervisor) REFERENCES Empleat(ID_empleat),
40    CONSTRAINT fk_supervisat FOREIGN KEY(ID_supervisat) REFERENCES Empleat(ID_empleat)
41);