Database management notes contains information about all components and types

Database Management System

2. Agenda
▶DATA
▶TYPESOFDATA
▶DATAANDINFORMATION
▶DATABASEMAANGEMENT SYSTEM
▶WHYWEUSEDATABASE?

4. WHATISDATA
Dataisacollectionofinformationgathered
byobservations,measurements,researchor
analysis.
Theymayconsistoffacts,numbers,names,
figuresorevendescriptionofthings.
Dataisorganizedintheformofgraphs,
chartsortables.Thereexistdatascientistwho
doesdataminingandwiththehelpofthat
dataanalyzeourworld.

5. -:https://w.splashlearn.com/

6. DataAndInformation
Datareferstorawfacts,details,ordistinctpieces
ofinformationthatarecollected,stored,and
processed.Itcanbeinvariousforms,including
numbers,text,images,oranyotherformat.
Example-:Iftrainiscomingtostationat2:30PM
inthiscasethetime2:30PMisthedata.
Informationreferstoprocessedandorganizeddatathathascontext,
relevance,andpurpose.Itresultsfromtheinterpretationandanalysis
ofrawfacts,makingitmeaningfulandvaluablefordecision-making.
Example-:iftrainiscomingtostationthenthevisualdisplayonthe
stationboardshowingthetrain'sname,platform,andarrivaltimeis
theinformation.
Imagesource-:https://www.flaticon.com/free-icon/management_3715093

7. DATAV/SINFORMATION

8. WHATISDATABASEMANAGEMENT
SYSTEM?

9. ComponentsofDBMS
HARDWARE SOFTWARE DATA PROCEDURES DATAACCESS
LANGUAGE

10. Componentsof
DBMScont..
Hardware:InDBMS,informationhardwareisthemost
importantvisiblepart.Theequipmentwhichisusedforthe
visibilityof thedataistheprinter,computer,scanner,etc.
Software:SoftwareisthemaincomponentoftheDBMS.
Softwareisdefinedasthecollectionofprogramsthatare
usedtoinstructthecomputer aboutitswork.Thesoftware
consistsofasetofprocedures,programs,androutines
associatedwiththecomputersystem'soperationand
performance.
Data:Thetermdatameansthecollectionofanyrawfact
storedinthedatabase.Herethedataareanytypeofraw
materialfromwhichmeaningfulinformationisgenerated.
Thedatabasecanstoreanyformofdata,suchas structural
data,non-structuraldata,andlogicaldata.

11. Componentsof
DBMScont..
Procedures:InDBMS,withthehelpofprocedure,
wecanvalidatethedata,controlthe accessand
reducethetrafficbetweentheserverandtheclients.
TheDBMScanofferbetterperformanceto
extensiveorcomplexbusinesslogicwhentheuser
followsalltheprocedurescorrectly.
DatabaseAccess Language:DatabaseAccess
Languageisalanguageusedtowritecommandsto
access,upset,anddeletedatastoredinadatabase.

12. Characteristics
of
DBMS
Security
ACIDproperties
Atomicity Consistency Integrity Durability
Dataindependence

14. Cont…
▶DataIndependence
LogicalIndependence:Changesinthelogicalstructure(schema)do
notaffecttheapplicationprograms.
PhysicalIndependence:Changesinthephysicalstorageand
structuredonotaffectthelogicalstructureorapplications.
▶AtomicityofOperations(Transactions):Here,atomicitymeanseither
theoperationshouldbeperformedornotperformed.i.e.,itshould
completetheoperationon0%or100%.DBMSprovidesatomicityasa
characteristic.Thisisthemostimportantandusefulcharacteristicofthe
DBMS.
▶Integrity:Integritymeansthedatashouldbecorrectandconsistentin
nature.ThedataavailableintheDatabaseshouldbecorrectaswellas
consistent.Ensurestheaccuracy,consistency,andreliabilityofdataby
enforcingconstraints(suchasuniquekeys,foreignkeys,anddatatypes)
onthedata.

15. Characteristics
of
DBMS
Cont..
Security:TheDatabaseshouldbeaccessibletotheusersina
limitedway.DBMShasauthenticationforvarioususersthat
directlyreferstothelimittowhichtheusercanaccessthe
Database.Authenticationmeanstheprocessofloggingoftheuser
onlywiththerightsthathe/shehasbeenauthorizedto.
ACIDProperties:ACIDstandsforAtomicity,
Consistency, Isolation,andDurability.Atomicitymeans
thetransactionshouldeitherbe0%or100%completed,
andconsistencymeansthatthechangeindatashouldbe
reflectedeverywhereinadatabase.
EaseofAccess(TheDBMSQueries):InDBMS,wecan
searchanykindofstoreddatabyapplyingasimplesearch
operationquery.Itissomuch fasterthanmanual
searching.InDBMS,

