EBD: Database Specification Component

To transform how movie and TV enthusiasts discover information, share news, and connect with one another, aiming to become the largest collaborative platform for entertainment lovers to engage and stay updated with the latest entertainment news.

A4: Conceptual Data Model

This section provides a description of the entities and relationships within the NewsHub project and its database specification.

1. Class diagram

UML drawio

2. Additional Business Rules

Identifier	Description
BR08	A user can only like a news item once
BR09	A user can only like a comment once
BR10	A blocked user must provide an appeal description if they have appealled to be unblocked
BR11	A blocked user can only provide an appeal description if they have appealled to be unblocked
BR12	An administrator is the only one able to grant an influencer with a privilege
BR13	An administrator is the only one able to access the reports

A5: Relational Schema, validation and schema refinement

This section contains the relational schema derived from the conceptual data model. The schema defines the structure of the relational database, ensures its integrity, and improves its design.

1. Relational Schema

Relation reference	Relation Compact Notation
R01	user(id, name NN, email UK NN, password NN, reputation NN DF 0 CK reputation >= 0, image_id -> image)
R02	administrator(id, name NN, email UK NN, password NN, image_id -> image)
R03	influencer(user_id -> user, start_date NN DF Today, has_privilege NN DF false)
R04	blocked(user_id -> user, date NN DF Today, appeal NN DF false, appeal_description)
R05	news(id, content NN, published_date NN DF Today, author_id -> user, image_id -> image)
R06	comment(id, content NN, published_date NN DF Today, news_id -> news NN, author_id -> user, image_id -> image)
R07	tag(id, name UK NN)
R08	follow((follower_id</ins, followed_id) -> user
R09	follow_tag(user_id -> user, tag_id -> tag)
R10	tag_news(news_id -> news, tag_id -> tag)
R11	favorite( user_id -> user, news_id -> news)
R12	report(id, description NN, created_date NN DF Today, user_id -> user, comment_id -> comment, news_id -> news)
R13	like(id, sender_id -> user, comment_id -> comment, news_id -> news)
R14	notification(id, sender_id -> user_id NN, receiver_id -> user_id NN, date NN DF Today, viewed NN DF False, notification_type NN CK notification_type IN Notification_types)
R15	notification_comment(notification_id -> notification, comment_id -> comment UK NN)
R16	notification_like(notification_id -> notification, like_id -> like UK NN)
R17	image(id, image_path UK NN)

Legend:

UK = UNIQUE KEY
NN = NOT NULL
DF = DEFAULT
CK = CHECK.

2. Domains

Domain Name	Domain Specification
Today	DATE DEFAULT CURRENT_DATE
Notification_types	ENUM(‘Like_notification’, ‘Comment_notification’, ‘Follow_notification’)

3. Schema validation

TABLE R01	user
Keys	{ id }, { email }
Functional Dependencies:
FD0101	id → {email, name, password, reputation, image_id }
FD0102	email → {id, name, password, reputation, image_id }
NORMAL FORM	BCNF

TABLE R02	administrator
Keys	{ id }, { email }
Functional Dependencies:
FD0201	id → {email, name, password, image_id }
FD0202	email → {id, name, password, image_id }
NORMAL FORM	BCNF

TABLE R03	influencer
Keys	{ user_id }
Functional Dependencies:
FD0301	user_id → { start_date, has_privilege}
NORMAL FORM	BCNF

TABLE R04	blocked
Keys	{ user_id }
Functional Dependencies:
FD0401	user_id → {date, appeal, appeal_description}
NORMAL FORM	BCNF

TABLE R05	news
Keys	{ id }
Functional Dependencies:
FD0501	id → {content, published_date, author_id, image_id}
NORMAL FORM	BCNF

TABLE R06	comment
Keys	{ id }
Functional Dependencies:
FD0601	id → { content, published_date, news_id, author_id, image_id}
NORMAL FORM	BCNF

TABLE R07	tag
Keys	{ id }, { name }
Functional Dependencies:
FD0701	id → {name}
FD0702	name → {id}
NORMAL FORM	BCNF

TABLE R08	follow
Keys	{ follower_id, followed_id }
Functional Dependencies:	None
NORMAL FORM	BCNF

TABLE R09	follow_tag
Keys	{ user_id, tag_id }
Functional Dependencies:	None
NORMAL FORM	BCNF

TABLE R10	tag_news
Keys	{ news_id, tag_id }
Functional Dependencies:	None
NORMAL FORM	BCNF

TABLE R11	favorite
Keys	{ user_id, news_id }
Functional Dependencies:	None
NORMAL FORM	BCNF

TABLE R12	report
Keys	{ id }
Functional Dependencies:
FD1201	id → {description, created_date, user_id, comment_id, news_id}
NORMAL FORM	BCNF

TABLE R13	like
Keys	{ id }
Functional Dependencies:
FD1301	id → { sender_id, comment_id, news_id}
NORMAL FORM	BCNF

TABLE R14	notification
Keys	{ id }
Functional Dependencies:
FD1401	id → { sender_id, receiver_id, date, viewed, notification_type }
NORMAL FORM	BCNF

TABLE R15	notification_comment
Keys	{ notification_id }, { comment_id}
Functional Dependencies:
FD1501	notification_id → { comment_id }
FD1502	comment_id → { notification_id }
NORMAL FORM	BCNF

TABLE R16	notification_like
Keys	{ notification_id }, { like_id }
Functional Dependencies:
FD1601	notification_id → { like_id }
FD1602	like_id → { notification_id }
NORMAL FORM	BCNF

TABLE R17	image
Keys	{ id }, { image_path }
Functional Dependencies:
FD1701	id → {image_path}
FD1702	image_path → { id }
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 normalized.

A6: Indexes, triggers, transactions and database population

This artifact contains the database workload, the physical schema, indexes, triggers, and the definitions of functions and transactions required to ensure data integrity. It also includes the complete database creation script, including indexes and triggers.

1. Database Workload

Relation reference	Relation Name	Order of magnitude	Estimated growth
R01	user	10 k	10 / day
R02	administrator	10	1 / semester
R03	influencer	1 k	1 / day
R04	blocked	100	1 / month
R05	news	100 k	1 k / day
R06	comment	1 M	10 k / day
R07	tag	10	1 / month
R08	follow	100 k	100 / day
R09	follow_tag	10 k	10 / day
R10	tag_news	100 k	1 k / day
R11	favorite	100 k	1 k / day
R12	report	100	10 / month
R13	like	10 M	100 k / day
R14	notification	10 M	100 k / day
R15	notification_comment	1 M	10 k / day
R16	notification_like	10 M	100 k / day
R17	image	100 k	1 k / day

2. Proposed Indices

2.1. Performance Indices

Index	IDX01
Relation	news
Attribute	published_date
Type	B-tree
Cardinality	medium
Clustering	No
Justification	The 'news' table is very large, and several queries frequently sort the news by date to generate the recent news feed. Since sorting by date is required, a B-tree index is the most appropriate choice. However, due to the high update frequency, clustering is not ideal as it could negatively impact performance.

`SQL code`

 CREATE INDEX idx_news_date ON news USING btree (published_date);

Index	IDX02
Relation	like
Attribute	news_id, sender_id
Type	B-tree
Cardinality	medium
Clustering	No
Justification	The 'like' table is one of the largest, and many queries frequently filter by news to generate the top news feed. Additionally, several queries need to filter by sender to check whether the news has already been liked by the user or not. Since it is a multi-column index, hash indexing is not supported. Due to the high update frequency, clustering is not ideal, as it could negatively impact performance.

`SQL code`

CREATE INDEX idx_like_news ON likes USING btree (news_id, sender_id) WHERE news_id IS NOT NULL;

Index	IDX03
Relation	notification
Attribute	receiver_id, viewed
Type	B-tree
Cardinality	medium
Clustering	No
Justification	The 'notification' table is one of the largest, and many queries are frequently made to check if a user has received a new notification. Since it uses a multi-column index, hash indexing is not supported. Due to the high update frequency, clustering is not ideal, as it could negatively impact performance.

`SQL code`

CREATE INDEX idx_notification ON notifications USING btree (receiver_id, viewed);

2.2. Full-text Search Indices

Index	IDX04
Relation	news
Attribute	content, author_id
Type	GIN
Clustering	No
Justification	To provide full-text search features to look for news based on matching content or author. The index type is GIN because the indexed fields are not expected to change often.

`SQL code`

ALTER TABLE news ADD COLUMN ts_content_user TSVECTOR;

CREATE OR REPLACE FUNCTION news_content_user_update() RETURNS TRIGGER AS $$
DECLARE
    user_name_value TEXT;
BEGIN
 SELECT COALESCE(user_name, 'Anonymous') INTO user_name_value FROM users WHERE users.id = NEW.author_id;
 
 IF TG_OP = 'INSERT' THEN
        NEW.ts_content_user = (
            setweight(to_tsvector('english', NEW.content), 'A') ||
            setweight(to_tsvector('english', user_name_value), 'B')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.content <> OLD.content OR NEW.author_id <> OLD.author_id) THEN
           NEW.ts_content_user = (
            setweight(to_tsvector('english', NEW.content), 'A') ||
            setweight(to_tsvector('english', user_name_value), 'B')
        );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

CREATE TRIGGER news_content_user_update
    BEFORE INSERT OR UPDATE ON news
    FOR EACH ROW 
    EXECUTE PROCEDURE news_content_user_update();

CREATE INDEX idx_news_content ON news USING GIN (ts_content_user);

Index	IDX05
Relation	comment
Attribute	content, author_id
Type	GIN
Clustering	No
Justification	To provide full-text search features to look for comments based on matching content or author. The index type is GIN because the indexed fields are not expected to change often

`SQL code`

ALTER TABLE comment ADD COLUMN ts_content_user TSVECTOR;

CREATE OR REPLACE FUNCTION comment_content_user_update() RETURNS TRIGGER AS $$
DECLARE
    user_name_value TEXT;
BEGIN
    SELECT COALESCE(user_name, 'Anonymous') INTO user_name_value FROM users WHERE users.id = NEW.author_id;

 IF TG_OP = 'INSERT' THEN            
        NEW.ts_content_user = (
            setweight(to_tsvector('english', NEW.content), 'A') ||
            setweight(to_tsvector('english', user_name_value), 'B')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.content <> OLD.content OR NEW.author_id <> OLD.author_id) THEN
           NEW.ts_content_user = (
            setweight(to_tsvector('english', NEW.content), 'A') ||
            setweight(to_tsvector('english', user_name_value), 'B')
        );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

CREATE TRIGGER comment_content_user_update
    BEFORE INSERT OR UPDATE ON comment
    FOR EACH ROW 
    EXECUTE PROCEDURE comment_content_user_update();

CREATE INDEX idx_comment_content ON comment USING GIN (ts_content_user);

3. Triggers

Trigger	TRIGGER01
Description	Influencers are a percentage of the users with the highest reputation on the platform

`SQL code`

CREATE FUNCTION manage_influencers()
RETURNS TRIGGER AS $$
DECLARE
    total_users INTEGER;
    total_influencers INTEGER;
    min_influencer RECORD;
    max_user RECORD;
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
    SELECT COUNT(*) INTO total_influencers FROM influencer;

    SELECT id, reputation
    INTO min_influencer
    FROM users
    WHERE id IN (SELECT user_id FROM influencer)
    ORDER BY reputation ASC
    LIMIT 1;

    IF TG_OP = 'INSERT' THEN
        IF total_influencers < CEIL(0.1 * total_users) THEN
            IF NEW.reputation >= 100 THEN
                INSERT INTO influencer (user_id, started_date, has_privilege)
                VALUES (NEW.id, now(), FALSE);
            END IF;
        ELSIF NEW.reputation > min_influencer.reputation THEN
            INSERT INTO influencer (user_id, started_date, has_privilege)
            VALUES (NEW.id, now(), FALSE);

            DELETE FROM influencer
            WHERE user_id = min_influencer.id;
        END IF; 
    END IF;

    IF TG_OP = 'UPDATE' THEN
        IF total_influencers < CEIL(0.1 * total_users) THEN
                IF NEW.reputation >= 100 AND NEW.id NOT IN (SELECT user_id FROM influencer) THEN
                    INSERT INTO influencer (user_id, started_date, has_privilege)
                    VALUES (NEW.id, now(), FALSE);
                END IF;
        ELSIF NEW.reputation > OLD.reputation AND NEW.id NOT IN (SELECT user_id FROM influencer) THEN
                IF NEW.reputation > min_influencer.reputation THEN
                    INSERT INTO influencer (user_id, started_date, has_privilege)
                    VALUES (NEW.id, now(), FALSE);

                    DELETE FROM influencer
                    WHERE user_id = min_influencer.id;
                END IF;
        ELSIF NEW.reputation < OLD.reputation AND NEW.id IN (SELECT user_id FROM influencer) THEN
            IF NEW.reputation < 100 THEN
                DELETE FROM influencer
                WHERE user_id = NEW.id;

                SELECT id, reputation INTO max_user 
                FROM users 
                WHERE id NOT IN (SELECT user_id FROM influencer) 
                ORDER BY reputation DESC 
                LIMIT 1;
                
                IF max_user IS NOT NULL AND max_user.reputation >= 100 THEN
                    INSERT INTO influencer (user_id, started_date, has_privilege) 
                    VALUES (max_user.id, now(), FALSE);
                END IF;
            ELSIF NEW.reputation < min_influencer.reputation THEN
                    SELECT id, reputation INTO max_user 
                    FROM users 
                    WHERE id NOT IN (SELECT user_id FROM influencer) 
                    ORDER BY reputation DESC 
                    LIMIT 1;
            
                    IF max_user IS NOT NULL AND NEW.reputation < max_user.reputation AND max_user.reputation >= 100 THEN
                            DELETE FROM influencer
                            WHERE user_id = NEW.id;

                            INSERT INTO influencer (user_id, started_date, has_privilege) 
                            VALUES (max_user.id, now(), FALSE);
                    END IF;
            END IF;
        END IF;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_influencers
    AFTER INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION manage_influencers();

Trigger	TRIGGER02
Description	Likes generate notifications for the users

`SQL code`

CREATE FUNCTION manage_like_notification() 
RETURNS TRIGGER AS $$
DECLARE
    notification_id INTEGER;
    receiver_id INTEGER;
BEGIN
    IF NEW.news_id IS NOT NULL THEN
        SELECT author_id INTO receiver_id
            FROM news
            WHERE id = NEW.news_id;
    
        INSERT INTO notifications (sender_id, receiver_id, notification_date, viewed, notification_type)
        VALUES (NEW.sender_id, receiver_id, now(), FALSE, 'Like_notification')
        RETURNING id INTO notification_id;

        INSERT INTO notifications_like (notification_id, like_id)
        VALUES (notification_id, NEW.id);
    
    ELSIF NEW.comment_id IS NOT NULL THEN
        SELECT author_id INTO receiver_id
            FROM comment
            WHERE id = NEW.comment_id;

        INSERT INTO notifications (sender_id, receiver_id, notification_date, viewed, notification_type)
        VALUES (NEW.sender_id, receiver_id, now(), FALSE, 'Like_notification')
        RETURNING id INTO notification_id;

        INSERT INTO notifications_like (notification_id, like_id)
        VALUES (notification_id, NEW.id);
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_like_notification
    AFTER INSERT ON likes
    FOR EACH ROW
    EXECUTE FUNCTION manage_like_notification();

Trigger	TRIGGER03
Description	Comments generate notifications for the users

`SQL code`

CREATE FUNCTION manage_comment_notification() 
RETURNS TRIGGER AS $$
DECLARE
    notification_id INTEGER;
    receiver_id INTEGER;
BEGIN
    SELECT author_id INTO receiver_id
        FROM news
        WHERE id = NEW.news_id;
    
    INSERT INTO notifications (sender_id, receiver_id, notification_date, viewed, notification_type)
    VALUES (NEW.author_id, receiver_id, now(), FALSE, 'Comment_notification')
    RETURNING id INTO notification_id;

    INSERT INTO notifications_comment (notification_id, comment_id)
    VALUES (notification_id, NEW.id);

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_comment_notification
    AFTER INSERT ON comment
    FOR EACH ROW
    EXECUTE FUNCTION manage_comment_notification();

Trigger	TRIGGER04
Description	Follows generate notifications for the users

`SQL code`

CREATE FUNCTION manage_follow_notification() 
RETURNS TRIGGER AS $$
DECLARE
    notification_id INTEGER;
BEGIN
    INSERT INTO notifications (sender_id, receiver_id, notification_date, viewed, notification_type)
    VALUES (NEW.follower_id, NEW.followed_id, now(), FALSE, 'Follow_notification')
    RETURNING id INTO notification_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_follow_notification
    AFTER INSERT ON follow
    FOR EACH ROW
    EXECUTE FUNCTION manage_follow_notification();

Trigger	TRIGGER05
Description	Deleting the like notification should remove the associated main notification

`SQL code`

CREATE FUNCTION delete_notification()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM notifications
    WHERE id = OLD.notification_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_notification_like
    AFTER DELETE ON notifications_like
    FOR EACH ROW
    EXECUTE FUNCTION delete_notification();

Trigger	TRIGGER06
Description	Deleting the comment notification should remove the associated main notification

`SQL code`

CREATE FUNCTION delete_notification()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM notifications
    WHERE id = OLD.notification_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_notification_comment
    AFTER DELETE ON notifications_comment
    FOR EACH ROW
    EXECUTE FUNCTION delete_notification();

Trigger	TRIGGER07
Description	Deleting a follow should remove the associated notification

`SQL code`

CREATE FUNCTION delete_notification_follow()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM notifications
    WHERE sender_id = OLD.follower_id AND receiver_id = OLD.followed_id AND notification_type = 'Follow_notification';

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_notification_follow
    AFTER DELETE ON follow
    FOR EACH ROW
    EXECUTE FUNCTION delete_notification_follow();

Trigger	TRIGGER08
Description	A user can like a news item only once

`SQL code`

CREATE FUNCTION like_news_once()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND (OLD.sender_id = NEW.sender_id AND OLD.news_id = NEW.news_id) THEN
        RETURN NEW;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM likes
        WHERE sender_id = NEW.sender_id AND news_id = NEW.news_id
    ) THEN
        RAISE EXCEPTION 'User already liked this news';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER like_news_once
    BEFORE INSERT OR UPDATE ON likes
    FOR EACH ROW
    EXECUTE FUNCTION like_news_once();

