Au niveau national, la natation est un sport géré par la Fédération Marocaine de Natation, puis par des clubs au niveau des différentes villes du Royaume.
La fédération organise des entraînements de natation communs aux différents athlètes dans le but d’harmoniser les pratiques et de déceler les futurs talents. Ces entraînements communs nécessitent de disposer de créneaux horaires dans trois piscines différentes.
La fédération souhaite mettre en place une gestion informatisée afin de contrôler que chaque athlète suit bien son plan d'entraînement personnalisé. Pour chaque athlète, le plan d'entraînement proposé définit la distance (exprimée en mètres) à parcourir pour chaque entraînement.
Pour assurer cette gestion, le schéma relationnel suivant a été établi :
ATHLETE(NumLicence, NomAthlete, PrenomAthlete, CategorieAthlete)
ENTRAINEMENT(NumEntrainement, DateEntrainement, HeureDebut, HeureFin, NumPiscine#)
PLAN_ENTRAINEMENT(NumEntrainement#, NumLicence#, DistanceAParcourir, DistanceParcourue)
PISCINE(NumPiscine, NomPiscine, AdressePiscine)
Les champs soulignés correspondent aux clefs primaires, les champs suivis du caractère # sont des clefs étrangères.
TRAVAIL À FAIRE
- Création de la base de données
- Créer la base de données sous SQL SERVER 2 pts
- Créer trois enregistrements par table
- Contraintes
- Les valeurs permises pour le champs CategorieAthlete sont (Catégorie1,Catégorie2,catégorie3)0,5pts
- La distance parcourue doit être positives et inférieure ou égale à la distance à parcourir 1 pts
III. Requêtes
- Afficher la liste des athlètes triés par ordre décroissant des catégories et ordre croissant de leur numéro de licence.1pts
- Afficher la liste de piscines triées par ordre croissant des noms, les noms doivent avoir le premier caractère en majuscule et les adresses des piscines en minuscule 1,5 pts.
- Afficher les athlètes qui ont participés au plan d’entrainement numéro 20, (nom,prénom,distanceparcourue,observation), le champs observation permettant d’afficher le mot débutant si la distance parcouru est inférieure à 2000 m sinon on affiche le mot expert.2 pts
- Afficher les piscines (numéro, nom,adresse) qui seront disponible pour le mois janvier de l’année 2012. 1,5 pts
- Créer une vue vue1 affichant le nombre d’athlète par catégorie. 1,5 pts
- Créer unevue vue2 affichant le total des distances parcourue au niveau des différents entrainements pour chaque athlètes (numéro athlète, total distance parcourue) 1,5 pts
- En utilisant la question N°6 afficher les athlètes dont la distance parcourue dans les différents entrainements est supérieur à 2000 m 1,5 pts
- Afficher les entrainements dont leur distance à parcourir est la valeur maximale.1,5 pts
- Créer une vue vue3permettant d’afficher la listes des entrainements suivis(numéro,date,heure début,heure fin,Nom piscine,Distance à parcourir, distance parcourue)pour chaque athlète. 1,5 pts
- Afficher les athlètes qui ont participé à au moins 4 entrainements.1,5 pts
- Créer une vue vue4affichant le nom de piscine le plus utilisé par les athlètes de la catégorie 1 (1,5 pts)
solution : Copier le script dans votre sql server pour qu'il s'affiche correctement bonne chance
create database EFM_SGBDI
use EFM_SGBDI
--I/1
create table ATHLETE(
NumLicence int primary key,
NomAthlete varchar(255),
PrenomAthlete varchar(255),
CategorieAthlete varchar(255)
)
create table ENTRAINEMENT(
NumEntrainement int primary key,
DateEntrainement date,
HeureDebut int,
HeureFin int,
NumPiscine int foreign key references PISCINE(NumPiscine)
)
create table PLAN_ENTRAINEMENT(
NumEntrainement int foreign key references ENTRAINEMENT(NumEntrainement),
NumLicence int foreign key references ATHLETE(NumLicence),
DistanceAParcourir float,
DistanceParcourue float,
constraint PK_PLAN_ENTRAINEMENT primary key(NumEntrainement,NumLicence)
)
create table PISCINE(
NumPiscine int primary key,
NomPiscine varchar(255),
AdressePiscine varchar(255)
)
set language french
--I/2
insert into ATHLETE values (1,'Hillal','Abdessamad','Catégorie2')
insert into ATHLETE values (2,'Sahri','Anas','Catégorie1')
insert into ATHLETE values (3,'badir','Zaid','Catégorie3')
insert into ATHLETE values (4,'Dahmane','Brahim','Catégorie1')
insert into ATHLETE values (5,'Azrig','Abdelhaq','Catégorie2')
insert into PISCINE values (1,'Albaladi','Syba')
insert into PISCINE values (2,'Koutobiya','Jam3 Alfana')
insert into PISCINE values (3,'Alfarah','Dawdiyat')
insert into PISCINE values (4,'Alnour','Massira')
insert into ENTRAINEMENT values (1,'19/01/2010',1,6,1)
insert into ENTRAINEMENT values (2,'25/01/2010',6,8,2)
insert into ENTRAINEMENT values (3,'26/02/2010',7,10,1)
insert into ENTRAINEMENT values (4,'28/02/2010',12,16,3)
insert into ENTRAINEMENT values (6,'05/03/2010',2,5,2)
insert into ENTRAINEMENT values (20,'10/03/2010',8,12,2)
insert into ENTRAINEMENT values (7,'1/1/2011',14,16,3)
insert into ENTRAINEMENT values (8,'5/1/2012',15,17,1)
insert into ENTRAINEMENT values (8,'5/1/2012',15,17,1)
insert into PLAN_ENTRAINEMENT values (1,1,500,250)
insert into PLAN_ENTRAINEMENT values (2,1,300,200)
insert into PLAN_ENTRAINEMENT values (3,2,600,150) `
insert into PLAN_ENTRAINEMENT values (1,3,100,50)
insert into PLAN_ENTRAINEMENT values (4,1,400,250)
insert into PLAN_ENTRAINEMENT values (2,2,600,400)
insert into PLAN_ENTRAINEMENT values (20,4,250,150)
insert into PLAN_ENTRAINEMENT values (20,1,2400,2200)
insert into PLAN_ENTRAINEMENT values (7,4,3000,2200)
insert into PLAN_ENTRAINEMENT values (1,2,700,150)
insert into PLAN_ENTRAINEMENT values (7,2,900,500)
insert into PLAN_ENTRAINEMENT values (4,2,1500,1200)
--II
--1. Les valeurs permises pour le champs CategorieAthlete sont (Catégorie1,Catégorie2,catégorie3) 0,5pts
alter table ATHLETE
add constraint CheckCat check(CategorieAthlete in ('Catégorie1','Catégorie2','Catégorie3'))
--2. La distance parcourue doit être positives et inférieure ou égale à la distance à parcourir 1 pts
alter table PLAN_ENTRAINEMENT
add constraint ChekDs check(DistanceParcourue > 0 and DistanceParcourue<= DistanceAParcourir)
--II. Requêtes
--1. Afficher la liste des athlètes triés par ordre décroissant des catégories et ordre croissant de leur numéro de licence. 1pts
select * from ATHLETE order by CategorieAthlete desc,NumLicence asc
--2. Afficher la liste de piscines triées par ordre croissant des noms, les noms doivent avoir le premier caractère en majuscule et les adresses des piscines en minuscule 1,5 pts.
select NumPiscine,UPPER(LEFT(NomPiscine,1))+lower(RIGHT(NomPiscine,LEN(NomPiscine)-1)),lower(AdressePiscine) from PISCINE order by NomPiscine asc
--3
select a.NomAthlete,a.PrenomAthlete,p.DistanceParcourue,case
when p.DistanceParcourue < 2000 then 'débutant'
else 'expert'
end as 'observation'
from ATHLETE a, PLAN_ENTRAINEMENT p
where a.NumLicence = p.NumLicence and p.NumEntrainement = 20
--4. Afficher les piscines (numéro, nom,adresse) qui seront disponible pour le mois janvier de l’année 2012. 1,5 pts
select * from PISCINE where NumPiscine in (select NumPiscine from ENTRAINEMENT where MONTH(DateEntrainement) = 1 and YEAR(DateEntrainement) = '2012')
--5. Créer une vue vue1 affichant le nombre d’athlète par catégorie. 1,5 pts
create view vue1 as (select CategorieAthlete,COUNT(*) as [Nombre ATHLETE] from ATHLETE group by CategorieAthlete)
select * from vue1
--6. Créer une vue vue2 affichant le total des distances parcourue au niveau des différents entrainements pour chaque athlètes (numéro athlète, total distance parcourue) 1,5 pts
create view vue2 as (select a.NumLicence,SUM(p.DistanceParcourue) as [total_distances] from PLAN_ENTRAINEMENT p, ATHLETE a where p.NumLicence = a.NumLicence group by a.NumLicence)
select * from vue2
--7. En utilisant la question N°6 afficher les athlètes dont la distance parcourue dans les différents entrainements est supérieur à 2000 m 1,5 pts
select * from ATHLETE a, vue2 v where a.NumLicence = v.NumLicence and v.total_distances > 2000
-- 8. Afficher les entrainements dont leur distance à parcourir est la valeur maximale. 1,5 pts
select e.NumEntrainement,e.DateEntrainement,e.HeureDebut,e.HeureFin,p.NomPiscine
from ENTRAINEMENT e, PISCINE p
where e.NumPiscine = p.NumPiscine and NumEntrainement in ( select NumEntrainement from PLAN_ENTRAINEMENT where DistanceAParcourir >= all (select DistanceAParcourir from PLAN_ENTRAINEMENT))
--9. Créer une vue vue3 permettant d’afficher la listes des entrainements suivis(numéro,date,heure début,heure fin,Nom piscine,Distance à parcourir, distance parcourue)pour chaque athlète. 1,5 pts
select a.NomAthlete ,e.NumEntrainement,e.DateEntrainement,e.HeureDebut,e.HeureFin,ps.NomPiscine,SUM(p.DistanceAParcourir) as [DistanceAParcourir],SUM(p.DistanceParcourue) as [DistanceParcourue]
from ATHLETE a, PLAN_ENTRAINEMENT p, ENTRAINEMENT e, PISCINE ps
where a.NumLicence = p.NumLicence and e.NumEntrainement = p.NumEntrainement and ps.NumPiscine = e.NumPiscine
group by a.NomAthlete ,e.NumEntrainement,e.DateEntrainement,e.HeureDebut,e.HeureFin,ps.NomPiscine
--10
select * from ATHLETE where NumLicence in ( select NumLicence from PLAN_ENTRAINEMENT group by NumLicence having COUNT(*) >= 4)
--11. Créer une vue vue4 affichant le nom de piscine le plus utilisé par les athlètes de la catégorie 1 (1,5 pts)
select ps.NomPiscine,COUNT(*)
from ATHLETE a, PLAN_ENTRAINEMENT p,ENTRAINEMENT e, PISCINE ps
where a.NumLicence = p.NumLicence and p.NumEntrainement = e.NumEntrainement and e.NumPiscine = ps.NumPiscine and a.CategorieAthlete = 'Catégorie1'
group by ps.NomPiscine having COUNT(*) >= all
(select COUNT(*) from ATHLETE a, PLAN_ENTRAINEMENT p,ENTRAINEMENT e, PISCINE ps
where a.NumLicence = p.NumLicence and p.NumEntrainement = e.NumEntrainement and e.NumPiscine = ps.NumPiscine and a.CategorieAthlete = 'Catégorie1'
group by ps.NomPiscine )