16. WHYDBMS?

17. Features
of
DBMS
Efficiency
ThroughStructure
EnsuringData
Accuracy
Scalabilityforthe
DigitalAge

18. Features
of
DBMS
Cont..
EnsuringDataAccuracy:Accuracyis
paramount.Databasesenforcedata
integrity,ensuringthattheinformationstored
isaccurateandconsistent.Nomoreworries
aboutconflicting oroutdateddata.
EfficiencyThroughStructure:Databasesbring
structuretoourdatachaos.Thinkofitasa
well-organizedlibrarywhereeachpieceof
informationhasitsplace.Thisstructure
facilitatesquickandefficientretrieval.

19. Features
of
DBMS
Cont…
ThisDBMSalsoprovidethefollowing
propertieswhichmakesitveryimportant.
Itsupportsmulti-
users.
Itusedbyboth
smallandlarge
business.
Noredundancy
andintegrityissue
isfoundhere.
ScalabilityfortheDigitalAge:Inourdigital
age,thevolumeofdataisstaggering.
Databasesprovidescalabilitytheycan
handlevastamountsofinformationwithout
compromisingperformance.Thisscalabilityis
vitalasourdataneedscontinuetogrow.

21. Last we Discuss
▶DATA
▶TYPESOFDATA
▶DATAANDINFORMATION
▶DATABASEMANAGEMENTSYSTEM
▶WHYWEUSE DATABASE?

22. AGENDA
▶DATABASEMANAGEMENT SYSTEM
▶TYPESOFDBMS
▶COMPONENTS OFDATABASE
▶ADVANTAGESOFDBMS
▶DISADVANTAGESOFDBMS

23. WHATISDBMS?
Imagesource-:https://dir.texas.gov/sites/default/files/2022-10/Data%20and%20Information%20Governance%20

25. TYPESOFDBMS
Imagesource-:https://www.slideserve.com/Simplilearn/introduction-to-dbms-database-management-system-what-is-dbms-dbms-explana

26. Hierarchical Model
•HierarchicalModelwasthefirstDBMSmodel.
•Thismodelorganizesthedatainthehierarchicaltreestructure.
•Thehierarchystartsfromtherootwhichhasrootdataandthenit
expandsintheformofatreeaddingchildnodetotheparent
node.
•Thismodeleasilyrepresentssomeofthereal-worldrelationships
likefoodrecipes,sitemapofawebsiteetc.

27. Example

28. Features of a Hierarchical Model
•One-to-manyrelationship:Thedatahereisorganisedinatree-likestructurewhere
theone-to-manyrelationshipisbetweenthedatatypes.Also,therecanbeonlyone
pathfromparenttoanynode.Example:Intheaboveexample,ifwewanttogoto
thenodesneakersweonlyhaveonepathtoreachtherei.ethroughmen'sshoes
node.
•Parent-ChildRelationship:Eachchildnodehasaparentnodebutaparentnode
canhavemorethanonechildnode.Multipleparentsarenotallowed.
•DeletionProblem:Ifaparentnodeisdeletedthenthechildnodeisautomatically
deleted.
•Pointers:Pointersareusedtolinktheparentnodewiththechildnodeandareused
tonavigatebetweenthestoreddata.Example:Intheaboveexamplethe'shoes'
nodepointstothetwoothernodes'womenshoes'nodeand'men'sshoes'node.

29. Advantages of Hierarchical Model
•It is very simple and fast to traverse through a tree-
like structure.
•Any change in the parent node is automatically
reflected in the child node so, the integrity of data
is maintained.

30. Disadvantages of Hierarchical Model
•Complex relationships are not supported.
•As it does not support more than one parent of the child node
so if we have some complex relationship where a child node
needs to have two parent node then that can't be represented
using this model.
•If a parent node is deleted then the child node is
automatically deleted.

