Instant Download Oracle 12c: sql 3rd edition, (ebook pdf) PDF All Chapters by Education Libraries

https://ebookmass.com/product/oracle-12c-sql-3rd-edition-

Instant digital products (PDF, ePub, MOBI) ready for you

Download now and discover formats that fit your needs...

Oracle SOA Suite 12c handbook 1st Edition Jellema

https://ebookmass.com/product/oracle-soa-suite-12c-handbook-1stedition-jellema/

ebookmass.com

Oracle PL/SQL by Example, 6th Edition Benjamin Rosenzweig

https://ebookmass.com/product/oracle-pl-sql-by-example-6th-editionbenjamin-rosenzweig/

ebookmass.com

OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) 1st Edition Steve O’Hearn

https://ebookmass.com/product/oca-oracle-database-sql-exam-guideexam-1z0-071-1st-edition-steve-ohearn/

ebookmass.com

Atmospheric Thermodynamics, 2nd Edition Craig Bohren

https://ebookmass.com/product/atmospheric-thermodynamics-2nd-editioncraig-bohren/

ebookmass.com

(eTextbook PDF) for Comparative Politics 2nd Edition by

David J. Samuels

https://ebookmass.com/product/etextbook-pdf-for-comparativepolitics-2nd-edition-by-david-j-samuels/

ebookmass.com

Study in Color (Hunting Grounds Book 5) Nichole Severn

https://ebookmass.com/product/study-in-color-hunting-groundsbook-5-nichole-severn/

ebookmass.com

Tell Me I’m Worthless Alison Rumfitt

https://ebookmass.com/product/tell-me-im-worthless-alison-rumfitt/

ebookmass.com

National Identity in 21st-Century Cuban Cinema: Screening the Repeating Island 1st ed. Edition Dunja Fehimovi■

https://ebookmass.com/product/national-identity-in-21st-century-cubancinema-screening-the-repeating-island-1st-ed-edition-dunja-fehimovic/

ebookmass.com

Concept-Based Clinical Nursing Skills: Fundamental to Advanced 1st Edition Stein

https://ebookmass.com/product/concept-based-clinical-nursing-skillsfundamental-to-advanced-1st-edition-stein/

ebookmass.com

https://ebookmass.com/product/outsourcing-repression-everyday-statepower-in-contemporary-china-lynette-h-ong-2/

ebookmass.com

Thepastfewdecadeshaveseenaproliferationoforganizationsthatrelyheavilyon informationtechnology.Theseorganizationsstoretheirdataindatabases,andmany chooseOracledatabasemanagementsystemstoaccesstheirdata.ThecurrentOracle databaseversion,Oracle12c, isadatabasemanagementsystemthatenablesusersto create,manipulate,andretrievedata.Thepurposeofthistextbookistointroduce studentstobasicSQLcommandsforinteractingwithOracle12c databasesinabusiness environment.Inaddition,conceptsrelatingtoobjectivesofthecurrentOracle certificationexamshavebeenincorporatedforstudentswantingtopursuecertification.

TheIntendedAudience

Thistextbookhasbeendesignedforstudentsintechnicaltwo-yearorfour-yearprograms whoneedtolearnhowtointeractwithdatabases.Althoughhavinganunderstandingof databasedesignispreferable,anintroductorychapterhasbeenincludedtoreviewthe basicconceptsofE-Rmodelingandthenormalizationprocess.

OracleCertificationProgram(OCP)

ThistextbookcoverstheobjectivesofSQLFundamentalsExamsavailable:1Z0-051for Oracle11g andExam1Z0-061forOracleDatabase12c.MostobjectivesforExam1Z0047,OracleDatabaseSQLExpert,arealsocovered.Anyoftheseexamsserveasthefirst examintheOracleApplicationDevelopmentandOracleDatabaseAdministratorOracle CertifiedAssociatelevelcertificationtracks.Informationaboutregisteringforthese exams,alongwithotherreferencematerial,isavailableat www.oracle.com

TheApproach

Theconceptsintroducedinthistextbookarediscussedinthecontextofahypothetical real-worldbusiness:anonlinebookretailernamedJustLeeBooks.Thecompany’ s businessoperationandthedatabasestructureareintroducedandanalyzed,andas commandsareintroducedthroughoutthetextbook,they’remodeledwithexamplesusing theJustLeeBooksdatabase.Usingconsistentexamplesofahypotheticalcompanyhelps youlearnthesyntaxofcommandsandhowtousetheminareal-worldenvironment.In addition,ascriptfilethatgeneratesthedatabaseisavailabletogiveyouhands-onpractice inre-creatingexamplesandpracticingvariationsofSQLcommandstoenhanceyour understanding.

