BrCwR1Du

1DROP TABLE IF EXISTS Aluno;
2DROP TABLE IF EXISTS Prof;
3DROP TABLE IF EXISTS Cadeira;
4DROP TABLE IF EXISTS Prova;
5
6CREATE TABLE aluno
7(	nr	varchar2(5)	 not null,
8 	nome	varchar2(50) not null,
9 	CONSTRAINT aluno_pk PRIMARY KEY (nr)
10);
11
12INSERT INTO aluno VALUES ('100','Joao');
13INSERT INTO aluno VALUES ('110','Manuel');
14INSERT INTO aluno VALUES ('120','Rui');
15INSERT INTO aluno VALUES ('130','Abel');
16INSERT INTO aluno VALUES ('140','Fernando');
17INSERT INTO aluno VALUES ('150','Ismael');
18
19CREATE TABLE prof
20(	sigla	varchar2(5)	not null,
21 	nome	varchar2(50)	not null,
22 	CONSTRAINT prof_pk PRIMARY KEY (sigla)
23);
24
25INSERT INTO prof VALUES ('ECO','Eugénio');
26INSERT INTO prof VALUES ('FNF','Fernando');
27INSERT INTO prof VALUES ('JLS','João');
28
29CREATE TABLE cadeira
30(	cod	varchar2(5)	not null,
31 	design	varchar2(50)	not null,
32    curso	varchar2(10)	not null,
33    regente	varchar2(5)	REFERENCES prof(sigla),
34 	CONSTRAINT cadeira_pk PRIMARY KEY (cod)
35);
36
37INSERT INTO cadeira VALUES ('TS1','Teoria dos Sistemas 1','IS','FNF');
38INSERT INTO cadeira VALUES ('BD','Bases de Dados','IS','ECO');
39INSERT INTO cadeira VALUES ('EIA','Estruturas de Informação e Algoritmos','IS','ECO');
40INSERT INTO cadeira VALUES ('EP','Electrónica de Potência','AC','JLS');
41INSERT INTO cadeira VALUES ('IE','Instalações Eléctricas','AC','JLS');
42
43CREATE TABLE prova
44(	nr	varchar2(5)	REFERENCES aluno(nr),
45 	cod	varchar2(50) REFERENCES cadeira(cod),
46    data	date,
47    nota	number(2) check (nota>=0 AND nota<=20),
48 	CONSTRAINT prova_pk PRIMARY KEY (nr, cod, data)
49);
50
51INSERT INTO prova VALUES ('100','TS1','92-02-11',8);
52INSERT INTO prova VALUES ('100','TS1','93-02-02',11);
53INSERT INTO prova VALUES ('100','BD','93-02-04',17);
54INSERT INTO prova VALUES ('100','EIA','92-01-29',16);
55INSERT INTO prova VALUES ('100','EIA','93-02-02',13);
56INSERT INTO prova VALUES ('110','EP','92-01-30',12);
57INSERT INTO prova VALUES ('110','IE','92-02-05',10);
58INSERT INTO prova VALUES ('110','IE','93-02-01',14);
59INSERT INTO prova VALUES ('120','TS1','93-01-31',15);
60INSERT INTO prova VALUES ('120','EP','93-02-04',13);
61INSERT INTO prova VALUES ('130','BD','93-02-04',12);
62INSERT INTO prova VALUES ('130','EIA','93-02-02',7);
63INSERT INTO prova VALUES ('130','TS1','92-02-11',8);
64INSERT INTO prova VALUES ('140','TS1','93-01-31',10);
65INSERT INTO prova VALUES ('140','TS1','92-02-11',13);
66INSERT INTO prova VALUES ('140','EIA','93-02-02',11);
67INSERT INTO prova VALUES ('150','TS1','92-02-11',10);
68INSERT INTO prova VALUES ('150','EP','93-02-02',11);
69INSERT INTO prova VALUES ('150','BD','93-02-04',17);
70INSERT INTO prova VALUES ('150','EIA','92-01-29',16);
71INSERT INTO prova VALUES ('150','IE','93-02-02',13);