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
| CREATE TABLE identifiant
( identifiant NUMBER IDENTITY CONSTRAINT PRIMARY KEY
, nom VARCHAR(128) CONSTRAINT UNIQUE
)
;
CREATE TABLE mot_de_passe
( identifiant NUMBER CONSTRAINT PRIMARY KEY
, mot_de_passe VARCHAR(128)
, CONSTRAINT FOREIGN KEY identifiant REFERENCES identifiant(identifiant)
)
;
CREATE VIEW utilisateur
AS SELECT idt.identifiant AS id_user
, idt.nom AS nom_user
, pwd.mot_de_passe
FROM identifiant AS idt
INNER JOIN
mot_de_passe AS pwd
ON idt.identifiant = pwd.identifiant
;
CREATE VIEW groupe
AS SELECT idt.identifiant AS id_groupe
, idt.nom AS nom_groupe
FROM identifiant AS idt
WHERE NOT EXISTS
( SELECT NULL
FROM mot_de_passe AS pwd
WHERE idt.identifiant = pwd.identifiant
)
; |
Partager