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 charitys 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 groups 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 groups 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
Applied Sciences
Architecture and Design
Biology
Business & Finance
Chemistry
Computer Science
Geography
Geology
Education
Engineering
English
Environmental science
Spanish
Government
History
Human Resource Management
Information Systems
Law
Literature
Mathematics
Nursing
Physics
Political Science
Psychology
Reading
Science
Social Science
Home
Homework Answers
Blog
Archive
Tags
Reviews
Contact
twitterfacebook
Copyright © 2022 SweetStudy.comSWEETSTUDY.COM YOUR HOMEWORK ANSWERS
chat0
Home.Literature.
Help.
Log in / Sign up
Can You Help me with this assignment
profile
Tajinder
GroupProject-DataBase.docx
Home>Computer Science homework help>Can You Help me with this assignment
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 charitys 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 groups 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 groups 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
Applied Sciences
Architecture and Design
Biology
Business & Finance
Chemistry
Computer Science
Geography
Geology
Education
Engineering
English
Environmental science
Spanish
Government
History
Human Resource Management
Information Systems
Law
Literature
Mathematics
Nursing
Physics
Political Science
Psychology
Reading
Science
Social Science
Home
Homework Answers
Blog
Archive
Tags
Reviews
Contact
twitterfacebook
Copyright © 2022 SweetStudy.com
