Voici le code final modifié pour que l'`id` de la table `Matieres` soit de type
`VARCHAR`, ainsi que les ajustements nécessaires pour les clés étrangères.
### 1. Créer un utilisateur `ecole`
```sql
CREATE USER ecole IDENTIFIED BY 'ecole';
GRANT ALL PRIVILEGES TO ecole;
```
### 2. Créer une connexion portant le nom `con_ecole`
```sql
CREATE DATABASE LINK con_ecole
CONNECT TO ecole IDENTIFIED BY 'ecole';
```
### 3. Créer toutes les tables de la base de données
```sql
CREATE TABLE Classes (
id INT PRIMARY KEY,
nom VARCHAR(100)
);
CREATE TABLE Etudiants (
id INT PRIMARY KEY,
nom VARCHAR(100),
prenom VARCHAR(100),
date_naissance DATE,
sexe CHAR(1),
email VARCHAR(100),
telephone VARCHAR(15),
classe_id INT,
FOREIGN KEY (classe_id) REFERENCES Classes(id)
);
CREATE TABLE Matieres (
id VARCHAR(10) PRIMARY KEY,
nom VARCHAR(100)
);
CREATE TABLE Enseigner (
classe_id INT,
matiere_id VARCHAR(10),
coefficient INT,
PRIMARY KEY (classe_id, matiere_id),
FOREIGN KEY (classe_id) REFERENCES Classes(id),
FOREIGN KEY (matiere_id) REFERENCES Matieres(id)
);
CREATE TABLE Obtenir (
etudiant_id INT,
matiere_id VARCHAR(10),
note FLOAT,
PRIMARY KEY (etudiant_id, matiere_id),
FOREIGN KEY (etudiant_id) REFERENCES Etudiants(id),
FOREIGN KEY (matiere_id) REFERENCES Matieres(id)
);
```
### 4. Créer deux séquences auto incrémentales
```sql
CREATE SEQUENCE seq_etudiants START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_classes START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trg_etudiants
BEFORE INSERT ON Etudiants
FOR EACH ROW
BEGIN
:new.id := seq_etudiants.NEXTVAL;
END;
CREATE OR REPLACE TRIGGER trg_classes
BEFORE INSERT ON Classes
FOR EACH ROW
BEGIN
:new.id := seq_classes.NEXTVAL;
END;
```
### 5. Créer une séquence pour `Matières`
```sql
CREATE SEQUENCE seq_matieres START WITH 100 INCREMENT BY 10;
CREATE OR REPLACE TRIGGER trg_matieres
BEFORE INSERT ON Matieres
FOR EACH ROW
BEGIN
:new.id := 'M' || seq_matieres.NEXTVAL;
END;
```
### 6. Identifier les autres contraintes
```sql
ALTER TABLE Etudiants ADD CONSTRAINT chk_sexe CHECK (sexe IN ('M', 'F'));
ALTER TABLE Etudiants ADD CONSTRAINT uq_email UNIQUE (email);
```
### 7. Insérer des données dans `Classes` et `Matieres`
```sql
INSERT INTO Classes (id, nom) VALUES (1, 'Classe A'), (2, 'Classe B');
INSERT INTO Matieres (id, nom) VALUES ('M100', 'Mathématiques'),
('M110', 'Physique'),
('M120', 'Chimie'),
('M130', 'Biologie'),
('M140', 'Informatique');
```
### 8. Déclencheur pour insérer dans `Obtenir`
```sql
CREATE OR REPLACE TRIGGER trg_obtenir
AFTER INSERT ON Etudiants
FOR EACH ROW
DECLARE
v_matiere_id VARCHAR(10);
BEGIN
FOR matiere IN (SELECT matiere_id FROM Enseigner WHERE classe_id
= :new.classe_id) LOOP
INSERT INTO Obtenir (etudiant_id, matiere_id, note)
VALUES (:new.id, matiere.matiere_id, 0);
END LOOP;
END;
```
### 9. Insérer dans la table `Etudiants`
```sql
INSERT INTO Etudiants (nom, prenom, date_naissance, sexe, email, telephone,
classe_id)
VALUES ('Dupont', 'Jean', TO_DATE('2000-01-01', 'YYYY-MM-DD'), 'M',
'
[email protected]', '0123456789', 1),
('Durand', 'Alice', TO_DATE('2000-02-01', 'YYYY-MM-DD'), 'F',
'
[email protected]', '0123456780', 1),
('Martin', 'Pierre', TO_DATE('2000-03-01', 'YYYY-MM-DD'), 'M',
'
[email protected]', '0123456781', 1),
('Leblanc', 'Sophie', TO_DATE('2000-04-01', 'YYYY-MM-DD'), 'F',
'
[email protected]', '0123456782', 2);
```
### 10. Créer deux vues
a. Pour la moyenne de tous les étudiants d'une classe
```sql
CREATE VIEW vue_moyenne_classes AS
SELECT c.id AS classe_id, AVG(o.note * en.coefficient) / SUM(en.coefficient) AS
moyenne
FROM Obtenir o
JOIN Etudiants e ON o.etudiant_id = e.id
JOIN Enseigner en ON en.matiere_id = o.matiere_id AND e.classe_id = en.classe_id
JOIN Classes c ON c.id = e.classe_id
GROUP BY c.id;
```
b. Pour un étudiant donné, son bulletin
```sql
CREATE VIEW vue_bulletin_etudiant AS
SELECT e.nom, e.prenom, m.nom AS matiere, o.note, (o.note * en.coefficient) AS
note_coefficée
FROM Obtenir o
JOIN Etudiants e ON o.etudiant_id = e.id
JOIN Matieres m ON o.matiere_id = m.id
JOIN Enseigner en ON m.id = en.matiere_id AND e.classe_id = en.classe_id
WHERE e.id = :etudiant_id; -- Remplacez :etudiant_id par l'ID de l'étudiant pour
tester
```
### 11. Écrire une fonction moyenne
```sql
CREATE OR REPLACE FUNCTION calc_moyenne(etudiant_id INT) RETURN FLOAT IS
v_moyenne FLOAT;
BEGIN
SELECT AVG(o.note * en.coefficient) / SUM(en.coefficient) INTO v_moyenne
FROM Obtenir o
JOIN Enseigner en ON o.matiere_id = en.matiere_id
WHERE o.etudiant_id = etudiant_id;
RETURN v_moyenne;
END;
```
Tester la fonction :
```sql
SELECT calc_moyenne(1) FROM dual; -- Remplacez 1 par l'ID de l'étudiant à tester
```
### 12. Écrire une procédure stockée pour supprimer un étudiant
```sql
CREATE OR REPLACE PROCEDURE supprimer_etudiant(etudiant_id INT) IS
BEGIN
DELETE FROM Obtenir WHERE etudiant_id = etudiant_id;
DELETE FROM Etudiants WHERE id = etudiant_id;
END;
```
Voilà le code final avec les modifications nécessaires. Si vous avez d'autres
questions ou besoin d'aide, n'hésitez pas à demander !