Toexplainwhatadatabaseisandhowit’screated,thistextbookinitiallyfocuses oncreatingtablesandlearninghowtoperformdatamanipulationoperations.After you ’refamiliarwiththedatabasestructure,thefocusthenturnstoqueryingadatabase.

InChapters8through13,youlearnhowtoretrievedatafromthedatabase,usingthe manyoptionsofaSELECTstatement,includingrowfiltering,joins,functions,and subqueries.

Toreinforcethematerial,eachchapterincludesachaptersummaryand,when appropriate,asyntaxguideforthecommandscoveredinthechapter.Inaddition,each chapterincludesreviewquestionsandhands-onactivitiesthattestyourknowledgeand challengeyoutoapplythatknowledgetosolvingbusinessproblems.Arunningcasestudy thatbuildsthroughoutthetextbookprovidesasecondreal-worldsetting acityjail system asanotheropportunitytoworkwithdatabases.

NewtoThisEdition

TopicsaddedcoveringnewOracle12c featuresinclude:

• Extendeddatatypes

• Invisiblecolumns

• Defaultcolumnvalues

• SpecifyingavalueforNULLinserts

• Basedonasequencevalue

• Identitycolumns

• Leftouterjoinenhancements

• CROSSAPPLYjoinmethod

• Patternmatchingfortrendanalyses

• EnhancedTop-N-Queryfeatures

In addition,coverageofsubqueryfactoringoruseoftheWITHclausehasbeenadded toChapter12.

OverviewofThisBook

Theexamples,assignments,andcasesinthisbookhelpyouachievethefollowing objectives:

• IssueSQLcommandsthatretrievedatabasedoncriteriaspecifiedby the user.

• UseSQLcommandstojointablesandretrievedatafromjoinedtables.

• Performcalculationsbasedondatastoredinthedatabase.

• Usefunctionstomanipulateandaggregatedata.

• Usesubqueriestoretrievedatabasedonunknownconditions.

• Create,modify,anddropdatabasetables.

• Manipulatedatastoredindatabasetables.

• Enforcebusinessrulesbyusingtableconstraints.

• Createusersandassigntheprivilegesusersneedtoperformtasks.

The chapters’ contentsbuildincomplexitywhilereinforcingpreviousideas.Chapter1 introducesbasicdatabasemanagementconcepts,includingdatabasedesign.Chapter2 showshowtoretrievedatafromatable.Chapter3explainshowtocreatenewdatabase tables.Chapter4addressestheuseofconstraintstoenforcebusinessrulesandensurethe integrityoftabledata.Chapter5explainsaddingdatatoatable,modifyingexistingdata, anddeletingdata.Chapter6showshowtouseasequencetogeneratenumbers,create

indexestospeedupdataretrieval,andcreatesynonymstoprovidealiasesfortables. Chapter7stepsyouthroughcreatinguseraccountsandrolesandshowshowtogrant(and revoke)privilegestotheseaccountsandroles.Chapter8explainshowtorestrictrows retrievedfromatable,basedonagivencondition.Chapter9showshowtolinktableswith commoncolumnsbyusingjoins.Chapter10describesthesingle-rowfunctionssupported byOracle12c. Chapter11coverstheusageofmultiple-rowfunctionstoderiveasingle valueforagroupofrowsandexplainshowtorestrictgroupsofrows. Chapter 12covers usingsubqueriestoretrieverowsbasedonanunknownconditionalreadystoredinthe database.Chapter13explainsusingviewstorestrictaccesstodataandreducethe complexityofcertaintypesofqueries.

Theappendixessupportandreinforcechaptermaterials. AppendixA contains printedversionsoftheinitialtablestructureanddatafortheJustLeeBooksdatabaseused throughoutthistextbook. AppendixB introducestheoperationoftheSQL*PlusandSQL Developerclientsoftwaretools. AppendixC listsOracleresourcesforfurtherstudy. AppendixD introducestheSQL*Loaderutilityforimportingdata. AppendixE introduces basicSQLstatementtuningconcepts. AppendixF identifiesSQLdifferencesinsome populardatabases.

Features

Toenhanceyourlearningexperience,eachchapterinthistextbookincludesthefollowing elements:

• Chapterobjectives: Each chapterbeginswithalistoftheconceptstobe masteredbythechapter’sconclusion.Thislistgivesyouaquickoverviewof chaptercontentsandservesasausefulstudyaid.

• Runningcase: A sustainedexample,thebusinessoperationofJustLeeBooks, isthebasisforintroducingnewcommandsandpracticingthematerial coveredineachchapter.

