موضوع امتحان كتابي · Ministère de l'Agriculture et de la Pêche maritime — Ingénieurs d'État (informatique) · 2012
موضوع حقيقي منقول من النسخة المنشورة، مع تصحيح مقترح من فريق KounKour (ليس تصحيحاً رسمياً).
Photo de la copie (1 page) publiée par alwadifa-maroc.com. Aucun corrigé officiel.
Royaume du Maroc — Ministère de l'Agriculture et de la Pêche Maritime — Direction des ressources humainesConcours de recrutement des Ingénieurs d'État au Ministère de l'Agriculture et de la Pêche Maritime — Session du 24 novembre 2012 (option Informatique)Coefficient : 4, durée : 3 heures
Un agriculteur désire connaître les productions réalisées sur ses parcelles culturales. Une parcelle peut comporter plusieurs productions dans la même année civile.Pour chaque parcelle, on veut connaître quelles productions ont été réalisées et à quelles dates. On désire également connaître le rendement de chaque production par parcelle, ainsi que les apports en N, P, K pour une période donnée.Enfin, on doit pouvoir disposer de la quantité et du nom de l'engrais qui a été épandu sur chaque parcelle (à une date donnée).
L'agriculteur vous fournit les informations suivantes :- Le nom de la parcelle et sa superficie- Les coordonnées géographiques de la parcelle- Le nom de la culture- Les dates début et fin de production- La quantité produite et l'unité dans laquelle elle est exprimée- Le nom de l'engrais- La proportion de N, de P et de K dans l'engrais- La quantité d'engrais épandue à une date donnée sur une parcelle donnée et l'unité dans laquelle elle est exprimée.
1. Établir le dictionnaire des données2. Établir le modèle conceptuel des données correspondant puis le modèle logique associé.3. Écrire les instructions SQL permettant la création des tables et des relations entre les tables.4. Rédiger un programme en PL/SQL (trigger) permettant la vérification des chevauchements des périodes de culture au sein de la même parcelle.
Parcelle : Une parcelle est généralement une superficie de terrain ayant une unité de propriété. Une parcelle peut être dans ce cas la propriété d'une personne privée ou publique, seule ou en groupe. En agriculture, elle désigne alors la division agricole (champ, pré, vignoble, verger, etc.) exploitée par la même personne ou le même groupe de personnes.Coordonnées géographiques : Par coordonnées géographiques (ou encore « repères géographiques ») d'un lieu, on entend la latitude, la longitude et l'altitude.Rendement : En agriculture, on appelle habituellement rendement la quantité de produit récolté (production) sur une surface cultivée donnée (superficie).Engrais : Les engrais sont des substances, le plus souvent des mélanges d'éléments minéraux, destinées à apporter aux plantes des compléments d'éléments nutritifs, de façon à améliorer leur croissance, et à augmenter le rendement et la qualité des cultures.Épandage : L'épandage est une technique agricole consistant à répandre divers produits sur des zones cultivées.N : Azote, P : Phosphore, K : Potassium
ليس تصحيحاً رسمياً: حرره مشرفو KounKour.
- id_parcelle : identifiant de la parcelle (numérique)- nom_parcelle : nom de la parcelle (texte)- superficie : superficie de la parcelle en hectares (numérique)- latitude, longitude, altitude : coordonnées géographiques (numérique)- id_culture : identifiant de la culture (numérique)- nom_culture : nom de la culture (texte)- id_production : identifiant d'une production (numérique)- date_debut, date_fin : période de production (date)- quantite_produite, unite_production : quantité récoltée et unité (numérique, texte)- id_engrais : identifiant de l'engrais (numérique)- nom_engrais : nom de l'engrais (texte)- pct_n, pct_p, pct_k : proportions d'azote, de phosphore et de potassium (numérique)- date_epandage : date de l'épandage (date)- quantite_engrais, unite_engrais : quantité épandue et unité (numérique, texte)
MCD (entités et associations) :- PARCELLE (id_parcelle, nom_parcelle, superficie, latitude, longitude, altitude)- CULTURE (id_culture, nom_culture)- ENGRAIS (id_engrais, nom_engrais, pct_n, pct_p, pct_k)- PRODUCTION : entité qui relie une parcelle et une culture (0,n) avec date_debut, date_fin, quantite_produite et unite_production. Une parcelle peut porter plusieurs productions par an.- ÉPANDRE : association entre PARCELLE (0,n) et ENGRAIS (0,n), datée, porteuse de quantite_engrais et unite_engrais.
MLD (relationnel) :- PARCELLE(id_parcelle, nom_parcelle, superficie, latitude, longitude, altitude)- CULTURE(id_culture, nom_culture)- PRODUCTION(id_production, #id_parcelle, #id_culture, date_debut, date_fin, quantite_produite, unite_production)- ENGRAIS(id_engrais, nom_engrais, pct_n, pct_p, pct_k)- EPANDAGE(#id_parcelle, #id_engrais, date_epandage, quantite_engrais, unite_engrais), clé primaire (id_parcelle, id_engrais, date_epandage)Rendement d'une production = quantite_produite / superficie.Apport en N sur une période = Σ quantite_engrais × pct_n / 100 (même calcul pour P et K).
CREATE TABLE parcelle (id_parcelle NUMBER PRIMARY KEY, nom_parcelle VARCHAR2(80) NOT NULL, superficie NUMBER(10,2) CHECK (superficie > 0), latitude NUMBER(9,6), longitude NUMBER(9,6), altitude NUMBER(7,2));CREATE TABLE culture (id_culture NUMBER PRIMARY KEY, nom_culture VARCHAR2(80) NOT NULL);CREATE TABLE production (id_production NUMBER PRIMARY KEY, id_parcelle NUMBER NOT NULL REFERENCES parcelle, id_culture NUMBER NOT NULL REFERENCES culture, date_debut DATE NOT NULL, date_fin DATE NOT NULL, quantite_produite NUMBER(12,2), unite_production VARCHAR2(20), CONSTRAINT ck_periode CHECK (date_fin >= date_debut));CREATE TABLE engrais (id_engrais NUMBER PRIMARY KEY, nom_engrais VARCHAR2(80) NOT NULL, pct_n NUMBER(5,2), pct_p NUMBER(5,2), pct_k NUMBER(5,2));CREATE TABLE epandage (id_parcelle NUMBER REFERENCES parcelle, id_engrais NUMBER REFERENCES engrais, date_epandage DATE, quantite_engrais NUMBER(10,2), unite_engrais VARCHAR2(20), CONSTRAINT pk_epandage PRIMARY KEY (id_parcelle, id_engrais, date_epandage));
Un trigger de niveau ligne qui lit la table PRODUCTION provoquerait l'erreur de « table en mutation » (ORA-04091) lors d'un UPDATE. On utilise donc un trigger de niveau instruction, qui vérifie après chaque insertion ou modification :
CREATE OR REPLACE TRIGGER trg_production_chevauchementAFTER INSERT OR UPDATE OF id_parcelle, date_debut, date_fin ON productionDECLARE v_nb NUMBER;BEGIN SELECT COUNT(*) INTO v_nb FROM production a JOIN production b ON a.id_parcelle = b.id_parcelle AND a.id_production < b.id_production WHERE a.date_debut <= b.date_fin AND b.date_debut <= a.date_fin; IF v_nb > 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Chevauchement de périodes de culture sur une même parcelle'); END IF;END;/Deux périodes [d1, f1] et [d2, f2] se chevauchent si d1 ≤ f2 et d2 ≤ f1. Variante acceptée : un trigger composé (COMPOUND TRIGGER) ou un trigger BEFORE INSERT de niveau ligne limité aux insertions.
Corrigé proposé par l'équipe KounKour (administrateurs de la plateforme). Ce n'est pas un corrigé officiel : les points que la copie ne permet pas de trancher (figures, plans, énoncés coupés) sont signalés.