ebd

EBD: Database Specification Component

Assembling a community-driven database of knowledge by asking and answering questions.

A4: Conceptual Data Model

This component describes the artefacts that will support the storage and retrival of information described in the requirements specification.

1. Class diagram

UML conceptual data model

A5: Relational Schema, validation and schema refinement

The goal of this artefact is to present the relational schema obtained from the UML along with the SQL code.

1. Relational Schema

The following Relational Schema is in the compact notation. It includes the relation schemas, attributes, domains, primary keys, foreign keys, and other integrity rules, such as UNIQUE, DEFAULT, NOT NULL, and CHECK.

Relation reference	Relation Compact Notation
R01	user(id, name, username UK, password, email UK, about, picture UK)
R02	moderator(id -> user)
R03	administrator(id -> moderator)
R04	ban(id_user -> user, id_admin -> administrator, date NN DF Today CK date <= today, reason)
R05	news(id, author -> administrator NN, title NN, body, date NN DF Today CK date <= today)
R06	topic_proposal(id, id_user -> user NN , id_admin -> administrator, topic_name NN, date NN DF Today CK date <= today, reason, accepted NN DF false)
R07	achievement(id, title NN UK, body NN)
R08	achieved(id_user -> User, id_achievement -> achievement, date NN DF Today CK date <= today)
R09	post(id, id_owner -> user NN, body NN, date NN DF Today CK date <= today)
R10	answer(id -> post)
R11	question(id -> post, accepted_answer -> answer, title UK NN, bounty NN CK bounty>=0 AND bounty <= 500, closed NN DF false
R12	answer_question(id_answer -> answer, id_question -> question)
R13	comment(id -> post, id_question -> question, id_answer -> answer CK (id_question=NULL and id_answer!=NULL) or (id_answer=NULL and id_question!=NULL)
R14	vote(id_post -> post, id_user -> user, value CK value=1 OR value=-1)
R15	edit_proposal(id, id_post -> post NN, id_user -> user NN, id_moderator -> moderator, body NN, accepted NN DF false)
R16	topic(id, name NN UK)
R17	topic_question(id_topic -> topic, id_question -> question)
R18	report(id_post -> post, reporter -> user, date NN DF Today CK date <= today, reason, state NN DF Pending, reviewer -> moderator)
R19	notification(id, date NN DF Today CK date <= today, title NN, body, recipient -> user NN)
R20	notification_achievement(id -> notification, id_achievement -> achievement NN)
R21	notification_post(id -> notification, id_post -> post NN)

* These are null when a user deletes its account.

1.1 Generalisation Mapping

The E/R style was chosen to map Users, Moderators and Admins. It was chosen because it allows hierarchization in a direct way. Ex: A moderator reviewing a topic proposal can either be a moderator or an administrator, and this type of schema allows for a direct and effiecent query.

The E/R style was also chosen to map Post, Quesiton, Answer and Comment, since this generalization is overlapping and has a decent number of subclasses.

Lastly, the E/R method was used aswell when mapping the Notification hierarchy because it is overlapping.

2. Domains

Domain Name	Domain Specification
Today	DATE DEFAULT CURRENT_DATE
ReportState	ENUM ('Pending', 'Approved', 'Rejected')

3. Schema validation

To validate the Relational Schema obtained from the Conceptual Model, all functional dependencies are identified and the normalization of all relation schemas is accomplished.

TABLE R01	user
Keys	{ id }, { email }, { username }
Functional Dependencies:
FD0101	id → {email, name, password, about, picture}
NORMAL FORM	BCNF

TABLE R02	moderator
Keys	{ id }
Functional Dependencies:	---
NORMAL FORM	BCNF

TABLE R03	administrator
Keys	{ id }
Functional Dependencies:	---
NORMAL FORM	BCNF

TABLE R04	ban
Keys	{ id_user }
Functional Dependencies:
FD0401	id_user → {id_admin, date, reason}
NORMAL FORM	BCNF

TABLE R05	news
Keys	{ id }
Functional Dependencies:
FD0501	id → {author, title, body, date}
NORMAL FORM	BCNF

TABLE R06	topic_proposal
Keys	{ id }
Functional Dependencies:
FD0601	id → {id_user, id_admin, topic_name, date, reason, accepted}
NORMAL FORM	BCNF

TABLE R07	achievement
Keys	{ id, title }
Functional Dependencies:
FD0701	id → {title, body}
FD0702	title → {id}
NORMAL FORM	BCNF

TABLE R08	achieved
Keys	{ {id_user, id_achievement} }
Functional Dependencies:
FD0801	id_user, id_achievement → {date}
NORMAL FORM	BCNF

TABLE R09	post
Keys	{ id }
Functional Dependencies:
FD0901	id → {id_owner, body, date}
NORMAL FORM	BCNF

TABLE R10	answer
Keys	{ id }
Functional Dependencies:
NORMAL FORM	BCNF

TABLE R11	question
Keys	{ id, title }
Functional Dependencies:
FD1401	id → {accepted_answer, title, bounty, closed}
FD1402	title → {id}
NORMAL FORM	BCNF

TABLE R12	answer_question
Keys	{ {id_answer, id_question} }
Functional Dependencies:
NORMAL FORM	BCNF

TABLE R13	comment
Keys	{ id }
Functional Dependencies:
FD1301	id → {id_question, id_answer}
NORMAL FORM	BCNF

TABLE R14	vote
Keys	{ {id_post, id_user} }
Functional Dependencies:
FD1001	id_post, id_user → {value}
NORMAL FORM	BCNF

TABLE R15	edit_proposal
Keys	{ id }
Functional Dependencies:
FD1101	id → {id_post, id_user, id_moderator, body, accepted}
NORMAL FORM	BCNF

TABLE R16	topic
Keys	{ id, name }
Functional Dependencies:
FD1501	id → {name}
FD1502	name → {id}
NORMAL FORM	BCNF

TABLE R17	topic_question
Keys	{ {id_topic, id_question} }
Functional Dependencies:	---
NORMAL FORM	BCNF

TABLE R18	report
Keys	{ {id_post, reporter} }
Functional Dependencies:
FD1701	id_post, reporter → {date, reason, state, reviewer}
NORMAL FORM	BCNF

TABLE R19	notification
Keys	{ id }
Functional Dependencies:
FD1801	id → {date, title, body, recipient}
NORMAL FORM	BCNF

TABLE R20	notification_achievement
Keys	{ id }
Functional Dependencies:
FD1901	id → {id_achievement}
NORMAL FORM	BCNF

TABLE R21	notification_post
Keys	{ id }
Functional Dependencies:
FD2001	id → {id_post}
NORMAL FORM	BCNF

Justification of the BCNF:
The schema is in the Boyce–Codd Normal Form (BCNF) because all its relations are also in the BCNF.

A6: Indexes, triggers, user functions, transactions and population

This artefact contains tuple estimations, the most frequent queries, most frequent updates, indices, triggers and transactions.

1. Database Workload

1.1. Tuple Estimation

Relation reference	Relation Name	Order of magnitude	Estimated growth
R01	user	tens of thousands	dozens per day
R02	moderator	hundreds	units per week
R03	administrator	dozens	units per month
R04	ban	hundreds	units per week
R05	news	dozens	units per month
R06	topic_proposal	hundreds	units per week
R07	achievement	dozens	units per year
R08	achieved	tens of thousands	units per day
R09	post	hundreds of thousands	dozens per day
R10	answer	tens of thousands	dozens per day
R11	question	tens of thousands	dozens per day
R12	answer_question	tens of thousands	units per day
R13	comment	tens of thousands	units per day
R14	vote	hundreds of thousands	hundreds per day
R15	edit_proposal	hundreds	units per day
R16	topic	hundreds	units per month
R17	topic_question	tens of thousands	dozens per day
R18	report	thousands	units per day
R19	notification	hundreds of thousands	hundreds per day
R20	notification_achievement	hundreds of thousands	dozens per day
R21	notification_post	tens of thousands	dozens per day

1.2. Frequent Queries

Query	SELECT01
Description	Search question by topic
Frequency	hundreds per day

SELECT post.id, question.title, ts_rank(topic.search, query) AS rank
FROM question JOIN post ON(question.id = post.id), topic JOIN topic_question ON(topic.id = topic_question.id_topic),
  to_tsquery($topic_query) as query
WHERE question.id = topic_question.id_question AND
  topic.search @@ query
ORDER BY rank;

Query	SELECT02
Description	Search question by its content and owner
Frequency	hundreds per day

SELECT "user".id, name, username, email, about, picture, reputation, 
	ts_rank_cd("user".search, user_query) AS user_rank, ts_rank_cd(question.search, question_query) AS question_rank
FROM "user" JOIN post ON(post.id_owner = "user".id) JOIN question ON(question.id = post.id),
	to_tsquery($user_query) as user_query, to_tsquery($question_query) as question_query
WHERE "user".search @@ user_query AND
  question.search @@ question_query
ORDER BY question_rank, user_rank;

Query	SELECT03
Description	Get answers of a question
Frequency	hundreds per day

SELECT answer.id, post.body, post.date
FROM answer
	JOIN answer_question
    	ON(answer_question.id_answer = answer.id)
	JOIN post ON post.id = answer.id
WHERE answer_question.id_question = $idQuestion;

Query	SELECT04
Description	Get comments of a question
Frequency	hundreds per day

SELECT comment.id, comment_post.body
FROM comment JOIN post AS comment_post USING(id)
WHERE comment.id_question = $idQuestion;

Query	SELECT05
Description	Get comments of an answer
Frequency	hundreds per day

SELECT comment.id, comment_post.body
FROM comment JOIN post AS comment_post USING(id)
WHERE comment.id_answer = $idAnswer

User Related Queries

Query	SELECT06
Description	Get User Information
Frequency	hundreds per day

SELECT id, name, username, email, about, picture, reputation
FROM "user"
WHERE "user".id = $userId;

Query	SELECT07
Description	Get User Achievements
Frequency	dozens per month

SELECT achievement.title, achievement.body
FROM Achievement
  JOIN Achieved ON (Achievement.id = Achieved.id_achievement)
WHERE user_id = $userId;

Query	SELECT08
Description	Get User Notifications
Frequency	hundreds per month

SELECT id, title, body
FROM notification
WHERE notification.recipient = $userId;

Query	SELECT09
Description	Get Pending Topic Proposals
Frequency	dozens per month

SELECT id, topic_name, date, reason
FROM topic_proposal
WHERE accepted = false;

Query	SELECT10
Description	Get All Pending Reports
Frequency	dozens per month

SELECT id_post, reporter, date, reason
FROM report
WHERE state = 'Pending';

Query	SELECT11
Description	Get All Posts within a date range
Frequency	dozens per day

SELECT body, score, date
FROM Post
WHERE date between $start_date AND $end_date

Query	SELECT12
Description	Get All Posts of a User
Frequency	dozens per day

SELECT body, score, date
FROM Post
WHERE id_owner = $id_user;

Query	SELECT13
Description	Search user by his personal information
Frequency	units per day

SELECT "user".id, name, username, email, about, picture, reputation
FROM "user" JOIN post ON(post.id_owner = "user".id) JOIN question ON(question.id = post.id)
WHERE "user".search @@ to_tsquery($user_info);

1.3. Frequent Updates

Query	UPDATE01
Description	Accept an answer
Frequency	dozens per month

UPDATE question SET
  accepted_answer = $accepted_answer, closed = TRUE
  WHERE id = $id

Query	UPDATE02
Description	Update user information
Frequency	dozens per month

UPDATE "user" SET
  name = $name, about = $about, picture = $picture
  WHERE id = $id

Query	UPDATE03
Description	Update vote
Frequency	hundreds per day

UPDATE vote SET
  value = $value
  WHERE id_post = $id_post AND id_user = $id_user

1.4. Frequent Inserts

Query	INSERT01
Description	Creates new post
Frequency	dozens per day

INSERT INTO post (id_owner, body) VALUES
  ($id_user, $body)

Query	INSERT02
Description	Create new question
Frequency	dozens per day

INSERT INTO question (id, title, bounty) VALUES
  ($id, $title, $bounty)

Query	INSERT03
Description	Create new answer
Frequency	dozens per day

INSERT INTO answer (id) VALUES
  ($id)

Query	INSERT04
Description	Create new comment to a question
Frequency	units per day

INSERT INTO comment (id, id_question) VALUES
  ($id, $id_question)

Query	INSERT05
Description	Create new comment to an answer
Frequency	units per day

INSERT INTO comment (id, id_answer) VALUES
  ($id, $id_answer)

Query	INSERT06
Description	New user registered*
Frequency	dozens per day

INSERT INTO "user" (username, password, email) VALUES
  ($username, $password, $email)

Query	INSERT07
Description	Votes on a post
Frequency	hundreds per day

INSERT INTO vote (id_post, id_user, value) VALUES
  ($id_post, $id_user, $value)

1.5. Frequent Deletes

Query	DELETE01
Description	Delete vote
Frequency	units per day

DELETE FROM vote
  WHERE id_post = $id_post AND id_user = $id_user

Query	DELETE02
Description	Delete post
Frequency	units per day

DELETE FROM post
  WHERE id = $id

2. Proposed Indices

2.1. Performance Indices

Index	IDX01
Related queries	SELECT11
Relation	post
Attribute	date
Type	B-tree
Cardinality	high
Clustering	No
Justification	To allow the search of post by range faster; B-Tree to allow ranged queries
`SQL code`	`CREATE INDEX date_idx ON post USING btree ("date")`

Index	IDX02
Related queries	SELECT03
Relation	answer_question
Attribute	id_question
Type	Hash
Cardinality	medium
Clustering	Yes
Justification	To allow searching for all answers of a specific question; Used when loading a question page, so executed very frequently
`SQL code`	`CREATE INDEX question_idx ON answer_question USING hash (id_question)`

Index	IDX03
Related queries	SELECT12
Relation	post
Attribute	id_owner
Type	Hash
Cardinality	high
Clustering	No
Justification	To allow searching for all posts of a given user. Highly used when requesting a user's profile
`SQL code`	`CREATE INDEX owner_idx ON post USING hash (id_owner)`

Index	IDX04
Related queries	SELECT08
Relation	achieved
Attribute	id_user
Type	Hash
Cardinality	high
Clustering	No
Justification	To allow searching for all achievements of a given user. Highly used when requesting a user's profile
`SQL code`	`CREATE INDEX user_idx ON achieved USING hash (id_user)`

Index	IDX05
Related queries	SELECT11
Relation	report
Attribute	state
Type	Hash
Cardinality	Small
Clustering	Yes
Justification	To allow searching for all pending reports. Highly used on the moderation page
`SQL code`	`CREATE INDEX state_idx ON report USING hash ("state")`

2.2. Full-text Search Indices

Index	IDX01
Related queries	SELECT01, ...
Relation	user
Attribute	name and username
Type	GiST
Clustering	No
Justification	The webpage is centered in finding the information you need. The search feature includes user search.
`SQL code`	`CREATE INDEX user_search_idx ON "user" USING GiST (search)`

Index	IDX02
Related queries	SELECT01, ...
Relation	question
Attribute	title and body
Type	GiST
Clustering	No
Justification	The webpage is centered in finding the information you need. The search feature includes question searchs (by title).
`SQL code`	`CREATE INDEX question_search_idx ON question USING GiST (search)`

Index	IDX03
Related queries	SELECT01, ...
Relation	topic
Attribute	name
Type	GIN
Clustering	No
Justification	The webpage is centered in finding the information you need. The search feature includes topic searchs.
`SQL code`	`CREATE INDEX topic_search_idx ON topic USING GIN (search)`

3. Triggers

Trigger	TRIGGER01
Description	The score of a post is the sum of its votes

CREATE OR REPLACE FUNCTION on_score_change()
RETURNS TRIGGER
AS $$
  DECLARE
    val integer;
    post_id integer;
    owner_id integer;
  BEGIN
    IF (TG_OP = 'DELETE') THEN
      post_id := OLD.id_post;
    ELSIF (TG_OP = 'UPDATE') THEN
      post_id := NEW.id_post;
    ELSIF (TG_OP = 'INSERT') THEN
      post_id := NEW.id_post;
    END IF;

    val := (SELECT sum(value)
            FROM post JOIN vote ON (post.id = vote.id_post)
            WHERE id = post_id);

    -- update the question score
    UPDATE post
    SET score = val
    WHERE id = post_id;

    -- update the question's owner reputation
    owner_id := (SELECT id_owner
                  FROM post
                  WHERE id = post_id);

    val := (SELECT count(*)
            FROM post
            JOIN "user" ON (post.id_owner = "user".id)
            WHERE "user".id = owner_id);

    UPDATE "user" as u
    SET reputation = val
    WHERE owner_id = u.id;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_score ON vote CASCADE;
CREATE TRIGGER update_score
AFTER DELETE OR INSERT OR UPDATE
ON vote
FOR EACH ROW EXECUTE FUNCTION on_score_change();

Trigger	TRIGGER02
Description	Used in order to achieve better search results for users

CREATE OR REPLACE FUNCTION user_search_update()
RETURNS TRIGGER
AS $$
  BEGIN
  IF (TG_OP = 'INSERT') THEN
    NEW.search = (setweight(to_tsvector('english', NEW.username), 'A') || setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'B'));
  ELSIF (TG_OP = 'UPDATE') THEN
    IF NEW.username <> OLD.username or NEW.name <> OLD.name THEN
      NEW.search = (setweight(to_tsvector('english', NEW.username), 'A') || setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'B'));
    END IF;
  END IF;

  RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS user_search_update_trigger ON "user" CASCADE;
