Practical Assignment Outcomes

Learning Units $1$ to $7$ are covered in this assignment:

At the end of this assessment, students should be able to:

Compare the features of PL$/$SQL;

Compare the components of the PL$/$SQL environment;

Distinguish between the types of PL$/$SQL blocks;

Explain how to output messages using PL$/$SQL$.$

Construct PL$/$SQL Blocks;

Design PL$/$SQL Queries that use Variables, Construct a PL$/$SQL query with a Cursor Explain how to deal with exceptions

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

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 $101$ Summer $0834567891$ aclark@mcom.co$.$za

$11014$ Kevin Jones Lane

$55$ Mountain $0612547895$ kj@isat.co$.$za

$11015$ Lucy Williams way

$5$ Main rd $0827238521$ w@mcal.co$.$za

EMPLOYEE

EMPLOYEE$\_$ID FIRST$\_$NAME SURNAME CONTACT$\_$NUMBER ADDRESS EMAIL

emp$101$ Jeff Davis $087727752110$ main road land@isat.com

emp$102$ Kevin Marks $083737752218$ water

road

km@isat.com

emp$103$ Adanya Andrews $081711752321$ circle lane aa@isat.com

emp$104$ Adebayo Dryer $07972152441$ sea road aryer@isat.com

emp$105$ Xolani Samson $082712225512$ main road xosam@isat.com

DONATOR

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$ j@isat.com

$20114$ Kelly Ross $0826575650$ kross@gsat.com

$20115$ Abraham Clark $0797656430$ aclark@ymail.com

DONATION

DONATION ID DONATOR ID DONATION PRICE DONATION DATE

$711120111$ KIC Fridge R $5991$ May $2024$

$711220112$ Samsung $42$inch

LCD R $12993$ May $2024$

$711320113$ Sharp Microwave R $15993$ May $2024$

$7114201156$ Seat Dining room table R $7995$ May $2024$

$7115201,14$ Lazyboy Sofa R $11997$ May $2024$

$711620113$ VC Surround Sound System R $1799$ May $2024$

DELIVERY

DELIVERY ID DELIVERY NOTES DISPATCH$\_$DATE DELIVERY$\_$DATE

$511$ Double packaging requested $10$ May $202415$ May $2024$

$512$ Delivery to work address $12$ May $202415$ May $2024$

$513$ Signature required $12$ May $202417$ May $2024$

$514$ No notes $12$ May $202415$ May $2024$

$515$ Birthday present wrapping required $18$ May $202419$ May $2024$

$516$ Delivery to work address $20$ May $202425$ May $2024$

RETURNS

RETURN ID RETURN DATE REASON CUSTOMER ID DONATION$\_$ID EMPLOYEE$\_$ID

ret$00125$ May $2024$ Customer not satisfied with product $11011$

$7116$

emp$101$

ret$00225$ May $2024$ Product had broken section $110137114$ emp$103$

INVOICE

INVOICE NUM$|$ CUSTOMER ID INVOICE DATE EMPLOYEE ID DONATION ID DELIVERY ID

$81111101115$ May $2024$ emp$1037111511$

$81121101315$ May $2024$ emp$1017114512$

$81131101217$ May $2024$ emp$1017112513$

$81141101517$ May $2024$ emp$1027113514$

$81151101117$ May $2024$ emp$1027115515$

$81161101518$ May $2024$ emp$1037116516$

Refer to Appendix A for all Marking rubrics.

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.

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.$

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.