---
# System prepended metadata

title: EBD

---


## ebd
## EBD: Database Specification Component


### A4: Conceptual Data Model

<p>The identification and descriptions of the entities and relationships pertinent to the database specification are contained in the conceptual data model.</p>

<p>The model is described in a UML class diagram.</p>
    


<p>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.</p>
#### 4.1. Class diagram

<p>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</p>


![](https://hackmd.io/_uploads/B1QCT5GM6.png)




   <center> Figure 1: TaskSquad conceptual data model in UML.</center>

#### 2. Additional Business Rules

<p>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.</p>

*    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

<p>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.</p>


#### 5.1. Relational Schema

<p>The Relational Schema includes the relation schemas, attributes, domains, primary keys, foreign keys and other integrity rules: UNIQUE, DEFAULT, NOT NULL, CHECK.</p>
<p>Relation schemas are specified in the compact notation:</p>

Relation reference |	Relation Compact Notation|
| ----------- | -------------------------------------------------------|
|R01| 	user(<u>id</u>,username **UK** **NN**,password **NN**,email **UK** **NN**)
|R02| 	generic_user(<u>id</u>,name **NN**, birthdate **NN**,profilePic,isBanned **NN** **DF** False,<u>id_user</u>->user)|
|R03|isAdmin(<u>id_user</u>->user)|
R04| project(<u>id</u>,title **NN UK**,description **NN**,theme **NN**,archived **NN DF** False )|
|R05| 	task(<u>id</u>,<u>id_project</u>->project,content **NN**,isCompleted **NN DF** False,dateCreation **NN**,title **NN**,deadLine **NN CK** deadLine>dateCreation,priority NN CK priority IN priorityTask)|
|R06 |	comment(<u>id</u>,<u>id_task</u>->task,content **NN**,date **NN DF** Today)|
|R07|likes(<u>id_generic_user</u>->generic_user,<u>id_comment</u>->comment)|
|R08|favorite(<u>id_generic_user</u>->generic_user,<u>id_project</u>->project)|
|R09|notifications(<u>id</u>,description **NN**)|
|R10|projectNotification(<u>id_user</u>->user,<u>idProject</u>->project,ptype **NN CK** ptype **IN** projectType)|
|R11|taskNotification(<u>id_user</u>->user,<u>id_task</u>->task,ttype **NN CK** ttype **IN** notificationType)|
|R12|commentNotification(<u>id_user</u>->user,<u>id_comment</u>->comment,ctype **NN CK** ctype **IN** commentType)|
|R13|isMember(<u>id_user</u>->user,<u>id_projext</u>->project)|
|R14|isLeader((<u>id_user</u>->user,<u>id_project</u>->project))|
|R15|taskOwner(<u>id_user</u>->user,<u>id_task</u>->task)|
|R16|assigned(<u>id_user</u>->user,<u>id_task</u>->task)|
|R17|commentOwner(<u>id_user</u>->user,<u>id_comment</u>->comment,<u>id_task</u>->task)|
    
    
Legend:

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


#### 5.2. Domains

<p>The specification of additional domains can also be made in a compact form, using the notation:</p>

|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

<p>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).</p>


|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

<p>The physical database structure, index requirements, data integrity enforcement using triggers, and user-defined function definitions are all covered by this artifact.</p>
<p>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.</p>

#### 6.1. Database Workload

<p>A robust database design relies on a clear grasp of the workload, encompassing estimated tuple counts and growth projections.</p>

|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