32. Features of a Network Model
•AbilitytoMergemoreRelationships:Inthismodel,asthereare
morerelationshipssodataismorerelated.Thismodelhasthe
abilitytomanageone-to-onerelationshipsaswellasmany-to-
manyrelationships.
•Manypaths:Astherearemorerelationshipssotherecanbe
morethanonepathtothesamerecord.Thismakesdataaccess
fastandsimple.
•CircularLinkedList:Theoperationsonthenetworkmodelare
donewiththehelpofthecircularlinkedlist.Thecurrent
positionismaintainedwiththehelpofaprogramandthis
positionnavigatesthroughtherecordsaccordingtothe
relationship.

33. Advantages of Network Model
•Thedatacanbeaccessedfasterascomparedtothe
hierarchicalmodel.Thisisbecausethedataismorerelated
inthenetworkmodelandtherecanbemorethanonepathto
reachaparticularnode.Sothedatacanbeaccessedinmany
ways.
•Asthereisaparent-childrelationshipsodataintegrityis
present.Anychangeinparentrecordisreflectedinthechild
record.

34. Disadvantages of Network Model
•Asmoreandmorerelationshipsneedtobehandledthe
systemmightgetcomplex.
•So,ausermustbehavingdetailedknowledgeofthemodel
toworkwiththemodel.
•Anychangelikeupdation,deletion,insertionisvery
complex.

35. Relational Model
•RelationalModelisthemostwidelyusedmodel.
•Inthismodel,thedataismaintainedintheformofatwo-
dimensionaltable.
•Alltheinformationisstoredintheformofrowand
columns.
•Thebasicstructureofarelationalmodelistables.
•So,thetablesarealsocalledrelationsintherelational
model.

37. Features of Relational Model
•Tuples:Eachrowinthetableiscalledtuple.Arowcontains
alltheinformationaboutanyinstanceoftheobject.Inthe
aboveexample,eachrowhasalltheinformationaboutany
specificindividuallikethefirstrowhasinformationabout
John.
•Attributeorfield:Attributesarethepropertywhichdefines
thetableorrelation.Thevaluesoftheattributeshouldbe
fromthesamedomain.Intheaboveexample,wehave
differentattributesoftheemployeelikeSalary,Mobile_no,
etc.

38. Advantages of Relational Model
•Simple:Thismodelismoresimpleascomparedtothe
networkandhierarchicalmodel.
•Scalable:Thismodelcanbeeasilyscaledaswecanaddas
manyrowsandcolumnswewant.
•StructuralIndependence:Wecanmakechangesindatabase
structurewithoutchangingthewaytoaccessthedata.When
wecanmakechangestothedatabasestructurewithout
affectingthecapabilitytoDBMStoaccessthedatawecan
saythatstructuralindependencehasbeenachieved.

39. Disadvantages of Relational Model
•HardwareOverheads:Forhidingthecomplexitiesand
makingthingseasierfortheuserthismodelrequiresmore
powerfulhardwarecomputersanddatastoragedevices.
•BadDesign:Astherelationalmodelisveryeasytodesign
anduse.Sotheusersdon'tneedtoknowhowthedatais
storedinordertoaccessit.Thiseaseofdesigncanleadto
thedevelopmentofapoordatabasewhichwouldslowdown
ifthedatabasegrows.

40. COMPONENTSOFDBMS
Imagesource-:https://www.tutorialsmate.com/2022/01/components-of-dbms.html

41. ▶Software-:ThesoftwarecomponentinadatabasesystemcomprisestheDatabase
ManagementSystem(DBMS),facilitatingdatabasecreation,management,and
datainteractionthroughfeatureslikequerylanguage,security,andtransaction
management.Itincludesadatabaseengineforstorage,queryexecution,and
additionalutilitiesformaintenance,formingacohesivesystemfororganizeddata
handling.
▶Hardware-:Thehardwarecomponentinadatabasesystemconsistsofthephysical
devicessupportingdatastorageandprocessing,suchasservers,storagedevices,
andnetworkinfrastructure,ensuringefficientandreliabledatabaseoperations.It
includesthetangibleequipmentnecessaryfordatastorage,retrieval,and
communicationwithinthedatabaseenvironment.
▶Data:Thefundamentalinformationstoredinthedatabase,organizedintotables
withrowsandcolumns, representingentitiesandtheirattributes.
▶User:Anindividualorsysteminteractingwiththedatabasetoperformoperations
likedataretrieval,insertion,ormodification.