• Methodology: As newcommandsareintroducedineachchapter,the commandsyntaxisshownandthenanexample,usingtheJustLeeBooks database,illustratesusingthecommandinthecontextofbusiness operations.Thismethodologyshowsyounotonly how thecommandisused, butalso when and why it’sused.Thescriptfileusedtocreatethedatabaseis availablesothatyoucanworkthroughtheexamplesinthistextbook, engenderingahands-onenvironmentinwhichyoucanreinforceyour knowledgeofchaptermaterial.

• Tip: This feature,designatedbytheTipicon,providespracticaladviceand sometimesexplainshowaconceptappliesintheworkplace.

• Note: These explanations,designatedbytheNoteicon,offermore informationonperformingoperationswithdatabases.

• DatabasePreparation: These notes,placedattheend-of-chapter introductions,tellyouwhichscriptfromthestudentdatafilesyoushould runinpreparationforchapterexamplesandactivities.

• Caution: This warning,designatedbytheCautionicon,pointsoutdatabase operationsthat,ifmisused,couldhavedevastatingresults.

• Chaptersummaries: Each chapter’stextisfollowedbyasummaryofchapter concepts.Thesesummariesareahelpfulrecapofchaptercontents.

xviPreface

• Syntaxsummaries: BeginningwithChapter2,aSyntaxGuidetableis includedaftereachchaptersummarytorecapthecommandsyntaxcovered inthechapter.

• Reviewquestions: End-of-chapter assessmentbeginswithreviewquestions thatreinforcethemainideasintroducedineachchapter.Thesequestions ensurethatyouhavemasteredtheconceptsandunderstandtheinformation coveredinthechapter.

• Multiple-choicequestions: Each chaptercontainsmultiple-choice questionscoveringthematerialinthechapter.Oraclecertification-type questionsareincludedtoprepareyouforthetypeofquestionsyou canexpectoncertificationexamsandmeasureyourlevelof understanding.

• Hands-onassignments: Along withconceptualexplanationsand examples,eachchapterincludeshands-onassignmentsrelatedtothe chapter’scontents.Thepurposeoftheseassignmentsistogiveyou practicalexperience.Inmostcases,theassignmentsarebasedonthe JustLeeBooksdatabaseandbuildontheexamplesinthechapter.

• Advancedchallenge: This sectionposesanotherproblemabouttheJustLee Booksdatabaseforyoutosolveandislargerinscopethanthehands-on assignments.

• Casestudies: At theendofeachchapterisamajorcasestudy,designed tohelpyouapplywhatyouhavelearnedtoreal-worldsituations.These casesgiveyoutheopportunitytosynthesizeandevaluateinformation independently,examinepotentialsolutions,andmakerecommendations, muchasyouwouldinanactualbusinesssituation.Thesecasesusesa databasebasedonacityjailsystem.

SupplementalMaterials

Thefollowingsupplementalmaterialsareavailablewhenthisbookisusedinaclassroom setting.Allteachingtoolsavailablewiththisbookareprovidedtoinstructorsonthe CengageLearningWebsiteat www.cengagebrain.com.

• ElectronicInstructor’sManual: TheInstructor’sManualaccompanyingthis textbookincludesthefollowingitems:

• Additionalinstructionalmaterialtoassistinclasspreparation,including suggestions forlecturetopics

• Asamplesyllabus

• Whenapplicable,informationaboutpotentialproblemsthatcanoccurin networked environments

• CengageLearningTestingPoweredbyCogneroisaflexible,onlinesystem that allowsyouto:

• author,edit,andmanagetestbankcontentfrommultipleCengage Learning solutions

• createmultipletestversionsinaninstant

• delivertestsfromyourLMS,yourclassroomorwhereveryouwant

• PowerPointpresentations: Microsoft® PowerPointslidesareincludedfor eachchapter.Instructorscanusetheslidesinthreeways:asteaching aidsduringclassroompresentations,asprintedhandoutsforclassroom distribution,orasnetwork-accessibleresourcesforchapterreview. Instructorscanaddtheirownslidesforadditionaltopicsintroducedto theclass.

• Datafiles: The scriptfilesneededtocreatetheJustLeeBooksandCity JaildatabasesareavailableontheCengageLearningWebsite atwww .cengagebrain.com.

• Solutionfiles: Solutions tochapterexamples,end-of-chapterreview questionsandmultiple-choicequestions,hands-onassignments,andcase studiesareavailableontheCengageLearningWebsiteat www .cengagebrain.com.Thesolutionsarepasswordprotected.

• Figurefiles: Figure filesallowinstructorstocreatetheirownpresentations withfiguresfromthetextbook.

Acknowledgments

IfeelfortunatethatCengageLearningpursuedmyauthorshipofthistextbookand continuestosupportmyefforts.Iamoneluckyperson Ihavetwoangelsinheaven,my motherandgrandmother,andoneangelhereonearth,Scott.Withoutthemwatching overme,Iwouldnotbeabletotacklesuchchallenges.Ialsowanttothankmyfather, whoalwaysseemsmoreexcitedthanmeeverytimeIfinishabookproject.