CREATE TRIGGER user_search_update_trigger
BEFORE INSERT OR UPDATE
ON "user"
FOR EACH ROW EXECUTE FUNCTION user_search_update();

Trigger	TRIGGER03
Description	Used in order to achieve better search results for questions

CREATE OR REPLACE FUNCTION question_search_update()
RETURNS TRIGGER
AS $$
  DECLARE
    b text;
    topics text;
  BEGIN
    -- get the body
    b := (SELECT body
          FROM post
          WHERE post.id = NEW.id);

    -- get the topics
    topics := (SELECT string_agg(name, ' ')
                FROM topic JOIN topic_question ON (topic.id = topic_question.id_topic)
                  JOIN question ON (question.id = topic_question.id_question)
                WHERE question.id = NEW.id);

    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      NEW.search = (setweight(to_tsvector('english', NEW.title), 'A') ||
                    setweight(to_tsvector('english', b), 'B') ||
                    setweight(to_tsvector('english', COALESCE(topics, '')), 'C'));
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS question_search_update_trigger ON question CASCADE;
CREATE TRIGGER question_search_update_trigger
BEFORE INSERT OR UPDATE
ON question
FOR EACH ROW EXECUTE FUNCTION question_search_update();

Trigger	TRIGGER04
Description	Used in order to achieve better search results for topics

