PAGEPAGE6《數(shù)據(jù)庫原理與實用技術(shù)實驗》2016年期末大作業(yè)項目任課教師：張云春、劉宇GOAL：Thegoalofthisprojectistoprovidearealisticexperienceintheconceptualdesign,logicaldesign,implementation,operation,andmaintenanceofarelationaldatabaseandassociatedapplications.First,Ishalldescribetheapplication,thenthecategoriesofrequirements,andthensomesuggestionsonhowdeeplyyouneedtogoineachcategory.Arealprojectofthissortwouldrequireasubstantialdevelopmentteamworkingforseveralmonths(ormore).Youwilldothisaloneoverseveralweeks.Ihavechosentogoindividualratherthangroupprojectsbecausethegoalofthisprojectisforyoutogainapersonalappreciationofthedepthandbreadthofissuesthatgointothedesignofadatabaseapplication,ratherthantohave youspecializeinjustoneaspect(andrelyonothersfortherest).TheprojectcangowellbeyondtheminimalrequirementsIoutlineattheend.Iencouragesuchextensions.Theycouldturnintoaseniordesignprojectorotherindependentwork.要求：業(yè)項目報告中的詳細(xì)要求。5張，否則認(rèn)為設(shè)計失敗。20SQLE-R圖（使用專業(yè)工具繪制基數(shù)（cardinality；實體與屬性的基數(shù)可以不寫，但是在分析時必須明確哪NULLE-R轉(zhuǎn)換所對應(yīng)的轉(zhuǎn)換規(guī)則（6條轉(zhuǎn)換規(guī)則。最后，附上相關(guān)的設(shè)計總結(jié)與心得體會。項目相關(guān)內(nèi)容說明PackageDeliveryCompanyDatabaseRequirements:ApplicationdescriptionTheapplicationisdesignedtomanagepackagedeliverycompany(similar順豐,申通,圓通,中通,EMS等).Thecompanyneedstokeeptrackofpackagesshippedandtheircustomers.Tofindoutmoreaboutthisapplication,thinkaboutanyexperiencesyoumayhavehadshippingpackagesandreceivingpackages,andbrowseshipperwebsites.Inourhypotheticalcompany,themanagerassignedtosolicitdatadesignproposalsisnotverycomputerliterateandisunabletoprovideaverydetailedspecification.Hereareafewpointstoconsider:Therearedifferentkindsofservicepossiblybaseduponthetypepackage(flatenvelop,smallbox,largerboxes,etc.),theweightofthepackage,andthetimelinessofdelivery(overnight,secondday,longer).Somecustomershaveacontractwiththeshipperandbilltheirshipmentstoanaccountnumber.Theyarebilledmonthly.Othercustomersareinfrequentcustomersandpaywithacreditcard.Certainshipmentsareprepaid,asismightbethecaseofsomeoneisreturningsomethingthatwaspurchasedbyphoneorInternet(e.g.returningclothesthatdon’tfit,orreturningmalfunctioningelectronics).Forthemostpart,theshippingcompanydoesnotcarewhatisbeingshipped.However,therearecaseswhereitmatters.Someexamplesinclude:hazardousmaterialsinternationalshipments,forwhichacustomsdeclarationthecontentsandtheirvalueisneededThecompanyneedstotrackthetimethecustomerdropsitoff(oritispickedupbythecompany)untilthetimeitisdeliveredandsignedfor.Takealookattheonlinetrackingofferedbyvariousshippingcompaniestogetanideaofhowthisserviceworks.Ifyouarehavingsomethingshippedtoyou,you’llfindyoucangeteverylittledetailofwherethepackageis,whereithasbeen,andtowhereitiscurrentlyheaded.Beyondwhatthecustomersees,thecompanyitselfneedstoknowonwhichtruckorplaneorwarehousethepackageisatanypointintime.Trackingisnotjustan“inthepresent”issue.Thecompanymaywanttolookbackintimeandfindoutwherethepackagewasyesterday,forexample.Itmayalsowanttolookatdatafromthestandpointofatruckorwarehouse.Thereareotheraspectstotheoperationofthecompanybesidespackagetrackingsuchasroutingoftrucksandplanes,theassignmentofstafftothem,etc.Forthisassignment,we’llconsideronlythepackagehandlingandbillingaspectsofthedatabase.基于如上的分析，要求每位同學(xué)給出完整的需求分析，并撰寫“需求分析”文檔。上面的需求有的可以推導(dǎo)出實體、屬性等；有的則可以作為功能語句（如觸發(fā)器、存儲過程、索引等）來實現(xiàn)，需要在分析時進行說明。DataGeneration:Forsimplicity,thedesigndoesnotrequireperfectlyrealisticdatasothatyoudonthavetoensurethat.However,youshouldstriveforagooddegreerealisminyourdata.Notethecommentsoncollaborationbelowandthatsharingdatawithothersisacceptableaslongasappropriatecreditisgiventoyoursource.Thefollowingoutlinesmayhelpyouwithyourdesign:E-RModelConstructanE-Rdiagramrepresentingtheconceptualdesignofdatabase.Atminimumyoumustincludealltheentityandrelationshipsetsimpliedbythishandout.Youmaygobeyondtheminimum.Rememberthatthemanagerwhodefinedthespecificationsisnotcomputerliteratesothespecificationsshouldnotbeviewedasnecessarilybeingpreciseandcomplete.Besuretoidentifyprimarykeys,relationshipcardinalities,etc.RelationalModeAftercreatinganinitialrelationaldesignfromyourE-Rdesign,refinebasedontheprinciplesofrelationaldesign(Chapter6).Createtherelations(Tables)inSQLServerdatabase.Createindicesandconstraintsasappropriate.Ifasyourefineyourdesign,youdiscoverflawsintheE-Rdesign,backandchangeit.YourfinalE-Rdesignmustbeconsistentwithyourrelationaldesign.PopulateRelationsIncludeenoughdatatomakeanswerstoyourqueriesinterestingnontrivialfortestpurposes.Atleast20rowsaresupposedtobeinsertedineachtable.(Optional)Youmayfindithelpfultowriteaprogramtogeneratetestdataorcollectdatafromarealpackagedeliverycompany.Queries:Youshouldrunanumberoftestqueriestoseethatyouhaveloadedyourdatabaseinthewayyouintended.Thequerieslistedbelowarethosethatyourclients(themanagersfromthepackagedeliverycompany)findofinterest.Theymayprovidefurtherhintsaboutdatabasedesign,sothinkaboutthemattheoutsetofyourworkonthisproject.Assumetruck9527isdestroyedinacrash.Findallcustomerswhohadapackageonthattruckatthetimeofthecrash.Findallrecipientshadapackageonthattruckatthetimeofthecrash.Findthelastsuccessfuldeliverybythattruckpriortothecrash.Findthecustomerwhohasshippedthemostpackagesinthepastyear.Findthecustomerwhohasspentthemostmoneyonshippingintheyear.Findthecitywiththemostcustomers.Findthosepackagesthatwerenotdeliveredwithinthepromisedtime.Generatethebillforeachcustomerforthepastmonth.Considerseveraltypesofbills.Asimplebill:withcustomer,address,andamountowed.Abilllistingchargesbytypeofservice.Anitemizebillinglistingeachindividualshipmentandchargesforit.Customersliketheirbillstobereadable.WhiletheclientwillacceptarelationaltablecomingfromSQLServerasthebill,itwouldbe tohaveagood-lookingbill.Interfaces(OPTIONAL):Thereareseveraltypesofuserswhoaccessthedatabase,andseveralapplicationsthatrunontheirown.Thedatabaseadministrator(you)mayuseSQLeitherviathelineorSQLDeveloper.Customerserviceneedsalookupapplicationthroughmen