Gestion documentaire

[30 min]

Soit le schéma relationnel suivant permettant une gestion électronique de documents (GED) :

1
Users (#login, pwd, name)
2
Documents (#id, title, creator=>Users(login))
3
Versions (#num, #doc=>Documents(id), contrib=>Users(login))

NB : L'on utilisera ni les opérateurs ensemblistes, ni les sous-requêtes.

Vous pouvez tester vos requêtes directement sur votre base de données en l'initialisant avec le fichier de données.

1
/**
2
DROP TABLE Versions ;
3
DROP TABLE Documents ;
4
DROP TABLE Users ;
5
**/
6
7
CREATE TABLE Users (
8
	login VARCHAR PRIMARY KEY,
9
	pwd VARCHAR,
10
	name VARCHAR
11
) ;
12
13
CREATE TABLE Documents (
14
	id INTEGER PRIMARY KEY,
15
	title VARCHAR,
16
	creator VARCHAR REFERENCES Users(login)
17
) ;
18
19
CREATE TABLE Versions (
20
	num INTEGER PRIMARY KEY,
21
	doc INTEGER REFERENCES Documents(id),
22
	contrib VARCHAR REFERENCES Users(login)
23
) ;
24
25
INSERT INTO Users (login, pwd, name) VALUES ('karl', 'marx', 'Karl Marx') ;
26
INSERT INTO Users (login, pwd, name) VALUES ('al', 'ecm2', 'Albert Einstein') ;
27
INSERT INTO Users (login, pwd, name) VALUES ('stc', 'nf17', 'Stéphane Crozat') ;
28
INSERT INTO Users (login, pwd, name) VALUES ('paul', 'mot', 'Paul Pagnol') ;
29
30
INSERT INTO Documents (id, title, creator) VALUES (1, 'Le Capital', 'karl') ;
31
INSERT INTO Documents (id, title, creator) VALUES (2, 'La théorie de la relativité restreinte et générale', 'al') ;
32
INSERT INTO Documents (id, title, creator) VALUES (3, 'Les bases de données', 'stc') ;
33
34
INSERT INTO Versions (num, doc, contrib) VALUES (1, 1, 'karl') ;
35
INSERT INTO Versions (num, doc, contrib) VALUES (2, 1, 'karl') ;
36
INSERT INTO Versions (num, doc, contrib) VALUES (3, 1, 'karl') ;
37
INSERT INTO Versions (num, doc, contrib) VALUES (4, 2, 'al') ;
38
INSERT INTO Versions (num, doc, contrib) VALUES (5, 2, 'stc') ;
39
INSERT INTO Versions (num, doc, contrib) VALUES (6, 3, 'stc') ;
Question
En algèbre relationnel et en SQL, afficher le nom de l'auteur du document dont  le titre est "Le Capital".
Résultat
name
Karl Marx
Question
En algèbre relationnel et en SQL, afficher les logins des users qui sont à la fois creator et contrib et qui commencent par "k".
Résultat
login
karl
...
Question
En algèbre relationnel et en SQL, Afficher les logins des users qui n'ont ni créé, ni contribué.
Résultat
login
paul
...
Question
En SQL, afficher la dernière version (celle qui a le numéro le plus élevé) de chaque document, la liste sera  triée par id des documents croissants.
Résultat
id
last_version
001
2
...
...
Question
En SQL, afficher le nombre de contributions pour chaque utilisateur, en face de leur nom, à condition que l'utilisateur ait contribué au moins 2 fois, la liste sera triée par nombre de contributions décroissant.
Résultat
name
nb_contributions
Karl Marx
14
...
...