However,thistextbookistheresultofanincredibleeffortbymanypeoplewhom IwishIhadtheopportunitytothankpersonally.First,hatsofftoLoriBradshawand WendyLangerudofS4CarlislePublishingServicesformanagingthecontentforthis projectandkeepingeveryoneonschedule.AndaspecialthankyoutoArulJosephRaj ofLuminaDatamaticsLTDforhisdetailedeffortsinpreparing,reviewing,andformatting thecontent.Thereweremanyothersinvolvedinthisprojectlikeproductmanagement, editing,marketing,andartdevelopment,andeveryeffortwascriticalinmakingthis bookareality.Itrulyappreciatealloftheseefforts.

Inaddition,Ineedtorecognizetheenormouscontributionofcolleaguesand reviewers,whoprovidedhelpfulsuggestionsandinsightintothedevelopmentofthis textbookinearliereditions.And,finally,manythankstotheinstructorswhohave adoptedthistextandhavededicatedtheireffortstoassistotherstolearnthesemost importantdatabaseskills.

xviiiPreface

TOTHEUSER

DataFiles

Toworkthroughtheexamplesandcompletetheprojectsinthisbook,youneedtoload thedatafilescreatedforthisbook.Yourinstructorwillprovidethesedatafiles,oryou candownloadthemfromtheCengageLearningWebsiteat www.cengagebrain.com and thensearchforthisbook’stitle.Thedatafilesaredesignedtosupplythesamedatashown inchapterexamples,soyoucanhavehands-onpracticeinre-creatingthequeriesand theiroutput.Thetablesinthedatabasecanberesetifyouencounterproblems,suchas accidentallydeletingdata.Workingthroughallexamplesishighlyrecommendedto reinforceyourlearning.

StartingwithChapter2,databasescriptinstructionsaregivenatthebeginningofthe chapter,ifapplicable.Thesedatabasescriptfilesareinthefoldercorrespondingtothe chapter(Chapter5,Chapter10,andsoforth)on www.cengagebrain.com andhave filenamessuchasJLDB_Build_#.sql(substitutingthechapternumberforthe #symbol).Ifthecomputerinyourschoollab oryourowncomputer hasOracle12c installed,youcanworkthroughthechapterexamplesandcompletethehands-on assignmentsandcaseprojects.Manyofthecodingexamplesinthistextbookcanbe completedsuccessfullywithpreviousversionsofOracle(Oracle10g orOracle11g).

ConnectingtoOracle

MostcollegeswillprovidevariousoptionsforaccessingOraclesoftwaretoexecuteSQL statementsrequiredinthistext,typicallyviatheinternetand/orusingcollegecomputer labs.Contactyourinstructorfirsttoidentifytheoptionsavailable.Ifyouchoosetoinstall Oracle12c onyourowncomputer,visit www.oracle.com andgotothedatabase downloadarea.Thedatabasesoftwareisfreelyavailableforlearningpurposes,however,it isveryimportantthatyouviewthedocumentationforthesoftwarepriortodownloading andinstallingthesoftware.Besuretoreviewthehardwareandsoftwarerequirements priortoattemptingtheinstall.YouwillbeaskedtosetupafreeOracleTechnology Networkaccountbeforedownloading.IfyouarenewtoOracle,consultwithyour instructorpriortoinstallingOracledatabasesoftwareforguidance.Thistextwastested onOracleDatabase12c release12.1.0.1.0.

WhenyouinstalltheOraclesoftware,you’repromptedtochangethepasswordfor certaindefaultadministrativeuseraccounts.Makesureyourecordtheaccounts’ names andpasswordsbecauseyoumightneedtologintothedatabasewithoneofthese administrativeaccountsinlaterchapters.AfteryouinstallOracle,you’rerequiredto

enterausernameandpasswordtoaccessthesoftware.Onedefaultusernamecreated duringinstallationis “scott.”

Also,notethatyoucan’tuseyourowncomputertoworkthroughchapterexamples andcompleteprojectsunlessyouhavethedatafiles.Youcangetthesefilesfromyour instructor,oryoucandownloadthemfromtheCengageLearningWebsiteat www .cengagebrain.com andthensearchforthisbook’stitle.Whenyoudownloadthedata files,theyshouldbestoredinadirectoryseparatefromanyotherfilesonyourharddrive. Youneedtorememberthepathorfoldercontainingthesefilesbecauseeachscript filenamemustbeprefixedwithitslocationbeforeyourunthescript.

VisitOurWebSite

