1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105
| CREATE TABLE dbo.matiere
(
id_matiere tinyint NOT NULL IDENTITY(0,1)
CONSTRAINT PK_matiere PRIMARY KEY
, nom_matiere varchar(64) NOT NULL
CONSTRAINT UQ_matiere__nom_matiere UNIQUE
)
GO
INSERT INTO dbo.matiere
(
nom_matiere
)
VALUES ('Physique-Chimie')
, ('Sciences et Vie de la Terre')
GO
CREATE TABLE dbo.professeur
(
id_professeur smallint NOT NULL IDENTITY
CONSTRAINT PK_professeur PRIMARY KEY
, licence_professeur varchar(16) NOT NULL
CONSTRAINT UQ_professeur__licence_professeur UNIQUE
, id_matiere tinyint NOT NULL
CONSTRAINT FK_professeur__id_matiere
FOREIGN KEY (id_matiere) REFERENCES dbo.matiere
)
GO
INSERT INTO dbo.professeur
(
licence_professeur
, id_matiere
)
VALUES ('PC2015-001', 0)
, ('SVT2014-001', 1)
, ('PC2015-002', 0)
, ('SVT2015-001', 1)
GO
CREATE TABLE dbo.classe
(
id_classe int NOT NULL IDENTITY
CONSTRAINT PK_classe PRIMARY KEY
, nom_classe varchar(16)
CONSTRAINT UQ_classe__nom_classe UNIQUE
)
GO
INSERT INTO dbo.classe
(
nom_classe
)
VALUES ('Seconde'), ('Première'), ('Terminale')
GO
CREATE TABLE dbo.classe_professeur
(
id_classe_professeur int NOT NULL IDENTITY
CONSTRAINT PK_classe_professeur PRIMARY KEY
, id_classe int NOT NULL
CONSTRAINT FK_classe_professeur__id_classe
FOREIGN KEY (id_classe) REFERENCES dbo.classe
, id_professeur smallint NOT NULL
CONSTRAINT FK_classe_professeur__id_professeur
FOREIGN KEY (id_professeur) REFERENCES dbo.professeur
, CONSTRAINT UQ_classe_professeur__id_classe__id_professeur
UNIQUE (id_classe, id_professeur)
)
GO
CREATE FUNCTION fn_check_classe_professeur
(
@_id_classe int
, @id_professeur smallint
)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
DECLARE @ok bit = 1;
IF EXISTS
(
SELECT CP.id_classe
, M.id_matiere
FROM dbo.classe_professeur AS CP
INNER JOIN dbo.professeur AS P
ON P.id_professeur = CP.id_professeur
INNER JOIN dbo.matiere AS M
ON P.id_matiere = M.id_matiere
GROUP BY CP.id_classe, M.id_matiere HAVING COUNT(*) > 1
)
BEGIN
SET @ok = 0;
END
RETURN @ok;
END
GO
ALTER TABLE dbo.classe_professeur
ADD CONSTRAINT CHK_classe_professeur__id_classe__id_professeur
CHECK (dbo.fn_check_classe_professeur(id_classe, id_professeur) = 1)
GO |
Partager