DATABASES

You are required to design and implement a relational database to help the local community center better serve the residents in your neighborhood.

Database Specifications:

In your preliminary analysis, you have determined the following basic facts about the community center and the services it provides to the residents in the neighborhood: - The community center serves multiple areas which fall into different zip codes.

- The community center provides collaborative family based care services. An immediate family is defined to include one or both parents and any of their children living at the same or different address.

- Each family resides at an address or addresses. An address includes an apartment or floor number (or none), street number, street name, borough or city name, and zip code.

- The community center engages in activities related to the services it provides. Possible services are: [a] Health services [b] Education and learning [c] Gender related services [d] Water and sanitation [e] Child protection [f] Nutrition [g] Others.

- Residents volunteer to help in the activities which target various groups of residents. Possible groups are: [a] The community at large/collective benefit [b] Women [c] Youth [d] Children [e] Senior citizens [f] Minorities [g] Underprivileged/disadvantaged [h] Disabled and handicapped [i] Others.

-The community center carries out projects related to the services it provides, which aim to benefit some of the groups in its area.

- A project is specific to one or more activities. The characteristics of a project include a unique ID, title, starting date, and duration.

- Local and/or regional donors of various types (individuals, organizations, charities, government, etc.) provide funds for the projects. Funds encompass, but are not limited to cash, training, and/or equipment, each of monetary value.

Assignment [I]

1) Explore at least five sites of family based community services on the Web to verify the basic facts and collect additional information. Provide in the introduction of the project report the URLs of the sites explored and the additional information and/or requirements you gathered.

2) 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.

3) Translate the E/R diagram in [2] to relational database schemas.

4) 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.

5) Examine the database relations for BCNF and 3NF violations. Decompose the relations as necessary to resolve any anomalies that may be found the data.

II) Create SQL expressions to answer the following queries. You may need to modify the database schema to answer the queries given.

1) Identify all members of a given family. Display the family members names, addresses, and relationships.

2) Identify all family members of a given family who volunteered to a certain activity.

3) Identify all family members of a given family who benefited from a certain project

4) Identify the total amount of money donated by the members of a given family and the projects the money was donated to.

5) In view of the information in the database, establish a criterion based on which the community center could identify residents for assistance with one of its services/activities/projects.

6) Any other queries you deem important.

III) Submit a written report that includes:

1) Introduction that presents an overview of the problem and solution approach.

2) The complete E/R diagram and schema of the relational database fully specifying the given requirements and any other requirements gathered. Identify all keys, foreign keys, functional dependencies of the database relations.

3) SQL code that creates the tables structure.

4) SQL code that loads the data. Data must be representative of fair size.

5) SQL code that answer the given queries.

6) Sample outputs for your SQL code.