· 6 years ago · Nov 22, 2019, 08:42 AM
1DROP TABLE IF EXISTS Inscrire, Adherents, Chevaux, Villes, Stages, Categories cascade;
2drop table if exists adherents, catégories;
3
4create table Categories (
5 Code_categorie varchar(2) primary key,
6 Libelle_categorie text not null,
7 Montant_cotisation real not null check ( Montant_cotisation>0 ));
8
9create table Stages (
10 Num_stage serial primary key,
11 Du date not null,
12 Au date not null,
13 Prix_forfaitaire real not null check ( Prix_forfaitaire>0 ));
14
15create table Villes (
16 Cp int primary key,
17 Ville text not null);
18
19create table Chevaux (
20 Num_C serial primary key,
21 Nom_C text not null,
22 Sexe_C varchar(1) check ( Sexe_C='M' or Sexe_C='F' ),
23 Encolure numeric(3,0),
24 DN_C date,
25 Niv_C numeric(1,0));
26
27create table Adherents (
28 Num_adherent numeric(4,0) primary key,
29 Nom_adherent text not null,
30 Prenom text not null,
31 Sexe varchar(1) check ( Sexe='M' or Sexe='F' ),
32 CP int references Villes,
33 Date_nais date not null,
34 Echeance_cot date not null,
35 niveau numeric(2,0),
36 Cat varchar(2),
37 foreign key (Cat) references Categories (Code_categorie));
38
39create table Inscrire (
40 Num_adherent numeric(4,0),
41 Num_stage int,
42 Num_cheval serial,
43 primary key (Num_adherent, Num_cheval, Num_stage),
44 foreign key (Num_adherent) references Adherents (Num_adherent),
45 foreign key (Num_stage) references Stages (Num_stage),
46 foreign key (Num_cheval) references Chevaux (Num_C));
47
48insert into Categories
49values ('AA','Adulte à l Année',900.00),
50 ('EA','Enfant à l année',800.00),
51 ('AT','Adulte au Trimestre',250.00),
52 ('ET','Enfant au Trimestre',250.00);
53
54insert into Stages
55values (10,'2013-09-13','2013-09-14',50.00),
56 (15,'2013-12-14','2013-12-19',300.00);
57
58insert into Villes
59values (13001,'Marseille'),
60 (13002,'Marseille'),
61 (13500,'Martigues'),
62 (13700,'Mariganne');
63
64insert into Chevaux
65values (1,'Doumé','M',140,'2011-05-15',3),
66 (2,'Camomille','F',169,'2003-02-26',3),
67 (11,'Roméo','M',158,'2012-04-18',1),
68 (13,'Juliette','F',145,'2008-11-02',2);
69
70insert into Adherents
71values (0165,'MARTIN','Claude','M',13500,'1967-07-13','2014-05-29',04,'AA'),
72 (0166,'DUPOND','Louise','F',13002,'1974-11-25','2014-08-29',03,'AT'),
73 (0167,'FAVRE','Olivier','M',13001,'2000-06-06','2013-09-30',01,'ET'),
74 (0168,'FAVRE','Julie','F',13001,'2000-06-06','2013-09-30',01,'ET');
75
76insert into Inscrire
77values (0165,10,13),
78 (0166,15,01),
79 (0165,15,11),
80 (0167,10,13);