Trigger	TRIGGER09
Description	A user can like a comment only once

`SQL code`

CREATE FUNCTION like_comment_once()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND (OLD.sender_id = NEW.sender_id AND OLD.comment_id = NEW.comment_id) THEN
        RETURN NEW;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM likes
        WHERE sender_id = NEW.sender_id AND comment_id = NEW.comment_id
    ) THEN
        RAISE EXCEPTION 'User already liked this comment';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER like_comment_once
    BEFORE INSERT OR UPDATE ON likes
    FOR EACH ROW
    EXECUTE FUNCTION like_comment_once();

Trigger	TRIGGER10
Description	A user cannot follow themselves

`SQL code`

CREATE FUNCTION manage_self_follow()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.follower_id = NEW.followed_id THEN
        RAISE EXCEPTION 'User cannot follow themselves';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_self_follow
    BEFORE INSERT OR UPDATE ON follow
    FOR EACH ROW
    EXECUTE FUNCTION manage_self_follow();

Trigger	TRIGGER11
Description	A news item cannot be deleted if it has any likes or comments

`SQL code`

CREATE FUNCTION delete_news()
RETURNS TRIGGER AS $$
BEGIN 
    IF EXISTS (
        SELECT 1 
        FROM likes 
        WHERE news_id = OLD.id
    ) THEN 
        RAISE EXCEPTION 'News has likes';

    ELSIF EXISTS (
        SELECT 1
        FROM comment
        WHERE news_id = OLD.id
    ) THEN
        RAISE EXCEPTION 'News has comments';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_news
    BEFORE DELETE ON news
    FOR EACH ROW
    EXECUTE FUNCTION delete_news();

