ebd

EBD: Database Specification Component

A4: Conceptual Data Model

1. Class diagram

2. Additional Business Rules

Business Rule Identifier	Business Rule
BR01	Administrator accounts are independent of the user and organizer accounts, i.e. they cannot create or participate in events and so on.
BR02	Administrator accounts cannot be blocked.
BR03	Organizers have the same (and more) permissions than users. Organizers just can't make an organizer request and users can.
BR04	Only users who joined an event can answer to polls for that event.
BR05	Users cannot answer to the same poll more than once.
BR06	Users cannot vote on their own comments.
BR07	Users cannot vote to the same comment more than once.
BR08	Users cannot invite themselves.
BR09	Users cannot invite another user more than once to the same event.
BR10	Users cannot make a join request to a private event
BR11	Only organizers can create polls.
BR12	Only organizers can create events.
BR13	Organizers cannot make a join request for their own event.
BR14	Organizers cannot make a report for their own event.

A5: Relational Schema, validation and schema refinement

The goal of this artifact is to represent the relational schema of the database. It also includes additonal domains and validations of the schema.

1. Relational Schema

Relation schemas are specified in the compact notation:

Relation reference	Relation Compact Notation
R01	User(UserId, username UK NN, name NN, email UK NN CK EMAL_IS_VALID, password NN, userPhoto, accountStatus NN CK accountStatus IN AccountStatus, userType NN CK userType IN UserTypes)
R02	Event(EventId, eventName UK NN, public NN, address NN, description NN, eventPhoto, organizerId → User NN, startDate NN, endDate NN CH endDate > startDate NN, eventCanceled NN DF False)
R03	Attendee(attendeeId → User, eventId → Event)
R04	JoinRequest(JoinRequestId, requesterId → User NN, eventId → Event NN, requestStatus)
R05	Category(CategoryId, name UK NN)
R06	CategoryEvent(categoryId → Category, eventId → Event)
R07	Tag(TagId, name UK NN)
R08	TagEvent(tagId → Tag, eventId → Event)
R09	Comment(CommentId, authorId → User NN, eventId → Event NN, parentId → Comment, comment NN, commentDate NN DF Now)
R10	Upload(UploadId, commentId → Comment NN, fileName NN)
R11	Vote(voterId → User, commentId → Comment, type NN)
R12	Poll(PollId, eventId → Event NN, pollContent NN)
R13	PollOption(PollOptionId, optionContent NN)
R14	Answer(userId → User NN, PollOptionId → PollOption NN)
R15	Invitation(InvitationId, (inviterId,inviteeId) → User, eventId → Event NN, invitationStatus)
R16	OrganizerRequest(OrganizerRequestId, requesterId → User NN, requestStatus)
R17	Report(ReportId, reporterId → User NN, eventId → Event NN, message NN, reportStatus NN DF False)
R18	Notification(NotificationId, receiverId → User NN, message NN, notificationDate NN DF Now, notificationStatus NN DF False, pollId → Poll, eventId → Event, joinRequestId → JoinRequest, organizerRequestId → OrganizerRequest, inviteId → Invitation, notificationType NN CK notificationType IN NotificationTypes)

Legend:

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

2. Domains

Specification of additional domains:

Domain Name	Domain Specification
Now	DATE DEFAULT CURRENT_TIMESTAMP
AccountStatus	ENUM ('Active', 'Disabled', 'Blocked')
UserTypes	ENUM ('User', 'Organizer', 'Administrator')
NotificationTypes	ENUM ('NewPoll', 'EventChange', 'JoinRequestReviewed', 'OrganizerRequestReviewed', 'InviteAccepted', 'InviteReceived')
EMAL_IS_VALID	TEXT CHECK(VALUE LIKE '%@%.__%')

3. Schema validation

Table R01	User
Keys	{userId, email, username}
Functional Dependencies
FD0101	{userId} -> {username, name, email, password, userPhoto,isBlocked}
FD0102	{email} -> {userId, username, name, email, password, userPhoto,isBlocked}
FD0103	{username} -> {userId,email, name, email, password, userPhoto,isBlocked}
Normal Form	BCNF

Table R02	Event
Keys	{eventId}
Functional Dependencies
FD0201	{eventId} -> {eventName, organizerId, public, localization, description, eventPhoto,start, end}
Normal Form	BCNF

Table R03	Attendee
Keys	{attendeeId,eventId}
Functional Dependencies	none
Normal Form	BCNF

Table R04	JoinRequest
Keys	{requestId}
Functional Dependencies
FD0401	{requestId} -> {requestStatus, requesterId, eventId}
Normal Form	BCNF

Table R05	Category
Keys	{categoryId, category}
Functional Dependencies
FD0501	{categoryId} -> {category}
Normal Form	BCNF

Table R06	CategoryEvent
Keys	{categoryId, eventId}
Functional Dependencies	none
Normal Form	BCNF

Table R07	Tag
Keys	{tagId,tag}
Functional Dependencies
FD0701	{tagId} -> {tag}
Normal Form	BCNF

Table R08	TagEvent
Keys	{tagId,eventId}
Functional Dependencies	none
Normal Form	BCNF

Table R09	Comment
Keys	{commentId}
Functional Dependencies
FD0701	{commentId} -> {comment, commentDate, eventId, authorId, parentId}
Normal Form	BCNF

Table R10	Upload
Keys	{uploadId}
Functional Dependencies
FD0801	{uploadId} -> {fileName, commentId}
Normal Form	BCNF

Table R11	Vote
Keys	{userId, commentId}
Functional Dependencies
FD0901	{voterId, commentId} -> {type}
Normal Form	BCNF

Table R12	Poll
Keys	{pollId}
Functional Dependencies
FD1001	{pollId} -> {pollContent,eventId}
Normal Form	BCNF

Table R13	PollOption
Keys	{pollOptionId}
Functional Dependencies
FD1101	{pollOptionId} -> {optionContent,pollId}
Normal Form	BCNF

Table R14	Answer
Keys	{userId, pollId}
Functional Dependencies
FD1201	{userId, pollId} -> {voteType}
Normal Form	BCNF

Table R15	Invitation
Keys	{invitationId}
Functional Dependencies
FD1301	{invitationId} -> {invitationStatus, inviterId, inviteeId}
Normal Form	BCNF

Table R16	OrganizerRequest
Keys	{OrganizerRequestId}
Functional Dependencies
FD1401	{OrganizerRequestId} -> {requesterId, requestStatus}
Normal Form	BCNF

Table R17	Report
Keys	{reportId}
Functional Dependencies
FD1501	{reportId} -> {reporterId, eventId, message, reportStatus}
Normal Form	BCNF

Table R18	Notification
Keys	{notificationId}
Functional Dependencies
FD1601	{notificationId} -> {receiverId, notificationDate, notificationStatus, eventId ,pollId, inviteId, joinRequestId, organizerRequestId }
Normal Form	BCNF

Because all relations are in the Boyce–Codd Normal Form (BCNF), the relational schema is also in the BCNF. The schema is, therefore, fully normalised.

