Data Base Management

DBAS32100 group project Template

Walid Belal

March 2020

Introduction

In this project you will assist a pet rescue charity with managing their donations related data. The charity organizes an annual donation drive. The city is divided to donations areas (every donation area is approximately 6 postal codes) and assign a group of volunteers to every area.

The volunteers will go door to door in their designated areas in order to collect donations from the residents. They can collect cash, checks, or credit card payments. At the end of every week the volunteering group leaders will fill in a list with the donation record that were collected in their area, and send it to the charity main office. The staff in the main office will load the list to a central table after rejecting non-valid entries and then use the data to perform analysis.

Data Sources

Central donations repository

These table resides in the charity’s oracle database server.

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

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 two entries is just an example of the format . you can enter as many entries as necessary to test yourcode

Master Addresses Table

The address table that the charity maintains 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

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

Port: 1433

Database name: Integration

Schema Name: dbo

User Name: DataIntegrator

Password: Sher1dan

Tasks

Task 1 – Refresh the address table in the oracle database with the addresses in the master table. 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.

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 file into the central repository (Oracle tables). You need to make sure that only the donations with valid addresses are inserted into the table. Donation records with erroneous addresses must be rejected. Also make sure to reject the donations that have nulls for the mandatory columns in your database (Not null columns). You must generate csv files to the volunteer group leaders with the records that were rejected in their area.

Task 3 – Create a star schema for the donations. The grain of the schema should be the combination of day, address, and volunteer

Task 4 - Create a process to Load the data to the star schema from the central donation repository

Task- 5 Create views that shows

· The average and sum of the donation by day, month, Year

· The average and sum of the donations by address, Postal code

· The average and sum of the donations by Volunteer and volunteer group leader

Task 6 Basic Security

· Create a user named DMLUser and give the user permissions to implement all DML on address, donation, and volunteer tables

· Create a user named Dashboard and give the user read permissions on the views

Deliverables

Proposal (Group work)

In the past few weeks you learned advanced SQL statements, PL/SQL and Talend Data Integrator. You can use a combination of the three tools to accomplish tasks 1,2, and 4. In order to start the project you need to research these technologies and decide what combination of technologies you will use for every task. You are required to write a one or two pages proposal that must include the below information:

· Who is responsible for each task? The work should be assigned in a balanced manner between the group members.

· Proposed due date for every task.

· Dependencies between the tasks.

· How do you plan to complete tasks 1,2 and 4. in this part you need to describe the process that you intend to follow in order to compete the tasks.

You will be provided with a template for the proposal. The proposal will be submitted on Friday March 13. I will review them and will either approve them or will ask you to modify the proposal. The whole project will be graded as 0 if you do not submit the proposal or if your proposal was not approved

Report (Group work)

Create a Microsoft word report documenting the processes. The report must include 6 sections. Each of the section will be describing one of the 6 tasks. You must describe the processes in details and include all the code that you used to create the process.in case you used talend then you need to include screenshots of all the configurations that you used. I will interview the group if I find that the report is not clearly describing the project.

Individual work

You must write a one page summary that describes your design for the project in your own words and details your involvement in the project. The summary must have the below 4 sections

· A brief description of your group’s solution to the project, what are the differences between your final solution and the proposal if any, and why did not you partially follow your proposal

· Your contribution to the project. You will get the full grade on this item if your contribution is proportionate to the other group members and if it is well described. You will get a bonus grade if your contribution is greater that the other members in the group

· Difficulties that you faced in the project

· Suggest improvements to the pet rescue operation data collection process. The suggestions must be related to the scope of this project.

The professor can ask to interview the group if some of the details are not clear about the project implementation. The result of the interview will be considered in the grade

Evaluation

Item		Criteria	%	Delivery Date
Proposal		-Who is responsible for each task. The work should be assigned in a balanced manner between the group members (2%) -Proposed due date for every task. (1%) -The dependencies between the tasks.(2%) -How do you plan to complete tasks 1,2 and 4. in this part you need to describe the process that you intend to follow in order to compete the tasks.(10 %)	15
	Format		5
	Task 1	-Reload addresses in address table (5%) -Maintain referential integrity in donations table (5%)	10
	Task 2	-Load the donation list (5%) -Reject incorrect data (5%) -Generate reports of rejected data (5%)	15
	Task 3	-ER Diagram for the Start Schema (4%) -Create tables statements for the schema (4%) -Create indexes for the schema (2%)	10
	Task 4	-Create ETL process to load data to the schema (10%)	10
	Task 5	Create views that shows: -The average and sum of the donation by day, month, Year (2%) -The average and sum of the donations by address, Postal code (2%) -The average and sum of the donations by Volunteer and volunteer group leader (1%)	5
	Task 6	Basic Security · Create a user named DMLUser and give the user permissions to implement all DML on address, donation, and volunteer tables (3%) · Create a user named Dashboard and give the user read permissions on the views (3%) · Audit DMLS on the Star schema tables (2%) · Create a SQL statement that filters the audit table to show any DML activities on the tables (2%)	10
Individual summary		· A brief description of your group’s solution to the project, what are the differences between your final solution and the proposal if any, and why did not you partially follow your proposal (5%) · Your contribution to the project (10%) · Difficulties that you faced in the project (2.5%) · Suggest improvements to the pet rescue operation data collection process. The suggestions must be related to the scope of this project. (2.5%)	20
Total			100