CREATE OR REPLACE FUNCTION topic_search_update()
RETURNS TRIGGER
AS $$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      NEW.search = to_tsvector('english', NEW.name);
    ELSIF (TG_OP = 'UPDATE') THEN
      IF NEW.name <> OLD.name THEN
        NEW.seach = to_tsvector('english', NEW.name);
      END IF;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS topic_search_update_trigger ON topic CASCADE;
CREATE TRIGGER topic_search_update_trigger
BEFORE INSERT OR UPDATE
ON topic
FOR EACH ROW EXECUTE FUNCTION topic_search_update();

Trigger	TRIGGER06
Description	A closed question cannot be re-opened.

CREATE OR REPLACE FUNCTION reopen_question() RETURNS TRIGGER AS
$$
BEGIN
    IF (OLD.closed <> NEW.closed and OLD.closed = TRUE) THEN
      RAISE EXCEPTION 'A closed question can not be re-opened.';
    END IF;
    RETURN NEW;
END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS reopen_question_trigger ON question CASCADE;
CREATE TRIGGER reopen_question_trigger
BEFORE UPDATE ON question
FOR EACH ROW
EXECUTE PROCEDURE reopen_question();

Trigger	TRIGGER07
Description	A user cannot vote on its own post.

CREATE OR REPLACE FUNCTION vote() RETURNS TRIGGER
AS $$
  DECLARE
    owner integer;
  BEGIN
      owner := (SELECT id_owner FROM post WHERE id = NEW.id_post);

      IF (owner = NEW.id_user) THEN
        RAISE EXCEPTION 'A user can not vote on its own post.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS vote_trigger ON vote CASCADE;
CREATE TRIGGER vote_trigger
BEFORE INSERT OR UPDATE ON vote
FOR EACH ROW
EXECUTE PROCEDURE vote();

Trigger	TRIGGER08
Description	Notification generalization must be disjoint. Notification_achievement id cannot exist already.

CREATE OR REPLACE FUNCTION notification_generalization() RETURNS TRIGGER
AS $$
  BEGIN
      IF EXISTS (SELECT *
        FROM notification_post, notification_achievement
        WHERE notification_post.id = New.id OR notification_achievement.id = New.id) THEN
        RAISE EXCEPTION 'Notification must be disjoint.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS notification_achievement_generalization_trigger ON notification_achievement CASCADE;
CREATE TRIGGER notification_achievement_generalization_trigger
BEFORE INSERT OR UPDATE ON notification_achievement
FOR EACH ROW
EXECUTE PROCEDURE notification_generalization();

Trigger	TRIGGER09
Description	Notification generalization must be disjoint. Notification_post id cannot exist already.

DROP TRIGGER IF EXISTS notification_post_generalization_trigger ON notification_post CASCADE;
CREATE TRIGGER notification_post_generalization_trigger
BEFORE INSERT OR UPDATE ON notification_post
FOR EACH ROW
EXECUTE PROCEDURE notification_generalization();

Trigger	TRIGGER10
Description	Post generalization must be disjoint. Answer id cannot exist already.

CREATE OR REPLACE FUNCTION post_generalization() RETURNS TRIGGER
AS $$
  BEGIN
      IF EXISTS (SELECT *
        FROM question, answer, comment
        WHERE question.id = New.id OR answer.id = New.id OR comment.id = New.id) THEN
        RAISE EXCEPTION 'Post must be disjoint.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS post_answer_generalization_trigger ON answer CASCADE;
CREATE TRIGGER post_answer_generalization_trigger
BEFORE INSERT OR UPDATE ON answer
FOR EACH ROW
EXECUTE PROCEDURE post_generalization();

Trigger	TRIGGER11
Description	Post generalization must be disjoint. Question id cannot exist already.

DROP TRIGGER IF EXISTS post_question_generalization_trigger ON question CASCADE;
CREATE TRIGGER post_question_generalization_trigger
BEFORE INSERT ON question
FOR EACH ROW
EXECUTE PROCEDURE post_generalization();

Trigger	TRIGGER12
Description	Post generalization must be disjoint. Comment id cannot exist already.

DROP TRIGGER IF EXISTS post_comment_generalization_trigger ON comment CASCADE;
CREATE TRIGGER post_comment_generalization_trigger
BEFORE INSERT OR UPDATE ON comment
FOR EACH ROW
EXECUTE PROCEDURE post_generalization();

Trigger	TRIGGER13
Description	Trigger for achievement 1: First post of a user

CREATE OR REPLACE FUNCTION achievement_first_question() RETURNS TRIGGER 
AS $$
  DECLARE
    owner_id integer;
    question_amount integer;
  BEGIN
      owner_id := (SELECT post.id_owner AS owner_id FROM post JOIN question ON(post.id = question.id) WHERE post.id = NEW.id);
      question_amount := (SELECT COUNT(*) FROM post JOIN question ON(post.id = question.id) WHERE post.id_owner = owner_id);
      
      IF (question_amount = 1) THEN
        IF NOT EXISTS (SELECT * FROM achieved WHERE id_user = owner_id AND id_achievement = 1) THEN
          INSERT INTO achieved(id_user, id_achievement) VALUES (owner_id, 1);
        END IF;
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS achievement_first_question_trigger ON question CASCADE;
CREATE TRIGGER achievement_first_question_trigger
AFTER INSERT ON question
FOR EACH ROW
EXECUTE PROCEDURE achievement_first_question();