Asupplementalchapteronformattingreportoutputhasbeenincludedinthebook’ s onlinematerials.Additionalmaterialsdesignedespeciallyforthistextbookmightbe availableontheCengageLearningWebsite.Goto www.cengagebrain.com periodically andsearchthissiteformoredetails.

TOINSTRUCTORS

Tocompleteexamplesandactivitiesinthistextbook,yourstudentsmusthaveaccessto thedatafilesincludedontheInstructorResourcesGD(ordownloadedfrom www .cengagebrain.com).

ThedatafilesconsistoftheJustLeeDatabasefolderandafolderforeachchapter. Manychaptersrequirerunningascript;ifso,theseinstructionsaregiveninanoteatthe beginningofthechapter.Thesescriptsareinfolderscorrespondingtothechapter (Chapter5,Chapter10,andsoforth)andhavefilenamessuchasJLDB_Build_#.sql (substitutingthechapternumberforthe#symbol).Theinitialdatabasecreationisdone atthebeginningofChapter2tocreatetheJustLeeBooksdatabase.Studentsshouldrun thescriptsasinstructedtohaveacopyofthetablesstoredintheirschemas.Youshould instructyourstudentsonhowtoaccessandcopydatafilestotheirowncomputers.The chaptersandprojectsinthisbookweretestedwithOracle12c StandardEdition.

CengageLearningDataFiles

Youaregrantedalicensetocopydatafilestoanycomputerorcomputernetworkusedby peoplewhohavepurchasedthisbook.

CHAPTER 1 OVERVIEWOFDATABASE CONCEPTS

LEARNINGOBJECTIVES

Aftercompletingthischapter,youwillbeabletodothefollowing:

• Definedatabaseterms

• Identifythepurposeofadatabasemanagementsystem(DBMS)

• Explaindatabasedesignbyusingentity-relationshipmodelsand normalization

• ExplainthepurposeofaStructuredQueryLanguage(SQL)

• Understandhowthistextbook’stopicsaresequencedandhowthetwo sampledatabasesareused

• Identifythesoftwareusedinthistextbook

INTRODUCTION

Imagineyou’restartingupanonlinebookretailcompany.Howwillcustomerorders berecorded?Willcustomersbeabletosearchforproductsbynameorkeywords?Willyou beabletoanalyzesalesinformationtotrackprofits,determineproductsuccess,andtarget marketingeffortstocustomers?Analyzingthousandsoforderscouldtakedayswithout usingadatabase.A database simplifiesthesetasksbecauseit’sastoragestructurethat providesmechanismsforrecording,manipulating,andretrievingdata.

Thedatabaseusedthroughoutthistextbookisbasedontheactivitiesofa hypotheticalbusiness,anonlinebooksellernamedJustLeeBooks.Thecompanysells booksviatheInternettocustomersthroughouttheUnitedStates.Whenanewcustomer placesanorder,heorsheprovidesdatasuchasname,billingandshippingaddresses,and itemsordered.Thecompanyalsousesadatabaseforallbooksininventory.

ToaccessthedatarequiredforoperatingJustLeeBooks,managementreliesona DBMS.A databasemanagementsystem(DBMS) isusedtocreateandmaintainthe structureofadatabase,andthentoenter,manipulate,andretrievethedataitstores. Creatinganefficientdatabasedesignisthekeytousingadatabaseeffectivelytosupport anorganization’sbusinessoperations.

Thischapterintroducesbasicdatabaseterminologyanddiscussestheprocessof designingadatabaseforJustLeeBooks.

DATABASETERMINOLOGY

Wheneveracustomeropensanaccountwithacompany,certaindatamustbecollected. Inmanycases,thecustomercompletesanonlineformthatasksforthecustomer’ sname, address,andsoon,asshowninFigure1-1.

FIGURE1-1 Collectingcustomerinformation

Whilecollectingcustomerinformation,aseriesofcharactersisidentifiedforeachitem. A character isthebasicunitofdata,anditcanbealetter,number,orspecialsymbol.

Agroupofrelatedcharacters(forexample,thecharactersthatmakeupacustomer’ s name)iscalleda field.Afieldrepresentsoneattributeorcharacteristic(thename,for instance)ofthecustomer.Acollectionoffieldsaboutonecustomer(forexample,name, address,city,state,andzipcode)iscalleda record.Agroupofrecordsaboutthesame typeofentity(suchascustomersorinventoryitems)isstoredina file.Acollectionof interrelatedfiles suchasthoserelatingtocustomers,theirpurchases,andtheir payments isstoredinadatabase.

Thesetermsrelatetothelogicaldatabasedesign,buttheyareoftenused interchangeablywiththeterminologyforthephysicaldatabasedesign.Whencreatingthe physicaldatabase,afieldiscommonlyreferredtoasa column,arecordiscalleda row, andafileisknownasa table.Atableisquitesimilartoaspreadsheet,inthatitcontains columnsandrows.Figure1-2showsarepresentationoftheseterms.

