please use sql to answer the following question an erd has been provide
Task 1: Creating a Logon Procedure
The home page of the Brewbean’s Web site has an option for members to log on with their IDs
and passwords. Develop a procedure namedMEMBER_CK_SPthat accepts the user ID and password
as inputs, checks whether they match avalid logon, and returns the member first name+lastname and cookie
value.
The name should be returned as a single text string containing the first and last name.
The head developer wants the number of parameters minimized so that the same
parameter is used to accept the password and return the name value.(passwd Paramater as IN OUT use passwd as INPUT firstname||lastname as OUTPUT)
Also, if the user doesn’t enter a valid username and password, return the valueINVALIDin a parameter named
p_check.
Test the procedure using a valid logon first, with the usernamerat55and passwordkile. Then try it with an invalid logon by changing the username torat and kile999password.
Hint:
This procedure will have 4 parameters
p_userid IN
p_passwd IN OUT
p_cookie OUT
p_check OUT
You will select and return two columns/ information from bb_shopper table in this procedure body.
BEGIN
Hint for SELECT statement is like below
Firstname ||lastname , cookie INTO two local variables (p_passwd , p_cookie)
from bb_shoppper
WHERE username=userid and password=passwd
Assuming your SELECT statement will find record matching username and password and returns a records then right after SELECT statement you should have a statement like below
p_check := ‘VALID USER’;
Otherwise add Exception to give ‘Invalid User’ message
EXCEPTION
WHEN NO_DATA_FOUND THEN
END program;
Once you are done with developing procedure you can test like below
Test Cases:
Valid username scenario
DECLARE
lv_pass_txt VARCHAR2(30):=’kile’;
lv_cook_num bb_shopper.cookie%TYPE;
lv_chk_txt VARCHAR2(7);
BEGIN
member_ck_sp('rat55',lv_pass_txt,lv_cook_num,lv_chk_txt);
DBMS_OUTPUT.PUT_LINE(lv_pass_txt); -- this will return Kenny Ratman
DBMS_OUTPUT.PUT_LINE(lv_cook_num); -- this will return 0
DBMS_OUTPUT.PUT_LINE(lv_chk_txt); -- this will return VALID USER
END;
Invalid username scenario (username : rat passwd :kile
DECLARE
lv_pass_txt VARCHAR2(30):=’wrong password’;
lv_cook_num bb_shopper.cookie%TYPE;
lv_chk_txt VARCHAR2(7);
BEGIN
member_ck_sp('rat',lv_pass_txt,lv_cook_num,lv_chk_txt);
DBMS_OUTPUT.PUT_LINE(lv_pass_txt); -- returns. wrong password (because first name and last name is not selected
DBMS_OUTPUT.PUT_LINE(lv_cook_num); --NULL result will come
DBMS_OUTPUT.PUT_LINE(lv_chk_txt); -- Invalid User
END;
Extracted text: bb_shopper bb_department bb_basket bb_basketstatus bb_product bb_basketitem bb_tax bb_shipping bb_productoption bb_productoptiondetail bb_productoptioncategory FIGURE 1-7 The Brewbean's database ERD