UyqjAWNV

1CREATE DATABASE IF NOT EXISTS exercici2;
2USE exercici2;
3CREATE TABLE IF NOT EXISTS Recurs(
4id_recurs INTEGER NOT NULL AUTO_INCREMENT,
5nom VARCHAR(20),
6descripcio VARCHAR(100),
7CONSTRAINT pk_recurs PRIMARY KEY (id_recurs)
8);
9CREATE TABLE IF NOT EXISTS Consumeix(
10id_recurs INTEGER NOT NULL,
11id_projecte INTEGER NOT NULL,
12CONSTRAINT pk_consumeix PRIMARY KEY (id_recurs,id_projecte)
13);
14CREATE TABLE IF NOT EXISTS Projecte(
15id_projecte INTEGER NOT NULL AUTO_INCREMENT,
16nom VARCHAR(20),
17durada INTEGER,
18data_inici DATE,
19CONSTRAINT pk_projecte PRIMARY KEY (id_projecte)
20);
21CREATE TABLE IF NOT EXISTS Empleat(
22id_empleat INTEGER NOT NULL AUTO_INCREMENT,
23nom VARCHAR(20),
24cognom1 VARCHAR(20),
25adreça VARCHAR(50),
26num_mobil INTEGER(9),
27id_projecte INTEGER,
28data_ini DATE,
29CONSTRAINT pk_empleat PRIMARY KEY (id_empleat)
30);
31CREATE TABLE IF NOT EXISTS Supervisa(
32id_supervisor INTEGER NOT NULL,
33id_supervisat INTEGER NOT NULL,
34CONSTRAINT pk_supervisa PRIMARY KEY (id_supervisor, id_supervisat)
35);
36ALTER TABLE Consumeix ADD CONSTRAINT fk_consumeix_recurs FOREIGN KEY (id_recurs)
37REFERENCES Recurs(id_recurs);
38ALTER TABLE Consumeix ADD CONSTRAINT fk_consumeix_projecte
39FOREIGN KEY (id_projecte) REFERENCES Projecte(id_projecte);
40ALTER TABLE Empleat ADD CONSTRAINT fk_empleat_projecte FOREIGN KEY (id_projecte)
41REFERENCES Projecte(id_projecte);
42ALTER TABLE Supervisa ADD CONSTRAINT fk_supervisor FOREIGN KEY (id_supervisor)
43REFERENCES Empleat(id_empleat);
44ALTER TABLE Supervisa ADD CONSTRAINT fk_supervisat FOREIGN KEY (id_supervisat)
45REFERENCES Empleat(id_empleat);