A6: Indexes, triggers, transactions and database population

The goal of this artifact is to define all the Triggers, Indexes and Transactions that will be used when the database is fully operational.

1. Database Workload

Relation reference	Relation Name	Order of magnitude	Estimated growth
R01	User	10 k (tens of thousands)	10 (tens) / day
R02	Event	10 k (tens of thousands)	10 (tens) / day
R03	JoinRequest	10 k (hundreds of thousands)	10 (tens) / day
R04	Category	100	no growth
R05	Tag	100	no growth
R06	Comment	100 k (hundreds of thousands)	100 (hundreds) / day
R07	Upload	100 k (hundreds of thousands)	100 (hundreds) / day
R08	Vote	100 k (hundreds of thousands)	100 (hundreds) / day
R09	Poll	10 k (tens of thousands)	10 (tens) / day
R10	PollOption	10 k (tens of thousands)	10 (tens) / day
R11	Answer	100 k (hundreds of thousands)	100 (hundreds) / day
R12	Invitation	10 k (tens of thousands)	10 (tens) / day
R13	OrganizerRequest	1 k (one thousand)	1 /day
R14	Report	1 k (one thousand)	1 / day
R15	Notification	100 k (hundreds of thousands)	100 (hundreds) / day

2. Proposed Indices

2.1. Performance Indices

Indices proposed to improve performance of the identified queries.

Index	IDX01
Relation	Comment
Attribute	eventId
Type	Hash
Cardinality	Medium
Clustering	No
Justification	One of the most frequent queries in the application will be fetching comments of an event. Therefore, it is crucial we create an hash index to perform exact search of comments that belong to a certain event, considering the comments' workload's high order of magnitude. At last, due to the high estimated growth of this relation and the medium cardinality, we determined that this index shouldn't use clustering.
`SQL code`	`CREATE INDEX comments_event ON Comment USING hash (eventId);`

Index	IDX02
Relation	Upload
Attribute	commentId
Type	Hash
Cardinality	Medium
Clustering	No
Justification	As mentioned above, of the most frequent queries in the application will be fetching comments of an event and for every comment fetched, we'll also need to fetch any uploads associated to it (if any exist). Therefore, in order to improve the perfomance of the database, we decided to create an hash index to perform exact search of uploads that belong to a certain comment, considering the comments' and the uploads' workload's high order of magnitude. At last, due to the high estimated growth of this relation and the medium cardinality, we determined that this index shouldn't use clustering.
`SQL code`	`CREATE INDEX comments_upload ON Upload USING hash (commentId);`

Index	IDX03
Relation	Notification
Attribute	receiverId
Type	Hash
Cardinality	Medium
Clustering	No
Justification	Fetching notifications of an user will also be one of the most frequent queries in the application. Consequently, there's the need to have an hash index to perform exact search of notifications that an user received, considering that the notifications' workload has a high order of magnitude. Finally, and equal to the indexes above, we determined that this index shouldn't use clustering due to the high estimated growth of this relation and the medium cardinality.
SQL Code	`CREATE INDEX notification_receiver ON Notification USING hash (receiverId);`

2.2. Full-text Search Indexes

Index	IDX11
Relation	Event
Attribute	(eventName, description)
Type	GiST
Clustering	No
Justification	Users will frequently search for events with a given name or description. Therefore, we need a full-text search index to efficiently fetch the results of this search. For this matter, we chose GiST, since it's faster to build and update, as users might create/edit events often.
SQL Code	`ALTER TABLE "Event" ADD COLUMN tsvectors TSVECTOR; CREATE INDEX event_search ON "Event" USING GIST (tsvectors);`

3. Triggers

User-defined functions and trigger procedures that add control structures to the SQL language or perform complex computations, are identified and described to be trusted by the database server. Every kind of function (SQL functions, Stored procedures, Trigger procedures) can take base types, composite types, or combinations of these as arguments (parameters). In addition, every kind of function can return a base type or a composite type. Functions can also be defined to return sets of base or composite values.

Trigger	TRIGGER01
Description	Adds an user to the event when they accept a invite to a event

CREATE FUNCTION insert_attendee_invitation() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.invitationStatus && NEW.inviteeId NOT IN (SELECT Attendee.attendeeId FROM Attendee
    WHERE Attendee.eventId==NEW.eventId)) THEN
        INSERT INTO Attendee(attendeeId,eventId)
        VALUES (NEW.inviteeId,NEW.eventId);
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER attendee_inserted
    AFTER UPDATE ON Invitation
    EXECUTE PROCEDURE insert_attendee_invitation();

Trigger	TRIGGER02
Description	Adds the user to an event when their join request is accepted

CREATE FUNCTION insert_attendee_request() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.requestStatus && NEW.requesterId NOT IN (SELECT Attendee.attendeeId FROM Attendee
    WHERE Attendee.eventId==NEW.requesterId)) THEN
        INSERT INTO Attendee(attendeeId,eventId)
        VALUES (NEW.requesterId,NEW.eventId);
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER joinUserEventRequestAccepted
    AFTER UPDATE ON JoinRequest
    EXECUTE PROCEDURE insert_attendee_request();

Trigger	TRIGGER03
Description	Creates a notification to all users that go to an event when the date of start and/or end of that event has changed

CREATE FUNCTION eventChange() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF ((NEW.eventStart != OLD.eventStart) || (NEW.eventEnd != OLD.eventEnd)) THEN
        INSERT INTO Notification (receiverId,eventId,notificationDate,notificationType)
        SELECT userId,eventId, DATE('now'),'EventChange'
        FROM Attendee WHERE NEW.eventId == Attendee.attendeeId;
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER eventChange_notification
    AFTER UPDATE ON Event
    EXECUTE PROCEDURE eventChange();

Trigger	TRIGGER04
Description	Creates a notification to the user when another user accepts a invite made by the first user

CREATE FUNCTION inviteAccepted() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.invitationStatus) THEN
        INSERT INTO Notification (receiverId,invitationId,notificationDate,notificationType)
        VALUES(NEW.inviterId,NEW.invitationId, DATE('now'),'InviteAccepted');
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER notification_invite_accepted
    AFTER UPDATE ON Invitation
    EXECUTE PROCEDURE inviteAccepted();

Trigger	TRIGGER05
Description	Creates a notification when a user receives a new invite to an event

CREATE FUNCTION newInvitation() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO Notification (receiverId,invitationId,notificationDate,notificationType)
    VALUES(NEW.inviteeId,NEW.invitationId, DATE('now'),'NewInvitation');
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER new_invitation
    AFTER INSERT ON Invitation
    EXECUTE PROCEDURE newInvitation();

Trigger	TRIGGER06
Description	Creates a notification to a user when a join request made by them is accepted/declined

CREATE FUNCTION joinRequestReviewed() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO Notification (receiverId,joinRequestId,notificationDate,notificationType)
    VALUES(NEW.requesterId,NEW.joinRequestId, DATE('now'),'JoinRequestReviewed');
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER join_request_reviewed
    AFTER UPDATE ON JoinRequest
    EXECUTE PROCEDURE joinRequestReviewed();

