NGO/CBO Database Project
The purpose of this project is to build a database to store and retrieve basic information on NGOs/CBOs collected through a questionnaire given in the Appendix.
Database Specifications
In view of the questionnaire, and based on prudent consideration of the database specifications, the following high-level description of the database may be assumed:
- The characteristics of an NGO entity include a unique ID, an native-language name, a Latin Name, an organization type, the year the NGO was founded, and the home country in which it exists. The home country of an NGO need not be the country in which it operates.
- An NGO operates and has branches in its home country. It may operate and/or have branches in other countries. An NGO branch is identified with a unique ID. Each NGO has at least one branch, namely, its main office, whose head is the head of the NGO. Branch characteristics include the branch type, address, telephone and fax numbers, and the head of the branch.
- NGOs engage in a number of activities that are categorized into larger activity classes. Each activity belongs to only a single class. NGOs may also have different scopes, i.e., operate in different geographical areas, as well as work with various population groups. NGOs may themselves be classified based on their activities, geographical coverage (or scope,) and beneficiary population groups. The characteristics of each scope and beneficiary group include a unique ID, name, and a succinct description.
- NGOs carry out projects in their areas of activity aimed at their beneficiary groups in their locales. The characteristics of a project include a unique ID, title, starting date, and duration. Local, regional, and/or international donors of various types (individuals, organizations, charities, governments, etc.) provide funds for the projects. Funds encompass, but are not limited to cash, in-kind matching, technical assistance and training, and equipment, each of monetary value.
- It is possible that certain projects of regional or global interest and/or scope are carried out by two or more collaborating NGOs of the same or different home countries.
- Other NGO activities include, often as a component of some project, training, participation in workshops and conferences, and regular or intermittent publication of books, reports, newsletters, and/or advisories.
Assignment [I]:
Consider the NGO/CBO questionnaire enclosed. It is suggested that you explore some of the NGO/CBO sites on the Web to verify the basic facts and collect additional information. Write down the additional information and/or requirements you gathered. Give the URLs for the sites contacted.
1- Draw an appropriate E/R diagram that satisfies the basic and additional facts, indicating, weak and subclass entity sets, whenever exist, multiplicity of relationships, and the key, or keys, for each entity set. Distinguish between the parts of the E/R diagram pertaining to the given and extra basic facts.
2- Translate the E/R diagram to relational database schemas.
3- Specify a number of essential functional dependencies for each relation. Identify possible keys, whenever exist, and the primary key and foreign keys for each relation.
4- Examine the database relations for BCNF violations. Decompose the relations that are not in BCNF into collections of relations that are in BCNF as necessary.
[II]: Generate relational algebra [RA] expressions and expression trees to answer the following queries. Use standard notation and appropriate RA terminology. You may need to modify the database schema to answer the questions below.
1- Identify all NGOs actively working in the Health, Education, and Gender group, together with the number of projects and total amount of funds received by each NGO.
2- Repeat the query in [1] for the Youth Development, Child Protection, and Nutrition group.
3- Identify the donors together with the total number of projects funded and amount of funds targeting the groups of activities in [1] and [2].
4- Repeat the queries in [1] and [2] to identify NGOs actively working in rural areas and slums together with the number of projects and total amount of funds received by each NGO.
5- Identify the donors together with the total number of projects funded and amount of funds targeting rural areas and slums.
[III] Create SQL code to answer the queries in [II].
Attachment:- Data Base Project Assignment.rar