數(shù)據(jù)庫定義實驗指導模板

TPCH數(shù)據(jù)庫模式關系圖PARTKEYPARTKEYNAMEMFGRBRANDTYPESIZECONTAINERCOMMENTRETAILPRICEPARTKEYSUPPKEYAVAILQTYSUPPLYCOSTCOMMENTSUPPKEYNAMEADDRESSNATIONKEYPHONEACCTBALCOMMENTORDERKEYPARTKEYSUPPKEYLINENUMBERRETURNFLAGLINESTATUSSHIPDATECOMMITDATERECEIPTDATESHIPINSTRUCTSHIPMODECOMMENTCUSTKEYORDERSTATUSTOTALPRICEORDERDATEORDER-PRIORITYSHIP-PRIORITYCLERKCOMMENTCUSTKEYNAMEADDRESSPHONEACCTBALMKTSEGMENTCOMMENTPART(P_)PARTSUPP(PS_)LINEITEM(L_)ORDERS(O_)CUSTOMER(C_)SUPPLIER(S_)ORDERKEYNATIONKEYEXTENDEDPRICEDISCOUNTTAXQUANTITYNATIONKEYNAMEREGIONKEYNATION(N_)COMMENTREGIONKEYNAMECOMMENTREGION(R_)TPCH數(shù)據(jù)庫模式詳細說明PART零件表字段名數(shù)據(jù)類型要求說明P_PARTKEYinteger零件號P_NAMEvariabletext,size55零件名稱P_MFGRfixedtext,size25制造商名稱P_BRANDfixedtext,size10品牌P_TYPEvariabletext,size25類型P_SIZEinteger尺寸P_CONTAINERfixedtext,size10包裝P_RETAILPRICEreal零售價格P_COMMENTvariabletext,size23備注PrimaryKey:P_PARTKEYSUPPLIER供應商表字段名數(shù)據(jù)類型要求說明S_SUPPKEYinteger供應商號S_NAMEfixedtext,size25名稱S_ADDRESSvariabletext,size40地址S_NATIONKEYinteger國家代碼S_PHONEfixedtext,size15電話S_ACCTBALreal賬戶余額S_COMMENTvariabletext,size101備注PrimaryKey:S_SUPPKEYPARTSUPP供應商-零件表字段名數(shù)據(jù)類型要求說明PS_PARTKEYintegerForeignKeytoP_PARTKEYPS_SUPPKEYintegerForeignKeytoS_SUPPKEYPS_AVAILQTYinteger可用數(shù)量PS_SUPPLYCOSTreal供應價格PS_COMMENTvariabletext,size199備注PrimaryKey:PS_PARTKEY,PS_SUPPKEY CUSTOMER客戶字段名數(shù)據(jù)類型要求說明C_CUSTKEYintegerSF*150,000arepopulatedC_NAMEvariabletext,size25顧客姓名C_ADDRESSvariabletext,size40地址C_NATIONKEYintegerForeignKeytoN_NATIONKEYC_PHONEfixedtext,size15電話C_ACCTBALreal賬戶余額C_MKTSEGMENTfixedtext,size10市場區(qū)塊（如中國區(qū)、南美區(qū)、北美區(qū)等）C_COMMENTvariabletext,size117備注PrimaryKey:C_CUSTKEY ORDERS訂單表字段名數(shù)據(jù)類型要求說明O_ORDERKEYintegerO_CUSTKEYintegerForeignKeytoC_CUSTKEYO_ORDERSTATUSfixedtext,size1訂單狀態(tài)O_TOTALPRICEreal總金額O_ORDERDATEDate訂單日期O_ORDERPRIORITYfixedtext,size15訂單優(yōu)先級O_CLERKfixedtext,size15記賬員O_SHIPPRIORITYInteger裝運優(yōu)先級O_COMMENTvariabletext,size79備注PrimaryKey:O_ORDERKEYLINEITEM訂單明細字段名數(shù)據(jù)類型要求說明L_ORDERKEYintegerForeignKeytoO_ORDERKEYL_PARTKEYintegerForeignkeytoP_PARTKEY,firstpartofthecompoundForeignKeyto(PS_PARTKEY,PS_SUPPKEY)withL_SUPPKEYL_SUPPKEYintegerForeignkeytoS_SUPPKEY,secondpartofthecompoundForeignKeyto(PS_PARTKEY,PS_SUPPKEY)withL_PARTKEYL_LINENUMBERinteger訂單明細號L_QUANTITYdecimal數(shù)量L_EXTENDEDPRICEreal金額(L_EXTENDEDPRICE=L_QUANTITY*P_RETAILPRICE)L_DISCOUNTreal折扣[0.00..0.10].L_TAXreal稅率[0.00-0.08]L_RETURNFLAGfixedtext,size1退貨標志(IfL_RECEIPTDATE<=CURRENTDATEtheneither"R"or"A"isselectedatrandomelse"N"isselected)L_LINESTATUSfixedtext,size1明細狀態(tài)("O"ifL_SHIPDATE>CURRENTDATE"F"otherwise)L_SHIPDATEdate裝運日期L_COMMITDATEdate委托日期L_RECEIPTDATEdate簽收日期L_SHIPINSTRUCTfixedtext,size25裝運說明（如deliverinperson）L_SHIPMODEfixedtext,size10裝運方式（如空運，陸運，海運）L_COMMENTvariabletextsize44備注PrimaryKey:L_ORDERKEY,L_LINENUMBERNATION國家表字段名數(shù)據(jù)類型要求說明N_NATIONKEYinteger25nationsarepopulatedN_NAMEfixedtext,size25N_REGIONKEYintegerForeignKeytoR_REGIONKEYN_COMMENTvariabletext,size152Prima