外文翻译--关系数据库的结构.doc -- 5 元

StructureoftherelationaldatabaseTherelationalmodelisthebasisforanyrelationaldatabasemanagementsystemRDBMS.Arelationalmodelhasthreecorecomponentsacollectionofobjectsorrelations,operatorsthatactontheobjectsorrelations,anddataintegritymethods.Inotherwords,ithasaplacetostorethedata,awaytocreateandretrievethedata,andawaytomakesurethatthedataislogicallyconsistent.Arelationaldatabaseusesrelations,ortwodimensionaltables,tostoretheinformationneededtosupportabusiness.Letsgooverthebasiccomponentsofatraditionalrelationaldatabasesystemandlookathowarelationaldatabaseisdesigned.Onceyouhaveasolidunderstandingofwhatrows,columns,tables,andrelationshipsare,youllbewellonyourwaytoleveragingthepowerofarelationaldatabase.Tables,Rows,andColumnsAtableinarelationaldatabase,alternativelyknownasarelation,isatwodimensionalstructureusedtoholdrelatedinformation.Adatabaseconsistsofoneormorerelatedtables.NoteDontconfusearelationwithrelationships.Arelationisessentiallyatable,andarelationshipisawaytocorrelate,join,orassociatetwotables.Arowinatableisacollectionorinstanceofonething,suchasoneemployeeoronelineItemonaninvoice.Acolumncontainsalltheinformationofasingletype,andthepieceofdataattheintersectionofarowandacolumn,afield,isthesmallestpieceofinformationthatcanberetrievedwiththedatabasesquerylanguage.Forexample,atablewithinformationaboutemployeesmighthaveacolumncalledLAST_NAMEthatcontainsalloftheemployeeslastnames.Dataisretrievedfromatablebyfilteringonboththerowandthecolumn.PrimaryKeys,Datatypes,andForeignKeysTheexamplesthroughoutthisarticlewillfocusonthehypotheticalworkofScottSmith,databasedeveloperandentrepreneur.HejuststartedanewwidgetcompanyandwantstoimplementafewofthebasicbusinessfunctionsusingtherelationaldatabasetomanagehisHumanResourcesHRdepartment.RelationAtwodimensionalstructureusedtoholdrelatedinformation,alsoknownasatable.NoteMostofScottsemployeeswerehiredawayfromoneofhispreviousemployers,someofwhomhaveover20yearsofexperienceinthefield.Asahiringincentive,Scotthasagreedtokeepthenewemployeesoriginalhiredateinthenewdatabase.RowAgroupofoneormoredataelementsinadatabasetablethatdescribesaperson,place,orthing.ColumnThecomponentofadatabasetablethatcontainsallofthedataofthesamenameandtypeacrossallrows.Youlllearnaboutdatabasedesigninthefollowingsections,butletsassumeforthemomentthatthemajorityofthedatabasedesigniscompletedandsometablesneedtobeimplemented.ScottcreatestheEMPtabletoholdthebasicemployeeinformation,anditlookssomethinglikethisEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO7369SMITHCLERK790217DEC80800207499ALLENSALESMAN769820FEB811600300307521WARDSALESMAN769822FEB811250500307566JONESMANAGER783902APR812975207839KINGPRESIDENT17NOV815000107902FORDANALYST756603DEC81300020NoticethatsomefieldsintheCommissionCOMMandManagerMGRcolumnsdoesnotcontainavaluetheyareblank.Arelationaldatabasecanenforcetherulethatfieldsinacolumnmayormaynotbeempty.3Inthiscase,itmakessenseforanemployeewhoisnotintheSalesdepartmenttohaveablankCommissionfield.ItalsomakessenseforthepresidentofthecompanytohaveablankManagerField,sincethatemployeedoesntreporttoanyone.FieldThesmallestpieceofinformationthatcanberetrievedbythedatabasequerylanguage.Afieldisfoundattheintersectionofarowandacolumninadatabasetable.Ontheotherhand,noneofthefieldsintheEmployeeNumberEMPNOcolumnareblank.Thecompanyalwayswantstoassignanemployeenumbertoanemployee,andthatnumbermustbedifferentforeachemployee.Oneofthefeaturesofarelationaldatabaseisthatitcanensurethatavalueisenteredintothiscolumnandthatitisunique.TheEMPNOcolumn,inthiscase,istheprimarykeyofthetable.PrimaryKeyAcolumnorcolumnsinatablethatmakestherowinthetabledistinguishablefromeveryotherrowinthesametable.NoticethedifferentdatatypesthatarestoredintheEMPtablenumericvalues,characteroralphabeticvalues,anddatevalues.Asyoumightsuspect,theDEPTNOcolumncontainsthedepartmentnumberfortheemployee.ButhowdoyouknowwhatdepartmentnameisassociatedwithwhatnumberScottcreatedtheDEPTtabletoholdthedescriptionsforthedepartmentcodesintheEMPtable.DEPTNODNAMELOC10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONTheDEPTNOcolumnintheEMPtablecontainsthesamevaluesastheDEPTNOcolumnintheDEPTtable.Inthiscase,theDEPTNOcolumnintheEMPtableisconsideredaforeignkeytothesamecolumnintheDEPTtable.Aforeignkeyenforcestheconceptofreferentialintegrityinarelationaldatabase.TheconceptofreferentialintegritynotonlypreventsaninvaliddepartmentnumberfrombeinginsertedintotheEMPtable,butitalsopreventsarowintheDEPTtablefrombeingdeletedifthereareemployeesstillassignedtothatdepartment.ForeignKeyAcolumnorcolumnsinatablethatdrawsitsvaluesfromaprimaryoruniquekeycolumninanothertable.Aforeignkeyassistsinensuringthedataintegrityofatable.ReferentialIntegrityAmethodemployedbyarelationaldatabasesystemthatenforcesonetomanyrelationshipsbetweentables.DataModelingBeforeScottcreatedtheactualtablesinthedatabase,hewentthroughadesignprocessknownasdatamodeling.Inthisprocess,thedeveloperconceptualizesanddocumentsallthetablesforthedatabase.OneofthecommonmethodsformodelingadatabaseiscalledERA,whichstandsforentities,relationships,andattributes.Thedatabasedesignerusesanapplicationthatcanmaintainentities,theirattributes,andtheirrelationships.Ingeneral,anentitycorrespondstoatableinthedatabase,andtheattributesoftheentitycorrespondtocolumnsofthetable.DataModelingAprocessofdefiningtheentities,attributes,andrelationshipsbetweentheentitiesinpreparationforcreatingthephysicaldatabase.Thedatamodelingprocessinvolvesdefiningtheentities,definingtherelationshipsbetweenthoseentities,andthendefiningtheattributesforeachoftheentities.Onceacycleiscomplete,itisrepeatedasmanytimesasnecessarytoensurethatthedesigneriscapturingwhatisimportantenoughtogointothedatabase.Letstakeacloserlookateachstepinthedatamodelingprocess.DefiningtheEntitiesFirst,thedesigneridentifiesalloftheentitieswithinthescopeofthedatabaseapplication.Theentitiesarethepersons,places,orthingsthatareimportanttotheorganizationandneedtobetrackedinthedatabase.Entitieswillmostlikelytranslateneatlytodatabasetables.Forexample,forthefirstversionofScottswidgetcompanydatabase,heidentifiesfourentitiesemployees,departments,salarygrades,andbonuses.ThesewillbecometheEMP,DEPT,SALGRADE,andBONUStables.DefiningtheRelationshipsbetweenEntitiesOncetheentitiesaredefined,thedesignercanproceedwithdefininghoweachoftheentitiesisrelated.Often,thedesignerwillpaireachentitywitheveryotherentityandask,IstherearelationshipbetweenthesetwoentitiesSomerelationshipsareobvioussomearenot.Inthewidgetcompanydatabase,thereismostlikelyarelationshipbetweenEMPandDEPT,butdependingonthebusinessrules,itisunlikelythattheDEPTandSALGRADEentitiesarerelated.Ifthebusinessrulesweretorestrictcertainsalarygradestocertaindepartments,therewouldmostlikelybeanewentitythatdefinestherelationshipbetweensalarygradesanddepartments.Thisentitywouldbeknownasanassociativeorintersectiontableandwouldcontainthevalidcombinationsofsalarygradesanddepartments.AssociativeTableAdatabasetablethatstoresthevalidcombinationsofrowsfromtwoothertablesandusuallyenforcesabusinessrule.Anassociativetableresolvesamanytomanyrelationship.Ingeneral,therearethreetypesofrelationshipsinarelationaldatabaseOnetomanythemostcommontypeofrelationshipisonetomany.Thismeansthatforeachoccurrenceinagivenentity,theparententity,theremaybeoneormoreoccurrencesinasecondentity,thechildentity,towhichitisrelated.Forexample,inthewidgetcompanydatabase,theDEPTentityisaparententity,andforeachdepartment,therecouldbeoneormoreemployeesassociatedwiththatdepartment.TherelationshipbetweenDEPTandEMPisonetomany.OnetooneInaonetoonerelationship,arowinatableisrelatedtoonlyoneornoneoftherowsinasecondtable.Thisrelationshiptypeisoftenusedforsubtyping.Forexample,anEMPLOYEEtablemayholdtheinformationcommontoallemployees,whiletheFULLTIME,PARTTIME,andCONTRACTORtableshold