DATABASEMANAGEMENTSYSTEM

Asmentionedearlier,adatabaseishousedinaDBMS,whichprovidesthefunctionalityto createandworkwithadatabase.Thisfunctionalityincludesthefollowing:

• Datastorage: Managethephysicalstructureofthedatabase.

• Security: Controluseraccessandprivileges.

• Multiuseraccess: Manageconcurrentdataaccess.

• Backup: Enablerecoveryoptionsfordatabasefailures.

• Dataaccesslanguage: Providealanguagethatallowsdatabaseaccess.

• Dataintegrity: Enableconstraintsorchecksondata.

• Datadictionary: Maintaininformationaboutdatabasestructure.

FIGURE1-2 Databaseterminology

DATABASEDESIGN

Todeterminethemostappropriatestructureoffields,records,andfilesinadatabase, developersgothroughadesignprocess.Thedesignanddevelopmentofasystemis accomplishedthroughaprocessthat’sformallycalledthe SystemsDevelopmentLife Cycle(SDLC) andconsistsofthefollowingsteps:

1. Systemsinvestigation: Understandingtheproblem

2. Systemsanalysis: Understandingthesolutiontothepreviouslyidentified problem

3. Systemsdesign: Definingthelogicalandphysicalcomponents

4. Systemsimplementation: Creatingthesystem

5. Systemsintegrationandtesting: Placingthesystemintooperationfortesting

6. Systemsdeployment: Placingthesystemintoproduction

7. Systemsmaintenanceandreview: Evaluatingtheimplementedsystem

AlthoughtheSDLCisamethodologydesignedforanytypeofsystemanorganization needs,thischapterspecificallyaddressesdevelopingaDBMS.Forthepurposesofthis discussion,assumetheproblemidentifiedistheneedtocollectandmaintaindataabout customersandtheirorders.Theidentifiedsolutionistouseadatabasetostoreallneeded data.Thediscussionthatfollowspresentsthestepsfordesigningthedatabase.

NOTE

AvarietyofSDLCmodelshavebeendevelopedtoaddressdifferentdevelopmentenvironments.The stepspresentedhererepresentatraditionalwaterfallmodel.Othermodels,suchasfountainandrapid prototyping,involveadifferentseriesofsteps.

Todesignadatabase,therequirementsofthedatabase inputs,processes,and outputs mustbeidentifiedfirst.Usually,thefirstquestionaskedis, “Whatinformation,or output,mustcomefromthisdatabase?” or “Whatquestionsshouldthisdatabasebeableto answer?” Byunderstandingthenecessaryoutput,thedesignercanthendeterminewhat informationshouldbestoredinthedatabase.Forexample,iftheorganizationwantsto sendbirthdaycardstoitscustomers,thedatabasemuststoreeachcustomer’sbirthdate. Aftertherequirementsofadatabasehavebeenidentified,anentity-relationship(E-R) modelisusuallydraftedtobetterunderstandthedatatobestoredinthedatabase.InanE-R model,an entity isanyperson,place,orthingwithcharacteristicsorattributesthatwillbe includedinthesystem.An E-Rmodel isadiagramthatidentifiestheentities(customers, books,orders,andsuch)inthedatabase,anditshowshowtheentitiesarerelatedtoone another.Itservesasthelogicalrepresentationofthephysicalsystemtobebuilt.

ThenexttwosectionsexplaintheconstructionofanE-Rmodelandthe normalizationprocessusedtodetermineappropriateentitiesforadatabase.

Entity-Relationship(E-R)Model

InanE-Rmodel,anentityisusuallyrepresentedasasquareorrectangle.Asshownin Figure1-3,alinedepictshowanentity’sdatarelatestoanotherentity.Iftheline connectingtwoentitiesissolid,therelationshipbetweentheentitiesismandatory. However,iftherelationshipbetweentwoentitiesisoptional,adashedlineisused.

AsshowninFigure1-3,thefollowingtypesofrelationshipscanexistbetweentwo entities:

• One-to-one: Inaone-to-onerelationship,eachoccurrenceofdatainone entityisrepresentedbyonlyoneoccurrenceofdataintheotherentity. Forexample,ifeachclassroomisassignedtoonlyoneacademicdivision,a one-to-onerelationshipiscreatedbetweentheclassroomanddivision entities.ThistypeofrelationshipisdepictedinanE-Rmodelasasimple straightline.

• One-to-many: Inaone-to-manyrelationship,eachoccurrenceofdatainone entitycanberepresentedbymanyoccurrencesofthedataintheother entity.Forexample,aclasshasonlyoneinstructor,butaninstructormight teachmanyclasses.Aone-to-manyrelationshipisrepresentedbyastraight linewithacrow’sfootatthe “ many ” end.

