ASSIGNMENT SEM -3
I0034 – DAT DATABASE ABASE MANAGEMENT
Q.1 The data is stored in the form of tables whih an be retrie!ed effiientl" b" an" #ro$ram as #er the re%&est' Sine the database &sers are !ast in n&mber( the database #ro!ides a&thentiation' E)#lain the different t"#es of database &sers'
Types of Database Users Different persons who are involved in the design, usage and maintenance of a large database include the following: 1. Database Administrator (DBA)
2. Database Designers (DBD) 3. End users 4. System analysts and application programmers 5. DBMS designers and implementers
*' Databa Database se Adminis Administrat trator or +DBA, +DBA, Database is one of the many primary resources that is used by many people in an organisation.
!e responsibilities o" database administrator are listed belo#$ %. DBMS and related so"t#are are t!e secondary resources. Administering t!is secondary resource is t!e responsibility o" t!e database administrator.
2. &e's!e usually !as t!e complete aut!ority to access and monitor t!e database.
3. &e's!e is responsible "or creating modi"ying and maintaining t!e database.
4. &e's!e grants permission to t!e users o" t!e database.
5. &e's!e stores t!e pro"ile o" eac! user in t!e database.
. &e's!e de"ines procedures to reco*er t!e database resulting "rom "ailures due to !uman natural or !ard#are causes.
2. Database Desi$ners +DBD,
A database designer designs t!e database in suc! a manner t!at it meets t!e re+uirements o" t!e clients.
3' En End d & &se sers rs ,eople #!o access t!e database +uery and update t!e database and generate t!e *arious reports- t!e database primarily eists "or t!eir use. End users are o" t#o types$ !ey are t!e users accessing DBMS #it! S/0 +ueries. o Casual users – users – !ey o Naïe users – users – !ey !ey are t!e users accessing DBMS t!roug! menus.
4' S"stem anal"sts and a##liat a##liation ion # #ro$rammers ro$rammers System analysts collect t!e in"ormation regarding re+uirements o" t!e end users and de*elop speci"ications "or canned transactions (standardised +ueries and updates #it! care"ully
programmed data *alidity c!ec1ing) t!at meet t!eir re+uirements.
Application programmers implement speci"ications de*eloped by t!e system analysts in t!e "orm o" programs. !ey are also responsible to test debug document and maintain t!ese programs. !ese are t!e programmers #!o #rite menus applications. applications.
S s"stem s"stem desi$n desi$ners ers and im#lem im#lemente enters rs
System Designers – Designers –There There need a detailed report to be generated to follow the rules of developmentt and integration of computer system to satisfy the business requirements. developmen requirements. Implementers implement implement the D!S modules and interfaces as a software pac"age.
Q.2 .om#&ter stora$e is di!ided into #rimar" memor" and seondar" memor"' Dis&ss with an e)am#le( the different t"#es of #rimar" memor" and seondar" memor"' ANS AN S
,rimary Memory is internal memory o" t!e computer. AM A AD D M bot! "orm part o" primary memory. !e primary memory pro*ides main #o #or1ing r1ing space to t!e computer. computer.!e !e "ollo#ing terms comes under primary memory o" a computer are discussed belo#$
$a%&o# A''ess Me#ory ($AM)* !e primary storage is re"erred to as random access memory (AM) because it is possible to randomly select and use any location o" t!e memory directly store and retrie*e data.
$ea& +%ly Me#ory ($+M)* !ere is anot!er memory in computer #!ic! is called ead nly Memory (M). Again it is t!e 6s inside t!e ,6 t!at "orm t!e M. !e storage o" program and data in t!e M is permanent.
!$+M* !ere is anot!er type o" primary memory in computer #!ic! is called ,rogrammable ead nly Memory (,M).
Me mory #!ic! E!$+M* !is stands "or Erasable ,rogrammable ead nly Memory o*ercome t!e problem o" ,M 7 M.
Ca',e Me#ory* !e speed o" 6,8 is etremely !ig! compared to t!e access time o" main memory. !ere"ore t!e per"ormance o" 6,8 decreases due to t!e slo# speed o" main memory.
$e"sters* !e 6,8 processes data and instructions #it! !ig! speed- t!ere is also mo*ement o" data bet#een *arious units o" computer.
Secondary memory is eternal and permanent in nature. !e secondary memory is concerned #it! magnetic memory. Secondary memory can be stored on storage s torage media li1e "loppy " loppy dis1s magnetic dis1s magnetic tapes !is memory can also be stored optically on ptical dis1s 9 6D9M. !e "ollo#ing terms comes under secondary memory o" a computer are discussed belo#$
Ma%et"' Tape* Tape* Magnetic tapes are used "or large computers li1e main"rame computers #!ere large *olume o" data is stored "or a longer time. n ,6 also you can use tapes in t!e "orm o" cassettes. !e cost o" storing data in tapes is inepensi*e. apes consist o" magnetic materials t!at store data permanently. t can be %2.5 mm to 25 mm #ide plastic "ilm9type and 5:: meter to %2:: meter long #!ic! is coated #it! magnetic material.
;ou u mig!t !a*e seen t!e gramop!one record #!ic! is circular li1e a Ma%et"' D"s* ;o dis1 and coated #it! magnetic material. Magnetic dis1s used in computer are made on t!e same principle. t rotates #it! *ery !ig! speed inside t!e computer dri*e. Data is stored on bot! t!e sur"ace o" t!e dis1. Magnetic dis1s are most popular "or direct access storage de*ice. Eac! dis1 consists o" a number o" in*isible concentric circles called trac1s magnetic spot
+pt"'al D"s* <it! e*ery ne# application and so"t#are t!ere is greater demand "or memory capacity. t is t!e necessity to store large *olume o" data t!at !as led to t!e de*elopment o" optical dis1 storage medium. ptical dis1s can be di*ided into t!e "ollo#ing categories$
Co#pa't D"s/ $ea& +%ly Me#ory (CD-$+M
0r"te +%'e $ea& Ma%y (0+$M)
Erasable +pt"'al D"s
Q.3 Co%s"&er a%y o%e ea#ple of a relat"o%al &atabase a%& s,o ,o t,e &"ffere%t operat"o%s of relat"o%al alebra 'a% be perfor#e& o% t,e table s,o"% t,e output.
Relational Relatio nal Algebra !e elational Algebra #as introduced by E. =. 6odd in %>?2. t consists o" a set o" operations on relations. Some o" t!em are as "ollo#s$ t acts as t!e building bloc1 "or relational model operations. • t is t!e basis "or DBMS. • =e# operation concepts are used in S/0 in DBMS
elational algebra is classi"ied based on t#o types namely mat!ematical set t!eory and operations "or relational databases. Set t!eoretic operations @ Based on mat!ematical set t!eory #e !a*e t!e "ollo#ing operations in relational algebra$ 8nion • ntersection • Set di""erence • 6artesian product •
elational operations @ Based on operations "or relational databases #e !a*e t!e "ollo#ing operations in relational algebra$ SE0E6 • ,E6 • •
SE0E6 and ,E6 are t!e unary operations and is a binary relation. 8nary operations are t!e one t!at operate on one relation. Binary relations are t!e ones t!at operate on t#o relations. !e t#o relations are said to re+uire t!e table to be union compatible. %. !e t#o relations'tables (say 7 S as s!o#n in ables .4(a) and .4(b)) !a*e t!e same number o" columns (!a*e t!e same degree).
2. Eac! column o" t!e "irst relation'table must be eit!er t!e same data type as t!e corresponding column o" t!e second relation'table(s).
Table 6.4(a): R Relation
Table 6.4(b): S Relation S
Union ( ∪ ) 9 !e union operation is denoted by symbol C ∪ !e result o" t!is operation is denoted by S in #!ic! C and CS are relations and t!e result is also a relation t!at includes all tuples t!at are eit!er in or in S or in bot!. •
Intersection ( ∩ ) – The intersection operation is denoted by the symbol -./ The intersection operation selects the common tuples from the two relations.
0or eample, the result of the intersection operation in the above two relations is given in Table 2.+3d4 Table 6.4(d): R∩S Relation
Cartesian products (X ) – !e 6artesian product is denoted by t!e symbol C. =or
eample let us assume and S as relations #it! n and m attributes respecti*ely- t!e 6artesian products S can be #ritten as$ (A% A2 F An) G S(B% B2 F Bn) !e result o" t!e abo*e set operation is / (A% A2 F An B% B2 F Bn) otal number o" columns in / #!ic! is called as t!e degree (/) H n I m otal number o" tuples in / #!ic! is called as count (/) H umber o" tuples in J umber o" tuples in S.
$elat"o%al operat"o%s T,e SE4ECT operat"o%* !is operation selects re+uired ro#s "rom t!e table. !is
operation is used to select t!e subset o" t!e tuples "rom a relation operation relation t!at satis"ies satis"ies a selection condition or searc! criteria. •
T,e !$+5ECT operat"o% - ,roKection operation is used to select only "e# columns "rom a table. T,e 5+IN operat"o% 6 !is is denoted as oin (). !e capability o" retrie*ing data "rom multiple tables using a single S/0 statement is one o" t!e most po#er"ul and use"ul "eatures o" DBMS
Q.7 Des'r"be t,e &"ffere%t %or#al for#s "t, o%e ea#ple t,rou,out.
ANS 8 "#porta%t %or#al for#s •
0irst %ormal 0orm 31%04
Second %ormal 0orm 3'%04 • Third %ormal 0orm 3)%04 •
oyce67ode %ormal 0orm 37%04 • 0ourth %ormal 0orm 3+%04 • 0ifth %ormal 0orm 35%04 •
9"rst Nor#al 9or# (1N9) A relation is said to be in =irst ormal =orm only i"
%. t is a relation. 2. t !as no repeating ro#s. 3. Eac! attribute *alue is atomic. " a relation does not satis"y any one o" t!e abo*e conditions t!en it is not in %=.
=or eample able 4.3(a). consider t!e S8DE sc!ema !a*ing t!e "ields as s!o#n in
Table 7.3(a)* $elat"o% S',e#a of a STUDENT $elat"o% St&. ID St&:Na#e Class A&&ress 2:% anKit! L4 E 6!o11an 6!o 11ana!al a!alli li 2:2 S!i*raK E5 And!eri (east) Mumbai
4:::4 L%: Dadra ,ost Bandra (east) Mumbai 4:::%4
Tel. No. 2???:
25%4>: >54324? 25234>?2 >>%245%35
!e abo*e table is not in %= since t!e "ield el. no. is multi9*alued "or std D 2:2 and 3:4.
Se'o%& Nor#al 9or# (2N9)
Second ormal =orm is based on "ull "unctional dependency. A "unctional dependency is said to be "ully "unctional dependency. " #e remo*e any attribute "rom t!e relation t!en t!e dependency #ill be lost in t!e relation. According to . Elmasri and S.B. a*at!e CA relation is said to be in 2= only i" t!e relation is in %= and e*ery nonprime attribute in t!e relation is "ully "unctionaly dependent on t!e primary 1ey o" t!e relation. =or eample consider a S8DN, relation relati on as s!o#n in able 4.5.
S td_ ID
Project_ Hor! Code
9ospital !s. management Sahana System
!eghna Sinh inha
Simula mulattio ion n of of !r. !urali petrol bun"
Data mining !r. in research en*amin analysis
able 4.5 (S8DN,) is in %= but not in 2=. !ere"ore #e need to decompose t!e table as gi*en in =igure 4.%.
T,"r& Nor#al 9or# (3N9)
According to . Elmasri and S.B. a*at!e Ca relation is said to be 3= i" it satis"ies and !olds a nontri*ial "unctional dependency eit!er by %. a super 1ey o" relation or 2. a prime attribute o" t!e relation. 3= is based on transiti*e dependency. et us ta"e an eample of ;#<0=SS<# relation as given in Table +.2 for our understanding of )%0.
Table 4.6: PR'SS'R Relation Pr Pro o$_ na#e na#e Pr Pro o$_id $_id
;rof. Sanat Sha ;rof. %eena (upta
rts ? 9umanities
0igure +.' shows the decomposition decomposition of the above table to form )%0.
;oy'e6Co&& Nor#al 9or# (;CN9)
ormal =orm is t!e simplest "orm o" 3=. But it is stricter t!an 3=. E*ery relation in B6= is also in 3= but not all t!e relations relat ions in 3=s need to necessarily be in [email protected]
ormal =orm. ort% "or#al or# (4") n entity is in the 0ourth %ormal 0orm 3+%04 if it is in )%0 and has entity which has more than one one>to>many relationships relationships in the relationship within the [email protected]
if any many>to>many relationship eists, they are resolved independently. 0or eample, consider the relation STAD=%T as shown in Table +.B3a4 which has three attribute names Std$name, Sub$name, 0ac$incharge. Table 4.,(a): ST-D"T Relation Std_na#e
i$t% "or#al or# (") n entity is said to be 0ifth %ormal 0orm 35%04 if and only if it is in +%0 and every *oin dependency depende ncy for the t he entity is a consequence of its candidate "eys. &oin dependency dependency means every legal state of the relation should have no additive *oin decompositio decomposition. n.
Q.8 0r"te S,ort Notes o% a. Tra%sa't"o% Co##"t b. Ato#"'"ty '. Deferre& up&ate &. Se'ur"ty e. Syste# lo
Tra%sa't"o% 'o##"t 'o##"t - ransaction commit is responsible "or ma1ing all t!e data modi"ications permanent in t!e database. <!en transaction commit occurs t!e "ollo#ing are t!e obser*ations$
o A commit is made toupon indicate t!at t!e modi"ications permanent t!is isis #ritten to t!erecord log Depending t!e type o" commit t!e logare in"ormation in and memory simultaneously #ritten to t!e dis1. o 0oc1s are released. !is means t!e modi"ications can be *ie#able. n t!is unit you #ill study t!e di""erent reco*ery reco*er y tec!ni+ues in t!e database. ;o ;ou u #ill study in detail detai l about t!e security and bac1up "eature in a database. Ato#"'"ty
Atomicity is a process #!ere it states t!e database as a rule o" CA00 or E. " any one part o" t!e transaction "ails t!e #!ole transaction "ails and t!at transaction is said to be an atomic transaction. A *ery critical c!aracteristic o" database management is t!at it !as to maintain o" t!is transactions. Anare eample o" atomic transaction can be t!at o" ordering aatomic plane nature tic1et. n case t!ere t#o actions in*ol*ed in t!is transaction. Deferre& up&ate
t de"ers or postpones any actual updates to t!e database until t!e transaction completes its eecution success"ully and reac!es its commit point. During transaction eecution t!e updates are recorded only in t!e log and in eac! bu""er. A"ter t!e transaction reac!es its commit point t!e log is "orce #ritten to t!e dis1 t!at is t!e updates are recorded in t!e database. " a transaction "ails be"ore it reac!es t!e commit point t!ere is no need to undo any operations Se'ur"ty
n t!is section you #ill study !o# to secure databases against *arious t!reats. <e #ill pro*ide you t!e introduction o" o" t!e security issues and t!e *arious t!reats to t!e databases.
<e s!all also discuss !o# to !andle t!ese t!reats using some control measures. " you go t!roug! t!is section you #ill be able to understand t!e basic database security tec!ni+ues.
!is contains e*ents logged by any operating systems. =or eample i" any dri*er "ails booting during t!e initial process t!at e*ent is recorded in t!e system log. !e operating system "inds t!e e*ents t!at are logged by t!e system components be"ore!and.
Q .< 0"t, a% ea#ple epla"% t,e or"% of +D;MS. ANS 7onsider an eample of Student$7ourse rela relations tionship hip.. The entity entity STAD=%T STAD=%T has got attributes std$id, std$name and std$address. The following will be the table for STAD=%T database. ST-D)"T Std_id !'881
Std_na#e ;riyadarshini hat
Std_add C12, 7ambridge ayout, angalore
C+5, (upta ayout, !umbai
C5+, irport #oad, Delhi
5th !ain, T! ayout, angalore
C+, ana"a ayout, uc"now
The entity 7<A#S= has got attributes 7ourse id and 7ourse name C'-RS) Cor!e_id
The relationship between the STAD=%T and 7<A#S= is identified by the relation OPTED. Therefore, the relationship database has Std$id and course$id as its attributes. 'PT)D Std_id
The eamples of queries in relational database database model are given below: 1. Ehen we require to "now the course of the student with student ID !'885, then the query may be o (o to <;T=D and loo" up student with id !'885 and return the course$id. o
It will return !1. (o to 7<A#S= and loo" up !1 and return !ar"eting.
'. 0or the query, name all students opting !ar"etin !ar"eting, g, then the query may be o (o to 7<A#S= and find course$id. It will return !1. (o to <;T=D and loo" up !1 and return all std$id. o It will return !'881 and !'885. o (o to STAD=%T and find each std$id and return each std$name. It will return ;riyadarshini hat and #ashi hanna. 0igure 1).' represents the ob*ect>oriented database model.
i&. /0.2: 'bject+'riented Databa!e 1odel
!e same eamples o" +ueries are represented in t!e obKect9oriented database model as gi*en belo#. %. <!en #e re+uire to 1no# t!e course o" t!e student #it! student st udent D MBA2::5 t!en t!e +uery may be o Searc! S8DE inde "or pointer to MB2::5. o =ollo# course pointer pointer to M% and return course name. t #ill return Mar1eting 2. =or t!e +uery name all students opting Mar1eting t!en t!e +uery may be o Searc! 6ourse inde and "ind 6ourse id. o =ollo# student pointers loo1ing up up eac! stdNid. !is process is called a*igation. a*igati on. ;ou s!ould note t!at t!e process relies on pointers and "or t!is reason pointers must m ust be persistent. <!en t!is system #as "irst initiated t!e +uerying *aried considerably. But due to t!e eistence o" bKect9riented 0anguage (0) it !as become normalised.