of x

Database Management System

Published on 2 weeks ago | Categories: Documents | Downloads: 0 | Comments: 0

Comments

Content

 

ASSIGNMENT SEM -3

  I0034 – DAT DATABASE ABASE  MANAGEMENT

SYSTEM

Q.1 The data is stored in the form of tables whih an be retrie!ed effiientl" b" an" #ro$ram as #er the re%&est' Sine the database &sers are !ast in n&mber( the database #ro!ides a&thentiation' E)#lain the different t"#es of database &sers'

ANS

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 eists "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##liat a##liation 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.

-' DB

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 seondar" memor"' Dis&ss with an e)am#le( the different t"#es of #rimar" memor" and seondar" memor"' ANS AN S

!r"#ary Me#ory

,rimary Memory is internal memory o" t!e computer. AM A AD 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 etremely !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.

Se'o%&ary #e#ory

Secondary memory is eternal 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 6D9M. !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 inepensi*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

2.

0r"te +%'e $ea& Ma%y (0+$M)

3.

Erasable +pt"'al D"s 

Q.3 Co%s"&er a%y o%e ea#ple of a relat"o%al &atabase a%& s,o ,o t,e &"ffere%t operat"o%s of relat"o%al alebra 'a% be perfor#e& o% t,e table s,o"% t,e output.

ANS

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

#

Std$ID

Std$ID

%ame

%ame

1

&yothi

)

'

(anga

+

)

(iri*a

5

Tanvi

2

!anvi

+

 n"itha

(iri*a  n"itha

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 eample, the result of the intersection operation in the above two relations is given in Table 2.+3d4 Table 6.4(d): R∩S Relation

#S



Std$ID

%ame

)

(iri*a

+

n"itha

  Cartesian products (X )  – !e 6artesian product is denoted by t!e symbol C. =or 

eample 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 ea#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 37%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 eample able 4.3(a). consider t!e S8DE 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

3:4

0a*anya

 

E

4:::4 L%: Dadra ,ost Bandra (east) Mumbai 4:::%4

Tel. No. 2???:

25%4>: >54324? 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 eample consider a S8DN, relation relati on as s!o#n in able 4.5.

S td_  ID

Project_  Hor! Code

181

9!S1

'8)

SI!'

)8)

D!1

 

Std_ "a#e

Cla!!

Proj_ na#e

  Pro$_  inc%ar&e

'8

#an*ith &ha

1 !

9ospital !s. management Sahana System

 

)8

!eghna Sinh inha

' !

Simula mulattio ion n of  of !r. !urali petrol bun"

 

15

Sami"sha Shu"la

) !

Data mining !r. in research en*amin analysis

able 4.5 (S8DN,) 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 eample 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

Sbject! !*eciali!ation

Qali$i+ cation

De*t_ "#ber 

De*t_ "a#e

H'D_ID

Dr. #ao

 

,1

0inance

;hD

 

D1

!anage> ment

9'

Dr. #avi

 

,'

!ar"eting

;hD

 

D1

!anage> ment

9'

1

7omputer 

!7

 

D'

IT

'

science Sociology

;rof. Sanat Sha ;rof. %eena (upta

!, !;hil

D)

 

 rts ? 9umanities

91

9)

0igure +.' shows the decomposition decomposition of the above table to form )%0.

;oy'e6Co&& Nor#al 9or# (;CN9)

[email protected] 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. ort% "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 eists, they are resolved independently. 0or eample, 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

Sb_na#e

ac_inc%ar&e

;ushpa

!aths

;rof. 7hidanand

;ushpa

;hysics

;rof. #amesh

;ushpa

;hysics

;rof. 7hidanand

;ushpa

!aths

;rof. #amesh

 

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 eample 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 eecution success"ully and reac!es its commit point. During transaction eecution 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.

Syste# lo 

!is contains e*ents logged by any operating systems. =or eample 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% ea#ple epla"% t,e or"% of +D;MS. ANS 7onsider an eample 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

!'88'

shwini Sharma

C+5, (upta ayout, !umbai

!'88)

#avi &oshi

C5+, irport #oad, Delhi

!'88+

Shilpa Saena

5th !ain, T! ayout, angalore

!'885

#ashi hanna

C+, ana"a ayout, uc"now

The entity 7<A#S= has got attributes 7ourse id and 7ourse name C'-RS) Cor!e_id

Cor!e_na#e

!1

!ar"eting

91

9uman #esource

IS1

Information Science

I T'

Information Technology

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

Cor!e_id

!'881

 

!1

!'88'

 

91

!'88)

 

IS1

!'88+

 

IT'

!'885

 

!1

 

The eamples 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 eamples 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! S8DE 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 eistence o" bKect9riented 0anguage (0) it !as become normalised.

Sponsor Documents

Hide

Forgot your password?

Or register your new account on INBA.INFO

Hide

Lost your password? Please enter your email address. You will receive a link to create a new password.

Back to log-in

Close