• Many-to-many: Inamany-to-manyrelationship,datacanhavemultiple occurrencesinbothentities.Forexample,aclasscanconsistofmorethan onestudent,andastudentcantakemorethanoneclass.Astraightlinewith acrow ’sfootateachendindicatesamany-to-manyrelationship.

Figure1-4showsasimplifiedE-RmodelfortheJustLeeBooksdatabaseused throughoutthistextbook.AmorethoroughE-Rmodelwouldincludealistofattributesfor eachentity.

NOTE

ThenotationsinthesampleE-Rmodelsinthischapterreflectonlyonewayofdiagrammingentity relationships.Ifyou’reusingamodelingsoftwaretool,youmightencounterdifferentnotationsto representrelationships.Forexample,Microsoftproductstypicallyrepresentthemanysideofa relationshipwiththeinfinitysymbol(∞).Inaddition,somemodelingtoolsautomaticallyaddthecommon fieldsorforeignkeycolumnsneededasrelationshipsaredefined.

FIGURE1-3 E-Rmodelnotationexamples

ThefollowingrelationshipsaredefinedintheE-RmodelinFigure1-4:

• Customerscanplacemultipleorders,buteachordercanbeplacedbyonlyone customer(one-to-many).ThedashedlinebetweenCustomersandOrders meansacustomercanexistinthedatabasewithouthavingacurrentorder storedintheORDERStable.Therefore,thisrelationshipisconsideredoptional.

• Anordercanconsistofmorethanonebook,andabookcanappearonmore than oneorder(many-to-many).

• Abookcanhavemorethanoneauthor,andanauthorcanwritemorethan one book(many-to-many).

• Abookcanhaveonlyonepublisher,butapublishercanpublishmorethan one book(one-to-many).

AlthoughsomeE-Rmodelingapproachesaremorecomplex,thesimplifiednotations usedinthischapterdopointouttheimportantrelationshipsamongentities,andusing themhelpsdesignersidentifypotentialproblemsintablelayouts.AfterexaminingtheE-R modelinFigure1-4,youshouldhavenoticedthetwomany-to-manyrelationships.Before creatingthedatabase,allmany-to-manyrelationshipsmustbereducedtoasetofoneto-manyrelationships,asyoulearnin “RelatingTablesintheDatabase” laterinthis chapter.

Identifyingentitiesandrelationshipsinthedatabasedesignprocessisimportant becauseentitiesareusuallyrepresentedasatable,andrelationshipscanrevealwhether additionaltablesareneededinthedatabase.Iftheproblemarisingfromthemanyto-manyrelationshipintheE-Rmodelisn’tapparenttoadesigneratthispoint,itwill becomeclearduringthenormalizationprocess.

DatabaseNormalization

Manypeopleunfamiliarwithdatabasedesignprinciplesoftenask, “Whynotjustputallthe datainonebigtable?” Thissingle-tableapproachleadstoproblemsofdataredundancy (duplication)anddataanomalies(datainconsistencies).Forexample,reviewtheorderdata recordedinTable1-1.Thecustomerinformationisrepeatedforeachorderacustomer places(redundancy).Also,thecitydatainthelastrowisdifferentfromthefirsttworows. Underthesecircumstances,itisn’tclearwhetherthelastrowactuallyrepresentsadifferent

customer,whetherthepreviouscustomerhadanaddresschange,orwhetherthecity informationisincorrect(dataanomaly).

TABLE1-1 Single-TableApproachExample

Toavoidthesedataissues,database normalization isusedtocreateadesignthat reducesoreliminatesdataredundancyand,therefore,avoidsdataanomalies.Ingeneral, normalizationhelpsdatabasedesignersdeterminewhichattributes,orfields,belongto eachentity.Inturn,thisinformationhelpsdeterminewhichfieldsbelongineachtable. Normalizationisamultistageprocessthatenablesdesignerstotaketherawdatatobe collectedaboutanentityanddevelopthedataintoastructured,normalizedformthat reducestherisksassociatedwithdataredundancy.Dataredundancyposesaspecial problemindatabasesbecausestoringthesamedataindifferentplacescancauseproblems whenupdatesorchangestodataarerequired.

Mostnoviceshavedifficultyunderstandingtheimpactofstoring unnormalized data datathathasn’tbeendesignedbyusinganormalizationprocess.Here’sanexample. Supposeyouworkforalargecompanyandsubmitachange-of-addressformtotheHuman Resources(HR)Department.IfallthedataHRstoresisnormalized,adataentryclerkneeds toupdateonlytheEMPLOYEESmastertablewithyournewaddress.

