Documentation technique

art2rue_creation_et_requetes.sql

5 min de lecture

art2rue_creation_et_requetes.sql

Note de lecture générée automatiquement

Le vrai fichier est scripts/bdd/art2rue_creation_et_requetes.sql (Obsidian n’affiche pas les scripts eux-mêmes). Ne modifie pas cette note : modifie le script, puis relance python3 generer_vues_obsidian.py dans le dossier scripts.

Code

/* =============================================================================
   art2rue_creation_et_requetes.sql — Exercice « Art-2-Rue » (Révision Merise/UML)
   SGBD : SQL Server (T-SQL). Exécuter dans VS Code (extension SQL Server),
          SSMS ou : sqlcmd -S localhost -U sa -C -i art2rue_creation_et_requetes.sql
   ============================================================================= */
 
-- Base dédiée au corrigé (nom différent de ta base de cours « ArtDeRue » pour ne jamais l'écraser)
IF DB_ID(N'ArtDeRue_Corrige') IS NULL CREATE DATABASE ArtDeRue_Corrige;
GO
USE ArtDeRue_Corrige;
GO
 
/* ---------- Suppression dans l'ordre inverse des dépendances (relancer sans erreur) */
DROP TABLE IF EXISTS Photos, Oeuvre_Technique, Oeuvre_Artiste, Oeuvres,
                     Benevoles, Techniques, Lieux, Artistes;
GO
 
/* ---------- Création des tables ----------------------------------------------- */
CREATE TABLE Artistes (
    id_artiste   INT IDENTITY(1,1) CONSTRAINT PK_Artistes PRIMARY KEY,
    pseudonyme   NVARCHAR(100) NOT NULL CONSTRAINT UQ_Artistes_Pseudo UNIQUE,
    nom_reel     NVARCHAR(150) NULL,
    bio          NVARCHAR(MAX) NULL,
    nationalite  NVARCHAR(60)  NULL
);
 
CREATE TABLE Lieux (
    id_lieu    INT IDENTITY(1,1) CONSTRAINT PK_Lieux PRIMARY KEY,
    nom        NVARCHAR(150) NOT NULL,
    adresse    NVARCHAR(255) NULL,
    quartier   NVARCHAR(100) NOT NULL,
    latitude   DECIMAL(9,6)  NOT NULL CONSTRAINT CK_Lieux_Lat CHECK (latitude BETWEEN -90 AND 90),
    longitude  DECIMAL(9,6)  NOT NULL CONSTRAINT CK_Lieux_Lon CHECK (longitude BETWEEN -180 AND 180)
);
 
CREATE TABLE Techniques (
    id_technique INT IDENTITY(1,1) CONSTRAINT PK_Techniques PRIMARY KEY,
    nom          NVARCHAR(80) NOT NULL CONSTRAINT UQ_Techniques_Nom UNIQUE
);
 
CREATE TABLE Benevoles (
    id_benevole INT IDENTITY(1,1) CONSTRAINT PK_Benevoles PRIMARY KEY,
    nom         NVARCHAR(100) NOT NULL,
    prenom      NVARCHAR(100) NOT NULL,
    email       NVARCHAR(255) NOT NULL CONSTRAINT UQ_Benevoles_Email UNIQUE
);
 
CREATE TABLE Oeuvres (
    id_oeuvre             INT IDENTITY(1,1) CONSTRAINT PK_Oeuvres PRIMARY KEY,
    titre                 NVARCHAR(200) NOT NULL,
    date_creation         DATE NULL,
    description           NVARCHAR(MAX) NULL,
    est_detruite          BIT NOT NULL CONSTRAINT DF_Oeuvres_Detruite DEFAULT 0,
    id_lieu               INT NOT NULL CONSTRAINT FK_Oeuvres_Lieux REFERENCES Lieux(id_lieu),
    id_oeuvre_recouverte  INT NULL CONSTRAINT FK_Oeuvres_Recouvre REFERENCES Oeuvres(id_oeuvre)
);
-- Une œuvre ne peut être recouverte que par UNE autre : unicité sur les valeurs non nulles
CREATE UNIQUE INDEX UX_Oeuvres_Recouverte ON Oeuvres(id_oeuvre_recouverte) WHERE id_oeuvre_recouverte IS NOT NULL;
 
CREATE TABLE Oeuvre_Artiste (
    id_oeuvre  INT NOT NULL CONSTRAINT FK_OA_Oeuvre  REFERENCES Oeuvres(id_oeuvre)  ON DELETE CASCADE,
    id_artiste INT NOT NULL CONSTRAINT FK_OA_Artiste REFERENCES Artistes(id_artiste),
    CONSTRAINT PK_Oeuvre_Artiste PRIMARY KEY (id_oeuvre, id_artiste)
);
 
CREATE TABLE Oeuvre_Technique (
    id_oeuvre    INT NOT NULL CONSTRAINT FK_OT_Oeuvre    REFERENCES Oeuvres(id_oeuvre) ON DELETE CASCADE,
    id_technique INT NOT NULL CONSTRAINT FK_OT_Technique REFERENCES Techniques(id_technique),
    CONSTRAINT PK_Oeuvre_Technique PRIMARY KEY (id_oeuvre, id_technique)
);
 
CREATE TABLE Photos (
    id_cliche      INT IDENTITY(1,1) CONSTRAINT PK_Photos PRIMARY KEY,
    date_prise_vue DATETIME2(0) NOT NULL,
    nom_fichier    NVARCHAR(255) NOT NULL,
    etat_oeuvre    NVARCHAR(500) NULL,
    id_oeuvre      INT NOT NULL CONSTRAINT FK_Photos_Oeuvre   REFERENCES Oeuvres(id_oeuvre),
    id_benevole    INT NOT NULL CONSTRAINT FK_Photos_Benevole REFERENCES Benevoles(id_benevole)
);
GO
 
/* ---------- Jeu de données d'essai -------------------------------------------- */
INSERT INTO Artistes (pseudonyme, nom_reel, nationalite) VALUES
 (N'C215', N'Christian Guémy', N'Française'),
 (N'Invader', NULL, N'Française'),
 (N'Kalouf', NULL, NULL);
 
INSERT INTO Lieux (nom, adresse, quartier, latitude, longitude) VALUES
 (N'Mur du marché', N'2 place du Marché', N'Centre', 47.213000, -1.553000),
 (N'Quai des Antilles', NULL, N'Ile de Nantes', 47.200500, -1.573600),
 (N'Escalier Sainte-Anne', N'Rue de l''Hermitage', N'Ste Anne', 47.206000, -1.581000);
 
INSERT INTO Techniques (nom) VALUES (N'Bombe aérosol'), (N'Pochoir'), (N'Collage'), (N'Mosaïque');
 
INSERT INTO Benevoles (nom, prenom, email) VALUES
 (N'Durand', N'Léa', N'lea.durand@exemple.fr'),
 (N'Nguyen', N'Tom', N'tom.nguyen@exemple.fr');
 
INSERT INTO Oeuvres (titre, date_creation, description, id_lieu) VALUES
 (N'Portrait de chat', '2023-05-12', N'Chat au pochoir',  1),
 (N'Space invader NA_01', '2021-09-01', N'Mosaïque',      2),
 (N'Fresque collective', '2024-03-20', NULL,              3);
 
INSERT INTO Oeuvre_Artiste VALUES (1, 1), (2, 2), (3, 1), (3, 3);
INSERT INTO Oeuvre_Technique VALUES (1, 2), (2, 4), (3, 1), (3, 2);
INSERT INTO Photos (date_prise_vue, nom_fichier, etat_oeuvre, id_oeuvre, id_benevole) VALUES
 ('2024-06-01T10:00', N'mur_01.jpg', N'Intacte', 1, 1),
 ('2025-01-15T15:30', N'mur_02.jpg', N'Tag par-dessus', 1, 2),
 ('2024-04-02T09:00', N'fresque_01.jpg', NULL, 3, 2);
GO
 
/* ---------- Les 4 requêtes demandées ------------------------------------------ */
 
-- 1. Annuaire des lieux : adresse et quartier, triés par quartier
SELECT l.adresse, l.quartier
FROM Lieux AS l
ORDER BY l.quartier;
 
-- 2. Fiche artiste : titre, date et pseudonyme des œuvres créées après le 01/01/2023
SELECT o.titre, o.date_creation, a.pseudonyme
FROM Oeuvres AS o
JOIN Oeuvre_Artiste AS oa ON oa.id_oeuvre = o.id_oeuvre
JOIN Artistes AS a        ON a.id_artiste = oa.id_artiste
WHERE o.date_creation > '2023-01-01'
ORDER BY o.date_creation;
 
-- 3. Bilan des techniques : nombre d'œuvres par technique, y compris 0
--    (LEFT JOIN depuis Techniques + COUNT sur une colonne de la table de droite)
SELECT t.nom AS technique, COUNT(ot.id_oeuvre) AS nb_oeuvres
FROM Techniques AS t
LEFT JOIN Oeuvre_Technique AS ot ON ot.id_technique = t.id_technique
GROUP BY t.id_technique, t.nom
ORDER BY nb_oeuvres DESC, t.nom;
 
-- 4. Traçabilité : photographes ayant photographié des œuvres de C215 dans le quartier « Centre »
SELECT DISTINCT b.nom, b.prenom
FROM Benevoles AS b
JOIN Photos AS p          ON p.id_benevole = b.id_benevole
JOIN Oeuvres AS o         ON o.id_oeuvre = p.id_oeuvre
JOIN Lieux AS l           ON l.id_lieu = o.id_lieu
JOIN Oeuvre_Artiste AS oa ON oa.id_oeuvre = o.id_oeuvre
JOIN Artistes AS a        ON a.id_artiste = oa.id_artiste
WHERE a.pseudonyme = N'C215'
  AND l.quartier = N'Centre';
GO

Vue Graphique

Liens retour