外文翻译--关系数据库的结构.doc
StructureoftherelationaldatabaseTherelationalmodelisthebasisforanyrelationaldatabasemanagementsystem(RDBMS).Arelationalmodelhasthreecorecomponents:acollectionofobjectsorrelations,operatorsthatactontheobjectsorrelations,anddataintegritymethods.Inotherwords,ithasaplacetostorethedata,awaytocreateandretrievethedata,andawaytomakesurethatthedataislogicallyconsistent.Arelationaldatabaseusesrelations,ortwo-dimensionaltables,tostoretheinformationneededtosupportabusiness.Letsgooverthebasiccomponentsofatraditionalrelationaldatabasesystemandlookathowarelationaldatabaseisdesigned.Onceyouhaveasolidunderstandingofwhatrows,columns,tables,andrelationshipsare,youllbewellonyourwaytoleveragingthepowerofarelationaldatabase.Tables,Rows,andColumnsAtableinarelationaldatabase,alternativelyknownasarelation,isatwo-dimensionalstructureusedtoholdrelatedinformation.Adatabaseconsistsofoneormorerelatedtables.Note:Dontconfusearelationwithrelationships.Arelationisessentiallyatable,andarelationshipisawaytocorrelate,join,orassociatetwotables.Arowinatableisacollectionorinstanceofonething,suchasoneemployeeoronelineItemonaninvoice.Acolumncontainsalltheinformationofasingletype,andthepieceofdataattheintersectionofarowandacolumn,afield,isthesmallestpieceofinformationthatcanberetrievedwiththedatabasesquerylanguage.Forexample,atablewithinformationaboutemployeesmighthaveacolumncalledLAST_NAMEthatcontainsalloftheemployeeslastnames.Dataisretrievedfromatablebyfilteringonboththerowandthecolumn.PrimaryKeys,Datatypes,andForeignKeysTheexamplesthroughoutthisarticlewillfocusonthehypotheticalworkofScottSmith,databasedeveloperandentrepreneur.HejuststartedanewwidgetcompanyandwantstoimplementafewofthebasicbusinessfunctionsusingtherelationaldatabasetomanagehisHumanResources(HR)department.Relation:Atwo-dimensionalstructureusedtoholdrelatedinformation,alsoknownasatable.Note:MostofScottsemployeeswerehiredawayfromoneofhispreviousemployers,someofwhomhaveover20yearsofexperienceinthefield.Asahiringincentive,Scotthasagreedtokeepthenewemployeesoriginalhiredateinthenewdatabase.Row:Agroupofoneormoredataelementsinadatabasetablethatdescribesaperson,place,orthing.Column:Thecomponentofadatabasetablethatcontainsallofthedataofthesamenameandtypeacrossallrows.Youlllearnaboutdatabasedesigninthefollowingsections,butletsassumeforthemomentthatthemajorityofthedatabasedesigniscompletedandsometablesneedtobeimplemented.ScottcreatestheEMPtabletoholdthebasicemployeeinformation,anditlookssomethinglikethis:EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO7369SMITHCLERK790217-DEC-80800207499ALLENSALESMAN769820-FEB-811600300307521WARDSALESMAN769822-FEB-811250500307566JONESMANAGER783902-APR-812975207839KINGPRESIDENT17-NOV-815000107902FORDANALYST756603-DEC-81300020NoticethatsomefieldsintheCommission(COMM)andManager(MGR)columnsdoesnotcontainavalue;theyareblank.Arelationaldatabasecanenforcetherulethatfieldsinacolumnmayormaynotbeempty.3Inthiscase,itmakessenseforanemployeewhoisnotintheSalesdepartmenttohaveablankCommissionfield.ItalsomakessenseforthepresidentofthecompanytohaveablankManagerField,sincethatemployeedoesntreporttoanyone.Field:Thesmallestpieceofinformationthatcanberetrievedbythedatabasequerylanguage.Afieldisfoundattheintersectionofarowandacolumninadatabasetable.Ontheotherhand,noneofthefieldsintheEmployeeNumber(EMPNO)columnareblank.Thecompanyalwayswantstoassignanemployeenumbertoanemployee,andthatnumbermustbedifferentforeachemployee.Oneofthefeaturesofarelationaldatabaseisthatitcanensurethatavalueisenteredintothiscolumnandthatitisunique.TheEMPNOcolumn,inthiscase,istheprimarykeyofthetable.PrimaryKey:Acolumn(orcolumns)inatablethatmakestherowinthetabledistinguishablefromeveryotherrowinthesametable.NoticethedifferentdatatypesthatarestoredintheEMPtable:numericvalues,characteroralphabeticvalues,anddatevalues.Asyoumightsuspect,theDEPTNOcolumncontainsthedepartmentnumberfortheemployee.Buthowdoyouknowwhatdepartmentnameisassociatedwithwhatnumber?ScottcreatedtheDEPTtabletoholdthedescriptionsforthedepartmentcodesintheEMPtable.DEPTNODNAMELOC10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONTheDEPTNOcolumnintheEMPtablecontainsthesamevaluesastheDEPTNOcolumnintheDEPTtable.Inthiscase,theDEPTNOcolumnintheEMPtableisconsideredaforeignkeytothesamecolumnintheDEPTtable.Aforeignkeyenforcestheconceptofreferentialintegrityinarelationaldatabase.TheconceptofreferentialintegritynotonlypreventsaninvaliddepartmentnumberfrombeinginsertedintotheEMPtable,butitalsopreventsarowintheDEPTtablefrombeingdeletedifthereareemployeesstillassignedtothatdepartment.ForeignKey:Acolumn(orcolumns)inatablethatdrawsitsvaluesfromaprimaryoruniquekeycolumninanothertable.Aforeignkeyassistsinensuringthedataintegrityofatable.ReferentialIntegrity:Amethodemployedbyarelationaldatabasesystemthatenforcesone-to-manyrelationshipsbetweentables.DataModelingBeforeScottcreatedtheactualtablesinthedatabase,hewentthroughadesignprocessknownasdatamodeling.Inthisprocess,thedeveloperconceptualizesanddocumentsallthetablesforthedatabase.OneofthecommonmethodsformodelingadatabaseiscalledERA,whichstandsforentities,relationships,andattributes.Thedatabasedesignerusesanapplicationthatcanmaintainentities,theirattributes,andtheirrelationships.Ingeneral,anentitycorrespondstoatableinthedatabase,andtheattributesoftheentitycorrespondtocolumnsofthetable.DataModeling:Aprocessofdefiningtheentities,attributes,andrelationshipsbetweentheentitiesinpreparationforcreatingthephysicaldatabase.Thedata-modelingprocessinvolvesdefiningtheentities,definingtherelationshipsbetweenthoseentities,andthendefiningtheattributesforeachoftheentities.Onceacycleiscomplete,itisrepeatedasmanytimesasnecessarytoensurethatthedesigneriscapturingwhatisimportantenoughtogointothedatabase.Letstakeacloserlookateachstepinthedata-modelingprocess.DefiningtheEntitiesFirst,thedesigneridentifiesalloftheentitieswithinthescopeofthedatabaseapplication.Theentitiesarethepersons,places,orthingsthatareimportanttotheorganizationandneedtobetrackedinthedatabase.Entitieswillmostlikelytranslateneatlytodatabasetables.Forexample,forthefirstversionofScottswidgetcompanydatabase,heidentifiesfourentities:employees,departments,salarygrades,andbonuses.ThesewillbecometheEMP,DEPT,SALGRADE,andBONUStables.DefiningtheRelationshipsbetweenEntitiesOncetheentitiesaredefined,thedesignercanproceedwithdefininghoweachoftheentitiesisrelated.Often,thedesignerwillpaireachentitywitheveryotherentityandask,"Istherearelationshipbetweenthesetwoentities?"Somerelationshipsareobvious;somearenot.Inthewidgetcompanydatabase,thereismostlikelyarelationshipbetweenEMPandDEPT,butdependingonthebusinessrules,itisunlikelythattheDEPTandSALGRADEentitiesarerelated.Ifthebusinessrulesweretorestrictcertainsalarygradestocertaindepartments,therewouldmostlikelybeanewentitythatdefinestherelationshipbetweensalarygradesanddepartments.Thisentitywouldbeknownasanassociativeorintersectiontableandwouldcontainthevalidcombinationsofsalarygradesanddepartments.AssociativeTable:Adatabasetablethatstoresthevalidcombinationsofrowsfromtwoothertablesandusuallyenforcesabusinessrule.Anassociativetableresolvesamany-to-manyrelationship.Ingeneral,therearethreetypesofrelationshipsinarelationaldatabase:One-to-manythemostcommontypeofrelationshipisone-to-many.Thismeansthatforeachoccurrenceinagivenentity,theparententity,theremaybeoneormoreoccurrencesinasecondentity,thechildentity,towhichitisrelated.Forexample,inthewidgetcompanydatabase,theDEPTentityisaparententity,andforeachdepartment,therecouldbeoneormoreemployeesassociatedwiththatdepartment.TherelationshipbetweenDEPTandEMPisone-to-many.One-to-oneInaone-to-onerelationship,arowinatableisrelatedtoonlyoneornoneoftherowsinasecondtable.Thisrelationshiptypeisoftenusedforsubtyping.Forexample,anEMPLOYEEtablemayholdtheinformationcommontoallemployees,whiletheFULLTIME,PARTTIME,andCONTRACTORtableshold