42. ▶Procedure:Apredefinedsetofinstructionsorroutinesfollowedbytheuser or
systemtoaccomplishspecifictaskswithinthedatabase,ensuringconsistencyand
efficiency.
▶DatabaseaccessLanguage-:TheprimarydatabaseaccesslanguageinDatabase
ManagementSystems(DBMS)isSQL(StructuredQueryLanguage).SQLisa
standardizedlanguageusedfordefining,manipulating,andqueryingrelational
databases,providingauniforminterfaceforinteractingwithvariousdatabase
systems.

43. ADVANTAGESOFDBMS
Imagesource-:https://www.slideserve.com/Simplilearn/introduction-to-dbms-database-management-system-what-is-dbms-dbms-explana

44. DISADVANTAGESOFDBMS
Imagesource-:https://www.slideserve.com/Simplilearn/introduction-to-dbms-database-management-system-what-is-dbms-dbms-explana

46. ImageSource-:https://www.behance.net/usemynotes

47. Naiveusers
▶InDatabaseManagementSystems(DBMS),naiveusersaretypicallyend-userswho
havelimitedtechnicalexpertiseorknowledgeoftheunderlyingdatabasestructure.
▶Unsophisticatedusers.
▶InvoketheapplicationProgram.
▶Theseusersinteractwiththedatabaseusingsimple,user-friendlyinterfacesprovidedby
applications,without needingtounderstandthe
complexitiesofthedatabaseschema
orquerylanguages.

48. Applicationprogrammers
▶ApplicationprogrammersinDatabaseManagementSystems(DBMS)areindividuals
responsiblefordesigningandimplementingsoftwareapplicationsthatinteractwith
databases.
▶TheyuseprogramminglanguagesandAPIs(ApplicationProgrammingInterfaces)to
developapplicationsthatperformtaskssuchasdatainput,retrieval,andmanipulation.
Theseprogrammerswritecodetoensureseamlesscommunicationbetweentheapplication
andtheunderlyingdatabase.
▶Computerprofessionals.
▶Developuserinterface.
▶ApplicationDevelopmenttools.

49. Sophisticatedusers
▶SophisticatedusersinDatabaseManagementSystems(DBMS)areindividuals
withadvancedknowledgeandskillsindatabaseoperations.
▶Unlikenaiveusers,theypossessadeeperunderstandingofdatabasestructures,
querylanguages,andsystemfunctionality.Sophisticatedusersofteninclude
databaseadministrators,analysts,orpoweruserswhocancreatecomplexqueries,
designdatabasestructures,optimizeperformance,andmanagesecurityaspects
withintheDBMSenvironment.
▶Interactwiththesystem.
▶DataanalysisSoftware.
▶Businessanalyst.

50. Specializedusers
▶SpecializedusersinDatabaseManagementSystems(DBMS)refertoindividuals
withexpertiseinspecificdomainsorapplicationsthatinvolveintricateuseofthe
database.
▶Theseusersoftenhavespecializedknowledgeinareassuchasdataanalysis,
businessintelligence,orscientificresearch.
▶TheyutilizeadvancedfeaturesoftheDBMS,specializedtools,orprogramming
interfacestoperformtaskstailoredtotheirspecificneeds,contributingtodomain-
specificsolutionsandinsights.
▶Computeraideddesignsystem.
▶Multimediadata.

51. CharacteristicsoftheDatabase
▶DataIntegrity:Ensuresaccuracyandreliabilityofdatabyenforcingconstraints,
relationships,andvalidationrules.
▶DataSecurity:Involves measurestoprotectdatafromunauthorizedaccess,
modification,ordisclosure,oftenthroughuserauthentication,accesscontrols,and
encryption.
▶DataIndependence:Allowschangesinthedatabasestructurewithoutaffectingthe
applicationprogramsthatusethedata,achievedthroughconceptslikelogicaland
physicaldataindependence.
▶DataConsistency:Guaranteesuniformityandreliabilityofdatabyenforcing
predefinedrulesandconstraints,preventingcontradictoryorinvalidinformation.

52. ▶ConcurrencyControl:Managessimultaneousaccesstothedatabasebymultiple
usersortransactionstoavoidconflictsandensureconsistency.
▶DataPersistence:Ensuresthatdataisstoredandmaintainedovertime,surviving
systemfailuresorshutdowns.
▶DataRedundancyControl:Minimizesduplicationofdatatoavoidinconsistencies
andsavestoragespace.
▶QueryLanguageSupport:Providesastandardizedlanguage(e.g.,SQL)forusers
tointeractwiththedatabase,makingdataretrievalandmanipulationmore
accessible.

