COMS4111 - CU Eventify: Event Management System

PostgreSQL Account

• Username: gp2755
• Database Name: proj1part2

---

Web Application URL

• URL: [http://104.196.31.191:8111/events]

---

---

Teammates:

1. Geethanjali Prakash - gp2755
2. Atharv Hrushikesh Sardesai - ahs2204

---

Eventify: Event Management System

Query 1:

1. Multi-Table Join with Aggregation and Conditions: Highest Revenue per Organizer

This query calculates the total revenue generated by each organizer from ticket fees for their events. It uses multi-table joins between the organizers, events, and tickets tables. The results are grouped by the organizer's name, and ticket fees are summed for each organizer. The query filters organizers with revenue greater than 0 and sorts the results by highest total revenue.

SELECT o.name AS organizer_name, SUM(t.fee) AS total_revenue
FROM organizers o
JOIN events e ON o.organizer_id = e.organizer_id
JOIN tickets t ON e.event_id = t.event_id
GROUP BY o.name
HAVING SUM(t.fee) > 0
ORDER BY total_revenue DESC;

Detailed Explanation:

1. SELECT o.name AS organizer_name, SUM(t.fee) AS total_revenue:
   Selects the organizer’s name and calculates the total revenue for each organizer by summing the ticket fees.

2. FROM organizers o:
   Specifies the organizers table, assigning it an alias (o) for easy reference.

3. JOIN events e ON o.organizer_id = e.organizer_id:
   Joins the events table with organizers based on the matching organizer_id, associating events with their organizers.

4. JOIN tickets t ON e.event_id = t.event_id:
   Joins the tickets table with events based on the event_id, linking tickets with events.

5. GROUP BY o.name:
   Groups the results by organizer name to calculate revenue per organizer.

6. HAVING SUM(t.fee) > 0:
   Filters to show only organizers with total revenue greater than 0.

7. ORDER BY total_revenue DESC:
   Orders the results in descending order by total revenue, showing the highest revenue first.

Query 2:

Event Audience Diversity Index (Cross-Department Participation)

This query calculates the diversity index for each event, which is the number of unique departments that attendees come from. It helps determine how diverse the event's audience is across different departments within the university.

SELECT e.name AS event_name, 
       COUNT(DISTINCT a.department) AS department_diversity,
       COUNT(a.attendee_id) AS total_attendees
FROM events e
JOIN tickets t ON e.event_id = t.event_id
JOIN attendees a ON t.ticket_id = a.ticket_id
GROUP BY e.name
HAVING COUNT(DISTINCT a.department) > 1 -- Show only events with attendees from more than one department
ORDER BY department_diversity DESC, total_attendees DESC;

Detailed Explanation:

1. SELECT e.name AS event_name, COUNT(DISTINCT a.department) AS department_diversity, COUNT(a.attendee_id) AS total_attendees:
   Selects the event name, calculates the number of unique departments attendees are from (COUNT(DISTINCT a.department)), and counts the total number of attendees for each event.

2. FROM events e:
   Specifies the events table as the primary source for the query.

3. JOIN tickets t ON e.event_id = t.event_id:
   Joins the tickets table on the event_id, linking tickets to their respective events.

4. JOIN attendees a ON t.ticket_id = a.ticket_id:
   Joins the attendees table on the ticket_id, linking each attendee with the ticket they purchased.

5. GROUP BY e.name:
   Groups the results by event name to allow aggregation over each event's attendees and their departments.

6. HAVING COUNT(a.attendee_id) > 0:
   Ensures that only events with attendees are included.

7. ORDER BY department_diversity DESC, total_attendees DESC:
   Orders the results first by the number of unique departments attending (highest diversity first), and then by total number of attendees in case of ties in diversity.

Query 3:

Query: Most Active Attendees

This query identifies the most active attendees, calculating how many events each attendee has attended. It helps analyze the level of engagement among participants by sorting them based on the number of events attended.

SELECT a.first_name, 
       a.last_name, 
       COUNT(DISTINCT e.event_id) AS events_attended,
FROM attendees a
JOIN tickets t ON a.ticket_id = t.ticket_id
JOIN events e ON t.event_id = e.event_id
GROUP BY a.first_name, a.last_name
HAVING COUNT(DISTINCT e.event_id) > 1 -- Consider only attendees who attended more than 1 event
ORDER BY events_attended DESC;

Detailed Explanation:

1. SELECT a.first_name, a.last_name, COUNT(DISTINCT e.event_id) AS events_attended:
   Selects the attendee's first and last names and counts how many unique events they attended.

2. FROM attendees a:
   Specifies the attendees table as the main data source.

3. JOIN tickets t ON a.ticket_id = t.ticket_id:
   Joins the tickets table with attendees to link attendees to their tickets.

4. JOIN events e ON t.event_id = e.event_id:
   Joins the events table with tickets to link tickets to events.

5. GROUP BY a.first_name, a.last_name:
   Groups the results by attendee name to calculate event attendance for each individual.

6. HAVING COUNT(DISTINCT e.event_id) > 1:
   Filters the results to show only attendees who have attended more than one event.

7. ORDER BY events_attended DESC:
   Sorts the results by the number of events attended, showing the most active attendees first.

Functionality of Code Files

---

Python Files

1. app.py

• Purpose: The entry point for running the Flask application.
• Features:
  • Imports routes and Flask app configurations from config.py.
  • Starts the application on host=0.0.0.0 and port=8111 in debug mode.

2. config.py

• Purpose: Configures the Flask application and initializes the database connection.
• Features:
  • Loads environment variables using dotenv.
  • Sets up the QueryExecutor with the DATABASE_URL from .env.
  • Defines the static and template folders for Flask.

3. query_executor.py

• Purpose: Handles all PostgreSQL database interactions through a utility class.
• Features:
  • __init__: Establishes a connection to the database using SQLAlchemy.
  • _execute_fetch_query: Executes SQL queries that retrieve data.
  • _execute_query: Executes SQL queries that modify data, such as INSERT, UPDATE, or DELETE.

4. routes.py

• Purpose: Implements all HTTP routes for user interaction with the web application.
• Features:
  • Routing:
    • /: Redirects to the event browsing page.
    • /events: Displays a list of events with optional filtering.
    • /event/<int:event_id>: Shows details of a specific event and available tickets.
    • /book_ticket/<int:event_id>: Allows users to book tickets for an event.
    • /my_tickets: Displays tickets booked by the user.
    • /remove_ticket/<int:ticket_id>: Removes a booked ticket.
  • Database Operations:
    • Fetches events, tickets, and user bookings using QueryExecutor.
    • Inserts or removes records for ticket reservations.
  • Templates:
    • Connects dynamic data to templates like events.html, events_details.html, and my_tickets.html.

---

HTML Files (Templates)

1. base.html

• Purpose: Base layout template for the application.
• Features:
  • Includes a navigation bar linking to events and user tickets.
  • Uses Bootstrap for responsive design and contains a content block for dynamic pages.

2. book_ticket.html

• Purpose: Enables users to book tickets for specific events.
• Features:
  • Pre-filled form for ticket type.
  • Styled with Bootstrap for a clean and user-friendly experience.

3. booking.html

• Purpose: Provides a simple interface for booking a ticket.
• Features:
  • Basic layout with minimal styling, used for testing or functional purposes.

4. events_details.html

• Purpose: Displays detailed information about an event and its tickets.
• Features:
  • Lists event details such as name, description, date, and location.
  • Shows a table of tickets with options to book.

5. events.html

• Purpose: Displays a list of events and provides filtering options.
• Features:
  • Events are shown in a card-based layout.
  • Users can filter events by organizer, event type, or campus.

6. index.html

• Purpose: Displays all events in a tabular format.
• Features:
  • Presents event data such as ID, name, type, and description.
  • Minimal styling focused on data visibility.

7. my_tickets.html

• Purpose: Displays tickets booked by the user and allows for ticket removal.
• Features:
  • Shows ticket details like ID, name, type, and fee.
  • Includes a "Remove" button for each ticket.

8. prompt_email.html

• Purpose: Prompts users to enter their email before accessing booked tickets.
• Features:
  • Simple form to collect the user's email.
  • Redirects to the my_tickets page upon submission.

---

CSS Files

1. index.css

• Purpose: Styles tables on data-heavy pages such as my_tickets.html.
• Features:
  • Adds border-collapse, alternating row shading, and hover effects for tables.
  • Uses a professional green and white color scheme for headers and rows.

2. styles.css

• Purpose: Global styles for the web application.
• Features:
  • Styles the navigation bar, cards, and layout elements.
  • Provides a consistent aesthetic using Bootstrap and additional custom rules.

---

Connections Between Files

• Python Files:

  • app.py initializes the application.
  • routes.py handles HTTP requests and integrates with the database via query_executor.py.
  • config.py configures the app and provides a database connection.

• HTML Files:

  • Serve as the front-end interface for routes defined in routes.py.
  • Templates like events.html and events_details.html display dynamic data fetched from the database.

• CSS Files:

  • styles.css ensures consistent styling across pages.
  • index.css is specific to tabular data presentation.

---

Parts Implemented

1. Event Browsing:

   • Allows users to view and filter events by keyword, campus, or event type.

2. Event Details:

   • Displays comprehensive details about events and their available tickets.

3. Ticket Booking:

   • Enables users to reserve tickets by entering their email and selecting a ticket type.

4. Manage Bookings:

   • Users can view their booked tickets and remove them if needed.

---

Unimplemented Features

• The functionality for generating digital tickets after booking is not implemented due to the complexity of implementation. While users can reserve tickets through the booking interface, the generation and management of digital tickets will be extended and implemented in Project 2.

---

Interesting Web Pages

Event Details Page

• Combines data from multiple tables (events, tickets, organizers, locations) to display rich event details.
• Uses input from users to dynamically fetch and display ticket options.

My Tickets Page

• Provides an interface to view and manage ticket bookings.
• Allows seamless removal of tickets, with real-time updates to the database.

---

AI Tools Usage

• Tool Used: ChatGPT
• Purpose: AI tools were used for debugging Flask routing, understanding Python syntax, and enhancing UI design with CSS, strictly adhering to CS4111 policies. All SQL queries, SQLAlchemy implementation, and core logic formulation were independently written without AI assistance. Prompts focused on Flask setup, Python libraries, and CSS styling for better project efficiency and aesthetics.

Project 2

1) Full-Text Search Implementation

