KSR COLLEGE OF ARTS AND SCIENCE

DEPARTMENT OF COMPUTER APPLICATIONS (UG)

UNIT - I

NOTES ON RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)

(09BCAM402)

Contents

Introduction.....................................................................................................................1 1.DatabaseSystemApplications................................................................................1 2.PurposeofDatabaseSystems...................................................................................2 3.ViewofData................................................................................................................3 3.1DataAbstraction....................................................................................................3 3.2InstancesandSchemas..........................................................................................4 4.DataModels.................................................................................................................4 5.DataBaseLanguages..................................................................................................5 5.1DataManipulationLanguage(DML).................................................................5 5.2DataDefinitionLanguage(DDL)........................................................................5 6.DatabaseArchitecture................................................................................................6 7.DatabaseUsersandAdministrators........................................................................8 7.1DatabaseUsersandUserInterfaces....................................................................8 7.2DatabaseAdministrator........................................................................................8 8.StructureofRelationalDatabase.............................................................................9 8.1BasicStructure........................................................................................................9 8.2DatabaseSchema .................................................................................................10 . 8.3Keys........................................................................................................................13 8.4QueryLanguages.................................................................................................14 9.TheEntityRelationshipModel..............................................................................14 9.1EntitySets..............................................................................................................15 9.2RelationshipSets..................................................................................................15 9.3Attributes..............................................................................................................16 . 10.Constraints...............................................................................................................18 10.1MappingCardinalities......................................................................................18

10.2Keys......................................................................................................................20 10.2.1EntitySets.....................................................................................................20 10.2.2RelationshipSets.........................................................................................20 11.TheEntityRelationshipDiagram........................................................................20 12.ExtendedERFeatures...........................................................................................25 . 12.1Specialization(Topdownapproach)..............................................................25 12.2Generalization(Bottomupapproach)............................................................25 13.TheNetworkModel...............................................................................................26 14.TheHierarchicalModel.........................................................................................28

Introduction
1. A database management system (DBMS), or simply a database system (DBS), consistsof o Acollectionofinterrelatedandpersistentdata(usuallyreferredtoasthe database(DB)). o A set of application programs used to access, update and manage that data(whichformthedatamanagementsystem(MS)). 2. The goal of a DBMS is to provide an environment that is both convenient and efficienttousein o Retrievinginformationfromthedatabase. o Storinginformationintothedatabase. 3. Databases are usually designed to manage large bodies of information. This involves o Definitionofstructuresforinformationstorage(datamodeling). o Provision of mechanisms for the manipulation of information (file and systemsstructure,queryprocessing). o Providing for the safety of information in the database (crash recovery andsecurity). o Concurrencycontrolifthesystemissharedbyusers.

1. Database-System Applications
o o o o o

o o o o

Banking:forcustomerinformation,account,loans,andbankingtransactions. Airlines:forreservationsandscheduleinformation. Universities:forstudentinformation,courseregistrations,andgrades. Credit card transactions: for purchases on credit cards and generation of monthlystatements. Telecommunication:forkeepingrecordsofcallsmade,generatingmonthlybills, maintainingbalancesonprepaidcallingcards,andstoringinformationaboutthe communicationnetworks. Finance:forstoringinformationaboutholdings,sales,andpurchasesoffinancial instrumentssuchasstocksandbonds. Sales:forcustomer,product,andpurchaseinformation Onlineretailers:onlineordertracking,generationofrecommendationlists,and maintenanceofonlineproductevaluations. Manufacturing:formanagementofthesupplychainandfortrackingproduction ofitemsinfactories,inventoriesofitemsinwarehousesandstores,andorders foritems. Human resource: for information about employees, salaries, Payroll taxes, benefits,andforgenerationofpaychecks.

2. Purpose of Database Systems