53. ▶ACIDProperties(Atomicity,Consistency,Isolation,Durability):Guarantees
reliableprocessingofdatabasetransactions,ensuringthattheyarecompleted
successfullyandthedatabaseremainsinaconsistentstate.
▶Scalability:Enablesthedatabasetohandlegrowingamountsofdataorincreased
userloadsbyefficientlyscalingresources.
▶PerformanceOptimization:Involvestechniquessuchasindexing,query
optimization,andcachingtoenhancethespeedandefficiencyofdataretrievaland
processing.

54. DatabaseSystemsConcepts
And
Architecture

55. DatabaseSystemsConcepts
▶CentralisedDatabaseSystem
▶DistributedDatabaseSystem
▶PersonalDatabaseSystem
▶CloudDatabaseSystem
▶CommercialDatabaseSystem
▶RelationalDatabaseSystem

56. CentralisedDatabaseSystem
▶SingleDatabaseServer:Alldataisstoredonasinglecentralserver,and
thisserverisresponsibleformanaging,storing,andprocessingalldatabase
transactions.
▶DataAccessibility:Usersandapplicationsaccessthedatabasethroughthe
centralserver,whichactsasthesolepointofentryfordataretrieval,
updates,andotheroperations.
▶DataConsistency:Centralizationhelpsmaintaindataconsistencyasthere
isasinglecopyofthedata.Changesaremadedirectlytothiscentral
repository,reducingtheriskofinconsistenciesthatmightarisefrom
distributeddatastorage.
▶SimplifiedManagement:Databaseadministrationandmanagementtasks
arecentralized,makingiteasiertoimplementsecuritymeasures,backup
procedures,andothermaintenanceactivities.Thissimplicitycanleadto
morestraightforwardsystemmanagement.
▶CommunicationOverhead:Whilecentralizedsystemsoffersimplicity,they
canalsointroducepotentialbottlenecks.Allcommunicationbetweenusers
andthedatabase,aswellasbetweendifferentcomponentsofthesystem,
mustpassthroughthecentralserver,whichcanleadtoincreased
communicationoverheadasthesystemscales.

57. DistributedDatabaseSystem
▶GeographicalDistribution:Dataisstoredin
multiplelocationsornodesthatmaybe
geographicallydispersed.Theselocations
areconnectedthroughanetwork,enabling
userstoaccessdatafromdifferent
geographicallocations.
▶ImprovedScalabilityandPerformance:
Distributionallowsforhorizontal
scalability,enablingthesystemtohandle
largervolumesofdataandincreaseduser
load.
▶DataAutonomy:Eachnodeinthe
distributedsystemcanhaveitsowndegree
ofautonomy,allowingforlocalcontrolover
specificdata

58. PersonalDatabaseSystem
▶ScopeandPurpose:Intendedforpersonaluse,thesedatabasescatertoan
individual'sspecificneeds,suchasmanagingcontacts,trackingpersonalfinances,
maintainingabookcollection,ororganizingtasks.
▶User-FriendlyInterface:Personaldatabasesystemsoftenfeatureuser-friendly
interfacesthatmakeiteasyfornon-technicaluserstoinput,retrieve,andmanage
datawithouttheneedforextensivedatabasemanagementskills.
▶Flexibility:Personaldatabasesareoftenhighlyflexible,allowinguserstodefine
andcustomizedatafieldsbasedontheiruniquerequirements.
▶SoftwareTools:Personaldatabasesystemscanbeimplementedusingdedicated
softwaretoolsdesignedforindividualuse.Thesetoolsmayincludepersonal
informationmanagement(PIM)software.

59. CloudDatabaseSystem
▶Cloud-BasedInfrastructure:Thedatabasesystemishostedoncloudinfrastructure,
suchasAmazonWebServices(AWS),MicrosoftAzure,orGoogleCloud
Platform.Thisallowsforon-demandprovisioning,scalability,andaccessibility
overtheinternet.
▶Scalability:Clouddatabasesofferhorizontalscalability,allowinguserstoeasily
scaletheirdatabaseresourcesupordownbasedonchangingdemands.This
flexibilityisparticularlyusefulforapplicationswithvariableworkloads.
▶AccessibilityandGeographicDistribution:Clouddatabasesenableremoteaccess
todatafromanywherewithaninternetconnection.
▶ManagedServices:Clouddatabaseprovidersoftenoffermanagedservicesthat
handleroutinetaskssuchasbackups,security,andsoftwareupdates.

