Pour répondre à cet exercice, voici la solution complète :
Dossier 1 : Implémentation d'une base de données
1. Script de création de la base de données "GESCAB" :
-- Création de la base de données
CREATE DATABASE GESCAB;
USE GESCAB;
-- Table TEST
CREATE TABLE TEST (
NumTest INT PRIMARY KEY,
intitule VARCHAR(100),
datedebut DATE,
datfin DATE,
CodStr INT
);
-- Table STRUCTURE
CREATE TABLE STRUCTURE (
CodStr INT PRIMARY KEY,
NomStruct VARCHAR(100),
Adresse VARCHAR(255)
);
-- Table CANDIDAT
CREATE TABLE CANDIDAT (
NumCand INT PRIMARY KEY IDENTITY(100, 1), -- Numéros candidats auto-incrémentés à partir de 100
Nom VARCHAR(100),
Prenom VARCHAR(100)
);
-- Table EPREUVE
CREATE TABLE EPREUVE (
NumEpr INT PRIMARY KEY,
DatEpreuve DATE,
duree INT,
NoteMin DECIMAL(4,2) DEFAULT 10, -- Par défaut la note minimum est 10
NumTest INT,
FOREIGN KEY (NumTest) REFERENCES TEST(NumTest)
);
-- Table COMPOSER
CREATE TABLE COMPOSER (
NumCand INT,
NumEpr INT,
Note DECIMAL(4,2) CHECK (Note BETWEEN 0 AND 20), -- La note doit être entre 0 et 20
PRIMARY KEY (NumCand, NumEpr),
FOREIGN KEY (NumCand) REFERENCES CANDIDAT(NumCand),
FOREIGN KEY (NumEpr) REFERENCES EPREUVE(NumEpr)
);
Dossier 2 : Procédures et fonctions stockées
1. Procédure TESTSPER : Cette procédure retourne la liste des épreuves dans une période donnée.
CREATE PROCEDURE TESTSPER(@DateDebut DATE, @DateFin DATE)
AS
BEGIN
SELECT *
FROM EPREUVE
WHERE DatEpreuve BETWEEN @DateDebut AND @DateFin;
END;
2. Procédure TESTSTR : Cette procédure retourne les tests organisés par une structure donnée dans les deux
dernières années.
CREATE PROCEDURE TESTSTR(@CodStr INT)
AS
BEGIN
SELECT T.NumTest, T.intitule, T.datedebut, T.datfin, S.NomStruct
FROM TEST T
JOIN STRUCTURE S ON T.CodStr = S.CodStr
WHERE T.CodStr = @CodStr AND T.datedebut >= DATEADD(YEAR, -2, GETDATE());
END;
3. Fonction NBTEST : Cette fonction retourne le nombre de tests déjà enregistrés.
CREATE FUNCTION NBTEST()
RETURNS INT
AS
BEGIN
DECLARE @nbTests INT;
SELECT @nbTests = COUNT(*) FROM TEST;
RETURN @nbTests;
END;
4. Procédure NBCAND : Cette procédure retourne le nombre de candidats ayant composé pour chaque épreuve
d’un test donné.
CREATE PROCEDURE NBCAND(@NumTest INT)
AS
BEGIN
SELECT E.NumEpr, COUNT(C.NumCand) AS NbCandidats
FROM EPREUVE E
LEFT JOIN COMPOSER C ON E.NumEpr = C.NumEpr
WHERE E.NumTest = @NumTest
GROUP BY E.NumEpr;
END;
5. Procédure LISTCAND : Cette procédure retourne la liste des candidats ayant obtenu une moyenne
supérieure ou égale à 10.
CREATE PROCEDURE LISTCAND(@NumTest INT)
AS
BEGIN
SELECT C.NumCand, C.Nom, C.Prenom, AVG(Co.Note) AS Moyenne
FROM CANDIDAT C
JOIN COMPOSER Co ON C.NumCand = Co.NumCand
JOIN EPREUVE E ON Co.NumEpr = E.NumEpr
WHERE E.NumTest = @NumTest
GROUP BY C.NumCand, C.Nom, C.Prenom
HAVING AVG(Co.Note) >= 10;
END;
Voilà une solution complète pour l'exercice de gestion de la base de données avec SQL Server.
