DBAS$32100$ group project Template

The project for this course is worth $25\%$ of your mark and

Introduction

In this project you will assist the fictional organization Pet Rescue Charity with managing their donations data. The charity organizes an annual donation drive across the city. The city is divided into donation areas. Every donation area consists of approximately $6$ postal codes. A group of volunteers and one volunteer leader is assigned to each area. The volunteers go door$-$to$-$door in their designated areas and collect donations from the residents $($the donors$).$ The donations can be in the form of cash, cheque or credit card.

At the end of every week each volunteer leader will fill in a list showing the donations collected in their area and send it to the charity main office. The staff in the main office will then run a process that you will design to load the list to a Central Donation Repository. The process will reject non$-$valid entries. You will also create a data mart which will have data loaded from the Central Donation Repository. This data mart will be used for reports and to perform analysis based on interviews that have been conducted with Pet Rescue Charity.

Data Sources

Central donations repository

The Central Donations Repository is the main database where all the information on donations is stored it consists of Donation, Address, and volunteer tables

Your group will create the Central Donation Repository tables on your Project Oracle database instance. Below are the table creation statements. Every group will be provided the connection information for their project database. you will connect to the database as user name $$prc$$ in order to create these tables

$1.$ Donation table

Every row in this table is a donation made by a donor. Below is the script that creates donation table

$2.$ Address Table

This is an address lookup table. It has a list of all the known addresses

$3.$ Volunteer Table

This table has information on the volunteers. Consider yourself to be the volunteers so you need to insert your information in this table

Your Oracle instance has the following schemas already created for your group to use:

$$ PRC $-$ is used to store donations, donor addresses and volunteer information in the Central Donation Repository you will use PCR username in order to create the above three tables.

$$ Datamart $-$ is used to store the data mart star schema you will use Datamart user in order to create the star schema.

Users $-$ the last two users $($marked with $*)$ must be created by you as task $6$ describes later:

$$ Users SYS and SYSTEM are for database administration.

$$ PRC is the owner of the tables $($will create the tables$)$

$$ Datamart $-$ Is the owner of the datamart tables $($will create the tables$).$

Donation List

This is the list that the volunteer group lead sends to the main office. It is a comma separated text file of the following format. The below image is just a sample of how the file should be structured. You need to create at least $3$ different csv files with $20$ entries each to test your process.

Addresses Table

The address table that the charity maintains in their oracle database is not updated and it often gets out of date. However, there is another department in the organization has an address table that they regularly update and keep current. The table is in SQLServer database and below is the connection information to the server

Make sure that you have setup Sheridan VPN$.$ You can not access the server unless you are connected to Sheridan vpn$.$

Server Name: dbr$.$fast.sheridanc.on$.$ca

Port: $1433$

Database name: Integration

Schema Name: dbo

User Name: DataIntegrator

Password: Sher$1$dan

Tasks

Task $1$ write scripts or document a process that refreshes $($copy new or changed addresses$)$ the address table in the oracle database with the addresses in the master table $($the table in SQL server database$).$ You will need to transform some data types and generate a sequential id for the address. Also note that the ids of some addresses will be used as foreign key in donation table your solution should accommodate this fact. It does not have to be one scrip. You can make this task as a sequence of separate processes

Task $2$ You need to create a process that loads the donations list to the central donations table: In this task you will load the data from the csv file into the Donation table, You need to make sure that only the donations with valid addresses are inserted into the table. Donation records with erroneous addresses or incorrect volunteer ids must be rejected. Also make sure to reject the donations that have nulls for the mandatory columns in your database $($Not null columns$).$ Your process must generate csv files to the volunteer group leaders with the records that were rejected in their area.

You need to test your data by loading donations of $60$ distinct donor names and addresses across at least $3$ sepa