create database ateneo;



use ateneo;

create table studenti(
matr char(6) primary key,
cognome varchar(20) not null,
nome varchar(20) not null,
residenza varchar(30),
CDS char(6) ) ;

create table corsi(
codice char(4) primary key,
denominazione varchar(30) not null,
docente varchar(30),
CDS char(6) not null ) ;

create table esami(
matr char(6),
codice char(4),
data date not null,
voto smallint not null,
lode char,
primary key (matr, codice),
foreign key (matr) references studenti(matr)
  on delete cascade on update cascade,
foreign key (codice) references corsi(codice)
  on delete cascade on update cascade ) ;

insert into studenti
values ('111111', 'Rossi', 'Marco', 'Bologna', 'LS_G') ;
insert into studenti
values ('222222', 'Bianchi', 'Anna', 'Modena', 'L_PG') ;
insert into studenti
values ('333333', 'Neri', 'Luca', 'Bologna', 'L_G') ;
insert into studenti
values ('444444', 'Verdi', 'Paola', 'Ferrara', 'L_I') ;
insert into studenti
values ('555555', 'Violi', 'Vito', 'Roma', 'L_PG') ;
insert into studenti
values ('666666', 'Bruni', 'Maria', 'Ravenna', 'L_G') ;
insert into studenti
values ('777777', 'Rosi', 'Enzo', 'Modena', 'LS_G');

insert into corsi
values ('0001', 'Basi di Dati L', 'Grandi F.', 'L_PG') ;
insert into corsi
values ('0002', 'Sistemi Informativi L', 'Ciaccia', 'L_I') ;
insert into corsi
values ('0003', 'Sistemi Informativi L', 'Grandi F.', 'L_PG') ;
insert into corsi
values ('0004', 'Fondamenti di Informatica LB', 'Patella', 'L_G' ) ;

insert into esami
values ('222222', '0001', '2006-07-20', 30, 'X' ) ;
insert into esami
values ('222222', '0003', '2007-06-25', 28, NULL ) ;
insert into esami
values ('555555', '0001', '2007-01-17', 30, NULL ) ;
insert into esami
values ('777777', '0003', '2007-09-21', 30, 'X' ) ;
insert into esami
values ('444444', '0002', '2008-02-14', 27, NULL ) ;
insert into esami
values ('111111', '0001', '2007-10-03', 22, NULL ) ;
insert into esami
values ('333333', '0004', '2007-12-14', 30, 'X' ) ;
insert into esami
values ('666666', '0004', '2007-07-17', 18, NULL ) ;