1. Toseewhydatabasemanagementsystemsarenecessary,let'slookatatypical ``fileprocessingsystem''supportedbyaconventionaloperatingsystem. Theapplicationisasavingsbank: Savingsaccountandcustomerrecordsarekeptinpermanentsystemfiles. Applicationprogramsarewrittentomanipulatefilestoperformthe followingtasks: Debitorcreditanaccount. Addanewaccount. Findanaccountbalance. Generatemonthlystatements. 2. Developmentofthesystemproceedsasfollows: o Newapplicationprogramsmustbewrittenastheneedarises. o Newpermanentfilesarecreatedasrequired. o Butoveralongperiodoftimefilesmaybeindifferentformats,and o Applicationprogramsmaybeindifferentlanguages. 3. Sowecanseethereareproblemswiththestraightfileprocessingapproach: o Dataredundancyandinconsistency Sameinformationmaybeduplicatedinseveralplaces. Allcopiesmaynotbeupdatedproperly. o Difficultyinaccessingdata Needtowriteanewprogramtocarryouteachnewtask E.g.findallcustomerswiththesamepostalcode. Couldgeneratethisdatamanually,butalongjob... o Dataisolation Dataindifferentfilesanddifferentformats. Difficulttowritenewapplicationprograms. o Integrityproblems Integrityconstraints(e.g.accountbalance>0)becomeburied inprogramcoderatherthanbeingstatedexplicitly Hardtoaddnewconstraintsorchangeexistingones o Atomicityproblems Failuresmayleavedatabaseinaninconsistentstatewithpartial updatescarriedout Example:Transferoffundsfromoneaccounttoanothershould eithercompleteornothappenatall o Concurrentaccessanomalies Concurrentaccessedneededforperformance Uncontrolledconcurrentaccessescanleadtoinconsistencies Example:Twopeoplereadingabalanceandupdatingitatthe sametime. o Securityproblems
o o

Everyuserofthesystemshouldbeabletoaccessonlythedata theyarepermittedtosee. E.g.payrollpeopleonlyhandleemployeerecords,andcannotsee customeraccounts;tellersonlyaccessaccountdataandcannot seepayrolldata. Difficulttoenforcethiswithapplicationprograms.

Theseproblemsandothersledtothedevelopmentofdatabasemanagementsystems.

3. View of Data
Themajorpurposeofadatabasesystemistoprovideuserswithanabstractviewofthe system. The system hides certain details of how data is stored and created and maintained.Complexityshouldbehiddenfromdatabaseusers. 3.1 Data Abstraction Thereareseverallevelsofabstraction: 1. PhysicalLevel: Howarecordisstored.E.g.index,Btree,hashing. Lowestlevelofabstraction. Complexlowlevelstructuresdescribedindetail. 2. LogicalLevel: Nexthighestlevelofabstraction. Describeswhatdataarestored. Describestherelationshipsamongdata. Databaseadministratorlevel. 3. ViewLevel: Highestlevel. Describespartofthedatabaseforaparticulargroupofusers. Canbemanydifferentviewsofadatabase. E.g.tellersinabankgetaviewofcustomeraccounts,butnotof payrolldata.

Figure1.1:Thethreelevelsofdataabstraction

3.2 Instances and Schemas Similartotypesandvariablesinprogramminglanguages Schemathelogicalstructureofthedatabase o Example:Thedatabaseconsistsofinformationaboutasetofcustomers andaccountsandtherelationshipbetweenthem) o Analogoustotypeinformationofavariableinaprogram o Physicalschema:databasedesignatthephysicallevel o Logicalschema:databasedesignatthelogicallevel Instancetheactualcontentofthedatabaseataparticularpointintime o Analogoustothevalueofavariable PhysicalDataIndependencetheabilitytomodifythephysicalschemawithout changingthelogicalschema o Applicationsdependonthelogicalschema o Ingeneral,theinterfacesbetweenthevariouslevelsandcomponents shouldbewelldefinedsothatchangesinsomepartsdonotseriously influenceothers.

4. Data Models
Datamodelsareacollectionofconceptualtoolsfordescribingdata,data relationships,datasemanticsandconsistencyconstraints.Therearefour differentgroups: 1. 2. 3. 4. RelationalModel TheEntityRelationshipModel ObjectBasedLogicalModel SemistructuredDataModel

1. RelationalModel Acollectionoftablestorepresentbothdataandtherelationshipamongthose data. Thedatabaseisstructuredinfixedformatrecordsofseveraltypes. Mostwidelyuseddatamodel 2. TheEntityRelationshipModel Anentityisathingorobjectintherealworldthatisdistinguishablefrom otherobjects. Thecollectionofbasicobjectscalledentities,andofrelationshipsamongthese objects. 3. ObjectBasedDataModel Combinesthefeaturesoftheobjectorienteddatamodelandrelationaldata model. ExtendingtheERmodelwithnotionsofencapsulation,methods(functions), andobjectidentity.

