EBD - HackMD

## ebd ## EBD: Database Specification Component ### A4: Conceptual Data Model The identification and descriptions of the entities and relationships pertinent to the database specification are contained in the conceptual data model. The model is described in a UML class diagram. To avoid overloading the diagram too early in the process, the class diagram is constructed by starting with just the classes and their relationships. other information, such as class properties, attribute domains, multiplicity of relationships, and other OCL limitations, are incorporated in the subsequent iterations. #### 4.1. Class diagram The UML diagram in Figure 1 presents the main organizational entities, the relationships between them, attributes and their domains, and the multiplicity of relationships for the TaskSquad platform ![](https://hackmd.io/_uploads/B1QCT5GM6.png) <center> Figure 1: TaskSquad conceptual data model in UML.</center> #### 2. Additional Business Rules Additional business regulations or limitations that cannot be represented by a UML class diagram are stated in text as standalone notes in this section or as UML notes in the diagram. * BR01. The Comment Owner can't like their own comment * BR02. When a user's account is deleted, every detail is kept, since it is considered work product ### A5: Relational Schema, validation and schema refinement The Relational Schema generated by mapping from the Conceptual Data Model is contained in this item. Each relation structure, attributes, domains, primary keys, foreign keys, and other integrity rules, such as UNIQUE, DEFAULT, NOT NULL, and CHECK, are all included in the relational schema. #### 5.1. Relational Schema The Relational Schema includes the relation schemas, attributes, domains, primary keys, foreign keys and other integrity rules: UNIQUE, DEFAULT, NOT NULL, CHECK. Relation schemas are specified in the compact notation: Relation reference | Relation Compact Notation| | ----------- | -------------------------------------------------------| |R01| user(id,username **UK** **NN**,password **NN**,email **UK** **NN**) |R02| generic_user(id,name **NN**, birthdate **NN**,profilePic,isBanned **NN** **DF** False,id_user->user)| |R03|isAdmin(id_user->user)| R04| project(id,title **NN UK**,description **NN**,theme **NN**,archived **NN DF** False )| |R05| task(id,id_project->project,content **NN**,isCompleted **NN DF** False,dateCreation **NN**,title **NN**,deadLine **NN CK** deadLine>dateCreation,priority NN CK priority IN priorityTask)| |R06 | comment(id,id_task->task,content **NN**,date **NN DF** Today)| |R07|likes(id_generic_user->generic_user,id_comment->comment)| |R08|favorite(id_generic_user->generic_user,id_project->project)| |R09|notifications(id,description **NN**)| |R10|projectNotification(id_user->user,idProject->project,ptype **NN CK** ptype **IN** projectType)| |R11|taskNotification(id_user->user,id_task->task,ttype **NN CK** ttype **IN** notificationType)| |R12|commentNotification(id_user->user,id_comment->comment,ctype **NN CK** ctype **IN** commentType)| |R13|isMember(id_user->user,id_projext->project)| |R14|isLeader((id_user->user,id_project->project))| |R15|taskOwner(id_user->user,id_task->task)| |R16|assigned(id_user->user,id_task->task)| |R17|commentOwner(id_user->user,id_comment->comment,id_task->task)| Legend: UK = UNIQUE KEY NN = NOT NULL DF = DEFAULT CK = CHECK. #### 5.2. Domains The specification of additional domains can also be made in a compact form, using the notation: |Domain Name | Domain Specification| | ----------- | -------------------------------------------------------| Today | DATE DEFAULT CURRENT_DATE| PriorityTask| ENUM ('High', 'Medium', 'Low')| CommentType| ENUM ('Like', 'Response')| ProjectType| ENUM ('NewLeader', 'Expelled','Deleted','NewMember')| TaskType| ENUM ('Assigned', 'Completed')| #### 5.3. Schema validation All functional connections are determined, and all relational schemas are normalized, in order to validate the Relational Schema derived from the Conceptual Model. If necessary, normalization is used to refine the relational schema if it is not in the Boyce-Codd Normal Form (BCNF). |TABLE R01 | User| | ----------- | -------------------------------------------------------| Keys |{ id }, { email },{username} **Functional Dependencies:** | FD0101| id → {email, username}| FD0102 | email → {id, username}| |**NORMAL FORM** | BCNF| |TABLE R02 |Generic User| | ----------- | -------------------------------------------------------| Keys |{ id } **Functional Dependencies:** | FD0201| id → {name,birthdate,profilePic,isbanned}| |**NORMAL FORM** | BCNF| |TABLE R03 | IsAdmin| | ----------- | -------------------------------------------------------| Keys |{ id_user} **Functional Dependencies:** |none |**NORMAL FORM** | BCNF| |TABLE R04 |Project| | ----------- | -------------------------------------------------------| Keys |{ id},{title} **Functional Dependencies:** | FD0401| id → {title,description,theme,archived}| FD0402 | title → {id,description,theme,archived}| |**NORMAL FORM** | BCNF| |TABLE R05 |Task| | ----------- | -------------------------------------------------------| Keys |{ id} **Functional Dependencies:** | FD0501| id → {content,isCompleted,dateCreation,deadLine,priority,task}| |**NORMAL FORM** | BCNF| |TABLE R06 |Comment| | ----------- | -------------------------------------------------------| Keys |{ id} **Functional Dependencies:** | FD0601| id → {content,date}| |**NORMAL FORM** | BCNF| |TABLE R07 |Likes| | ----------- | -------------------------------------------------------| Keys |{ id_comment}{id_generic_user} **Functional Dependencies:** |none |**NORMAL FORM** | BCNF| |TABLE R08 |Notifications| | ----------- | -------------------------------------------------------| Keys |{ id} **Functional Dependencies:** | FD0801| id → {description}| |**NORMAL FORM** | BCNF| |TABLE R09 |ProjectNotification| | ----------- | -------------------------------------------------------| Keys |{ id} **Functional Dependencies:** | FD0901| id → {ptype}| |**NORMAL FORM** | BCNF| |TABLE R10 |TaskNotification| | ----------- | -------------------------------------------------------| Keys |{ id} **Functional Dependencies:** | FD1001| id → {ttype}| |**NORMAL FORM** | BCNF| |TABLE R11 |CommentNotification| | ----------- | -------------------------------------------------------| Keys |{ id} **Functional Dependencies:** | FD1101| id → {ctype}| |**NORMAL FORM** | BCNF| |TABLE R12 |Favorite| | ----------- | -------------------------------------------------------| Keys |{ id_project},{id_generic_user} **Functional Dependencies:** |none |**NORMAL FORM** | BCNF| |TABLE R13 |isMember| | ----------- | -------------------------------------------------------| Keys |{ id_user},{id_project} **Functional Dependencies:** |none |**NORMAL FORM** | BCNF| |TABLE R14 |isLeader| | ----------- | -------------------------------------------------------| Keys |{ id_user},{id_project} **Functional Dependencies:** |none |**NORMAL FORM** | BCNF| |TABLE R15 |commentOwner| | ----------- | -------------------------------------------------------| Keys |{ id_user},{id_comment},{id_task} **Functional Dependencies:** |none |**NORMAL FORM** | BCNF| |TABLE R16 |taskOwner| | ----------- | -------------------------------------------------------| Keys |{ id_user},{id_task} **Functional Dependencies:** |none |**NORMAL FORM** | BCNF| |TABLE R17 |assigned| | ----------- | -------------------------------------------------------| Keys |{ id_user},{id_task} **Functional Dependencies:** |none |**NORMAL FORM** | BCNF| ### A6: Indexes, triggers, transactions and database population The physical database structure, index requirements, data integrity enforcement using triggers, and user-defined function definitions are all covered by this artifact. Additionally, it takes into account the workload of the database and provides a thorough script for creating databases that includes all crucial SQL commands for specifying integrity constraints, indexes, and triggers. A separate script with INSERT statements is also present for populating the database. #### 6.1. Database Workload A robust database design relies on a clear grasp of the workload, encompassing estimated tuple counts and growth projections. |Relation reference| Relation Name | Order of magnitude | Estimated growth| | ----------- | -------------------------|-------------------|-----------| R01 | user | 10k (tens of thousands) | 10 (tens) / day R02 | generic_user|10k (tens of thousands)| 10 (tens) / day R03 | isAdmin | 100 |1 / day R04| task|1k | 5/day R05| project| 20k | 20 / day R04| notification| 100k | 100 (hundreds) / day R04| projectNotification| 20 k | 20/day R04| taskNotification| 15k | 15/day R04| commentNotification| 5k |5/day R04| favorite| 200 | 5 / day #### 6.2. Proposed Indices ##### 6.2.1. Performance Indices Index | IDX01| | ----------- | -------------------------------------------------------| Relation | generic_user Attribute | name Type | Hash Cardinality | Medium Clustering | No Justification | Several times, it is needed to filter access to generic_user by its name. Filtering is done by exact match, thus an hash type would be best suited. Clustering is not needed based on this index and cardinality is medium. ```sql DROP INDEX IF EXISTS searchGenericUserName; CREATE INDEX searchGenericUserName ON generic_user USING HASH (name); ``` Index | IDX02| | ----------- | -------------------------------------------------------| Relation | projectt Attribute | title Type | Hash Cardinality | Medium Clustering | No Justification | Projects are frequently accessed. Filtering is done by exact match, so an hash type is the best. Since expected update frequency is medium then clustering is not proposed ```sql DROP INDEX IF EXISTS searchProjectTitle; CREATE INDEX searchProjectTitle ON projectt USING HASH (title); ``` Index | IDX03| | ----------- | -------------------------------------------------------| Relation | projectt Attribute | theme Type | Hash Cardinality | Low Clustering | No Justification | Projects are frequently accessed. Filtering is done by exact match, so an hash type is the best. Expected update frequency is medium and clustering is not proposed ```sql DROP INDEX IF EXISTS searchProjectTheme; CREATE INDEX searchProjectTheme ON projectt USING HASH (theme); ``` Index | IDX04| | ----------- | -------------------------------------------------------| Relation | task Attribute | deadline Type | B-Tree Cardinality | Medium Clustering | Yes Justification | Tasks are frequently accessed filtered by deadline. Cardinality is medium so it's a good candidate for clustering. ```sql DROP INDEX IF EXISTS searchTaskDeadline; CREATE INDEX searchTaskDeadline ON task USING BTREE (deadLine); ``` Index | IDX05| | ----------- | -------------------------------------------------------| Relation | task Attribute | priority Type | B-Tree Cardinality | Medium Clustering | Yes Justification | Tasks are frequently accessed filtered by priority. Cardinality is medium so it's a good candidate for clustering. ```sql DROP INDEX IF EXISTS searchTaskPriority; CREATE INDEX searchTaskPriority ON task USING BTREE (priority); ``` Index | IDX06| | ----------- | -------------------------------------------------------| Relation | isMember Attribute | id_project Type | B-Tree Cardinality | Low Clustering | No Justification | Members are frequently accessed filtered by project. Given the low cardinality, clustering isn't a priority in this scenario. ```sql DROP INDEX IF EXISTS searchMember; CREATE INDEX searchMember ON isMember USING BTREE (id_project); ``` Index | IDX07| | ----------- | -------------------------------------------------------| Relation | isLeader Attribute | id_project Type | Hash Cardinality | Low Clustering | No Justification | Leaders are frequently accessed filtered by project. Filtering is done by exact match, so an hash type is the best. Given the low cardinality, clustering isn't a priority in this scenario. ```sql DROP INDEX IF EXISTS searchLeader; CREATE INDEX searchLeader ON isLeader USING HASH (id_project); ``` Index | IDX08| | ----------- | -------------------------------------------------------| Relation | taskOwner Attribute | id_task Type | Hash Cardinality | Low Clustering | No Justification | Task owners are frequently accessed filtered by task. Filtering is done by exact match, so an hash type is the best. Given the low cardinality, clustering isn't a priority in this scenario. ```sql DROP INDEX IF EXISTS searchTaskOwner; CREATE INDEX searchTaskOwner ON taskOwner USING HASH (id_task); ``` Index | IDX09| | ----------- | -------------------------------------------------------| Relation | assigned Attribute | id_task Type | B-Tree Cardinality | Low Clustering | No Justification | Assigned users are frequently accessed filtered by task. Given the low cardinality, clustering isn't a priority in this scenario. ```sql DROP INDEX IF EXISTS searchAssigned; CREATE INDEX searchAssigned ON assigned USING BTREE (id_task); ``` Index | IDX10| | ----------- | -------------------------------------------------------| Relation | commentOwner Attribute | id_comment Type | Hash Cardinality | Low Clustering | No Justification | Comment owners are frequently accessed filtered by comment. Filtering is done by exact match, so an hash type is the best. Given the low cardinality, clustering isn't a priority in this scenario. ```sql DROP INDEX IF EXISTS searchCommentOwner; CREATE INDEX searchCommentOwner ON commentOwner USING HASH (id_comment); ``` #### 2.2. Full-Text Search Indexes Index | IDX11| | ----------- | -------------------------------------------------------| Relation | generic_user Attribute | name Type | GIN Clustering | No Justification | To provide full-text search features to look for works based on matching names. The index type is GIN because the indexed fields are not expected to change often. ```sql DROP INDEX IF EXISTS searchUser; CREATE INDEX searchUser ON generic_user USING GIN (search); CREATE OR REPLACE FUNCTION user_search_update() RETURNS TRIGGER AS $BODY$ BEGIN IF TG_OP = 'INSERT' THEN NEW.search = (SELECT setweight(to_tsvector(generic_user.name), 'A') FROM generic_user WHERE NEW.id=generic_user.id); ELSIF TG_OP = 'UPDATE' AND (NEW.username <> OLD.username) THEN NEW.search = (SELECT setweight(to_tsvector(generic_user.name), 'A') FROM generic_user WHERE NEW.id=generic_user.id); END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; DROP TRIGGER IF EXISTS update_user_search ON generic_user; CREATE TRIGGER update_user_search BEFORE INSERT OR UPDATE ON generic_user FOR EACH ROW EXECUTE PROCEDURE user_search_update(); ``` Index | IDX12| | ----------- | -------------------------------------------------------| Relation | projectt Attribute | search Type | GIN Clustering | No Justification | To provide full-text search features to look for works based on matching titles and descriptions. The index type is GIN because the indexed fields are not expected to change often. ```sql DROP INDEX IF EXISTS searchProject; CREATE INDEX searchProject ON projectt USING GIN (search); CREATE OR REPLACE FUNCTION project_search_update() RETURNS TRIGGER AS $BODY$ BEGIN IF TG_OP = 'INSERT' THEN NEW.search = (SELECT setweight(to_tsvector(NEW.title), 'A') || setweight(to_tsvector(NEW.description), 'B')); ELSIF TG_OP = 'UPDATE' AND (NEW.title <> OLD.title OR NEW.description <> OLD.description) THEN NEW.search = (SELECT setweight(to_tsvector(NEW.title), 'A') || setweight(to_tsvector(NEW.description), 'B')); END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; DROP TRIGGER IF EXISTS update_project_search ON projectt; CREATE TRIGGER update_project_search BEFORE INSERT OR UPDATE ON projectt FOR EACH ROW EXECUTE PROCEDURE project_search_update(); ``` #### 3. Triggers Trigger | TRIGGER01| | ----------- | -------------------------------------------------------| Description |An user cannot have more than 5 favorite projects ```sql DROP TRIGGER IF EXISTS add_favorite ON favorite; CREATE TRIGGER add_favorite BEFORE INSERT OR UPDATE ON favorite FOR EACH ROW EXECUTE PROCEDURE add_favorite(); CREATE OR REPLACE FUNCTION add_favorite() RETURNS TRIGGER AS $BODY$ BEGIN IF ((SELECT COUNT(*) FROM favorite WHERE NEW.generic_user_id = generic_user_id)>=5) THEN RAISE EXCEPTION 'A user cant have more than 5 favorite projects'; END IF; RETURN NEW; END; $BODY$ LANGUAGE plpgsql; ``` Trigger | TRIGGER02| | ----------- | -------------------------------------------------------| Description | Create a notification when a new member is added to a project ```sql CREATE OR REPLACE FUNCTION add_new_member_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO project_notification (projectType, notification_id, user_id, project_id) VALUES ('newMember', 4, NEW.user_id, NEW.id); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_new_member_notification ON project_notification; CREATE TRIGGER add_new_member_notification AFTER INSERT ON project_notification FOR EACH ROW EXECUTE PROCEDURE add_new_member_notification(); ``` Trigger | TRIGGER03| | ----------- | -------------------------------------------------------| Description | Create a notification when a project is deleted ```sql CREATE OR REPLACE FUNCTION add_deleted_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO project_notification (projectType, notification_id, user_id, project_id) VALUES ('deleted', 3, NEW.user_id, NEW.id); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_deleted_notification ON project_notification; CREATE TRIGGER add_deleted_notification AFTER INSERT ON project_notification FOR EACH ROW EXECUTE PROCEDURE add_deleted_notification(); ``` Trigger | TRIGGER04| | ----------- | -------------------------------------------------------| Description | Create a notification when a project member is expelled ```sql CREATE OR REPLACE FUNCTION add_expelled_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO project_notification (projectType, notification_id, user_id, project_id) VALUES ('expelled', 2, NEW.user_id, NEW.id); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_expelled_notification ON project_notification; CREATE TRIGGER add_expelled_notification AFTER INSERT ON project_notification FOR EACH ROW EXECUTE PROCEDURE add_expelled_notification(); ``` Trigger | TRIGGER05| | ----------- | -------------------------------------------------------| Description | Create a notification when there is a new project leader ```sql CREATE OR REPLACE FUNCTION add_project_leader_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO project_notification (projectType, notification_id, user_id, project_id) VALUES ('newLeader', 1, NEW.user_id, NEW.id); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_project_leader_notification ON project_notification; CREATE TRIGGER add_project_leader_notification AFTER INSERT ON project_notification FOR EACH ROW EXECUTE PROCEDURE add_project_leader_notification(); ``` Trigger | TRIGGER06| | ----------- | -------------------------------------------------------| Description | Create a notification when a task is completed ```sql CREATE OR REPLACE FUNCTION add_task_completed_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO task_notification (taskType, notification_id, user_id, task_id) VALUES ('completed', 6, NEW.user_id, NEW.id_task); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_task_completed_notification ON task_notification; CREATE TRIGGER add_task_completed_notification AFTER INSERT ON task_notification FOR EACH ROW EXECUTE PROCEDURE add_task_completed_notification(); ``` Trigger | TRIGGER07| | ----------- | -------------------------------------------------------| Description | Create a notification when a task is assigned ```sql CREATE OR REPLACE FUNCTION add_task_assigned_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO task_notification (taskType, notification_id, user_id, task_id) VALUES ('assigned', 5, NEW.user_id, NEW.id_task); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_task_assigned_notification ON task_notification; CREATE TRIGGER add_task_assigned_notification AFTER INSERT ON task_notification FOR EACH ROW EXECUTE PROCEDURE add_task_assigned_notification(); ``` Trigger | TRIGGER08| | ----------- | -------------------------------------------------------| Description | Create a notification when a comment response is given ```sql CREATE OR REPLACE FUNCTION add_comment_response_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO comment_notification (comment, notification_id, user_id, comment_id) VALUES ('response', 8, NEW.user_id, NEW.id_comment); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_comment_response_notification ON comment_notification; CREATE TRIGGER add_comment_response_notification AFTER INSERT ON comment_notification FOR EACH ROW EXECUTE PROCEDURE add_comment_response_notification(); ``` Trigger | TRIGGER09| | ----------- | -------------------------------------------------------| Description | Create a notification when a comment is liked ```sql CREATE OR REPLACE FUNCTION add_comment_like_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO comment_notification (comment, notification_id, user_id, comment_id) VALUES ('like', 7, NEW.user_id, NEW.id_comment); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_comment_like_notification ON comment_notification; CREATE TRIGGER add_comment_like_notification AFTER INSERT ON comment_notification FOR EACH ROW EXECUTE PROCEDURE add_comment_like_notification(); ``` 4. Transactions Transactions needed to assure the integrity of the data. SQL Reference | TRAN01| | ----------- | -------------------------------------------------------| Description | Insert a new generic user 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 generic_user fails). The isolation level is Repeatable Read, because, otherwise, an update of a generic user could happen, due to an insert in the table work committed by a concurrent transaction, and as a result, inconsistent data would be stored. Isolation level| REPEATABLE READ ```sql BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; INSERT INTO userss(username, password, email) VALUES ('poppy', 'poppy123', 'poppy@example.com'); INSERT INTO generic_user(name, birthdate, profilePic, isBanned) VALUES ('Poppy Deyes', '2023-10-22', 'profile60.jpg', false); END TRANSACTION; COMMIT; ``` SQL Reference | TRAN02| | ----------- | -------------------------------------------------------| Description | Assign a task 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 task fails). The isolation level is Repeatable Read, because, otherwise, an update of assigned task could happen, due to an insert in the table work committed by a concurrent transaction, and as a result, inconsistent data would be stored. Isolation level| REPEATABLE READ ```sql BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; INSERT INTO task(priority, content, isCompleted, dateCreation, deadLine, title, id_project) VALUES ('High', 'Conduct market research', false, '2023-10-22', '2023-11-30', 'Market Research', 3); INSERT INTO assigned(id_user, id_task) VALUES (20, 15); END TRANSACTION; COMMIT; ``` ### Annex A. SQL Code A.1. Database schema ```sql SET search_path TO public; DROP TABLE IF EXISTS userss CASCADE; DROP TABLE IF EXISTS generic_user CASCADE; DROP TABLE IF EXISTS projectt CASCADE; DROP TABLE IF EXISTS task CASCADE; DROP TABLE IF EXISTS comment CASCADE; DROP TABLE IF EXISTS likes CASCADE; DROP TABLE IF EXISTS isAdmin CASCADE; DROP TABLE IF EXISTS favorite CASCADE; DROP TABLE IF EXISTS notification CASCADE; DROP TABLE IF EXISTS project_notification CASCADE; DROP TABLE IF EXISTS task_notification CASCADE; DROP TABLE IF EXISTS comment_notification CASCADE; DROP TABLE IF EXISTS isMember CASCADE; DROP TABLE IF EXISTS isLeader CASCADE; DROP TABLE IF EXISTS taskOwner CASCADE; DROP TABLE IF EXISTS assigned CASCADE; DROP TABLE IF EXISTS commentOwner CASCADE; --Tables CREATE TABLE userss ( id SERIAL PRIMARY KEY, username VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL ); CREATE TABLE generic_user( id SERIAL PRIMARY KEY, name varchar(255) NOT NULL, birthdate DATE NOT NULL, profilePic TEXT NOT NULL, isBanned boolean NOT NULL, search TSVECTOR ); CREATE TABLE projectt( id SERIAL PRIMARY KEY, title varchar(255) NOT NULL, description varchar(255) NOT NULL, theme varchar(255) NOT NULL, archived boolean NOT NULL, search TSVECTOR ); CREATE TABLE task ( id SERIAL PRIMARY KEY, priority TEXT CHECK(priority IN ('Low', 'Medium', 'High')) NOT NULL, content TEXT, isCompleted BOOLEAN, dateCreation DATE, deadLine DATE, title varchar(255) NOT null, id_project int not null, foreign key (id_project) references projectt(id) ); CREATE TABLE comment( id SERIAL PRIMARY KEY, content varchar(255) NOT NULL, date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE likes( id SERIAL PRIMARY KEY, comment_id INT NOT NULL, generic_user_id INT NOT NULL, FOREIGN KEY (comment_id) REFERENCES comment (id), FOREIGN KEY (generic_user_id) REFERENCES generic_user (id) ); CREATE TABLE isAdmin( id SERIAL PRIMARY KEY, user_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES userss (id) ); CREATE TABLE favorite( id SERIAL primary key, project_id INT NOT NULL, generic_user_id INT NOT NULL, FOREIGN KEY (project_id) REFERENCES projectt (id), FOREIGN KEY (generic_user_id) REFERENCES generic_user (id) ); Create TABLE notification( id SERIAL PRIMARY KEY, description TEXT NOT NULL ); CREATE TABLE project_notification( id SERIAL PRIMARY KEY, project_id INT NOT NULL, notification_id INT, user_id INT NOT NULL, projectType TEXT CHECK(projectType IN ('newLeader', 'expelled','deleted','newMember')) NOT NULL, FOREIGN KEY (project_id) REFERENCES projectt (id), FOREIGN KEY (notification_id) REFERENCES notification (id), FOREIGN KEY (user_id) REFERENCES userss (id) ); CREATE TABLE task_notification( id SERIAL PRIMARY KEY, task_id INT NOT NULL, notification_id INT NOT NULL, user_id INT NOT NULL, taskType TEXT CHECK(taskType IN ('assigned', 'completed')) NOT NULL, FOREIGN KEY (task_id) REFERENCES task (id), FOREIGN KEY (notification_id) REFERENCES notification (id), FOREIGN KEY (user_id) REFERENCES userss (id) ); CREATE TABLE comment_notification( id SERIAL PRIMARY KEY, comment_id INT NOT NULL, notification_id INT NOT NULL, user_id INT NOT NULL, comment TEXT CHECK(comment IN ('like', 'response')) NOT NULL, FOREIGN KEY (comment_id) REFERENCES comment (id), FOREIGN KEY (notification_id) REFERENCES notification (id), FOREIGN KEY (user_id) REFERENCES userss (id) ); CREATE TABLE isMember( id_user Int not null, id_project int not null, primary key (id_user, id_project), foreign key(id_user) references userss(id), foreign key(id_project) references projectt(id) ); CREATE TABLE isLeader( id_user Int not null, id_project int not null, primary key (id_user, id_project), foreign key(id_user) references userss(id), foreign key(id_project) references projectt(id) ); CREATE TABLE taskOwner( id_user Int not null, id_task int not null, primary key (id_user, id_task), foreign key(id_user) references userss(id), foreign key(id_task) references task(id) ); CREATE TABLE assigned( id_user Int not null, id_task INT not null, primary key (id_user, id_task), foreign key(id_user) references userss(id), foreign key(id_task) references task(id) ); CREATE TABLE commentOwner( id_user Int not null, id_comment int not null, primary key (id_user, id_comment), foreign key(id_user) references userss(id), foreign key(id_comment) references comment(id) ); DROP INDEX IF EXISTS searchGenericUserName; DROP INDEX IF EXISTS searchProjectTitle; DROP INDEX IF EXISTS searchProjectTheme; DROP INDEX IF EXISTS searchTaskDeadline; DROP INDEX IF EXISTS searchTaskPriority; DROP INDEX IF EXISTS searchMember; DROP INDEX IF EXISTS searchLeader; DROP INDEX IF EXISTS searchTaskOwner; DROP INDEX IF EXISTS searchAssigned; DROP INDEX IF EXISTS searchCommentOwner; DROP INDEX IF EXISTS searchUser; DROP INDEX IF EXISTS searchProject; -- IDX01 CREATE INDEX searchGenericUserName ON generic_user USING HASH (name); -- IDX02 CREATE INDEX searchProjectTitle ON projectt USING HASH (title); -- IDX03 CREATE INDEX searchProjectTheme ON projectt USING HASH (theme); -- IDX04 CREATE INDEX searchTaskDeadline ON task USING BTREE (deadLine); -- IDX05 CREATE INDEX searchTaskPriority ON task USING BTREE (priority); -- IDX06 CREATE INDEX searchMember ON isMember USING BTREE (id_project); -- IDX07 CREATE INDEX searchLeader ON isLeader USING HASH (id_project); -- IDX08 CREATE INDEX searchTaskOwner ON taskOwner USING HASH (id_task); -- IDX09 CREATE INDEX searchAssigned ON assigned USING BTREE (id_task); -- IDX10 CREATE INDEX searchCommentOwner ON commentOwner USING HASH (id_comment); -- IDX11 CREATE INDEX searchUser ON generic_user USING GIN (search); CREATE OR REPLACE FUNCTION user_search_update() RETURNS TRIGGER AS $BODY$ BEGIN IF TG_OP = 'INSERT' THEN NEW.search = (SELECT setweight(to_tsvector(generic_user.name), 'A') FROM generic_user WHERE NEW.id=generic_user.id); ELSIF TG_OP = 'UPDATE' AND (NEW.username <> OLD.username) THEN NEW.search = (SELECT setweight(to_tsvector(generic_user.name), 'A') FROM generic_user WHERE NEW.id=generic_user.id); END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; DROP TRIGGER IF EXISTS update_user_search ON generic_user; CREATE TRIGGER update_user_search BEFORE INSERT OR UPDATE ON generic_user FOR EACH ROW EXECUTE PROCEDURE user_search_update(); -- IDX12 CREATE INDEX searchProject ON projectt USING GIN (search); CREATE OR REPLACE FUNCTION project_search_update() RETURNS TRIGGER AS $BODY$ BEGIN IF TG_OP = 'INSERT' THEN NEW.search = (SELECT setweight(to_tsvector(NEW.title), 'A') || setweight(to_tsvector(NEW.description), 'B')); ELSIF TG_OP = 'UPDATE' AND (NEW.title <> OLD.title OR NEW.description <> OLD.description) THEN NEW.search = (SELECT setweight(to_tsvector(NEW.title), 'A') || setweight(to_tsvector(NEW.description), 'B')); END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; DROP TRIGGER IF EXISTS update_project_search ON projectt; CREATE TRIGGER update_project_search BEFORE INSERT OR UPDATE ON projectt FOR EACH ROW EXECUTE PROCEDURE project_search_update(); -- TRIGGER01 DROP TRIGGER IF EXISTS add_favorite ON favorite; CREATE TRIGGER add_favorite BEFORE INSERT OR UPDATE ON favorite FOR EACH ROW EXECUTE PROCEDURE add_favorite(); CREATE OR REPLACE FUNCTION add_favorite() RETURNS TRIGGER AS $BODY$ BEGIN IF ((SELECT COUNT(*) FROM favorite WHERE NEW.generic_user_id = generic_user_id)>=5) THEN RAISE EXCEPTION 'A user cant have more than 5 favorite projects'; END IF; RETURN NEW; END; $BODY$ LANGUAGE plpgsql; -- TRIGGER02 CREATE OR REPLACE FUNCTION add_new_member_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO project_notification (projectType, notification_id, user_id, project_id) VALUES ('newMember', 4, NEW.user_id, NEW.id); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_new_member_notification ON project_notification; CREATE TRIGGER add_new_member_notification AFTER INSERT ON project_notification FOR EACH ROW EXECUTE PROCEDURE add_new_member_notification(); -- TRIGGER03 CREATE OR REPLACE FUNCTION add_deleted_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO project_notification (projectType, notification_id, user_id, project_id) VALUES ('deleted', 3, NEW.user_id, NEW.id); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_deleted_notification ON project_notification; CREATE TRIGGER add_deleted_notification AFTER INSERT ON project_notification FOR EACH ROW EXECUTE PROCEDURE add_deleted_notification(); -- TRIGGER04 CREATE OR REPLACE FUNCTION add_expelled_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO project_notification (projectType, notification_id, user_id, project_id) VALUES ('expelled', 2, NEW.user_id, NEW.id); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_expelled_notification ON project_notification; CREATE TRIGGER add_expelled_notification AFTER INSERT ON project_notification FOR EACH ROW EXECUTE PROCEDURE add_expelled_notification(); -- TRIGGER05 CREATE OR REPLACE FUNCTION add_project_leader_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO project_notification (projectType, notification_id, user_id, project_id) VALUES ('newLeader', 1, NEW.user_id, NEW.id); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_project_leader_notification ON project_notification; CREATE TRIGGER add_project_leader_notification AFTER INSERT ON project_notification FOR EACH ROW EXECUTE PROCEDURE add_project_leader_notification(); -- TRIGGER06 CREATE OR REPLACE FUNCTION add_task_completed_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO task_notification (taskType, notification_id, user_id, task_id) VALUES ('completed', 6, NEW.user_id, NEW.id_task); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_task_completed_notification ON task_notification; CREATE TRIGGER add_task_completed_notification AFTER INSERT ON task_notification FOR EACH ROW EXECUTE PROCEDURE add_task_completed_notification(); --TRIGGER07 CREATE OR REPLACE FUNCTION add_task_assigned_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO task_notification (taskType, notification_id, user_id, task_id) VALUES ('assigned', 5, NEW.user_id, NEW.id_task); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_task_assigned_notification ON task_notification; CREATE TRIGGER add_task_assigned_notification AFTER INSERT ON task_notification FOR EACH ROW EXECUTE PROCEDURE add_task_assigned_notification(); -- TRIGGER08 CREATE OR REPLACE FUNCTION add_comment_response_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO comment_notification (comment, notification_id, user_id, comment_id) VALUES ('response', 8, NEW.user_id, NEW.id_comment); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_comment_response_notification ON comment_notification; CREATE TRIGGER add_comment_response_notification AFTER INSERT ON comment_notification FOR EACH ROW EXECUTE PROCEDURE add_comment_response_notification(); -- TRIGGER09 CREATE OR REPLACE FUNCTION add_comment_like_notification() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO comment_notification (comment, notification_id, user_id, comment_id) VALUES ('like', 7, NEW.user_id, NEW.id_comment); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS add_comment_like_notification ON comment_notification; CREATE TRIGGER add_comment_like_notification AFTER INSERT ON comment_notification FOR EACH ROW EXECUTE PROCEDURE add_comment_like_notification(); ``` A.2. Database population ```sql SET search_path TO public; -- Insert data into userss table with 50 users INSERT INTO userss (username, password, email) VALUES ('alice', 'P@ssw0rd1', 'alice@example.com'), ('bob', 'Secur3P@ss', 'bob@example.com'), ('charlie', 'Str0ngP@ssw0rd', 'charlie@example.com'), ('diana', 'Pa$$w0rd123', 'diana@example.com'), ('edward', 'MyP@ssw0rd', 'edward@example.com'), ('frank', 'Passw0rd!', 'frank@example.com'), ('grace', 'P@ss123', 'grace@example.com'), ('henry', 'P@ssw0rd12', 'henry@example.com'), ('isabel', 'S3cureP@ss', 'isabel@example.com'), ('jack', 'P@ssw0rd!', 'jack@example.com'), ('kate', 'Str0ngP@ssword', 'kate@example.com'), ('liam', 'P@ssw0rd456', 'liam@example.com'), ('mary', 'Secure123!', 'mary@example.com'), ('nathan', 'P@ssw0rd321', 'nathan@example.com'), ('olivia', 'P@ssw0rd789', 'olivia@example.com'), ('peter', 'P@ssw0rd567', 'peter@example.com'), ('quinn', 'P@ssw0rd!!', 'quinn@example.com'), ('ryan', '12345P@ss', 'ryan@example.com'), ('sophia', 'P@ssw0rd111', 'sophia@example.com'), ('thomas', 'S3cureP@ssword', 'thomas@example.com'), ('violet', 'P@ssw0rd@123', 'violet@example.com'), ('william', 'P@ssw0rd444', 'william@example.com'), ('xander', 'Secure1234!', 'xander@example.com'), ('yasmine', 'P@ssw0rd!!123', 'yasmine@example.com'), ('zane', 'P@ssw0rd7890', 'zane@example.com'), ('aurora', 'P@ssw0rd5678', 'aurora@example.com'), ('beckett', 'P@ssw0rd#123', 'beckett@example.com'), ('clara', 'Secure@1234', 'clara@example.com'), ('dexter', 'P@ssw0rd_456', 'dexter@example.com'), ('ella', 'P@ssw0rd123_', 'ella@example.com'), ('finn', 'P@ssw0rd#567', 'finn@example.com'), ('gabriella', 'P@ssw0rd$123', 'gabriella@example.com'), ('hudson', 'Secure5678!', 'hudson@example.com'), ('isla', 'P@ssw0rd789#', 'isla@example.com'), ('jaxon', 'P@ssw0rd_7890', 'jaxon@example.com'), ('kayla', 'P@ssw0rd123!', 'kayla@example.com'), ('luca', 'P@ssw0rd$5678', 'luca@example.com'), ('mia', 'Secure#1234', 'mia@example.com'), ('noah', 'P@ssw0rd!1234', 'noah@example.com'), ('oliver', 'P@ssw0rd5678#', 'oliver@example.com'), ('penelope', 'P@ssw0rd$1234', 'penelope@example.com'), ('quinton', 'Secure12345!', 'quinton@example.com'), ('rebecca', 'P@ssw0rd!5678', 'rebecca@example.com'), ('samuel', 'P@ssw0rd_12345', 'samuel@example.com'), ('tabitha', 'P@ssw0rd123!$', 'tabitha@example.com'), ('ulysses', 'Secure5678$!', 'ulysses@example.com'), ('vivienne', 'P@ssw0rd$12345', 'vivienne@example.com'), ('wesley', 'P@ssw0rd_5678$', 'wesley@example.com'), ('xavier', 'P@ssw0rd!12345', 'xavier@example.com'), ('yara', 'Secure12345!$', 'yara@example.com'); -- Insert data into generic_user table with auto-incremented IDs INSERT INTO generic_user (name, birthdate, profilePic, isBanned) VALUES ('Alice Smith', '1990-05-15', 'profile1.jpg', false), ('Bob Johnson', '1985-09-23', 'profile2.jpg', false), ('Charlie Brown', '1988-11-30', 'profile3.jpg', false), ('Diana Davis', '1992-03-18', 'profile4.jpg', false), ('Edward Wilson', '1991-07-07', 'profile5.jpg', false), ('Frank Martin', '1987-12-02', 'profile6.jpg', false), ('Grace Taylor', '1989-02-14', 'profile7.jpg', false), ('Henry Anderson', '1993-06-20', 'profile8.jpg', false), ('Isabel White', '1986-10-10', 'profile9.jpg', false), ('Jack Harris', '1990-04-03', 'profile10.jpg', false), ('Kate Wilson', '1985-08-29', 'profile11.jpg', false), ('Liam Brown', '1988-05-16', 'profile12.jpg', false), ('Mary Jackson', '1992-11-08', 'profile13.jpg', false), ('Nathan Davis', '1991-09-07', 'profile14.jpg', false), ('Olivia Taylor', '1987-03-21', 'profile15.jpg', false), ('Peter Martinez', '1994-07-15', 'profile16.jpg', false), ('Quinn Rodriguez', '1986-12-10', 'profile17.jpg', false), ('Ryan Lee', '1990-01-25', 'profile18.jpg', false), ('Sophia Johnson', '1989-04-13', 'profile19.jpg', false), ('Thomas Smith', '1988-08-07', 'profile20.jpg', false), ('Violet Wilson', '1993-05-12', 'profile21.jpg', false), ('William Brown', '1992-09-28', 'profile22.jpg', false), ('Xander Lee', '1987-07-04', 'profile23.jpg', false), ('Yasmine Davis', '1991-06-09', 'profile24.jpg', false), ('Zane Martin', '1985-12-30', 'profile25.jpg', false), ('Aurora Jackson', '1990-10-18', 'profile26.jpg', false), ('Beckett Taylor', '1986-04-27', 'profile27.jpg', false), ('Clara Anderson', '1993-02-03', 'profile28.jpg', false), ('Dexter Smith', '1988-11-01', 'profile29.jpg', false), ('Ella Wilson', '1989-07-22', 'profile30.jpg', false), ('Finn Brown', '1992-03-29', 'profile31.jpg', false), ('Gabriella Harris', '1994-06-05', 'profile32.jpg', true), -- Marked as banned ('Hudson Johnson', '1987-08-14', 'profile33.jpg', false), ('Isla Taylor', '1990-11-26', 'profile34.jpg', false), ('Jaxon Davis', '1988-09-16', 'profile35.jpg', false), ('Kayla Lee', '1986-05-08', 'profile36.jpg', false), ('Luca Martin', '1991-02-27', 'profile37.jpg', false), ('Mia Rodriguez', '1989-04-19', 'profile38.jpg', false), ('Noah Smith', '1985-06-28', 'profile39.jpg', false), ('Oliver Johnson', '1992-08-11', 'profile40.jpg', false); -- Insert data into projectt table with 15 projects (one archived) INSERT INTO projectt (title, description, theme, archived) VALUES ('Research and Development', 'In-depth research and product development', 'Technology', false), ('Community Outreach Initiative', 'Engage with local communities and create positive impact', 'Community', false), ('Environmental Sustainability', 'Promoting eco-friendly practices and sustainability', 'Environment', false), ('Artistic Expression Showcase', 'Showcasing diverse forms of art and creativity', 'Art and Culture', false), ('Health and Wellness Campaign', 'Promoting healthy lifestyles and well-being', 'Health', false), ('Educational Innovation', 'Revolutionizing education for the digital age', 'Education', false), ('Cultural Heritage Preservation', 'Preserving and celebrating cultural heritage', 'Heritage', false), ('Business Expansion Strategy', 'Developing strategies for business growth', 'Business', false), ('Green Energy Revolution', 'Transitioning to clean and renewable energy sources', 'Energy', false), ('Music Festival Planning', 'Organizing a grand music festival event', 'Music', false), ('Urban Development Project', 'Transforming urban areas for the future', 'Urban Planning', false), ('Humanitarian Aid Mission', 'Providing aid to those in need worldwide', 'Humanitarian', false), ('Sports and Fitness Initiative', 'Promoting sports and fitness in the community', 'Sports', false), ('Scientific Research Exploration', 'Exploring the frontiers of scientific knowledge', 'Science', false), ('Culinary Delights Adventure', 'Discovering and savoring world cuisines', 'Food and Dining', true); -- Marked as archived -- Insert data into task table with 21 tasks for selected projects INSERT INTO task (priority, content, isCompleted, dateCreation, deadLine, title, id_project) VALUES -- Tasks for 'Research and Development' project (Project ID: 1) ( 'High', 'Conduct market research', false, '2023-10-15', '2023-11-15', 'Market Research', 1), ( 'Medium', 'Develop a prototype', false, '2023-10-16', '2023-11-30', 'Prototype Development', 1), ( 'Low', 'Prepare project documentation', false, '2023-10-20', '2023-12-15', 'Documentation Preparation', 1), -- Marked as completed -- Tasks for 'Community Outreach Initiative' project (Project ID: 2) ( 'Medium', 'Organize community event', false, '2023-10-18', '2023-11-25', 'Community Event Organization', 2), ( 'High', 'Create promotional materials', false, '2023-10-19', '2023-11-10', 'Promotional Materials Creation', 2), ( 'Low', 'Engage with local volunteers', false, '2023-10-22', '2023-11-30', 'Volunteer Engagement', 2), -- Tasks for 'Environmental Sustainability' project (Project ID: 3) ( 'Low', 'Conduct environmental audit', false, '2023-10-15', '2023-11-30', 'Environmental Audit', 3), ( 'High', 'Develop eco-friendly products', false, '2023-10-17', '2023-12-10', 'Product Development', 3), ( 'Medium', 'Create awareness campaigns', false, '2023-10-20', '2023-11-20', 'Awareness Campaigns', 3), -- Tasks for 'Artistic Expression Showcase' project (Project ID: 4) ( 'Medium', 'Curate art exhibitions', false, '2023-10-19', '2023-12-01', 'Art Exhibition Curation', 4), ( 'High', 'Coordinate performing artists', false, '2023-10-22', '2023-11-30', 'Artist Coordination', 4), ( 'Low', 'Design event posters', false, '2023-10-25', '2023-12-10', 'Poster Design', 4), -- Tasks for 'Health and Wellness Campaign' project (Project ID: 5) ( 'Low', 'Organize fitness classes', false, '2023-10-17', '2023-11-30', 'Fitness Class Organization', 5), ( 'High', 'Create health-related content', false, '2023-10-20', '2023-11-25', 'Content Creation', 5), ( 'Medium', 'Promote mental well-being', false, '2023-10-23', '2023-12-15', 'Mental Health Promotion', 5), -- Tasks for 'Educational Innovation' project (Project ID: 6) ( 'Medium', 'Develop online learning platform', false, '2023-10-16', '2023-12-05', 'Learning Platform Development', 6), ( 'Low', 'Design educational content', false, '2023-10-19', '2023-11-30', 'Content Design', 6), ( 'High', 'Conduct teacher training sessions', false, '2023-10-22', '2023-12-10', 'Teacher Training', 6), -- Tasks for 'Culinary Delights Adventure' project (Project ID: 15) ( 'Medium', 'Plan international cuisine showcase', false, '2023-10-16', '2023-12-05', 'Cuisine Showcase Planning', 15), ( 'High', 'Coordinate with local chefs', false, '2023-10-18', '2023-11-25', 'Chef Coordination', 15), ( 'Low', 'Select event venues', false, '2023-10-20', '2023-12-15', 'Venue Selection', 15); -- Insert data into comment table with 10 comments for selected tasks INSERT INTO comment (content, date) VALUES ('Great progress on the market research!', '2023-10-20 09:15:00'), ('Looking forward to the prototype development.', '2023-10-22 14:30:00'), ('The volunteer engagement is going well.', '2023-10-25 16:45:00'), ('Awesome work on the eco-friendly products!', '2023-10-26 11:20:00'), ('Can we schedule a meeting for the project?', '2023-10-27 09:30:00'), ('The environmental audit results are promising.', '2023-10-28 12:00:00'), ('This art exhibition curation is fantastic!', '2023-10-30 15:55:00'), ('We need to finalize the artist coordination.', '2023-10-31 10:10:00'), ('The fitness class organization is a hit!', '2023-11-01 14:15:00'), ('Looking forward to the learning platform launch.', '2023-11-02 16:40:00'); -- Insert data into likes table with 15 likes for selected comments INSERT INTO likes (comment_id, generic_user_id) VALUES (1, 1), -- Like for the first comment (2, 2), -- Like for the second comment (3, 3), -- Like for the third comment (4, 4), -- Like for the fourth comment (5, 5), -- Like for the fifth comment (6, 6), -- Like for the sixth comment (7, 7), -- Like for the seventh comment (8, 8), -- Like for the eighth comment (9, 9), -- Like for the ninth comment (10, 10), -- Like for the tenth comment (1, 11), -- Like for the first comment from a different user (2, 12), -- Like for the second comment from a different user (3, 13), -- Like for the third comment from a different user (4, 14), -- Like for the fourth comment from a different user (5, 15); -- Like for the fifth comment from a different user -- Insert data into isAdmin table with 15 different admin-user associations INSERT INTO isAdmin (user_id) VALUES (40), -- User 40 is an admin (41), -- User 41 is an admin (42), -- User 42 is an admin (43), -- User 43 is an admin (44), -- User 44 is an admin (45), -- User 45 is an admin (46), -- User 46 is an admin (47), -- User 47 is an admin (48), -- User 48 is an admin (49), -- User 49 is an admin (50); -- User 50 is an admin -- Insert data into favorite table with 15 favorite project associations (generic_user_id, project_id) INSERT INTO favorite (generic_user_id, project_id) VALUES (1, 1), -- First user's favorite project (2, 2), -- Second user's favorite project (3, 3), -- Third user's favorite project (4, 4), -- Fourth user's favorite project (5, 5), -- Fifth user's favorite project (6, 6), -- Sixth user's favorite project (7, 7), -- Seventh user's favorite project (8, 8), -- Eighth user's favorite project (9, 9), -- Ninth user's favorite project (10, 10), -- Tenth user's favorite project (11, 1), -- First user's another favorite project (12, 2), -- Second user's another favorite project (13, 3), -- Third user's another favorite project (14, 4), -- Fourth user's another favorite project (15, 5); -- Fifth user's another favorite project -- Insert data into the notification table with 10 notifications INSERT INTO notification (description) VALUES ('New project leader assigned.'), -- 1 project ('You have been expelled from the project.'), -- 2 project ('Project deleted by the administrator.'), -- 3 project ('New member joined the project.'), -- 4 project ('Task assigned to you.'), -- 5 task ('Task completed successfully.'), -- 6 task ('You received a like on your comment.'), -- 7 comment ('You received a response on your comment.'); -- 8 comment -- Insert data into isMember table with 40 user-project associations (excluding leaders) INSERT INTO isMember (id_user, id_project) VALUES (11, 1), -- User 11 is a member of Project 1 (12, 2), -- User 12 is a member of Project 2 (13, 3), -- User 13 is a member of Project 3 (14, 4), -- User 14 is a member of Project 4 (15, 5), -- User 15 is a member of Project 5 (16, 6), -- User 16 is a member of Project 6 (17, 7), -- User 17 is a member of Project 7 (18, 8), -- User 18 is a member of Project 8 (19, 9), -- User 19 is a member of Project 9 (20, 10), -- User 20 is a member of Project 10 (21, 1), -- User 21 is a member of Project 1 (22, 2), -- User 22 is a member of Project 2 (23, 3), -- User 23 is a member of Project 3 (24, 4), -- User 24 is a member of Project 4 (25, 5), -- User 25 is a member of Project 5 (26, 6), -- User 26 is a member of Project 6 (27, 7), -- User 27 is a member of Project 7 (28, 8), -- User 28 is a member of Project 8 (29, 9), -- User 29 is a member of Project 9 (30, 10), -- User 30 is a member of Project 10 (31, 1), -- User 31 is a member of Project 1 (32, 2), -- User 32 is a member of Project 2 (33, 3), -- User 33 is a member of Project 3 (34, 4), -- User 34 is a member of Project 4 (35, 5), -- User 35 is a member of Project 5 (36, 6), -- User 36 is a member of Project 6 (37, 7), -- User 37 is a member of Project 7 (38, 8), -- User 38 is a member of Project 8 (39, 9), -- User 39 is a member of Project 9 (40, 10); -- User 40 is a member of Project 10 -- Insert data into isLeader table with one leader per project INSERT INTO isLeader (id_user, id_project) VALUES (11, 1), -- User 11 is the leader of Project 1 (12, 2), -- User 12 is the leader of Project 2 (13, 3), -- User 13 is the leader of Project 3 (14, 4), -- User 14 is the leader of Project 4 (15, 5), -- User 15 is the leader of Project 5 (16, 6), -- User 16 is the leader of Project 6 (17, 7), -- User 17 is the leader of Project 7 (18, 8), -- User 18 is the leader of Project 8 (19, 9), -- User 19 is the leader of Project 9 (20, 15); -- User 20 is the leader of Project 15 -- Insert data into taskOwner table with task-owner associations INSERT INTO taskOwner (id_user, id_task) VALUES (11, 1), -- User 11 is the owner of Task 1 (12, 2), -- User 12 is the owner of Task 2 (13, 3), -- User 13 is the owner of Task 3 (14, 4), -- User 14 is the owner of Task 4 (15, 5), -- User 15 is the owner of Task 5 (16, 6), -- User 16 is the owner of Task 6 (17, 7), -- User 17 is the owner of Task 7 (18, 8), -- User 18 is the owner of Task 8 (19, 9), -- User 19 is the owner of Task 9 (20, 10), -- User 20 is the owner of Task 10 (11, 11), -- User 11 is the owner of Task 11 (12, 12), -- User 12 is the owner of Task 12 (13, 13), -- User 13 is the owner of Task 13 (14, 14), -- User 14 is the owner of Task 14 (15, 15), -- User 15 is the owner of Task 15 (16, 16), -- User 16 is the owner of Task 16 (17, 17), -- User 17 is the owner of Task 17 (18, 18), -- User 18 is the owner of Task 18 (19, 19), -- User 19 is the owner of Task 19 (20, 20), -- User 20 is the owner of Task 20 (11, 21); -- User 11 is the owner of Task 21 -- Insert data into assigned table with assigned-user associations INSERT INTO assigned (id_user, id_task) VALUES (11, 1), -- User 11 is assigned to Task 1 (12, 2), -- User 12 is assigned to Task 2 (13, 3), -- User 13 is assigned to Task 3 (14, 4), -- User 14 is assigned to Task 4 (15, 5), -- User 15 is assigned to Task 5 (16, 6), -- User 16 is assigned to Task 6 (17, 7), -- User 17 is assigned to Task 7 (18, 8), -- User 18 is assigned to Task 8 (19, 9), -- User 19 is assigned to Task 9 (20, 10), -- User 20 is assigned to Task 10 (11, 11), -- User 11 is assigned to Task 11 (12, 12), -- User 12 is assigned to Task 12 (13, 13), -- User 13 is assigned to Task 13 (14, 14), -- User 14 is assigned to Task 14 (15, 15), -- User 15 is assigned to Task 15 (16, 16), -- User 16 is assigned to Task 16 (17, 17), -- User 17 is assigned to Task 17 (18, 18), -- User 18 is assigned to Task 18 (19, 19), -- User 19 is assigned to Task 19 (20, 20), -- User 20 is assigned to Task 20 (11, 21); -- User 11 is assigned to Task 21 -- Insert data into commentOwner table with comment-owner associations INSERT INTO commentOwner (id_user, id_comment) VALUES (11, 1), -- User 11 is the owner of Comment 1 (12, 2), -- User 12 is the owner of Comment 2 (13, 3), -- User 13 is the owner of Comment 3 (14, 4), -- User 14 is the owner of Comment 4 (15, 5), -- User 15 is the owner of Comment 5 (16, 6), -- User 16 is the owner of Comment 6 (17, 7), -- User 17 is the owner of Comment 7 (18, 8), -- User 18 is the owner of Comment 8 (19, 9), -- User 19 is the owner of Comment 9 (20, 10); -- User 20 is the owner of Comment 10 ``` Revision history Changes made to the first submission: Item 1 .. GROUP2373, 23/10/2023 David dos Santos Ferreira , up202006302@fc.up.pt (Editor) Ana Sofia Oliveira Teixeira , up201806629@fe.up.pt Ana Sofia Silva Pinto , up202004606@fc.up.pt Gabriela Dias Salazar Neto Silva , up202004443@fe.up.pt

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.