However,ifthedatais not storedinanormalizedformat,thedataentryclerklikely needstoenterthechangeineachtablecontainingyouraddress theEMPLOYEE RECORDtable,theHEALTHINSURANCEtable,theSICKLEAVEtable,theANNUALTAX INFORMATIONtable,andsoon eventhoughallthisdataisstoredinthesamedatabase. Asaresult,ifyourmailingaddressisstoredinseveraltables(orevenduplicatedinthe sametable)andthedataentryclerkfailstomakethechangeinonetable,youmightgeta paycheckshowingoneaddressand,attheendoftheyear,haveyourW-2formmailedtoa differentaddress!Storingdatainanormalizedformatmeansonlyoneupdateisrequired toreflectthenewaddress,anditshouldalwaysbetheonethatappearswheneveryour mailingaddressisneeded.

AportionofthedatabaseforJustLeeBooks isusedinthissectiontostepthroughthe normalizationprocess specifically,thebookssoldtocustomers.Foreachbook,youneedto storeitsInternationalStandardBookNumber(ISBN),title,publicationdate,wholesalecost, retailprice,category(literature,self-help,andsoforth),publishername,contactpersonatthe publisherforreorderingthebook(andtelephonenumber),andauthororauthors’ names.

Table1-2showsasampleofthedatathatmustbemaintained.Foreaseofillustration, thepublishers’ telephonenumbersareeliminated,andtheauthors’ namesusejustthe firstinitialandlastname.Thefirststepindeterminingwhichdatashouldbestoredin eachtableisidentifyinga primarykey,whichisafieldthatidentifieseachrecord

uniquely.YoumightselecttheISBNtoidentifyeachbookbecausenotwobookseverhave thesameISBN.

NOTE

Whendatathatalreadyexists,suchasabookISBN,isusedasaprimarykey,it’softenreferredtoas anintelligentkeyoranaturalkey.Attimes,dataservingasaprimarykeydoesn’texist,soasystemgenerateduniquevalueisusedasaprimarykey.Forexample,JustLeeBooksdoesn’thaveanIDto associatewithbookauthors,soanIDnumberisgenerated.Thistypeofdataisreferredtoasa surrogatekeyoranartificialkey.

However,notethatinTable1-2,ifabookhasmorethanoneauthor,theAuthorfield containsmorethanonedatavalue.Whenarecordcontainsrepeatinggroups(thatis, multipleentriesforasinglecolumn),it’sconsideredunnormalized. First-normalform (1NF) indicatesthatallvaluesofthecolumnsareatomic meaningtheycontainno repeatingvalues.Toconvertrecordsto1NF,removetherepeatingvaluesbymakingeach authorentryaseparaterecord,asshowninTable1-3.

InTable1-3,therepeatingvaluesofauthors’ namesareeliminated eachrecordnow containsnomorethanonedatavaluefortheAuthorfield.Noticethatyoucannolonger usethebook’sISBNastheprimarykeybecausemorethanonerecordnowhasthesame valueintheISBNfield. Theonlycombinationoffieldsthatidentifieseachrecord uniquelyistheISBNandAuthorfieldstogether. Whenmorethanonefieldisusedasthe primarykeyforatable,thecombinationoffieldsisusuallyreferredtoasa composite primarykey.Nowthattherepeatingvalueshavebeeneliminatedandtherecordscanbe identifieduniquely,thedataisin1NF,butafewdesignproblemsremain.

Aproblemknownaspartialdependencycanoccurwhentheprimarykeyconsistsof morethanonefield. Partialdependency meansthefieldscontainedinarecord(row) dependononlyoneportionoftheprimarykey.Forexample,abook’stitle,publicationdate, publishername,andsoon,alldependonthebookitself,notonwhowrotethebook(the author). Thesimplestwaytoresolveapartialdependencyistobreakthecomposite primarykeyintotwoparts eachrepresentingaseparatetable. Inthiscase,youcan createatableforbooksandatableforauthors.Byremovingthepartialdependency,you haveconvertedtheBOOKStableto second-normalform(2NF),asshowninTable1-4.

NowthattheBOOKSrecordsarein2NF,youmustlookforanytransitivedependencies. A transitivedependency meansatleastonevalueintherecordisn’tdependentonthe primarykeybutonanotherfieldintherecord.Inthiscase,thecontactpersonfromthe publisher’sofficeisactuallydependentonthepublisher,notonthebook.Toremovethe transitivedependencyfromtheBOOKStable,removethecontactinformationandplaceitin aseparatetable.Becausethetablewasin2NFandhadalltransitivedependenciesremoved, theBOOKStableisnowin third-normalform(3NF),asshowninTable1-5.

TABLE1-5 TheBOOKSTablein3NF