Voici le
schéma relationnel de la base AcciRoute pour représentater les
rapports d’accidents de la route. Le S.R de chaque relation est enrichi avec
un type de l’attribut, afin de vous permettre de formuler adéquatement les
requétes SQL
Personne (NAS :
char(9), nom : varchar(35), VilleP : Varchar(50))
Voiture (Imma :
Char(6), modele : varchar(20), annee : char(4), nas : char(9))
Accident (DateAc : Date, NAS :
char(9), dommage : numeric(7 :2), villeAc : varchar(50), imma :
char(6) )
Note :
- Les types des attributs représentent les
domaines syntaxiques.
- Une personne est propriétaire d’une ou
plusieurs voitures.
- Une personne conduit q’une voiture dont elle
est propriétaire.
- Il peut y avoir des homonymes dans la base
différentiés par leur NAS respectif.
Questions :
- Créer la base de données AcciRoute.
- Créer la procédure CreateAcciRoute qui permet de
construire les tables de données AcciRoute en les suppriman
- t s’ils existent avant leur création.
- Créer la procédure InsertAcciRoute qui permet
d’insérer les données dans AcciRoute
- en vérifiant
l’intégrité référentielle.
- Créer la procédure GetnumProp qui permet de calculer
le nombre de propriétaires impliqués
- dans un accident entre deux années données.
- Créer la procédure GetProp qui donne le nom et le nas
des propriétaires qui ont fait deux accidents
- dans un
intervalle de 4 mois.
- Créer la procédure GetDamCity qui calcule le total des
dommages d’une ville donnée et affiche
- « catégorie1 » pour dommage<=5000 et
« catégorie2 » pour dommage entre 5000 et 10000
- et « catégorie3 » pour dommage >10000.
- Créer la procédure GetnumAcci qui permet d’afficher
pour chaque ville le nombre total d’accidents enregistrés.
- Créer la procédure GetNamProp qui permet d’afficher le
nom des propriétaires qui résident
- dans une ville
où il y a eu plus de x accidents tel que x un paramètre de la procédure.
- Créer la procédure GetnumAcciDat qui calcule le nombre
d’accidents qui sont survenus à une date donnée.
- Créer la procédure GetnumAcciHour qui calcule le
nombre d’accidents survenus entre deux heures données.
- Créer la procédure UpdateDam qui permet de diminuer de
5% le dommage à chaque véhicule
- dont les dommages dépassant les 5000.00.
Solution ;
-- 1)
use master
go
create database
M21N3Ex1--AcciRoute
go
-- 2)
use M21N3Ex1
go
alter Proc AcciRoute
as
begin
if exists(select * from
sys.tables where name like 'Personne')
drop table Personne
create table Personne (NAS
char(9)primary key, nom varchar(35), VilleP Varchar(50))
if exists(select * from
sys.tables where name like 'Voiture')
drop table Voiture
create table Voiture (Imma
Char(6)primary key, modele varchar(20), annee smalldatetime, nas char(9))
if exists(select * from
sys.tables where name like 'Accident')
drop table Accident
create table Accident
(DateAc smallDatetime, NAS char(9)foreign key references Personne(Nas),
dommage numeric(7,2),
villeAc varchar(50),imma char(6)foreign key references Voiture(imma))
end
Go
Exec AcciRoute
go
-- 3)
create proc inserte(@dateAc
datetime,@NAS char,@dommage numeric,@villeAc varchar,@imma char)
as
begin
BEGIN try
if not
exists(select * from Personne where @NAS=NAS)
RAISERROR('NAS introuvable ',15,1)
else if not
exists(select * from Voiture where @Imma=Imma)
RAISERROR('IMMA introuvale ',15,1)
else --if not
exists(select * from Accident)
insert
into Accident values(@dateAc,@NAS,@dommage,@villeAc,@imma)
end try
begin catch
select
ERROR_MESSAGE() AS ErrorMessage
end catch
end
Exec inserte '12/06/1993
05:30','xxx',30,'rabat','yyy'
Exec inserte
'xxx','fandi','casa'
Exec inserte
'yyy','uno','12/05/1990','qqq'
Exec inserte
'12/06/1993','xxx',30,'rabat','yyy'
go
--- 4)
alter proc GetnumProp(@A1
int,@A2 int)
as
begin
select COUNT(distinct
NAS) from Accident where YEAR(DateAc) between @A1 and @A2
end
Go
exec GetnumProp 1990,1998
go
--- 5)
create proc GetProp
as
begin
select P.nom,P.NAS from
Personne P,Accident A1,Accident A2
where A1.NAS=A2.NAS and
A1.NAS=P.NAS and A1.NAS>=A2.NAS and ABS(DATEDIFF(month ,A1.DateAc,A2.DateAc))>4
end
exec GetProp
go
--- 6)
alter proc GetDamCity @ville
varchar(12) as
begin
select sum(dommage) as
'total' ,case when sum(dommage)<=5000 then 'catégorie1'
when (sum(dommage)between
5000 AND 10000) then 'catégorie2'
else 'catégorie3'
end
from Accident where
@ville=villeAc
end
exec GetDamCity
go
---- autre méthode
alter proc GetDamCiti @ville
varchar(12) as
begin
declare @total numeric(6)
select @total=sum(dommage)
from Accident where @ville=villeAc
if @total<5000
begin
print 'catégorie1'
end
if @total between 5000 AND
10000
begin
print 'catégorie2'
end
if @total>10000
begin
print 'catégorie3'
end
print @total
end
exec GetDamCiti
go
---Q6
create proc GetnumAcci as
begin
select villeAc, COUNT(*)as
'nombre total' from Accident group by villeAc
end
exec GetnumAcci
go
-----Q7
alter proc GetNamProp @X int
as
begin
select P.nom,COUNT(*)as
'nombre accident' from Personne P,Accident A
where P.VilleP=A.villeAc
group by P.nom,A.villeAc
having count(*)>=@X
end
exec GetNamProp 1
go
-----Q8
create proc GetnumAcciDat
@DateAc datetime as
begin
select COUNT(*)as'nbr
d"accident' from Accident where DateAc=@DateAc
end
exec GetnumAcciDat
'01/01/2010'
go
----Q9
create proc GetnumAcciHour
@h1 datetime,@h2 datetime as
begin
select count(*)as 'nbr
d"accident' from Accident A where DATEPART(hour,A.DateAc) between
DATEPART(hour,@h1)and DATEPART(hour,@h2)
end
exec GetnumAcciHour
'01:00:00','02:10:00'
go
------Q10
alter proc UpdateDam as
begin
update Accident set
dommage=dommage-(dommage*0.05) where dommage>=5000
end
exec UpdateDam
|
Exercice HTML JavaScript