60. CommercialDatabaseSystem
▶VendorDevelopmentandSupport:Commercialdatabasesystemsaredeveloped
andmaintainedbycommercialvendorsorcompanies.
▶Feature-RichSolutions:Commercialdatabasesoftenofferacomprehensivesetof
features,includingadvancedqueryoptimization,transactionmanagement,security
mechanisms,andscalabilityoptions.
▶EnterpriseIntegration:Thesedatabasesaredesignedtoseamlesslyintegratewith
otherenterprisesoftwaresolutions,facilitatingthecreationofcomprehensive
businessapplications.
▶DocumentationandTraining:Commercialdatabasesystemscomewith
comprehensivedocumentation,includingusermanuals,technicalguides,and
onlineresources.

61. RelationalDatabaseSystem
▶TabularDataStructure:Arelationaldatabasesystemorganizesdatainto
tableswithrowsandcolumns,creatingatabularstructure.Eachtable
representsanentity,andrelationshipsbetweenentitiesareestablishedthrough
keys,providingalogicalandeasy-to-understandmodel.
▶StructuredQueryLanguage(SQL):Relationaldatabasesystemsuse
SQL(StructuredQueryLanguage)asthestandardlanguagefor
definingandmanipulatingdata.SQLallowsuserstoperformoperations
suchasquerying,inserting,updating,anddeletingdata,makingita
powerfulandstandardizedinterface.
▶ACIDProperties:RelationaldatabasesadheretotheACIDproperties
(Atomicity,Consistency,Isolation,Durability)toensurethereliability
andintegrityoftransactions.Thesepropertiesguaranteethatdatabase
transactionsareprocessedreliably,maintainingdataconsistencyandpreventing
datacorruption.

62. ImageSource-:https://usemynotes.com/what-is-the-dbms-architecture

63. ▶1-Tierarchitecture-:Ina1-tierarchitecture,boththeuserinterfaceandthe
databasemanagementsystemresideonthesamemachine,meaningthereisno
separationbetweentheclientandtheservercomponents.It'sasimpleandlocally
executedmodelcommonlyfoundinstandaloneapplications.
▶2-Tierarchitecture-:Ina2-tierarchitecture,theapplicationisdividedintoaclient
layerfortheuserinterfaceandaserverlayerforthedatabasemanagementsystem.
Theclientsendsrequeststotheserver,whichprocessesthemandinteractsdirectly
withthedatabase.
▶3-Tierarchitecture-:Ina3-tierarchitecture,theapplicationisdividedintothree
layers:thepresentationlayer(userinterface),applicationlayer(businesslogic),
anddatalayer(databasemanagement).Eachlayeroperatesindependently,
enhancingmodularityandscalabilityindistributedsystems.

66. Column Name Data Type Constraint
StudentID INT Primary Key
Name VARCHAR Not Null
DeptID INT Foreign Key
Example: College Database Schema
Let’s consider a schema for a simple College Database:
1. Student Table
Column Name Data Type Constraint
DeptID INT Primary Key
DeptName VARCHAR Unique
2. Department Table
Column Name Data Type Constraint
CourseID INT Primary Key
Title VARCHAR Not Null
DeptID INT Foreign Key
3. Course Table
Relationships
•Student.DeptID→ references Department.DeptID
•Course.DeptID→ references Department.DeptID

84. DATA DICTIONARY

97. DATA ABSTRACTION

102. Aspect Data Independence Data Abstraction
Definition
Protecting applications from
changes in schema or storage.
Hiding storage details and
showing only needed info.
Purpose
To allow changes without
rewriting applications.
To simplify interaction
between user and database.
Focus Change management. Information hiding.
Example
Adding a column without
changing queries.
User sees only name & email,
not storage details.
Types/Levels
Logical & Physical data
independence.
Physical, Logical & View levels.
Data Independence and Data Abstraction

106. WhatisEntity-relationshipmodel?

107. ▶TheERmodel providesawaytoconceptualizeandorganizedatainamannerthat
reflectsthereal-worldrelationshipsandentities.Itisparticularlyusefulduringthe
designphaseofadatabasewhendatabasedesignersneedtounderstand,
▶
▶
communicate,andvisualizethestructureofthedata.
TheERmodelusesvarioussymbolsandnotationstovisuallyrepresentthese
components.Entitiesaretypicallyrepresentedbyrectangles,attributesbyovals,
andrelationshipsbydiamondshapes.Linesconnectingthesesymbolsindicatethe
natureandcardinalityoftherelationships.
TheERmodelalsointroducestheconceptofkeys,whichareattributesor
combinationsofattributesthatuniquelyidentifyeachinstanceofanentitywithin
anentityset.Keysarecrucialformaintainingdataintegrityandensuringthat
eachentityisuniquelyidentifiable.

