DBMS Basics

Database Management Systems Basics

1. SESSION NO -I
DBMS
By Dr. Dhobale J V
Assistant Professor
IBS, IFHE, Hyderabad.
IBS Hyderabad 1

2. Objectives
DatabaseSystemApplications.
PurposeofDatabasesystems.
ViewofData.
DatabaseLanguage.
RelationalDatabase.
DatabaseDesign.
DatabaseArchitecture.
DatabaseUsersandDBA.
2IBS Hyderabad

3. DBMS
3IBS Hyderabad

4. DBMS
DBMS(DatabaseManagementSystems)?
Isacollectionofinterrelateddataandasetof
programstoaccessthosedata.
Thecollectionofdata,usuallyreferredtoas
database,containsinformationrelevanttoan
enterprise.
4IBS Hyderabad

5. DBMS
Databasesystemsaredesignedtomanage
largebodiesofinformation.
Managementofdatainvolvesbothdefining
structuresforstorageofinformationand
providingmechanismforthemanipulationof
information.
Ensuressafetyofinformation.
5IBS Hyderabad

6. Data and Information
6IBS Hyderabad

7. Data and Information
7IBS Hyderabad

8. DBMS features
1.Itiswellorganized.
2.Itisrelated.
3.Itisaccessibleindifferentorderswithout
greatdifficulty.
4.Itisstoredonlyonce.
8IBS Hyderabad

9. DBMS Applications
Databasesarewidelyused–
1.EnterpriseInformation–
Sales–Customer,Product&Purchaseinformation.
Accounting–Payments,Receipts,account
balances,assetsandotheraccountinginformation.
HR–Employeesinformation,salaries,payrolltaxes
&benefits.
Manufacturing–ManagementofSCM,inventories
ofitemsinwarehouses&storesandordereditems.
Onlineretailers–Onlineordertrackinggeneration,
maintenanceofonlineproductevaluation.
9IBS Hyderabad

10. DBMS Applications
2.BankingandFinance–
Banking–Customerinformation,account,loans,
bankingtransactions.
Creditcardtransactions–Purchasesoncredit
cardsandgenerationofmonthlystatements.
Finance–informationaboutholdings,sales,and
purchaseoffinancialinstrumentssuchasstocks
andbonds.
10IBS Hyderabad

11. DBMS Applications
3.Universities–Studentinformation,course
registrationsandgradesalongwithenterprise
information.
4.Airlines–Reservationsandschedule
information.
FirstlyuseDBMS capabilitiesina
geographicallydistributedmanner.
11IBS Hyderabad

12. DBMS Applications
5.Telecommunication–recordsofcallsmade,
Billinginformation,recordofprepaidcalling
andbalances,communicationnetwork
information.
12IBS Hyderabad

13. Purpose of DBMS
13IBS Hyderabad

14. Purpose of DBMS
DatabaseApproach–
Manyprogramsanduserscansharedatain
database.
Securesdatasothatauthorizeduserscan
accesscertaindata.
14IBS Hyderabad

15. Purpose of DBMS
15IBS Hyderabad

16. Purpose of DBMS
16IBS Hyderabad

17. Purpose of DBMS
17IBS Hyderabad

18. Views of Data -Abstraction
Weknowthatthesamething,ifviewedfrom
differentanglesproducesdifferencesights.
Likewise,thedatabasethatwehavecreated
alreadycanhavedifferentaspectstorevealif
seenfromdifferentlevelsofabstraction.
Abstractionmeanstheamountofdetailyou
wanttohide.
18IBS Hyderabad

19. Views of Data –Abstraction Example
Acomputerrevealstheminimumofitsinternal
details,whenseenfromoutside.
Wedonotknowwhatpartsitisbuiltwith.This
isthehighestlevelofabstraction.
Ifweopenthecomputercaseandlookinside
attheharddisc,motherboard,CDdrive,CPU
andRAM,weareinmiddlelevelof
abstraction.
19IBS Hyderabad

