Ce document pédagogique est spécifiquement conçu comme un support de révision pour les étudiants en Brevet de Technicien Supérieur (BTS) Commerce Gestion des Organisations (CGO) de deuxième année, dans le cadre de l'unité d'enseignement "Organisation du Système d’Informations". Il propose une série d'exercices pratiques visant à consolider les compétences en modélisation et en interrogation de bases de données.
Il couvre les notions suivantes:
- L'élaboration d'un Modèle Conceptuel de Données (MCD) à partir d'un schéma relationnel donné.
- La rédaction de requêtes SQL simples (sélections, filtres, gestion des dates).
- La conception de requêtes SQL complexes, intégrant regroupements, sous-requêtes et calculs agrégés.
Modélisation Merise : Application revisions SQL sujet
Télécharger PDFRévisions BTS CGO : Organisation du Système d'Information et SQL
Ce document est conçu pour les étudiants en BTS Comptabilité et Gestion (CGO) et propose une série d'exercices pratiques axés sur l'organisation des systèmes d'information, incluant la modélisation de bases de données via le Modèle Conceptuel de Données (MCD) et la manipulation de données à l'aide de requêtes SQL. Ces révisions couvrent des aspects fondamentaux pour la gestion et l'analyse des données en entreprise.
Schéma Relationnel de la Base de Données
Les exercices de requêtes SQL ci-après s'appuient sur le schéma relationnel suivant, décrivant la structure d'une base de données pour la gestion de cours, participants et animateurs :
- Cours (code_cours (PK), #code_thème (FK), #matricule_animateur (FK), niveau, tarif_hr, prime_resp, droit_inscrip) : Cette table contient les informations détaillées sur chaque cours proposé, y compris son niveau, son tarif horaire et les droits d'inscription.
- Participant (matricule (PK), #code_entreprise (FK), nom, prénom, localité) : Cette table recense les participants inscrits aux différents cours, avec leurs coordonnées.
- Entreprise (code_entreprise (PK), nom, adresse) : Cette table stocke les informations sur les entreprises des participants.
- Thème (code_thème (PK), #sur_thème (FK vers Thème.code_thème), désignation) : Cette table organise les cours par thèmes et sous-thèmes.
- Animateur (matricule_animateur (PK), nom, prénom) : Cette table liste les animateurs ou formateurs responsables des cours.
- Participer (#code_cours (FK, PK partielle), #matricule (FK, PK partielle)) : Table d'association indiquant quels participants sont inscrits à quels cours.
- Animer (#code_cours (FK, PK partielle), #matricule_animateur (FK, PK partielle), date, nbre_heures) : Table d'association détaillant quel animateur a dispensé quel cours, à quelle date et pour combien d'heures.
Note importante : Le champ Thème.sur_thème représente, s'il existe, le code du thème parent auquel un thème est rattaché. Cela permet d'établir une hiérarchie entre les thèmes (thèmes principaux et sous-thèmes).
Modèle Conceptuel de Données (MCD)
Le Modèle Conceptuel de Données (MCD) est une représentation graphique et structurée des données d'un système d'information, indépendamment de toute contrainte technique. Il décrit les entités (objets d'intérêt), leurs attributs (caractéristiques) et les relations (liens) entre ces entités, ainsi que les cardinalités (nombre de participations minimal et maximal des entités dans une relation). L'exercice consiste à élaborer le MCD correspondant au schéma relationnel fourni ci-dessus.
Entités et Attributs Principaux du MCD
D'après le schéma relationnel, les entités principales à considérer pour le MCD sont :
- Entreprise : avec comme attributs code_entreprise (identifiant), nom, adresse.
- Thème : avec comme attributs code_thème (identifiant), désignation, et un lien récursif via sur_thème pour les sous-thèmes.
- Cours : avec comme attributs code_cours (identifiant), niveau, tarif_hr, prime_resp, droit_inscrip.
- Participant : avec comme attributs matricule (identifiant), nom, prénom, localité.
- Animateur : avec comme attributs matricule_animateur (identifiant), nom, prénom.
Les relations entre ces entités, comme "Une Entreprise embauche plusieurs Participants", "Un Cours est rattaché à un Thème", "Un Animateur dispense des Cours", "Un Participant participe à des Cours", sont définies par les clés étrangères dans le schéma relationnel. Les tables "Participer" et "Animer" du schéma relationnel correspondent à des entités d'association dans le MCD, liant respectivement les Participants aux Cours et les Animateurs aux Cours.
Exercices SQL : Requêtes Simples (SELECT)
Cette section propose des exercices de requêtes SQL basiques, se concentrant sur les projections (SELECT) et les filtrages (WHERE). Elles nécessitent généralement une ou deux tables et n'impliquent pas de jointures complexes ni de regroupements multiples.
- Lister les dates uniques (sans doublons) auxquelles des cours ont eu lieu.
- Lister les cours qui :
- ont eu lieu soit le 19/04/2016, soit le 29/03/2016 ;
- se sont déroulés entre le 29/03/2016 et le 19/04/2016 ;
- ont été dispensés durant un mois d’avril (peu importe l'année).
- Lister les cours dispensés par Monsieur Taylor Young au cours de l’année 2016.
- Calculer le nombre total d’heures de cours dispensées par Monsieur Taylor Young au cours de l’année 2016.
- Lister les sous-thèmes rattachés au thème principal « Sciences ».
Exercices SQL : Requêtes Complexes
Cette section aborde des requêtes SQL plus avancées, nécessitant potentiellement des jointures (JOIN) entre plusieurs tables, des regroupements (GROUP BY), des fonctions d'agrégation (COUNT, SUM, AVG, etc.) et/ou des sous-requêtes (SUBQUERY) pour obtenir les résultats souhaités.
- Calculer le nombre de cours dispensés par niveau.
- Calculer le nombre total d’heures de cours dispensées par chaque animateur au cours de l’année 2016.
- Calculer le chiffre d’affaires total rapporté par chaque animateur au cours de l’année 2016.
- Identifier l'animateur ayant dispensé le plus grand nombre d’heures de cours durant le mois d’avril 2016.
- Calculer le chiffre d’affaires rapporté par thème et par mois au cours de l’année 2016. Seuls les thèmes n'ayant pas de « sur-thème » (c'est-à-dire les thèmes principaux) doivent être inclus.
- Calculer le chiffre d’affaires total rapporté par chaque entreprise au cours de l’année 2016, et trier les résultats par chiffre d’affaires décroissant.
Foire Aux Questions (FAQ)
Qu'est-ce qu'un schéma relationnel ?
Un schéma relationnel est une description formelle de la structure d'une base de données, spécifiant les tables (relations), leurs colonnes (attributs), les types de données, ainsi que les clés primaires et étrangères qui définissent les liens entre les tables. Il sert de base pour la conception et l'implémentation d'une base de données.
Quelle est la différence entre une clé primaire et une clé étrangère ?
Une clé primaire est un attribut (ou un ensemble d'attributs) qui identifie de manière unique chaque enregistrement dans une table. Elle garantit l'intégrité de l'entité. Une clé étrangère est un attribut (ou un ensemble d'attributs) dans une table qui fait référence à la clé primaire d'une autre table, établissant ainsi une relation entre les deux tables. Elle assure l'intégrité référentielle et la cohérence des données entre les tables.
Pourquoi utiliser des regroupements (GROUP BY) en SQL ?
La clause
GROUP BYen SQL est utilisée pour regrouper les lignes qui ont les mêmes valeurs dans une ou plusieurs colonnes spécifiées. Cela permet d'appliquer des fonctions d'agrégation (commeCOUNT,SUM,AVG,MIN,MAX) à chaque groupe de lignes, plutôt qu'à l'ensemble des résultats. Elle est essentielle pour l'analyse de données, les rapports récapitulatifs et pour obtenir des statistiques agrégées.