Trigger	TRIGGER14
Description	Trigger for achievement 2: First accepted answer of a user

CREATE OR REPLACE FUNCTION achievement_first_accepted_answer() RETURNS TRIGGER 
AS $$
  DECLARE
    accepted_answer_id integer;
    answer_owner_id integer;
  BEGIN
      accepted_answer_id := NEW.accepted_answer;

      IF (accepted_answer_id IS NOT NULL) THEN
        answer_owner_id := (SELECT id_owner FROM post WHERE id = accepted_answer_id);
        IF NOT EXISTS (SELECT * FROM achieved WHERE id_user = answer_owner_id AND id_achievement = 2) THEN 
          INSERT INTO achieved(id_user, id_achievement) VALUES (answer_owner_id, 2);
        END IF;
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS achievement_first_accepted_answer_trigger ON question CASCADE;
CREATE TRIGGER achievement_first_accepted_answer_trigger
AFTER INSERT OR UPDATE ON question
FOR EACH ROW
EXECUTE PROCEDURE achievement_first_accepted_answer();

Trigger	TRIGGER15
Description	Trigger for achievements 3: Notification of a user reeaching a reputation threshold

CREATE OR REPLACE FUNCTION achievement_reputation() RETURNS TRIGGER 
AS $$
  BEGIN
    IF (NEW.reputation < 100) THEN
      RETURN NEW;
    END IF;
    IF (NEW.reputation >= 100) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 3)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 3);
      END IF;
    END IF;
    IF (NEW.reputation >= 200) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 4)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 4);
      END IF;
    END IF;
    IF (NEW.reputation >= 500) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 5)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 5);
      END IF;
    END IF;
    IF (NEW.reputation >= 1000) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 6)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 6);
      END IF;
    END IF;
    RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS achievement_reputation_trigger ON "user" CASCADE;
CREATE TRIGGER achievement_reputation_trigger
AFTER INSERT OR UPDATE ON "user"
FOR EACH ROW
EXECUTE PROCEDURE achievement_reputation();

Trigger	TRIGGER16
Description	Trigger for notifications 1: Notification of receiving an achievement

CREATE OR REPLACE FUNCTION add_achievement_notification() RETURNS TRIGGER 
AS $$
  BEGIN
      INSERT INTO notification (title, body, recipient) VALUES ('New achievement', 'You have achieved: ', NEW.id_user);
      INSERT INTO notification_achievement (id, id_achievement) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_achievement);
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS add_achievement_notification ON achieved CASCADE;
CREATE TRIGGER add_achievement_notification
AFTER INSERT ON achieved
FOR EACH ROW
EXECUTE PROCEDURE add_achievement_notification();

Trigger	TRIGGER17
Description	Trigger for notifications 2: Notification of someone answering one of the user's questions

CREATE OR REPLACE FUNCTION add_new_answer_notification() RETURNS TRIGGER 
AS $$
  BEGIN
      INSERT INTO notification (title, body, recipient) VALUES ('New answer', 'Someone answered your question: ', NEW.id_question);
      INSERT INTO notification_post (id, id_post) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_question);
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS add_new_answer_notification ON answer_question CASCADE;
CREATE TRIGGER add_new_answer_notification
AFTER INSERT ON answer_question
FOR EACH ROW
EXECUTE PROCEDURE add_new_answer_notification();

Trigger	TRIGGER18
Description	Trigger for notifications 3: Notification of a new comment on a user's post

CREATE OR REPLACE FUNCTION add_new_comment_notification() RETURNS TRIGGER 
AS $$
  BEGIN
      IF (NEW.id_question IS NOT NULL) THEN
        INSERT INTO notification (title, body, recipient) VALUES ('New Comment', 'Someone commented your question: ', NEW.id_question);
        INSERT INTO notification_post (id, id_post) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_question);
      END IF;
      IF (NEW.id_answer IS NOT NULL) THEN
        INSERT INTO notification (title, body, recipient) VALUES ('New Comment', 'Someone commented your answer: ', NEW.id_answer);
        INSERT INTO notification_post (id, id_post) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_answer);
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS add_new_comment_notification ON comment CASCADE;
CREATE TRIGGER add_new_comment_notification
AFTER INSERT ON comment
FOR EACH ROW
EXECUTE PROCEDURE add_new_comment_notification();

Trigger	TRIGGER19
Description	Trigger for preserving user information when deleted

DROP RULE IF EXISTS remove_user ON "user" CASCADE;
CREATE RULE remove_user
AS ON DELETE TO "user"
DO INSTEAD
    UPDATE "user"
    SET
      name = 'Deleted User',
      username = NULL,
      search = '',
      password = NULL,
      email = NULL,
      about = NULL,
      picture = NULL,
      reputation = NULL
    WHERE id = Old.id;

4. Transactions

SQL Reference	Insert a new Question
Justification	When inserting a question, inserting a new Post tuple is required, due to the E/R mapping of this generalization. To avoid inconsistent data, both insertions must be captured in a transaction statement.
Isolation level	REPEATABLE READ
`Complete SQL Code`

BEGIN TRANSACTION;
INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
INSERT INTO question(id, accepted_answer, title, bounty, closed) 
  VALUES (currval(pg_get_serial_sequence('post','id')), NULL, Title, Bounty, Closed);
END TRANSACTION;

SQL Reference	Insert a new Comment
Justification	When inserting a comment, inserting a new Post tuple is required, due to the E/R mapping of this generalization. To avoid inconsistent data, both insertions must be captured in a transaction statement.
Isolation level	REPEATABLE READ
`Complete SQL Code`

BEGIN TRANSACTION;
  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
  INSERT INTO comment(id, id_question, id_answer) VALUES (currval(pg_get_serial_sequence('post','id')), IdQuestion, IdAnswer);
END TRANSACTION;

SQL Reference	Get questions of a Answer
Justification	When loading a user's answer, it is useful to retreive the exact number of posts and 10 most recent posts.
Isolation level	REPEATABLE READ
`Complete SQL Code`

BEGIN TRANSACTION;
  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
  INSERT INTO answer(id) VALUES (currval(pg_get_serial_sequence('post','id')));
  INSERT INTO answer_question(id_answer, id_question) VALUES(currval(pg_get_serial_sequence('post','id')), IdQuestion);
END TRANSACTION;

Annex A. SQL Code

A.1. Database schema

-- CLEANUP
DROP DOMAIN IF EXISTS Today CASCADE;
DROP TYPE IF EXISTS report_state CASCADE;
DROP TABLE IF EXISTS "user" CASCADE;
DROP TABLE IF EXISTS moderator CASCADE;
DROP TABLE IF EXISTS administrator CASCADE;
DROP TABLE IF EXISTS ban CASCADE;
DROP TABLE IF EXISTS news CASCADE;
DROP TABLE IF EXISTS topic_proposal CASCADE;
DROP TABLE IF EXISTS achievement CASCADE;
DROP TABLE IF EXISTS achieved CASCADE;
DROP TABLE IF EXISTS post CASCADE;
DROP TABLE IF EXISTS answer CASCADE;
DROP TABLE IF EXISTS question CASCADE;
DROP TABLE IF EXISTS answer_question;
DROP TABLE IF EXISTS comment CASCADE;
DROP TABLE IF EXISTS vote CASCADE;
DROP TABLE IF EXISTS edit_proposal CASCADE;
DROP TABLE IF EXISTS topic CASCADE;
DROP TABLE IF EXISTS topic_question CASCADE;
DROP TABLE IF EXISTS report CASCADE;
DROP TABLE IF EXISTS notification CASCADE;
DROP TABLE IF EXISTS notification_achievement CASCADE;
DROP TABLE IF EXISTS notification_post CASCADE;


-- DOMAINS
CREATE DOMAIN Today AS DATE DEFAULT CURRENT_DATE;
CREATE TYPE report_state AS ENUM ('pending', 'approved', 'rejected');


