jb06A2cS

1SET search_path TO fakenewscorpus;
2
3DROP TABLE IF EXISTS content;
4DROP TABLE IF EXISTS author;
5DROP TABLE IF EXISTS keywords;
6DROP TABLE IF EXISTS tags;
7DROP TABLE IF EXISTS article;
8
9CREATE TABLE article
10(
11    id serial not null
12        constraint article_pkey
13            primary key,
14    title      varchar(100),
15    written_at date,
16    scraped_at timestamp,
17    summary    varchar(2000),
18    type       varchar(50)
19        constraint allowed_types
20            check ((type)::text = ANY ('{fake,reliable,unreliable,hate,political,conspiracy}'::text[]))
21);
22
23CREATE TABLE author (
24    author_id INT,
25    text varchar(50),
26    foreign key (author_id) references article(id)
27);
28
29CREATE TABLE content (
30    content_id INT,
31    phrase varchar(50),
32    foreign key (content_id) references article(id)
33);
34
35
36CREATE TABLE tags (
37    tags_id INT,
38    text varchar(50),
39    foreign key (tags_id) references article(id)
40);
41
42CREATE TABLE keywords (
43    keywords_id INT,
44    text varchar(50),
45    foreign key (keywords_id) references article(id)
46);
47
48
49
50-- Insert Article
51INSERT INTO article(title, written_at, scraped_at, summary, type)
52VALUES('Theo', '11/03/2021', '2021-03-11', 'xd', 'conspiracy'),
53       ('Mads', '11/03/2021', '2021-03-11', 'xd', 'unreliable'),
54       ('Peter', '11/03/2021', '2021-03-11', 'xd', 'hate');
55
56-- Insert Content
57INSERT INTO content(content_id, phrase)
58VALUES (1, 'Jeg heder Theo'), (2, 'Jeg heder Mads'), (3,'Jeg heder Peter'), (1, 'Jeg heder ikke Mads');
59
60-- Insert Tags
61INSERT INTO tags(tags_id, text)
62VALUES (1, 'porn, trump, capitalism, market, fakenews'), (2, 'markzuckerberg'), (3, 'Bill Gates');
63
64-- Insert Author
65INSERT INTO author(author_id, text)
66VALUES (1, 'Theo'), (2, 'Mads'), (3,'Peter'), (1, 'Ikke Mads');
67
68-- Insert Keywords
69INSERT INTO keywords(keywords_id, text)
70VALUES (1, 'C#'), (2, 'IDK'), (3,'#Matematikeren'), (1, '#IkkeC#');
71
72
73
74
75SELECT id, count(*) from article
76JOIN author
77    ON author_id = id
78JOIN tags
79    ON tags_id = id
80JOIN keywords
81    ON keywords_id = id
82JOIN content
83    ON content_id = id
84GROUP BY (id) ORDER BY ID asc
85
86