20. Views of Data –Abstraction Example
Ifwemoveontoopentheharddiscand
examineitstracks,sectorsandread-write
heads,weareatthelowestlevelof
abstraction.
Inthesamemanner,thedatabasecanalsobe
viewedfromdifferentlevelsofabstractionto
revealdifferentlevelsofdetails.whereno
detailsareinvisible.
20IBS Hyderabad

21. Views of Data –Abstraction Levels
21IBS Hyderabad

22. Views of Data –Abstraction Levels
Thewordschemameansarrangement(design)–how
wewanttoarrangethingsthatwehavetostore.
Thediagramaboveshowsthethreedifferentschemas
usedinDBMS,seenfromdifferentlevelsof
abstraction.
Thecollectionofinformationstoredinthedatabaseat
aparticularmomentiscalledaninstance.
22IBS Hyderabad

23. Data Models
DataModelsarenothingbutRulesand
Standardsthatdefinehowdatabaseorganizes
data.
Differentdatamodelsare–
1.RelationalModel
2.Entity-RelationshipModel
3.Object-BasedDataModel
4.SemistructuredDataModel
23IBS Hyderabad

24. Data Models
1.RelationalModel–Collectionoftablesto
representbothdataandtherelationship
amongthosedata.
IsRecordbasedmodel.
24IBS Hyderabad

25. Data Models
2.Entity-RelationshipModel:Collectionofbasic
objectscalledentities.
Itisbasedonentitiesanditsrelationship.
25IBS Hyderabad

26. Data Models
3.Object-BasedDataModel:Objectoriented
programminghasbecomethedominant
softwaredevelopmentmethodology.
Itleadstodevelopanobjectorienteddata
modelswhichisextendingtheE-Rmodel
withnotionsofencapsulation,methods
(functions),andobjectentity.
26IBS Hyderabad

27. Data Models
4.SemistructuredDataModel:Thisdata
modelpermitsthespecificationofdatawhere
individualdataitemsofthesametypemay
havedifferentsetofattributes.
TheExtensibleMarkupLanguage(XML)is
widelyusedtorepresentsemistructured
data.
27IBS Hyderabad

28. Database Languages
ADBMSprovidesadata-definition
languagetospecifythedatabase
schema andadata-manipulation
languagetoexpressdatabasequeries
andupdates.
Data-definitionanddata-manipulation
languagesarepartsofsingledatabase
language.
Ex.-SQL-StructuredQueryLanguage
28IBS Hyderabad

29. Database Languages
Data-ManipulationLanguages-which
enablesuserstoaccessormanipulate
dataasorganizedbytheappropriate
datamodel.
Accesstypes(operations)–Retrieval,
Insertion,Deletion&Modification.
TherearetwotypesofDMLs.
1.ProceduralDMLs–What&How(data).
2.Declarative(Nonprocedural)DMLs–
What.
29IBS Hyderabad

30. Database Languages
Data-DefinitionLanguage: Data
DefinitionLanguage(DDL)statements
areusedtoclassifythedatabase
structureorschema.
Itisatypeoflanguagethatallowsthe
DBAorusertodepictandnamethose
entities,attributes,andrelationshipsthat
arerequiredfortheapplicationalong
withanyassociatedintegrityandsecurity
constraints.
30IBS Hyderabad

31. Database Languages
Data-DefinitionLanguage:Herearethe
listsoftasksthatcomeunderDDL-
CREATE–usedtocreateobjectsinthedatabase.
ALTER–usedtoaltersthestructureofthedatabase.
DROP–usedtodeleteobjectsfromthedatabase.
TRUNCATE–usedtoremoveallrecordsfromatable,
includingallspacesallocatedfortherecordsare
removed.
COMMENT –usedtoaddcommentstothedata
dictionary.
RENAME–usedtorenameanobject.
31IBS Hyderabad

32. Relational Database
Therelationalmodelisthetheoreticalbasisof
relationaldatabaseswhichisatechniqueor
wayofstructuringdatausingrelations,which
aregrid-likemathematicalstructures
consistingofcolumnsandrows.
CoddproposedtherelationalmodelforIBM.
32IBS Hyderabad