-- TABLES
-- R01
CREATE TABLE "user"(
  id SERIAL PRIMARY KEY,
  name TEXT,
  username TEXT UNIQUE NOT NULL,
  search TSVECTOR NOT NULL,
  password TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  about TEXT,
  picture TEXT,
  reputation INTEGER NOT NULL DEFAULT 0
);

-- R02
CREATE TABLE moderator(
  id INTEGER PRIMARY KEY,
  CONSTRAINT fk_user
    FOREIGN KEY(id)
      REFERENCES "user"(id)
);

-- R03
CREATE TABLE administrator(
  id INTEGER PRIMARY KEY,
  CONSTRAINT fk_moderator
    FOREIGN KEY(id)
      REFERENCES moderator(id)
);

-- R04
CREATE TABLE ban(
  id_user INTEGER PRIMARY KEY,
  id_admin INTEGER NOT NULL,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_DATE), -- bans can't have happened in the future
  reason TEXT,
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id),
  CONSTRAINT fk_admin
    FOREIGN KEY(id_admin)
      REFERENCES Administrator(id)
);

-- R05
CREATE TABLE news(
  id SERIAL PRIMARY KEY,
  author INTEGER NOT NULL,
  title TEXT NOT NULL,
  subtitle TEXT,
  body TEXT,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_DATE), -- news can't be posted in the future
  CONSTRAINT fk_author
    FOREIGN KEY(author)
      REFERENCES administrator(id)
);

-- R06
CREATE TABLE topic_proposal(
  id SERIAL PRIMARY KEY,
  id_user INTEGER NOT NULL,
  id_admin INTEGER,
  topic_name TEXT NOT NULL,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_DATE), -- proposals can't have happened in the future
  reason TEXT,
  accepted boolean NOT NULL DEFAULT false,
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id),
  CONSTRAINT fk_admin
    FOREIGN KEY(id_admin)
      REFERENCES administrator(id)
);

-- R07
CREATE TABLE achievement(
  id SERIAL PRIMARY KEY,
  title TEXT UNIQUE NOT NULL,
  body TEXT NOT NULL
);

-- R08
CREATE TABLE achieved(
  id_user INTEGER,
  id_achievement INTEGER,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_DATE), -- achievements can't be acquired in the future
  PRIMARY KEY (id_user, id_achievement),
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id),
  CONSTRAINT fk_achievement
    FOREIGN KEY(id_achievement)
      REFERENCES achievement(id)
);

-- R09
CREATE TABLE post(
  id SERIAL PRIMARY KEY,
  id_owner INTEGER NOT NULL,
  body TEXT NOT NULL,
  score INTEGER NOT NULL DEFAULT 0,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_DATE), -- posts can't be made in the future
  CONSTRAINT fk_owner
    FOREIGN KEY(id_owner)
      REFERENCES "user"(id)
);

-- R10
CREATE TABLE answer(
  id INTEGER PRIMARY KEY,
  CONSTRAINT fk_post
    FOREIGN KEY(id)
      REFERENCES post(id)
);

-- R11
CREATE TABLE question(
  id INTEGER PRIMARY KEY,
  accepted_answer INTEGER,
  title TEXT UNIQUE NOT NULL,
  search TSVECTOR NOT NULL,
  bounty smallint NOT NULL CHECK (
    bounty >= 0
    AND bounty <= 500
  ),
  closed boolean NOT NULL DEFAULT false,
  CONSTRAINT fk_post
    FOREIGN KEY(id)
      REFERENCES post(id),
  CONSTRAINT fk_answer
    FOREIGN KEY(accepted_answer)
      REFERENCES answer(id)
);

-- R12
CREATE TABLE answer_question(
  id_answer INTEGER,
  id_question INTEGER,
  PRIMARY KEY(id_answer, id_question),
  CONSTRAINT fk_answer
    FOREIGN KEY(id_answer)
      REFERENCES answer(id),
  CONSTRAINT fk_question
    FOREIGN KEY(id_question)
      REFERENCES question(id)
);

-- R13
CREATE TABLE comment(
  id INTEGER PRIMARY KEY,
  id_question INTEGER,
  id_answer INTEGER,
  CHECK (
    (
      id_question = NULL
      AND id_answer != NULL
    )
    OR (
      id_question != NULL
      AND id_answer = NULL
    )
  ),
  CONSTRAINT fk_post
    FOREIGN KEY(id)
      REFERENCES post(id),
  CONSTRAINT fk_question
    FOREIGN KEY(id_question)
      REFERENCES question(id),
  CONSTRAINT fk_answer
    FOREIGN KEY(id_answer)
      REFERENCES answer(id)
);

-- R14
CREATE TABLE vote(
  id_post INTEGER,
  id_user INTEGER,
  value smallint NOT NULL CHECK (
    value = 1
    OR value = -1
  ),
  PRIMARY KEY (id_post, id_user),
  CONSTRAINT fk_post
    FOREIGN KEY(id_post)
      REFERENCES post(id),
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id)
);

-- R15
CREATE TABLE edit_proposal(
  id SERIAL PRIMARY KEY,
  id_post INTEGER NOT NULL,
  id_user INTEGER NOT NULL,
  id_moderator INTEGER,
  body TEXT NOT NULL,
  accepted boolean NOT NULL DEFAULT false,
  CONSTRAINT fk_post
    FOREIGN KEY(id_post)
      REFERENCES post(id),
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id),
  CONSTRAINT fk_moderator
    FOREIGN KEY(id_moderator)
      REFERENCES moderator(id)
);

-- R16
CREATE TABLE topic(
  id SERIAL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  search TSVECTOR NOT NULL
);

-- R17
CREATE TABLE topic_question(
  id_topic INTEGER,
  id_question INTEGER,
  PRIMARY KEY(id_topic, id_question),
  CONSTRAINT fk_topic
    FOREIGN KEY(id_topic)
      REFERENCES topic(id),
  CONSTRAINT fk_question
    FOREIGN KEY(id_question)
      REFERENCES question(id)
);

-- R18
CREATE TABLE report(
  id_post INTEGER,
  reporter INTEGER,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_DATE), -- reports can't be made in the future
  reason TEXT,
  PRIMARY KEY(id_post, reporter),
  "state" report_state,
  reviewer INTEGER,
  CONSTRAINT fk_post
    FOREIGN KEY(id_post)
      REFERENCES post(id),
  CONSTRAINT fk_reporter
    FOREIGN KEY(reporter)
      REFERENCES "user"(id),
  CONSTRAINT fk_reviewer
    FOREIGN KEY(reviewer)
      REFERENCES "moderator"(id)
);

-- R19
CREATE TABLE notification(
  id SERIAL PRIMARY KEY,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_DATE), -- can't be notified in the future
  title TEXT NOT NULL,
  body TEXT,
  recipient INTEGER,
  CONSTRAINT fk_recipient
    FOREIGN KEY(recipient)
      REFERENCES "user"(id)
);

-- R20
CREATE TABLE notification_achievement(
  id INTEGER PRIMARY KEY,
  id_achievement INTEGER NOT NULL,
  CONSTRAINT fk_notification
    FOREIGN KEY(id)
      REFERENCES notification(id),
  CONSTRAINT fk_achievement
    FOREIGN KEY(id_achievement)
      REFERENCES achievement(id)
);

-- R21
CREATE TABLE notification_post(
  id INTEGER PRIMARY KEY,
  id_post INTEGER NOT NULL,
  CONSTRAINT fk_notification
    FOREIGN KEY(id)
      REFERENCES notification(id),
  CONSTRAINT fk_post
    FOREIGN KEY(id_post)
      REFERENCES post(id)
);


-- INDEXES
DROP INDEX IF EXISTS date_idx CASCADE;
DROP INDEX IF EXISTS question_idx CASCADE;
DROP INDEX IF EXISTS owner_idx CASCADE;
DROP INDEX IF EXISTS user_idx CASCADE;
DROP INDEX IF EXISTS state_idx CASCADE;
DROP INDEX IF EXISTS user_search_idx CASCADE;
DROP INDEX IF EXISTS question_search_idx CASCADE;
DROP INDEX IF EXISTS topic_search_idx CASCADE;