Overview

This section enhances the database schema by adding a text attribute with support for full-text search. The reviews table was introduced to store user reviews for events, utilizing PostgreSQL's full-text search capabilities with to_tsvector, to_tsquery, and GIN indexing for efficient queries.

---

Schema Changes

reviews Table

The reviews table was added to the schema with the following structure:

• review_id: A unique identifier for each review.
• event_id: A foreign key linking the review to the events table.
• reviewer_name: The name of the reviewer.
• review_text: A column storing document-style text for user reviews.

SQL Code:

CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    event_id INTEGER REFERENCES events(event_id),
    reviewer_name VARCHAR(100),
    review_text TEXT NOT NULL
);

Full-Text Search Setup

To enable full-text search on the review_text column, a GIN index was created. This optimizes search performance.

SQL Code:

CREATE INDEX review_text_idx ON reviews USING GIN (to_tsvector('english', review_text));

Data Population

Reviews Data

At least 10 meaningful reviews were added for each event in the events table. Below is an example of data added for the event Journalism Ethics Panel (event_id = 6):

Example Data:

INSERT INTO reviews (event_id, reviewer_name, review_text)
VALUES
(6, 'Alice Johnson', 'The Journalism Ethics Panel was thought-provoking and insightful. The panelists discussed critical topics affecting modern journalism.'),
(6, 'David Brown', 'An engaging session with real-world examples on maintaining ethical standards in journalism.'),
(6, 'Rachel Adams', 'The focus on ethical dilemmas faced by journalists was very informative and eye-opening.'),
(6, 'Tom Carter', 'Loved the discussion on balancing freedom of speech with responsible reporting.'),
(6, 'Julia Kim', 'The panelists shared valuable insights on dealing with misinformation in journalism.'),
(6, 'Liam Wilson', 'An excellent session that highlighted the importance of integrity in journalism.'),
(6, 'Sophia White', 'The Q&A session was interactive and clarified many of my doubts about ethical journalism.'),
(6, 'Emma Davis', 'The examples of case studies discussed during the session were very impactful.'),
(6, 'Noah Scott', 'I appreciated the diversity of opinions and approaches shared by the panelists.'),
(6, 'Mia Collins', 'The panelists’ depth of knowledge on ethics in journalism was impressive and inspiring.');