Trigger	TRIGGER12
Description	A comment cannot be deleted if it has any likes

`SQL code`

CREATE FUNCTION delete_comment()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM likes
        WHERE comment_id = OLD.id
    ) THEN
        RAISE EXCEPTION 'Comment has likes';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_comment
    BEFORE DELETE ON comment
    FOR EACH ROW
    EXECUTE FUNCTION delete_comment();

Trigger	TRIGGER13
Description	A user's reputation should be calculated based on the likes they receive on their news and comments

`SQL code`

CREATE FUNCTION update_reputation()
RETURNS TRIGGER AS $$
BEGIN 
    IF TG_OP = 'INSERT' THEN
        IF NEW.news_id IS NOT NULL THEN
            UPDATE users u
            SET reputation = u.reputation + 1
            FROM news n
            WHERE n.id = NEW.news_id AND u.id = n.author_id;
        ELSIF NEW.comment_id IS NOT NULL THEN
            UPDATE users u
            SET reputation = u.reputation + 1
            FROM comment c
            WHERE c.id = NEW.comment_id AND u.id = c.author_id; 
        END IF;
    ELSIF TG_OP = 'DELETE' THEN
        IF OLD.news_id IS NOT NULL THEN
            UPDATE users u
            SET reputation = u.reputation - 1
            FROM news n
            WHERE n.id = OLD.news_id AND u.id = n.author_id;
        ELSIF OLD.comment_id IS NOT NULL THEN
            UPDATE users u 
            SET reputation = u.reputation - 1
            FROM comment c
            WHERE c.id = OLD.comment_id AND u.id = c.author_id;
        END IF;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_reputation
    AFTER INSERT OR DELETE ON likes
    FOR EACH ROW
    EXECUTE FUNCTION update_reputation();