33. Relational Database
Intherelationalmodel,alldataislogically
structuredwithinrelationsi.e.tables.
Eachrelationhasanameandisformedfrom
namedattributesorcolumnsofdata.
Eachtupleorrowholdsonevalueper
attribute.
Thegreateststrengthoftherelationalmodelis
thissimplelogicalstructurethatitforms. 33IBS Hyderabad

34. Relational Database
ObjectivesofRelationalModel:
1.Toallowahighdegreeofdataindependence.
2.Toprovideconsiderablegroundsfordealingwith
datasemantics,reliability,andredundancy
problems.
3.Toallowtheexpansionofset-orienteddata
manipulationlanguages.
34IBS Hyderabad

35. Relational Database
Tables:
Eachtablehasmultiplecolumnsandeach
columnhasauniquename.
35IBS Hyderabad

36. Relational Database
Tables:RelationalModelTerms-
Relation:Arelationisatablewithcolumnsand
rows.
Attribute:Anattributeisanamedcolumnofa
relation.
Domain:Adomainisthesetofallowablevaluesfor
oneormoreattributes.
Tuple:Atupleisarowofarelation.
36IBS Hyderabad

37. Relational Database
Tables:Ex.
37IBS Hyderabad

38. Relational Database
Data-ManipulationLanguage–TheSQLquery
languageisnonprocedural.
Aquerytakesasinputseveraltables/tableand
alwaysreturnasingletable.
SQLstatementforfindingthenamesofall
instructorsintheHistorydepartment.
38IBS Hyderabad

39. Relational Database
Data-ManipulationLanguage:-
Thequeryspecifiesthatthoserowsfromthe
tableinstructorwheredept_nameisHistory
mustberetrievedandnameattributeofthese
rowsmustbedisplayed.
39IBS Hyderabad

40. Relational Database
DMLs:.
40IBS Hyderabad

41. Relational Database
Data-ManipulationLanguage:-Querymay
involveinformationfrommorethanonetable.
ThisqueryfindstheinstructorsIDandofall
theinstructorsassociatedwithadepartment
namedepartmentwithbudgetgreaterthan
$95000. 41IBS Hyderabad

42. Relational Database
DMLs:.
42IBS Hyderabad

43. Relational Database
Data-DefinitionLanguage:SQLprovidesarich
DDLthatallowsonetodefinetables,integrity
constraints,assertions,etc.
FollowingSQLDDLstatementdefinesthe
departmenttable:
createtabledepartment
(dept_namechar(20),
buildingchar(15),
budget numeric(12,2));
43IBS Hyderabad

44. Relational Database
Database Access fromApplication
Programs:
SQLisaquerylanguagewhichdealswith
creationandmanipulationofdatabaseand
datarespectively.
SQLdoesnotsupportactionslikeinputfrom
user,outputtodisplay&network
communication.
Suchactionsandcomputationsmustbe
writtenusinggeneralpurposelanguageslike
C,C++,Javaetc.
44IBS Hyderabad

45. Relational Database
Database Access fromApplication
Programs:
Applicationprogramsareprogramsthatare
usedtointeractwiththedatabaseforspecific
application,likefillingoftheforms,doing
calculations,generationofreports,etc.
45IBS Hyderabad

46. Database Design
DBMSaredesignedtomanagelargebodies
ofinformation.
Theselargebodiesofinformationdonotexist
inisolation.
HereDBMSplaysonlysupportingroleby
providingrequiredinformationorservice.
Databasedesignmainlyinvolvesthedesignof
thedatabaseschema.
46IBS Hyderabad

47. Database Design
DesignProcess:
1.DatabaseConceptualFramework–data
requirements,databasestructure.
2.DataNeedsoftheprospectivedatabaseusers–
outcomeofthethisphaseisspecificationofuser
requirements.
3.Thedesignerneedstochooseadatamodeland
translatesuserrequirementsintoconceptual
schemaofthedatabase.
4.Thedesignerreviewstheschematoconfirmdata
requirementsanditsconflicts;focusisondata&
itsrelationship.
47IBS Hyderabad

