Embedded Files
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 supprimant 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
Google Sites
Report abuse