Trigger	TRIGGER07
Description	Creates a notification to a user when a request to be given organization status made by them is accepted/declined

CREATE FUNCTION organizerRequestReviewed() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO Notification (receiverId,organizerRequestId,notificationDate,notificationType)
    VALUES(New.requesterId,New.organizerRequestId, DATE('now'),'OrganizerRequestReviewed');
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER organizer_request_reviewed
    AFTER UPDATE ON OrganizerRequest
    EXECUTE PROCEDURE organizerRequestReviewed();

Trigger	TRIGGER08
Description	Creates a notification to a user when a report made by them is reviewed by the admins

CREATE FUNCTION reportReviewed() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.reportStatus) THEN
        INSERT INTO Notification (receiverId,reportId,notificationDate,notificationType)
        VALUES(NEW.reporterId,NEW.reportId, DATE('now'),'ReportReviewed');
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER report_reviewed
    AFTER UPDATE ON Report
    EXECUTE PROCEDURE reportReviewed();

Trigger	TRIGGER09
Description	Creates notifications to all users in a event when a poll is created

CREATE FUNCTION newPoll() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO Notification (receiverId,pollId,notificationDate,notificationType)
    SELECT userId,NEW.pollId, DATE('now'),'NewPoll'
    FROM Attendee WHERE NEW.eventId == Attendee.eventId;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER new_poll_notification
    AFTER INSERT ON Poll
    EXECUTE PROCEDURE newPoll();

Trigger	TRIGGER10
Description	Updates the user to a organization when a request made by them is accepted

CREATE FUNCTION updateUserToOrg() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.requestStatus) THEN
        UPDATE Users 
        SET userType = 'Organizer'
        WHERE NEW.requesterId==Users.userId;
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER update_user_to_organization
    AFTER UPDATE ON OrganizerRequest
    EXECUTE PROCEDURE updateUserToOrg();

Trigger	TRIGGER11
Description	Deletes all information from an user when they delete their account

CREATE FUNCTION deleteUser() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.accountStatus=='Disabled') THEN
        DELETE FROM Atendee
        WHERE attendeeId = NEW.userId;
        
        DELETE FROM JoinRequest
        WHERE requesterId = NEW.userId AND requestStatus=NULL;

        DELETE FROM OrganizerRequest
        WHERE requesterId = NEW.userId AND requestStatus= FALSE;

        DELETE FROM Notification
        WHERE receiverId = NEW.userId;

        UPDATE Users 
        SET 
        username = CONCAT('Anonymous',userId),
        name='Anonymous',
        email=CONCAT('Deleted',userId),
        password = 'Deleted',
        userPhoto = NULL,
        userTypes = NULL
        WHERE NEW.userId==Users.userId;


    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;


CREATE TRIGGER user_deleted
    AFTER UPDATE ON Users
    EXECUTE PROCEDURE deleteUser();

Trigger	TRIGGER12
Description	Deletes all user information about an event when it is cancelled

CREATE FUNCTION eventCancelled() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.eventCanceled ==TRUE) THEN
        DELETE FROM Atendee
        WHERE eventId = NEW.eventId;

        DELETE FROM JoinRequest
        WHERE eventId = NEW.eventId;

    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;


CREATE TRIGGER event_cancelled
    AFTER UPDATE ON Event
    EXECUTE PROCEDURE eventCancelled();

Trigger	TRIGGER13
Description	Pre-calculate Event's tsvectors (IDX11)

CREATE FUNCTION event_search_update() RETURNS TRIGGER AS $$

  IF TG_OP = 'INSERT' THEN
    NEW.tsvectors = (
      setweight(to_tsvector('english', NEW.eventName), 'A') ||
      setweight(to_tsvector('english', NEW.description), 'B')
    );
  END IF;

  IF TG_OP = 'UPDATE' THEN
      IF (NEW.eventName <> OLD.eventName OR NEW.description <> OLD.description) THEN
        NEW.tsvectors = (
          setweight(to_tsvector('english', NEW.eventName), 'A') ||
          setweight(to_tsvector('english', NEW.description), 'B')
        );
      END IF;
  END IF;

  RETURN NEW;
END $$


CREATE TRIGGER event_search_update
  BEFORE INSERT OR UPDATE ON "Event"
  FOR EACH ROW
  EXECUTE PROCEDURE event_search_update();

Trigger	TRIGGER14
Description	Inserts Event Organizer in attendees table after they create a new event

CREATE FUNCTION NewEvent() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO attendee (attendeeid,eventid)
    VALUES(NEW.userid,NEW.eventid);
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER new_event
    AFTER INSERT ON event
    FOR EACH ROW
    EXECUTE PROCEDURE NewEvent();

4. Transactions

Transactions needed to assure the integrity of the data.

Transaction	TRAN01
Description	Add a new notification when a new invitation or joinRequest is sent
Justification	In order to maintain consistency, we have to guarantee that this code executes without interferences. The isolation is Repeatable Read because an update on the sequence invitation_id_seq or joinRequest_id_seq by an insert in each respective table would mean that the notification that is created would be associated to the wrong id.
Isolation level	REPEATABLE READ

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

INSERT INTO Invitation (inviterId, inviteeId, eventId)
  VALUES ($inviterId, $inviteeId, $eventId);

INSERT INTO Notification (receiverId,  inviteId)
  VALUES ($receiverId, currval('invitation_id_seq'));

COMMIT;

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

INSERT INTO JoinRequest (requesterId , eventId)
  VALUES ($requesterId, $eventId);

INSERT INTO Notification (receiverId,  requestId )
  VALUES ($receiverId, currval('joinRequest_id_seq'));

COMMIT;

Transaction	TRAN02
Description	Delete a user account
Justification	In order to maintain consistency, it's necessary to ensure that all this code executes without errors, when deleting all the internal data related to the user. If an error occurs, a ROLLBACK is done and all the operations restart. The isolation level is Serializable since a phantom read could occur, thus avoiding actions such as accepting an Organizer request of a user that doesn't exist, or getting the wrong number of attendees in an event.
Isolation level	SERIALIZABLE

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE User
SET username = CONCAT('Anonymous',userId),
SET name = 'Anonymous',
SET email = CONCAT('Deleted',userId),
SET password = 'Deleted',
SET userPhoto = NULL,
SET accountStatus = 'Disabled'
SET userTypes = NULL
WHERE userId = $userId;

DELETE FROM Atendee
WHERE attendeeId = $userId;

DELETE FROM JoinRequest
WHERE requesterId = $userId AND requestStatus=NULL;

DELETE FROM OrganizerRequest
WHERE requesterId = $userId AND requestStatus= FALSE;

DELETE FROM Notification
WHERE receiverId = $userId;

COMMIT;

