eBay数据库设计与实现

挑战2eBay数据库设计和实现挑战数据：提供十年前从eBay网站下载的大量拍卖数据，数据格式为XML。挑战要求：要求设计一个合理的关系数据模式(数据库)，同时编写C++程序将XML格式的数据，基于设计的数据模式转换成SQLServer/PostgreSQL能够导入的文本格式，并在SQLServer/PostgreSQL上建立设计的数据模式，导入转换得到的数据，并通过SQL查询操作进行测试和验证。挑战步骤：PartA设计关系数据模式根据xml说明文档和实际的eBay拍卖数据，设计一个合理的关系数据模式，回答以下5个步骤。列出你设计的所有关系，为每个关系指定主码，此阶段可以不指定数据类型。users(userid,rating,location,country);item(itemid,name,currently,Buy_Price,First_Bid,Number_of_Bids,Started,Ends,seller,Description);category(category);item_category(itemid,category);item_bids(itemid,time,bidder,amount)列出你设计的关系中的所有函数依赖，也可以回答“没有”。users：userid—rating,location,country;item：itemid—name,currently,Buy_Price,First_Bid,Number_of_Bids,Started,Ends,seller,Description;item_bids：itemid,time—bidder,amount)你设计的关系是否属于Boyce-CoddNormalForm(BCNF)范式？如果不是，请重新设计，或者解释为什么采用非BCNF较好。属于BC范式列出你设计的关系中的所有多值依赖，也可以回答“没有”。item_category：itemid——category;你设计的关系是否属于第4范式？如果不是，请重新设计，或者解释为什么采用非4-NF较好。属于第4范式PartB编写数据转换程序考虑到对XML代码处理的效率，使用c#进行编程，使用其自带的System.Xml进行XML文件的读取。XML文件存放在程序Debug文件夹下。根据设计的关系数据库模型，同步生成4个文本文件(item.txt、item_bid.txt、item_ca.txt、user.txt)。数据处理过程中，考虑对元数据中可能影响数据导入的因素进行处理，包括日期、

单引号、逗号等，使用“《幻〉》”作为列分隔符。由于SQLServer的数据导入导出工具的文本数据转换出错，所以直接利用C#对生成的四个文本文件进行再处理，生成包含SQL插入语句的文本文件(iitem.txt、iitem_bid.txt、iitem_ca.txt、iuser.txt)。文件均生成在程序Debug文件夹下。PartC将转换生成的数据导入SQLServer1.在SQLServer上新建数据库(eBay)，创建设计的关系数据模式，即createtable,并指定主码还外码，执行create.sqloU3rridvarchar〔LOO)ratingU3rridvarchar〔LOO)ratingititLocationirarcharI.LQO)countryirarchar1.100)usehe!En□aeiidvarchai(ICO)ratingimlocationvhchaz(103)countTyrarchar(ICO)itemi+cmidirarchar(100)n-diievarchar(100)currentlyMnay0l4T_PlLC5moneyFiist_BidroneyHimberofBidsintStarteddatetime2Endsda-tE-tineSsellervarchar(100)>Descriptionvarchar(lUUl)itEn_bids:l土可tidwarchar〔lOO)5k.，biddervarchar(ICO)<fk2>Hrr傅dmlNiri必'口aiiowtnDneyit&nL_categoryt&mLlcmidvarchar1100)

catrsoTyvarchar(100)注意：XML拍卖数据中的Description数据长度较长，部分超过8000字符，应将类型设计为varchar(MAX)，保证数据在插入过程中不因截断而出错。数据库初始大小为100Mo

create.sql-admin-PC\...\admiri(52))日ere己仁己tatleasezsijseridvsrcaaz100)primarykey,ratingint,Iccaticnvarchai100)fcauntryvaxchax10*3)ncreatetatleitem(itemidvarcnai100)printarykey,namevarchar100carre^tlynuonsYf5ay_Fricenaney^First_Sidnancy,WaidSEr□:£五业Mt.*|椅二T消息命令已成功完成。2.表创建后，利用程序生成的文本文件，进行数据导入导入。在数据导入的过程中，执行单个insert文件时间较长，且提示“资源池internal'没有足够的系统内存来运行此查询。”将部分数据导入分批进行，依次导入insert-1.sql~insert_8.sql，可以保证所有数据导入成功。

xnaer"mccinsertintclusezrlucexnaerr-niceinsertintzemseriluceinaerr-niceinsertintoiaseEiluceinaerr-niceinsertintclusezrlucexnaerr-niceinsert1.TLCCinsert_Lsql-admini-PCVA3^^^(S2>Ju.serg'ceirLval^ea(■r'Jlabmla*1Q35Z■itsadryMeaz■,BUSJkr);uaeTstenivaliaesi.^dollface^ar221..PLONGISLANDUSAP^USA.!)jnsexsLeELvaluesf■goldsoastvldeo1.29L5^'LosJkngeleSnCZLF'OSA11?u.sers'cenLval^ea(■■r352^rHll^ac.£eeNi■,r「J5A'j;u3eT3temval-uc3i.pfall-santiQuej1r952^!，MIlwaakrrHipT^USA');LLSersr-eff.valuesf・lLiDlDiDdLyl：3已..xnaer"mccinsertintclusezrlucexnaerr-niceinsertintzemseriluceinaerr-niceinsertintoiaseEiluceinaerr-niceinsertintclusezrlucexnaerr-niceinsert1.TLCCuaerstemvaliarai.^0^00012311r106zpNoTthernIreland*T11UnitedKingdosr/J;LLsarsLafiLvaluesi■wicc2idenmaxi'.37srM：-jnleh',,GwtiLmny11]；u.serg'ceELvaluea(■nrwr?!'.."92rTKP^'USil'f=uaeTstcmvaliaesi9eIIi^I1tISr"NewYmk，'。吨LLsersLeELvalciesi■L'rfial231r13s1yisso.irlTheStiowMe5匚&匚巳'『'口SS'卜；u.sers'cenLvalues(■inaley'f^22^T■sja\rU5Jk'];u—UT—eiri11匕rkioiid'「■£「Lurid.•H"-i:J消息*1行懿府'；!行受墨响J|*1行懿府'；!行受墨响J|：1行受村p-naerr-niceMerainsertintcitemp-naerr-niceMerainsertintcitemmseriluce1esminaerr-niceiteninserti.ntciteraiaseEiULCC1can)inaerr-niceiteninsertinceiteml^sexrnice1cexninaerr-niceiteninsertintcitemmseriluce1esminaerr-niceiten.insertintcitem寸玉丽执■行.admin-PCi&CJLEXPMSS(10.0SPl)admin-PQadmin(52]eBay00:00:&5。行行受神行受影荷吟萱有巳煎:执行『admin-PCi&QLEXPRESS(10.0SPl)admin-RQadmin052]eBay-30:00:14。行insert_2.sql-admira-PC\_.\admin(52)JTallies(1IDS337459.5''cMriscni：nerradlrc|£^igen_frosty81Eld.lnffr,$30.QQrHlTLLrfs四1安51/1[1三33卯45丁JPMBOYANDGIRLEXCLUSIVEEVENTPIECEDEp^33.99.NULLr699.99values(ilD^34OJ~67,'PreciousEionentaGirl5coveMiniLea3ecB»$4BOOrCJULL,.a00.1^aluieat110^3402735'.'Eea-czlHrotterTailorstGlojcestersnakErsr.$3.SQrNULL,S3.5values(*10^3^95702!.'PreciousMomentsFiff-ANGELOFMERCY-NURSE"f$28.00PNULL^S9B9vaolteB(11D3506302\'ZiREJlWsICLE5sumcarclies"LOVELETTERS"new/nr^$6.00.NULLf$6uTallies(I1D^3562O35,,'TcnrjnZliezCE1Q2srS15.□□,yULL,$15»QQrd■2DD1-12-Q4G3；Dg：56pralues(J10^9S6S651!H'HertzCDTE?OTationBondCeTtificate1570p*3%$24.9S.=：CL：rS21.values(110^3635452''Eall^dXk：TINMallTruckSexles'r$7-95,511.27,$7.@5.0,s2aDl-lTallies(11□5355953Q'.'iiUWEHC1CAvon^arLZ511ver/GOl(3Wa^cilNL3r.$24.99rKULLjrSZ4raluesC-10^37^7228','HANI)BLffilNrETCHEDGLASSBRANDYSNIFTER^613.35.NULLrS19.95,0vainest110^3749560''CGC9.□SOPJkGEGISMT*SSECRETORIGINSUF/W"$1S7-50rNTTLl,!^aiuiea^iDtaEi?^^^'.'I^adhunosttownjdoicIeiiijoingJoooSr1961r..aarnull,S6.□Clisesf-10^3&99328!.'PRECIOUSMOMENTS15HAEEYYEARSATWEETBIRDS*■.£19,90.NUL.£〔吟萱有巳煎:执行『酒部，亍克彰lv行登轲：I等标，

(inserT-xnee1LIEEinsertin.toitenluserrilLCCIteminsertintciteminsert±n.toiteninserr-niceiteninsertintciteminsertixitciteminserr-niceiteninserti.nt□itemlaseriluce1cemmserr-niceiteninsertin.toitenInsexrlILCCIteminsert_3.sql-adnnira-PC\_\3dmi[ri(52)J^aliuesI：■1595652037'.'CAt^iQEFESTIVAL1992CFEICIRLUkPELPINBPS2.50.UULLr^2.50r□f'―valte-5(114956521S21r'GuidingLightRimZiirjT.srBEa-thquakeOutfit!'r9.76,NULL,,*values(l149565JE13,f1SKCKEYMBTHEBM3DITfFM.T2-L511X14"8?7B49,EIULL,$7.^9sD.Lraloes(11495653797'.'GHOSTBUSTERSC&NAYKROYDSIGNEDPICSrS6.99^nLLHS6.99r0,p200values(11*3956545SB1r1Dianey11rsBlaEnEHardBoundTheTEicredihilBJourney1r$3.99rN1JE.L.F$valuesi：■1^9565^.99.1'.'PiacruEcitneZC1<EIE3EiE5Hauiniermovieeromo-,$11.99rwltiL.$ralcea(11^956550^1','ECWHESTERTAPEHOSTILECITYSHOWDOWN95*KTOLL,619.9valuesi：1149565525€''CHEECH£CriO'JGOPTK5MC1EZIPPCLIKELIGHTER"rNnilr?6valuesI：111g5656031'.'1UCI1IE3RLL由1IJHfiRIQMhEX-SXIQ'r^5.99rWULLh>5■'S^rQr■2DD1-1Clises(1195657S32!.!NEWErnunFlexIntElectricRaiorShhtfct"^20,5DrNUu^,vailLSS(■1^956550221f'OASISLIVEV3SVIDEO-97B($7.00.NULL,$7-00,0,,"2001-12-1017t4valuesI：(inserT-xnee1LIEEinsertin.toitenluserrilLCCIteminsertintciteminsert±n.toiteninserr-niceiteninsertintciteminsertixitciteminserr-niceiteninserti.nt□itemlaseriluce1cemmserr-niceiteninsertin.toitenInsexrlILCCItem曰inserr-insertlaseri-xnccintoluceinserr-insertxn.ccintoinc曰inserr-insertlaseri-xnccintoluceinserr-insertxn.ccintoinccinserrinsertinsertxn.ccintc±n.tdinserr-insertinsertlaserrxneeintointdniceirimLcsnbblds^vwww^^www^志.4=*:忐获、基：■由it-ssc.h±d^g心次核HM.E；欢it-mhidfAAAAAAAJ■■%AAAAAAJLczrjDLdc:炽二、.切;欢iternbidsAAAAAAAJ■■%AAAAAAJvalues["1043=402761■rvaluer('^3-^95702arvalues('104349570.2'.values["1043-4957G2srvaluer(^10-^3-^957021rvaluesi："10434-95,?02s.values("1043435702"rwahim["104374知牝•「valuesi；,,104374-9S60Brvaluesciouv^^aeG"rwahim["1加3M罪切.「viluesi；,■104374-9360'rvalues("104374-93fiC-1rwN心i'H4.g*C』-PJISjnlilnoaBtVldleCi1rB2DD1-1^-Q6Q6：：5G"$4；nobody1381.B2001-12-0^23:20507^512^9);amnlMl汕乏。。。\^OQi-LJ-DC02J00107■r515.991；£□^0^123'/"2QQ1-12-07□^.：^0：Qlir£lB.39：l；KBtehdenmark1r■2001-12-0807:20:071,e22.00);zLi>nrh,,ll'2001-12-0910:00:07'.?35-CJijmllwl11』'2D01-12-1D12：40：07-r^2a.DDJ；hhfcl213'「12001-12-0506:301r$17■37);littlelau'『"2001-12-0522:3£:30-r533.74);eaztnileev'/"2QQ2-12-061《；38：日口・rS5D.12：i;cl0TCT.3-woodaedgep,p2001-12-01?Ofi:36;30\.-19!?BEtsniirt'r'2001-12-Q755:3C:30Br$B2.E7);ClOD^llnQltorokeE1rB2001-12-D8S：3D",$99.24：?咛的支功执&admin-PCi&QLEXPRESS(10.0SP11admin-PC.admin(52]eBayOOKX>1COrrinsert_4.iql-admira-PC\_\a[§2)J,X、或七A■L亍乏蓼杠'C1'亍由加'•冷向，微或已蛔1代.admin-PO.SQLEXPMSS(10.0SP1)admin-PQadmin(52)eB*00:0003•佛a(1

/■insert^S.sql-admini-PC\_\3(S2))■Xinserr.xnccitera_aa,ceCrCiry~e3iiiTaluea('10^33755^5"•'CollecDUsles■);insertintoitera_categ；Dryizsinralues；11C4237454511r'Lee口匚曰七三#三&rlaliday1;l^serr-incciceni_caLegorycentivalues:'lC^-2374545"(,'Eecorailvety3^and')?insertintciteffl_categorycemvalues«；*10-133^5511,11ChristopheeBidko!)insertluteit«=m_categDrytesnralues;11C-423975911rrCallectih1c31);inaerr-xneeite^aa'cefforycejqvalueai'10-33337^59"'Eecorative&Holiday'!■?insert,intcitcra_categoryccmTaluea(*10^13397^59"，--DecorativebyBr^nd')?insertixit□it-m_categiorytemv<lu-s11C-S2397455|,||pEcesco■[finaerr-xnccLtem_aa-cefforyGsjqvaluea:'10^3397^59pTreciousMcniEntBrj;insert-intomH吒睥tgmyt;顷values；,10-^3^102767"15CollectiblesB);laserr-luceiceni_caiLegcirycenivalues:'02765"(,'Eecozazlve&Holiday'?inserr-xneeitem_aa-cefforyceiiQvalues('IC-^34D2767"*■1Eecorarive匕誓3rand')?insertintoitera_cat.egDryteinraJlucs；11C-42-4027fl711rTnescc-1;inserzXaccLnmmaa^eaor/-em'/aj.'LL-25'1C3^027^7r-'PreciousXi^encs'■；j6jMlf•蛀4L行登熟律L：1片而，M行受脚，*1*巨，':芾七A卜-碧非巳^功执三admin-PC\&QJ_EXPRESS[1(X0SP1)ad-in-PCiadminC5^eBay00:0ft{>7。行i，，sert_Gwql-admii[S2)}-■X[inaerr.xneezte^aa'cefforycejqvaluea('10^9566619"r&VDW”―1insert,intcitcra_categoryccmvaluej(110-^^566019"*■Ornionienta1J:'j_^3ertinccicem_caLegorycemvalues:110^^563756"„-Collecrltles-);inserr-xneezte^aa'cefforvesjqvaluea(1IG-^956B756prPOPCUlLUIE9>；insert,intoitera_cateo0rvcemvaluta«；1lC^^o60?56"，-"Finbacks"}:laserr-inccicenh_caiLegciryEenivalues:1lC^S56914i5"(,"Collecrliles■);inserr-xnee1tera_ca-cefforycsjqvalues(1IC-^95691^5"rlouseware3&Kltchen'ware"[;insertintcitemcategorycemvalues«；110^1S5691511,11Metalware■}:lasersUicc1cem_ca^egoryi：enivalues:11C^95S91^5"#rSterlingSiIver『);inaerr-xneezte^aa'cefforycejqvaluea('10^9569619"pCollect1£les■);insert,intcitcracategoryccmvaluej(110^^569619""PopCulturE"}jj_^3ertinccicem_caLegorycemvalues:11C^S569S15"„"ComicalR?inserr-xneezte^aa'cefforvesjqvaluea(110^9569613prGolden丽E(1935-55)'F?insert,inccitemcateiiorvcsTOLuc^!lC^95i696i91.1Westerns11>:一4Inr-1卜J.际，■芾七A■I行登村「1*巨，.1T^.tJ-I:l'■I行登轲「1等林，4卜命查南丑五功执Eadmin-PC\&QJ.EXPRESS[Ift&SPl]ad-in-PCiadminC5^eBay00:0ftDS•:行

由于直接生成的文本数据存在一定的重复，不满足部分表的主键要求，所以创建临时表userstem、item_categorytem，在此基础上进行唯一查询后，再将数据导入相关表。最后删除临时表。此操作包含在insert.sql文件中。insertintousersselectdistinct*fromuserstem;droptableuserstem;insertintocategoryselectdistinctcategory

fromitem_categorytem;insertintoitem_categoryselectdistinct*fromitem_categorytem;droptableitem_categorytem;PartD测试你生成的数据库SQLServer/PostgreSQL导入数据后，首先对每个关系进行测试验证是否正确导入，然后实现以下查询来检查数据库的正确性。1.关系验证1)users存在locating、country为空的用户，黄色显示，为空值，2)item——日期、金额数据正常，空值保留SQLQuery25.sql-admin-PC\-V-n(52))*■X日select■[壬》攻iWB|-iiitmrinameCUTaTth'BL^_RrE；BFntjidNu...■StatBdBids1:104337^54&:diratopharradke1frtzn_InoEh'slEddria3D.<NNULL如加0^KI1-12-031E<1Q:4a.DX-M0(>2®1-1221(M3agf7i5&PM0OY.WDGIRLEXTLUSIVEEVENTPIECELE9S.9SNULL99.3902001-12^318.3S23.KXX顾2-X-1-1231043402767PreckiLriMomentsGrlStowktriTeaSet4.00NULL4.1M13001-12-3116.44：54.C^M0025C-1-1241M34O2誓SiMirijcPotterTaihrnlGbuze^erSiakera3.50NULL3.M02001-124131E44：玷工硕0002-301-125眄7艰PreciousMcHK-rfeFlaANGELQFMERCY-NURSE2600NULL9.9962001-12<J20迎由.WKXHXffl2W1-1251043500?DRERMSICUESwiEdg'LOVELEE盼e巾rMONULL6.M02001-1M320-59:45.03™™2®1-127［混海域Tc^nCnerCDIK15.00NULL15M020O1~12--M皿能:56.KXXKK»细值B1^3565^91HertzCHpcraUcnHcridCertficale1970Je2d.95NULL24.9502001-12-34M02:D4.KXX顾2-DC-1-1251^5608482HalmarkTINMaiTruckSedee7.9511.177.55020ai-12-M-3E.ia；2fl.D»MKI&JK1-1210lM36S06flOAiihmli匚AuoiQuart?SkiH-.-CxiklWHchNIBa的NULL24玷02001-12-341030：玷.0330002-301-12111G4J74J722BHVID0LOTN.ETCHEDGU^SBRANDYSNIFTER1996NULL1395Q2001-12-0414-29:34.03000002W1-12皿邪制PAGEGIANTSECRETORIGINSVFA.197.90NULLO.M122001-12^14再3O.WKXMXffl2W1-12131^^B17906NE(/ADAghasitownboGtedg-snobcok,1%l€.00NULL讷12001-12-^皈5€27.KX>顾1d佃加9&gPRECDUSbO』ENTS15HAPPYYEARSATWEE...19.90NULL19.9012001-12^1^.3.5:481^30002-K1-1215佃始如SG250」s8&1妁BUBBLEMAILERS11.99NULL11.59120G1-12-^3419XM:25.KX>M002-K1-121BIM顷颂EHESCOMARTE；MMMOOSCOWCOUPLEW日3.9ftNULL9.&B02001^341S11：42.&3C*MOO2-301-1217iMsasnraFa-rfasticFqltS79NHDsvr-alW3日.59NULL9390MOI-12-342Cs<J3:5S.DX-M(Mi2-301-12爆登借已员功执&admini-PQSQLEXPRE5S(10.Q即1〕admin-POiSdmir[52]eBayMsOOHM19532汗Description数据字段最长字节13960/SQLQuery23.sql-admin-PC\...\-n(52))*□selectn.ax(LZtr(Descripticn|N'NEEE'g'EE'E'Z'Z'Z'rLfxorciitemJ结果-1消息I无列名「—1IDescription数据字段内容点件见WSlEJ^KLO.1*»IV)W>；HJ魄51»dniinPCvVr*便知1*1B«Lect躬景安由四虫岛｝ErotB共明■leltrtTS3.X1Z24Despz351ci.g魄51»dniinPCvVr*便知1*1B«Lect躬景安由四虫岛｝ErotB共明■leltrtTS3.X1Z24Despz351ci.gjl-frsBi'cerii•CalBhIpj(196早Ciset甚UEE5g姑Ta野bJ哪piBaj-msd3lisdmSn-PCiSi^at.aSergEEniPaulRyker—・skaCaseAsainsiPaulfiykerThe(1^2)..okaCaseftsainstSerpranlEyfcerThe(19眄〕...akaTom5etPKenTwdVa.Lues口，6&)NesnE-rtHenintheWestThe(1!^67)(TV)……*打5■&OpcrailnnDirtjIinzen(1^7)(nicredited).…*HinscLERciizkTheQBBT](LincreJiteiil...HirselfToniteLef*bMllikeLewinLotidon(IB&rtrr,,Guest,--akaLmdcti豹eneTheCl'SbT)PointBlank(况研)….MslkerDirtyDozenT&(1第T)..…HajorJahnFLeisnanPjDfeasitMjalsThf(L^&S'I企监年"砧《FsrianShipofFools(19&5)BillTcnnrCertBallou….Shellcen-StraMiKillcreThe〔1964)「…CkarLie：Strom…akaErnestHemine^i/eTheKillers(1964)(DEA：pranDtianaltitle)"'LawbreakersThe*Q.QG3)TVSericaCvDiu*…』Narrator…akaT_aNbre±er^(1953?CLfiriehttitle)Donegan*aKcef(19^3)L.,,ThduasAloysinsJBaa+sJGilhoolcyHanWhoShotLiberlyValanceThe(15&2J….Liberty&larc«=CanairxherDisThe(19杖L)..…TullsrCrow租msccirLTrEfirelerThenB..ToljiatErcwiOptratioi-iRaitHreeIJ957)Iwfirei^ted^1…口『HinEclfRaintrecCojntr(1957)…-Orvi11eJriashJPerkiriEV)1版函*(195?)T¥SeriesLiEiitenantPrankB-allir^erPillarsd£theSky(1at.aSeiEEEtiiLloydCarraiarx…okalonraha^andtheCreesThe(OT)RackIhr(19街)..iaCsff-tainJoinfLMillrrAttack(1H弱)…Calane-1BartlettSevenHenfroraBam(1SES)...»BillNsEttrs[DiedaThouMndTimes(11:65)BabeKosruzkPeteEellyJsBIuej(1S55)・・，.ALViolentSaturday(1^)...-DillSliackOutcmICQ(19成).，，Slob.'Tlr-^re^oryNdi曲aStranger(19肉)BrundageLifeint&BalsaceA[1^55).=TheKillerBadDayatBlackRjoek(1955)…八HectorEavldGdtLLIsaiLar昵……Shan曲Jijes砂RaidTtie〔19绍)...aLientenmtEe.3tin£C-aineHutiny7he(1§54)rBI.SeoiranMeatballWildOntThe(156^).#,ChinoDrwnAunngthe!£hjelteririiPalms(1SK3)PrivateSmveLj-..“akaFriendlylelsnd(19眨)CUEAjGlory如igadeThe(1953)-at.,CarporslBownaiGunFury(195^)-.-r,ElirdiyStranE^rMore：aGuriThe(1965^ss,,DonEijrthSEninaLe..一一Sergeantla^rudtrBigHeatThe(1953)-.VinjceStoneDit>lDmaricCouriEr〔L95营……ILF.TriesteDuelstSilb'erTOC\o"1-5"\h\zJ^LQuerySajql-adinin-PC\._\-H■X□select*T1trom'3品甲.上消息◎asoary•-：<'Maiths""2Whch12-24Moni^IthchIfkMdioramai^r1伽印1Rhch&1^fr591370-®1豌Now剥hch22hch24hch33HPM./LF^MMEhs50如t崩〔1蜘2DO那6-12Marciaanri■admii-PC\SQLEXPREES(1J0.DSP1)|admn-PC^drr-in(52eBa#OOtOCMD1D4W行4)item_category—-存在多值依赖SQLQuery23.M|l-admin-P€\„Vh(52)}*■X曰flelecr.■L王二■口5it-iTjIzateqazyir3拮果_j消.皂isnidjayI:1W3374M&?ChrrtoplxrFladko□Z1043374M&CdtediNM31043374M&DecoratweliH加ay4■KM3374V5DscafBth'BbyErand5KH338745&口仙湖BS61(M3a97d5&DecorativeLHdiddy1(M3a97d5&DecorativeBrandB104338745SErescog1043137459FreeialbMorertiW1-3434027^7Cdfedibles111043402沔了Decanative1Hdkiay121043402757DecoratwebyBrand13KH340275?ErCKD14KH3402767Freeloa_aMcfrsrte帽1043^02735C仙!tUHbs161^3^02735DacarativeLH^lddy171iM3dO27B5Decarativeb^Brand但ia43J027B5Eresco191-3434027B&OtherEtiesbt>=ms田萱饲1已成物而^dffl-in-PC^SQLEXPRESS(10.0SP1}admin-P^dmin(52)eBayOttM:C»90263行5)item_bids日期、金额数据正常

SQLQueryia.fjql-admin--XHlselect*L_fHem:it=mbids1|-Zir_J咚果盅消皂ittnidbdderbmeDfrcurti:KH34027S7:醐由&aetsdea^1-12-06监则:54.<)OOOM-3佃□21CM3495702^1-12-042S:2C'fl7.0000M-312.S331-M^I957(]2dailehbSM-2X-1-12-06a2®.07.0000M^15.&941<M3fl95702bonco「il2323C-1-1207山.如时0000叫18.&9BT3434957-32waiuhdermark2301-12-Uft-3720业;倾加22.1M€1-M34957-Knrwwh2W1-12-091&0DA7ffiMDM-325.D371W3495702■alywl2®l-lZ-Wl?4Dfl7flOOO-™2S.MB104371^'bbb121S2001-1243506-36-30000000017.37314M374K5C-Itllalau3K1-12<I52236:M.WOO»3X3.7-41D1043祖蛔汨hdeew2X'1-12<I61d.36.3fl.OOOO»3M.1211佃冽蛔dove佗*igdg$d弟2M'1-124J70€.36.3fl.0000»3砥犯121M3用蜒uLsmeft2M1-12-D722l63Jjftj0O^3&2A?111(14374.^660■zlnihingbarker2M1-12-0&14魅制ftMODM明&1410437496505U市rgnMFit：2001中所跖明的OOWMOJnae1&1043743650parker3B32001-12-092?36-3DflfMOM^13Z00l&佃B74驱印tff1519^1-12-W14:36:3JJ.OOOOM-314S.3717佃齐4脱新atomqilarHlnBt2K1-12-11监36知.0000而164.7518佃3祖蛔iTikekomm2X1-12-112236.3fl.OOOOM^181.1219佃3海郡kaela23C-1-12-121J.^.3J].OOOQ^3197.50或宣肯已旧姬行.|admin-PC^SQLEXPRESS(1<i.&卯1)|jdmin-PC\admin(521eB即|0000:00|9£74E2.用户人数。2.selectcount(*)fromusers&LQu«y£2岳ql-admin-PC\...\...n(52))*□selectC3S.T.Z()Lfrcir.aserslJ结果，消息I无列名厂134223.来自NewYork的用户人数(即用户的location为“NewYork”，区分大小写)。selectcount(*)fromuserswherelocation='NewYork';

ADMLN-POlSQLEXPRE,.„eEay-dbo,users^QlElselectcoj.rt(k)fromjbetb,■WvvVyVvV--wherelocatj.ciii=1Newhfork1;*二1结果_J］消息既列匐J_1D1所属类别(category)个数为4的拍卖(auction)次数。selectCOUNT(*)from(selectitemid,count(*)ascountfromitem_categorygroupbyitemid)awherecount=4ADMIN-F>C\SQLEXPRE,„.ay-dbc.u^3selectCCUT-IT(ifion(select■吧iaid.rco二二七i匕ascos:tiaracategorygrcapby1temid.）a列名惟mid'无效oLw：Aeieco"^t=4|理结果岳s1i8365]5.当前价格最高的拍卖（aucion）的ID。selectitemid,currently

fromitemwh