Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Exercice : Un bureau d'études réalise une étude au profit du ministère de l'agr

Exercice : Un bureau d'études réalise une étude au profit du ministère de l'agriculture qui souhaite disposer de statistiques sur les récoltes d'agrumes au Maroc. Sur le terrain, un ensemble d'agriculteurs sont enregistrés. Un agriculteur peut travailler un ou plusieurs champs. Seuls les champs d'agrumes nous intéressent ici. Chaque agriculteur est tenu de déclarer ses récoltes annuelles. L'analyse a conduit à la structure suivante de la base de données : L'équipe responsable de cette étude compte 3 personnes : - Youssef : L'administrateur du système ; - Zineb : La secrétaire responsable de la mise à jour des produits, des agriculteurs, de leurs champs et des récoltes ; - Mehdi : Responsable de la réalisation des différentes statistiques. Il peut effectuer des consultations de toutes les données. Tâche 1. Créer la base de données Récoltes (un fichier de données, un fichier journal dans d:\votreNom avec une taille initiale de 5Mo et une croissance de 5%) et ses tables en respectant les contraintes suivantes : - Le type Nom est un type utilisateur construit sur la base du type varchar(50) ; - Les numéros de champs sont des numéros auto ; - Les numéros d'agriculteurs sont des clés primaires non clustérisés ; - On ne peut pas avoir deux champs à la même adresse ; - Le nom de la région doit commencer par "R_" ; - Le nom de région prend par défaut "R_Orientale" ; - La quantité récoltée doit obligatoirement être fournie ; Tâche 2. Créer un index clustérisé sur les champs nom et prénom de l'agriculteur Tâche 3. Les catégories peuvent être Orange, Citron. La valeur par défaut sera Orange. Ajouter les contraintes nécessaires Tâche 4. Sachant que Youssef doit accéder à la base de données en utilisant l'authentification Windows et que Zineb et Mehdi vont utiliser une authentification SQL Server, écrire les instructions pour : - Créer les éléments nécessaires permettant aux différentes personnes d'accéder à la base de données ; - Affecter les droits nécessaires. Des stagiaires peuvent être recrutés : - Créer un groupe pour les stagiaires; - Créer un schéma permettant au groupe des stagiaires de faire des consultations ; - Inclure la table récoltes dans le schéma ; - Laila est une nouvelle stagiaire. Faire le nécessaire pour lui permettre d'accéder au système en tant que stagiaire. Tâche 5. Ajouter le produit "P25" portant le nom "Pamplemousse" et ayant un degré d'acidité 6. Exceptionnellement, ce produit aura la catégorie "Pamplemousse". Tâche 6. Supprimer les récoltes des années précédentes pour l'agriculteur 'Najib El ALAMI'. Tâche 7. Ecrire les requêtes suivantes : 1. La liste de tous les champs de la région "R_Orientale" (Numéro, adresse, nom et prénom de l'agriculteur (nom et prénom regroupés et séparés par un tiret)) triés par ordre décroissant du nom et du prénom ; 2. Les couples d'agriculteurs qui habitent la même région ; 3. Les produits qui n'ont été récolté par aucun agriculteur durant l'année en cours ; 4. Les noms des agriculteurs qui n'ont récolté que des oranges ; 5. Le total des récoltes par année, par produit ; 6. Les champs où sont récoltés tous les produits de catégorie "Citron". Correction : create database Récoltes on (name=Recoltes_Data, filename='c:\bd\data\Rec.mdf',size=5MB,FileGrowth=5%) log on (name=Recoltes_Log, filename='c:\bd\data\Rec.ldf',size=5MB,FileGrowth=5%) use Récoltes Create Type Nom from varchar(50) Create table Agriculteur (NumAg int primary key nonClustered, NomAg Nom, PrenomAg Nom, Region Nom check(Region like 'R[_]%') default 'R_Oriantale' ) Create table Produit (CodPro varchar(10) primary key, NomPro Nom, Categorie varchar(100), DegreAcidite int) Create table Champ (NumCh int identity(1,1) primary key, AdresseCh varchar(100) unique, NumAg int foreign key References Agriculteur) Create table Recolte (NumCh int foreign key references Champ, CodPro varchar(10) foreign key references Produit , annee int, QteRecoltee float not null, constraint PK_Recoltes Primary Key(NumCh,CodPro, Annee) ) go create clustered index IX on Agriculteur (NomAg,PrenomAg) go Alter table Produit add constraint CK_Pro Check (categorie in ('Orange', 'Citron')) Alter table Produit add constraint def_Pro Default 'Orange' for categorie CREATE LOGIN [NAOUAL-PC\Youssef] FROM WINDOWS CREATE LOGIN [zineb] WITH PASSWORD='123456' CREATE LOGIN [mehdi] WITH PASSWORD='123456' use Récoltes create user [NAOUAL-PC\Youssef] for login [NAOUAL-PC\Youssef] Create user zineb for login zineb Create user mehdi for login mehdi exec sp_addsrvrolemember '[NAOUAL-PC\Youssef]', 'SysAdmin' use récoltes exec sp_addrolemember 'db_datawriter','zineb' exec sp_addrolemember 'db_datareader','mehdi' use Récoltes Create role RoleStag Create schema S1 grant select on schema::S1 to RoleStag alter schema S1 transfer recolte create login leila with password='123456' use Récoltes create user leila for login leila exec sp_addrolemember 'RoleStag', 'leila' alter table produit nocheck constraint CK_Pro insert into Produit values ('P25','Pamplemousse','Pamplemousse',6) alter table produit check constraint CK_Pro delete from Recolte where annee <YEAR(getdate()) and numch in (select numch from Champ where NumAg in (select NumAg from Agriculteur where NomAg='El Alami' and PrenomAg='Najib')) select NumCh,AdresseCh, NomAg + '-' + PrenomAg as [Nom et Prénom] from Champ C inner join Agriculteur A on C.NumAg=A.NumAg where Region='R_Orientale' order by [Nom et Prénom] desc Select A1.*,A2.* From Agriculteur A1 inner join Agriculteur A2 on A1.Region=A2.Region where A1.NumAg<A2.NumAg select * from Produit where CodPro not in (select CodPro from Recolte where annee=YEAR(getdate())) select nomag from agriculteur A inner join Champ C on A.NumCh=C.NumCh where numch in (select numch from recolte R inner join Produit P on R.CodPro=P.codpro where categorie='Orange' ) and not in (select numch from recolte R inner join Produit P on R.CodPro=P.codpro where categorie<>'Orange' ) select P.Codpro, Nompro, Annee, SUM(QteRecoltee) as [Total Récoltes] from recolte R inner join Produit P on R.CodPro=P.CodPro group by P.Codpro, Nompro, Annee select C.numch,AdresseCh from recolte R inner join Champ C on R.NumCh=C.NumCh inner join Produit P on P.CodPro=R.CodPro where Categorie='citron' group by C.numch,AdresseCh having count(distinct R.codpro)= (select COUNT(distinct codpro) from Produit where Categorie='citron') Partie 2 : SGBDII Une table Categorie sert à enregistrer des statistiques : Tâche 8. Créer les fonctions suivantes : Fct1. Qui retourne le nombre de régions distinctes Fct2. Qui retourne la liste des régions distinctes Tâche 9. Créer les procédures stockées suivantes (Quand c'est possible, il faut exploiter les procédures et fonctions précédemment créées) : PS1. Qui affiche la liste des produits de degré d'acidité inférieur à 5 récoltés dans un champ dont le numéro est fourni en paramètre PS2. Qui retourne la valeur 0 si aucun champ n'a été trouvé pour une région fournie en paramètre et la valeur 1 sinon PS3. Qui retourne en sortie le nombre de champs travaillés par les agriculteurs d'une région fournie en paramètre. PS4. - Qui affiche le nombre total de régions sous la forme : On a XXX Région - Qui affiche la liste des régions - Quand une région contient au moins un champ, La procédure stockée affiche pour chaque région :  Le nombre de champs travaillés  La liste des champs  Pour chaque champ la liste des produits de degré d'acidité inférieur à 5 - Quand la région ne contient aucun champ, il faut afficher un message d'erreur système Tâche 10. Créer les déclencheurs suivants : TR1. Personne n'a le droit de créer ou de modifier la structure des tables TR2. La suppression de récoltes est interdite TR3. A chaque enregistrement d'une récolte, mettre à jour la table des statistiques pour les catégories correspondantes TR4. A l'ajout de produits, vérifier si la catégorie est présente dans la table Categorie. Si ce n'est pas le cas la créer avec un total récolte 0 TR5. Qui transforme tous les noms de produits en majuscules avant de les insérer dans la table Produit uploads/Industriel/ efm-corrige 1 .pdf