Transaction	TRAN03
Description	Cancel an event
Justification	In order to maintain consistency, it's necessary to ensure that all this code executes without errors. The isolation level is Serializable since a phantom read could occur, thus leading, for example, to the organizer receiving Joinrequests of an event he has canceled.
Isolation level	SERIALIZABLE

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE Event
SET eventCanceled = TRUE,
WHERE eventId = $eventId;

DELETE FROM Atendee
WHERE eventId = $eventId;

DELETE FROM JoinRequest
WHERE eventId = $eventId;

COMMIT;

Annex A. SQL Code

The database scripts are included in this annex to the EBD component.

The database creation script and the population script should be presented as separate elements. The creation script includes the code necessary to build (and rebuild) the database. The population script includes an amount of tuples suitable for testing and with plausible values for the fields of the database.

The complete code of each script must be included in the group's git repository and links added here.

A.1. Database schema

create schema if not exists lbaw2252;

DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS event CASCADE;
DROP TABLE IF EXISTS attendee CASCADE;
DROP TABLE IF EXISTS category CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
DROP TABLE IF EXISTS report CASCADE;
DROP TABLE IF EXISTS invitation CASCADE;
DROP TABLE IF EXISTS poll CASCADE;
DROP TABLE IF EXISTS comment CASCADE;
DROP TABLE IF EXISTS joinrequest CASCADE;
DROP TABLE IF EXISTS organizerrequest CASCADE;
DROP TABLE IF EXISTS notification CASCADE;
DROP TABLE IF EXISTS vote CASCADE;
DROP TABLE IF EXISTS polloption CASCADE;
DROP TABLE IF EXISTS answer CASCADE;
DROP TABLE IF EXISTS upload CASCADE;
DROP TABLE IF EXISTS event_category CASCADE;
DROP TABLE IF EXISTS event_tag CASCADE;
DROP TABLE IF EXISTS contact CASCADE;

DROP TYPE IF EXISTS notificationtype;
DROP TYPE IF EXISTS accountstatus;
DROP TYPE IF EXISTS usertypes;

CREATE TYPE notificationtype AS ENUM ('EventChange','JoinRequestReviewed','OrganizerRequestReviewed','InviteReceived','InviteAccepted','NewPoll');
CREATE TYPE accountstatus AS ENUM ('Active','Disabled','Blocked');
CREATE TYPE usertypes AS ENUM ('User','Organizer','Admin');

CREATE TABLE users(
  userid SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL CONSTRAINT unique_usernam_uk UNIQUE,
  name VARCHAR(150) NOT NULL, 
  email TEXT NOT NULL CONSTRAINT user_email_uk UNIQUE,
  password TEXT NOT NULL,
  userphoto TEXT,
  accountstatus accountstatus NOT NULL,
  usertype usertypes NOT NULL,
  remember_token TEXT -- Laravel's remember me functionality
);

CREATE TABLE event(
    eventid SERIAL PRIMARY KEY,
    userid INTEGER REFERENCES users (userid) ON DELETE SET NULL ON UPDATE CASCADE,
    eventname TEXT NOT NULL CONSTRAINT unique_eventname UNIQUE,
    public BOOLEAN NOT NULL,
    eventaddress TEXT NOT NULL,
    description TEXT NOT NULL,
    eventcanceled BOOLEAN NOT NULL DEFAULT FALSE,
    eventphoto TEXT NOT NULL,
    startdate DATE NOT NULL,
    enddate DATE NOT NULL,
    CONSTRAINT end_after_start_ck CHECK (enddate > startdate)
);

CREATE TABLE attendee(
  attendeeid INTEGER NOT NULL REFERENCES users (userid) ON DELETE CASCADE ON UPDATE CASCADE,
  eventid INTEGER NOT NULL REFERENCES event (eventid) ON UPDATE CASCADE,
  PRIMARY KEY(attendeeid, eventid)
);

CREATE TABLE category(
  categoryid SERIAL PRIMARY KEY,
  categoryname TEXT NOT NULL CONSTRAINT category_uk UNIQUE
);

CREATE TABLE tag(
  tagid SERIAL PRIMARY KEY,
  tagname TEXT NOT NULL CONSTRAINT tag_uk UNIQUE
);