CREATE INDEX date_idx ON post USING btree ("date");
CREATE INDEX question_idx ON answer_question USING hash (id_question);
CREATE INDEX owner_idx ON post USING hash (id_owner);
CREATE INDEX user_idx ON achieved USING hash (id_user);
CREATE INDEX state_idx ON report USING hash ("state");
CREATE INDEX user_search_idx ON "user" USING GiST (search);
CREATE INDEX question_search_idx ON question USING GiST (search);
CREATE INDEX topic_search_idx ON topic USING GIN (search);


-- FUNCTIONS
-- used to store the derived attribute 'score' (see comment in vote table)
CREATE OR REPLACE FUNCTION on_score_change()
RETURNS TRIGGER
AS $$
  DECLARE
    val integer;
    post_id integer;
  BEGIN
    IF (TG_OP = 'DELETE') THEN
      val := -OLD.value;
      post_id := OLD.id_post;
    ELSIF (TG_OP = 'UPDATE') THEN
      val := -OLD.value + NEW.value;
      post_id := NEW.id_post;
    ELSIF (TG_OP = 'INSERT') THEN
      val := NEW.value;
      post_id := NEW.id_post;
    END IF;

    -- update the question score
    UPDATE post
    SET score = score + val
    WHERE id = post_id;

    -- update the question's owner reputation
    UPDATE "user" as u
    SET reputation = reputation + val
    FROM post as p
    WHERE p.id = post_id and p.id_owner = u.id;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION user_search_update()
RETURNS TRIGGER
AS $$
  BEGIN
  IF (TG_OP = 'INSERT') THEN
    --  NEW.search = (setweight(to_tsvector('english', NEW.username), 'A') || setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'B'));
    NEW.search = (setweight(to_tsvector('english', NEW.username), 'A'));
  ELSIF (TG_OP = 'UPDATE') THEN
    IF NEW.username <> OLD.username or NEW.name <> OLD.name THEN
      NEW.search = (setweight(to_tsvector('english', NEW.username), 'A'));
    END IF;
  END IF;

  RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION question_search_update()
RETURNS TRIGGER
AS $$
  DECLARE
    b text;
  BEGIN
    -- get the body
    b := (SELECT body
          FROM post
          WHERE post.id = NEW.id);

    IF (TG_OP = 'INSERT') THEN
      NEW.search = (setweight(to_tsvector('english', NEW.title), 'A') || setweight(to_tsvector('english', b), 'B'));
    ELSIF (TG_OP = 'UPDATE') THEN
      IF NEW.title <> OLD.title THEN
        NEW.search = (setweight(to_tsvector('english', NEW.title), 'A') || setweight(to_tsvector('english', b), 'B'));
      END IF;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION topic_search_update()
RETURNS TRIGGER
AS $$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      NEW.search = to_tsvector('english', NEW.name);
    ELSIF (TG_OP = 'UPDATE') THEN
      IF NEW.name <> OLD.name THEN
        NEW.seach = to_tsvector('english', NEW.name);
      END IF;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION reopen_question() RETURNS TRIGGER AS
$$
BEGIN
    IF (OLD.closed <> NEW.closed and OLD.closed = TRUE) THEN
      RAISE EXCEPTION 'A closed question can not be re-opened.';
    END IF;
    RETURN NEW;
END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vote() RETURNS TRIGGER
AS $$
  DECLARE
    owner integer;
  BEGIN
      owner := (SELECT id_owner FROM post WHERE id = NEW.id_post);

      IF (owner = NEW.id_user) THEN
        RAISE EXCEPTION 'A user can not vote on its own post.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

-- TRIGGERS
DROP TRIGGER IF EXISTS update_score ON vote CASCADE;
CREATE TRIGGER update_score
AFTER DELETE OR INSERT OR UPDATE
ON vote
FOR EACH ROW EXECUTE FUNCTION on_score_change();

DROP TRIGGER IF EXISTS user_search_update_trigger ON "user" CASCADE;
CREATE TRIGGER user_search_update_trigger
BEFORE INSERT OR UPDATE
ON "user"
FOR EACH ROW EXECUTE FUNCTION user_search_update();

DROP TRIGGER IF EXISTS question_search_update_trigger ON question CASCADE;
CREATE TRIGGER question_search_update_trigger
BEFORE INSERT OR UPDATE
ON question
FOR EACH ROW EXECUTE FUNCTION question_search_update();

DROP TRIGGER IF EXISTS topic_search_update_trigger ON topic CASCADE;
CREATE TRIGGER topic_search_update_trigger
BEFORE INSERT OR UPDATE
ON topic
FOR EACH ROW EXECUTE FUNCTION topic_search_update();

DROP TRIGGER IF EXISTS reopen_question_trigger ON question CASCADE;
CREATE TRIGGER reopen_question_trigger
BEFORE UPDATE ON question
FOR EACH ROW
EXECUTE PROCEDURE reopen_question();

DROP TRIGGER IF EXISTS vote_trigger ON vote CASCADE;
CREATE TRIGGER vote_trigger
BEFORE INSERT OR UPDATE ON vote
FOR EACH ROW
EXECUTE PROCEDURE vote();


-- TRANSACTIONS
CREATE OR REPLACE PROCEDURE create_question
(
  OwnerUser INT,
  Body TEXT,
  DatePost DATE,
  Title TEXT,
  Bounty INT,
  Closed BOOLEAN
)
LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN
  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
  -- INSERT INTO question(id, accepted_answer, title, bounty, closed) SELECT(1, NULL, Title, Bounty, Closed);
  INSERT INTO question(id, accepted_answer, title, bounty, closed) 
  	VALUES (currval(pg_get_serial_sequence('post','id')), NULL, Title, Bounty, Closed);
END
$$;

CREATE OR REPLACE PROCEDURE create_comment
(
  OwnerUser INT,
  Body TEXT,
  DatePost DATE,
  IdQuestion INT,
  IdAnswer INT
)
LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN
  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
  -- INSERT INTO question(id, accepted_answer, title, bounty, closed) SELECT(1, NULL, Title, Bounty, Closed);
  INSERT INTO comment(id, id_question, id_answer) VALUES (currval(pg_get_serial_sequence('post','id')), IdQuestion, IdAnswer);
END
$$;

CREATE OR REPLACE PROCEDURE create_answer
(
  OwnerUser INT,
  Body TEXT,
  DatePost DATE,
  IdQuestion INT
)
LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN
  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
  -- INSERT INTO question(id, accepted_answer, title, bounty, closed) SELECT(1, NULL, Title, Bounty, Closed);
  INSERT INTO answer(id) VALUES (currval(pg_get_serial_sequence('post','id')));
  INSERT INTO answer_question(id_answer, id_question) VALUES(currval(pg_get_serial_sequence('post','id')), IdQuestion);
END
$$;

A.2. Database population