48. Database Design
DesignProcess:
5.Afullydevelopedconceptualschemaindicates
thefunctionalrequirementsoftheenterprise;at
thisstageofconceptualdesign,thedesignercan
reviewtheschematoensureitmeetsfunctional
requirements.
6.Designtoimplementationcanbetakenintotwo
differentways–Logicaldesignphase&Physical
designphase.
48IBS Hyderabad

49. Database Design
DesignProcess;Example-
DatabasedesignforaUniversity:Pointstobe
considered.
TheUniversityisorganizedintodepartments
(dept_name,bulding,budget).
Eachdepartmenthasalistofcoursesitoffers
(course_id,dept_name,credit,andprerequisites).
InstructorsareidentifiedbytheiruniqueID(name,
dept_name,salary).
StudentsareidentifiedbytheiruniqueID(name,
dept_name,total_cred).
49IBS Hyderabad

50. Database Design
DesignProcess;Example-
DatabasedesignforaUniversity:Pointstobe
considered.
TheUniversitymaintainsalistofclassrooms
(building,room_number&room_capacity).
Theuniversitymaintainsalistofclasses
(sections)taught(course_id,sec_id,year,
semester).
Thedepartmenthasalistofteachingassignments
specifying,foreachinstructor,thesectionthe
instructoristeaching.
TheUniversityhasalistofallstudentcourse
registration.
50IBS Hyderabad

51. Database Design
DesignProcess-
TheEntity-RelationshipModel–Thismodel
usesacollectionofbasicobjects,called
entities,andrelationshipsamongthese
objects.
Ex.Person,bankaccount;
Entitiesaredescribedinadatabasebyaset
ofattributes.
Ex.ID,name,andsalarymaydescribe
instructorfromuniversity.
51IBS Hyderabad

52. Database Design
DesignProcess-
TheEntity-RelationshipModel–
TheexternalattributeIDisusedtoidentifyan
instructoruniquely.
Arelationshipisanassociationamongseveral
entities.
Ex.Amemberrelationshipassociatesan
instructorwithherdepartment.
Entityset–sametypesofentities.
Relationshipset–sametypeofrelations.
52IBS Hyderabad

53. Database Design
DesignProcess-
TheEntity-RelationshipModel–
Theoveralllogicalstructure(schema)ofa
databasecanbeexpressedgraphicallybyan
entity-relationship(E-R)diagram.
Severalwaystodrawthesediagramsbutmost
popularisUnifiedModelingLanguage(UML).
53IBS Hyderabad

54. Database Design
DesignProcess-
TheEntity-RelationshipModel–
54IBS Hyderabad

55. Database Design
DesignProcess-
TheEntity-RelationshipModel–
Entitysetsarerepresentedbyarectangular
boxwiththeentitysetnameintheheaderand
theattributeslistedbelowit.
Relationshipsetsarerepresentedbya
diamondconnectingapairofrelatedentity
sets.Thenameoftherelationshipisplaced
insidethediamond.
55IBS Hyderabad

56. Database Design
DesignProcess-
TheEntity-RelationshipModel–
Inadditiontoentitiesandrelationships,theE-
Rmodelrepresentscertainconstraintsto
whichthecontentsofdatabasemustconform.
MappingCardinalities,whichexpressesthe
numberofentitiestowhichanotherentitycan
beassociatedviaarelationshipset.
56IBS Hyderabad

57. Database Design
DesignProcess-
Normalization:Anothercommonlyused
methodofdesigningRelationaldatabaseis.
Itcreatesschematostoreinformationwithout
unnecessaryredundancyandalsoallowsusto
retrieveinformationeasily.
Theapproachistodesignschemasthatarein
anappropriatenormalforms.
57IBS Hyderabad