108. ▶EntitiesintheERmodelcanfurtherbecategorizedintostrongentitiesandweak
entities.Astrongentityisonethatcanbeuniquelyidentifiedbyitsattributes,
whileaweakentitydependsonarelatedstrongentityforidentification.This
distinctionhelpsinunderstandingthehierarchicalnatureofentitieswithina
database.
▶
▶
Anotherkeyconceptistheideaofcardinality,whichdefinesthenumberof
instancesinoneentitysetrelatedtothenumberofinstancesinanotherentityset
througharelationship.Cardinalitycanbeone-to-one,one-to-many,ormany-to-
many,providingawaytoexpressthenatureofrelationshipsinthemodel.
TheERmodelisanessentialtoolduringtheinitialstagesofdatabasedesign.It
facilitatescommunicationbetweendatabasedesigners,stakeholders,andend-
usersbyprovidingavisualrepresentationofthedatastructure.Thisvisual
representationaidsinidentifyingentities,attributes,relationships,andconstraints,
leadingtoamorecomprehensiveunderstandingofthedata.

109. ▶TheERmodelisanessentialtoolduringtheinitialstagesofdatabasedesign.It
facilitatescommunicationbetweendatabasedesigners,stakeholders,andend-
usersbyprovidingavisualrepresentationofthedatastructure.Thisvisual
representationaidsinidentifyingentities,attributes,relationships,andconstraints,
leadingtoamorecomprehensiveunderstandingofthedata.
▶TheEntity-RelationshipmodelisafoundationalframeworkinDBMSthatoffers
astructuredandvisualrepresentationoftherelationshipsbetweenentitiesina
database.Itisanindispensabletoolfordatabasedesigners,enablingthemto
capturetheintricaciesofreal-worldscenariosandprovidingaroadmapforthe
implementationofefficientandwell-structureddatabases.

110. WhyweuseERdiagram?

111. AdvantagesofERModel
▶Simple:itissimpletodrawanerdiagramwhenweknowentitiesand
relationships.
▶
▶
Effective:itisaneffectivecommunicationtool.
Easytounderstand:thedesignoferisverylogicalandhencetheyareeasyto
▶
▶
▶
designandunderstand,theyshowdatabasecapabilitieslikehowtables,keysand
columnsareusedtofindasolutiontothegivenquestion.
Integrated:theermodelcanbeeasilyintegratedwithrelationalmodel.
Usefulindecisionmaking:Sincesomeoftheentitiesinthedatabaseareanalyzed
byanER-Diagram,SobydrawinganER-Diagramwecometoknowwhatkind
ofattributesandrelationshipexistbetweenthem.
Easyconvertion:Itcanbeeasilyconvertedtoothertypeofmodels.

112. ▶Flexible:ermodels areflexible and canbemodifiedeasily toaccommodate
changes inthe database designorstructure.
▶
▶
▶
▶
Minimizes data redundancy: ermodels helpminimize dataredundancyby
identifying and eliminatingduplicatedata entries.
Scalability:er models canbe usedfordatabasesof varyingsizes,from small to
large.
Visualrepresentation:ermodelsprovide avisualrepresentationofthedatabase
structure, makingiteasierforstakeholders tounderstandandprovide feedback.
Documentation:ermodels provideusefuldocumentationforfuturereference,
makingiteasiertomaintain andupdatethedatabaseinthelongrun.

113. DisadvantagesofERModel
▶
▶
Lossofinformation:whiledrawinganermodelsomeoftheinformationcanbe
hiddenorlost.
Limitedrelationship:ermodelcanrepresentlimitedrelationshipsascomparedto
othermodelsanditisnotpossibletoindicateprimarykeysandforeignkeyswhen
they’reexpected.
▶
▶
▶
▶
Norepresentationfordatamanipulation:itisnotpossibletorepresentdata
manipulation(commandslikeinsert(),delete(),alter(),update())inermodel.
Noindustrystandard: thereis noindustrystandardfor notationsofanerdiagram.
Datainconsistency:duetoimpropernormalizationsomedatainconsistencymay
occurso,whilecreatinganerdiagramat least it shouldbeinthirdnormal form.
Missingcardinalities:missingrelationshipcardinalities,soeverythinglookslikea
one-to-onerelationship.one-to-onerelationshipsareactuallyquiterare.

