introducttion to Relational Databases ppt

introducttion to Relational Databases ppt

1. CS4451 DATABASE MANAGEMENT SYSTEMS
UNIT–IRELATIONALDATABASES
Introductiontodatabases-PurposeofDatabaseSystem-DatabasesystemApplications-Views
ofdata-DataModels-Filesystem,HierarchicalandNetwork-DatabasesystemArchitecture-
RelationalModel-keys-RelationalAlgebra.
I.Introductiontodatabases
DBMScontainsinformationaboutaparticularenterprise
Collectionofinterrelateddata
Setofprogramstoaccessthedata
Anenvironmentthatisbothconvenientandefficienttouse

2. Databasesystemsareusedtomanagecollectionsofdatathatare:
Highlyvaluable
Relativelylarge
Accessedbymultipleusersandapplications,oftenatthesametime.
Amoderndatabasesystemisacomplexsoftwaresystemwhosetaskistomanagealarge,
complexcollectionofdata.
Databasestouchallaspectsofourlives
DatabaseApplicationsExamples
EnterpriseInformation
Sales:customers,products,purchases

3. Accounting:payments,receipts,assets
HumanResources:Informationaboutemployees,salaries,payrolltaxes.
Manufacturing:managementofproduction,inventory,orders,supplychain.
Bankingandfinance
customerinformation,accounts,loans,andbankingtransactions.
Creditcardtransactions
Finance:salesandpurchasesoffinancialinstruments(e.g.,stocksandbonds;storingreal-
timemarketdata
Universities:registration,grades
Airlines:reservations,schedules

4. Telecommunication:recordsofcalls,texts,anddatausage,generatingmonthlybills,
maintainingbalancesonprepaidcallingcards
Web-basedservices
Onlineretailers:ordertracking,customizedrecommendations
Onlineadvertisements
Documentdatabases
Navigationsystems:Formaintainingthelocationsofvariesplacesofinterestalongwiththe
exactroutesofroads,trainsystems,buses,etc.

5. Soadatabaseisacollectionofrelateddatathatwecanusefor
Defining-specifyingtypesofdata
Constructing-storing&populating
Manipulating-querying,updating,reporting
DisadvantagesOfFileSystemOverDb
Intheearlydays,File-Processingsystemisusedtostorerecords.Itusesvariousfilesforstoring
therecords.Drawbacksofusingfilesystemstostoredata:
Dataredundancyandinconsistency
Multiplefileformats,duplicationofinformationindifferentfiles
Difficultyinaccessingdata
Needtowriteanewprogramtocarryouteachnewtask

6. Dataisolation—multiplefilesandformats
Integrityproblems
Hardtoaddnewconstraintsorchangeexistingones
Atomicityproblem
FailuresmayleavedatabaseinaninconsistentstatewithpartialupdatescarriedOut.
E.g.transferoffundsfromoneaccounttoanothershouldeithercompleteornothappenat
all
Concurrentaccessanomalies
Concurrentaccessedneededforperformance
Securityproblems
Databasesystemsoffersolutionstoalltheaboveproblems

7. II. PURPOSE OF DATABASE SYSTEM
Thetypicalfileprocessingsystemissupportedbyaconventionaloperatingsystem.
Thesystemstorespermanentrecordsinvariousfiles,anditneedsdifferentapplication
programstoextractrecordsfrom,andaddrecordsto,theappropriatefiles.
Afileprocessingsystemhasanumberofmajordisadvantages.
Dataredundancyandinconsistency
Difficultyinaccessingdata
Dataisolation–multiplefilesandformats
Integrityproblems
Atomicityofupdates

8. Concurrentaccessbymultipleusers
Securityproblems
1.Dataredundancyandinconsistency:
Infileprocessing,everyusergroupmaintainsitsownfilesforhandlingitsdataprocessing
applications.
Example:
ConsidertheUNIVERSITYdatabase.Here,twogroupsofusersmightbethecourseregistration
personnelandtheaccountingoffice.
Theaccountingofficealsokeepsdataonregistrationandrelatedbillinginformation,whereasthe
registrationofficekeepstrackofstudentcoursesandgrades.
Storingthesamedatamultipletimesiscalleddataredundancy.Thisredundancyleadstoseveral
problems.

9. Needtoperformasinglelogicalupdatemultipletimes.
Storagespaceiswasted.
Filesthatrepresentthesamedatamaybecomeinconsistent.
DatainconsistencyisthevariouscopiesofthesamedatamaynolargerAgree.
2.Difficultyinaccessingdata
Fileprocessingenvironmentsdonotallowneededdatatoberetrievedinaconvenientand
efficientmanner.
3.Dataisolation
Becausedataarescatteredinvariousfiles,andfilesmaybeindifferentformats,writingnew
applicationprogramstoretrievetheappropriatedataisdifficult.

10. 4.Integrityproblems
Thedatavaluesstoredinthedatabasemustsatisfycertaintypesofconsistencyconstraints.
Example:
Thebalanceofcertaintypesofbankaccountsmayneverfallbelowaprescribedamount.Developers
enforcetheseconstraintsinthesystembyadditionappropriatecodeinthevariousapplicationprograms
5.Atomicityproblems
Atomicmeansthetransactionmusthappeninitsentiretyornotatall.Itisdifficulttoensureatomicityina
conventionalfileprocessingsystem.
Example:
Consideraprogramtotransfer$50fromaccountAtoaccountB.Ifasystemfailureoccursduringthe
executionoftheprogram,itispossiblethatthe$50wasremovedfromaccountAbutwasnotcreditedto
accountB,resultinginaninconsistentdatabasestate.

11. 6.Concurrentaccessanomalies
Forthesakeofoverallperformanceofthesystemandfasterresponse,manysystemsallowmultiple
userstoupdatethedatasimultaneously.
Insuchanenvironment,interactionofconcurrentupdatesispossibleandmayresultininconsistent
data.Toguardagainstthispossibility,thesystemmustmaintainsomeformofsupervision.
Butsupervisionisdifficulttoprovidebecausedatamaybeaccessedbymanydifferentapplication
programsthathavenotbeencoordinatedpreviously.
Example:Whenseveralreservationclerkstrytoassignaseatonanairlineflight,thesystemshouldensure
thateachseatcanbeaccessedbyonlyoneclerkatatimeforassignmenttoapassenger.
7.Securityproblems
Enforcingsecurityconstraintstothefileprocessingsystemisdifficult.

12. III. APPLICATION OF DATABASE
DatabaseApplications
Banking:alltransactions
Airlines:reservations,schedules
Universities:registration,grades
Sales:customers,products,purchases
Manufacturing:production,inventory,orders,supplychain
Humanresources:employeerecords,salaries,taxdeductions
Telecommunication:CallHistory,Billing
Creditcardtransactions:Purchasedetails,Statements

13. IV. VIEWS OF DATA
Itrefersthathowdatabaseisactuallystoredindatabase,whatdataandstructureofdatausedby
databasefordata.
Sodescribeallthisdatabaseprovidesuserwithviewsandtheseare
Dataabstraction
Instancesandschema
Dataabstraction
Asadataindatabasearestoredwithverycomplexdatastructuresowhenusercomeandwanttoaccess
anydata,hewillnotbeabletoaccessdataifhehasgothroughthisdatastructure.
Sotosimplifytheinteractionofuseranddatabase,DBMShidessomeinformationwhichisnotof
userinterest,athisiscalleddataabstraction:-Sodeveloperhidescomplexityfromuserandstore
abstractviewofdata.

14. Dataabstractionhasthreelevelofabstractions
Physicallevel/internallevel
Logicallevel/conceptuallevel
viewlevel/externallevel
Physicallevel:-thisisthelowestlevelofdataabstractionwhichdescribeHowdataisactual
storedindatabase.Thislevelbasicallydescribethedatastructureandaccesspath
/indexinguseforaccessingfile.
Logicallevel:-Thenextlevelofabstractiondescribewhatdataarestoredinthedatabase
andwhataretherelationshipexistedamongthoseofdata.
Viewlevel:-Inthisleveluseronlyinteractwithdatabaseandthecomplexityremainunview
.userseedataandtheremaybemanyviewsofonedatalikechartandgraph.

15. V. DATA MODELS IN DBMS
ADataModelisalogicalstructureofDatabase.
Itdescribesthedesignofdatabasetoreflectentities,attributes,relationshipamongdata,constrains
etc.

16. TypesofDataModels:
ObjectbasedlogicalModels–Describedataattheconceptualandviewlevels.
1.E-RModel
Anentity–relationshipmodel(ERmodel)isasystematicwayofdescribinganddefininga
businessprocess.AnERmodelistypicallyimplementedasadatabase.Themaincomponents
ofE-Rmodelare:entitysetandrelationshipset.

17. 2.ObjectorientedModel
Anobjectdatamodelisadatamodelbasedonobject-orientedprogramming,associatingmethods
(procedures)withobjectsthatcanbenefitfromclasshierarchies.
Thus,―objectsarelevelsofabstractionthatincludeattributesandbehavior.
RecordbasedlogicalModels
LikeObjectbasedmodel,theyalsodescribedataattheconceptualandviewlevels.
Thesemodelsspecifylogicalstructureofdatabasewithrecords,fieldsandattributes.
1.RelationalModel
Inrelationalmodel,thedataandrelationshipsarerepresentedbycollectionofinter-relatedtables.
Eachtableisagroupofcolumnandrows,wherecolumnrepresentsattributeofanentityandrows
representsrecords.

18. SamplerelationshipModel:Studenttablewith3columnsandthreerecords.
2.HierarchicalModel
Inhierarchicalmodel,dataisorganizedintoatreelikestructurewitheachrecordishaving
oneparentrecordandmanychildren.
Themaindrawbackofthismodelisthat,itcanhaveonlyonetomanyrelationshipsbetween
nodes.
SampleHierarchicalModelDiagram

19. 3.NetworkModel
NetworkModelissameashierarchicalmodelexceptthatithasgraph-likestructureratherthanatree-
basedstructure.
Unlikehierarchicalmodel,thismodelallowseachrecordtohavemorethanoneparentrecord.
PhysicalDataModels–Thesemodelsdescribedataatthelowestlevelofabstraction.
ThreeSchemaArchitecture
Thegoalofthethreeschemaarchitectureistoseparatetheuserapplicationsandthephysicaldatabase.
Theschemascanbedefinedatthefollowinglevels:
1.Theinternallevel
Ithasaninternalschemawhichdescribesthephysicalstoragestructureofthedatabase.
Usesaphysicaldatamodelanddescribesthecompletedetailsofdatastorageandaccesspathsforthe
database.

20. 2.Theconceptuallevel
Ithasaconceptualschemawhichdescribesthestructureofthedatabaseforusers.
Ithidesthedetailsofthephysicalstoragestructures,andconcentratesondescribingentities,
datatypes,relationships,useroperationsandconstraints.
Usuallyarepresentationaldatamodelisusedtodescribetheconceptualschema.
3.TheExternalorViewlevel
Itincludesexternalschemasoruservies.
Eachexternalschemadescribesthepartofthedatabasethataparticularusergroupis
interestedinandhidestherestofthedatabasefromthatusergroup.
Representedusingtherepresentationaldatamodel

21. Thethreeschemaarchitectureisusedtovisualizetheschemalevelsinadatabase.
Thethreeschemasareonlydescriptionsofdata,thedataonlyactuallyexistsisatthephysical
level

22. VI. File System
Filebasedsystemswereanearlyattempttocomputerizethemanualsystem.
Itisalsocalledatraditionalbasedapproachinwhichadecentralizedapproachwastaken
whereeachdepartmentstoredandcontrolleditsowndatawiththehelpofadataprocessing
specialist.
Themainroleofadataprocessingspecialistwastocreatethenecessarycomputerfile
structures,andalsomanagethedatawithinstructuresanddesignsomeapplicationprograms
thatcreatereportsbasedonfiledata.

23. Intheabovefigure:
Consideranexampleofastudent'sfilesystem.
Thestudentfilewillcontaininformationregardingthestudent(i.e.rollno,studentname,courseetc.).
Similarly,wehaveasubjectfilethatcontainsinformationaboutthesubjectandtheresultfilewhich
containstheinformationregardingtheresult.
Somefieldsareduplicatedinmorethanonefile,whichleadstodataredundancy.Sotoovercomethis
problem,weneedtocreateacentralizedsystem,i.e.DBMSapproach.

24. DBMS:
Adatabaseapproachisawell-organizedcollectionofdatathatarerelatedinameaningful
waywhichcanbeaccessedbydifferentusersbutstoredonlyonceinasystem.
ThevariousoperationsperformedbytheDBMSsystemare:Insertion,deletion,selection,
sortingetc.
In the above figure,
In the above figure, duplication of data is reduced due to centralization of data.

26. VII. Hierarchical and Network
1. Hierarchical Data Model:
Hierarchical data model is the oldest type of the data model.
ItwasdevelopedbyIBMin1968.
Itorganizesdatainthetree-likestructure.Hierarchicalmodelconsistsofthefollowing:
Itcontainsnodeswhichareconnectedbybranches.
Thetopmostnodeiscalledtherootnode.
Iftherearemultiplenodesappearatthetoplevel,thenthesecanbecalledasroot
segments.
Eachnodehasexactlyoneparent.
Oneparentmayhavemanychild.

27. Intheabovefigure,Electronicsistherootnodewhichhastwochildreni.e.Televisionsand
PortableElectronics.
Thesetwohasfurtherchildrenforwhichtheyactasparent.Forexample:Televisionhas
childrenasTube,LCDandPlasma,forthesethreeTelevisionactasparent.
Itfollowsonetomanyrelationship.

28. 2.NetworkDataModel:
Itistheadvanceversionofthehierarchicaldatamodel.
Toorganizedataitusesdirectedgraphsinsteadofthetree-structure.
Inthischildcanhavemorethanoneparent.
Itusestheconceptofthetwodatastructuresi.e.RecordsandSets.
Intheabovefigure,Projectistherootnodewhichhastwo
childreni.e.Project1andProject2.
Project1has3childrenandProject2has2children.
Totalthereare5childreni.eDepartmentA,DepartmentB
andDepartmentC,theyarenetworkrelatedchildrenaswe
saidthatthismodelcanhavemorethanoneparent.
So,fortheDepartmentBandDepartmentChavetwo
parentsi.e.Project1andProject2.

29. F

30. C

31. VIII. Database system Architecture
ADatabasestoresalotofcriticalinformationtoaccessdataquicklyandsecurely.
Henceitisimportanttoselectthecorrectarchitectureforefficientdatamanagement.
DBMSArchitecturehelpsuserstogettheirrequestsdonewhileconnectingtothedatabase.
Wechoosedatabasearchitecturedependingonseveralfactorslikethesizeofthedatabase,
numberofusers,andrelationshipsbetweentheusers.
Therearetwotypesofdatabasemodelsthatwegenerallyuse,logicalmodelandphysical
model.
Severaltypesofarchitecturearethereinthedatabasewhichwewilldealwithinthenext
section.

32. Types of DBMS Architecture
ThereareseveraltypesofDBMSArchitecturethatweuseaccordingtotheusagerequirements.
TypesofDBMSArchitecturearediscussedhere.
1-TierArchitecture
2-TierArchitecture
3-TierArchitecture
1-TierArchitecture
In1-TierArchitecturethedatabaseisdirectlyavailabletotheuser,theusercandirectlysit
ontheDBMSanduseitthatis,theclient,server,andDatabaseareallpresentonthesame
machine.
ForExample:tolearnSQLwesetupanSQLserverandthedatabaseonthelocalsystem.

33. Advantages of 1-Tier Architecture
SimpleArchitecture:1-TierArchitectureisthemostsimplearchitecturetosetup,asonlyasingle
machineisrequiredtomaintainit.
Cost-Effective:Noadditionalhardwareisrequiredforimplementing1-TierArchitecture,whichmakesit
cost-effective.
EasytoImplement:1-TierArchitecturecanbeeasilydeployed,andhenceitismostlyusedinsmall
projects.

34. 2-TierArchitecture
The2-tierarchitectureissimilartoabasicclient-servermodel.
Theapplicationattheclientenddirectlycommunicateswiththedatabaseontheserverside.
APIslikeODBCandJDBCareusedforthisinteraction.
Theserversideisresponsibleforprovidingqueryprocessingandtransactionmanagement
functionalities.
Ontheclientside,theuserinterfacesandapplicationprogramsarerun.
Theapplicationontheclientsideestablishesaconnectionwiththeserversidetocommunicate
withtheDBMS.

35. Anadvantageofthistypeisthatmaintenanceandunderstandingareeasier,andcompatible
withexistingsystems.
However,thismodelgivespoorperformancewhentherearealargenumberofusers.
Advantagesof2-TierArchitecture
EasytoAccess:2-TierArchitecturemakeseasyaccesstothedatabase,whichmakesfast
retrieval.
Scalable:Wecanscalethedatabaseeasily,byaddingclientsorupgradinghardware.
LowCost:2-TierArchitectureischeaperthan3-TierArchitectureandMulti-TierArchitecture.
EasyDeployment:2-TierArchitectureiseasiertodeploythan3-TierArchitecture.
Simple:2-TierArchitectureiseasilyunderstandableaswellassimplebecauseofonlytwo
components.

36. 3-TierArchitecture
In3-TierArchitecture,thereisanotherlayerbetweentheclientandtheserver.
Theclientdoesnotdirectlycommunicatewiththeserver.Instead,itinteractswithan
applicationserverwhichfurthercommunicateswiththedatabasesystemandthenthequery
processingandtransactionmanagementtakesplace.
Thisintermediatelayeractsasamediumfortheexchangeofpartiallyprocesseddatabetween
theserverandtheclient.
Thistypeofarchitectureisusedinthecaseoflargewebapplications.

37. Advantagesof3-TierArchitecture
Enhancedscalability:Scalabilityisenhancedduetothedistributeddeploymentofapplicationservers.
Now,individualconnectionsneednotbemadebetweentheclientandserver.
DataIntegrity:3-TierArchitecturemaintainsDataIntegrity.Sincethereisamiddlelayerbetweenthe
clientandtheserver,datacorruptioncanbeavoided/removed.
Security:3-TierArchitectureImprovesSecurity.Thistypeofmodelpreventsdirectinteractionofthe
clientwiththeservertherebyreducingaccesstounauthorizeddata.

38. IX. Relational Model
Structure of Relational Databases
Arelationaldatabaseconsistsofacollectionoftables,eachofwhichisassignedaunique
name.
Forexample,considertheinstructortableofbelowfigure,whichstoresinformationabout
instructors.
Thetablehasfourcolumnheaders:ID,name,deptname,andsalary.
Eachrowofthistablerecordsinformationaboutaninstructor,consistingoftheinstructor’sID,
name,deptname,andsalary.

40. RelationSchemaandInstance
A
1,A
2,…,A
nareattributes
R=(A
1,A
2,…,A
n)isarelationschema
Example:
instructor=(ID,name,dept_name,salary)
ArelationinstancerdefinedoverschemaRisdenotedbyr(R).
Thecurrentvaluesarelationarespecifiedbyatable
Anelementtofrelationriscalledatupleandisrepresentedbyarowinatable

41. Attributes
Thesetofallowedvaluesforeachattributeiscalledthedomainoftheattribute.
Attributevaluesare(normally)requiredtobeatomic;thatis,indivisible.
Thespecialvaluenullisamemberofeverydomain.Indicatedthatthevalueis“unknown”.
Thenullvaluecausescomplicationsinthedefinitionofmanyoperations.
RelationsareUnordered
Orderoftuplesisirrelevant(tuplesmaybestoredinanarbitraryorder)
Example:instructorrelationwithunorderedtuples

42. Database Schema
Database schema --is the logical structure of the database.
Database instance --is a snapshot of the data in the database at a given instant in time.

43. X. Keys
Keysplayanimportantroleintherelationaldatabase.
Itisusedtouniquelyidentifyanyrecordorrowofdatafromthetable.Itisalsousedto
establishandidentifyrelationshipsbetweentables.
Forexample,IDisusedasakeyintheStudenttablebecauseitisuniqueforeachstudent.In
thePERSONtable,passport_number,license_number,SSNarekeyssincetheyareuniquefor
eachperson.

44. Typesofkeys:
1.Primarykey
Itisthefirstkeyusedtoidentifyoneandonlyoneinstanceofanentityuniquely.
Anentitycancontainmultiplekeys,aswesawinthePERSONtable.
Thekeywhichismostsuitablefromthoselistsbecomesaprimarykey.
IntheEMPLOYEEtable,IDcanbetheprimarykeysinceitisuniqueforeachemployee.

45. IntheEMPLOYEEtable,wecanevenselectLicense_NumberandPassport_Numberasprimarykeys
sincetheyarealsounique.
Foreachentity,theprimarykeyselectionisbasedonrequirementsanddevelopers.
2.Candidatekey
Acandidatekeyisanattributeorsetofattributesthatcanuniquelyidentifyatuple.
Exceptfortheprimarykey,theremainingattributesareconsideredacandidatekey.
Thecandidatekeysareasstrongastheprimarykey.

46. Forexample:IntheEMPLOYEEtable,idisbestsuitedfortheprimarykey.
Therestoftheattributes,likeSSN,Passport_Number,License_Number,etc.,areconsidereda
candidatekey.
3.SuperKey
Superkeyisanattributesetthatcanuniquelyidentifyatuple.
Asuperkeyisasupersetofacandidatekey.

47. Forexample:IntheaboveEMPLOYEEtable,for(EMPLOEE_ID,EMPLOYEE_NAME), the
nameoftwoemployeescanbethesame,buttheirEMPLYEE_IDcan'tbethesame.
Hence,thiscombinationcanalsobeakey.
ThesuperkeywouldbeEMPLOYEE-ID(EMPLOYEE_ID,EMPLOYEE-NAME),etc.

48. 4.Foreignkey
Foreignkeysarethecolumnofthetableusedtopointtotheprimarykeyofanothertable.
Everyemployeeworksinaspecificdepartmentinacompany,andemployeeanddepartmentaretwodifferent
entities.Sowecan'tstorethedepartment'sinformationintheemployeetable.That'swhywelinkthesetwotables
throughtheprimarykeyofonetable.
WeaddtheprimarykeyoftheDEPARTMENTtable,Department_Id,asanewattributeintheEMPLOYEEtable.
IntheEMPLOYEEtable,Department_Idistheforeignkey,andboththetablesarerelated.

49. 5.Alternatekey
Theremaybeoneormoreattributesoracombinationofattributesthatuniquelyidentifyeach
tupleinarelation.
Theseattributesorcombinationsoftheattributesarecalledthecandidatekeys.
Onekeyischosenastheprimarykeyfromthesecandidatekeys,andtheremainingcandidate
key,ifitexists,istermedthealternatekey.
Inotherwords,thetotalnumberofthealternatekeysisthetotalnumberofcandidatekeys
minustheprimarykey.
Thealternatekeymayormaynotexist.
Ifthereisonlyonecandidatekeyinarelation,itdoesnothaveanalternatekey.

50. Forexample,employeerelationhastwoattributes,Employee_IdandPAN_No,thatactas
candidatekeys.
Inthisrelation,Employee_Idischosenastheprimarykey,sotheothercandidatekey,
PAN_No,actsastheAlternatekey.
6.Compositekey
Wheneveraprimarykeyconsistsofmorethanoneattribute,itisknownasacompositekey.
ThiskeyisalsoknownasConcatenatedKey.

51. Forexample,inemployeerelations,weassumethatanemployeemaybeassignedmultiple
roles,andanemployeemayworkonmultipleprojectssimultaneously.
Sotheprimarykeywillbecomposedofallthreeattributes,namelyEmp_ID,Emp_role,and
Proj_IDincombination.
Sotheseattributesactasacompositekeysincetheprimarykeycomprisesmorethanone
attribute.

52. 7.Artificialkey
Thekeycreatedusingarbitrarilyassigneddataareknownasartificialkeys.
Thesekeysarecreatedwhenaprimarykeyislargeandcomplexandhasnorelationshipwith
manyotherrelations.
Thedatavaluesoftheartificialkeysareusuallynumberedinaserialorder.

53. XI. Relational Algebra
Therelationalalgebraconsistsofasetofoperationsthattakeoneortworelationsasinputand
produceanewrelationastheirresult.
Someoftheseoperations,suchastheselect,project,andrenameoperations,arecalledunary
operationsbecausetheyoperateononerelation.
Theotheroperations,suchasunion,Cartesianproduct,andsetdifference,operateonpairsof
relationsandare,therefore,calledbinaryoperations.

54. Six basic operators
1.select: 
2.project: 
3.union: 
4.set difference: –
5.Cartesian product: x
6.rename: 
1.SelectOperation
Theselectoperationselectstuplesthatsatisfyagivenpredicate.
Notation:
p(r)
piscalledtheselectionpredicate

55. Example:selectthosetuplesoftheinstructorrelationwheretheinstructorisinthe“Physics”
department.
Query : 
dept_name=“Physics” (instructor)
Result
Instructor table

56. We allow comparisons using in the selection predicate.
=, , >, . <. 
We can combine several predicates into a larger predicate by using the connectives:
(and), (or), (not)
Example: Find the instructors in Physics with a salary greater $90,000, we write:

dept_name=“Physics”
salary >90,000(instructor)
The select predicate may include comparisons between two attributes.
Example, find all departments whose name is the same as their building name:

dept_name=building(department)

57. 2.Project Operation
Aunaryoperationthatreturnsitsargumentrelation,withcertainattributesleftout.
Notation:

A
1
,A
2
,A
3
….A
k
(r)
whereA
1,A
2,…,A
kareattributenamesandrisarelationname.
Theresultisdefinedastherelationofkcolumnsobtainedbyerasingthecolumnsthatarenot
listed
Duplicaterowsremovedfromresult,sincerelationsaresets

58. Project Operation Example
•Example: eliminate the dept_nameattribute of instructor
•Query:

ID, name, salary(instructor)
•Result:

59. Composition of Relational Operations
The result of a relational-algebra operation is relation and therefore of relational-algebra
operations can be composed together into a relational-algebra expression.
Consider the query --Find the names of all instructors in the Physics department.

name(
dept_name=“Physics”(instructor))
Instead of giving the name of a relation as the argument of the projection operation, we give
an expression that evaluates to a relation.

60. Cartesian-Product Operation
The Cartesian-product operation (denoted by X) allows us to combine information from any
two relations.
Example: the Cartesian product of the relations instructorand teachesis written as:
instructorX teaches
We construct a tuple of the result out of each possible pair of tuples: one from the instructor
relation and one from the teachesrelation
Since the instructorID appears in both relations we distinguish between these attribute by
attaching to the attribute the name of the relation from which the attribute originally came.
instructor.ID
teaches.ID

61. TheinstructorX teaches table

62. Join Operation
TheCartesian-Productassociateseverytupleofinstructorwitheverytupleofteaches.
instructorXteaches
MostoftheresultingrowshaveinformationaboutinstructorswhodidNOTteacha
particularcourse.
Togetonlythosetuplesof“instructorXteaches“thatpertaintoinstructorsandthecourses
thattheytaught,wewrite:

instructor.id=teaches.id(instructorxteaches))
Wegetonlythosetuplesof“instructorXteaches”thatpertaintoinstructorsandthecourses
thattheytaught.

63. Thetable corresponding to:

instructor.id = teaches.id(instructor xteaches))

64. ThejoinoperationallowsustocombineaselectoperationandaCartesian-Productoperation
intoasingleoperation.
Considerrelationsr(R)ands(S)
Let“theta”beapredicateonattributesintheschemaR“union”S.Thejoinoperationr⋈
??????sis
definedasfollows:
&#3627408479;⋈
??????&#3627408480;=??????
??????(&#3627408479;×&#3627408480;)
Thus

instructor.id=teaches.id(instructorxteaches))
Canequivalentlybewrittenas
instructor⋈
Instructor.id=teaches.idteaches.

65. Union Operation
Theunionoperationallowsustocombinetworelations
Notation:rs
Forrstobevalid.
1.r,smusthavethesamearity(samenumberofattributes)
2.Theattributedomainsmustbecompatible(example:2
nd
columnofrdealswiththesametypeofvaluesasdoesthe
2
nd
columnofs)
Example: to find all courses taught in the Fall 2017 semester, or in the Spring 2018 semester, or
in both

course_id(
semester=“Fall”Λyear=2017 (section)) 

course_id(
semester=“Spring”Λyear=2018 (section))

66. Result of:

course_id(
semester=“Fall”Λyear=2017 (section)) 

course_id(
semester=“Spring”Λyear=2018 (section))
Set-Intersection Operation
The set-intersection operation allows us to find tuples that are in both the input relations.
Notation: r s
Assume: Result
r, s have the same arity
attributes of r and s are compatible

67. Example:FindthesetofallcoursestaughtinboththeFall2017andtheSpring2018semesters.

course_id(
semester=“Fall”Λyear=2017 (section)) 

course_id(
semester=“Spring”Λyear=2018 (section))
Result
Set Difference Operation
Theset-differenceoperationallowsustofindtuplesthatareinonerelationbutarenotin
another.
Notationr–s
Setdifferencesmustbetakenbetweencompatiblerelations.
randsmusthavethesamearity
attributedomainsofrandsmustbecompatible

68. •Example: to find all courses taught in the Fall 2017 semester, but not in the Spring 2018 semester

course_id(
semester=“Fall”Λyear=2017 (section)) −

course_id(
semester=“Spring”Λyear=2018 (section))
The Assignment Operation
Itisconvenientattimestowritearelational-algebraexpressionbyassigningpartsofitto
temporaryrelationvariables.
Theassignmentoperationisdenotedbyandworkslikeassignmentinaprogramming
language.
Example:Findallinstructorinthe“Physics”andMusicdepartment.
Physicsdept_name=“Physics”(instructor)
Musicdept_name=“Music”(instructor)
PhysicsMusic

69. With the assignment operation, a query can be written as a sequential program consisting of a
series of assignments followed by an expression whose value is displayed as the result of the
query.
The Rename Operation
The results of relational-algebra expressions do not have a name that we can use to refer to
them. The rename operator, ,is provided for that purpose
The expression: returns the result of expression Eunder the name x

x(E)
Another form of the rename operation:

x(A1,A2, .. An) (E)

70. Equivalent Queries
Thereismorethanonewaytowriteaqueryinrelationalalgebra.
Example:FindinformationaboutcoursestaughtbyinstructorsinthePhysicsdepartmentwith
salarygreaterthan90,000
Query1

dept_name=“Physics”
salary>90,000(instructor)
Query2

dept_name=“Physics”(
salary>90.000(instructor))
Thetwoqueriesarenotidentical;theyare,however,equivalent--theygivethesameresulton
anydatabase.

71. Equivalent Queries
Thereismorethanonewaytowriteaqueryinrelationalalgebra.
Example:FindinformationaboutcoursestaughtbyinstructorsinthePhysicsdepartment
Query1

dept_name=“Physics”(instructor⋈
instructor.ID=teaches.IDteaches)
Query2
(
dept_name=“Physics”(instructor))⋈
instructor.ID=teaches.IDteaches
Thetwoqueriesarenotidentical;theyare,however,equivalent--theygivethesameresulton
anydatabase.