u9JBY9rv

1-----------------------------------------------------------
2-- Drop old schema and create new one
3-----------------------------------------------------------
4
5DROP SCHEMA IF EXISTS public CASCADE;
6CREATE SCHEMA public;
7ALTER SCHEMA public OWNER TO ricardo;
8COMMENT ON SCHEMA public IS 'standard public schema';
9
10-----------------------------------------------------------
11-- Types
12-----------------------------------------------------------
13
14-----------------------------------------------------------
15-- Tables
16-----------------------------------------------------------
17
18-- All authors.
19CREATE TABLE "author" (
20    author_id           SERIAL PRIMARY KEY,
21    name                TEXT NOT NULL,
22    country             TEXT NOT NULL -- Maybe better to use a 3 letter code in order to use VARCHAR(3)
23);
24
25-- All papers
26CREATE TABLE "paper" (
27    paper_id            SERIAL PRIMARY KEY,
28    title               TEXT NOT NULL,
29    author_id           INTEGER NOT NULL,
30    publication_date    DATE NOT NULL,
31    conference          TEXT NOT NULL, -- Not sure about the not null
32
33    FOREIGN KEY(author_id) REFERENCES
34    "author"(author_id) ON DELETE CASCADE
35);
36
37-- All patterns
38CREATE TABLE "pattern" (
39    pattern_id          SERIAL PRIMARY KEY,
40    name                TEXT NOT NULL
41);
42
43-- Connections between papers and patterns
44CREATE TABLE "pattern_papers" (
45    pattern_id          INTEGER NOT NULL,
46    paper_id            INTEGER NOT NULL,
47
48    PRIMARY KEY (pattern_id, paper_id)
49);
50
51-- All keywords
52CREATE TABLE "keyword" (
53    keyword_id          SERIAL PRIMARY KEY,
54    name                TEXT NOT NULL,
55    paper_id            INTEGER, 
56    pattern_id          INTEGER,
57
58    FOREIGN KEY(paper_id) REFERENCES
59    "paper"(paper_id) ON DELETE CASCADE,
60
61    FOREIGN KEY(pattern_id) REFERENCES
62    "pattern"(pattern_id) ON DELETE CASCADE
63);
64
65INSERT INTO author (name, country) VALUES ('Raul Vidal', 'Portugal');
66INSERT INTO paper (title, author_id, publication_date, conference) VALUES ('Uma história de vida', 1, '17/03/2020', 'FEUP');
67INSERT INTO pattern (name) VALUES ('Repository');
68INSERT INTO pattern_papers (pattern_id, paper_id) VALUES (1, 1);
69INSERT INTO keyword (name, paper_id) VALUES ('Inspiring', 1);
70INSERT INTO keyword (name, pattern_id) VALUES ('Database pattern', 1);