Download Database Management Systems (DBMS) Theory Notes: Concepts, Models, and Languages and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
DBMS(CSE 201 )THEORYNOTES
PrimaryKey:Aprimarykeyisaconstraintinatablewhichuniquely identifieseachrow/recordinadatabasetable.Primarykeysmust containuniquevalues.AprimarykeycolumncannothaveNULLvalues. Atablecanhaveonlyoneprimarykey,whichmayconsistofsingleor multiplefields. UniqueKey:AUniquekeyisaconstraintinatablewhichidentifieseach row/recordinadatabasetable.AnattributewithUNIQUEKEYcanhave NULLvalue. ForeignKey:Aforeignkeyisafield(orcollectionoffields)inonetable thatuniquelyidentifiesarowofanothertable. CandidateKey:Acandidatekeyisacolumn,orsetofcolumns,inatable thatcanuniquelyidentifyanydatabaserecordwithoutreferringtoany otherdata.Eachtablemayhaveoneormorecandidatekeys,but onecandidatekeyisspecial,anditiscalledtheprimarykey. FundamentalsofDatabase 1 )Field:Thesmallestpieceofmeaningfullinformationinafileis calledadataitem orfield. Example: Name Locality City State Pincode
2 )Data:Dataaretherawfacts,facesofinformationthatrepresents qualitativeorquantitativeattributesofavariable. Example:HeightofmountEverest. 3 )Information:Setofdatathatconveyameaning. Example:AbookonMountEverest. 4 )Knowledge:Aknowledgebase(KB)isatechnologyusedtostore complexstructuredandunstructuredinformationusedbya computersystem. 5 )Metadata:Itisadataaboutotherdata.Itprovidesinformation aboutotherdatamanagedwithinanapplicationorenvironment. 6 )Database:Itisawelldefiedcollectionofinformationthatis organisedsothatitiseasilymanageable. 7 )Databasesystem:Asystem designedtomanagelargebodiesof information.TheDBSmustprovidesafety. 8 )Databasemanagementsystem:Itisasetofcomputerprograms thatcontrolsthecreation,maintenance,andtheuseofdatabase ofanorganisationanditsenduser.
1 )Dataredundancyandinconsistency:Since,differentprogrammers createthefilesandapplicationprogramsoveralongperiodthe variousfileslikelytohavedifferentstructuresandprogramsmay bewritteninseveralprogramminglanguages.Moreover,thesame informationmaybeduplicated.Forexample:Addressand telephonenumberofacustomermayappearinafilethatconsists ofcheckingaccountrecords.Thisredundancy,leadstoahigher storageandexcesscost. 2 )Dataisolation:Becausedataarescatteredinvariousfilesof differentformats,writingnewapplicationsprogramstoretrieve theappropriatedataisdifficult. 3 )Integrity:Thedatavaluesstoredindatabasemustsatisfycertain typesofconsistencyconstraints. 4 )Atomicity:Onetransactioniseithersuccessfulorfailed. 5 )Concurrentaccessanomalies:Readsmorethanoneuser simultaneously. TypesofUsers 1 )Enduser:Apersonwhohasnoknowledgeaboutthepresenceand structureofthedatabase. 2 )Onlineuser:Apersonwhomaycommunicatewiththedatabase directlyviaanonlineterminalorindirectlyviaauserinterfacead applicationsprogram.Theseuserareawareofdatabase. 3 )Applicationprogrammers:Professionalprogrammerarethose whoareresponsiblefordevelopingapplications/UItheapplication couldbewritteninageneralpurposelanguage. 4 )Databaseadministrator:Itisapersonknowledgeablepersonwho isresponsibleforthephysicaldesignandmanagementof databaseandevaluationandimplementationofdatabase (Physical).
Dataabstraction Themajorpurposeofadatabasesystem istoprovideuserswithan abstractview;thesystem hidescertainfeaturesabouthow/wheredata isstored.Example:Abankercanviewdetailsofcustomers. Levelsofdataabstractions: InternallevelsofDBMSistheonewhichisclosettodatastorageit referstohowdataisactuallystoredinthephysicallevelsuchasHard Disk.Iisalsoknownasphysicallevel. Logicallevelsdealwithwhatdataarestoredandrelationshipsbetween dataitems…/Needsrevisit/ Viewlevelistheonewhichisclosetotheuser.Aviewinvolvesonlydata whichareon;yconcernedtotheuser. DifferencebetweenDAandDBA
Itdoesnotjointhetables Itcanjoinmorethanonetables Itdoesnotusetheforeignkey Itusesforeignkey DataModels: Theyareusedtodesignadatabase.Datamodelsareacollectionof conceptualtoolsfordescribingdata,datarelationship,datasemantics andconsistencyconstraints.Itprovidesawaytodesignthedatabaseat physical,logicalandviewlevel.Theycanbeclassifiedintofour categories. 1 )RelationalModel:Thismodelusesacollectioniftablesto representbothdataandrelationshipsamongthosedata.Each tablehasmultiplecolumnsandeachcolumnhasauniquename,It isarecordbasedmodelbecausedatabaseisstoredinafixed format. 2 )EntityRelationshipDataModel:Itisbasedonaperceptionofareal worldthatconsistsofacollectionofbasicobjects(calledentity) andofrelationshipamongtheseobjects.Anentityisathingor objectthatisdistinguishablefrom othertables. Example:
3 )ObjectBasedDataModel:Theobjectorienteddatamodelin anotherdatamodelinanotherdatamodelthathasbeenseen increasingattentiontheobjectorienteddatamodel…//HERE 4 )SemiStructuredDataModel:Itpermitsthespecificationofdata whereindividualdataitemsofthesametypemayhavedifferent typesofattributes.Example:XMLiswidelyusedtorepresentsemi structureddatamodels. Databaselanguages: 1 )DataDefinitionLanguage(DDL):Itisusedtodescribedataand datastructureofadatabasewiththehelpofdataschemeandalso changedlater.Example:TypicalDDLoperationswiththeir respectivekeywordsinSQL.TherearevariouscommandsofDDL: CREATETABLE,ALTERTABLEandDROPTABLE. 2 )DataManipulationLanguage(DML):Itisusedtostore,searchand read.Suchoperationscanbedonewithadatamanipulation languagewithINSERT,MODIFY,UPDATE,DELETE,SELECT. 3 )DataControlModel(DCL):Itisusedtocontrolaccesstodata storedinadata.Example:GRANT,REVOKE. 4 )TransactionControlLanguage(TCL):TransactionControl
3 )AlternateKey:Acandidatekeywhichisnotapartoftheprimary keyiscalledthealternatekey.Example:IfE_noisPKthenVoter_ID, Pad_IDandadhaar_noareAK. 4 )CompositeKey:CreatingmorethanonePKjointlyisknownasCK. Example:Employee_IDandVoter_ID. 5 )SuperKey:Ifweaddadditionalattributestotheresulting combinationinstance.Suchaugmentedkeysarecalledsuperkey. APKiscalledaminimum SuperKey.Example:E_Noandname. 6 )ForeignKey:ItisaPKofmastertablewhichisreferencedinthe currenttable.So,itisknownasforeignkeyinthecurrenttable.It hasoneormorecolumnswhosevaluemustexistinPKofother tables. EntityRelationshipDiagram Convention: Entity:Theyarerepresentedbyarectangularboxwiththenameifentity inthebox. Example: AnAttributeisshownasanellipse attachedtoarelevantentitywithaline.
.Theentitynameiswritteninuppercasewhereasattributenameis writtenislowercase. .ThePKorkeyattributesareunderlined. .Theattributesareconnectedusinglines.Iftheattributeissimplesingle valued,asinglelineused. .Iftheattributeisaderivedattributethenadottedellipseisused. .Ifitismultivaluedthendoublelinesareused. Relationship:Theassociationorrelationshipthatexistsbetweenentities relatesdataitemsinameaningfulway. Degreeofrelationship:Thedegreeofarelationshipindicatesthe numberofassociateentities….//HERE Therearefourtypesofrelationships:Unary,Binary,Tertiary,and Quaternary. 1 )Unary:Existswhenanassociationismaintainedwithinasingle entity.
Simpleattributes: Itcannotbedividedintosimplercomponents.Example:Ageof employee. CompositeAttribute: Itcanbesplitintosimplercompounds.Example:Name,Dataofjoining ofemployee. SingleValuedattribute: Itcantakeonlyonevalueforentityinstance.Example:Age. MultivaluedAttribute: Itcantakeupmanyvalues.Example:Skillsetofemployee. StoredAttributes: Astoredattributeisanattributethatneedtobestoredpermanently. Example:Name. Derived: Theyarederivedfrom anotherattribute.Example:Yearofservicefrom thedateofjoining.
RegularEntityandWeakEntity: Regularentityhasitsownkeyattributes.Example:Emp_ID,E_No. Weakentitydependsonotherentitiesforitsexistenceanddoesnot haveanykeyattributesoritsown.Example:Spousename. Strong/Regular Weak AnentitysetwhichhasaPK. Itdoesnothavesufficient attributesforaPK. Example:EntitiesEMPandSTUD arestrongentitiesbecausethey havePK. Theyarerepresentedbydouble retangles. EPISODE3: DatabaseSchemea: Itisadescriptionofadatabasewhichisspecifiedduringdatabsedesign anditdoesnotchangeveryoften. Example:STUDENT Name Student_ENo Class Major ASchemadiagram isoftenusedtorepresentdisplayingtheschemas. ThedataisthedatabaseataparticularmomentintimeiscalledDB.
ReferencingColumns Itiswritteninareferencingtable thatareFKforacolumninother referencingcolumn ReferencedColumn PKcolumnoruniquecolumnina referencedtable. Generalization: Itistheresultoftakingtheunionofseverallowerlevelentitysetsto proceduretohigherlevelentityset.Therearesimilaritiesbetween SAVING_ACCOUNTandCURRENT_ACCOUNT. Accountentitysetinabankaccount.Thesesimilaritiesareinthesense thattheyhaveseveralattributesincommon.Thiscanbeexpressedby generalization.Whichiscontainmentrelationshipthatexistsbetweena higherlevelentitysetandlowerlevelentityset?Example:Accountisa higherlevelentityandsavingandcurrentaccountarelowerlevel. Aggregation:
Processofcompilinginformationonanobject.Thereby,abstractinga Higherlevelobjectorentityset. TO Specialization:
