数据库定义实验指导模板

TPCH数据库模式关系图PARTKEYPARTKEYNAMEMFGRBRANDTYPESIZECONTAINERCOMMENTRETAILPRICEPARTKEYSUPPKEYAVAILQTYSUPPLYCOSTCOMMENTSUPPKEYNAMEADDRESSNATIONKEYPHONEACCTBALCOMMENTORDERKEYPARTKEYSUPPKEYLINENUMBERRETURNFLAGLINESTATUSSHIPDATECOMMITDATERECEIPTDATESHIPINSTRUCTSHIPMODECOMMENTCUSTKEYORDERSTATUSTOTALPRICEORDERDATEORDER-PRIORITYSHIP-PRIORITYCLERKCOMMENTCUSTKEYNAMEADDRESSPHONEACCTBALMKTSEGMENTCOMMENTPART(P_)PARTSUPP(PS_)LINEITEM(L_)ORDERS(O_)CUSTOMER(C_)SUPPLIER(S_)ORDERKEYNATIONKEYEXTENDEDPRICEDISCOUNTTAXQUANTITYNATIONKEYNAMEREGIONKEYNATION(N_)COMMENTREGIONKEYNAMECOMMENTREGION(R_)TPCH数据库模式详细说明PART零件表字段名数据类型要求说明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供应商表字段名数据类型要求说明S_SUPPKEYinteger供应商号S_NAMEfixedtext,size25名称S_ADDRESSvariabletext,size40地址S_NATIONKEYinteger国家代码S_PHONEfixedtext,size15电话S_ACCTBALreal账户余额S_COMMENTvariabletext,size101备注PrimaryKey:S_SUPPKEYPARTSUPP供应商-零件表字段名数据类型要求说明PS_PARTKEYintegerForeignKeytoP_PARTKEYPS_SUPPKEYintegerForeignKeytoS_SUPPKEYPS_AVAILQTYinteger可用数量PS_SUPPLYCOSTreal供应价格PS_COMMENTvariabletext,size199备注PrimaryKey:PS_PARTKEY,PS_SUPPKEY CUSTOMER客户字段名数据类型要求说明C_CUSTKEYintegerSF*150,000arepopulatedC_NAMEvariabletext,size25顾客姓名C_ADDRESSvariabletext,size40地址C_NATIONKEYintegerForeignKeytoN_NATIONKEYC_PHONEfixedtext,size15电话C_ACCTBALreal账户余额C_MKTSEGMENTfixedtext,size10市场区块（如中国区、南美区、北美区等）C_COMMENTvariabletext,size117备注PrimaryKey:C_CUSTKEY ORDERS订单表字段名数据类型要求说明O_ORDERKEYintegerO_CUSTKEYintegerForeignKeytoC_CUSTKEYO_ORDERSTATUSfixedtext,size1订单状态O_TOTALPRICEreal总金额O_ORDERDATEDate订单日期O_ORDERPRIORITYfixedtext,size15订单优先级O_CLERKfixedtext,size15记账员O_SHIPPRIORITYInteger装运优先级O_COMMENTvariabletext,size79备注PrimaryKey:O_ORDERKEYLINEITEM订单明细字段名数据类型要求说明L_ORDERKEYintegerForeignKeytoO_ORDERKEYL_PARTKEYintegerForeignkeytoP_PARTKEY,firstpartofthecompoundForeignKeyto(PS_PARTKEY,PS_SUPPKEY)withL_SUPPKEYL_SUPPKEYintegerForeignkeytoS_SUPPKEY,secondpartofthecompoundForeignKeyto(PS_PARTKEY,PS_SUPPKEY)withL_PARTKEYL_LINENUMBERinteger订单明细号L_QUANTITYdecimal数量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明细状态("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国家表字段名数据类型要求说明N_NATIONKEYinteger25nationsarepopulatedN_NAMEfixedtext,size25N_REGIONKEYintegerForeignKeytoR_REGIONKEYN_COMMENTvariabletext,size152Prima