114. ▶Difficulttomodify:ermodelscanbedifficulttomodifyoncetheyarecreated.
anychangesmadetothemodelmayrequireextensiverework,whichcanbetime-
consumingandexpensive.
▶
▶
▶
Limitedattributerepresentation:ermodelsmaynotbeabletorepresentallthe
attributesrequiredforaparticularproblemdomain.thiscanleadtoeithertheloss
ofimportantdataorthecreationofacomplexandunwieldymodel.
Lackofsupportforbusinessrules:ermodelsdonotprovidesupportforbusiness
rules,whichcanmakeitdifficulttoensuredataintegrityandenforceconstraints.
Difficulttoscale:ermodelscanbedifficulttoscaletoaccommodatelargerdata
sets.astheamountofdatainthedatabasegrows,theermodelmayneedtobe
modifiedtohandletheincreasedcomplexity.

115. DifferentSymbols

116. ExampleofERModel
From theaboveexamplewecanseetherelationshipWORK_FORisbetweenentities
EMPLOYEEand DEPARTMENT.Itisasamplediagramastheoriginaldiagram includes
Many entitiesandrelationships.
EmpID, Ename, Salary are theattributesofEmployeeentity.
DeptID,DnamearetheattributesofDepartmententity.

117. Components

118. ENTITY
▶
▶
▶
An Entity maybean object with aphysical existence –a particularperson, car,
house,oremployee–or it maybe anobject witha conceptualexistence –a
company,ajob,orauniversitycourse.
An Entity type hasakeyattribute thatuniquely identifieseachentity intheentity
set.Butsomeentity typeexistsforwhichkeyattributescan’tbe defined.These
arecalledWeakEntitytypes.
AStrong Entityisa typeof entitythat has a keyAttribute.StrongEntitydoes not
dependonotherEntityinthe Schema.Ithasaprimarykey, thathelpsin
identifying ituniquely,and itis represented by arectangle.Thesearecalled
StrongEntityTypes.

119. ATTRIBUTE
▶
▶
▶
▶
▶
Theattributeisusedtodescribethepropertyofanentity.Eclipseisusedto
representanattribute.
Thekeyattributeisusedtorepresentthemaincharacteristicsofanentity.It
representsaprimarykey.Thekeyattributeisrepresentedbyanellipsewiththe
textunderlined.
Anattributethatcomposedofmanyotherattributesisknownasacomposite
attribute.Thecompositeattributeisrepresentedbyanellipse,andthoseellipses
areconnectedwithanellipse.
Anattributecanhavemorethanonevalue.Theseattributesareknownasa
multivaluedattribute.Thedoubleovalisusedtorepresentmultivaluedattribute.
Anattributethatcanbederivedfromotherattributeisknownasaderived
attribute.Itcanberepresentedbyadashedellipse.

120. RELATIONSHIP
Arelationshipisusedtodescribetherelationbetweenentities.Diamondor
rhombusisusedtorepresenttherelationship.
Intheexamplebelow,boththestudent andthecourseareentities,andstudyisthe
relationshipbetweenthem.

121. One-to-OneRelationship
When onlyone instance of an entityis associatedwiththerelationship.For example,
astudent hasonlyone identification cardandanidentificationcardisgiventoone
person.

122. One-to-ManyRelationship
▶
▶
When onlyone instanceof the entityonthe left,andmore thanone instanceof an
entity on therightassociates withthe relationship
Forexample,a customercanplacemanyorders,butanorder cannot be placedby
manycustomers.

123. Many-to-OneRelationship
▶
▶
Whenmore thanone instance of theentityonthe left,andonlyone instanceofan
entity on therightassociates withthe relationship
Forexample,studentshave toopt for asingle course,butacourse canhave many
students.

124. Many-to-ManyRelationship
▶
▶
Whenmore thanone instance oftheentityonthe left,andmore thanoneinstance
ofanentityonthe rightassociates withthe relationship.
Forexample,youcanassignanemployee tomanyprojects anda projectcanhave
manyemployees.

125. Database Design
Databasedesignisastep-by-stepprocessofstructuringdatalogicallyandphysicallysothatitcanbe
stored,retrieved,andmaintainedefficiently.Herearethemainstepsofdatabasedesign: