资源预览需要最新版本的Flash Player支持。
您尚未安装或版本过低,建议您
STRUCTUREOFTHERELATIONALDATABASETHERELATIONALMODELISTHEBASISFORANYRELATIONALDATABASEMANAGEMENTSYSTEMRDBMSARELATIONALMODELHASTHREECORECOMPONENTSACOLLECTIONOFOBJECTSORRELATIONS,OPERATORSTHATACTONTHEOBJECTSORRELATIONS,ANDDATAINTEGRITYMETHODSINOTHERWORDS,ITHASAPLACETOSTORETHEDATA,AWAYTOCREATEANDRETRIEVETHEDATA,ANDAWAYTOMAKESURETHATTHEDATAISLOGICALLYCONSISTENTARELATIONALDATABASEUSESRELATIONS,ORTWODIMENSIONALTABLES,TOSTORETHEINFORMATIONNEEDEDTOSUPPORTABUSINESSLETSGOOVERTHEBASICCOMPONENTSOFATRADITIONALRELATIONALDATABASESYSTEMANDLOOKATHOWARELATIONALDATABASEISDESIGNEDONCEYOUHAVEASOLIDUNDERSTANDINGOFWHATROWS,COLUMNS,TABLES,ANDRELATIONSHIPSARE,YOULLBEWELLONYOURWAYTOLEVERAGINGTHEPOWEROFARELATIONALDATABASETABLES,ROWS,ANDCOLUMNSATABLEINARELATIONALDATABASE,ALTERNATIVELYKNOWNASARELATION,ISATWODIMENSIONALSTRUCTUREUSEDTOHOLDRELATEDINFORMATIONADATABASECONSISTSOFONEORMORERELATEDTABLESNOTEDONTCONFUSEARELATIONWITHRELATIONSHIPSARELATIONISESSENTIALLYATABLE,ANDARELATIONSHIPISAWAYTOCORRELATE,JOIN,ORASSOCIATETWOTABLESAROWINATABLEISACOLLECTIONORINSTANCEOFONETHING,SUCHASONEEMPLOYEEORONELINEITEMONANINVOICEACOLUMNCONTAINSALLTHEINFORMATIONOFASINGLETYPE,ANDTHEPIECEOFDATAATTHEINTERSECTIONOFAROWANDACOLUMN,AFIELD,ISTHESMALLESTPIECEOFINFORMATIONTHATCANBERETRIEVEDWITHTHEDATABASESQUERYLANGUAGEFOREXAMPLE,ATABLEWITHINFORMATIONABOUTEMPLOYEESMIGHTHAVEACOLUMNCALLEDLAST_NAMETHATCONTAINSALLOFTHEEMPLOYEESLASTNAMESDATAISRETRIEVEDFROMATABLEBYFILTERINGONBOTHTHEROWANDTHECOLUMNPRIMARYKEYS,DATATYPES,ANDFOREIGNKEYSTHEEXAMPLESTHROUGHOUTTHISARTICLEWILLFOCUSONTHEHYPOTHETICALWORKOFSCOTTSMITH,DATABASEDEVELOPERANDENTREPRENEURHEJUSTSTARTEDANEWWIDGETCOMPANYANDWANTSTOIMPLEMENTAFEWOFTHEBASICBUSINESSFUNCTIONSUSINGTHERELATIONALDATABASETOMANAGEHISHUMANRESOURCESHRDEPARTMENTRELATIONATWODIMENSIONALSTRUCTUREUSEDTOHOLDRELATEDINFORMATION,ALSOKNOWNASATABLENOTEMOSTOFSCOTTSEMPLOYEESWEREHIREDAWAYFROMONEOFHISPREVIOUSEMPLOYERS,SOMEOFWHOMHAVEOVER20YEARSOFEXPERIENCEINTHEFIELDASAHIRINGINCENTIVE,SCOTTHASAGREEDTOKEEPTHENEWEMPLOYEESORIGINALHIREDATEINTHENEWDATABASEROWAGROUPOFONEORMOREDATAELEMENTSINADATABASETABLETHATDESCRIBESAPERSON,PLACE,ORTHINGCOLUMNTHECOMPONENTOFADATABASETABLETHATCONTAINSALLOFTHEDATAOFTHESAMENAMEANDTYPEACROSSALLROWSYOULLLEARNABOUTDATABASEDESIGNINTHEFOLLOWINGSECTIONS,BUTLETSASSUMEFORTHEMOMENTTHATTHEMAJORITYOFTHEDATABASEDESIGNISCOMPLETEDANDSOMETABLESNEEDTOBEIMPLEMENTEDSCOTTCREATESTHEEMPTABLETOHOLDTHEBASICEMPLOYEEINFORMATION,ANDITLOOKSSOMETHINGLIKETHISEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO7369SMITHCLERK790217DEC80800207499ALLENSALESMAN769820FEB811600300307521WARDSALESMAN769822FEB811250500307566JONESMANAGER783902APR812975207839KINGPRESIDENT17NOV815000107902FORDANALYST756603DEC81300020NOTICETHATSOMEFIELDSINTHECOMMISSIONCOMMANDMANAGERMGRCOLUMNSDOESNOTCONTAINAVALUE；THEYAREBLANKARELATIONALDATABASECANENFORCETHERULETHATFIELDSINACOLUMNMAYORMAYNOTBEEMPTY3INTHISCASE,ITMAKESSENSEFORANEMPLOYEEWHOISNOTINTHESALESDEPARTMENTTOHAVEABLANKCOMMISSIONFIELDITALSOMAKESSENSEFORTHEPRESIDENTOFTHECOMPANYTOHAVEABLANKMANAGERFIELD,SINCETHATEMPLOYEEDOESNTREPORTTOANYONEFIELDTHESMALLESTPIECEOFINFORMATIONTHATCANBERETRIEVEDBYTHEDATABASEQUERYLANGUAGEAFIELDISFOUNDATTHEINTERSECTIONOFAROWANDACOLUMNINADATABASETABLEONTHEOTHERHAND,NONEOFTHEFIELDSINTHEEMPLOYEENUMBEREMPNOCOLUMNAREBLANKTHECOMPANYALWAYSWANTSTOASSIGNANEMPLOYEENUMBERTOANEMPLOYEE,ANDTHATNUMBERMUSTBEDIFFERENTFOREACHEMPLOYEEONEOFTHEFEATURESOFARELATIONALDATABASEISTHATITCANENSURETHATAVALUEISENTEREDINTOTHISCOLUMNANDTHATITISUNIQUETHEEMPNOCOLUMN,INTHISCASE,ISTHEPRIMARYKEYOFTHETABLEPRIMARYKEYACOLUMNORCOLUMNSINATABLETHATMAKESTHEROWINTHETABLEDISTINGUISHABLEFROMEVERYOTHERROWINTHESAMETABLENOTICETHEDIFFERENTDATATYPESTHATARESTOREDINTHEEMPTABLENUMERICVALUES,CHARACTERORALPHABETICVALUES,ANDDATEVALUESASYOUMIGHTSUSPECT,THEDEPTNOCOLUMNCONTAINSTHEDEPARTMENTNUMBERFORTHEEMPLOYEEBUTHOWDOYOUKNOWWHATDEPARTMENTNAMEISASSOCIATEDWITHWHATNUMBERSCOTTCREATEDTHEDEPTTABLETOHOLDTHEDESCRIPTIONSFORTHEDEPARTMENTCODESINTHEEMPTABLEDEPTNODNAMELOC10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONTHEDEPTNOCOLUMNINTHEEMPTABLECONTAINSTHESAMEVALUESASTHEDEPTNOCOLUMNINTHEDEPTTABLEINTHISCASE,THEDEPTNOCOLUMNINTHEEMPTABLEISCONSIDEREDAFOREIGNKEYTOTHESAMECOLUMNINTHEDEPTTABLEAFOREIGNKEYENFORCESTHECONCEPTOFREFERENTIALINTEGRITYINARELATIONALDATABASETHECONCEPTOFREFERENTIALINTEGRITYNOTONLYPREVENTSANINVALIDDEPARTMENTNUMBERFROMBEINGINSERTEDINTOTHEEMPTABLE,BUTITALSOPREVENTSAROWINTHEDEPTTABLEFROMBEINGDELETEDIFTHEREAREEMPLOYEESSTILLASSIGNEDTOTHATDEPARTMENTFOREIGNKEYACOLUMNORCOLUMNSINATABLETHATDRAWSITSVALUESFROMAPRIMARYORUNIQUEKEYCOLUMNINANOTHERTABLEAFOREIGNKEYASSISTSINENSURINGTHEDATAINTEGRITYOFATABLEREFERENTIALINTEGRITYAMETHODEMPLOYEDBYARELATIONALDATABASESYSTEMTHATENFORCESONETOMANYRELATIONSHIPSBETWEENTABLESDATAMODELINGBEFORESCOTTCREATEDTHEACTUALTABLESINTHEDATABASE,HEWENTTHROUGHADESIGNPROCESSKNOWNASDATAMODELINGINTHISPROCESS,THEDEVELOPERCONCEPTUALIZESANDDOCUMENTSALLTHETABLESFORTHEDATABASEONEOFTHECOMMONMETHODSFORMODELINGADATABASEISCALLEDERA,WHICHSTANDSFORENTITIES,RELATIONSHIPS,ANDATTRIBUTESTHEDATABASEDESIGNERUSESANAPPLICATIONTHATCANMAINTAINENTITIES,THEIRATTRIBUTES,ANDTHEIRRELATIONSHIPSINGENERAL,ANENTITYCORRESPONDSTOATABLEINTHEDATABASE,ANDTHEATTRIBUTESOFTHEENTITYCORRESPONDTOCOLUMNSOFTHETABLEDATAMODELINGAPROCESSOFDEFININGTHEENTITIES,ATTRIBUTES,ANDRELATIONSHIPSBETWEENTHEENTITIESINPREPARATIONFORCREATINGTHEPHYSICALDATABASETHEDATAMODELINGPROCESSINVOLVESDEFININGTHEENTITIES,DEFININGTHERELATIONSHIPSBETWEENTHOSEENTITIES,ANDTHENDEFININGTHEATTRIBUTESFOREACHOFTHEENTITIESONCEACYCLEISCOMPLETE,ITISREPEATEDASMANYTIMESASNECESSARYTOENSURETHATTHEDESIGNERISCAPTURINGWHATISIMPORTANTENOUGHTOGOINTOTHEDATABASELETSTAKEACLOSERLOOKATEACHSTEPINTHEDATAMODELINGPROCESSDEFININGTHEENTITIESFIRST,THEDESIGNERIDENTIFIESALLOFTHEENTITIESWITHINTHESCOPEOFTHEDATABASEAPPLICATIONTHEENTITIESARETHEPERSONS,PLACES,ORTHINGSTHATAREIMPORTANTTOTHEORGANIZATIONANDNEEDTOBETRACKEDINTHEDATABASEENTITIESWILLMOSTLIKELYTRANSLATENEATLYTODATABASETABLESFOREXAMPLE,FORTHEFIRSTVERSIONOFSCOTTSWIDGETCOMPANYDATABASE,HEIDENTIFIESFOURENTITIESEMPLOYEES,DEPARTMENTS,SALARYGRADES,ANDBONUSESTHESEWILLBECOMETHEEMP,DEPT,SALGRADE,ANDBONUSTABLESDEFININGTHERELATIONSHIPSBETWEENENTITIESONCETHEENTITIESAREDEFINED,THEDESIGNERCANPROCEEDWITHDEFININGHOWEACHOFTHEENTITIESISRELATEDOFTEN,THEDESIGNERWILLPAIREACHENTITYWITHEVERYOTHERENTITYANDASK,ISTHEREARELATIONSHIPBETWEENTHESETWOENTITIESSOMERELATIONSHIPSAREOBVIOUS；SOMEARENOTINTHEWIDGETCOMPANYDATABASE,THEREISMOSTLIKELYARELATIONSHIPBETWEENEMPANDDEPT,BUTDEPENDINGONTHEBUSINESSRULES,ITISUNLIKELYTHATTHEDEPTANDSALGRADEENTITIESARERELATEDIFTHEBUSINESSRULESWERETORESTRICTCERTAINSALARYGRADESTOCERTAINDEPARTMENTS,THEREWOULDMOSTLIKELYBEANEWENTITYTHATDEFINESTHERELATIONSHIPBETWEENSALARYGRADESANDDEPARTMENTSTHISENTITYWOULDBEKNOWNASANASSOCIATIVEORINTERSECTIONTABLEANDWOULDCONTAINTHEVALIDCOMBINATIONSOFSALARYGRADESANDDEPARTMENTSASSOCIATIVETABLEADATABASETABLETHATSTORESTHEVALIDCOMBINATIONSOFROWSFROMTWOOTHERTABLESANDUSUALLYENFORCESABUSINESSRULEANASSOCIATIVETABLERESOLVESAMANYTOMANYRELATIONSHIPINGENERAL,THEREARETHREETYPESOFRELATIONSHIPSINARELATIONALDATABASEONETOMANYTHEMOSTCOMMONTYPEOFRELATIONSHIPISONETOMANYTHISMEANSTHATFOREACHOCCURRENCEINAGIVENENTITY,THEPARENTENTITY,THEREMAYBEONEORMOREOCCURRENCESINASECONDENTITY,THECHILDENTITY,TOWHICHITISRELATEDFOREXAMPLE,INTHEWIDGETCOMPANYDATABASE,THEDEPTENTITYISAPARENTENTITY,ANDFOREACHDEPARTMENT,THERECOULDBEONEORMOREEMPLOYEESASSOCIATEDWITHTHATDEPARTMENTTHERELATIONSHIPBETWEENDEPTANDEMPISONETOMANYONETOONEINAONETOONERELATIONSHIP,AROWINATABLEISRELATEDTOONLYONEORNONEOFTHEROWSINASECONDTABLETHISRELATIONSHIPTYPEISOFTENUSEDFORSUBTYPINGFOREXAMPLE,ANEMPLOYEETABLEMAYHOLDTHEINFORMATIONCOMMONTOALLEMPLOYEES,WHILETHEFULLTIME,PARTTIME,ANDCONTRACTORTABLESHOLD
