def fetch_sname_product_cost_for_cost_gte_47():
table_name = relAlg.join("Supply", "sid", "Suppliers", "sid")
table_name_1 = relAlg.join(table_name, "pid", "Products", "pid")
table_name_2 = relAlg.select(table_name_1, "cost", ">=", "47")
relAlg.project(table_name_2, ["sname", "pname", "cost"])
return
# select_supplier_name_s23_withBTree()
# select_name_s23()
# cost_of_p20_supplied_by_kiddie()
# find_addresses_of_suppliers_who_supplied_p15()
# fetch_sname_product_cost_for_cost_gte_47()
def query_a():
tmp_result = select(SUPPLIERS_FILE, "sid", "=", "s23")
query_result = project(tmp_result, ["sname"])
print(query_result)
def query_e():
tmp_result = select(SUPPLY_FILE, "cost", ">=", 47.00)
tmp_result = join(tmp_result, "pid", PRODUCTS_FILE, "pid")
tmp_result = join(tmp_result, "sid", SUPPLIERS_FILE, "sid")
project(tmp_result, ["sname", "pname", "cost"])
def query_d():
tmp_result = select(SUPPLIERS_FILE, "sname", "=", "Kiddie")
tmp_result = join(tmp_result, "sid", SUPPLY_FILE, "sid")
tmp_result = select(tmp_result, "pid", "=", "p20")
project(tmp_result, ["cost"])
def query_c():
tmp_result = select(SUPPLY_FILE, "pid", "=", "p15")
tmp_result = join(SUPPLIERS_FILE, "sid", tmp_result, "sid")
project(tmp_result, ["address"])
queryHelpers.resetQueries()
# Run build(*) on the provided data set, and create two B+_trees with an order of 2, one on
# Suppliers.sid, and the other on Supply.pid
suppliersTree = buildTree.build("Suppliers", "sid", 2)
supplyTree = buildTree.build("Supply", "pid", 2)
# Run displayTree(*) to display the structures of the two B+_trees you create under item 6
# above. They should be displayed in files Suppliers_sid.txt and Supply_pid.txt, respectively,
# under folder treePic
display.displayTree(suppliersTree, "Suppliers_sid.txt")
display.displayTree(supplyTree, "Supply_pid.txt")
# question a
aStr = "Query a) Find the name for the supplier 's23' when a B+_tree exists on Suppliers.sid:\n"
queryHelpers.writeToQueryFile(aStr)
aSel = relAlg.select("Suppliers", "sid", "=", "s23")
aProj = relAlg.project(aSel, ["sname"])
display.displayTable(aProj, "queryResult.txt")
queryHelpers.writeToQueryFile("\n")
# question b
remove.removeTree("Suppliers", "sid")
bStr = "Query b) Find the name for the supplier 's23' without a B+ tree:\n"
queryHelpers.writeToQueryFile(bStr)
aSel2 = relAlg.select("Suppliers", "sid", "=", "s23")
aProj2 = relAlg.project(aSel, ["sname"])
display.displayTable(aProj2, "queryResult.txt")
queryHelpers.writeToQueryFile("\n")
# there are a few common queries we will perform here
suppliersJoinSupply = relAlg.join("Suppliers", "sid", "Supply", "sid")
joinAll = relAlg.join(suppliersJoinSupply, "pid", "Products", "pid")
def select_supplier_name_s23_withBTree():
table_name = relAlg.select("Suppliers", "sid", "=", "s23")
relAlg.project(table_name, ["sname"])
return
def cost_of_p20_supplied_by_kiddie():
table_name = relAlg.join("Supply", "sid", "Suppliers", "sid")
table_name_1 = relAlg.select(table_name, "sname", "=", "Kiddie")
table_name_2 = relAlg.select(table_name_1, "pid", "=", "p20")
relAlg.project(table_name_2, ["cost"])
return
def find_addresses_of_suppliers_who_supplied_p15():
table_name = relAlg.join("Supply", "sid", "Suppliers", "sid")
table_name_1 = relAlg.select(table_name, "pid", "=", "p15")
relAlg.project(table_name_1, ["address"])
return