holdsqlserver2012

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