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 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
| Create table Adherent (
numadh numeric(4) NOT NULL,
nom varchar(10) NOT NULL,
prenom varchar(10) NOT NULL,
adresse varchar(40),
telephone varchar(10),
skipper char (3) NOT NULL,
fonction varchar (15),
anneeadh numeric(4) DEFAULT extract(year from now()) ,
check (anneeadh between 1901 and 2100),
check (skipper = 'Oui' or skipper = 'Non'),
check (fonction = 'président' or fonction = 'vice-président' or fonction = 'trésorier' or fonction = 'secrétaire' or fonction = 'membre actif' or fonction = 'autre'),
primary key (numadh));
Create table Activite (
numact numeric(4) NOT NULL,
typeact varchar(6) DEFAULT 'sortie',
depart varchar(10),
arrivee varchar(10),
datedebut date,
datefin date,
check (datefin >= datedebut),
check (typeact = 'rallye' or typeact = 'sortie'),
primary key (numact));
Create table Bateau (
numbat numeric(4) NOT NULL,
nom varchar(20),
taille numeric(4,2),
typebat varchar(20),
nbplaces numeric(2) NOT NULL,
check (taille > 0),
check (nbplaces > 5),
primary key (numbat));
Create table Agence (
nomagence varchar(10) NOT NULL,
adresse varchar(40),
telephone varchar(10),
fax varchar(10),
primary key (nomagence));
Create table Regate (
numregate numeric(2) NOT NULL,
numact numeric(4) NOT NULL,
forcevent numeric(2),
primary key (numregate, numact),
foreign key (numact) references Activite(numact));
Create table Cotisation (
numadh numeric(4) NOT NULL,
anneecot numeric(4),
montantpaye real,
paye char(3),
primary key (numadh , anneecot),
foreign key (numadh) references Adherent(numadh));
Create table Fournit (
numbat numeric(4) NOT NULL,
nomagence varchar(20) NOT NULL,
primary key (numbat , nomagence),
foreign key (numbat) references Bateau(numbat),
foreign key (nomagence) references Agence(nomagence));
Create table Proprietaire (
numadh numeric(4) NOT NULL,
numbat numeric(4) NOT NULL,
primary key (numadh),
foreign key (numadh) references Cotisation(numadh),
foreign key (numbat) references Fournit(numbat));
Create table ChefDeBord (
numchef numeric(4) NOT NULL,
numadh numeric(4) NOT NULL,
numact numeric(4) NOT NULL,
numbat numeric(4) NOT NULL,
primary key (numchef, numadh),
foreign key (numbat) references Proprietaire(numbat),
foreign key (numact) references Regate(numact),
foreign key (numadh) references Proprietaire(numadh));
Create table Equipage (
numadh numeric(4) NOT NULL,
numact numeric(4) NOT NULL,
numchef numeric(4) NOT NULL,
numbat numeric(4) NOT NULL,
primary key (numadh , numact),
foreign key (numadh) references ChefDeBord(numadh),
foreign key (numact) references ChefDeBord(numact),
foreign key (numchef) references ChefDeBord(numchef),
foreign key (numbat) references ChefDeBord(numbat));
Create table Resultat (
numbat numeric(4) NOT NULL,
numact numeric(4) NOT NULL,
numregate numeric(2) NOT NULL,
classement numeric(2) NOT NULL,
points numeric(4) NOT NULL,
primary key (numbat , numregate , classement),
foreign key (numbat) references Equipage(numbat),
foreign key (numact) references Equipage(numact),
foreign key (numregate) references Regate(numregate)); |
Partager