Description
1 Objective
The objective of this team project is for you to apply what you have learned in class to design and develop a database application using PostgreSQL. The project is to be done in teams of four students. The project consists of the following four tasks:
1. Design an ER data model for the application. Your ER model should capture as many of the application’s requirements as possible.
2. Translate your ER data model into a relational database schema. Your relational schema should capture as many of the application’s constraints as possible.
3. Implement triggers as required by the specification as listed in the Application Triggers.
4. Implement an SQL or PL/pgSQL functions/procedures for each of the functionalities listed in Application Functionalities.
2 Files
1. DDL.sql: The file containing the schema used for part 2 of the project.
2. Proc.sql: The file containing the template for the procedures and functions.
3 Recap: Part 1
3.1 ER Diagram
3.2 Schema
CREATE TABLE Employees ( id INT PRIMARY KEY, name TEXT NOT NULL, salary NUMERIC NOT NULL CHECK (salary > 0)
);
1
2
3
4
5
CREATE TABLE Users (
email TEXT PRIMARY KEY,
name TEXT NOT NULL,
cc1 TEXT NOT NULL,
cc2
); TEXT CHECK (cc1 <> cc2)
1
2
3
4
5
6
CREATE TABLE Verifies ( email TEXT PRIMARY KEY
REFERENCES Users(email),
); Employees(id),
1
2
3
4
5
6
CREATE TABLE Backers ( email TEXT PRIMARY KEY
REFERENCES Users(email) ON UPDATE CASCADE,
street TEXT NOT NULL,
num TEXT NOT NULL,
zip TEXT NOT NULL,
country TEXT
); NOT NULL
1
2
3
4
5
6
7
8
CREATE TABLE Creators ( email TEXT PRIMARY KEY
REFERENCES Users(email) ON country TEXT NOT NULL
); UPDATE CASCADE,
1
2
3
4
5
CREATE TABLE ProjectTypes (
name TEXT PRIMARY KEY,
id
); INT NOT NULL REFERENCES Employees(id)
1
2
3
4
CREATE TABLE Projects (
id INT PRIMARY KEY, email TEXT NOT NULL
REFERENCES Creators(email) ON UPDATE CASCADE, ptype TEXT NOT NULL
);
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Updates ( time TIMESTAMP,
id INT REFERENCES Projects(id)
ON UPDATE CASCADE, — ON DELETE CASCADE (optional) message TEXT NOT NULL, PRIMARY KEY (time, id)
);
1
2
3
4
5
6
7
CREATE TABLE Rewards ( name TEXT,
id INT REFERENCES Projects(id)
ON UPDATE CASCADE, — ON DELETE CASCADE (optional) min_amt NUMERIC NOT NULL CHECK (min_amt > 0), PRIMARY KEY (name, id)
);
1
2
3
4
5
6
7
CREATE TABLE Backs ( email TEXT REFERENCES Backers(email), name TEXT NOT NULL,
id INT,
— status will be derived via queries instead
PRIMARY KEY (email, id),
FOREIGN KEY (name, id) REFERENCES Rewards(name, id) );
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Refunds ( email TEXT, pid INT, eid INT NOT NULL
REFERENCES Employees(id),
accepted BOOLEAN NOT NULL, PRIMARY KEY (email, pid),
FOREIGN KEY (email, pid)
REFERENCES Backs(email, id)
);
1
2
3
4
5
6
7
8
9
10
11
Please study the schema above and note the difference from the ER diagram in the guide. The schema is available in the file DDL.sql.
• An additional check on Users that cc2 must be different from cc1 (or
NULL).
• The simplification of data types:
– INT instead of SERIAL to avoid complications from running numbers.
∗ This means that any INSERT will specify the id directly.
– TEXT instead of VARCHAR.
4 Terminologies
Before we start, we will define a few useful terminology:
• Refund Status: There are four possible refund status that can be computed (note, it is a computed attribute in our ER diagram):
– Not Requested: A refund status is not requested if there is no request for the refund (i.e., Backs.request is NULL).
– Requested: A refund status is requested if there is a request for the refund BUT there is no approval and/or rejection yet (i.e., Backs.request is not NULL and no entry in Refunds table).
– Accepted: A refund status is accepted if it is a requested status and has been accepted (i.e., Refunds.accepted is True).
– Rejected: A refund status is rejected if it is a requested status and has been rejected (i.e., Refunds.accepted is False).
• Money: We have a few terminology for money:
– Fund: A backer has funded their money if the backer backs a project and the project is successful. Additionally, the backer have not requested a refund.
• Success Metric: We can rank successful projects by their success metric which is based on:
– the ratio of the amount of money funded to the project and the funding goal (i.e., funded / goal) with larger values indicate a more successful project. The funded money is regardless of whether there are any refund or not.
– lastly, if there are still any ties, we rank by the project id with smaller id indicate a more successful project.
• Popularity Metric: We can rank projects (successful or not) by their current popularity. This metric is based on how fast a project reach their funding goal counted as the number of days since the project is created. The smaller the better.
– We rank by the popularity metric above.
– If there are any ties, we rank by the project id in ascending order.
5 Application Triggers
Our relational schema could not enforce the following constraints listed below. For each constraints below, implement a trigger to enforce them. Unless specified, you only need to consider INSERT triggers (i.e., triggers that are activated by insertions). You do NOT need to consider DELETE or UPDATE (the only exception is the trigger requirement number 6, which requires an UPDATE trigger). For each constraint, you are allowed to use multiple triggers to enforce it. Please be careful about the order of triggers to ensure that the constraints are properly enforced.
1. Users must be backers, creators, or both. In other words, there must not be any users that are neither backers nor creators.
• Consider the procedure add user, how would the procedure affect this trigger?
2. Backers must pledge an amount greater than or equal to the minimum amount for the reward level.
3. Projects must have at least one reward level. In other words, there must not be any projects without any reward level.
• Consider the procedure add project, how would the procedure affect this trigger?
6 Application Functionalities
The routines (i.e., functions/procedures) application functionalities must be implemented with the same name specified in this document, following the same order of input parameters (including type), returning the exact output parameter type (if any), and using exactly the language specified (i.e., sql or plpgsql only).
org/docs/14/functions-datetime.html).
6.1 Procedures
The following routines in this section do not have return values, and should be implemented as PostgreSQL procedures https://www.postgresql.org/docs/ 14/sql-createprocedure.html. The routines must be successful on valid inputs even in the presence of the triggers above.
CREATE OR REPLACE PROCEDURE add_user( email TEXT, name TEXT, cc1 TEXT, cc2 TEXT, street TEXT, num TEXT, zip TEXT, country TEXT, kind TEXT
) AS $$
— add declaration here BEGIN
— your code here
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
• email is the user email.
• name is the user name.
• cc1 is the user first credit card number.
• num is the user address house number (only used for backers).
• zip is the user address zip code (only used for backers).
• country is the user address country code (can be used for both backers and creators).
• kind is one of the following:
– ’BACKER’ if the user is only a backer.
– ’CREATOR’ if the user is only a creator.
– ’BOTH’ if the user is both a backer and a creator.
CREATE OR REPLACE PROCEDURE add_project(
id INT, email TEXT, ptype TEXT,
goal NUMERIC, names TEXT[],
amounts NUMERIC[]
) AS $$
— add declaration here BEGIN
— your code here
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
• id is the project id.
• email is the creator email.
• ptype is the project type.
• name is the project name.
• goal is the funding goal.
• DATATYPE[] refers to an array of DATATYPE.
– For each index idx, the element at index idx corresponds to the same reward level.
– names is an array of reward names.
– amounts is an array of minimum amount to back the project through the reward.
) AS $$
— add declaration here BEGIN
— your code here
END;
$$ LANGUAGE plpgsql; auto_reject(
1
2
3
4
5
6
7
8
• eid is the employee id.
6.2 Functions
The following routines in this section have return values, and should be implemented as PostgreSQL function https://www.postgresql.org/docs/14/ sql-createfunction.html. The routines must be successful on valid inputs even in the presence of the triggers above.
1. Write a function to find the email and name of all superbackers for a given month and year.
) RETURNS TABLE(email TEXT, name TEXT) AS $$
— add declaration here BEGIN
— your code here
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
• The result should be ordered in ascending order of email.
• The outputs are:
– email: The backer email.
– name: The backer name.
) RETURNS TABLE(id INT, name TEXT, email TEXT, amount NUMERIC) AS $$
SELECT 1, ”, ”, 0.0; — replace this
$$ LANGUAGE sql;
1
2
3
4
5
6
• n is the top N.
• ptype is the project type.
• The result should be ordered in descending order of success metric.
• The outputs are:
– id: The project id.
– name: The project name.
– email: The creator email.
– amount: The total amount funded (regardless of whether refund request is created or not).
) RETURNS TABLE(id INT, name TEXT, email TEXT, days INT) AS $$
— your code here
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
• n is the top N.
• ptype is the project type.
• The result should be ordered in descending order of popularity metric.
• The outputs are:
– id: The project id.
– name: The project name.
– email: The creator email.
– days: The number of days to reach funding
7 Deliverables
Each team is to upload an ZIP file named teamNNN.zip where NNN is the three digit team number according to your project group number. You should add leading zeroes to your team number (e.g., team005.zip). The ZIP file should contain the following two files:
1. Report.pdf: Project report in PDF format.
2. Proc.sql: The triggers and routines of your implementation (for simplicity, this is the same name as the template file).
Submit your pdf file on Canvas assignment named ER + Schema (https:
//canvas.nus.edu.sg/courses/24662/assignments/11992). Only one file is to be submitted per group. If there are multiple submissions for a group, the latest submission will be chosen. If the later submission is late, late penalty will apply.
The project report must be at most 20 pages with font size of at least 12 point and consists of the following:
• Project team number & names of team members (on the first page).
• A listing of the Project (Part 2) responsibilities of each team member.
• For each trigger:
– Provide the name of the trigger and the trigger function.
– Explain the basic idea of the trigger and the trigger function implementation.
• For each routine:
– Provide the name of other routines used.
– Explain the basic idea of the routine implementation. • For each additional routines (if any):
– Explain the basic idea of the routine implementation. • A summary of any difficulties encountered and lessons learned from the project.
9 FAQ
1. ”Why are we not allowed to make any changes to the schema provided or the routine names and parameters given?”
10 tl;dr
10.1 Triggers
1. Enforce the constraint Users === {Creators, Backers}.
2. Enforce the constraint that (backers’ plegde amount) ≥ (reward level minimum amount).
3. Enforce the constraint Projects === Has (i.e., every project has at least one reward level).
6. Enforce the constraint that refund can only be made for successful projects.
10.2 Procedures
2. Write a procedure to add project with all the corresponding reward levels.
10.3 Function
1. Find all superbackers sorted by email which satisfies one or both of the following
• Condition #1: satisfy all
– Backed ≥ 5 successful projects in the last 30 days
– Backed ≥ 3 project types in the last 30 days
• Condition #2: satisfy all
– Funded ≥$1,500 on successful projects in the last 30 days
– 0 refund request in the last 30 days
• (total money funded) / (project goal) descending.
• (project id) ascending.
• (# days for funding goal to be reached) descending.
• (project id) ascending.
