Payment transaction

Input

• w_id = const.
• d_id = rand [1..10]
• customer select 60% via last name and 40% via id
  • id: c_last = c_id = NURand(1023, 1, 3000)
  • last name: ?generation?
• warehouse: 85% "home", 15% "remote"
  • home: c_w_id = w_id, c_d_id = d_id
  • remote: c_w_id = random other, c_d_id = Rand(1, 10)

???what is SUT???

???how to get h_amount???

Start Transaction

bla

select W_NAME, W_STREET_1, W_STREET_2, D_CITY, D_STATE, D_ZIP
from WAREHOUSE
where W_ID = w_id

TPC

update WAREHOUSE
set W_YTD = W_YTD + h_amount
where W_ID = w_id

bla

select D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP
from DISTRICT
where D_W_ID = d_id

update DISTRICT
set D_YTD = D_YTD + h_amount
where D_W_ID = d_id

GetCustomerData (customer select via c_id)

select C_FIRST, C_MIDDLE, C_LAST, C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM, C_DISCOUNT, C_BALANCE
from CUSTOMER
where C_W_ID = c_w_id
  and C_D_ID = c_d_id
  and C_ID = c_id

GetCutomerData (customer select via last name)

select C_ID, C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM, C_DISCOUNT, C_BALANCE
from CUSTOMER
where C_W_ID = c_w_id
  and C_D_ID = c_d_id
sort by C_FIRST ASC

n = number of selected rows
balance_row = ceil(n / 2)

UpdateCustomerData

???c_id usable as key???

update CUSTOMER
set C_BALANCE = C_BALANCE + h_amount,
    C_YTD_PAYMENT = C_YTD_PAYMENT + 1
where C_ID = c_id

=========5========== (c_credit = "BC")

???sql howto: insert somethong into string field???

=========6=========

Client:

h_data = w_name + "    " + d_name;

insert into HISTORY (H_C_ID, H_C_D_ID, H_C_W_ID, H_D_ID, H_W_ID)
values (c_id, c_d_id, c_w_id, d_id, w_id)

????What about H_DATA????

Commit

Output