1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
| create table TECHNICIEN (
TechnicienId numeric(5) not null,
TechnicienNom varchar(64) not null,
TecnicienPrenom varchar(64) not null,
constraint Technicien_PK primary key (TechnicienId));
create table ESPECE (
EspeceId numeric(5) not null,
EspeceNom varchar(64) not null,
constraint Espece_PK primary key (EspeceId));
create table DEPARTEMENT (
DepartementId numeric(3) not null,
DepartementNo char(3) not null,
DepartementNom varchar(64) not null,
constraint Departement_PK primary key (DepartementId));
constraint Departement_No unique (DepartementNo),
create table TRANSECT (
TransectId numeric(5) not null,
TransectNom varchar(64) not null,
DepartementId numeric(3) not null,
constraint Transect_PK primary key (TransectId));
create table POINT (
TransectId numeric(5) not null,
PointId numeric(5) not null,
Coordonnees char(32) not null,
constraint Point_PK primary key (TransectId, PointId));
create table PASSAGE (
TransectId numeric(5) not null,
PointId numeric(5) not null,
PassageId numeric(5) not null,
PassageDate date not null,
PassageHeure numeric(5) not null,
TechnicienId numeric(5) not null,
constraint Passage_PK_ID primary key (TransectId, PointId, PassageId));
create table COMPTAGE (
TransectId numeric(5) not null,
PointId numeric(5) not null,
PassageId numeric(5) not null,
EspeceId numeric(5) not null,
NbTiges numeric(5) not null,
NbGraines char(1) not null,
constraint Comptage_PK primary key (TransectId, PointId, PassageId, EspeceId));
alter table TRANSECT add constraint Transect_Departement_FK
foreign key (DepartementId)
references DEPARTEMENT;
alter table POINT add constraint Point_Transect_FK
foreign key (TransectId)
references TRANSECT;
alter table PASSAGE add constraint Passage_Point_PK
foreign key (TransectId, PointId)
references POINT;
alter table PASSAGE add constraint Passage_Technicien_FK
foreign key (TechnicienId)
references TECHNICIEN;
alter table COMPTAGE add constraint Comptage_Passage_FK
foreign key (TransectId, PointId, PassageId)
references PASSAGE;
alter table COMPTAGE add constraint Comptage_Espece_FK
foreign key (EspeceId)
references ESPECE; |
Partager