Bases de Données / Databases

Ceci est une ancienne révision du document !

---

SET LINESIZE 1000

COLUMN nom FORMAT A15

drop table Observation; drop table TourneAutour ; drop table Astre; drop table Categorie ;

create table Categorie (

idC Number(10) primary key,
nom Varchar2(15) not null

);

insert into Categorie values (10,'étoile'); insert into Categorie values (11,'planète'); insert into Categorie values (12,'satellite');

– objet celeste create table Astre (

idA Number(10) primary key,
nom Varchar2(15) not null,
rayon Number(10) not null,
idC Number(10) not null references Categorie(idC)

); – rayon en km insert into Astre values (100,'Soleil',696342, (select idC from Categorie where nom='étoile')); insert into Astre values (101,'Terre', 6371, (select idC from Categorie where nom='planète')); insert into Astre values (102,'Lune', 1737, (select idC from Categorie where nom='satellite')); insert into Astre values (103,'Mars', 3390, (select idC from Categorie where nom='planète'));

create table TourneAutour (

idA1 Number(10) primary key references Astre (idA),
idA2 Number(10)  not null references Astre (idA),
position Number(3) not null

);

insert into TourneAutour values (101,100,3); insert into TourneAutour values (102,101,1); insert into TourneAutour values (103,100,4);

create table Observation (

idO Number(10),
idA Number(10) not null,
dateObs Date,
valObs Number(5),
constraint pk primary key(idO),
constraint fk foreign key(idA) references Astre (idA),
constraint ck check ( valObs between 100 and 20000)

);

– Solution H2 yyyy-MM-dd insert into Observation values (90,100,'2010-05-10',12001); insert into Observation values (91,100,'2010-05-10',12003); insert into Observation values (92,101,'2013-12-18',8005); insert into Observation values (93,102,'2014-08-27',3007);