-- R01
insert into "user" (id, name, username, password, email, about, picture, reputation) values
  (1, 'Torrance Jerrom', 'tjerrom0', 'YeBGgmC', 'tjerrom0@deliciousdays.com', 'Dental Hygienist', 'http://dummyimage.com/102x100.png/ff4444/ffffff', 612),
  (2, 'Keenan O''Bruen', 'kobruen1', 'BTFYEdJ', 'kobruen1@yolasite.com', 'Tax Accountant', 'http://dummyimage.com/135x100.png/dddddd/000000', 215),
  (3, 'Joey Kores', 'jkores2', 'hSpFcNF', 'jkores2@parallels.com', 'Administrative Officer', 'http://dummyimage.com/169x100.png/5fa2dd/ffffff', 449),
  (4, 'Berky Shakespeare', 'bshakespeare3', 'UlBGb5', 'bshakespeare3@deliciousdays.com', 'Biostatistician IV', 'http://dummyimage.com/110x100.png/dddddd/000000', 901),
  (5, 'Ives Shinn', 'ishinn4', 'ct4ESru', 'ishinn4@topsy.com', 'Programmer Analyst IV', 'http://dummyimage.com/226x100.png/cc0000/ffffff', 741),
  (6, 'Ian Franklyn', 'ifranklyn5', 'zLrS0M6kj', 'ifranklyn5@oracle.com', 'Senior Quality Engineer', 'http://dummyimage.com/231x100.png/ff4444/ffffff', 712),
  (7, 'Pernell Danelut', 'pdanelut6', 'Ev58adsWg', 'pdanelut6@shareasale.com', 'Marketing Manager', 'http://dummyimage.com/233x100.png/ff4444/ffffff', 84),
  (8, 'Malachi Rilings', 'mrilings7', 'KzNfOzPGNf0', 'mrilings7@tinypic.com', 'Nurse', 'http://dummyimage.com/248x100.png/ff4444/ffffff', 850),
  (9, 'Gill Lehrian', 'glehrian8', '5so0kMf9N7D9', 'glehrian8@sina.com.cn', 'Junior Executive', 'http://dummyimage.com/170x100.png/ff4444/ffffff', 153),
  (10, 'Baily Bernet', 'bbernet9', 'TTXdHTpMu3N', 'bbernet9@weather.com', 'Chemical Engineer', 'http://dummyimage.com/199x100.png/ff4444/ffffff', 669),
  (11, 'Miner Abazi', 'mabazia', 'EllP4J8DVM', 'mabazia@mashable.com', 'Payment Adjustment Coordinator', 'http://dummyimage.com/141x100.png/dddddd/000000', 345),
  (12, 'Kleon Olech', 'kolechb', 'Nau0PYH5ZS', 'kolechb@studiopress.com', 'Environmental Specialist', 'http://dummyimage.com/198x100.png/5fa2dd/ffffff', 788),
  (13, 'Emmy Sisley', 'esisleyc', 'VICwW4yX', 'esisleyc@noaa.gov', 'Account Representative II', 'http://dummyimage.com/174x100.png/5fa2dd/ffffff', 104),
  (14, 'Olav Zanetto', 'ozanettod', 'OJJLPwNPIIf', 'ozanettod@hostgator.com', 'Senior Developer', 'http://dummyimage.com/116x100.png/dddddd/000000', 655),
  (15, 'Paulina Habbeshaw', 'phabbeshawe', 'QZkZLn', 'phabbeshawe@time.com', 'Librarian', 'http://dummyimage.com/224x100.png/ff4444/ffffff', 91),
  (16, 'Karil Peoples', 'kpeoplesf', '9mLVnY1Fkksm', 'kpeoplesf@behance.net', 'Occupational Therapist', 'http://dummyimage.com/200x100.png/5fa2dd/ffffff', 968),
  (17, 'Daisi Worters', 'dwortersg', 'WEwGru55Bl4a', 'dwortersg@icio.us', 'Help Desk Operator', 'http://dummyimage.com/143x100.png/dddddd/000000', 953),
  (18, 'Cathee Carthy', 'ccarthyh', 'rhfqZj3kV02', 'ccarthyh@twitter.com', 'Senior Quality Engineer', 'http://dummyimage.com/177x100.png/ff4444/ffffff', 360),
  (19, 'Dusty Maxwaile', 'dmaxwailei', 'oZJFF9', 'dmaxwailei@google.nl', 'Systems Administrator II', 'http://dummyimage.com/235x100.png/ff4444/ffffff', 455),
  (20, 'Tybalt Russan', 'trussanj', '1rlJ9S3', 'trussanj@linkedin.com', 'Editor', 'http://dummyimage.com/179x100.png/ff4444/ffffff', 349),
  (21, 'Rhona Kemmett', 'rkemmettk', 'xCrizjpI', 'rkemmettk@lulu.com', 'Information Systems Manager', 'http://dummyimage.com/120x100.png/ff4444/ffffff', 221),
  (22, 'Rubetta Molesworth', 'rmolesworthl', 'HebsnJ7jD1nH', 'rmolesworthl@hexun.com', 'Geologist I', 'http://dummyimage.com/227x100.png/ff4444/ffffff', 375),
  (23, 'Magdaia Volcker', 'mvolckerm', '7B8AVrNiv7FK', 'mvolckerm@bluehost.com', 'VP Product Management', 'http://dummyimage.com/122x100.png/dddddd/000000', 739),
  (24, 'Eleen Bullin', 'ebullinn', 'LrZT0WxXi', 'ebullinn@vk.com', 'Software Engineer IV', 'http://dummyimage.com/172x100.png/5fa2dd/ffffff', 525),
  (25, 'Anallese Thoma', 'athomao', 'DBlMQm', 'athomao@flickr.com', 'Research Assistant III', 'http://dummyimage.com/150x100.png/5fa2dd/ffffff', 330),
  (26, 'Gib Kipping', 'gkippingp', 'f0GXcvW1l', 'gkippingp@joomla.org', 'Operator', 'http://dummyimage.com/120x100.png/cc0000/ffffff', 812),
  (27, 'Jasun Deverock', 'jdeverockq', 'T7ELVdowbl0', 'jdeverockq@addthis.com', 'Statistician IV', 'http://dummyimage.com/115x100.png/cc0000/ffffff', 493),
  (28, 'Jodee Burmaster', 'jburmasterr', 'mffWNq', 'jburmasterr@foxnews.com', 'Biostatistician I', 'http://dummyimage.com/148x100.png/5fa2dd/ffffff', 934),
  (29, 'Glynn Baytrop', 'gbaytrops', 'r4HP1rmMuAfo', 'gbaytrops@ed.gov', 'Sales Representative', 'http://dummyimage.com/218x100.png/5fa2dd/ffffff', 778),
  (30, 'Zilvia Marvell', 'zmarvellt', 'uudLf0', 'zmarvellt@cbslocal.com', 'Senior Quality Engineer', 'http://dummyimage.com/139x100.png/ff4444/ffffff', 928);

-- R02
insert into moderator (id) values (1), (2), (3);

-- R03
insert into administrator (id) values (1);

-- R04
insert into ban (id_user, id_admin, "date", reason) values
  (10, 1, '2021-03-23', 'Verbal abuse.'),
  (11, 1, '2021-03-22', 'Racist comments.');

-- R05
insert into news (id, author, title, subtitle, body, date) values
  (1, 1, 'Hello and welcome.', 'The website is open.', 'You can now join and use our community.', '2021-03-15'),
  (2, 1, 'We have reached 100 members.', 'Our community is growing.', 'Thank you everyone for trusting us.', '2021-03-15');

-- R06
INSERT INTO topic_proposal (id, id_user, id_admin, topic_name, "date", reason, accepted) VALUES
  (1, 3, NULL, 'zig', '2020-03-28', 'interesting language', false),
  (2, 1, 1, 'c++', '2020-03-28','i like c plus plus', false);

-- R07
INSERT INTO achievement(id, title, body) VALUES
  (1, 'Post first question', 'You posted your first question on Segmentation Fault'),
  (2, 'Get first accepted answer', 'An answer you posted was chosen as the accepted answer');

-- R08
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (1, 1, '2008-12-17');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (5, 1, '2008-09-25');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (9, 1, '2011-04-23');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (12, 1, '2021-03-28');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (15, 1, '2019-08-22');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (3, 2, '2010-05-27');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (10, 2, '2011-04-23');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (16, 2, '2020-09-20');

