Bac Blanc SIG

Systèmes d'Information de Gestion — Spécialité STMG

Coefficient 12

4 heures

Partie 1 : Base de données & SQLPartie 2 : Tableur & AnalysePartie 3 : Réseau & Sécurité

📋 Contexte de l'étude

L'entreprise TechVert est une PME spécialisée dans la vente d'équipements informatiques reconditionnés. Elle dispose d'un site e-commerce et d'un système d'information pour gérer ses commandes, clients et stocks.

Vous êtes stagiaire au service informatique et on vous confie plusieurs missions liées au SI de l'entreprise.

Partie 1 — Base de données et SQL (40 points)

Schéma relationnel de la base de données :

CLIENT(id_client, nom, prenom, email, ville, date_inscription)

PRODUIT(id_produit, designation, categorie, prix_ht, stock, etat)

COMMANDE(id_commande, #id_client, date_commande, statut)

LIGNE_COMMANDE(#id_commande, #id_produit, quantite, prix_unitaire)

Les clés primaires sont soulignées en bleu. Les clés étrangères sont en rouge.

L'attribut etat peut prendre les valeurs : 'neuf', 'reconditionne_A', 'reconditionne_B', 'reconditionne_C'.

1. (4 points) Identifiez la clé primaire et la(les) clé(s) étrangère(s) de la table LIGNE_COMMANDE. Expliquez leur rôle.

2. (4 points) Quel est le type de relation entre CLIENT et COMMANDE ? Justifiez.

3. (6 points) Écrivez les requêtes SQL suivantes :

a) Afficher le nom et le prénom de tous les clients habitant à Lyon.

b) Afficher la désignation et le prix TTC (prix_ht × 1,20) de tous les produits reconditionnés de catégorie 'ordinateur', triés par prix croissant.

c) Afficher le nombre total de commandes passées en 2024.

4. (8 points) Écrivez les requêtes SQL suivantes (jointures) :

a) Afficher le nom du client, la date de commande et le montant total TTC de chaque commande.

b) Afficher les clients qui n'ont jamais passé de commande.

5. (6 points) Écrivez la requête SQL pour insérer un nouveau produit : 'MacBook Air M2 Reconditionné', catégorie 'ordinateur', prix HT 799.99€, stock 15, état 'reconditionne_A'.

6. (6 points) Le directeur souhaite connaître le top 5 des clients par chiffre d'affaires TTC. Écrivez la requête.

7. (6 points) Écrivez la requête pour mettre à jour le stock : décrémenter de 2 le stock du produit id_produit = 42.

▶ Voir le corrigé Partie 1

1. Clés de LIGNE_COMMANDE :

Clé primaire composée : (id_commande, id_produit) — identifie de manière unique chaque ligne d'une commande.
Clés étrangères : id_commande référence COMMANDE(id_commande) et id_produit référence PRODUIT(id_produit). Elles assurent l'intégrité référentielle.

2. Type de relation :

Relation 1:N (un-à-plusieurs) : un client peut passer plusieurs commandes, mais chaque commande appartient à un seul client.

3. Requêtes SQL simples :

SELECT nom, prenom FROM CLIENT WHERE ville = 'Lyon';

SELECT designation, ROUND(prix_ht * 1.20, 2) AS prix_ttc
FROM PRODUIT
WHERE categorie = 'ordinateur'
  AND etat LIKE 'reconditionne%'
ORDER BY prix_ht ASC;

SELECT COUNT(*) AS nb_commandes
FROM COMMANDE
WHERE date_commande BETWEEN '2024-01-01' AND '2024-12-31';

4. Requêtes avec jointures :

a) Montant total par commande :

SELECT C.nom, CO.date_commande,
       ROUND(SUM(LC.quantite * LC.prix_unitaire * 1.20), 2) AS montant_ttc
FROM CLIENT C
JOIN COMMANDE CO ON C.id_client = CO.id_client
JOIN LIGNE_COMMANDE LC ON CO.id_commande = LC.id_commande
GROUP BY C.nom, CO.date_commande, CO.id_commande;

b) Clients sans commande :

SELECT C.nom, C.prenom
FROM CLIENT C
LEFT JOIN COMMANDE CO ON C.id_client = CO.id_client
WHERE CO.id_commande IS NULL;

5. Insertion :

INSERT INTO PRODUIT (designation, categorie, prix_ht, stock, etat)
VALUES ('MacBook Air M2 Reconditionné', 'ordinateur', 799.99, 15, 'reconditionne_A');

6. Top 5 clients par CA :

SELECT C.nom, C.prenom,
       ROUND(SUM(LC.quantite * LC.prix_unitaire * 1.20), 2) AS ca_ttc
FROM CLIENT C
JOIN COMMANDE CO ON C.id_client = CO.id_client
JOIN LIGNE_COMMANDE LC ON CO.id_commande = LC.id_commande
GROUP BY C.id_client, C.nom, C.prenom
ORDER BY ca_ttc DESC
LIMIT 5;

7. Mise à jour du stock :

UPDATE PRODUIT SET stock = stock - 2 WHERE id_produit = 42;

Partie 2 — Tableur et analyse (30 points)

Le directeur commercial vous fournit un extrait du tableau des ventes mensuelles 2024 :

	A	B	C	D
1	Mois	CA HT	Nb commandes	Panier moyen
2	Janvier	45 200	128	?
3	Février	38 750	112	?
...	...	...	...	...

