$$

Case Study

Study the following case study and answer the questions that are based thereon.

CHARITY $4$ AFRICA is a government initiative to allow individuals to donate their unwanted household items to a local charity store. All proceeds of the sale of the donations are provided to various charity organisations around the country. CHARITY $4$ AFRICA has grown from strength to strength due to excellent service and a wide variety of donations and a delivery service of large items, such as fridges and televisions. At present, all the data is stored in a flat file system. The charity at present is under pressure due to the increase in employees, customers, donators, donations, deliveries, returns and invoices and require a database system to manage their data.

You have been contracted to perform the design and implementation of a database for CHARITY $4$ AFRICA. The charity store opened their doors to the public five years ago and require a database to manage all areas of their business.

The data in flat files has been provided:

CUSTOMER

$\backslash$table$[[$CUSTOMER$\_$ID$,$FIRST$\_$NAME,SURNAME,ADDRESS,CONTACT$\_$NUMBER,EMAIL$],[11011,$Jack,Smith,$18$ Water Rd$,0877277521,$jsmith@ isat.com$],[11012,$Pat,Hendricks,$22$ Water Rd$,0863257857,$ph@ mcom.co$.$za$],[11013,$Andre,Clark,$\backslash$table$[[101$ Summer$],[$Lane$]],0834567891,$aclark@ mcom.co$.$za$],[11014,$Kevin,Jones,$\backslash$table$[[55$ Mountain$],[$way$]],0612547895,$kj@ isat.co$.$za$],[11015,$Lucy,Williams,$5$ Main rd$,0827238521,$Iw@ mcal.co$.$za$]]$

EMPLOYEE

$\backslash$table$[[$EMPLOYEE$\_$ID$,$FIRST$\_$NAME,SURNAME,CONTACT$\_$NUMBER,ADDRESS,EMAIL$],[$emp$101,$Jeff,Davis,$0877277521,10$ main road,jand@ isat.com$],[$emp$102,$Kevin,Marks,$0837377522,\backslash$table$[[18$ water$],[$road$]],$km@ isat.com$],[$emp$103,$Adanya,Andrews,$0817117523,21$ circle lane,aa@ isat.com$],[$emp$104,$Adebayo,Dryer,$0797215244,1$ sea road,aryer@ isat.com$],[$emp$105,$Xolani,Samson,$0827122255,12$ main road,xosam@ isat.com$]]$

DONATOR

$\backslash$table$[[$DONATOR$\_$ID$,$FIRST$\_$NAME,SURNAME,CONTACT$\_$NUMBER,EMAIL$],[20111,$Jeff,Watson,$0827172250,$jwatson@ymail.com$],[20112,$Stephen,Jones,$0837865670,$joness@ymail.com$],[20113,$James,Joe,$0878978650,$jj@ isat.com$],[20114,$Kelly,Ross,$0826575650,$kross@gsat.com$],[20115,$Abraham,Clark,$0797656430,$aclark@ymail.com$]]$

DONATION

$\backslash$table$[[$DONATION$\_$ID$,$DONATOR$\_$ID$,$DONATION,PRICE,DONATION$\_$DATE$],[7111,20111,$KIC Fridge,R $599,1$ May $2024],[7112,20112,\backslash$table$[[$Samsung $42$ inch$],[$LCD$]],$R $1299,3$ May $2024],[7113,20113,$Sharp Microwave,R $1599,3$ May $2024],[7114,20115,\backslash$table$[[6$ Seat Dining room$],[$table$]],$R $799,5$ May $2024],[7115,20114,$Lazyboy Sofa,R $1199,7$ May $2024],[7116,20113,\backslash$table$[[$JVC Surround$],[$Sound System$]],$R $179,9$ May $2024]]$

DELIVERY

$\backslash$table$[[$DELIVERY$\_$ID$,$DELIVERY$\_$NOTES,DISPATCH$\_$DATE,DELIVERY$\_$DATE$],[511,$Double packaging requested,$10$ May $2024,15$ May $2024],[512,$Delivery to work address,$12$ May $2024,15$ May $2024],[513,$Signature required,$12$ May $2024,17$ May $2024],[514,$No notes,$12$ May $2024,15$ May $2024],[515,$Birthday present wrapping required,$18$ May $2024,19$ May $2024],[516,$Delivery to work address,$20$ May $2024,25$ May $2024]]$

RETURNS

$\backslash$table$[[$RETURN$\_$ID$,$RETURN$\_$DATE,REASON,CUSTOMER$\_$ID$,$DONATION$\_$ID$,$EMPLOYEE$\_$ID$],[$ret$001,25$ May $2024,\backslash$table$[[$Customer not$],[$satisfied with$],[$product$]],11011,7116,$emp$101],[$ret$002,25$ May $2024,\backslash$table$[[$Product had$],[$broken section$]],11013,7114,$emp$103]]$

INVOICE

$\backslash$table$[[$INVOICE$\_$NUM,CUSTOMER$\_$ID$,$INVOICE$\_$DATE,EMPLOYEE$\_$ID$,$DONATION$\_$ID$,$DELIVERY$\_$ID$],[8111,11011,15$ May $2024,$emp$103,7111,511],[8112,11013,15$ May $2024,$emp$101,7114,512],[8113,11012,17$ May $2024,$emp$101,7112,513],[8114,11015,17$ May $2024,$emp$102,7113,514],[8115,11011,17$ May $2024,$emp$102,7115,515],[8116,11015,18$ May $2024,$emp$103,7116,516]]$

Question $1$

$($Marks: $10)$

You will need to create the above tables to complete this Assignment.

Please create the tables and populate them using SQL Developer or SQL$*$Plus$.$

$\backslash$table$[[$Requirement$,$Mark$],[$Tables created successfully,$5],[$Tables populated successfully,$5],[$TOTAL$,10]]$

Question $2$

$($Marks: $5)$

CHARITY $4$ AFRICA requires a report containing the combined customer name, employee id$,$ delivery notes, donation purchased and the invoice number. Create a SQL query to generate the required report. In your query, only display the results that have any invoice date before $18$ May $2024.$

Sample Results

$\backslash$table$[[8$ CUSTONER,$10$ EMPLOVEE$\_$D$,$DELNEEr$\_$MOTES,$[0$ DONation,$],[1$ Jack, Smith,emp $103,$Double packaging reqested,kIC Fridge,$\frac{811115}{M}R\frac{r}{24}$

Question $3$

$($Marks: $10)$

Management of CHARITY $4$ AFRICA would like to add a new table called Funding that at present will not have any referential integrity. The organisation would like an automatically generated unique id every time a record is inserted into the new table. The attributes the table must contain for now are funding$\_$id$,$ funder, and funding amount.

Create the new table and implement a solution to automatically generate the unique ids with every new insert. In your solution provide an example of the insert statement.

Question $4$

$($Marks: $10)$

Create a PL$/$SQL query to display the combined customer name, donation purchased, donation price and the reason that the d