Conception d’une base de données relationnelle
Modèle E/R
Schéma relationnel :
Professeur(NumProf, NomProf, Salaire, Bureau)
Module(NumMod, NomMod, #NumProfRef) ;
NumProfRef : clé étrangère Professeur(NumProf)
Etudiant(NumEt, Nom, Prenom, DateNaiss)
Inscrit(#NumEtu, #NumMod, Note) ;
NumEtu : clé étrangère Etudiant(NumEtu)
NumMod : clé étrangère Module(NumMod)
1
Schéma de Base de données : CPI2
DDL
Professeur
NumProf NomProf Salaire Bureau
Module
NumMod NomMod NumProfRef
Etudiant
NumEtu Nom Prenom DateNaiss
Inscrit
NumEtu NomMod Note
2
Base de données : CPI2
DML
Professeur
NumProf NomProf Salaire Bureau
1 MAHBOUB 30000 E306
2 YOUSSFI 20000 E404
3 EL YOUNOUSSSI 15000 E302
4 Sabri 25000 E320
Module
NumMod NomMod NumProfRef
1 JAVA 2
2 Base de données 3
3 PHP 1
4 UML 2
Etudiant
NumEtu NomEtu PrenomEtu DateNaiss
103 TAQI NOUHA 01/12/2003
106 NAJIB MAROUA 10/11/2003
108 MIFTAH Omar 02/01/2004
110 ETTALBI AYMANE 05/06/2003
Inscrit
NumEtu NumMod Note
103 3 15
103 2 16
108 2 12
108 1 10
3
PARTIE I - DDL : Requête SQL qui permet de créer le schéma de la base de données
Création de la base de données :
CREATE DATABASE IF NOT EXISTS CPI2
Création de la table professeur (Ne spécifiez pas que NumProf est une clé primaire) :
CREATE TABLE Professeur(
NumProf INTEGER,
NomProf VARCHAR(50),
Bureau VARCHAR(10),
Salaire FLOAT
);
Pour créer la contrainte clé primaire sur la colonne "NumProf" alors que la table professeur
est déjà créée :
Alter TABLE professeur
ADD PRIMARY KEY(NumProf);
Création de la table module :
CREATE TABLE Module(
NumMod INTEGER PRIMARY KEY,
NomMod VARCHAR(50),
NumProfRef Integer,
FOREIGN KEY (NumProfRef) REFERENCES professeur(NumProf)
);
Création de la table etudiant :
CREATE TABLE Etudiant(
NumEtu INTEGER,
NomEtu VARCHAR(50),
PrenomEtu VARCHAR(80),
DateNaiss DATE,
PRIMARY KEY(NumEtu)
);
Création de la table inscrit
CREATE TABLE Inscrit(
NumEtu INTEGER,
NumMod Integer,
Note FLOAT,
FOREIGN KEY (NumEtu) REFERENCES Etudiant(NumEtu),
FOREIGN KEY (NumMod) REFERENCES module(NumMod),
4
Primary key (NumEtu, NumMod));
Ajouter la colonne "grade" dans la table professeur
ALTER TABLE professeur
ADD grade Varchar(20);
Modifier le type de la colonne "grade"
ALTER TABLE professeur
MODIFY grade Varchar(3);
Supprimer la colonne "grade" de la table professeur
ALTER TABLE professeur
DROP COLUMN grade;
PARTIE II - DML : Requête SQL qui permet d’ajouter des enregistrements dans la
base de données
Insérer des enregistrements dans la base de données :
INSERT INTO professeur (NumProf, NomProf, Salaire, Bureau)
VALUES(1, "MAHBOUB", 30000, "E306"),
(2, "YOUSSFI", 20000, "E404"),
(3, "EL YOUNOUSSSI", 15000, "E302"),
(4, "Sabri", 25000, "E320");
INSERT INTO Module
VALUES(1, "JAVA", 2),
(2, "Base de données", 3),
(3, "PHP", 1),
(4, "UML", 2);
INSERT INTO etudiant
VALUES(103, "TAQI", "NOUHA", "2003-12-01"),
(106, "NAJIB", "MAROUA", "2003-11-10"),
(108, "MIFTAH", "Omar", "2004-01-02"),
(110, "ETTALBI", "AYMANE", "2003-06-05");
INSERT INTO Inscrit
VALUES(103, 3, 15),
(103, 2, 16),
(108, 2, 12),
(108, 1, 10);
5
PARTIE II – DQL
Afficher les notes et les modules de l’étudiante "TAQI NOUHA"
SELECT NomMod, Note from inscrit NATURAL JOIN module NATURAL Join etudiant
WHERE nomEtu = "Taqi" and PrenomEtu = "Nouha";
Afficher les professeurs de l’étudiant "MIFTAH OMAR"
SELECT nomProf from professeur Join module on numProf = numProfRef NATURAL JOIN inscrit
NATURAL JOIN etudiant WHERE nom = "Miftah" and prenom = "OMAR"
Afficher le nombre des étudiants
Select count(*) from etudiant
Afficher le nombre des étudiants inscrits dans chaque module
SELECT nomMod, count(*) from module Natural join inscrit Natural join etudiant
Group By numMod
Afficher le nombre des étudiants inscrits dans chaque module et qui ont une note > 12
SELECT nomMod, count(*) from module Natural join inscrit Natural join etudiant
Where note > 12
Group By numMod
Afficher le nom, le prénom et la note moyenne de chaque étudiant
Select nomEtu, prenomEtu, AVG (note) from etudiant natural join inscrit
Group By numEtu
Afficher le nom, le prénom et la note de l'étudiant qui a obtenu la meilleure note
Select nomEtu, prenomEtu, note from etudiant natural join inscrit
Where note > All (select note from inscrit)
Afficher le nom des professeurs qui n'enseignent aucun module.
Select nomProf from professeur Left Outer Join module on numProf = numProfRef
Where numMod is null
SELECT nomProf from professeur
WHERE NumProf NOT in (SELECT numProfRef from module)
Afficher le nom des modules enseignés par un professeur avec un salaire supérieur à 20000
Select nomMod from module natural join professeur on numProf = numProfRef
Where numProf In ( Select numProf from professeur where salaire > 20000)