Trigger	TRIGGER14
Description	A comment publication date must be later than the news publication date

`SQL code`

CREATE FUNCTION check_comment_date()
RETURNS TRIGGER AS $$
BEGIN 
    IF EXISTS (
        SELECT 1
        FROM news n
        WHERE n.id = NEW.news_id AND n.published_date > NEW.published_date
    ) THEN
        RAISE EXCEPTION 'Comment publication date must be later than the news publication date';
    END IF;
 
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_comment_date
    BEFORE INSERT OR UPDATE ON comment
    FOR EACH ROW
    EXECUTE FUNCTION check_comment_date();

4. Transactions

SQL Reference	Deleting a user
Justification	To maintain consistency, a transaction is used to ensure all operations execute as a single unit, preventing partial updates if any errors occur. In case of an error, the transaction will ROLLBACK, leaving the user's data unchanged. When deleting a user, it’s essential to anonymize their shared contributions (such as news, comments, and likes). The Read Committed isolation level provides the necessary consistency for this transaction without the added overhead of higher isolation levels, as no data is read during this process.
Isolation level	READ COMMITTED

`Complete SQL Code`

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DELETE FROM users 
WHERE id = $user_id;

UPDATE news 
SET author_id = NULL 
WHERE author_id = $user_id;

UPDATE comment 
SET author_id = NULL 
WHERE author_id = $user_id;

UPDATE likes 
SET sender_id = NULL 
WHERE sender_id = $user_id;

END TRANSACTION;

SQL Reference	Get news and the comments associated with the news
Justification	To maintain consistency, a transaction is used to ensure all operations execute seamlessly. If an error occurs, the transaction will ROLLBACK, preventing any partial results (e.g., ensuring the user does not see a news without its comments). The transaction includes viewing a news item and its associated comments, both of which require only read operations, making it suitable for a Read Only transaction that uses only select statements. Repeatable Read gives data consistency without increasing the overhead because the data does not change frequently.
Isolation level	REPEATABLE READ READ ONLY

`Complete SQL Code`

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;

SELECT *  
FROM news 
WHERE id = $news_id;

SELECT *
FROM comment
WHERE news_id = $news_id;

END TRANSACTION;

SQL Reference	See other users new feed
Justification	To ensure consistency, a transaction is used so that all operations execute together without errors. If an error occurs, a ROLLBACK will be issued, ensuring that users do not see incomplete or inconsistent data (e.g., news from different users). This transaction retrieves the user's info and their news item, and since it only requires select statements, it can be set as Read Only. Repeatable Read gives data consistency without increasing the overhead because the data does not change frequently.
Isolation level	REPEATABLE READ READ ONLY

`Complete SQL Code`

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;

SELECT * 
FROM users 
WHERE id = $user_id;

SELECT *
FROM news
WHERE author_id =  $user_id; 

END TRANSACTION;

SQL Reference	See news details
Justification	To maintain consistency, a transaction is used to ensure that all operations execute successfully as a single unit. If an error occurs, a ROLLBACK is issued, preventing users from seeing incomplete data (e.g., a partially loaded news item). This transaction retrieves the news details. Since it only involves select statements, it is designated as Read Only.
Isolation level	SERIALIZABLE READ ONLY

`Complete SQL Code`

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;

SELECT content, published_date, author_id, image_id 
FROM news 
WHERE id = $news_id;

SELECT user_name 
FROM users 
WHERE id = $author_id;

SELECT image_path 
FROM images 
WHERE id = $image_id;

SELECT like_count 
FROM news_like_counts 
WHERE news_id = $news_id;

SELECT tag.name 
FROM tag_news JOIN tag ON tag_id = tag.id 
WHERE news_id = $news_id;

END TRANSACTION;

SQL Reference	See comment details
Justification	To ensure consistency, a transaction is used to guarantee that all operations execute successfully as a single unit. If an error occurs, a ROLLBACK is issued, preventing users from seeing incomplete data (e.g., a partially loaded comment). This transaction retrieves the comment details and is designated as Read Only since it only involves select statements.
Isolation level	SERIALIZABLE READ ONLY

`Complete SQL Code`

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;

SELECT content, published_date, news_id, author_id, img_id FROM comment WHERE id = $comment_id;

SELECT user_name 
FROM users 
WHERE id = $author_id;

SELECT image_path 
FROM images 
WHERE id = $img_id;

SELECT like_count 
FROM comments_like_counts 
WHERE comment_id = $comment_id;

END TRANSACTION;

Annex A. SQL Code

Database Schema

Database Population

A.1. Database schema

CREATE SCHEMA IF NOT EXISTS lbaw24142;
SET search_path TO lbaw24142;

-----------------------------------------
-- Drop previous schema
-----------------------------------------

DROP TRIGGER IF EXISTS news_content_user_update ON news;
DROP TRIGGER IF EXISTS comment_content_user_update ON comment;
DROP TRIGGER IF EXISTS manage_influencers ON users;
DROP TRIGGER IF EXISTS manage_like_notification ON likes;
DROP TRIGGER IF EXISTS manage_comment_notification ON comment;
DROP TRIGGER IF EXISTS manage_follow_notification ON follow;
DROP TRIGGER IF EXISTS delete_notification_like ON notifications_like;
DROP TRIGGER IF EXISTS delete_notification_comment ON notifications_comment;
DROP TRIGGER IF EXISTS delete_notification_follow ON follow;
DROP TRIGGER IF EXISTS like_news_once ON likes;
DROP TRIGGER IF EXISTS like_comment_once ON likes;
DROP TRIGGER IF EXISTS manage_self_follow ON follow;
DROP TRIGGER IF EXISTS delete_news ON news;
DROP TRIGGER IF EXISTS delete_comment ON comment;
DROP TRIGGER IF EXISTS update_reputation ON likes;
DROP TRIGGER IF EXISTS check_comment_date ON comment;