58. Database Design
DesignProcess-
Normalization:Todeterminewhethera
relationschemaisinoneofthedesirable
normalforms,weneedadditionalinformation
abouttherealworld.
Mostcommonlyusedapproachistouse
functionaldependencies.
58IBS Hyderabad

59. Database Design
DesignProcess-
Normalization:Example
baddatabasedesignconsistsof–
RepetitionofInformation.
Inabilitytorepresentcertaininformation.
Weshalldiscusstheseproblemswiththehelp
ofamodifieddatabasedesignforour
universityexample.
59IBS Hyderabad

60. Database Design
DesignProcess-
Normalization:Example.
60IBS Hyderabad

61. Database Design
DesignProcess-
Normalization:Example.
Noticethepresenttablecontainsrepetitionof
informationwithdepartments(building,
budget)–redundancyproblem.
Thisredundancyleadstoinconsistencyin
managingdatabase.
Anotherproblemis“inabilitytorepresent
certaininformation”.(addition/deletionof
departmentwithoutinstructorisnotpossible).
61IBS Hyderabad

62. Data Storage and Querying
Adatabasesystemispartitionedintomodules
thatdealwitheachoftheresponsibilitiesofthe
overallsystem.
FunctionalModules–
1.StorageManager.
2.QueryProcessorComponents.
62IBS Hyderabad

63. Data Storage and Querying
StorageManagerisimportantbecause
databasetypicallyrequirealargeamountof
storagespace.
63IBS Hyderabad

64. Data Storage and Querying
Mainmemoryofcomputercannotstorethis
muchinformation,theinformationisstoredon
disks.
Dataaremovedbetweendiskandmain
memoryasneeded.
Movementofdatafromdisktomainmemory
isslowrelativetospeedofCPU,therefore
databasesystemstructurethedatasoasto
minimizetheneedtomovedatabetweendisk
andmainmemory.
64IBS Hyderabad

65. Data Storage and Querying
Thequeryprocessorsimplifiesdatabase
accessfacility.
Itworksatviewlevelwithoutunderstanding
thephysicallevelimplementationdetails.
Databasesystemtranslatesandupdatesthe
querieswritteninnonprocedurallanguage,at
logicallevelintoanefficientsequenceof
operationsatphysicallevelwiththehelpof
queryprocessor.
65IBS Hyderabad

66. Data Storage and Querying
StorageManager:Itprovidesinterface
betweenthelow-leveldatastoredindatabase
andtheapplicationprogramandqueries
submittedtothesystem.
Isresponsiblefortheinteractionwiththefile
managersupportedbyoperatingsystem.
Itisalsoresponsibleforstoring,retrieving,and
updatingdatainthedatabase.
66IBS Hyderabad

67. Data Storage and Querying
StorageManagerComponents:
1.AuthorizationandintegrityManager.
2.TransactionManager.
3.FileManager.
4.BufferManager.
67IBS Hyderabad

68. Data Storage and Querying
StorageManagerdatastructures:
1.DataFiles.
2.DataDictionary.
3.Indices.
68IBS Hyderabad

69. Data Storage and Querying
TheQueryProcessorComponents:
1.DDLinterpreter.
2.DMLCompiler.
3.Queryevaluationengine.
69IBS Hyderabad

70. Transaction Management
Atransactionisacollectionofoperationsthat
performsasinglelogicalfunctionina
database.
ThetransactionManagementconsistsofthe
Concurrency-controlmanager andthe
recoverymanager.
Eachtransactionisaunitofbothatomicityand
consistency.
Itisprogrammer’sresponsibilitytodefine
properlythevarioustransactions,sothateach
preservestheconsistencyofthedatabase.
70IBS Hyderabad

71. Transaction Management
Recoverymanagementensuresatomicityand
durability.
Thedatabasesystemmustperformfailure
recovery,thatis,detectsystemfailuresand
restorethedatabasetothestatethatexisted
priortotheoccurrenceofthefailure.
Itistheresponsibilityoftheconcurrency-
controlmanagementtocontroltheinteraction
amongtheconcurrenttransactions,toensure
theconsistencyofthedatabase.
71IBS Hyderabad