Full-Text Search Queries

The following queries demonstrate the use of full-text search on the review_text column:

Query 1: Search for Reviews Containing "journalism"

SELECT reviewer_name, review_text
FROM reviews
WHERE to_tsvector('english', review_text) @@ to_tsquery('journalism');

Verify All Reviews for event_id = 6

To view all reviews for the event:

SQL Query:

SELECT reviewer_name, review_text
FROM reviews
WHERE event_id = 6;

2) Adding an Array Attribute to the Schema

This section describes the addition of an array attribute to the events table in the schema. The new array attribute tags allows categorizing events using multiple labels.

Schema Modification

Adding the tags Array Attribute

We added a new column tags of type TEXT[] to the events table to store multiple tags for each event. This was done using the ALTER TABLE command:

ALTER TABLE events
ADD COLUMN tags TEXT[];

Data Population

Populating the tags Array

The tags array column was populated with meaningful values for all events. Below are some examples of how the data was added:

UPDATE events
SET tags = ARRAY['career', 'networking']
WHERE event_id = 1;

UPDATE events
SET tags = ARRAY['data science', 'symposium', 'conference']
WHERE event_id = 2;

UPDATE events
SET tags = ARRAY['climate', 'workshop', 'advocacy']
WHERE event_id = 3;

UPDATE events
SET tags = ARRAY['sports', 'competition', 'college']
WHERE event_id = 4;