DROP INDEX IF EXISTS idx_news_date;
DROP INDEX IF EXISTS idx_like_news;
DROP INDEX IF EXISTS idx_notification;
DROP INDEX IF EXISTS idx_news_content;
DROP INDEX IF EXISTS idx_comment_content;

DROP MATERIALIZED VIEW IF EXISTS news_like_counts CASCADE;
DROP MATERIALIZED VIEW IF EXISTS comments_like_counts CASCADE;

DROP FUNCTION IF EXISTS manage_influencers CASCADE;
DROP FUNCTION IF EXISTS manage_like_notification CASCADE;
DROP FUNCTION IF EXISTS manage_comment_notification CASCADE;
DROP FUNCTION IF EXISTS manage_follow_notification CASCADE;
DROP FUNCTION IF EXISTS delete_notification CASCADE;
DROP FUNCTION IF EXISTS delete_notification_follow CASCADE;
DROP FUNCTION IF EXISTS like_news_once CASCADE;
DROP FUNCTION IF EXISTS like_comment_once CASCADE;
DROP FUNCTION IF EXISTS manage_self_follow CASCADE;
DROP FUNCTION IF EXISTS delete_news CASCADE;
DROP FUNCTION IF EXISTS delete_comment CASCADE;
DROP FUNCTION IF EXISTS update_reputation CASCADE;
DROP FUNCTION IF EXISTS check_comment_date CASCADE;

DROP TABLE IF EXISTS notifications_like CASCADE;
DROP TABLE IF EXISTS notifications_comment CASCADE;
DROP TABLE IF EXISTS notifications CASCADE;
DROP TABLE IF EXISTS likes CASCADE;
DROP TABLE IF EXISTS report CASCADE;
DROP TABLE IF EXISTS favorite CASCADE;
DROP TABLE IF EXISTS tag_news CASCADE;
DROP TABLE IF EXISTS follow_tag CASCADE;
DROP TABLE IF EXISTS follow CASCADE;
DROP TABLE IF EXISTS comment CASCADE;
DROP TABLE IF EXISTS news CASCADE;
DROP TABLE IF EXISTS blocked CASCADE;
DROP TABLE IF EXISTS influencer CASCADE;
DROP TABLE IF EXISTS administrator CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
DROP TABLE IF EXISTS images CASCADE;

DROP TYPE IF EXISTS  Notification_types CASCADE;

-----------------------------------------
-- Types
-----------------------------------------

CREATE TYPE Notification_types AS ENUM(
    'Like_notification',
    'Comment_notification',  
    'Follow_notification'
);

-----------------------------------------
-- Tables
-----------------------------------------

CREATE TABLE images (
    id SERIAL PRIMARY KEY,
    image_path VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE tag (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    user_name VARCHAR(30) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    user_password VARCHAR(255) NOT NULL,
    reputation INTEGER NOT NULL DEFAULT 0 CHECK (reputation >= 0),
    image_id INTEGER REFERENCES images(id) ON DELETE SET NULL
);

CREATE TABLE administrator (
    id SERIAL PRIMARY KEY,
    adm_name VARCHAR(30) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    adm_password VARCHAR(255) NOT NULL,
    image_id INTEGER REFERENCES images(id) ON DELETE SET NULL
);

CREATE TABLE influencer (
    user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    started_date TIMESTAMP DEFAULT now() NOT NULL,
    has_privilege BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE blocked (
    user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    blocked_date TIMESTAMP DEFAULT now() NOT NULL,
    appeal BOOLEAN NOT NULL DEFAULT FALSE,
    appeal_description TEXT

    CHECK (
        (appeal = TRUE AND appeal_description IS NOT NULL) OR
        (appeal = FALSE AND appeal_description IS NULL)
    )
);

CREATE TABLE news (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    published_date TIMESTAMP DEFAULT now() NOT NULL,
    author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    image_id INTEGER REFERENCES images(id) ON DELETE SET NULL
);

CREATE TABLE comment (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    published_date TIMESTAMP DEFAULT now() NOT NULL,
    news_id INTEGER NOT NULL REFERENCES news(id) ON DELETE CASCADE,
    author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    img_id INTEGER REFERENCES images(id) ON DELETE SET NULL
);

CREATE TABLE follow (
    follower_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    followed_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    PRIMARY KEY (follower_id, followed_id)
);

CREATE TABLE follow_tag (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES  tag(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, tag_id)
);

CREATE TABLE tag_news (
    news_id INTEGER NOT NULL REFERENCES news(id) ON DELETE CASCADE,
    tag_id INTEGER  NOT NULL REFERENCES tag(id) ON DELETE CASCADE,
    PRIMARY KEY (news_id, tag_id)
);

CREATE TABLE favorite (
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    news_id INTEGER REFERENCES news(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, news_id)
);

CREATE TABLE report (
    id SERIAL PRIMARY KEY,
    report_description TEXT NOT NULL,
    created_date TIMESTAMP DEFAULT now() NOT NULL,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    comment_id INTEGER REFERENCES comment(id) ON DELETE CASCADE,
    news_id INTEGER REFERENCES news(id) ON DELETE CASCADE,

    CHECK (
        (user_id IS NOT NULL AND comment_id IS NULL AND news_id IS NULL) OR
        (user_id IS NULL AND comment_id IS NOT NULL AND news_id IS NULL) OR
        (user_id IS NULL AND comment_id IS NULL AND news_id IS NOT NULL)
    )
);

CREATE TABLE likes (
    id SERIAL PRIMARY KEY,
    sender_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    news_id INTEGER REFERENCES news(id) ON DELETE CASCADE,
    comment_id INTEGER REFERENCES comment(id) ON DELETE CASCADE,

    CHECK (
        (news_id IS NOT NULL AND comment_id IS NULL) OR
        (news_id IS NULL AND comment_id IS NOT NULL)
    )
);

CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    sender_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    receiver_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    notification_date TIMESTAMP DEFAULT now() NOT NULL,
    viewed BOOLEAN NOT NULL DEFAULT FALSE,
    notification_type Notification_types NOT NULL
);

CREATE TABLE notifications_comment (
    notification_id INTEGER PRIMARY KEY REFERENCES notifications(id) ON DELETE CASCADE,
    comment_id INTEGER NOT NULL UNIQUE REFERENCES comment(id) ON DELETE CASCADE
);

CREATE TABLE notifications_like (
    notification_id INTEGER PRIMARY KEY REFERENCES notifications(id) ON DELETE CASCADE,
    like_id INTEGER NOT NULL UNIQUE REFERENCES likes(id) ON DELETE CASCADE
);


-----------------------------------------
-- Views
-----------------------------------------

CREATE MATERIALIZED VIEW news_like_counts AS
SELECT 
    news_id,
    COUNT(*) AS like_count
FROM likes
WHERE news_id IS NOT NULL
GROUP BY news_id;

CREATE MATERIALIZED VIEW comments_like_counts AS
SELECT 
    comment_id,
    COUNT(*) AS like_count
FROM likes
WHERE comment_id IS NOT NULL
GROUP BY comment_id;

-----------------------------------------
-- Indexes
-----------------------------------------

CREATE INDEX idx_news_date ON news USING btree (published_date);

CREATE INDEX idx_like_news ON likes USING btree (news_id, sender_id) WHERE news_id IS NOT NULL;

CREATE INDEX idx_notification ON notifications USING btree (receiver_id, viewed);

--- FTS Indexes

-- FTS01

ALTER TABLE news ADD COLUMN ts_content_user TSVECTOR;

CREATE OR REPLACE FUNCTION news_content_user_update() RETURNS TRIGGER AS $$
DECLARE
    user_name_value TEXT;
BEGIN
 SELECT COALESCE(user_name, 'Anonymous') INTO user_name_value FROM users WHERE users.id = NEW.author_id;
 
 IF TG_OP = 'INSERT' THEN
        NEW.ts_content_user = (
            setweight(to_tsvector('english', NEW.content), 'A') ||
            setweight(to_tsvector('english', user_name_value), 'B')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.content <> OLD.content OR NEW.author_id <> OLD.author_id) THEN
           NEW.ts_content_user = (
            setweight(to_tsvector('english', NEW.content), 'A') ||
            setweight(to_tsvector('english', user_name_value), 'B')
        );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

CREATE TRIGGER news_content_user_update
    BEFORE INSERT OR UPDATE ON news
    FOR EACH ROW 
    EXECUTE PROCEDURE news_content_user_update();

CREATE INDEX idx_news_content ON news USING GIN (ts_content_user);

-- FTS02

ALTER TABLE comment ADD COLUMN ts_content_user TSVECTOR;

CREATE OR REPLACE FUNCTION comment_content_user_update() RETURNS TRIGGER AS $$
DECLARE
    user_name_value TEXT;
BEGIN
    SELECT COALESCE(user_name, 'Anonymous') INTO user_name_value FROM users WHERE users.id = NEW.author_id;

 IF TG_OP = 'INSERT' THEN            
        NEW.ts_content_user = (
            setweight(to_tsvector('english', NEW.content), 'A') ||
            setweight(to_tsvector('english', user_name_value), 'B')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.content <> OLD.content OR NEW.author_id <> OLD.author_id) THEN
           NEW.ts_content_user = (
            setweight(to_tsvector('english', NEW.content), 'A') ||
            setweight(to_tsvector('english', user_name_value), 'B')
        );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

CREATE TRIGGER comment_content_user_update
    BEFORE INSERT OR UPDATE ON comment
    FOR EACH ROW 
    EXECUTE PROCEDURE comment_content_user_update();

CREATE INDEX idx_comment_content ON comment USING GIN (ts_content_user);

-----------------------------------------
-- Triggers and UDFs
-----------------------------------------

-- TRIGGER01

CREATE FUNCTION manage_influencers()
RETURNS TRIGGER AS $$
DECLARE
    total_users INTEGER;
    total_influencers INTEGER;
    min_influencer RECORD;
    max_user RECORD;
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
    SELECT COUNT(*) INTO total_influencers FROM influencer;

    SELECT id, reputation
    INTO min_influencer
    FROM users
    WHERE id IN (SELECT user_id FROM influencer)
    ORDER BY reputation ASC
    LIMIT 1;

    IF TG_OP = 'INSERT' THEN
        IF total_influencers < CEIL(0.1 * total_users) THEN
            IF NEW.reputation >= 100 THEN
                INSERT INTO influencer (user_id, started_date, has_privilege)
                VALUES (NEW.id, now(), FALSE);
            END IF;
        ELSIF NEW.reputation > min_influencer.reputation THEN
            INSERT INTO influencer (user_id, started_date, has_privilege)
            VALUES (NEW.id, now(), FALSE);

            DELETE FROM influencer
            WHERE user_id = min_influencer.id;
        END IF; 
    END IF;

    IF TG_OP = 'UPDATE' THEN
        IF total_influencers < CEIL(0.1 * total_users) THEN
                IF NEW.reputation >= 100 AND NEW.id NOT IN (SELECT user_id FROM influencer) THEN
                    INSERT INTO influencer (user_id, started_date, has_privilege)
                    VALUES (NEW.id, now(), FALSE);
                END IF;
        ELSIF NEW.reputation > OLD.reputation AND NEW.id NOT IN (SELECT user_id FROM influencer) THEN
                IF NEW.reputation > min_influencer.reputation THEN
                    INSERT INTO influencer (user_id, started_date, has_privilege)
                    VALUES (NEW.id, now(), FALSE);

                    DELETE FROM influencer
                    WHERE user_id = min_influencer.id;
                END IF;
        ELSIF NEW.reputation < OLD.reputation AND NEW.id IN (SELECT user_id FROM influencer) THEN
            IF NEW.reputation < 100 THEN
                DELETE FROM influencer
                WHERE user_id = NEW.id;

                SELECT id, reputation INTO max_user 
                FROM users 
                WHERE id NOT IN (SELECT user_id FROM influencer) 
                ORDER BY reputation DESC 
                LIMIT 1;
                
                IF max_user IS NOT NULL AND max_user.reputation >= 100 THEN
                    INSERT INTO influencer (user_id, started_date, has_privilege) 
                    VALUES (max_user.id, now(), FALSE);
                END IF;
            ELSIF NEW.reputation < min_influencer.reputation THEN
                    SELECT id, reputation INTO max_user 
                    FROM users 
                    WHERE id NOT IN (SELECT user_id FROM influencer) 
                    ORDER BY reputation DESC 
                    LIMIT 1;
            
                    IF max_user IS NOT NULL AND NEW.reputation < max_user.reputation AND max_user.reputation >= 100 THEN
                            DELETE FROM influencer
                            WHERE user_id = NEW.id;

                            INSERT INTO influencer (user_id, started_date, has_privilege) 
                            VALUES (max_user.id, now(), FALSE);
                    END IF;
            END IF;
        END IF;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_influencers
    AFTER INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION manage_influencers();


-- TRIGGER02

CREATE FUNCTION manage_like_notification() 
RETURNS TRIGGER AS $$
DECLARE
    notification_id INTEGER;
    receiver_id INTEGER;
BEGIN
    IF NEW.news_id IS NOT NULL THEN
        SELECT author_id INTO receiver_id
            FROM news
            WHERE id = NEW.news_id;
    
        INSERT INTO notifications (sender_id, receiver_id, notification_date, viewed, notification_type)
        VALUES (NEW.sender_id, receiver_id, now(), FALSE, 'Like_notification')
        RETURNING id INTO notification_id;

        INSERT INTO notifications_like (notification_id, like_id)
        VALUES (notification_id, NEW.id);
    
    ELSIF NEW.comment_id IS NOT NULL THEN
        SELECT author_id INTO receiver_id
            FROM comment
            WHERE id = NEW.comment_id;

        INSERT INTO notifications (sender_id, receiver_id, notification_date, viewed, notification_type)
        VALUES (NEW.sender_id, receiver_id, now(), FALSE, 'Like_notification')
        RETURNING id INTO notification_id;

        INSERT INTO notifications_like (notification_id, like_id)
        VALUES (notification_id, NEW.id);
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_like_notification
    AFTER INSERT ON likes
    FOR EACH ROW
    EXECUTE FUNCTION manage_like_notification();


-- TRIGGER03

CREATE FUNCTION manage_comment_notification() 
RETURNS TRIGGER AS $$
DECLARE
    notification_id INTEGER;
    receiver_id INTEGER;
BEGIN
    SELECT author_id INTO receiver_id
        FROM news
        WHERE id = NEW.news_id;
    
    INSERT INTO notifications (sender_id, receiver_id, notification_date, viewed, notification_type)
    VALUES (NEW.author_id, receiver_id, now(), FALSE, 'Comment_notification')
    RETURNING id INTO notification_id;

    INSERT INTO notifications_comment (notification_id, comment_id)
    VALUES (notification_id, NEW.id);

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_comment_notification
    AFTER INSERT ON comment
    FOR EACH ROW
    EXECUTE FUNCTION manage_comment_notification();


-- TRIGGER04

CREATE FUNCTION manage_follow_notification() 
RETURNS TRIGGER AS $$
DECLARE
    notification_id INTEGER;
BEGIN
    INSERT INTO notifications (sender_id, receiver_id, notification_date, viewed, notification_type)
    VALUES (NEW.follower_id, NEW.followed_id, now(), FALSE, 'Follow_notification')
    RETURNING id INTO notification_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_follow_notification
    AFTER INSERT ON follow
    FOR EACH ROW
    EXECUTE FUNCTION manage_follow_notification();


-- TRIGGER05

CREATE FUNCTION delete_notification()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM notifications
    WHERE id = OLD.notification_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_notification_like
    AFTER DELETE ON notifications_like
    FOR EACH ROW
    EXECUTE FUNCTION delete_notification();


-- TRIGGER06    

CREATE TRIGGER delete_notification_comment
    AFTER DELETE ON notifications_comment
    FOR EACH ROW
    EXECUTE FUNCTION delete_notification();

-- TRIGGER07 

CREATE FUNCTION delete_notification_follow()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM notifications
    WHERE sender_id = OLD.follower_id AND receiver_id = OLD.followed_id AND notification_type = 'Follow_notification';

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_notification_follow
    AFTER DELETE ON follow
    FOR EACH ROW
    EXECUTE FUNCTION delete_notification_follow();


-- TRIGGER08

CREATE FUNCTION like_news_once()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND (OLD.sender_id = NEW.sender_id AND OLD.news_id = NEW.news_id) THEN
        RETURN NEW;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM likes
        WHERE sender_id = NEW.sender_id AND news_id = NEW.news_id
    ) THEN
        RAISE EXCEPTION 'User already liked this news';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER like_news_once
    BEFORE INSERT OR UPDATE ON likes
    FOR EACH ROW
    EXECUTE FUNCTION like_news_once();

-- TRIGGER09

CREATE FUNCTION like_comment_once()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND (OLD.sender_id = NEW.sender_id AND OLD.comment_id = NEW.comment_id) THEN
        RETURN NEW;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM likes
        WHERE sender_id = NEW.sender_id AND comment_id = NEW.comment_id
    ) THEN
        RAISE EXCEPTION 'User already liked this comment';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER like_comment_once
    BEFORE INSERT OR UPDATE ON likes
    FOR EACH ROW
    EXECUTE FUNCTION like_comment_once();


-- TRIGGER10

CREATE FUNCTION manage_self_follow()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.follower_id = NEW.followed_id THEN
        RAISE EXCEPTION 'User cannot follow themselves';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manage_self_follow
    BEFORE INSERT OR UPDATE ON follow
    FOR EACH ROW
    EXECUTE FUNCTION manage_self_follow();


-- TRIGGER11

CREATE FUNCTION delete_news()
RETURNS TRIGGER AS $$
BEGIN 
    IF EXISTS (
        SELECT 1 
        FROM likes 
        WHERE news_id = OLD.id
    ) THEN 
        RAISE EXCEPTION 'News has likes';

    ELSIF EXISTS (
        SELECT 1
        FROM comment
        WHERE news_id = OLD.id
    ) THEN
        RAISE EXCEPTION 'News has comments';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_news
    BEFORE DELETE ON news
    FOR EACH ROW
    EXECUTE FUNCTION delete_news();


-- TRIGGER012

CREATE FUNCTION delete_comment()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM likes
        WHERE comment_id = OLD.id
    ) THEN
        RAISE EXCEPTION 'Comment has likes';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_comment
    BEFORE DELETE ON comment
    FOR EACH ROW
    EXECUTE FUNCTION delete_comment();


-- TRIGGER13

CREATE FUNCTION update_reputation()
RETURNS TRIGGER AS $$
BEGIN 
    IF TG_OP = 'INSERT' THEN
        IF NEW.news_id IS NOT NULL THEN
            UPDATE users u
            SET reputation = u.reputation + 1
            FROM news n
            WHERE n.id = NEW.news_id AND u.id = n.author_id;
        ELSIF NEW.comment_id IS NOT NULL THEN
            UPDATE users u
            SET reputation = u.reputation + 1
            FROM comment c
            WHERE c.id = NEW.comment_id AND u.id = c.author_id; 
        END IF;
    ELSIF TG_OP = 'DELETE' THEN
        IF OLD.news_id IS NOT NULL THEN
            UPDATE users u
            SET reputation = u.reputation - 1
            FROM news n
            WHERE n.id = OLD.news_id AND u.id = n.author_id;
        ELSIF OLD.comment_id IS NOT NULL THEN
            UPDATE users u 
            SET reputation = u.reputation - 1
            FROM comment c
            WHERE c.id = OLD.comment_id AND u.id = c.author_id;
        END IF;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_reputation
    AFTER INSERT OR DELETE ON likes
    FOR EACH ROW
    EXECUTE FUNCTION update_reputation();


-- TRIGGER14

CREATE FUNCTION check_comment_date()
RETURNS TRIGGER AS $$
BEGIN 
    IF EXISTS (
        SELECT 1
        FROM news n
        WHERE n.id = NEW.news_id AND n.published_date > NEW.published_date
    ) THEN
        RAISE EXCEPTION 'Comment publication date must be later than the news publication date';
    END IF;
 
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_comment_date
    BEFORE INSERT OR UPDATE ON comment
    FOR EACH ROW
    EXECUTE FUNCTION check_comment_date();

-----------------------------------------
-- end
-----------------------------------------

A.2. Database population

SET search_path TO lbaw24142;

-----------------------------------------
-- Populate the database
-----------------------------------------

INSERT INTO images (image_path) VALUES
('images/avatar1.jpg'),
('images/avatar2.jpg'),
('images/avatar3.jpg'),
('images/avatar4.jpg'),
('images/avatar5.jpg'),
('images/avatar6.jpg');