72. Database Architecture
72IBS Hyderabad

73. Database Architecture
73IBS Hyderabad

74. Database Architecture
Thearchitectureofadatabasesystemis
greatlyinfluencedbytheunderlyingcomputer
systemonwhichthedatabasesystemruns.
Itcanbecentralizedorclient-servertypes.
Itcanalsobedesignedtoexploitparallel
computerarchitectures.
Distributeddatabase span multiple
geographicallyseparatedmachines.
ODBC&JDBCisusedtoprovideinterface
betweenclientandsever.
74IBS Hyderabad

75. Database Users and Administrators
Aprimarygoalofadatabasesystemisto
retrieveinformationfromandstorenew
informationintothedatabase.
Peoplewhoworkwithadatabasecanbe
categorizedasdatabaseusersordatabase
administrators.
75IBS Hyderabad

76. Database Users and Administrators
FourtypesofdatabaseUsers–basedonthe
waytheyexpecttointeractwithsystem.
1.NaïveUser:Sophisticateduserswho
interactswiththesystembyinvokingoneof
theapplicationprograms.
Ex,-Clerkaddsnewrecordtotheexisting
databaseusingprogramspecificallydesigned
toaddrecord.
AFormbaseduserinterfaceispreferredto
naiveusers.
76IBS Hyderabad

77. Database Users and Administrators
FourtypesofdatabaseUsers–basedonthe
waytheyexpecttointeractwithsystem.
2.ApplicationProgrammers:arecomputer
professionalswhowriteapplicationprograms.
RapidApplicationDevelopment(RAD)tools
speedsupprogrammingcapabilitieswith
minimizationofeffortstodevelopuser
interfaces.
77IBS Hyderabad

78. Database Users and Administrators
FourtypesofdatabaseUsers–basedonthe
waytheyexpecttointeractwithsystem.
3.SophisticatedUsers:interactswiththe
systemwithoutwritingprograms.
Instead,theyformtheirrequestseitherusinga
databasequerylanguageorbyusingtools
suchasdataanalysissoftware.
Analystswhosubmitqueriestoexploredatain
thedatabasefallinthiscategory.
78IBS Hyderabad

79. Database Users and Administrators
FourtypesofdatabaseUsers–basedonthe
waytheyexpecttointeractwithsystem.
4.SpecializedUsers:aresophisticatedusers
whowritespecializeddatabaseapplications
thatdonotfitintothetraditionaldata-
processingframework.
Ex.-ComputerAidedDesign,Knowledge
basedexpertsystems.
79IBS Hyderabad

80. Database Users and Administrators
DatabaseAdministrator:Oneofthemain
reasonforusingDBMSsistohavecentral
controlofboththedataandtheprogramsthat
accessthosedata.
Apersonwhohassuchcentralcontrolover
thesystemiscalledadatabaseAdministrator
(DBA).
80IBS Hyderabad

81. Database Users and Administrators
DatabaseAdministrator:Functionsofa
DBA-
1.Schemadefinition–TheDBAcreatesthe
originaldatabaseschemabyexecutingaset
ofdatadefinitionstatementsintheDDL.
2.Storagestructureandaccess-method
definition–
3.Schema and physical-orginzation
modification-
81IBS Hyderabad

82. Database Users and Administrators
DatabaseAdministrator:Functionsofa
DBA-
4.Grantingofauthorizationfordataaccess-
5.Routingmaintenance–like,backup,free
spacemanagement,monitoringjobsand
performanceofthedatabase.
82IBS Hyderabad

83. Reviews
DatabaseSystemApplications.
PurposeofDatabasesystems.
ViewofData.
DatabaseLanguage.
RelationalDatabase.
DatabaseDesign.
DatabaseArchitecture.
DatabaseUsersandDBA.
83IBS Hyderabad

84. Thank You!
84 IBS Hyderabad