1. (4 points) Quelle formule saisir en D2 pour calculer le panier moyen ? Doit-elle être recopiée ? Si oui, comment ?

2. (6 points) On souhaite créer une colonne E «Objectif atteint» qui affiche «OUI» si le CA HT dépasse 40 000 € et «NON» sinon. Quelle formule en E2 ?

3. (6 points) Écrivez la formule pour calculer en B14 le CA total annuel, et en B15 le CA moyen mensuel.

4. (6 points) On ajoute une colonne F avec le taux d'évolution du CA par rapport au mois précédent. Quelle formule en F3 ?

5. (8 points) Le directeur veut identifier le mois avec le plus fort CA. Quelle combinaison de fonctions utiliser ? Écrivez la formule.

▶ Voir le corrigé Partie 2

1. Panier moyen :

Formule en D2 : =B2/C2
Oui, elle se recopie vers le bas (jusqu'à D13). Les références sont relatives, elles s'adaptent automatiquement.

2. Objectif atteint :

Formule en E2 : =SI(B2>40000;"OUI";"NON")

3. CA total et moyen :

B14 (total) : =SOMME(B2:B13)
B15 (moyen) : =MOYENNE(B2:B13)

4. Taux d'évolution :

Formule en F3 : =(B3-B2)/B2
Formater en pourcentage. La cellule F2 reste vide (pas de mois précédent).

5. Mois avec le plus fort CA :

=INDEX(A2:A13;EQUIV(MAX(B2:B13);B2:B13;0))
Explication : MAX(B2:B13) trouve le CA maximum, EQUIV le localise dans la plage, INDEX retourne le nom du mois correspondant.

Partie 3 — Réseau et sécurité (30 points)

1. (6 points) L'entreprise TechVert a l'adresse réseau 192.168.10.0/24.

a) Quel est le masque de sous-réseau correspondant ?

b) Combien d'adresses IP sont utilisables pour des hôtes ?

c) Quelle est l'adresse de broadcast ?

2. (6 points) Le site e-commerce utilise le protocole HTTPS.

a) Quelle est la différence entre HTTP et HTTPS ?

b) Qu'est-ce qu'un certificat SSL/TLS et à quoi sert-il ?

c) Pourquoi est-ce indispensable pour un site e-commerce ?

3. (8 points) L'entreprise a subi une tentative de phishing par email.

a) Définissez le phishing.

b) Citez trois mesures techniques pour se protéger du phishing.

c) Rédigez un court message de sensibilisation (5 lignes max) à destination des employés.

4. (6 points) L'entreprise doit se conformer au RGPD.

a) Que signifie RGPD ?

b) Citez trois droits des utilisateurs garantis par le RGPD.

c) Quelles obligations cela implique-t-il pour TechVert vis-à-vis de sa base clients ?

5. (4 points) Le responsable IT souhaite mettre en place un plan de sauvegarde. Expliquez la règle «3-2-1» et proposez une solution adaptée à TechVert.

▶ Voir le corrigé Partie 3

1. Adressage réseau :

a) Masque : 255.255.255.0

b) Adresses utilisables : 2^8 - 2 = 254 hôtes (on retire l'adresse réseau .0 et le broadcast .255)

c) Adresse de broadcast : 192.168.10.255

2. HTTPS :

a) HTTP transmet les données en clair ; HTTPS chiffre les échanges via le protocole TLS, garantissant confidentialité et intégrité.

b) Un certificat SSL/TLS est un fichier numérique qui authentifie l'identité du serveur et permet le chiffrement. Il est délivré par une autorité de certification (CA).

c) Indispensable car les clients transmettent des données sensibles (coordonnées bancaires, données personnelles). Sans HTTPS, ces données pourraient être interceptées (attaque man-in-the-middle).

3. Phishing :

a) Le phishing (hameçonnage) est une technique d'ingénierie sociale consistant à usurper l'identité d'un tiers de confiance (banque, administration) pour inciter la victime à divulguer ses informations confidentielles.

b) Trois mesures techniques : filtre anti-spam avancé, authentification à deux facteurs (2FA) sur les comptes professionnels, formation régulière des employés à reconnaître les emails frauduleux.

c) Message de sensibilisation :

« Attention au phishing ! Ne cliquez jamais sur un lien dans un email vous demandant vos identifiants. Vérifiez toujours l'adresse de l'expéditeur. En cas de doute, contactez le service IT avant d'ouvrir une pièce jointe suspecte. Votre vigilance est notre meilleure protection. »

4. RGPD :

a) Règlement Général sur la Protection des Données (règlement européen 2016/679, applicable depuis mai 2018).

b) Droits des utilisateurs : droit d'accès (consulter ses données), droit de rectification (modifier ses données), droit à l'effacement (droit à l'oubli).

c) Obligations pour TechVert : nommer un DPO si nécessaire, tenir un registre des traitements, obtenir le consentement explicite des clients, sécuriser les données, notifier la CNIL en cas de violation sous 72h, permettre l'exercice des droits des personnes.

5. Règle 3-2-1 :

3 copies des données, sur 2 supports différents, dont 1 hors site (cloud ou site distant).
Solution pour TechVert : sauvegarde quotidienne de la base de données sur le serveur local (copie 1), réplication sur un NAS dédié (copie 2, support différent), et sauvegarde hebdomadaire chiffrée sur un cloud sécurisé comme AWS S3 (copie 3, hors site).

← Tous les bacs blancs Toutes les annales