SQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1InThisChaptercDatabaseSystems:AnOverviewcRelationalDatabaseSystemscDatabaseDesigncSyntaxConventionsRelationalDatabaseSystems:AnIntroductionChapter1Ch01.
indd31/24/124:39:21PM4MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Thischapterdescribesdatabasesystemsingeneral.
First,itdiscusseswhatadatabasesystemis,andwhichcomponentsitcontains.
Eachcomponentisdescribedbriefly,withareferencetothechapterinwhichitisdescribedindetail.
Thesecondmajorsectionofthechapterisdedicatedtorelationaldatabasesystems.
Itdiscussesthepropertiesofrelationaldatabasesystemsandthecorrespondinglanguageusedinsuchsystems—StructuredQueryLanguage(SQL).
Generally,beforeyouimplementadatabase,youhavetodesignit,withallitsobjects.
Thethirdmajorsectionofthechapterexplainshowyoucanusenormalformstoenhancethedesignofyourdatabase,andalsointroducestheentity-relationshipmodel,whichyoucanusetoconceptualizeallentitiesandtheirrelationships.
Thefinalsectionpresentsthesyntaxconventionsusedthroughoutthebook.
DatabaseSystems:AnOverviewAdatabasesystemisanoverallcollectionofdifferentdatabasesoftwarecomponentsanddatabasescontainingthefollowingparts:DatabaseapplicationprogramsCCClientcomponentsCCDatabaseserver(s)CCDatabasesCCAdatabaseapplicationprogramisspecial-purposesoftwarethatisdesignedandimplementedbyusersorbythird-partysoftwarecompanies.
Incontrast,clientcomponentsaregeneral-purposedatabasesoftwaredesignedandimplementedbyadatabasecompany.
Byusingclientcomponents,userscanaccessdatastoredonthesameoraremotecomputer.
Thetaskofadatabaseserveristomanagedatastoredinadatabase.
Eachclientcommunicateswithadatabaseserverbysendinguserqueriestoit.
Theserverprocesseseachqueryandsendstheresultbacktotheclient.
Ingeneral,adatabasecanbeviewedfromtwoperspectives,theusers'andthedatabasesystem's.
Usersviewadatabaseasacollectionofdatathatlogicallybelongtogether.
Foradatabasesystem,adatabaseissimplyaseriesofbytes,usuallystoredonadisk.
Althoughthesetwoviewsofadatabasearetotallydifferent,theydohavesomethingincommon:thedatabasesystemneedstoprovidenotonlyinterfacesthatenableuserstocreatedatabasesandretrieveormodifydata,butalsosystemcomponentstomanagethestoreddata.
Hence,adatabasesystemmustprovidethefollowingfeatures:Ch01.
indd41/24/124:39:21PMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Chapter1:RelationalDatabaseSystems:AnIntroduction5VarietyofuserinterfacesCCPhysicaldataindependenceCCLogicaldataindependenceCCQueryoptimizationCCDataintegrityCCConcurrencycontrolCCBackupandrecoveryCCDatabasesecurityCCThefollowingsectionsbrieflydescribethesefeatures.
VarietyofUserInterfacesMostdatabasesaredesignedandimplementedforusebymanydifferenttypesofuserswithvariedlevelsofknowledge.
Forthisreason,adatabasesystemshouldoffermanydistinctuserinterfaces.
Auserinterfacecanbeeithergraphicalortextual.
Graphicaluserinterfaces(GUIs)acceptuser'sinputviathekeyboardormouseandcreategraphicaloutputonthemonitor.
Aformoftextualinterface,whichisoftenusedbydatabasesystems,isthecommand-lineinterface(CLI),wheretheuserprovidestheinputbytypingacommandwiththekeyboardandthesystemprovidesoutputbyprintingtextonthecomputermonitor.
PhysicalDataIndependencePhysicaldataindependencemeansthatthedatabaseapplicationprogramsdonotdependonthephysicalstructureofthestoreddatainadatabase.
Thisimportantfeatureenablesyoutomakechangestothestoreddatawithouthavingtomakeanychangestodatabaseapplicationprograms.
Forexample,ifthestoreddataispreviouslyorderedusingonecriterion,andthisorderischangedusinganothercriterion,themodificationofthephysicaldatashouldnotaffecttheexistingdatabaseapplicationsortheexistingdatabaseschema(adescriptionofadatabasegeneratedbythedatadefinitionlanguageofthedatabasesystem).
LogicalDataIndependenceInfileprocessing(usingtraditionalprogramminglanguages),thedeclarationofafileisdoneinapplicationprograms,soanychangetothestructureofthatfileusuallyrequiresthemodificationofallprogramsusingit.
DatabasesystemsprovidelogicaldataCh01.
indd51/24/124:39:21PM6MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1independence—inotherwords,itispossibletomakechangestothelogicalstructureofthedatabasewithouthavingtomakeanychangestothedatabaseapplicationprograms.
Forexample,ifthestructureofanobjectnamedPERSONexistsinthedatabasesystemandyouwanttoaddanattributetoPERSON(saytheaddress),youhavetomodifyonlythelogicalstructureofthedatabase,andnottheexistingapplicationprograms.
(Theapplicationwouldhavetobemodifiedtoutilizethenewlyaddedcolumn.
)QueryOptimizationMostdatabasesystemscontainasubcomponentcalledoptimizerthatconsidersavarietyofpossibleexecutionstrategiesforqueryingthedataandthenselectsthemostefficientone.
Theselectedstrategyiscalledtheexecutionplanofthequery.
Theoptimizermakesitsdecisionsusingconsiderationssuchashowbigthetablesarethatareinvolvedinthequery,whatindicesexist,andwhatBooleanoperator(AND,OR,orNOT)isusedintheWHEREclause.
(ThistopicisdiscussedindetailinChapter19.
)DataIntegrityOneofthetasksofadatabasesystemistoidentifylogicallyinconsistentdataandrejectitsstorageinadatabase.
(ThedateFebruary30andthetime5:77:00p.
m.
aretwoexamplesofsuchdata.
)Additionally,mostreal-lifeproblemsthatareimplementedusingdatabasesystemshaveintegrityconstraintsthatmustholdtrueforthedata.
(Oneexampleofanintegrityconstraintmightbethecompany'semployeenumber,whichmustbeafive-digitinteger.
)ThetaskofmaintainingintegritycanbehandledbytheuserinapplicationprogramsorbytheDBMS.
Asmuchaspossible,thistaskshouldbehandledbytheDBMS.
(Dataintegrityisdiscussedintwochaptersofthisbook:declarativeintegrityinChapter5andproceduralintegrityinChapter14.
)ConcurrencyControlAdatabasesystemisamultiusersoftwaresystem,meaningthatmanyuserapplicationsaccessadatabaseatthesametime.
Therefore,eachdatabasesystemmusthavesomekindofcontrolmechanismtoensurethatseveralapplicationsthataretryingtoupdatethesamedatadosoinsomecontrolledway.
Thefollowingisanexampleofaproblemthatcanariseifadatabasesystemdoesnotcontainsuchcontrolmechanisms:Theownersofbankaccount4711atbankXhaveanaccountbalanceof$2000.
1.
Thetwojointownersofthisbankaccount,Mrs.
AandMr.
B,gototwodifferent2.
banktellers,andeachwithdraws$1000atthesametime.
Afterthesetransactions,theamountofmoneyinbankaccount4711shouldbe3.
$0andnot$1000.
Ch01.
indd61/24/124:39:21PMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Chapter1:RelationalDatabaseSystems:AnIntroduction7Alldatabasesystemshavethenecessarymechanismstohandlecaseslikethisexample.
ConcurrencycontrolisdiscussedindetailinChapter13.
BackupandRecoveryAdatabasesystemmusthaveasubsystemthatisresponsibleforrecoveryfromhardwareorsoftwareerrors.
Forexample,ifafailureoccurswhileadatabaseapplicationupdates100rowsofatable,therecoverysubsystemmustrollbackallpreviouslyexecutedupdatestoensurethatthecorrespondingdataisconsistentaftertheerroroccurs.
(SeeChapter16forfurtherdiscussiononbackupandrecovery.
)DatabaseSecurityThemostimportantdatabasesecurityconceptsareauthenticationandauthorization.
Authenticationistheprocessofvalidatingusercredentialstopreventunauthorizedusersfromusingasystem.
Authenticationismostcommonlyenforcedbyrequiringtheusertoentera(user)nameandapassword.
Thisinformationisevaluatedbythesystemtodeterminewhethertheuserisallowedtoaccessthesystem.
Thisprocesscanbestrengthenedbyusingencryption.
Authorizationistheprocessthatisappliedaftertheidentityofauserisauthenticated.
Duringthisprocess,thesystemdetermineswhatresourcestheparticularusercanuse.
Inotherwords,structuralandsystemcataloginformationaboutaparticularentityisnowavailableonlytoprincipalsthathavepermissiontoaccessthatentity.
(Chapter12discussestheseconceptsindetail.
)RelationalDatabaseSystemsThecomponentofMicrosoftSQLServercalledtheDatabaseEngineisarelationaldatabasesystem.
ThenotionofrelationaldatabasesystemswasfirstintroducedbyE.
F.
Coddinhisarticle"ARelationalModelofDataforLargeSharedDataBanks"in1970.
Incontrasttoearlierdatabasesystems(networkandhierarchical),relationaldatabasesystemsarebasedupontherelationaldatamodel,whichhasastrongmathematicalbackground.
NoteAdatamodelisacollectionofconcepts,theirrelationships,andtheirconstraintsthatareusedtorepresentdataofareal-worldproblem.
Thecentralconceptoftherelationaldatamodelisarelation—thatis,atable.
Therefore,fromtheuser'spointofview,arelationaldatabasecontainstablesandCh01.
indd71/24/124:39:21PM8MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1nothingbuttables.
Inatable,thereareoneormorecolumnsandzeroormorerows.
Ateveryrowandcolumnpositioninatablethereisalwaysexactlyonedatavalue.
WorkingwiththeBook'sSampleDatabaseThesampledatabaseusedinthisbookrepresentsacompanywithdepartmentsandemployees.
Eachemployeeintheexamplebelongstoexactlyonedepartment,whichitselfhasoneormoreemployees.
Jobsofemployeescenteronprojects:eachemployeeworksatthesametimeononeormoreprojects,andeachprojectengagesoneormoreemployees.
Thedataofthesampledatabasecanberepresentedusingfourtables:departmentCCemployeeCCprojectCCworks_onCCTables1-1through1-4showallthetablesofthesampledatabase.
Thedepartmenttablerepresentsalldepartmentsofthecompany.
Eachdepartmenthasthefollowingattributes:department(dept_no,dept_name,location)dept_norepresentstheuniquenumberofeachdepartment.
dept_nameisitsname,andlocationisthelocationofthecorrespondingdepartment.
Theemployeetablerepresentsallemployeesworkingforacompany.
Eachemployeehasthefollowingattributes:employee(emp_no,emp_fname,emp_lname,dept_no)Table1-1TheDepartmentTabledept_nodept_namelocationd1ResearchDallasd2AccountingSeattled3MarketingDallasCh01.
indd81/24/124:39:21PMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Chapter1:RelationalDatabaseSystems:AnIntroduction9emp_noemp_fnameemp_lnamedept_no25348MatthewSmithd310102AnnJonesd318316JohnBarrimored129346JamesJamesd29031ElkeHanseld22581ElsaBertonid228559SybillMoserd1Table1-2TheEmployeeTableemp_noproject_nojobenter_date10102p1Analyst2006.
10.
110102p3Manager2008.
1.
125348p2Clerk2007.
2.
1518316p2NULL2007.
6.
129346p2NULL2006.
12.
152581p3Analyst2007.
10.
159031p1Manager2007.
4.
1528559p1NULL2007.
8.
128559p2Clerk2008.
2.
19031p3Clerk2006.
11.
1529346p1Clerk2007.
1.
4Table1-4Theworks_onTableproject_noproject_namebudgetp1Apollo120000p2Gemini95000p3Mercury186500Table1-3TheProjectTableCh01.
indd91/24/124:39:21PM10MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1emp_norepresentstheuniquenumberofeachemployee.
emp_fnameandemp_lnamearethefirstandlastnameofeachemployee,respectively.
Finally,dept_noisthenumberofthedepartmenttowhichtheemployeebelongs.
Eachprojectofacompanyisrepresentedintheprojecttable.
Thistablehasthefollowingcolumns:project(project_no,project_name,budget)project_norepresentstheuniquenumberofeachproject.
project_nameandbudgetspecifythenameandthebudgetofeachproject,respectively.
Theworks_ontablespecifiestherelationshipbetweenemployeesandprojects.
Ithasthefollowingcolumns:works_on(emp_no,project_no,job,enter_date)emp_nospecifiestheemployeenumberandproject_nospecifiesthenumberoftheprojectonwhichtheemployeeworks.
Thecombinationofdatavaluesbelongingtothesetwocolumnsisalwaysunique.
jobandenter_datespecifythetaskandthestartingdateofanemployeeinthecorrespondingproject,respectively.
Usingthesampledatabase,itispossibletodescribesomegeneralpropertiesofrelationaldatabasesystems:Rowsinatabledonothaveanyparticularorder.
CCColumnsinatabledonothaveanyparticularorder.
CCEverycolumnmusthaveauniquenamewithinatable.
Ontheotherhand,CCcolumnsfromdifferenttablesmayhavethesamename.
(Forexample,thesampledatabasehasadept_nocolumninthedepartmenttableandacolumnwiththesamenameintheemployeetable.
)Everysingledataiteminthetablemustbesinglevalued.
ThismeansthatineveryCCrowandcolumnpositionofatablethereisneverasetofmultipledatavalues.
Foreverytable,thereisatleastonecolumnwiththepropertythatnotworowsCChavethesamecombinationofdatavaluesforalltablecolumns.
Intherelationaldatamodel,suchanidentifieriscalledacandidatekey.
Ifthereismorethanonecandidatekeywithinatable,thedatabasedesignerdesignatesoneofthemastheprimarykeyofthetable.
Forexample,thecolumndept_noistheprimarykeyofthedepartmenttable;thecolumnsemp_noandproject_noaretheprimarykeysofthetablesemployeeandproject,respectively.
Finally,theprimarykeyfortheworks_ontableisthecombinationofthecolumnsemp_no,project_no.
Ch01.
indd101/24/124:39:21PMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Chapter1:RelationalDatabaseSystems:AnIntroduction11Inatable,therearenevertwoidenticalrows.
(Thispropertyisonlytheoretical;CCtheDatabaseEngineandallotherrelationaldatabasesystemsgenerallyallowtheexistenceofidenticalrowswithinatable.
)SQL:ARelationalDatabaseLanguageTheSQLServerrelationaldatabaselanguageiscalledTransact-SQL.
Itisadialectofthemostimportantdatabaselanguagetoday:StructuredQueryLanguage(SQL).
TheoriginofSQLiscloselyconnectedwiththeprojectcalledSystemR,whichwasdesignedandimplementedbyIBMintheearly1980s.
Thisprojectshowedthatitispossible,usingthetheoreticalfoundationsoftheworkofE.
F.
Codd,tobuildarelationaldatabasesystem.
IncontrasttotraditionallanguageslikeC,C++,andJava,SQLisaset-orientedlanguage.
(Theformerarealsocalledrecord-orientedlanguages.
)ThismeansthatSQLcanquerymanyrowsfromoneormoretablesusingjustonestatement.
ThisfeatureisoneofthemostimportantadvantagesofSQL,allowingtheuseofthislanguageatalogicallyhigherlevelthanthelevelatwhichtraditionallanguagescanbeused.
AnotherimportantpropertyofSQLisitsnonprocedurality.
Everyprogramwritteninaprocedurallanguage(C,C++,Java)describeshowataskisaccomplished,stepbystep.
Incontrasttothis,SQL,asanyothernonprocedurallanguage,describeswhatitisthattheuserwants.
Thus,thesystemisresponsibleforfindingtheappropriatewaytosolveusers'requests.
SQLcontainstwosublanguages:adatadefinitionlanguage(DDL)andadatamanipulationlanguage(DML).
DDLstatementsareusedtodescribetheschemaofdatabasetables.
TheDDLcontainsthreegenericSQLstatements:CREATEobject,ALTERobject,andDROPobject.
Thesestatementscreate,alter,andremovedatabaseobjects,suchasdatabases,tables,columns,andindexes.
(ThesestatementsarediscussedindetailinChapter5.
)IncontrasttotheDDL,theDMLencompassesalloperationsthatmanipulatethedata.
Therearealwaysfourgenericoperationsformanipulatingthedatabase:retrieval,insertion,deletion,andmodification.
TheretrievalstatementSELECTisdescribedinChapter6,whiletheINSERT,DELETE,andUPDATEstatementsarediscussedindetailinChapter7.
DatabaseDesignDesigningadatabaseisaveryimportantphaseinthedatabaselifecycle,whichprecedesallotherphasesexcepttherequirementscollectionandtheanalysis.
Ifthedatabasedesigniscreatedmerelyintuitivelyandwithoutanyplan,theresultingdatabasewillmostlikelynotmeettheuserrequirementsconcerningperformance.
Ch01.
indd111/24/124:39:21PM12MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Anotherconsequenceofabaddatabasedesignissuperfluousdataredundancy,whichinitselfhastwodisadvantages:theexistenceofdataanomaliesandtheuseofanunnecessaryamountofdiskspace.
Normalizationofdataisaprocessduringwhichtheexistingtablesofadatabasearetestedtofindcertaindependenciesbetweenthecolumnsofatable.
Ifsuchdependenciesexist,thetableisrestructuredintomultiple(usuallytwo)tables,whicheliminatesanycolumndependencies.
Ifoneofthesegeneratedtablesstillcontainsdatadependencies,theprocessofnormalizationmustberepeateduntilalldependenciesareresolved.
Theprocessofeliminatingdataredundancyinatableisbaseduponthetheoryoffunctionaldependencies.
Afunctionaldependencymeansthatbyusingtheknownvalueofonecolumn,thecorrespondingvalueofanothercolumncanalwaysbeuniquelydetermined.
(Thesameistrueforcolumngroups.
)ThefunctionaldependenciesbetweencolumnsAandBisdenotedbyAB,specifyingthatavalueofcolumnAcanalwaysbeusedtodeterminethecorrespondingvalueofcolumnB.
("BisfunctionallydependentonA.
")Example1.
1showsthefunctionaldependencybetweentwoattributesofthetableemployeeinthesampledatabase.
Example1.
1emp_noemp_lnameByhavingauniquevaluefortheemployeenumber,thecorrespondinglastnameoftheemployee(andallothercorrespondingattributes)canbedetermined.
Thiskindoffunctionaldependency,whereacolumnisdependentupontheprimarykeyofatable,iscalledtrivialfunctionaldependency.
Anotherkindoffunctionaldependencyiscalledmultivalueddependency.
Incontrasttothefunctionaldependencyjustdescribed,themultivalueddependencyisspecifiedformultivaluedattributes.
Thismeansthatbyusingtheknownvalueofoneattribute(column),thecorrespondingsetofvaluesofanothermultivaluedattributecanbeuniquelydetermined.
Themultivalueddependencyisdenotedby.
Example1.
2showsthemultivalueddependencythatholdsfortwoattributesoftheobjectBOOK.
Example1.
2ISBNAuthorsTheISBNofabookalwaysdeterminesallofitsauthors.
Therefore,theAuthorsattributeismultivalueddependentontheISBNattribute.
Ch01.
indd121/24/124:39:21PMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Chapter1:RelationalDatabaseSystems:AnIntroduction13NormalFormsNormalformsareusedfortheprocessofnormalizationofdataandthereforeforthedatabasedesign.
Intheory,thereareatleastfivedifferentnormalforms,ofwhichthefirstthreearethemostimportantforpracticaluse.
Thethirdnormalformforatablecanbeachievedbytestingthefirstandsecondnormalformsattheintermediatestates,andassuch,thegoalofgooddatabasedesigncanusuallybefulfilledifalltablesofadatabaseareinthethirdnormalform.
NoteThemultivalueddependencyisusedtotestthefourthnormalformofatable.
Therefore,thiskindofdependencywillnotbeusedfurtherinthisbook.
FirstNormalFormFirstnormalform(1NF)meansthatatablehasnomultivaluedattributesorcompositeattributes.
(Acompositeattributecontainsotherattributesandcanthereforebedividedintosmallerparts.
)Allrelationaltablesarebydefinitionin1NF,becausethevalueofanycolumninarowmustbeatomic—thatis,singlevalued.
Table1-5demonstrates1NFusingpartoftheworks_ontablefromthesampledatabase.
Therowsoftheworks_ontablecouldbegroupedtogether,usingtheemployeenumber.
TheresultingTable1-6isnotin1NFbecausethecolumnproject_nocontainsasetofvalues(p1,p3).
SecondNormalFormAtableisinsecondnormalform(2NF)ifitisin1NFandthereisnononkeycolumndependentonapartialprimarykeyofthattable.
Thismeansif(A,B)isacombinationemp_noproject_no10102p110102p3Table1-5Partoftheworks_onTableCh01.
indd131/24/124:39:21PM14MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1oftwotablecolumnsbuildingthekey,thenthereisnocolumnofthetabledependingeitherononlyAoronlyB.
Forexample,Table1-7showstheworks_on1table,whichisidenticaltotheworks_ontableexceptfortheadditionalcolumn,dept_no.
Theprimarykeyofthistableisthecombinationofcolumnsemp_noandproject_no.
Thecolumndept_noisdependentonthepartialkeyemp_no(andisindependentofproject_no),sothistableisnotin2NF.
(Theoriginaltable,works_on,isin2NF.
)NoteEverytablewithaone-columnprimarykeyisalwaysin2NF.
ThirdNormalFormAtableisinthirdnormalform(3NF)ifitisin2NFandtherearenofunctionaldependenciesbetweennonkeycolumns.
Forexample,theemployee1table(seeTable1-8),whichisidenticaltotheemployeetableexceptfortheadditionalcolumn,dept_name,isnotin3NF,becauseforeveryknownvalueofthecolumndept_nothecorrespondingvalueofthecolumndept_namecanbeuniquelydetermined.
(Theoriginaltable,employee,aswellasallothertablesofthesampledatabasearein3NF.
)emp_noproject_no10102(p1,p3)Table1-6This"Table"IsNotin1NFemp_noproject_nojobenter_datedept_no10102p1Analyst2006.
10.
1d310102p3Manager2008.
1.
1d325348p2Clerk2007.
2.
15d318316p2NULL2007.
6.
1d1Table1-7Theworks_on1TableCh01.
indd141/24/124:39:22PMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Chapter1:RelationalDatabaseSystems:AnIntroduction15Entity-RelationshipModelThedatainadatabasecouldeasilybedesignedusingonlyonetablethatcontainsalldata.
Themaindisadvantageofsuchadatabasedesignisitshighredundancyofdata.
Forexample,ifyourdatabasecontainsdataconcerningemployeesandtheirprojects(assumingeachemployeeworksatthesametimeononeormoreprojects,andeachprojectengagesoneormoreemployees),thedatastoredinasingletablecontainsmanycolumnsandrows.
Themaindisadvantageofsuchatableisthatdataisdifficulttokeepconsistentbecauseofitsredundancy.
Theentity-relationship(ER)modelisusedtodesignrelationaldatabasesbyremovingallexistingredundancyinthedata.
ThebasicobjectoftheERmodelisanentity—thatis,areal-worldobject.
Eachentityhasseveralattributes,whicharepropertiesoftheentityandthereforedescribeit.
Basedonitstype,anattributecanbeAtomic(orsinglevalued)CCAnatomicattributeisalwaysrepresentedbyasinglevalueforaparticularentity.
Forexample,aperson'smaritalstatusisalwaysanatomicattribute.
Mostattributesareatomicattributes.
MultivaluedCCAmultivaluedattributemayhaveoneormorevaluesforaparticularentity.
Forexample,LocationastheattributeofanentitycalledENTERPRISEismultivalued,becauseeachenterprisecanhaveoneormorelocations.
CompositeCCCompositeattributesarenotatomicbecausetheyareassembledusingsomeotheratomicattributes.
Atypicalexampleofacompositeattributeisaperson'saddress,whichiscomposedofatomicattributes,suchasCity,Zip,andStreet.
TheentityPERSONinExample1.
3hasseveralatomicattributes,onecompositeattribute,Address,andamultivaluedattribute,College_degree.
emp_noemp_fnameemp_lnamedept_nodept_name25348MatthewSmithd3Marketing10102AnnJonesd3Marketing18316JohnBarrimored1Research29346JamesJamesd2AccountingTable1-8Theemployee1TableCh01.
indd151/24/124:39:22PM16MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Example1.
3PERSON(Personal_no,F_name,L_name,Address(City,Zip,Street),{College_degree})Eachentityhasoneormorekeyattributesthatareattributes(oracombinationoftwoormoreattributes)whosevaluesareuniqueforeachparticularentity.
InExample1.
3,theattributePersonal_noisthekeyattributeoftheentityPERSON.
Besidesentityandattribute,relationshipisanotherbasicconceptoftheERmodel.
Arelationshipexistswhenanentityreferstoone(ormore)otherentities.
Thenumberofparticipatingentitiesdefinesthedegreeofarelationship.
Forexample,therelationshipworks_onbetweenentitiesEMPLOYEEandPROJECThasdegreetwo.
Everyexistingrelationshipbetweentwoentitiesmustbeoneofthefollowingthreetypes:1:1,1:N,orM:N.
(Thispropertyofarelationshipisalsocalledcardinalityratio.
)Forexample,therelationshipbetweentheentitiesDEPARTMENTandEMPLOYEEis1:N,becauseeachemployeebelongstoexactlyonedepartment,whichitselfhasoneormoreemployees.
Also,therelationshipbetweentheentitiesPROJECTandEMPLOYEEisM:N,becauseeachprojectengagesoneormoreemployeesandeachemployeeworksatthesametimeononeormoreprojects.
Arelationshipcanalsohaveitsownattributes.
Figure1-1showsanexampleofanERdiagram.
(TheERdiagramisthegraphicalnotationusedtodescribetheERmodel.
)Figure1-1ExampleofanERdiagramproject_noproject_nameworks_onBudgetdept_nodept_nameLocationPROJECTEMPLOYEENMN1enter_datef_namel_nameemployee_noworks_forDEPARTMENTJobCh01.
indd161/24/124:39:22PMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Chapter1:RelationalDatabaseSystems:AnIntroduction17Usingthisnotation,entitiesaremodeledusingrectangularboxes,withtheentitynamewritteninsidethebox.
Attributesareshowninovals,andeachattributeisattachedtoaparticularentity(orrelationship)usingastraightline.
Finally,relationshipsaremodeledusingdiamonds,andentitiesparticipatingintherelationshipareattachedtoitusingstraightlines.
Thecardinalityratioofeachentityiswrittenonthecorrespondingline.
SyntaxConventionsThisbookusestheconventionsshowninTable1-9forthesyntaxoftheTransact-SQLstatementsandfortheindicationofthetext.
NoteIncontrasttobracketsandbraces,whichbelongtosyntaxconventions,parentheses,(),belongtothesyntaxofastatementandmustalwaysbetyped!
ConventionIndicationItalicsNewtermsoritemsofemphasis.
UPPERCASETransact-SQLkeywords—forexample,CREATETABLE.
AdditionalinformationaboutthekeywordsoftheTransact-SQLlanguagecanbefoundinChapter5.
lowercaseVariablesinTransact-SQLstatements—forexample,CREATETABLEtablename.
(Theusermustreplace"tablename"withtheactualnameofthetable.
)var1|var2Alternativeuseoftheitemsvar1andvar2.
(Youmaychooseonlyoneoftheitemsseparatedbytheverticalbar.
){}Alternativeuseofmoreitems.
Example:{expression|USER|NULL}[]Optionalitem(s).
Example:[FORLOAD]Item(s)thatcanberepeatedanynumberoftimes.
Example:{,@param1typ1}…boldNameofdatabaseobject(databaseitself,tables,columns)inthetext.
DefaultThedefaultvalueisalwaysunderlined.
Example:ALL|DISTINCTTable1-9SyntaxConventionsCh01.
indd171/24/124:39:22PM18MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1SummaryAlldatabasesystemsprovidethefollowingfeatures:VarietyofuserinterfacesCCPhysicaldataindependenceCCLogicaldataindependenceCCQueryoptimizationCCDataintegrityCCConcurrencycontrolCCBackupandrecoveryCCDatabasesecurityCCThenextchaptershowsyouhowtoinstallSQLServer2012.
ExercisesE.
1.
1Whatdoes"dataindependence"meanandwhichtwoformsofdataindependenceexistE.
1.
2WhichisthemainconceptoftherelationalmodelE.
1.
3WhatdoestheemployeetablerepresentintherealworldAndwhatdoestherowinthistablewiththedataforAnnJonesrepresentE.
1.
4Whatdoestheworks_ontablerepresentintherealworld(andinrelationtotheothertablesofthesampledatabase)E.
1.
5Letbookbeatablewithtwocolumns:isbnandtitle.
Assumingthatisbnisuniqueandtherearenoidenticaltitles,answerthefollowingquestions:Ch01.
indd181/24/124:39:22PMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter1Chapter1:RelationalDatabaseSystems:AnIntroduction19Isa.
titleakeyofthetableDoesb.
isbnfunctionallydependontitleIsthec.
booktablein3NFE.
1.
6Letorderbeatablewiththefollowingcolumns:order_no,customer_no,discount.
Ifthecolumncustomer_noisfunctionallydependentonorder_noandthecolumndiscountisfunctionallydependentoncustomer_no,answerthefollowingquestionsandexplainindetailyouranswers:Isa.
order_noakeyofthetableIsb.
customer_noakeyofthetableE.
1.
7Letcompanybeatablewiththefollowingcolumns:company_no,location.
Eachcompanyhasoneormorelocations.
InwhichnormalformisthecompanytableE.
1.
8Letsupplierbeatablewiththefollowingcolumns:supplier_no,article,city.
Thekeyofthetableisthecombinationofthefirsttwocolumns.
Eachsupplierdeliversseveralarticles,andeacharticleisdeliveredbyseveralsuppliers.
Thereisonlyonesupplierineachcity.
Answerthefollowingquestions:Inwhichnormalformisthea.
suppliertableHowcanyouresolvetheexistingfunctionaldependenciesb.
E.
1.
9LetR(A,B,C)bearelationwiththefunctionaldependencyBC.
(TheunderlinedattributesAandBbuildthecompositekey,andtheattributeCisfunctionallydependentonB.
)InwhichnormalformistherelationRE.
1.
10LetR(A,B,C)bearelationwiththefunctionaldependencyCB.
(TheunderlinedattributesAandBbuildthecompositekey,andtheattributeBisfunctionallydependentonC.
)InwhichnormalformistherelationRCh01.
indd191/24/124:39:22PM
Moack怎么样?Moack(蘑菇主机)是一家成立于2016年的商家,据说是国人和韩国合资开办的主机商家,目前主要销售独立服务器,机房位于韩国MOACK机房,网络接入了kt/lg/kinx三条线路,目前到中国大陆的速度非常好,国内Ping值平均在45MS左右,而且商家的套餐比较便宜,针对国人有很多活动。不过目前如果购买机器如需现场处理,由于COVID-19越来越严重,MOACK办公楼里的人也被感染...
六一云互联六一云互联为西安六一网络科技有限公司的旗下产品。是一个正规持有IDC/ISP/CDN的国内公司,成立于2018年,主要销售海外高防高速大带宽云服务器/CDN,并以高质量.稳定性.售后相应快.支持退款等特点受很多用户的支持!近期公司也推出了很多给力的抽奖和折扣活动如:新用户免费抽奖,最大可获得500元,湖北新购六折续费八折折上折,全场八折等等最新活动:1.湖北100G高防:新购六折续费八折...
国庆钜惠 最低5折起 限量促销CYUN专注海外精品服务器资源,主营香港CN2 GIA、美国CERA、美国高防服务器资源,实体公司,ISP/IDC资质齐全,客服配备齐全。本次针对国庆推出非常给力的促销活动,旗下所有平台同享,新老客户同享,限时限量,售完截止。活动截止时间:2021年10月9日官网地址:www.cyun.net参与机型:香港CN2 GIA云服务器、香港双程CN2云服...
sqlserver2012为你推荐
沙滩捡12块石头价值近百万朋友从内蒙古阿拉善那边的戈壁捡了很多石头,求大神们鉴定一下,据说那边产玛瑙。谢谢大神们,大大的悬赏美国互联网瘫痪美国网络大瘫痪到底是怎么发生的地陷裂口造成地陷都有哪些原因?钟神发跪求钟神发名言出处,A站大神看过来www.haole012.com阜阳有什么好的正规的招聘网站?avtt4.comwww.51kao4.com为什么进不去啊?99nets.com99nets网游模拟娱乐社区怎么打不开了?????????谁能告诉我 ???、www.idanmu.com腾讯有qqsk.zik.mu这个网站吗?www.ijinshan.com驱动人生是电脑自带的还是要安装啊!?在哪里呢?没有找到ww.66bobo.com这个WWW ̄7222hh ̄com是不是真的不太易开了,换了吗?
上海域名注册 动态ip的vps 三级域名网站 中文域名交易中心 arvixe 美国主机代购 iis安装教程 debian6 qq数据库 网站挂马检测工具 免费mysql 老左正传 中国电信宽带测速器 免费asp空间 后门 windowssever2008 godaddy域名 cloudflare weblogic部署 电信测速器在线测网速 更多