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 The tables created by projectTables.sql script reside in the charitys Oracle database server as well as the list of the volunteers. The server is on host named db8.fast.sheridanc.on.ca. Each group is assigned their own database. The credentials to use are: Server Name: db8.fast.sheridanc.on.ca Port: 22 – SSH, 1521 – Oracle Linux username: userxx Password: Or4cl3S2020 Oracle instance name: Uxx.world Each instance has the following usernames/passwords: sys/[same as ssh] system/[same as ssh] prc/PetRescue vl1/PetRescue vl2/PetRescue vl3/PetRescue vl4/PetRescue dml/DataMart Where the linux username and Oracle instance contain xx – replace it with your group #. Users SYS and SYSTEM are for database administration. PRC is to store the addresses and donors. VL1-VL4 are the accounts for the volunteer leaders. DML is to be used to store that datamart Donation List The list of donors is stored in a comma separated file. A sample named donorsList.csv shows a list that the volunteer group lead sends to the main office. It illustrates the schema and contains only two entries. DONOT USE EITHER OF THOSE ENTRIES IN YOUR SUBMISSION. Each member of the group (presumably a volunteer with a distinct volunteer no) should provide a separate list for their “area” with at least 15 entries. As a result each group will have three (or four) donor lists that contain a minimum of 15 additional entries in the donors list. Make sure that there are both valid and invalid entries. The invalid entries will be rejected and sent back to the respective volunteer coordinator. In total there must be at LEAST 60 distinct names/addresses on at LEAST 3 separate lists. 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 Refresh the address table in the group’s 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. Note that the: ids of some addresses will be used as foreign key in donation table so your solution should accommodate this fact. the master table does not contain postal codes so your solution should accommodate this fact Create a process that loads the donations list to the central donations table: In this task you will load each donor 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 (e.g. NOT NULL columns). You must generate a csv file corresponding to the volunteer group leaders with the records that were rejected in their area. Crea…
Looking for solution of this Assignment?
WHY CHOOSE US?
We deliver quality original papers |
Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected |
Free revisions |
We offer our clients multiple free revisions just to ensure you get what you want. |
Discounted prices |
All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount |
100% originality |
We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI |
On-time delivery |
We will deliver your paper on time even on short notice or short deadline, overnight essay or even an urgent essay |