Untitled

DROP TABLE Prova cascade constraints;
DROP TABLE Cadeira cascade constraints;
DROP TABLE Prof cascade constraints;
DROP TABLE Aluno cascade constraints;

CREATE TABLE Aluno(
num INTEGER NOT NULL,
nome varchar2(64) NOT NULL,
constraints aluno_pk PRIMARY KEY (num));

INSERT INTO Aluno VALUES(100, 'João');
INSERT INTO Aluno VALUES(110, 'Manuel');
INSERT INTO Aluno VALUES(120, 'Rui');
INSERT INTO Aluno VALUES(130, 'Abel');
INSERT INTO Aluno VALUES(140, 'Fernando');
INSERT INTO Aluno VALUES(150, 'Ismael');


CREATE TABLE Prof(
sigla varchar2(8) NOT NULL,
nome varchar2(64) NOT NULL,
constraints Prof_pk PRIMARY KEY (sigla));

INSERT INTO Prof VALUES('ECO', 'Eugénio');
INSERT INTO Prof VALUES('FNS', 'Fernando');
INSERT INTO Prof VALUES('JLS', 'João');

CREATE TABLE Cadeira(
cod varchar2(8) NOT NULL,
design varchar2(128) NOT NULL,
curso varchar2(4) NOT NULL,
regente varchar2(4) REFERENCES Prof(sigla),
constraints cadeira_pk PRIMARY KEY(cod));

INSERT INTO Cadeira VALUES('TS1', 'Teoria dos Sistemas I', 'IS', 'FNS');
INSERT INTO Cadeira VALUES('BD', 'Bases de Dados', 'IS', 'ECO');
INSERT INTO Cadeira VALUES('EIA', 'Estruturas de Informação e Algoritmos', 'IS', 'ECO');
INSERT INTO Cadeira VALUES('EP', 'Electrónica de Potência', 'AC', 'JLS');
INSERT INTO Cadeira VALUES('IE', 'Instalações Eléctricas', 'AC', 'JLS');

CREATE TABLE Prova(
num INTEGER REFERENCES Aluno(num),
cod varchar2(8) REFERENCES Cadeira(cod),
DATA DATE,
nota NUMBER(2) CHECK (nota>=0 AND nota<=20),
constraints prova_pk PRIMARY KEY(num, cod, DATA));

INSERT INTO Prova VALUES(100, 'TS1','92-02-11', 8);
INSERT INTO Prova VALUES(100, 'TS1','93-02-02', 11);
INSERT INTO Prova VALUES(100, 'BD','93-02-04', 17);
INSERT INTO Prova VALUES(100, 'EIA','92-01-29', 16);
INSERT INTO Prova VALUES(100, 'EIA','93-02-02', 13);
INSERT INTO Prova VALUES(110, 'EP','92-01-30', 12);
INSERT INTO Prova VALUES(110, 'IE','92-02-01', 10);
INSERT INTO prova VALUES (110,'IE','93-02-01',14);
INSERT INTO prova VALUES (120,'TS1','93-01-31',15);
INSERT INTO prova VALUES (120,'EP','93-02-04',13);
INSERT INTO prova VALUES (130,'BD','93-02-04',12);
INSERT INTO prova VALUES (130,'EIA','93-02-02',7);
INSERT INTO prova VALUES (130,'TS1','92-02-11',8);
INSERT INTO prova VALUES (140,'TS1','93-01-31',10);
INSERT INTO prova VALUES (140,'TS1','92-02-11',13);
INSERT INTO prova VALUES (140,'EIA','93-02-02',11);
INSERT INTO prova VALUES (150,'TS1','92-02-11',10);
INSERT INTO prova VALUES (150,'EP','93-02-02',11);
INSERT INTO prova VALUES (150,'BD','93-02-04',17);
INSERT INTO prova VALUES (150,'EIA','92-01-29',16);
INSERT INTO Prova VALUES (150, 'IE','93-02-02', 13);