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

{
  "operators": {
    "load": {
       "type": "TableLoad",
       "table": "WAREHOUSE",
       "filename": "warehouse.tbl"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": 0, "in": 0, "f": "W_ID", "vtype": 0, "value": <w_id>}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["W_NAME", "W_STREET_1", "W_STREET_2", "D_CITY", "D_STATE, D_ZIP"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

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

GetDistrictData

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

{
  "operators": {
    "load": {
       "type": "TableLoad",
       "table": "DISTRICT",
       "filename": "district.tbl"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": 0, "in": 0, "f": "D_W_ID", "vtype": 0, "value": <w_id>}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["D_NAME", "D_STREET_1", "D_STREET_2", "D_CITY", "D_STATE", "D_ZIP"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

UpdateAmount

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

{
  "operators": {
    "load": {
       "type": "TableLoad",
       "table": "DISTRICT",
       "filename": "district.tbl"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": 6 /*and*/},
         {"type": 0, "in": 0, "f": "C_W_ID", "vtype": 0, "value": <c_w_id>},
         {"type": 0, "in": 0, "f": "C_D_ID", "vtype": 0, "value": <c_d_id>}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["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"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

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

TPC