CREATE TABLE report(
  reportid SERIAL PRIMARY KEY,
  reporterid INTEGER REFERENCES users (userid) ON DELETE SET NULL ON UPDATE CASCADE,
  eventid INTEGER NOT NULL REFERENCES event (eventid) ON UPDATE CASCADE,
  message TEXT NOT NULL,
  reportstatus BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE invitation(
  invitationid SERIAL PRIMARY KEY,
  inviterid INTEGER NOT NULL REFERENCES users (userid) ON DELETE CASCADE ON UPDATE CASCADE,
  inviteeid INTEGER NOT NULL REFERENCES users (userid) ON DELETE CASCADE ON UPDATE CASCADE,
  eventid INTEGER NOT NULL REFERENCES event (eventid) ON UPDATE CASCADE,
  invitationstatus BOOLEAN,
  CONSTRAINT invite_To_Self_ck CHECK (inviterid != inviteeid)
);

CREATE TABLE poll(
  pollid SERIAL PRIMARY KEY,
  eventid INTEGER NOT NULL REFERENCES event (eventid) ON UPDATE CASCADE,
  pollcontent TEXT NOT NULL
);

CREATE TABLE comment(
  commentid SERIAL PRIMARY KEY,
  authorId INTEGER REFERENCES users (userid) ON DELETE SET NULL ON UPDATE CASCADE,
  eventid INTEGER NOT NULL REFERENCES event (eventid) ON UPDATE CASCADE,
  parentId INTEGER REFERENCES comment (commentid) ON DELETE CASCADE ON UPDATE CASCADE,
  commentcontent TEXT NOT NULL,
  commentdate DATE NOT NULL
);

CREATE TABLE joinrequest(
  joinrequestid SERIAL PRIMARY KEY,
  requesterid INTEGER NOT NULL REFERENCES users (userid) ON DELETE CASCADE ON UPDATE CASCADE,
  eventid INTEGER NOT NULL REFERENCES event (eventid) ON UPDATE CASCADE,
  requeststatus BOOLEAN
);

CREATE TABLE organizerrequest(
  organizerrequestid SERIAL PRIMARY KEY,
  requesterid INTEGER NOT NULL REFERENCES users (userid) ON DELETE CASCADE ON UPDATE CASCADE,
  requeststatus BOOLEAN
);

CREATE TABLE notification(
  notificationid SERIAL PRIMARY KEY,
  receiverid INTEGER NOT NULL REFERENCES users (userid) ON DELETE CASCADE ON UPDATE CASCADE,
  eventid INTEGER REFERENCES event (eventid) ON DELETE CASCADE ON UPDATE CASCADE,
  joinrequestid INTEGER REFERENCES joinrequest (joinrequestid) ON DELETE CASCADE ON UPDATE CASCADE,
  organizerrequestid INTEGER REFERENCES organizerrequest (organizerrequestid) ON DELETE CASCADE ON UPDATE CASCADE,
  invitationid INTEGER REFERENCES invitation (invitationid) ON DELETE CASCADE ON UPDATE CASCADE,
  pollid INTEGER REFERENCES poll (pollid) ON DELETE CASCADE ON UPDATE CASCADE,
  notificationdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  notificationtype notificationtype NOT NULL,
  notificationstatus BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE vote(
  voterid INTEGER REFERENCES users (userid) ON UPDATE CASCADE ON DELETE CASCADE,
  commentid INTEGER REFERENCES comment (commentid) ON UPDATE CASCADE ON DELETE CASCADE,
  type BOOLEAN NOT NULL,
  PRIMARY KEY(voterid, commentid)
);

CREATE TABLE polloption(
  polloptionid SERIAL NOT NULL,
  optioncontent TEXT NOT NULL
);

CREATE TABLE answer(
  userid INTEGER REFERENCES users (userid) ON UPDATE CASCADE ON DELETE CASCADE,
  pollid INTEGER REFERENCES poll (pollid) ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY(userid, pollid)
);

CREATE TABLE upload(
  uploadid SERIAL PRIMARY KEY,
  commentid INTEGER NOT NULL REFERENCES comment (commentid) ON UPDATE CASCADE ON DELETE CASCADE,
  fileName TEXT NOT NULL
);

CREATE TABLE event_category(
  eventid INTEGER NOT NULL REFERENCES event (eventid) ON UPDATE CASCADE ON DELETE CASCADE,
  categoryid INTEGER NOT NULL REFERENCES category (categoryid) ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY (eventid,categoryid)
);

CREATE TABLE event_tag(
  eventid INTEGER NOT NULL REFERENCES event (eventid) ON UPDATE CASCADE ON DELETE CASCADE,
  tagid INTEGER NOT NULL REFERENCES tag (tagid) ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY (eventid,tagid)
);

-- Added during PA development
CREATE TABLE contact(
  contactid SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL, 
  email TEXT NOT NULL,
  subject TEXT NOT NULL,
  message TEXT NOT NULL
);

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


DROP INDEX IF EXISTS comments_event;
DROP INDEX IF EXISTS comments_upload;
DROP INDEX IF EXISTS notification_receiver;
DROP INDEX IF EXISTS event_search;
DROP INDEX IF EXISTS user_search;


CREATE INDEX comments_event ON comment USING hash (eventid);
CREATE INDEX comments_upload ON upload USING hash (commentid);
CREATE INDEX notification_receiver ON notification USING hash (receiverid);

ALTER TABLE event ADD COLUMN tsvectors TSVECTOR; 
CREATE INDEX event_search ON event USING GIST (tsvectors);

ALTER TABLE users ADD COLUMN tsvectors TSVECTOR;
CREATE INDEX user_search ON users USING GIST (tsvectors);

-----------------------------------------
-- Triggers
-----------------------------------------



DROP FUNCTION IF EXISTS insert_attendee_invitation ;
DROP TRIGGER IF EXISTS attendee_inserted ON invitation;
DROP FUNCTION IF EXISTS insert_attendee_request;
DROP TRIGGER IF EXISTS joinUsereventRequestAccepted ON joinrequest;
DROP FUNCTION IF EXISTS EventChange;
DROP TRIGGER IF EXISTS EventChange_notification ON notification;
DROP FUNCTION IF EXISTS InviteAccepted;
DROP TRIGGER IF EXISTS notification_invite_accepted ON invitation;
DROP FUNCTION IF EXISTS newinvitation;
DROP TRIGGER IF EXISTS new_invitation ON invitation;
DROP FUNCTION IF EXISTS JoinRequestReviewed;
DROP TRIGGER IF EXISTS join_request_reviewed ON joinrequest;
DROP FUNCTION IF EXISTS OrganizerRequestReviewed;
DROP TRIGGER IF EXISTS organizer_request_reviewed ON organizerrequest;
DROP FUNCTION IF EXISTS reportReviewed;
DROP TRIGGER IF EXISTS report_reviewed ON report;
DROP FUNCTION IF EXISTS NewPoll;
DROP TRIGGER IF EXISTS new_poll_notification ON poll;
DROP FUNCTION IF EXISTS updateUserToOrg;
DROP TRIGGER IF EXISTS update_user_to_organization ON organizerrequest;
DROP FUNCTION IF EXISTS eventCancelled;
DROP TRIGGER IF EXISTS event_cancelled ON event;
DROP FUNCTION IF EXISTS NewEvent;
DROP TRIGGER IF EXISTS new_event ON event;
DROP FUNCTION IF EXISTS event_search_update;
DROP TRIGGER IF EXISTS event_search_update ON event;
DROP FUNCTION IF EXISTS user_search_update;
DROP TRIGGER IF EXISTS user_search_update ON users;



CREATE FUNCTION insert_attendee_invitation() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.invitationstatus AND NEW.inviteeid NOT IN (SELECT attendee.attendeeid FROM attendee
    WHERE attendee.eventid=NEW.eventid)) THEN
        INSERT INTO attendee(attendeeid,eventid)
        VALUES (NEW.inviteeid,NEW.eventid);
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER attendee_inserted
    AFTER UPDATE ON invitation
    FOR EACH ROW
    EXECUTE PROCEDURE insert_attendee_invitation();

CREATE FUNCTION insert_attendee_request() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.requeststatus && NEW.requesterid NOT IN (SELECT attendee.attendeeid FROM attendee
    WHERE attendee.eventid=NEW.requesterid)) THEN
        INSERT INTO attendee(attendeeid,eventid)
        VALUES (NEW.requesterid,NEW.eventid);
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER joinUsereventRequestAccepted
    AFTER UPDATE ON joinrequest
    FOR EACH ROW
    EXECUTE PROCEDURE insert_attendee_request();


CREATE FUNCTION EventChange() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF ((NEW.startdate != OLD.startdate) OR (NEW.enddate != OLD.enddate)) THEN
        INSERT INTO notification (receiverid,eventid,notificationtype)
        SELECT userid,eventid,'EventChange'
        FROM attendee WHERE NEW.eventid = attendee.eventid;
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER EventChange_notification
    AFTER UPDATE ON event
    FOR EACH ROW
    EXECUTE PROCEDURE EventChange();


CREATE FUNCTION InviteAccepted() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.invitationstatus) THEN
        INSERT INTO notification (receiverid,invitationid,notificationtype)
        VALUES(NEW.inviterid,NEW.invitationid,'InviteAccepted');
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER notification_invite_accepted
    AFTER UPDATE ON invitation
    FOR EACH ROW
    EXECUTE PROCEDURE InviteAccepted();


CREATE FUNCTION newinvitation() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO notification (receiverid,invitationid,notificationtype)
    VALUES(NEW.inviteeid, NEW.invitationid,'InviteReceived');
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER new_invitation
    AFTER INSERT ON invitation
    FOR EACH ROW
    EXECUTE PROCEDURE newinvitation();