-- R09
INSERT INTO post(id, id_owner, body, "date") VALUES
  (1, 1, 'If Python does not have a ternary conditional operator, is it possible to simulate one using other language constructs?', '2008-12-17'),
  (2, 2, 'In the Python 3.0 official documentation referenced in a comment above, this is referred to as "conditional_expressions" and is very cryptically defined. That documentation doesn''t even include the term "ternary", so you would be hard-pressed to find it via Google unless you knew exactly what to look for. The version 2 documentation is somewhat more helpful and includes a link to "PEP 308", which includes a lot of interesting historical context related to this question.', '2013-01-10'),
  (3, 3, '<expression 2> if <condition> else <expression 1>', '2010-05-27'),
  (4, 4, 'This one emphasizes the primary intent of the ternary operator: value selection. It also shows that more than one ternary can be chained together into a single expression.', '2010-10-04'),
  (5, 5, 'What is the difference between a function decorated with @staticmethod and one decorated with @classmethod?', '2008-09-25'),
  (6, 6, 'tl;dr >> when compared to normal methods, the static methods and class methods can also be accessed using the class but unlike class methods, static methods are immutable via inheritance.', '2018-07-11'),
  (7, 7, 'Basically @classmethod makes a method whose first argument is the class it''s called from (rather than the class instance), @staticmethod does not have any implicit arguments.','2008-09-25'),
  (8, 8, '@classmethod : can be used to create a shared global access to all the instances created of that class..... like updating a record by multiple users.... I particulary found it use ful when creating singletons as well..:)\n@static method: has nothing to do with the class or instance being associated with ...but for readability can use static method', '2017-09-20'),
  (9, 9, 'I have an array of numbers and I''m using the .push() method to add elements to it.\nIs there a simple way to remove a specific element from an array?\nI''m looking for the equivalent of something like:\narray.remove(number);\nI have to use core JavaScript. Frameworks are not allowed.', '2011-04-23'),
  (10, 10, 'Find the index of the array element you want to remove using indexOf, and then remove that index with splice.', '2011-04-23'),
  (11, 11, 'Serious question: why doesn''t JavaScript allow the simple and intuitive method of removing an element at an index? A simple, elegant, myArray.remove(index); seems to be the best solution and is implemented in many other languages (a lot of them older than JavaScript.)', '2020-09-10'),
  (12, 12, 'I''m trying to use the code from this repository. The problem is that it isn''t a package (I think?) because it can''t be found on PyPI and there''s no setup.py file (so I can''t use pip install git+<repo link>). How would I then be able to use this code in my project?', '2021-03-28'),
  (13, 13, 'That repository doesn''t seem to be properly packaged for library use at all. I''d recommend forking it, making the changes you need to make it usable (moving the files into a package, adding a setup.py) and then using that as a git+https:// style requirement.', '2021-03-28'),
  (14, 14, 'Exactly, the repo is also under an MIT License, which really even allows keeping the source files in the project directly.', '2021-03-28'),
  (15, 15, 'How do you set, clear, and toggle a bit?', '2019-08-22'),
  (16, 16, 'Use the bitwise OR operator (|) to set a bit.', '2020-09-20'),
  (17, 17, 'You are very dumb haha','2020-09-20');


-- R10
INSERT INTO answer(id) VALUES (3);
INSERT INTO answer(id) VALUES (7);
INSERT INTO answer(id) VALUES (8);
INSERT INTO answer(id) VALUES (10);
INSERT INTO answer(id) VALUES (13);
INSERT INTO answer(id) VALUES (16);

-- R11

INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (1, 3, 'Does Python have a ternary conditional operator?', 0, true);
INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (5, NULL, 'Difference between staticmethod and classmethod', 20, false);
INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (9, 10, 'How can I remove a specific item from an array?', 0, true);
INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (12, NULL, 'How to use non-packaged Python code from GitHub?', 30, false);
INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (15, 16, 'How do you set, clear, and toggle a single bit?', 0, true);


-- R12
INSERT INTO answer_question(id_answer, id_question) VALUES (3, 1);
INSERT INTO answer_question(id_answer, id_question) VALUES (10, 9);
INSERT INTO answer_question(id_answer, id_question) VALUES (16, 15);
INSERT INTO answer_question(id_answer, id_question) VALUES (7, 5);
INSERT INTO answer_question(id_answer, id_question) VALUES (8, 5);
INSERT INTO answer_question(id_answer, id_question) VALUES (13, 12);


-- R13
INSERT INTO comment(id, id_question, id_answer) VALUES (2, 1, NULL);
INSERT INTO comment(id, id_question, id_answer) VALUES (4, NULL, 3);
INSERT INTO comment(id, id_question, id_answer) VALUES (6, 5, NULL);
INSERT INTO comment(id, id_question, id_answer) VALUES (11, NULL, 10);
INSERT INTO comment(id, id_question, id_answer) VALUES (14, 12, NULL);
INSERT INTO comment(id, id_question, id_answer) VALUES (17, 15, NULL);

-- R14
INSERT INTO vote(id_post, id_user, value) VALUES
  (1, 1, 1),
  (1, 2, -1),
  (1, 3, 1),
  (1, 4, -1),
  (3, 6, 1),
  (3, 7, -1),
  (5, 10, 1),
  (5, 15, 1),
  (5, 11, 1);

-- R15
INSERT INTO edit_proposal(id, id_post, id_user, id_moderator, body) VALUES
  (1, 3, 5, NULL, '<expression 2> if <condition> else <expression 1>');


-- R16
INSERT INTO topic (id, name) VALUES (1, 'cpp');
INSERT INTO topic (id, name) VALUES (2, 'python');
INSERT INTO topic (id, name) VALUES (3, 'javascript');
INSERT INTO topic (id, name) VALUES (4, 'c');
INSERT INTO topic (id, name) VALUES (5, 'sql');
INSERT INTO topic (id, name) VALUES (6, 'prolog');

--R17
INSERT INTO topic_question (id_topic, id_question) VALUES (2, 1);
INSERT INTO topic_question (id_topic, id_question) VALUES (2, 5);
INSERT INTO topic_question (id_topic, id_question) VALUES (3, 9);
INSERT INTO topic_question (id_topic, id_question) VALUES (2, 12);
INSERT INTO topic_question (id_topic, id_question) VALUES (1, 15);
INSERT INTO topic_question (id_topic, id_question) VALUES (4, 15);

-- R18
INSERT INTO report (id_post, reporter, "date", reason, state, reviewer) VALUES (15, 15, '2020-07-29', 'He called me dumb', 'pending', 2);

-- R19
INSERT INTO notification (id, "date", title, body, recipient) VALUES (1, '2021-03-01', 'New achievement', 'You have achieved: ', 1);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (2, '2021-03-02', 'New achievement', 'You have achieved: ', 5);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (3, '2021-03-03', 'New achievement', 'You have achieved: ', 9);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (4, '2021-03-04', 'New achievement', 'You have achieved: ', 12);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (5, '2021-03-05', 'New achievement', 'You have achieved: ', 15);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (6, '2021-03-06', 'New achievement', 'You have achieved: ', 3);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (7, '2021-03-07', 'New achievement', 'You have achieved: ', 10);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (8, '2021-03-08', 'New achievement', 'You have achieved: ', 16);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (9, '2021-03-10', 'New answer', 'Someone answered your question: ', 1);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (10, '2021-03-11', 'New answer', 'Someone answered your question: ', 9);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (11, '2021-03-12', 'New answer', 'Someone answered your question: ', 15);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (12, '2021-03-13', 'New answer', 'Someone answered your question: ', 5);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (13, '2021-03-14', 'New answer', 'Someone answered your question: ', 5);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (14, '2021-03-15', 'New answer', 'Someone answered your question: ', 12);

-- R20
INSERT INTO notification_achievement (id, id_achievement) VALUES (1, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (2, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (3, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (4, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (5, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (6, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (7, 2);
INSERT INTO notification_achievement (id, id_achievement) VALUES (8, 2);
INSERT INTO notification_achievement (id, id_achievement) VALUES (9, 2);

-- R21
INSERT INTO notification_post (id, id_post) VALUES (9, 1);
INSERT INTO notification_post (id, id_post) VALUES (10, 9);
INSERT INTO notification_post (id, id_post) VALUES (11, 15);
INSERT INTO notification_post (id, id_post) VALUES (12, 5);
INSERT INTO notification_post (id, id_post) VALUES (13, 5);
INSERT INTO notification_post (id, id_post) VALUES (14, 12);

Revision history

Changes made to the first submission:

None

GROUP2113, 18/04/2021

Ana Inês Oliveira de Barros, up201806593@fe.up.pt
João de Jesus Costa, up201806560@fe.up.pt
João Lucas Silva Martins, up201806436@fe.up.pt
Ricardo Jorge Cruz Fontão, up201806317@fe.up.pt (Editor)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ebd