drop table Matrícula;
drop table Docencia;
drop table Examens;
drop table Assignatures;
drop table Professor;
drop table Alumnes;

Create table Assignatures(
        CodiAssignatura char(5) PRIMARY KEY,
        Nom varchar(30),
        Durada int,
        Cicle varchar(5),
        Curs smallint
);

Create table Professor(
        CodiProfessor varchar(5) PRIMARY KEY,
        Nom varchar(15),
        Especialitat varchar(15),
        Departament varchar(15),
        Antiguetat varchar(15)
);

Create table Alumnes(
        CodiAlumne varchar(5) PRIMARY KEY,
        Alumne varchar(40),
        DNI varchar(10),
        Telefon varchar(12),
        Email varchar(30),
        Direcció varchar(50)
);

ALTER TABLE Alumnes
ADD CHECK (Email like '*@*');

Create table Matrícula(
        Curs smallint,
        CodiAssignatura varchar(5),
        CodiAlumne varchar(5),
        Nota decimal,
        Aprovada boolean,
        Convalidada char,
        NotaFinal decimal,
        UNIQUE(Curs, CodiAssignatura,CodiAlumne)
);

Alter Table Matrícula
rename column Nota Notes

ALTER TABLE Matrícula
modify Notes decimal[];

ALTER TABLE Matrícula
ADD CHECK (Aprovada=true)


create table Docencia(
        Curs smallint,
        CodiAssignatura varchar(5),
        Grup char,
        CodiProfessor varchar(5),
        Aula varchar(10)
);

create table Examens(
        Curs smallint,
        CodiAssignatura varchar(5),
        Grup char,
        DataExamen date,
        CodiAlumne varchar(5),
        Trimestre char,
        UnitatFormativa varchar(5),
        Nota decimal
);

ALTER TABLE Examens
ADD CHECK (Trimestre in (1,2,3))


--UNIQUES
alter table Matrícula
add UNIQUE(Curs, CodiAssignatura,CodiAlumne);

alter table Docencia
add UNIQUE(Curs,CodiAssignatura,Grup);

alter Table Examens
add UNIQUE (Curs,CodiAssignatura,Grup,DataExamen,CodiAlumne);

--foreings de Assignatures
alter table Examens
add FOREIGN KEY (CodiAssignatura) REFERENCES Assignatures (CodiAssignatura);

alter table Docencia
add FOREIGN KEY (CodiAssignatura) REFERENCES Assignatures (CodiAssignatura);

--alter table Matrícula
add FOREIGN KEY (CodiAssignatura) REFERENCES Assignatures (CodiAssignatura);
--fin

--foreign de Professor
alter table Docencia
add FOREIGN KEY (CodiProfessor) REFERENCES Professor (CodiProfessor);

--Foreign de Alumnes
alter table Matrícula
add FOREIGN KEY (CodiAlumne) REFERENCES Alumnes (CodiAlumne);

--foreign de Docencia
alter table Matrícula
add FOREIGN KEY (CodiAssignatura) REFERENCES Docencia (CodiAssignatura),
add FOREIGN KEY (Curs) REFERENCES Docencia (Curs),

--foreign de Matrícula
alter table Examens
add FOREIGN KEY (CodiAssignatura) REFERENCES Matrícula (CodiAssignatura),
add FOREIGN KEY (Curs) REFERENCES Matrícula (Curs),
add FOREIGN KEY (CodiAlumne) REFERENCES Matrícula (CodiAlumne);




--INSERT INTO Matrícula (CodiAlumne, notes) VALUES ('asdf123', ARRAY[5,6,0])