CREATE FUNCTION JoinRequestReviewed() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO notification (receiverid,joinrequestid,notificationtype)
    VALUES(NEW.requesterid,NEW.joinrequestid,'JoinRequestReviewed');
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER join_request_reviewed
    AFTER UPDATE ON joinrequest
    FOR EACH ROW
    EXECUTE PROCEDURE JoinRequestReviewed();


CREATE FUNCTION OrganizerRequestReviewed() RETURNS TRIGGER AS
$BODY$
BEGIN
  IF (NEW.requeststatus) THEN
    INSERT INTO notification (receiverid,organizerrequestid,notificationtype)
    VALUES(NEW.requesterid,NEW.organizerrequestid,'OrganizerRequestReviewed');
  END IF;  
  RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER organizer_request_reviewed
    AFTER UPDATE ON organizerrequest
    FOR EACH ROW
    EXECUTE PROCEDURE OrganizerRequestReviewed();


CREATE FUNCTION NewPoll() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO notification (receiverid,pollid,notificationtype)
    SELECT attendeeid,NEW.pollid,'NewPoll'
    FROM attendee WHERE NEW.eventid = attendee.eventid;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER new_poll_notification
    AFTER INSERT ON poll
    FOR EACH ROW
    EXECUTE PROCEDURE NewPoll();


CREATE FUNCTION updateUserToOrg() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.requeststatus = TRUE) THEN
        UPDATE users 
        SET usertype = 'Organizer'
        WHERE NEW.requesterid=users.userid;
    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER update_user_to_organization
    AFTER UPDATE ON organizerrequest
    FOR EACH ROW
    EXECUTE PROCEDURE updateUserToOrg();


CREATE FUNCTION eventCancelled() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.eventcanceled =TRUE) THEN
        DELETE FROM Atendee
        WHERE eventid = NEW.eventid;

        DELETE FROM joinrequest
        WHERE eventid = NEW.eventid;

    END IF;
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;


CREATE TRIGGER event_cancelled
    AFTER UPDATE ON event
    FOR EACH ROW
    EXECUTE PROCEDURE eventCancelled();



CREATE FUNCTION NewEvent() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO attendee (attendeeid,eventid)
    VALUES(NEW.userid,NEW.eventid);
    RETURN NULL;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER new_event
    AFTER INSERT ON event
    FOR EACH ROW
    EXECUTE PROCEDURE NewEvent();

CREATE FUNCTION event_search_update() RETURNS TRIGGER AS
$BODY$
BEGIN

  IF TG_OP = 'INSERT' THEN
    NEW.tsvectors = (
      setweight(to_tsvector('english', NEW.eventName), 'A') ||
      setweight(to_tsvector('english', NEW.description), 'B')
    );
  END IF;

  IF TG_OP = 'UPDATE' THEN
      IF (NEW.eventName <> OLD.eventName OR NEW.description <> OLD.description) THEN
        NEW.tsvectors = (
          setweight(to_tsvector('english', NEW.eventName), 'A') ||
          setweight(to_tsvector('english', NEW.description), 'B')
        );
      END IF;
  END IF;

  RETURN NEW;
END
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER event_search_update
  BEFORE INSERT OR UPDATE ON event
  FOR EACH ROW
  EXECUTE PROCEDURE event_search_update();

CREATE FUNCTION user_search_update() RETURNS TRIGGER AS
$BODY$
  BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.tsvectors = (
      setweight(to_tsvector('english', NEW.username), 'A') ||
      setweight(to_tsvector('english', NEW.name), 'B')
    );
  END IF;

  IF TG_OP = 'UPDATE' THEN
      IF (NEW.username <> OLD.username OR NEW.name <> OLD.name) THEN
        NEW.tsvectors = (
          setweight(to_tsvector('english', NEW.username), 'A') ||
          setweight(to_tsvector('english', NEW.name), 'B')
        );
      END IF;
  END IF;

  RETURN NEW;
END 
$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER user_search_update
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW
  EXECUTE PROCEDURE user_search_update();

Database Schema

A.2. Database population

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

-- Users --