INSERT INTO tag (name) VALUES
('Action'),
('Comedy'),
('Drama'),
('Horror'),
('Thriller'),
('Sci-Fi'),
('Romance'),
('Documentary'),
('Animation'),
('Fantasy');

INSERT INTO users (user_name, email, user_password, reputation, image_id) VALUES
('movie_buff91', '[email protected]', 'password123', 50, 1),
('tv_series_queen', '[email protected]', 'password456', 100, 2),
('action_fanatic', '[email protected]', 'password789', 75, 3),
('horror_lover', '[email protected]', 'securepass1', 120, 4),
('comedy_guru', '[email protected]', 'password987', 40, 5),
('fantasy_enthusiast', '[email protected]', 'password654', 90, 3),
('film_critic_01', '[email protected]', 'password123', 80, 1),
('tvfanatic_89', '[email protected]', 'password456', 60, 2),
('blockbuster_boy', '[email protected]', 'password789', 100, 3),
('binge_watcher', '[email protected]', 'password321', 150, 4);

INSERT INTO administrator (adm_name, email, adm_password, image_id) VALUES
('João', 'joã[email protected]', 'adminpass123', 1),
('Carlos', '[email protected]', 'adminpass456', 2),
('Pedro', '[email protected]', 'adminpass456', 3),
('Manuel', '[email protected]', 'adminpass456', 4);

INSERT INTO blocked (user_id, blocked_date, appeal, appeal_description) VALUES
(3, '2024-01-01 12:00:00', FALSE, NULL),
(4, '2024-01-02 14:00:00', TRUE, 'I was trying to defend myself from an offensive comment about my family.'),
(10, '2024-01-03 16:00:00', FALSE, NULL);

INSERT INTO news (content, author_id, image_id) VALUES
('The latest Spider-Man movie breaks box office records!', 1, 1),
('Breaking News: The new Game of Thrones prequel is confirmed for 2025!', 2, 2),
('Top 10 Comedy Movies of 2024 that you must watch!', 3, NULL),
('How to watch all the Star Wars movies in chronological order', 4, 4),
('The scariest horror movies on Netflix right now', 5, NULL),
('New trailer for the upcoming "Dune" movie drops today!', 1, 1),
('Is "The Witcher" Season 3 the best yet? Here’s everything we know', 2, 2),
('Marvel announces new "Fantastic Four" casting lineup', 3, 3),
('The most anticipated movies of 2025', 4, 4),
('5 hidden gems on Netflix you may have missed in 2024', 5, 5);

INSERT INTO comment (content, news_id, author_id, img_id) VALUES
('I’m so excited for this new Spider-Man movie!', 1, 2, 1),
('Can’t believe they’re bringing back Game of Thrones, but with a twist!', 2, 3, NULL),
('Great list! I’ve seen most of them, but some are still on my watchlist.', 3, 4, NULL),
('Star Wars will always be my favorite, love this list!', 4, 5, 4),
('I’ll never be able to watch horror movies like I used to after seeing that list!', 5, 1, NULL),
('I can’t wait for "Dune"! The visuals in the first movie were stunning.', 1, 2, 1),
('The new trailer for Dune looks even better than the first one. So excited!', 1, 3, 2),
('The Witcher Season 3 is shaping up to be amazing. Can’t wait to see more of Geralt!', 2, 4, 3),
('I’m hoping for more character development in Season 3. The first two were amazing!', 2, 5, 4),
('The Marvel "Fantastic Four" casting news has me excited! I wonder who will be playing Reed Richards.', 3, 1, 5);

INSERT INTO follow (follower_id, followed_id) VALUES
(1, 2),
(2, 1),
(2, 3),
(3, 4),
(4, 5),
(5, 1),
(1, 3),
(2, 4),
(2, 5),
(3, 6);

INSERT INTO follow_tag (user_id, tag_id) VALUES
(1, 11), 
(1, 4),   
(3, 1),  
(4, 3),  
(5, 2),  
(5, 10),
(1, 1),  
(2, 3),
(3, 2),  
(4, 4);

INSERT INTO tag_news (news_id, tag_id) VALUES
(1, 11), 
(2, 2),
(2, 1), 
(3, 2),  
(4, 5),  
(1, 13),  
(2, 14),  
(4, 13),  
(5, 4),  
(6, 13);

INSERT INTO favorite (user_id, news_id) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(10, 15),
(11, 14),
(12, 13);

INSERT INTO report (report_description, user_id, comment_id, news_id) VALUES
('Offensive comment about a TV series', 1, NULL, NULL),
('False information about a movie release', NULL, 1, NULL),
('Clickbait headline in the news post', NULL, NULL, 3),
('Inaccurate review of the movie plot', NULL, NULL, 2),
('Offensive language in the comment section', 3, NULL, NULL),
('Clickbait title misleading readers', NULL, NULL, 4),
('Hate speech in a comment about a TV show', NULL, 3, NULL),
('Fake information about the movie plot', NULL, NULL, 5),
('Racist comment in the discussion', NULL, 4, NULL),
('Personal attack in a comment on the news post', NULL, 5, NULL);

INSERT INTO likes (sender_id, news_id, comment_id) VALUES
(1, 1, NULL),
(2, 2, NULL),
(3, NULL, 1),
(4, NULL, 2),
(5, 5, NULL),
(10, 10, NULL),
(1, 6, NULL),  
(2, 7, NULL),  
(3, 8, NULL),  
(4, 9, NULL);

-----------------------------------------
-- end
-----------------------------------------

Revision history

No changes have been made.

GROUP24142, 15/10/2024

João Victor Botelho Duarte, [email protected]
Luiz Henrique Mamede Queiroz, [email protected] (Editor)
Raphael Moragas Dias Taveira Gonçalves, [email protected]
Victor Duarte Frauzino Lima de Matos, [email protected]

ebd - luizhqueiroz/LBAW-NewsHub GitHub Wiki

EBD: Database Specification Component

A4: Conceptual Data Model

1. Class diagram

2. Additional Business Rules

A5: Relational Schema, validation and schema refinement

1. Relational Schema

2. Domains

3. Schema validation

A6: Indexes, triggers, transactions and database population

1. Database Workload

2. Proposed Indices

2.1. Performance Indices

2.2. Full-text Search Indices

3. Triggers

4. Transactions

Annex A. SQL Code

A.1. Database schema

A.2. Database population

Revision history

⚠️ GitHub.com Fallback ⚠️

ebd - luizhqueiroz/LBAW-NewsHub GitHub Wiki

EBD: Database Specification Component

A4: Conceptual Data Model

1. Class diagram

2. Additional Business Rules

A5: Relational Schema, validation and schema refinement

1. Relational Schema

2. Domains

3. Schema validation

A6: Indexes, triggers, transactions and database population

1. Database Workload

2. Proposed Indices

2.1. Performance Indices

2.2. Full-text Search Indices

3. Triggers

4. Transactions

Annex A. SQL Code

A.1. Database schema

A.2. Database population

Revision history

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️