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
103
104
105
106
107
108
109
110
111
112
113
| begin;
set datestyle = dmy;
create table client (
ncli refcli constraint cpcli primary key,
nom varchar(15) not null,
adresse varchar(25) not null,
ville varchar(25) not null,
tel varchar(10) not null
);
insert into client (ncli,nom,adresse,ville,tel) values('A1','Mercier','12, r. de Paris','Tournai',069442345);
insert into client (ncli,nom,adresse,ville,tel) values('A2','Romain','167, av. des boulanger','Mouscron',069236751);
insert into client (ncli,nom,adresse,ville,tel) values('A3','Cardon','40, r. st piat','Tournai',069658909);
insert into client (ncli,nom,adresse,ville,tel) values('B13','Jacob','1, pl. verte','Vervier',0498289765);
insert into client (ncli,nom,adresse,ville,tel) values('B1','Henriette','83, r. neuve','Bruxelles',0478258635);
insert into client (ncli,nom,adresse,ville,tel) values('B5','Toussaint','43, bvd. Albert','Tournai',069234876);
insert into client (ncli,nom,adresse,ville,tel) values('B14','Romain','170, r. st piat','Tournai',069877963);
create table vehicule (
vtype varchar(15) not null,
nimma char(7) constraint cpim primary key,
ncli char(4) not null constraint ceclive references client,
datecirc date,
kilom decimal not null
);
insert into vehicule (vtype,nimma,ncli,datecirc,kilom) values('Clio','CXS-234','A1','6/7/1998',12000);
insert into vehicule (vtype,nimma,ncli,datecirc,kilom) values('Escort','AFX-123','B5','5/2/1990',150000);
insert into vehicule (vtype,nimma,ncli,datecirc,kilom) values('Civic','FJW-876','B13','13/12/2002',8000);
insert into vehicule (vtype,nimma,ncli,datecirc,kilom) values('Leon','XPT-001','A3','30/8/2000',15000);
insert into vehicule (vtype,nimma,ncli,datecirc,kilom) values('Audi A8','DCE-469','B14','7/4/2000',50000);
insert into vehicule (vtype,nimma,ncli,datecirc,kilom) values('Lada','CGP-133','A2','26/10/1983',200000);
insert into vehicule (vtype,nimma,ncli,datecirc,kilom) values('Evo VIII','RJW-652','B5','15/12/2001',100000);
create table mecanicien (
nmec refmec constraint cpmec primary key,
nom char(15) not null,
taux decimal(3,2) not null
);
insert into mecanicien (nmec,nom,taux) values ('100','Patrick',1.20);
insert into mecanicien (nmec,nom,taux) values ('101','Bernard',1.15);
insert into mecanicien (nmec,nom,taux) values ('102','Paul',1.10);
insert into mecanicien (nmec,nom,taux) values ('103','Benoit',1.20);
create table produit (
npro refpro constraint cppro primary key ,
libelle varchar(25) not null ,
fournisseur char(20) not null,
prix decimal(6,2) not null ,
qstock decimal(4) not null,
reapmax decimal(3) not null,
constraint chk_stock check(qstock <= reapmax)
);
insert into produit (npro,libelle,fournisseur,prix,qstock,reapmax) values('BG12','Bougie','Midas',9,6,20);
insert into produit (npro,libelle,fournisseur,prix,qstock,reapmax) values('PF18','Plaquette de frein','Vroum',17,4,12);
insert into produit (npro,libelle,fournisseur,prix,qstock,reapmax) values('PN04','Pneu 17"','Speede',26,8,20);
insert into produit (npro,libelle,fournisseur,prix,qstock,reapmax) values('PG16','Porte gauche','Carosserie jambert',133,1,2);
insert into produit (npro,libelle,fournisseur,prix,qstock,reapmax) values('AM09','Amortisseur','Monroe',50,2,5);
insert into produit (npro,libelle,fournisseur,prix,qstock,reapmax) values('FR34','Far avant','Midas',23,2,6);
insert into produit (npro,libelle,fournisseur,prix,qstock,reapmax) values('BM54','Bloc moteur','AvoMoteur',1238,1,2);
create table reparation (
nmec refmec not null constraint cemecrep references mecanicien,
cpim char(10) not null constraint cerepvehi references vehicule,
temprep decimal(5,0) not null,
trepa char(20) not null,
daterep date not null,
npro char(4) constraint cereppro references produit
);
insert into reparation (nmec,cpim,temprep,trepa,daterep,npro) values ('101','AFX-123',180,'Bloc moteur','6/7/2003','BM54');
insert into reparation (nmec,cpim,temprep,trepa,daterep,npro) values ('103','FJW-876',30,'Remplacement bougie','6/11/2007','BG12');
insert into reparation (nmec,cpim,temprep,trepa,daterep,npro) values ('100','XPT-001',60,'Pneu','18/8/2003','PN04');
insert into reparation (nmec,cpim,temprep,trepa,daterep,npro) values ('102','XPT-001',90,'Far avant','30/5/2003','FR34');
insert into reparation (nmec,cpim,temprep,trepa,daterep,npro) values ('100','CXS-234',60,'Pneu','13/8/2003','PN04');
insert into reparation (nmec,cpim,temprep,trepa,daterep,npro) values ('101','FJW-876',60,'Amortisseur','25/8/2003','AM09');
insert into reparation (nmec,cpim,temprep,trepa,daterep,npro) values ('102','AFX-123',90,'Plaquette de frain','14/11/2007','PF18');
create table facture (
nfact varchar(10) constraint cpfact primary key,
datefact date not null,
ncli refcli not null constraint cefactcli references client,
nimma char(7) not null constraint cefactvehi references vehicule,
npro refpro not null constraint cefactpro references produit,
pripro decimal(6,2) not null,
qtt decimal(3) not null,
totpripro decimal(6,2) not null,
prihor decimal(6,2) not null,
nbrheurmin decimal(6) not null,
primain decimal(6,2) not null,
totfact decimal(6,2) not null
);
create table rdv (
daterdv date not null,
ncli refcli not null constraint cerdvcli references client,
motifrep varchar(12) constraint chk_motif check(motifrep = 'revision' or motifrep='reparation'),
naturtravau varchar(100) not null
);
insert into rdv (daterdv,ncli,motifrep,naturtravau) values ('18/8/2003','A3','reparation','Pneu');
insert into rdv (daterdv,ncli,motifrep,naturtravau) values ('30/5/2003','A3','reparation','Far avant');
insert into rdv (daterdv,ncli,motifrep,naturtravau) values ('10/01/2008','B14','revision','control technique');
insert into rdv (daterdv,ncli,motifrep,naturtravau) values ('19/01/2008','A2','reparation','amortisseur');
insert into rdv (daterdv,ncli,motifrep,naturtravau) values ('29/02/2008','B5','reparation','bloc moteur');
commit; |
