讲关系数据库的结构的计算机英语.docx

讲关系数据库的结构的计算机英语.docx

《讲关系数据库的结构的计算机英语.docx》由会员分享，可在线阅读，更多相关《讲关系数据库的结构的计算机英语.docx（10页珍藏版）》请在冰豆网上搜索。

讲关系数据库的结构的计算机英语

讲关系数据库的结构的计算机英语

计算机英语

StructureoftheRelationaldatabase

Therelationalmodelisthebasisforanyrelationaldatabasemanagementsystem（RDBMS）.Arelationalmodelhasthreecorecomponents:

acollectionofobjectsorrelations,operatorsthatactontheobjectsorrelations,anddataintegritymethods.Inotherwords,ithasaplacetostorethedata,awaytocreateandretrievethedata,andawaytomakesurethatthedataislogicallyconsistent.

Arelationaldatabaseusesrelations,ortwo-dimensionaltables,tostoretheinformationneededtosupportabusiness.Letsgooverthebasiccomponentsofatraditionalrelationaldatabasesystemandlookathowarelationaldatabaseisdesigned.Onceyouhaveasolidunderstandingofwhatrows,columns,tables,andrelationshipsare,youllbewellonyourwaytoleveragingthepowerofarelationaldatabase.

Tables,Row,andColumns

Atableinarelationaldatabase,alternativelyknownasarelation,isatwo-dimensionalstructureusedtoholdrelatedinformation.Adatabaseconsistsofoneormorerelatedtables.

Note:

Dontconfusearelationwithrelationships.Arelationisessentiallyatable,andarelationshipisawaytocorrelate,join,orassociatetwotables.

Arowinatableisacollectionorinstanceofonething,suchasoneemployeeoronelineitemonaninvoice.Acolumncontainsalltheinformationofasingletype,andthepieceofdataattheintersectionofarowandacolumn,afield,isthesmallestpieceofinformationthatcanberetrievedwiththedatabasesquerylanguage.Forexample,atablewithinformationaboutemployeesmighthaveacolumncalledLAST_NAMEthatcontainsalloftheemployeeslastnames.Dataisretrievedfromatablebyfilteringonboththerowandthecolumn.

PrimaryKeys,Datatypes,andForeignKeys

TheexamplesthroughoutthisarticlewillfocusonthehypotheticalworkofScottSmith,databasedeveloperandentrepreneur.HejuststartedanewwidgetcompanyandwantstoimplementafewofthebasicbusinessfunctionsusingtherelationaldatabasetomanagehisHumanResources（HR）department.

Relation:

Atwo-dimensionalstructureusedtoholdrelatedinformation,alsoknownasatable.

Note:

MostofScottsemployeeswerehiredawayfromoneofhispreviousemployers,someofwhomhaveover20yearsofexperienceinthefield.Asahiringincentive,Scotthasagreedtokeepthenewemployeesoriginalhiredateinthenewdatabase.

Row:

Agroupofoneormoredataelementsinadatabasetablethatdescribesaperson,place,orthing.

Column:

Thecomponentofadatabasetablethatcontainsallofthedataofthesamenameandtypeacrossallrows.

Youlllearnaboutdatabasedesigninthefollowingsections,butletsassumeforthemomentthatthemajorityofthedatabasedesigniscompletedandsometablesneedtobeimplemented.ScottcreatestheEMPtabletoholdthebasicemployeeinformation,anditlookssomethinglikethis:

NoticethatsomefieldsintheCommission（COMM）andManager（MGR）columnsdonotcontainavalue;theyareblank.Arelationaldatabasecanenforcetherulethatfieldsinacolumnmayormaynotbeempty.Inthiscase,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.ButhowdoyouknowwhatdepartmentnameisassociatedwithwhatnumberScottcreatedtheDEPTtabletoholdthedescriptionsforthedepartmentcodesintheEMPtable.

TheDEPTNOcolumnintheEMPtablecontainsthesamevaluesastheDEPTNOcolumnintheDEPTtable.Inthiscase,theDEPTNOcolumnintheEMPtableisconsideredaforeignkeytothesamecolumnintheDEPTtable.

Aforeignkeyenforcestheconceptofreferentialintegrityinarelationaldatabase.TheconceptofreferentialintegritynotonlypreventsaninvaliddepartmentnumberfrombeinginsertedintotheEMPtable,butitalsopreventsarowintheDEPTtablefrombeingdeletedifthereareemployeesstillassignedtothatdepartment.

ForeignKey:

Acolumn（orcolumns）inatablethatdrawsitsvaluesfromaprimaryoruniquekeycolumninanothertable.Aforeignkeyassistsinensuringthedataintegrityofatable.ReferentialIntegrity?

Amethodemployedbyarelationaldatabasesystemthatenforcesone-to-manyrelationshipsbetweentables.

DataModeling

BeforeScottcreatedtheactualtablesinthedatabase,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.

DefiningtheEntities

First,thedesigneridentifiesalloftheentitieswithinthescopeofthedatabaseapplication.Theentitiesarethepersons,places,orthingsthatareimportanttotheorganizationandneedtobetrackedinthedatabase.Entitieswillmostlikelytranslateneatlytodatabasetables.Forexample,forthefirstversionofScottswidgetcompanydatabase,heidentifiesfourentities:

employees,departments,salarygrades,andbonuses.ThesewillbecometheEMP,DEPT,*****E,andBONUStables.

DefiningtheRelationshipsBetweenEntities

Oncetheentitiesaredefined,thedesignercanproceedwithdefininghoweachoftheentitiesisrelated.Often,thedesignerwillpaireachentitywitheveryotherentityandask,IstherearelationshipbetweenthesetwoentitiesSomerelationshipsareobvious;somearenot.

Inthewidgetcompanydatabase,thereismostlikelyarelationshipbetweenEMPandDEPT,butdependingonthebusinessrules,itisunlikelythattheDEPTand*****Eentitiesarerelated.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,an*****Etablemayholdtheinformationcommontoallemployees,whilethe*****E,*****E,and*****TORtablesholdinformationuniquetofull-timeemployees,part-timeemployees,andcontractors,respectively.Theseentitieswouldbeconsideredsubtypesofan*****Eandmaintainaone-to-onerelationshipwiththe*****Etable.Theserelationshipsarenotascommonasone-to-manyrelationships,becauseifoneentityhasanoccurrenceforacorrespondingrowinanotherentity,inmostcases,theattributesfrombothentitiesshouldbeinasingleentity.

Many-to-manyInamany-to-manyrelationship,onerowofatablemayberelatedtomanyrowsofanothertable,andviceversa.Usually,whenthisrelationshipisimplementedinthedatabase,athirdentityisdefinedasanintersectiontabletocontaintheassociationsbetweenthetwoentitiesintherelationship.Forexample,inadatabaseusedforschoolclassenrollment,the*****tablehasamany-to-manyrelationshipwiththeCLASStableonestudentmaytakeoneormoreclasses,andagivenclassmayhaveoneormore