insert into Users (userId, username, name, email, password, userphoto, accountStatus, userType) values (1, 'mfalcus0', 'Micky Falcus', 'mfalcus0@google.com.hk', '4Bx1P1Yz1mlN', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAJ6SURBVDjLjZO7T1NhGMY7Mji6uJgYt8bElTjof6CDg4sMSqIxJsRGB5F4TwQSIg1QKC0KWmkZEEsKtEcSxF5ohV5pKSicXqX3aqGn957z+PUEGopiGJ583/A+v3znvPkJAAjWR0VNJG0kGhKahCFhXcN3YBFfx8Kry6ym4xIzce88/fbWGY2k5WRb77UTTbWuYA9gDGg7EVmSIOF4g5T7HZKuMcSW5djWDyL0uRf0dCc8inYYxTcw9fAiCMBYB3gVj1z7gLhNTjKCqHkYP79KENC9Bq3uxrrqORzy+9D3tPAAccspVx1gWg0KbaZFbGllWFM+xrKkFQudV0CeDfJsjN4+C2nracjunoPq5VXIBrowMK4V1gG1LGyWdbZwCalsBYUyh2KFQzpXxVqkAGswD3+qBDpZwow9iYE5v26/VwfUQnnznyhvjguQYabIIpKpYD1ahI8UTT92MUSFuP5Z/9TBTgOgFrVjp3nakaG/0VmEfpX58pwzjUEquNk362s+PP8XYD/KpYTBHmRg9Wch0QX1R80dCZhYipudYQY2Auib8RmODVCa4hfUK4ngaiiLNFNFdKeCWWscXZMbWy9Unv9/gsIQU09a4pwvUeA3Uapy2C2wCKXL0DqTePLexbWPOv79E8f0UWrencZ2poxciUWZlKssB4bcHeE83NsFuMgpo2iIpMuNa1TNu4XjhggWvb+R2K3wZdLlAZl8Fd9jRb5sD+Xx0RJBx5gdom6VsMEFDyWF0WyCeSOFcDKPnRxZYTQL5Rc/nn1w4oFsBaIhC3r6FRh5erPRhYMyHdeFw4C6zkRhmijM7CnMu0AUZonCDCnRJBqSus5/ABD6Ba5CkQS8AAAAAElFTkSuQmCC', 'Active', 'User');
insert into Users (userId, username, name, email, password, userphoto, accountStatus, userType) values (2, 'esergent1', 'Elfrida Sergent', 'esergent1@trellian.com', 'fkmQL7D', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAIkSURBVDjLjZPLaxpRGMWzsHQTpLvusgiF7rvuqn9L/4Muu/UxvlAUFa0SUWsU32+jg0iLi1KIohhREWnxTdU4aG2E5vS7AxHSWJvFwJ3H79xzzv3mCMDRoSudTksTiUQ+Fotx4XBY8vf7x8CFaDS6CoVCQiAQ4Hw+n+RRAqlUShqPxxm8Ho/HWK1WIPjG4/FwLpfr6UGBOzgSiayHwyFoDYoAQRBwdnb22+FwJG0225O9AslkUkof83cwA8/Pz0FxsFgsRCcECxaLhTOZTJK9MJW1HgwGIPvMNsgRLjsD6NMNxL5+g9lsvjUajSW9Xv9sB1NZUgJ4KkuEyQG8Xi9IFLPZDOaLJt5++IJ3nkt87w9BsKDVal/vhckBqCwx+3Q6RalUwo/ZHO8DFQQ+NZHgP0Oj0Ww5jnsjCtBufDAY3E4mE5AI3G63mJ3BJI5isYher4dGq4uPcZ7BNwS/2pVIcN7v99/2+310Oh3k83kRZg4ymQy63S4ajQayuQuo1eqNUqk8vXeM1LJQq9VwfX2NdruNVqslOmDNX11doVqtIpvNQqVS/ST45MEgUVmjcrksfjSfz9FsNkURtmulUhFdMFihUJzsHWWarOdOp3NbKBTEo2OtL5dL1Ot18DwPyvtLLpef7hu63cJutx9brdYp240dIcudy+UYvCH45b9G/t4NTdaxwWAYsfysQMq7kclkLw79cA8e6HS6Y2p6RLDwP5hdfwD40PFHzWBC4QAAAABJRU5ErkJggg==', 'Active', 'Organizer');
insert into Users (userId, username, name, email, password, userphoto, accountStatus, userType) values (3, 'glanahan2', 'Gaultiero Lanahan', 'glanahan2@rediff.com', 'RV61Fv407NB', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAITSURBVDjLpZNNSJRBGMd/s7q50ZoWISZSRAgF5VpELFJ0CK9RneokEl1qO3URpFNCXqIO0aFLEF0iiAj6JBB0WYwOoikRJiXUYqbsumv7zvu+M/N02BV30YPgc5h5GPh/zMz/USLCVirCFqt+tZGfb8UUFxEJEBMiNkRMgBgfsT6EGms0YjwINU0Xn6haAmuIHrm0TkEEFFQWQCD3/PJ6B37+N9tFEOeVDxSIOEAhrDGoSAMSehtcwRhcMI8pfgLnIxKUdxeA04jTiPPYtucCLixtQGB9wCBOg4QVUDVYI64EYpBgAwdmZalsuUbZwzldIfHAeWUR8289gbMaPTOK8b+DDUAMVheI7W8pKzuNWA/E1byBWg3S4oteibZ0EO86DzhcMEdx/BkN+3aBlBie1YzMOZY9j6CU489K/tabOxOD9VVMhAuT5D6m2dl9FaUUTkKQEu+/FZny45w5fYL23R0MT79kbGr0djLV1hyp/u/Gk72E+b/kR+5VwBqxmtdfc3QdSmAjlsTeHqwKSR7tBri+FmWjUXURdhy/gphmiplX1MUSxFr7WCgsEVVxzh2+AcDNs4842NIJEKvKgSb37j5iNBJ6BN4XmM1Q+vyUQiFgOpthIpumv+cxQx/6iNU1AGi1mWlMptoG2w80DXR3nqKj9Rgz8+NkJtP8+rF8V212nJOptiHgGtAIFIGHYw+y/f8B3ntD1Kp2NbQAAAAASUVORK5CYII=', 'Active', 'User');
insert into Users (userId, username, name, email, password, userphoto, accountStatus, userType) values (4, 'dblackader3', 'Darlene Blackader', 'dblackader3@shareasale.com', 'eKrMNfNnbjj', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAJZSURBVDjLtZNfSFNRHMdvzHqPIEIo8qEgKIrwIa4PE+ceZo0cC8Ymtq3duz/un7uz7spkxe022dx0e9OxgUqSQdlLYLZemkEhPvRQCGlxqAaW5kutovDbuTeUwgIjOvDlHH7w/fy+v8M5DADmX8T8N0Ay2WuVZenNXwNSqaSGmqVEQq5K0mX8zkR8Akec/loVkMmk2UymT1VfX4ql5ueFQh4TEzcRj1/cACCezu4lXxDE4ppRAel0CrlcFtlsPwYG+jE6Oozx8XGMjAwjJp79BUDcYZ6c9h6s2FxYbnOC6M1+GrkX5fIDlEolTE1NoVgsQpIkCsxCiITWAYQLcUu8H+SUI0ZOWEPLZhtIw/E5hs6KsbFrSCQSkGUZPT09EASBnq8gGPSpgMVW2zyx8bpFK+3aYlFrhG0BOaoDo1xUPj8Inudht9thNpthMBjg83mo3GuAZWJq19EdpPHkD8CRJpADDWC6u2MYGhpELCYiHA7BZGqFVqulsHZ4PK71EWbNh28/NO5Eqalm9V7ztpWnjftB9tbPMV1dEUSjYWr2q5EDAZ8KcTrtNJVTBUxb9ohPose+fLqTxuqzSXy8LmCm4xAe1e+4wUQ6A6y/w8t6vRzrdp9hed4xr4BEUQDHOVTA3WbNQpWakTMC4nbgah3eJRtB6y82PBLaVcO5HDJVdQ2gxF6dvYWf10p8lzrOH58oBbVRVdQEes2rDwV6H9T0+RyD91TEqwGtv97Uhylbai895vZ9rYh1eHt+Kxa4LbhvrPk2qddc2PSvm7bsjtGOL5XYSiLFrNS/A2oTmihPyHNrAAAAAElFTkSuQmCC', 'Active', 'User');
insert into Users (userId, username, name, email, password, userphoto, accountStatus, userType) values (5, 'hhead4', 'Harald Head', 'hhead4@apple.com', 'VA8jERoJmX2z', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAF7SURBVDjLpZI/S5thFMVPXt9aakBjIYJvyFycnZw7KDhkcCi41BK30tpkqA5d/QClLv0IKuIQB/8Mje5dVLCCxhQtkg+QFnLPuU8HqYY0Q4h3fLjnx+9ynlQIAY+ZqNfFxcr8ypvtVwN9A2icp/Fr53uq84SlajEv+ZyoacknRWVlwvhwDk6h1qh93lzY+dAV8L5anHL6cpLOFTJPR5F+kkYcxfDgoAt04rR+gtqvq9XK24NPABDfh78V85KWX2QmCmPpMfyx34iiCHRCLngIkAtGwoyD/3L3AFFzyVBSyA5lQRdMwtntOX426qAJyfMEpHB1U1vbLVU//gcgNT08mEHTmqCEox+H1zRubbyulABg9svLY5q+75Wr77q2IOPMyLMRDKRiXDYu0B4GADOum3Gxs4UHAxMoQsFRb9SxubBTal/cLx+udqu3DUAwEJKDLfb8E+M2RRgFQTDrA8CW7gxc/RnQCBPhoU8DaxF0wh9jsH+0d9cGewf8BRKi/IUanjYRAAAAAElFTkSuQmCC', 'Disabled', 'User');
insert into Users (userId, username, name, email, password, userphoto, accountStatus, userType) values (6, 'ckirtland5', 'Cathrin Kirtland', 'ckirtland5@fotki.com', 'ubbMHX3B', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAKoSURBVDjLpZLbT5JhHMdZ1/VP1Cqrtco21kU3zevWVa3NXOuuedVmoU2jUR6CoRwcw1SYHIzJQQxBUXgLXg6Ga+mIwkBBBp08pYGvEOa393nbWC5Xa1389mzPnu/v+/t+nh8PAO9/6p8FBoMBWq0Wvb29UKlU13ihUAikAoEAfD4fKIrC5OQkxsfHMTo6CrvdDovFApPJBL1ez70tl8vI5XKQy+UxXjAYxPb2Nkql0h8rn89Do9G839jYwNzcHGQyWVoikdTzaJrmLrLZLKamppDJZEDu0uk0PB4PkskknE4n98ZqtSIWi3ETicXimgoDr9eLcDgMl8vF9/v9sNlsfCI2Go18EqOvr49PxEqlkj84OMjlb21trao0cLvdiEajHINUKsUxIM5EHI/HQTmUmKcFGHqixezsLHGHUCjcv+sXRkZGUCgUMDExAZY03+FwECf+sNWEhLs2vZq0YMZeZ+zv7ydi/PaNbK6W6elpJBIJEDFxNpvNiIdUWI4bUS7M4/XwFbwKO9DU1LSz5x7odDpCGj09Peju7kafqg1R62UUl50ofujC2oILkaGbENxp2PnrIr21Xdr3xnzRsPLOimL2AehHZ/Ft1YoZbQ1kwutfdzUYGBg4ypJ+rFarCWl0dnZCIxcgTTWjtKQHM38DdMcZbGUasZ4ag6frwveI4tyBSgMWVgs5FQrFLalUuigVtzWwTi+/sOC2Fm9jM3H1ZyXr2ChyZPxKhCTVwkoDdqdb2LXkFiUSiWBM14wM3YXSJzXnvpmsZSNUcyeTqgfz8Snohyc/+0Unju/K3d7eDpFIhJD8/DqzsoDSGoXiEstgyfJL2VDOx5B7YcSz5iOWPQGy460EO04zgbZTDOvEsE6M7/4x5vm9KoYVMdTdwwzVeIjxCg4GfgDxYPxXmKLFvgAAAABJRU5ErkJggg==', 'Active', 'Admin');
insert into Users (userId, username, name, email, password, userphoto, accountStatus, userType) values (7, 'mdougary6', 'Merilee Dougary', 'mdougary6@artisteer.com', 'YsKeI5DrZ', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAH8SURBVDjLjZPfS1NhGMdXf0VEQhDUhdCN4X0IYT8ghIJQM0KoC4vushZddLELKyRhQQkSFIKEGEkUCI2oxVhepG5zi1xbc0u3cDs7Z+ec/ezT+x62scmmHvhwDrzP93Pe57znsQE2cR0SdAm6d+GwYL/M1LBVBV35fF4plUqVcrlMK8Q6TqdzYrukJuiW4Vwuh67rdbLZLJlMhmQyaUnigVlC05f4+dbB0tQplp92DsnwPimQBaZpUigUrLtE0zQURSGVSqHF37DhGkVZeQdagszKLJ7HvZtNAhmuIQWGYaCqKps/ZkivPqCwPs/Gp0cYvjnKUTe+F9fMJoFoo96zfJZ9K+sLpP33qRhujPANtr7dJPhqmO/PBxX3+PljTYLtqImPpH13qZge9LUrmLEB1FU7sZd9jJw5MljNthYk/KLnxdFqeAjzdz9Z/z3Ck2fRE36qx9pakAjME1y4Lbb9GTMyTD52GUXsZO3ZadTkL6umrSD4ZZrAezvLH54Q915EjwywtXSH8FQf+t+I9V12FLwe6wE1SmjyAi77Qb6Kt3rGe9H+hKzwrgLH9eMUPE4K3gm8jpPMjRwlHfNTLBbr7Cjo7znA2NVOXA/PsThzi2wyah1pI+0E/9rNQQsqMtM4CyfE36fLhb2ERa0mB7BR0CElexjnGnL0O2T2PyFunSz8jchwAAAAAElFTkSuQmCC', 'Active', 'User');
insert into Users (userId, username, name, email, password, userphoto, accountStatus, userType) values (8, 'bbullman7', 'Brandyn Bullman', 'bbullman7@amazon.co.jp', '9UHYEsUtub', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAIySURBVDjLpdNdTFJhGAdw120X3XXR5kU33fQxS0+5Yl24lFnQKsvl2nJLM0fmXLNASceKgAv8yBGgJPEhkcIShEEYKuKU1IxcTm0WUDiJ1Fpbm1tZ/855186oLS/k4r/34n2e355z9rwZADLSyX8vCm+WU6fqT38+21S4ztPy1rmK4lXF5Ry//Hwm6LjoHN8QOGOgUOe9iGByCJ7FJ5BMX0ORiosfa1/wTHqQIAQ4VCHbwpXL53iWHPAe7QefJAvq4G2MLY9gcnUcQ0kf/AkvAm4DPvhl6Lq+jwEuESD7inLrCWXJ10BygC56SgpHlofxfGUMjvhjDH7sR1e0Hfq3VmiqKSwOt6CldCcD7CDA3qrOXfRo37tjRojC5SRt81KYIxp4lxx0+mCOaqEON8NeR2Ght5ppBvsTT9Yqai60F/y0vTehPlyBW+FKAliiOvQnPGQKY+Q+TOOdCCjzEPU2/A1wxIaH3a8N0C20ouGVAI3TVVC9kcEa0yO0MgrfkptM0mprwqypGKG2AgaYYYEsqfGFI94D4csy1E6VonlWgt64Fb6EG7aYGTdGK1ETEv6yu+wEcDQeZoA7LHBEJfxkiejQQxczccZtEE8JwHNRKLMK1rRzng6R3xU8kLkdM/oidAh2M8BRFsi7W/Iu38wBty8bXCcdSy6OyfjfUneCbjj34OoeMkHq92+4SP8A95wSTlrA/ISGnxZAmgeV+ewKbwqwi3MZQLQZQP3nFTLnttS73y9CuFIqo/imAAAAAElFTkSuQmCC', 'Active', 'User');

-- removed for brevity

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

Full database population

Revision History

Changes made to this submission:

Added a new trigger to insert users in attendees table after creating an event
Added a new contact table to save contact form submissions from users
Fixed errors from previous delivery
Added parentId in comment table
Editor for this submission: Daniela Santos Tomás
Last Changed: 21/12/2022

GROUP2223

Daniela Tomás, up202004946@edu.fc.up.pt
Hugo Almeida, up202006814@edu.fe.up.pt
José Miguel Isidro, up202006485@edu.fe.up.pt
Sara Moreira Reis, up202005388@edu.fe.up.pt

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ebd