4. SemistructuredDataModel Individualdataitemsofthesametypemayhavedifferentsetofattributes. TheExtensibleMarkupLanguage(XML)isanexampleforsemistructureddata.

5. Data Base Languages

Adatabasesystemprovidestwodifferenttypesoflanguages:adatadefinition languagetospecifythedatabaseschemaandadatamanipulationlanguagetoexpress databasequeriesandupdates. 5.1 Data Manipulation Language (DML) 1. DataManipulationis: o Retrievalofinformationstoredinthedatabase o Insertionofnewinformationintothedatabase o Deletionofinformationfromthedatabase o Modificationofinformationstoredinthedatabase 2. ADMLisalanguagewhichenablesuserstoaccessandmanipulatedata. Thegoalistoprovideefficienthumaninteractionwiththesystem. 3. TherearetwotypesofDML: o Procedural:theuserspecifieswhatdataisneededandhowtogetit o Declarative:theuseronlyspecifieswhatdataisneededwithout specifyinghowtogetthosedata. Easierforuser Maynotgeneratecodeasefficientasthatproducedby procedurallanguages 4. AquerylanguageisaportionofaDMLinvolvinginformationretrievalonly.The termsDMLandquerylanguageareoftenusedsynonymously. 5.2 Data Definition Language (DDL) 1. UsedtospecifyadatabaseschemeasasetofdefinitionsexpressedinaDDL 2. DDLstatementsarecompiled,resultinginasetoftablesstoredinaspecialfile calledadatadictionaryordatadirectory. 3. Thedatadirectorycontainsmetadata(dataaboutdata) 4. Thestoragestructureandaccessmethodsusedbythedatabasesystemare specifiedbyasetofdefinitionsinaspecialtypeofDDLcalledadatastorageand definitionlanguage 5. basicidea:hideimplementationdetailsofthedatabaseschemesfromtheusers DomainConstratins Avalueisassociatedwitheveryattribute. Aparticularattributeactsasaconstraintonthevaluesthatitcantake.

ReferentialIntegrity Avaluethatappearsinonerelationforanattributealsoappearsforacertain setofattributesinanotherrelation. Databasemodificationcancauseviolationsofreferentialintegrity. Assertions Anassertionisanyconditionthatthedatabasemustalwayssatisfy. Iftheassertionisvalid,thenthedatabasemodificationisallowedinfuture. Authorization Thetypeofaccessarepermittedonvariousdatavaluestousers. Ex:Read/insert/update/deleteauthorization

6. Database Architecture
1. Database systems are partitioned into modules for different functions. Some functions(e.g.filesystems)maybeprovidedbytheoperatingsystem. 2. Componentsinclude: o Filemanagermanagesallocationofdiskspaceanddatastructuresused torepresentinformationondisk. o Databasemanager:Theinterfacebetweenlowleveldataandapplication programsandqueries. o Queryprocessortranslatesstatementsinaquerylanguageintolowlevel instructions the database manager understands. (May also attempt to findanequivalentbutmoreefficientform.) o DMLprecompilerconvertsDMLstatementsembeddedinanapplication program to normal procedure calls in a host language. The precompiler interactswiththequeryprocessor. o DDL compiler converts DDL statements to a set of tables containing metadatastoredinadatadictionary. In addition, several data structures are required for physical system implementation: o Datafiles:storethedatabaseitself. o Datadictionary:storesinformationaboutthestructureofthedatabase. Itisusedheavily.Greatemphasisshouldbeplacedondevelopingagood designandefficientimplementationofthedictionary. o Indices:providefastaccesstodataitemsholdingparticularvalues.

 Fig1.2:Systemstructure

Fig1.3:Twotierandthreetierarchitectures

7. Database Users and Administrators

