CS2102 Project: Part 1 Solution

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.
2 Application Description
Punchender is a crowd-funding system designed to compete with Kickstarter
(https://www.kickstarter.com/) and Indiegogo (https://www.indiegogo. com/). Its motto is to end the status quo with a punch.
If the users are backers, their home address must be recorded for the delivery of the final products. An address is composed of the street name, house number, zip code, and country name. On the other hand, if the users are creators, only their country of origin must be recorded for the purpose of taxation since every country have different tax codes.
But again, remember that the backers can only fund the same project exactly once. So, in the example above, if the backer already back the ”Metal” project on the ”Silver” reward level, the same user cannot back the ”Metal” project on the ”Gold” or ”Platinum” level.
3 Deliverables
Each team is to upload a pdf file named teamNNN.pdf 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.pdf). Submit your pdf file on Canvas assignment named ER + Schema (https://canvas.nus.edu.sg/courses/ 24662/assignments/7126). Only one file is to be submitted per group. If there are multiple submissions for a group, the submission with the lower mark will be chosen.
The submitted pdf file must be at most 8 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).
• ER data model of your application from https://www.comp.nus.edu. sg/~cs2102/Tools/ERD/.
• Justification for any non-trivial design decisions made.
– This includes but not limited to the use ISA hierarchies, aggregates, and ternary relationship set.
• List down up to 5 of the application’s constraints that are not captured by the proposed ER model.
• List down up to 5 of the application’s constraints that are not captured by the proposed relational schema.
In both ER diagram and relational schema, you should:
• capture as many of the application’s constraints as possible (as per specifications).
• not capture constraints not specified in the application’s constraints (as per specifications).
5 Grading
The following grading scheme will be used. The details of the grading scheme is hidden.
• (1 Mark) Validity of ER Diagram.
– The ER diagram should not contain invalid constructs.
– ER diagram should capture all the data needed by the application’s requirement.
– ER diagram should capture as many of the application’s requirements as possible.
– ER diagram should not add constraints not specified in the application’s requirements whenever possible.
• The relational schema should translate the ER diagram as much as possible when the requirements match with the application’s requirements. • The relational schema should enforce as many of the application’s constraints as possible.
– This includes reasonable real-world constraints.