UPDATE events
SET tags = ARRAY['tech', 'engineering', 'talk']
WHERE event_id = 5;

UPDATE events
SET tags = ARRAY['journalism', 'ethics', 'panel']
WHERE event_id = 6;

UPDATE events
SET tags = ARRAY['business', 'networking', 'mixer']
WHERE event_id = 7;

UPDATE events
SET tags = ARRAY['hackathon', 'engineering', 'competition']
WHERE event_id = 8;

UPDATE events
SET tags = ARRAY['art', 'exhibition', 'student']
WHERE event_id = 9;

UPDATE events
SET tags = ARRAY['coding', 'bootcamp', 'skills']
WHERE event_id = 10;

Queries to Demonstrate Functionality

Query 1: Find Events with a Specific Tag This query retrieves all events that contain a specific tag (e.g., "networking").

SELECT name, tags
FROM events
WHERE 'networking' = ANY(tags);

Output:

Query 2: Find Events with Multiple Tags

This query retrieves events that contain all specified tags (e.g., "networking" and "career").

SELECT name, tags
FROM events
WHERE tags @> ARRAY['networking', 'career'];

Output:

Verification

To verify the populated data, run the following query:

SELECT event_id, name, tags
FROM events;

Output:

3) Creating a SQL Trigger:

We add a boolean column to the tickets table that allows us to know if a ticket has an attendee already. Our trigger function runs on an insert in the attendees table. Whenever an attendee is added with a foreign key to our tickets, we can mark the corresponding entry in the tickets table as bought such that we know how many tickets are bought or still on the market in the tickets table.

ALTER TABLE tickets ADD COLUMN "bought" BOOLEAN DEFAULT FALSE

CREATE OR REPLACE FUNCTION update_tickets_table()
RETURNS TRIGGER AS $update_tickets$
   BEGIN
      UPDATE tickets set bought=TRUE
      WHERE tickets.ticket_id=NEW.ticket_id;
      RETURN NEW;
   END;
$update_tickets$ LANGUAGE plpgsql;

CREATE TRIGGER update_tickets_table 
AFTER INSERT ON attendees
FOR EACH ROW EXECUTE PROCEDURE update_tickets_table();

Sample Trigger Event:

For example if we run the query:

INSERT INTO attendees(attendee_id, uni_id, first_name, last_name, email, department, user_type, ticket_id)
VALUES (1, 'UNI1234', 'John', 'Doe', 'jd1234@columbia.edu', 'CS', 'Student', 5);

Then, first the sql engine will insert the row into the attendee table and then execute our trigger for every row inserted into the attendee table. The trigger will find the ticket with the ticket_id=5 and update the boolean column 'bought' and set it to True.

---

How to Run the Application

1. Place all source code files in a directory named proj1-3.
2. Ensure the PostgreSQL database is set up and connected.
3. Run the Flask server using python3 server.py.
4. Access the application via the provided URL.

---