CREATE TABLE tags (
       id integer NOT NULL PRIMARY KEY,
       name varchar(255),
       description text
);

CREATE TABLE files (
       id integer NOT NULL PRIMARY KEY,
       canonical_name varchar(255),
       full_name varchar(255),
       description text,
       date varchar(255)
);

CREATE TABLE file_tags (
       file integer,
       tag integer,
       FOREIGN KEY(file) REFERENCES files(id),
       FOREIGN KEY(tag)  REFERENCES tags(id)
);

INSERT INTO tags VALUES(0, 'inria', 'Document lié à Inria');
INSERT INTO tags VALUES(1, 'enseirb', 'Document lié à Enseirb');

INSERT INTO files VALUES(0, 'contrat_these.pdf', 'Contrat de travail pour la thèse.');

INSERT INTO file_tags VALUES(0 ,0);

-- Query: all files related to Inria
SELECT name, description FROM (
       files JOIN file_tags ON files.id = file_tags.file
) WHERE file_tags.tag = 0;
