Question

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.

Answer 1

import java.util.Scanner;
/* Class BTNode */
class BTNode
{
BTNode left, right;
int information;
/* Constructor */
open BTNode()
{
left = invalid;
right = invalid;
information = 0;
}
/* Constructor */
open BTNode(int n)
{
left = invalid;
right = invalid;
information = n;
}
/* Function to set left hub */
open void setLeft(BTNode n)
{
left = n;
}
/* Function to set right hub */
open void setRight(BTNode n)
{
right = n;
}
/* Function to get left hub */
open BTNode getLeft()
{
return left;
}
/* Function to get right hub */
open BTNode getRight()
{
return right;
}
/* Function to set information to hub */
open void setData(int d)
{
information = d;
}
/* Function to get information from hub */
open int getData()
{
return information;
}
}
/* Class BT */
class BT
{
private BTNode root;
/* Constructor */
open BT()
{
root = invalid;
}
/* Function to check if tree is unfilled */
open boolean isEmpty()
{
return root == invalid;
}
/* Functions to embed information */
open void insert(int information)
{
root = insert(root, information);
}
/* Function to embed information recursively */
private BTNode insert(BTNode hub, int information)
{
in the event that (hub == invalid)
hub = new BTNode(data);
else
{
in the event that (node.getRight() == invalid)
node.right = insert(node.right, information);
else
node.left = insert(node.left, information);
}
return hub;
}
/* Function to check number of hubs */
open int countNodes()
{
return countNodes(root);
}
/* Function to check number of hubs recursively */
private int countNodes(BTNode r)
{
in the event that (r == invalid)
return 0;
else
{
int l = 1;
l += countNodes(r.getLeft());
l += countNodes(r.getRight());
return l;
}
}
/* Function to scan for a component */
open boolean search(int val)
{
return search(root, val);
}
/* Function to scan for a component recursively */
private boolean search(BTNode r, int val)
{
on the off chance that (r.getData() == val)
return genuine;
on the off chance that (r.getLeft() != invalid)
on the off chance that (search(r.getLeft(), val))
return genuine;
on the off chance that (r.getRight() != invalid)
on the off chance that (search(r.getRight(), val))
return genuine;
return false;
}
/* Function for inorder traversal */
open void inorder()
{
inorder(root);
}
private void inorder(BTNode r)
{
on the off chance that (r != invalid)
{
inorder(r.getLeft());
System.out.print(r.getData() +" ");
inorder(r.getRight());
}
}
/* Function for preorder traversal */
open void preorder()
{
preorder(root);
}
private void preorder(BTNode r)
{
on the off chance that (r != invalid)
{
System.out.print(r.getData() +" ");
preorder(r.getLeft());
preorder(r.getRight());
}
}
/* Function for postorder traversal */
open void postorder()
{
postorder(root);
}
private void postorder(BTNode r)
{
in the event that (r != invalid)
{
postorder(r.getLeft());
postorder(r.getRight());
System.out.print(r.getData() +" ");
}
}
}
/* Class BinaryTree */
open class BinaryTree
{
open static void main(String[] args)
{
Scanner filter = new Scanner(System.in);
/* Creating object of BT */
Bt = new BT();
/* Perform tree operations */
System.out.println("Binary Tree Test\n");
singe ch;
do
{
System.out.println("\nBinary Tree Operations\n");
System.out.println("1. embed ");
System.out.println("2. seek");
System.out.println("3. number hubs");
System.out.println("4. check discharge");
int decision = scan.nextInt();
switch (decision)
{
case 1 :
System.out.println("Enter whole number component to embed");
bt.insert( scan.nextInt() );
break;
case 2 :
System.out.println("Enter whole number component to seek");
System.out.println("Search result : "+ bt.search( scan.nextInt() ));
break;
case 3 :
System.out.println("Nodes = "+ bt.countNodes());
break;
case 4 :
System.out.println("Empty status = "+ bt.isEmpty());
break;
default :
System.out.println("Wrong Entry \n ");
break;
}
/* Display tree */
System.out.print("\nPost arrange : ");
bt.postorder();
System.out.print("\nPre arrange : ");
bt.preorder();
System.out.print("\nIn arrange : ");
bt.inorder();
System.out.println("\n\nDo you need to proceed with (Type y or n) \n");
ch = scan.next().charAt(0);
} while (ch == 'Y'|| ch == 'y');
}
}