Description
Problem Statement
The enviromental-activist organization named “Green-not-Greed” (GnG) has grown to a point
where some computerized record keeping would help with day-to-day operations. They would
use online services for such record keeping, but are worried about security and surveillance
given recent revelations on how some security services gather data on ordinary citizens via data
on transmitted over the Internet. They would rather build a home-grown system to help them
with their work.
The main purpose for which the group exists is to raise public awareness on emerging environmental issues that have relatively local impact (i.e., “local” here would mean a region up
to a size of, say, “Vancouver Island”). Although GnG keeps in touch with other, more globallyoriented environmental groups, and some GnG members belong to such groups, GnG’s focus is
on “as-local-as-possible” issues.
1
Attempts to raise public awareness and effect change are via “campaigns”. These are person-onthe-street activities where volunteers are scheduled to be at street corners and public squares
in towns or cities. There the volunteers use posters, placards, and other material to capture the
attention of citizens in the public space and share with them the issues central to the campaign.
These campaigns can last anywhere from two weeks to two months, and may have events taking
place simultaneously in the same,or different, cities and towns and villages. There is a little bit
of fundraising that also occurs during campaigns, but GnG is mostly funded by several large
donors. Campaigns do have some costs associated with them. At present GnG operates out of a
small downtown office, the rent of which is paid by some GnG supporters.
GnG also has a website. Although the computer system solving the problem described here will
not be linked to the website, the group believes it makes sense that something be done to keep
track of when and how campaigns (and the phases of each campaign) will be, and have been,
pushed out to the website.
There are a few salaried employees (on very low salaries!) and most of the organization is based
on the work of its volunteers. There are two tiers of volunteers: those who have participated
in more than three publicity campaigns and then the others who have participated in two of
fewer. There are also members who are not volunteers but who are interested in supporting the
activities of GnG.
In essence the computer system is needed to help GnG keep track of campaigns, who is working
on them, when activities need to happen, and the way funds actually flow in and out.
For this problem you must also use your own understanding of the way such groups work in
order to “fill in the gaps” of the descriptions. The assumptions you make must be rooted in the
real world and therefore when stating your assumptions please provide some justification. Feel
free to look at how other similar groups are organized as you think through problems in this
assignment.
Deliverable A: ER Diagram
Prepare an ER diagram modelling the entities, relationships and constraints in this problem.
Feel free to use whatever tool you wish for preparing the diagram; however, hand-drawn diagrams are acceptable.
Please do this step first! You may be tempted to develop the SQL schemas first and then prepare the ER diagrams, but this order of work may yield poor results and possibly a much lower
assignment grade.
2
Deliverable B: A set of relations
After having prepared and reflected on your ER diagram (and you may have drawn several versions as you come to grips with the problem described), convert these into a set of relations.
If your diagram includes ISA hierarchies, then choose what you believe to be the appropriate
conversion approach (i.e., ER, O-O, or Nulls).
Deliverable C: SQL table creation commands
Prepare and implement the SQL statements (in PostgreSQL) needed to not only construct the
table schemas corresponding to your relations, but also populating them with dummy data.
(Use insert SQL commands to add tuples to tables.) Ensure any key and foreign-key constraints
are listed and handled appropriately. You do not need to use attribute or table constraints for
this assignment.
Deliverable D: At least ten SQL queries using your tables
You are to pose at least ten questions you would want to ask of the data in the tables. Provide at
least one SQL query per question that finds the answer to that question.
Amongst all of the SQL must appear the important query constructs we have discussed in class:
subqueries; subqueries using scalar values; set operations; use of exists, any, or all; join operations; grouping and aggregation; etc.
Each of your queries must be implemented as an SQL view where the view name corresponds
to the number of the question (above) you are answering.
What to submit in class
(a) A hardcopy of your ER diagram and relations (no more then four pages).
(b) A hardcopy of your numbered questions.
Ensure these items are stapled together with a cover sheet.
What to submit via conneX
(a) The SQL file produced by the pg_dump command for your database. This file contains all
of the SQL commands needed to reconstruct your database with all tables, table data, and
views (i.e., your SQL queries). This file may be used during your evaluation demo. The name
of the file must be a3.sql.
3
(b) If you so wish, electronic versions of the ER diagram and numbered questions.
Evaluation
As there are many possible correct solutions to the problem, evaluation will be done via a demonstration of work in front of a member of the CSC 370 teaching team. Information on demos (i.e.,
where, when, how to sign up) will be distributed shortly before the assignment due date.
The marking scheme below will be used:
• “A” grade: An exceptional submission demonstrating creativity and initiative. The data
modelling is thorough and shows insight, the database schema is well prepared, and required SQL features are intelligently (and clearly) used in assignment’s queries.
• “B” grade: A submission completing the requirements of the assignment. The data modelling is thorough, database schema has been prepared, and required SQL features are
used in the assignment’s queries.
• “C” grade: A submission completing most of the requirements of the assignment. There
may be problems with one: data modelling; database scheme; SQL queries.
• “D” grade: A serious attempt at completing the requirements of the assignment. There
are many problems with the submitted work.
• “F” grade: Either no submission is given, or submission represents very little work.
4