7.1 Database Users and User Interfaces 1. Thedatabaseusersfallintoseveralcategories: Application programmers are computer professionals interacting with thesystemthroughDMLcallsembeddedinaprogramwritteninahost language(e.g.C,PL/1,Pascal). Theseprogramsarecalledapplicationprograms. The DML precompiler converts DML calls (prefaced by a special character like $, #, etc.) to normal procedure calls in a host language. Thehostlanguagecompilerthengeneratestheobjectcode. Some special types of programming languages combine Pascal like control structures with control structures for the manipulationofadatabase. Thesearesometimescalledfourthgenerationlanguages. They often include features to help generate forms and display data. o Sophisticatedusersinteractwiththesystemwithoutwritingprograms. They form requests by writing queries in a database query language. These are submitted to a query processor that breaks a DML statement down into instructions for the database manager module. o Specialized users are sophisticated users writing special database application programs. These may be CADD systems, knowledgebased andexpertsystems,complexdatasystems(audio/video),etc. o Naive users are unsophisticated users who interact with the system by usingpermanentapplicationprograms(e.g.automatedtellermachine).
o

7.2 Database Administrator 1. The database administrator is a person having central control over data and programsaccessingthatdata.Dutiesofthedatabaseadministratorinclude: o Scheme definition: DBA Creates the original database schema by executingasetofdatadefinitionstatementsintheDDL. o Storage structure and access method definition: writing a set of definitions translated by the data storage and definition language compiler. o Scheme and physical organization modification: The DBA carries out changes to the schema and physical organization to improve performance.

Granting of authorization for data access: granting different types of authorizationfordataaccesstovarioususers o Integrityconstraintspecification:generatingintegrityconstraints.These areconsultedbythedatabasemanagermodulewheneverupdatesoccur. o Routinemaintenance: 1.PeriodicallybackinguptheDB. 2.Ensuringfreediskspace. 3.MonitoringJobsrunningontheDB.
o

8. Structure of Relational Database

1. Arelationaldatabaseconsistsofacollectionoftables,eachhavingaunique name. Arowinatablerepresentsarelationshipamongasetofvalues. Thusatablerepresentsacollectionofrelationships. 2. Thereisadirectcorrespondencebetweentheconceptofatableandthe mathematicalconceptofarelation.Asubstantialtheoryhasbeendevelopedfor relationaldatabases. 8.1 Basic Structure 1. Theaccounttablehasthreecolumnheaders:account_number,brance_name, andbalance.Theseareheaders,theheadersareattributes.Foreachattribute, thereisasetofpermittedvalues,calledthedomainofthatattribute. LetD1denotethesetofallaccountnumbers,D2thesetofallbranchnames,D3 thesetofallbalances.Anyrowofaccountmustconsistofa3tuple(v1,v2,v3), wherev1isanaccountnumber.

Ingeneral,accountcontainsasubsetofthesetofallpossiblerows. Thatis,accountisasubsetof

 Example:accountrelationwithunorderedtuples

2. MathematiciansdefinearelationtobeasubsetofaCartesianproductofalistof domains.Youcanseethecorrespondencewithourtables. Wewillusethetermsrelationandtupleinplaceoftableandrowfromnowon. 3. Somemoreformalities: o letthetuplevariable refertoatupleoftherelation .

o o

Wesay

todenotethatthetuple isinrelation .

Then [branch_name] = [1] = the value of on the branch_name attribute.

o o

So [branch_name]= [1]=``Downtown'', and [customer_name]= [3]=``Johnson''.

4. We'llalsorequirethatthedomainsofallattributesbeindivisibleunits.
o o o o

Adomainisatomicifitselementsareindivisibleunits. Forexample,thesetofintegersisanatomicdomain. Thesetofallsetsofintegersisnot. Why?Integersdonothavesubparts,butsetsdotheintegerscomprising them.

Wecouldconsiderintegersnonatomicifwethoughtofthemasordered listsofdigits.

8.2 Database Schema 1. Wedistinguishbetweenadatabasescheme(logicaldesign)andadatabase instance(datainthedatabaseatapointintime).

2. Arelationschemeisalistofattributesandtheircorrespondingdomains. 3. Thetextusesthefollowingconventions: o italicsforallnames o lowercasenamesforrelationsandattributes o namesbeginningwithanuppercaseforrelationschemes Thesenoteswilldothesame. Forexample,therelationschemeforthedepositrelation: o Depositscheme=(bname,account#,cname,balance) Figure1.4:Thedepositandcustomerrelations WemaystatethatdepositisarelationonschemeDepositschemebywriting deposit(Depositscheme). Ifwewishtospecifydomains,wecanwrite:
o

(bname:string,account#:integer,cname:string,balance:integer).

Notethatcustomersareidentifiedbyname.Intherealworld,thiswouldnotbe allowed,astwoormorecustomersmightsharethesamename.

Figure1.5:ERdiagramforthebankingenterprise

4. Therelationschemesforthebankingexampleusedthroughoutthetextare: o Account_schema=(account_number,branch_name,balance) o Branchscheme=(branch_name,branch_city,assets) o Customerscheme=(customer_name,customer_street,customer_city) o Depositscheme=(customer_name,account_number) o Loan_schema=(loan_number,branch_name,amount) o Borrowscheme=(bname,loan#,cname,amount) Note:someattributesappearinseveralrelationschemes(e.g.bname,cname). Thisislegal,andprovidesawayofrelatingtuplesofdistinctrelations. 5. Whynotputallattributesinonerelation? Supposeweuseonelargerelationinsteadofcustomeranddeposit:
o

Accountscheme=(bname,account#,cname,balance,street,ccity)

o o o o o

Ifacustomerhasseveralaccounts,wemustduplicateherorhisaddress foreachaccount. Ifacustomerhasanaccountbutnocurrentaddress,wecannotbuilda tuple,aswehavenovaluesfortheaddress. Wewouldhavetousenullvaluesforthesefields. Nullvaluescausedifficultiesinthedatabase. Byusingtwoseparaterelations,wecandothiswithoutusingnullvalues

8.3 Keys 1. Thenotionsofsuperkey,candidatekeyandprimarykeyallapplytothe relationalmodel. Loanrelation LetKR KisasuperkeyofRifvaluesforKaresufficienttoidentifyauniquetupleofeach possiblerelationr(R) o bypossiblerwemeanarelationrthatcouldexistintheenterprisewe aremodeling. o Example:{customer_name,customer_street}and {customer_name} arebothsuperkeysofCustomer,ifnotwocustomerscanpossiblyhave thesamename Inreallife,anattributesuchascustomer_idwouldbeused insteadofcustomer_nametouniquelyidentifycustomers,butwe omitittokeepourexamplessmall,andinsteadassumecustomer namesareunique. KisacandidatekeyifKisminimal Example:{customer_name}isacandidatekeyforCustomer,sinceitisa superkeyandnosubsetofitisasuperkey. Primarykey:acandidatekeychosenastheprincipalmeansofidentifyingtuples withinarelation o Shouldchooseanattributewhosevaluenever,orveryrarely,changes. o E.g.emailaddressisunique,butmaychange Borrowerrelation

ForeignKeys Arelationschemamayhaveanattributethatcorrespondstotheprimarykeyof anotherrelation.Theattributeiscalledaforeignkey. o E.g.customer_nameandaccount_numberattributesofdepositorare foreignkeystocustomerandaccountrespectively. o Onlyvaluesoccurringintheprimarykeyattributeofthereferenced relationmayoccurintheforeignkeyattributeofthereferencing relation. Schemadiagram

8.4 Query Languages 1. Aquerylanguageisalanguageinwhichauserrequestsinformationfroma database.Thesearetypicallyhigherlevelthanprogramminglanguages. Theymaybeoneof: o Procedural,wheretheuserinstructsthesystemtoperformasequence ofoperationsonthedatabase.Thiswillcomputethedesiredinformation. o Nonprocedural,wheretheuserspecifiestheinformationdesiredwithout givingaprocedureforobtainingtheinformation. 2. Acompletequerylanguagealsocontainsfacilitiestoinsertanddeletetuplesas wellastomodifypartsofexistingtuples.

9. The Entity-Relationship Model

TheER(entityrelationship)datamodelviewstherealworldasasetofbasic objects(entities)andrelationshipsamongtheseobjects. ItisintendedprimarilyfortheDBdesignprocessbyallowingthespecificationof anenterprisescheme.ThisrepresentstheoveralllogicalstructureoftheDB.

9.1 Entity Sets

Anentityisathingorobjectintherealworldthatisdistinguishablefromall otherobjects.Forinstance,JohnHarriswithS.I.N.890123456isanentity,ashe canbeuniquelyidentifiedasoneparticularpersonintheuniverse. Anentitymaybeconcrete(apersonorabook,forexample)orabstract(likea holidayoraconcept). Anentitysetisasetofentitiesofthesametypethatsharethesameproperties, orattributes.(e.g.,allpersonshavinganaccountatabank). Entitysetsneednotbedisjoint.Forexample,theentitysetemployee(all employeesofabank)andtheentitysetcustomer(allcustomersofthebank) mayhavemembersincommon. Anentityisrepresentedbyasetofattributes. o E.g.name,S.I.N.,street,cityfor``customer''entity. o Thedomainoftheattributeisthesetofpermittedvalues(e.g.the telephonenumbermustbesevenpositiveintegers). Formally,anattributeisafunctionwhichmapsanentitysetintoadomain. o Everyentityisdescribedbyasetof(attribute,datavalue)pairs. o Thereisonepairforeachattributeoftheentityset. o E.g.aparticularcustomerentityisdescribedbytheset{(name,Harris), (S.I.N.,890123456),(street,North),(city,Georgetown)}.

Fig1.6EntitysetsCustomerandLoanFig1.7Relationshipsetborrower 9.2 Relationship Sets Arelationshipisanassociationamongseveralentities o Example: Hayes depositor A102 customerentity relationshipset accountentity Arelationshipsetisamathematicalrelationamongn2entities,eachtaken fromentitysets {(e1,e2,en)|e1E1,e2E2,,enEn} where(e1,e2,,en)isarelationship

Example: (Hayes,A102)depositor

Anattributecanalsobepropertyofarelationshipset. Forinstance,thedepositorrelationshipsetbetweenentitysetscustomerand accountmayhavetheattributeaccessdate

Fig1.8Access_dateasattributeofthedepositorrelationshipset.

Forexample,considerthetwoentitysetscustomerandaccount.Wedefinethe relationshipCustAccttodenotetheassociationbetweencustomersandtheiraccounts. Thisisabinaryrelationshipset. Goingbacktoourformaldefinition,therelationshipsetCustAcctisasubsetofallthe possiblecustomerandaccountpairings. Thisisabinaryrelationship.Occasionallytherearerelationshipsinvolvingmorethan twoentitysets. Theroleofanentityisthefunctionitplaysinarelationship.Forexample,the relationshipworksforcouldbeorderedpairsofemployeeentities.Thefirstemployee takestheroleofmanager,andthesecondonewilltaketheroleofworker. Arelationshipmayalsohavedescriptiveattributes.Forexample,date(lastdateof accountaccess)couldbeanattributeoftheCustAcctrelationshipset. 9.3 Attributes Anentityisrepresentedbyasetofattributes,thatisdescriptiveproperties possessedbyallmembersofanentityset.

Domainthesetofpermittedvaluesforeachattribute Attributetypes: o Simpleandcompositeattributes. Canbedividedintosubparts(Composite). Ex:name(first_name,middle_name,last_name) o Singlevaluedandmultivaluedattributes Ex:Singlevalued:foraspecificloanreferstoonlyoneloan number. Example:multivaluedattribute:phone_numbers o Derivedattributes Canbecomputedfromotherattributes Example:age,givendate_of_birth

Fig:1.9Compositeattributescustomer_nameandcustomer_address

Anattributetakesanullvaluewhenanentitydoesnothaveavalueforit.Thenullvalue mayindicatenotapplicablethatis,thatthevaluedoesnotexistfortheentity.

10. Constraints
10.1 Mapping Cardinalities MappingCardinalities,orcardinalityratios,expressthenumberofentitiestowhich anotherentitycanbeassociatedviaarelationshipset. Itdescribesbinaryrelationshipset.

OnetoOneOnetoMany

ManytoOne

ManytoMany

OnetoOne:AnentityinAisassociatedwithatmostoneentityinB,andanentity inBisassociatedwithatmostoneentityinA. Example: o Acustomerisassociatedwithatmostoneloanviatherelationshipborrower o Aloanisassociatedwithatmostonecustomerviaborrower Onetomany:AnentityinAisassociatedwithanynumber(zeroormore)ofentities inB.AnentityinB,however,canbeassociatedwithatmostoneentityinA. Example:

o Intheonetomanyrelationshipaloanisassociatedwithatmostonecustomer viaborrower,acustomerisassociatedwithseveral(including0)loansvia borrower

Manytoone:AnentityinAisassociatedwithatmostoneentityinB.AnentityinB, however,canbeassociatedwithanynumber(zeroormore)ofentitiesinA. Example: o Inamanytoonerelationshipaloanisassociatedwithseveral(including0) customersviaborrower,acustomerisassociatedwithatmostoneloanvia borrower

Manytoone:AnentityinAisassociatedwithanynumber(zeroormore)ofentities inB,andanentityinB,isassociatedwithanynumber(zeroormore)ofentitiesinA. Example: o Acustomerisassociatedwithseveral(possibly0)loansviaborrower o Aloanisassociatedwithseveral(possibly0)customersviaborrower

10.2 Keys Touniquelyidentifytheentity. Toidentityasetofattributesthatsufficetodistinguishentitiesfromeachother. 10.2.1 Entity Sets Asuperkeyofanentitysetisasetofoneormoreattributeswhosevalues uniquelydetermineeachentity. Ex:Customer_id Acandidatekeyofanentitysetisaminimalsuperkey o Thecombinationofattributes,thatdistinguishtherecords. o Customer_idiscandidatekeyofcustomer o account_numberiscandidatekeyofaccount Althoughseveralcandidatekeysmayexist,oneofthecandidatekeysisselected tobetheprimarykey. Akey(primary,candidate,andsuper)isapropertyoftheentityset,ratherthan oftheindividualentities. 10.2.2 Relationship Sets Thecombinationofprimarykeysoftheparticipatingentitysetsformsasuper keyofarelationshipset. o (customer_id,account_number)isthesuperkeyofdepositor o NOTE:thismeansapairofentitysetscanhaveatmostonerelationship inaparticularrelationshipset. Example:ifwewishtotrackallaccess_datestoeachaccountby eachcustomer,wecannotassumearelationshipforeachaccess. Wecanuseamultivaluedattributethough Mustconsiderthemappingcardinalityoftherelationshipsetwhendeciding whatarethecandidatekeys Needtoconsidersemanticsofrelationshipsetinselectingtheprimarykeyin caseofmorethanonecandidatekey

11. The Entity Relationship Diagram

WecanexpresstheoveralllogicalstructureofadatabasegraphicallywithanER diagram. Itscomponentsare:

Rectanglesrepresententitysets. Diamondsrepresentrelationshipsets. Lineslinkattributestoentitysetsandentitysetstorelationshipsets. Ellipsesrepresentattributes

Doubleellipsesrepresentmultivaluedattributes. Dashedellipsesdenotederivedattributes. Underlineindicatesprimarykeyattributes(willstudylater) Doublelinetotalparticipationofanentityinarelationshipset Doublerectanglesrepresentweakentitysets.

ExampleforERdiagram:

Adirectedline()fromtherelationshipsetborrowertotheentitysetloan specifiesthatborroweriseitheraonetooneormanytoonerelationshipset, fromcustomertoloan;borrowercannotbeamanytomanyoraonetomany relationshipsetfromcustomertoloan. Anundirectedline()fromtherelationshipsetborrowertotheentitysetloan specifiesthatborroweriseitheramanytomanyoronetomanyrelationshipset fromcustomertoloan.

ERDiagramwithcomposite,multivalued,andderivedattributes. o Compositeattribute:address o Multivaluedattribute:phone_number o Derivedattribute:age

CompositeandMultivaluedAttributes Compositeattributesareflattenedoutbycreatingaseparateattributeforeach componentattribute o Example:givenentitysetcustomerwithcompositeattributenamewith componentattributesfirst_nameandlast_nametheschema correspondingtotheentitysethastwoattributes name.first_nameandname.last_name AmultivaluedattributeMofanentityEisrepresentedbyaseparateschemaEM o SchemaEMhasattributescorrespondingtotheprimarykeyofEandan attributecorrespondingtomultivaluedattributeM o Example:Multivaluedattributedependent_namesofemployeeis representedbyaschema: employee_dependent_names=(employee_id,dname) o Eachvalueofthemultivaluedattributemapstoaseparatetupleofthe relationonschemaEM Forexample,anemployeeentitywithprimarykey123456789 anddependentsJackandJanemapstotwotuples: (123456789,Jack)and(123456789,Jane) RolesinERDiagrams Entitysetsofarelationshipneednotbedistinct Thelabelsmanagerandworkerarecalledroles;theyspecifyhowemployee entitiesinteractviatheworks_forrelationshipset. RolesareindicatedinERdiagramsbylabelingthelinesthatconnectdiamonds torectangles. Rolelabelsareoptional,andareusedtoclarifysemanticsoftherelationship

ERdiagramwithroleindicators Cardinalitylimitscanalsoexpressparticipationconstraints

o Eachloanmusthaveexactlyoneassociatedcustomer.Thelimit0..*onthe edgefromcustomertoborrowerindicatesthatacustomercanhavezeroor moreloans,Thus,therelationshipborrowerisonetomanyfromcustomerto loan,andfurthertheparticipationofloaninborroweristotal. ERdiagramwithaternaryrelationship

SymbolsusingforERNotation

12. Extended E-R Features

12.1 Specialization (Top down approach) Topdowndesignprocess;wedesignatesubgroupingswithinanentityset thataredistinctivefromotherentitiesintheset. Thesesubgroupingsbecomelowerlevelentitysetsthathaveattributesor participateinrelationshipsthatdonotapplytothehigherlevelentityset. DepictedbyatrianglecomponentlabeledISA(E.g.customerisaperson). Attributeinheritancealowerlevelentitysetinheritsalltheattributesand relationshipparticipationofthehigherlevelentitysettowhichitislinked.

12.2 Generalization (Bottom-up approach) Abottomupdesignprocesscombineanumberofentitysetsthatsharethe samefeaturesintoahigherlevelentityset.

Specializationandgeneralizationaresimpleinversionsofeachother;theyare representedinanERdiagraminthesameway. Thetermsspecializationandgeneralizationareusedinterchangeably.

Ageneralizationrelationshipspecifiesthatseveraltypesofentitieswithcertain commonattributescanbegeneralizedintohigherlevelentityclassorsuperclassentity. (ie.Combinenumberofentitysetthatsharethesamefeaturesintoahigherlevelentity set)

Generalizationhidesdifferencesandemphasizessimilarities. Distinctionmadethroughattributeinheritance. Attributesofhigherlevelentityareinheritedbylowerlevelentities. Twomethodsforconversiontoatableform: o Createatableforthehighlevelentity,plustablesforthelowerlevel entitiescontainingalsotheirspecificattributes. o Createonlytablesforthelowerlevelentities.

13. The Network Model

Dataarerepresentedbycollectionsofrecords. o similartoanentityintheERmodel o Recordsandtheirfieldsarerepresentedasrecordtype type end customer=record customername:string; customerstreet:string; customercity:string; type account=record accountnumber:integer; balance:integer; end

Relationshipsamongdataarerepresentedbylinks o similartoarestricted(binary)formofanERrelationship o Restrictionsonlinksdependonwhethertherelationshipismanymany, manytoone,oronetoone. Sincealinkcannotcontainanydatavalue,representanERrelationshipwith attributeswithanewrecordtypeandlinks.

 Example

SampleDatabaseforNetwork

14. The Hierarchical Model

Ahierarchicaldatabaseconsistsofacollectionofrecordswhichareconnectedto oneanotherthroughlinks. Arecordisacollectionoffields,eachofwhichcontainsonlyonedatavalue. Alinkisanassociationbetweenpreciselytworecords. Thehierarchicalmodeldiffersfromthenetworkmodelinthattherecordsare organizedascollectionsoftreesratherthanasarbitrarygraphs.

TreeStructureDiagram Theschemaforahierarchicaldatabaseconsistsof o boxes,whichcorrespondtorecordtypes o lines,whichcorrespondtolinks Recordtypesareorganizedintheformofarootedtree. o Nocyclesintheunderlyinggraph. o Relationshipsformedinthegraphmustbesuchthatonlyonetomanyor onetoonerelationshipsexistbetweenaparentandachild.

GeneralStructure

Aparentmayhaveanarrowpointingtoachild,butachildmusthaveanarrow pointingtoitsparent.

REFERENCES: Abraham Silberschatz, Henry Korth.F and Sudarsham.S, 2006. Database System Concepts, [Fifth Edition], Tata McGraw Hill, New Delhi. Web Reference : http://db-book.com/ - Book Author Home Page

www.cs.sfu.ca/CC/354/zaiane

Educationisaprogressivediscoveryofourownignorance.WillDurant ***ALLTHEBEST***