**4. A4: Conceptual Data Model**

The presented UML below shows the organisation and structures of the various objects that make part of Askit's system, as well as its attributes, domains, relations between them and their multiplicity.
Our Business Rules are not represented in the UML but in a text box below the diagram.

**4.1 Class Diagram**

The UML diagram in Figure 1 presents the main entities and the relationships between them, attributes and some domains, and the multiplicity of relationships for the **Askitt** platform.


<p align= "center">
<b><i>Fig.1 Askit DB UML</i></b>
</p>

**4.2 Additional Business Rules**

- BR01. A user cannot follow his own question.
- BR02. Only one answer can be verified for each question.

**5. A5: Relational Schema, Validation and Schema Refinement**

We mapped the classes, attributes and multiplicities of our UML in our relation schema using the relation compact notation.<br>
We identified the functional dependencies and showed the relational schema below. It was necessary to make any decomposition since they were already in BCNF.

**5.1 Relational Schema**

|Rule number|RelationName|
|---|---|
|R01|user(<u>idUser</u>, email **UK NN**, name **NN**, password **NN**, bio, isModerator **NN**)
|R02|post(<u>idPost</u>, idUser->user, date **NN CK** date > 2022-01-01, score **NN CK** score > 0, content **NN**)|
|R03|answer(<u>idPost</u>->post, idQuestion->question, isVerified **NN**)|
|R04|question(<u>idPost</u>->post, title **NN**)|
|R05|tag(<u>idTag</u>, name **NN**)|
|R06|community(<u>idCommunity</u> , name **NN**, date **NN**)|
|R07|assUserTag(<u>idUserTag</u>, idUser->user, idTag->tag,isFavorite **NN**, isBlocked **NN CK** isFavorite!=isBlocked)
|R08|followed(<u>idUser</u>->user, <u>idPost</u>->question)|
|R09|userCommunity(<u>idUser</u>->user, <u>idCommunity</u>->community|
|R10|questionTag(<u>idQuestionTag</u>, idTag->tag, idQuestion->question)
|R11|interact(<u>idInteract</u>, idUser->user, idPost->post, isLiked **NN**, isDisliked **NN CK** isLiked!=isDisliked)

We chose to map the generalizations in the Object Oriented strategy (O-O-S), given that the child classes have their own different attributes and as both will have a large number of rows in the table, it would be a waste of space and time.

Legend
- UK = UNIQUE KEY
- NN = NOT NULL
- CK = CHECK

**5.2 Domains**

|Domain Name | Domain Specification|
|---|---|
|Post|ENUM('Question', 'Answer')|
|User |ENUM('Guest', 'Askitter' ,'Moderator')|
|content|VARCHAR(500)|
|bio|VARCHAR(250)|
|name|VARCHAR(25)|

**5.3 Schema validation**

|TABLE R01 | User |
|---|---|
| Keys | {idUser}, {email} |
|Functional Dependencies:||
|FD0101| {idUser} -> {email, name, password, bio, isModerator}|
|FD0102| {email}->{idUser, name, password, bio, isModerator} |
|Normal Form|BCNF|

|TABLE R02 | Post |
|---|---|
| Keys | {idPost}|
|Functional Dependencies:||
|FD0201| {idPost} -> {date, score, content}|
|Normal Form|BCNF|

|TABLE R03 | Answer |
|---|---|
| Keys | {idPost}|
|Functional Dependencies:||
|FD0301| {idPost} -> {idQuestion, isVerified}|
|Normal Form|BCNF|

|TABLE R04 | Question |
|---|---|
| Keys | {idPost}|
|Functional Dependencies:||
|FD0401| {idPost} -> {title}|
|Normal Form|BCNF|

|TABLE R05 | Tag |
|---|---|
| Keys | {idTag}|
|Functional Dependencies:||
|FD0501| {idTag} -> {name}|
|Normal Form|BCNF|

|TABLE R06 | Community |
|---|---|
| Keys | {idCommunity}|
|Functional Dependencies:||
|FD0601| {idCommunity} -> {name, date}|
|Normal Form|BCNF|

|TABLE R07 | AssUserTag |
|---|---|
| Keys | {idUserTag}|
|Functional Dependencies:||
|FD0701|{idUserTag} -> {idUser, idTag, isBlocked, isFavorite} |
|Normal Form|BCNF|

|TABLE R08 | Followed |
|---|---|
| Keys | {idUser, idPost}|
|Functional Dependencies:|none|
|Normal Form|BCNF|

|TABLE R09 | userCommunity |
|---|---|
| Keys | {idUser, idCommunity}|
|Functional Dependencies:|none|
|Normal Form|BCNF|

|TABLE R10 | questionTag |
|---|---|
| Keys | {idQuestionTag}|
|Functional Dependencies:||
|FD1001 {idQuestionTag} -> {idQuestion, idTag}|
|Normal Form|BCNF|

|TABLE R11 | interact |
|---|---|
| Keys | {idInteract}|
|Functional Dependencies:||
|FD1001 {idInteract} -> {idPost, idUser, isLiked, isDisliked}|
|Normal Form|BCNF|

Because all relations are in the Boyce–Codd Normal Form (BCNF), the relational schema is also in the BCNF and, therefore, the schema does not need to be further normalised.

**SQL CODE**

---- TYPES -----

CREATE TYPE User_ AS ENUM ('Askitter', 'Guest', 'Moderator');

--- TABLE ----

DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS post CASCADE;
DROP TABLE IF EXISTS answer CASCADE;
DROP TABLE IF EXISTS question CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
DROP TABLE IF EXISTS community CASCADE;
DROP TABLE IF EXISTS assUserTag CASCADE;
DROP TABLE IF EXISTS followed CASCADE;
DROP TABLE IF EXISTS userCommunity CASCADE;
DROP TABLE IF EXISTS interact CASCADE;


CREATE TABLE users (
idUser SERIAL PRIMARY KEY,
email TEXT NOT NULL CONSTRAINT user_email_uk UNIQUE,
name TEXT NOT NULL,
password TEXT NOT NULL,
bio TEXT,
isModerador BOOL NOT NULL
);

CREATE TABLE post (
idPost SERIAL PRIMARY KEY,
idUser INTEGER REFERENCES users (idUser) ON UPDATE CASCADE,
date DATE NOT NULL,
score INTEGER NOT NULL,
content TEXT NOT NULL
);

CREATE TABLE question (
idPost INTEGER PRIMARY KEY REFERENCES post (idPost) ON UPDATE CASCADE,
title TEXT NOT NULL
);

CREATE TABLE answer (
idPost INTEGER PRIMARY KEY REFERENCES post (idPost) ON UPDATE CASCADE,
idQuestion INTEGER REFERENCES question (idPost) ON UPDATE CASCADE,
isVerified BOOLEAN NOT NULL
);

CREATE TABLE tag (
idTag SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);

CREATE TABLE community (
idCommunity SERIAL PRIMARY KEY,
name TEXT NOT NULL,
date DATE NOT NULL
);

CREATE TABLE assUserTag (
idUserTag SERIAL PRIMARY KEY,
idUser INTEGER REFERENCES users (idUser) ON UPDATE CASCADE,
idTag INTEGER REFERENCES tag (idTag) ON UPDATE CASCADE,
isFavorite BOOLEAN NOT NULL,
isBlocked BOOLEAN NOT NULL,
CONSTRAINT fav_blocked_ck CHECK (isFavorite != isBlocked)
);

CREATE TABLE followed (
idUser INTEGER NOT NULL REFERENCES users (idUser) ON UPDATE CASCADE,
idPost INTEGER NOT NULL REFERENCES post (idPost) ON UPDATE CASCADE,
PRIMARY KEY (idUser, idPost)
);

CREATE TABLE userCommunity (
idUser INTEGER NOT NULL REFERENCES users (idUser) ON UPDATE CASCADE,
idCommunity INTEGER NOT NULL REFERENCES community (idCommunity) ON UPDATE CASCADE,
PRIMARY KEY (idUser, idCommunity)
);

CREATE TABLE questionTag (
idQuestionTag SERIAL PRIMARY KEY,
idQuestion INTEGER REFERENCES question (idPost) ON UPDATE CASCADE, 
idTag INTEGER REFERENCES tag (idTag) ON UPDATE CASCADE
);

CREATE TABLE interact (
idInteract SERIAL PRIMARY KEY,
idPost INTEGER REFERENCES post (idPost) ON UPDATE CASCADE,
idUser INTEGER REFERENCES users (idUser) ON UPDATE CASCADE
isLiked BOOLEAN NOT NULL, 
isDisliked BOOLEAN NOT NULL,
CONSTRAINT like_dislike_ck CHECK (isLiked != isDisliked)
);

# A06. Indexes, triggers, transactions and database population

The database workload shows the number of rows we estimate to be in each table, as well as their expected growth. <br>
We also created 3 performance indexes and 4 full text-search indexes in order to improve our DB performance and access information in a more efficient way, given that it has some tables with enormous amount of data. <br>
Furthermore, we developed a couple of triggers and transactions so that the integrity of data was maintained throughout the whole process.

**1. Database workload**

|Relation|RelationName|Order of magnitude|Estimated growth
|---|---|---|---|
|R01|user| 10k(tens of thousands) | 15(tens)/day
|R02|post| 100k | 100 (hundreds)/day
|R03|answer| 60k | 60/day
|R04|question| 40k | 40/day
|R05|tag| 500 | 5/day
|R06|community| 2k | 10/day
|R07|assUserTag| 1k | 10/day
|R08|followed| 10k | 50/day
|R09|userCommunity| 10k | 50/day
|R10|questionTag|40k| 40/day
|R11|interact|300k|300/day

**2. Proposed Indexes**

**2.1 Performance indices**

|Index| IDX01 |
|---|---|
|**Index relation** | post |
|**Index attribute** | idUser |
|**Index type** | B-tree |
|**Cardinality** | Medium |
|**Clustering** | Yes |
|**Justification**| Table 'post' is very large. Several queris need to frequently filter access to the posts by its owner (user). Filtering is done by exact match, thus an hash type index would be best suited. However, since we also want to apply clustering based on this index, and clustering is not possible on hash type indexes, we opted for a b-tree index. Update frequency is low and cardinality is medium so it's a good candidate for clustering.|

**SQL Code**

CREATE INDEX user_post ON post USING btree (idPost); 
CLUSTER post USING idPost 

|Index| IDX02 |
|---|---|
|**Index relation** | answer |
|**Index attribute** | idQuestion |
|**Index type** | B-tree |
|**Cardinality** | Medium |
|**Clustering** | Yes |
|**Justification**| Several queries need to frequently filter access to the answers by its respective question. Filtering is done by exact match, thus an hash type index would be best suited. However, since we also want to apply clustering based on this index, and clustering is not possible on hash type indexes, we opted for a b-tree index. Update frequency is low and cardinality is medium so it’s a good candidate for clustering. |

**SQL Code**

CREATE INDEX answer_question ON answer USING btree (idQuestion);
CLUSTER answer USING idQuestion

|Index| IDX03 |
|---|---|
|**Index relation** | questionTag |
|**Index attribute** | idTag |
|**Index type** | B-tree |
|**Cardinality** | Medium |
|**Clustering** | Yes |
|**Justification**| Several queries need to frequently filter access to the questions by the respective tag. Filtering is done by exact match, thus an hash type index would be best suited. However, since we also want to apply clustering based on this index, and clustering is not possible on hash type indexes, we opted for a b-tree index. Update frequency is low and cardinality is medium so it’s a good candidate for clustering. |

**SQL Code**

CREATE INDEX questionbytag ON questionTag USING btree (idTag);
CLUSTER questionTag USING idTag

**2.2 Full Text Search Index**

|Index |IDX04 |
|---|---|
|**Index relation**|question |
|**Index attribute**|title |
|**Index type** |GIN |
|**Clustering** | No |
|**Justification**| To provide full-text search features to look for questions based on matching titles. The index type is GIN because the indexed fields are not expected to change often. |

**SQL Code**

-- Add column to question to store computed ts_vectors.
ALTER TABLE question
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION question_search_update() RETURNS TRIGGER AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = (
         setweight(to_tsvector('english', NEW.title), 'A')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.title <> OLD.title) THEN
           NEW.tsvectors = (
             setweight(to_tsvector('english', NEW.title), 'A')
           );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on work.
CREATE TRIGGER question_search_update
 BEFORE INSERT OR UPDATE ON question
 FOR EACH ROW
 EXECUTE PROCEDURE question_search_update();


-- Finally, create a GIN index for ts_vectors.
CREATE INDEX search_idx ON question USING GIN (tsvectors);

|Index |IDX05 |
|---|---|
|**Index relation**|community |
|**Index attribute**|name |
|**Index type** |GIN |
|**Clustering** |No |
|**Justification**| To provide full-text search features to look for communities based on matching names. The index type is GIN because the indexed fields are not expected to change often. |

**SQL Code**

-- Add column to question to store computed ts_vectors.
ALTER TABLE community
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION community_search_update() RETURNS TRIGGER AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = (
         setweight(to_tsvector('english', NEW.name), 'A')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.name <> OLD.name) THEN
           NEW.tsvectors = (
             setweight(to_tsvector('english', NEW.name), 'A')
           );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on work.
CREATE TRIGGER community_search_update
 BEFORE INSERT OR UPDATE ON community
 FOR EACH ROW
 EXECUTE PROCEDURE community_search_update();


-- Finally, create a GIN index for ts_vectors.
CREATE INDEX search_idx ON community USING GIN (tsvectors);

|Index |IDX06 |
|---|---|
|**Index relation**|user |
|**Index attribute**|name |
|**Index type** |GIN |
|**Clustering** |No |
|**Justification**| To provide full-text search features to look for users based on matching names. The index type is GIN because the indexed fields are not expected to change often. |

**SQL Code**

-- Add column to question to store computed ts_vectors.
ALTER TABLE user
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION user_search_update() RETURNS TRIGGER AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = (
         setweight(to_tsvector('english', NEW.name), 'A')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.name <> OLD.name) THEN
           NEW.tsvectors = (
             setweight(to_tsvector('english', NEW.name), 'A')
           );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on work.
CREATE TRIGGER user_search_update
 BEFORE INSERT OR UPDATE ON user
 FOR EACH ROW
 EXECUTE PROCEDURE user_search_update();


-- Finally, create a GIN index for ts_vectors.
CREATE INDEX search_idx ON user USING GIN (tsvectors);

**3. Triggers**

|Trigger| TRIGGER01 |
|---|---|
| Description | A user cannot follow his/hers own question|
| Justification | A user should only be able to follow questions of other people, as he will already be notified when their question is interacted with. |

**SQL Code**

CREATE FUNCTION not_follow() RETURN TRIGGER AS 

$BODY$ 

BEGIN 
  IF EXISTS (SELECT * FROM followed WHERE NEW.idUser = followed.idUser && SELECT * FROM question WHERE NEW.idQuestion = question.idQuestion) THEN  

       RAISE EXCEPTION 'A user cannot follow his own question';

       END IF; 

       RETURN NEW; 

END 

$BODY$ 

LANGUAGE plpgsql;

CREATE TRIGGER not_follow

      BEFORE INSERT OR UPDATE ON followed

      FOR EACH ROW 

      EXECUTE PROCEDURE not_follow(); 

|Trigger| TRIGGER02 |
|---|---|
|Description|Delete user records after he is deleted. |
| Justification | The entirety of the user's data should be removed if/when he chooses to delete his account. |

**SQL Code**

CREATE FUNCTION delete_user() RETURNS TRIGGER AS
$BODY$
BEGIN
        DELETE FROM assUsertag where (NEW.idUser = idUser);
        DELETE FROM userCommunity where (NEW.idUser = idUser);
        DELETE FROM posts where (NEW.idUser = idUser);
        DELETE FROM followed where (NEW.idUser = idUser);
        RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER delete_user
        BEFORE DELETE ON user
        FOR EACH ROW
        EXECUTE PROCEDURE delete_user();

**4. Transactions**

|Transaction| TR01 |
|---|---|
|Description| Insert a new question |
|Justification| In order to maintain consistency, it's necessary to use a transaction to ensure that all the code executes without errors. If an error occurs, a ROLLBACK is issued (e.g. when the insertion of a post fails). The isolation level is Repeatable Read, because, otherwise, an update of post_id_seq could happen, due to an insert in the table post committed by a concurrent transaction, and as a result, inconsistent data would be stored. |
|Isolation level| REPEATABLE READ |

**SQL Code**

BEGIN TRANSACTION; 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
-- Insert question 
INSERT INTO question (idPost, title)
VALUES (currval('post_id_seq'), $title) 
-- Insert post 
INSERT INTO post (idPost, idUser, date, score, content) VALUES (currval('post_id_seq'), $idUser, $date, $score, $content); END TRANSACTION;

|Transaction| TR02 |
|---|---|
|Description| Get current number of user's posts |
|Justification| In the middle of the transaction, the insertion of new rows in the post table can occur, which implies that the information retrieved in both selects is different, consequently resulting in a Phantom Read. It's READ ONLY because it only uses Selects. |
|Isolation level| SERIALIZABLE READ ONLY |

**SQL Code**

BEGIN TRANSACTION; 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY SELECT COUNT(*) 
FROM post 
WHERE NEW.idUser = post.idUser AND date < now();
END TRANSACTION;

|Transaction| TR03 |
|---|---|
|Description| Insert a new answer |
|Justification| In order to maintain consistency, it's necessary to use a transaction to ensure that all the code executes without errors. As an answer is also a post, we need to make sure that when there is an insertion in the table answer, there also needs to be an insertion in the table post. If an error occurs, a ROLLBACK is issued (e.g. when the insertion of an answer fails). The isolation level is Repeatable Read, because, otherwise, an update of post_id_seq could happen, due to an insert in the table post committed by a concurrent transaction, and as a result, inconsistent data would be stored. |
|Isolation level| REPEATABLE READ |

**SQL Code**

BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
-- Insert answer 
INSERT INTO answer (idPost, idQuestion, isVerified ) 
VALUES (currval('post_id_seq'), $idQuestion, $isVerified) 
-- Insert post
INSERT INTO post (idPost, idUser, date, score, content) VALUES (currval('post_id_seq'), $idUser, $date, $score, $content); 
END TRANSACTION;

------------------

**SQL CODE**

DROP SCHEMA IF EXISTS local CASCADE;
CREATE SCHEMA local;

SET search_path to local;

DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS post CASCADE;
DROP TABLE IF EXISTS answer CASCADE;
DROP TABLE IF EXISTS question CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
DROP TABLE IF EXISTS community CASCADE;
DROP TABLE IF EXISTS assUserTag CASCADE;
DROP TABLE IF EXISTS followed CASCADE;
DROP TABLE IF EXISTS userCommunity CASCADE;


CREATE TABLE users (
idUser SERIAL PRIMARY KEY,
email TEXT NOT NULL CONSTRAINT user_email_uk UNIQUE,
name TEXT NOT NULL,
password TEXT NOT NULL,
bio TEXT,
isModerador BOOL NOT NULL
);

CREATE TABLE post (
idPost SERIAL PRIMARY KEY,
idUser INTEGER REFERENCES users (idUser) ON UPDATE CASCADE,
date DATE NOT NULL,
score INTEGER NOT NULL,
content TEXT NOT NULL
);

CREATE TABLE question (
idPost INTEGER PRIMARY KEY REFERENCES post (idPost) ON UPDATE CASCADE,
title TEXT NOT NULL
);

CREATE TABLE answer (
idPost INTEGER PRIMARY KEY REFERENCES post (idPost) ON UPDATE CASCADE,
idQuestion INTEGER REFERENCES question (idPost) ON UPDATE CASCADE,
isVerified BOOLEAN NOT NULL
);

CREATE TABLE tag (
idTag SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);

CREATE TABLE community (
idCommunity SERIAL PRIMARY KEY,
name TEXT NOT NULL,
date DATE NOT NULL
);

CREATE TABLE assUserTag (
idUserTag SERIAL PRIMARY KEY,
idUser INTEGER REFERENCES users (idUser) ON UPDATE CASCADE,
idTag INTEGER REFERENCES tag (idTag) ON UPDATE CASCADE,
isFavorite BOOLEAN NOT NULL,
isBlocked BOOLEAN NOT NULL,
CONSTRAINT fav_blocked_ck CHECK (isFavorite != isBlocked)
);

CREATE TABLE followed (
idUser INTEGER NOT NULL REFERENCES users (idUser) ON UPDATE CASCADE,
idPost INTEGER NOT NULL REFERENCES post (idPost) ON UPDATE CASCADE,
PRIMARY KEY (idUser, idPost)
);

CREATE TABLE userCommunity (
idUser INTEGER NOT NULL REFERENCES users (idUser) ON UPDATE CASCADE,
idCommunity INTEGER NOT NULL REFERENCES community (idCommunity) ON UPDATE CASCADE,
PRIMARY KEY (idUser, idCommunity)
);

CREATE TABLE questionTag (
idQuestionTag SERIAL PRIMARY KEY,
idQuestion INTEGER REFERENCES question (idPost) ON UPDATE CASCADE, 
idTag INTEGER REFERENCES tag (idTag) ON UPDATE CASCADE
);

CREATE TABLE interact (
idInteract SERIAL PRIMARY KEY,
idPost INTEGER REFERENCES post (idPost) ON UPDATE CASCADE,
idUser INTEGER REFERENCES users (idUser) ON UPDATE CASCADE
isLiked BOOLEAN NOT NULL, 
isDisliked BOOLEAN NOT NULL,
CONSTRAINT like_dislike_ck CHECK (isLiked != isDisliked)
);


-- Indexes --

CREATE INDEX user_post ON post USING btree (idPost); 
CLUSTER post USING user_post;

CREATE INDEX answer_question ON answer USING btree (idQuestion);
CLUSTER answer USING answer_question;

CREATE INDEX questionbytag ON questionTag USING btree (idTag);
CLUSTER questionTag USING questionbytag;

-- Triggers --

CREATE FUNCTION delete_user() RETURNS TRIGGER AS
$BODY$
BEGIN
        DELETE FROM assUsertag where (NEW.idUser = idUser);
        DELETE FROM userCommunity where (NEW.idUser = idUser);
        DELETE FROM posts where (NEW.idUser = idUser);
        DELETE FROM followed where (NEW.idUser = idUser);
        RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER delete_user
        BEFORE DELETE ON users
        FOR EACH ROW
        EXECUTE PROCEDURE delete_user();


-- Add column to question to store computed ts_vectors.
ALTER TABLE users
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION user_search_update() RETURNS TRIGGER AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = (
         setweight(to_tsvector('english', NEW.name), 'A')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.name <> OLD.name) THEN
           NEW.tsvectors = (
             setweight(to_tsvector('english', NEW.name), 'A')
           );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on work.
CREATE TRIGGER user_search_update
 BEFORE INSERT OR UPDATE ON users
 FOR EACH ROW
 EXECUTE PROCEDURE user_search_update();


-- Finally, create a GIN index for ts_vectors.
CREATE INDEX search_idx_user ON users USING GIN (tsvectors);


-- Add column to question to store computed ts_vectors.
ALTER TABLE community
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION community_search_update() RETURNS TRIGGER AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = (
         setweight(to_tsvector('english', NEW.name), 'A')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.name <> OLD.name) THEN
           NEW.tsvectors = (
             setweight(to_tsvector('english', NEW.name), 'A')
           );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on work.
CREATE TRIGGER community_search_update
 BEFORE INSERT OR UPDATE ON community
 FOR EACH ROW
 EXECUTE PROCEDURE community_search_update();


-- Finally, create a GIN index for ts_vectors.
CREATE INDEX search_idx_community ON community USING GIN (tsvectors);


-- Add column to question to store computed ts_vectors.
ALTER TABLE tag
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION tag_search_update() RETURNS TRIGGER AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = (
         setweight(to_tsvector('english', NEW.name), 'A')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.name <> OLD.name) THEN
           NEW.tsvectors = (
             setweight(to_tsvector('english', NEW.name), 'A')
           );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on work.
CREATE TRIGGER tag_search_update
 BEFORE INSERT OR UPDATE ON tag
 FOR EACH ROW
 EXECUTE PROCEDURE tag_search_update();


-- Finally, create a GIN index for ts_vectors.
CREATE INDEX search_idx_tag ON tag USING GIN (tsvectors);


-- Add column to question to store computed ts_vectors.
ALTER TABLE question
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION question_search_update() RETURNS TRIGGER AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = (
         setweight(to_tsvector('english', NEW.title), 'A')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.title <> OLD.title) THEN
           NEW.tsvectors = (
             setweight(to_tsvector('english', NEW.title), 'A')
           );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on work.
CREATE TRIGGER question_search_update
 BEFORE INSERT OR UPDATE ON question
 FOR EACH ROW
 EXECUTE PROCEDURE question_search_update();


-- Finally, create a GIN index for ts_vectors.
CREATE INDEX search_idx_vector ON question USING GIN (tsvectors);


--- Transactions ---


BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Insert answer 
INSERT INTO answer (idPost, idQuestion, isVerified )
VALUES (currval('post_id_seq'), new.idQuestion, new.isVerified);
-- Insert post
INSERT INTO post (idPost, idUser, date, score, content) VALUES (currval('post_id_seq'), new.idUser, new.date, new.score, new.content); 
END TRANSACTION;


BEGIN TRANSACTION; 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
SELECT COUNT(*) 
FROM post 
WHERE NEW.idUser = post.idUser AND date < now();
END TRANSACTION;


BEGIN TRANSACTION; 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Insert question 
INSERT INTO question (idPost, title)
VALUES (currval('post_id_seq'), new.title);
-- Insert post 
INSERT INTO post (idPost, idUser, date, score, content) VALUES (currval('post_id_seq'), new.idUser, new.date, new.score, new.content); 
END TRANSACTION;

**5. Database Population**

INSERT INTO user (idUser, email, name, password, bio, isModerator) 
VALUES (1, "[e-mail]", "Alberto", "a", "Abio", True);
INSERT INTO user (idUser, email, name, password, bio, isModerator) 
VALUES (2, "[e-mail]", "Berto", "b", "Bbio", False);
INSERT INTO user (idUser, email, name, password, bio, isModerator) 
VALUES (3, "[e-mail]", "Calberto", "c", "Cbio", False);

INSERT INTO post (idPost, idUser, date, score, content) 
VALUES (1, 1, '2000-01-01' , 0, 'Content #1');
INSERT INTO post (idPost, idUser, date, score, content) 
VALUES (2, 1, '2021-01-01' , 6, 'Content #2');
INSERT INTO post (idPost, idUser, date, score, content) 
VALUES (3, 2, '2015-01-01' , 3, 'Content #3');
INSERT INTO post (idPost, idUser, date, score, content) 
VALUES (4, 2, '2015-01-01' , 3, 'Yes');



INSERT INTO question(idPost, title)
VALUES (1, 'Can dogs bounce?');
INSERT INTO question(idPost, title)
VALUES (2, 'Can pandas bounce?');
INSERT INTO question(idPost, title)
VALUES (3, 'Can chicken bounce?');

INSERT INTO answer(idPost, idQuestion, isVerified)
VALUES (4, 1, True);

INSERT INTO tag(idTag, name)
VALUES (1, 'Politics');

INSERT INTO tag(idTag, name)
VALUES (2, 'Religion');

INSERT INTO tag(idTag, name)
VALUES (3, 'Youtube');

INSERT INTO community (idCommunity, name, date)
VALUES (1, 'Children united', '2001-09-12');
INSERT INTO community (idCommunity, name, date)
VALUES (2, 'Love & Peace', '2011-10-12');

INSERT INTO assUserTag (idUserTag, idUser, idTag, isFavorite, isBlocked)
VALUES (1, 1, 1, True, False);
INSERT INTO assUserTag (idUserTag, idUser, idTag, isFavorite, isBlocked)
VALUES (2, 1, 2, False, True);

INSERT INTO followed (idUser, idPost)
VALUES (1,3);

INSERT INTO userCommunity (idUser, idCommunity)
VALUES (1, 2);
INSERT INTO userCommunity (idUser, idCommunity)
VALUES (2, 1);

INSERT INTO questionTag (idQuestionTag, idQuestion, idTag)
VALUES (1, 1, 3);

INSERT INTO interact (idPost, idUser)
VALUES (1,1)

## Revision History

Changes made to the second submission:

1. 22/10/2022- Added content relative of A4.

2. 22/10/2022- Added content relative of A5.

3. 23/10/2022- Added content relative of A6 and database schema and population.

-----
GROUP22112, 24/10/2022
* Group member 1: Luís Tiago Trindade Cabral, [e-mail] (Editor)
* Group member 2: Rui Brogueira Andrade, [e-mail] (Editor)
* Group member 3: Bárbara Filipa da Silva Carvalho, [e-mail] (Editor)
* Group member 4: José Miguel Carvalho Rodrigues, [e-mail] (Editor)