lecture 9 - more on sql - views

7/25/2019 Lecture 9 - More on SQL - Views

1/32

ICT 321

VIEWS AND DATABASE

PROGRAMMING


2/32

Views

In some cases, it is not esi!a"#e $o!

a## %se!s to see t&e enti!e #o'ica#moe# (t&at is, a## t&e act%a# !e#ationssto!e in t&e ata"ase)*

A +iew contains !ows an co#%mns,%st #i-e a !ea# ta"#e) T&e .e#s in a+iew a!e .e#s $!om one o! mo!e !ea#

ta"#es in t&e ata"ase) In somecases, we can moi$/ a +iew an0!esent t&e ata as i$ t&e ata we!ecomin' $!om a sin'#e ta"#e)


3/32

Views Consie! a 0e!son w&o nees to

-now an inst!%cto!s name ane0a!tment, "%t not t&e sa#a!/) T&is0e!son s&o%# see a !e#ation

esc!i"e, in S, "/

select ID, name,

dept_name from instructor


4/32

Views A view0!o+ies a mec&anism to &ie

ce!tain ata $!om t&e +iew o$ ce!tain%se!s)

An/ !e#ation t&at is not o$ t&e

conce0t%a# moe# "%t is mae +isi"#eto a %se! as a +i!t%a# !e#ation4 isca##e a view)

In DBMS, A viewis a 5%e!/ sto!e int&e ata"aseT&in- o$ it as a ta"#e e.nition $o! $%t%!e

%se


5/32

Views Views a!e %se$%#, 0!ima!i#/ $o! READ6

on#/ %se!s an a!e not a#wa/s sa$e $o!CREATE, 7PDATE, an DEETE)


6/32

Views a+anta'es Simplicity

Ce!tain ata items a!e s%"ect to!et!ie+a# on a $!e5%ent "asis, t&en t&e/nee a 0!ee.ne 5%e!/

Security P!o+ies a #imite access to %se!s an

!ea6on#/ ata $!om "ase ta"#es

Maintainability8%st as an o"ect6o!iente c#ass a"st!acts

%ne!#/in' ata an "e&a+io!, a +iewa"st!acts t&e 'o!/ etai#s o$ a 5%e!/


7/32

View a+anta'es Development

Views can a##ow m%#ti0#e e+e#o0e!s to0oint t&ei! e+e#o0ment coe to a sin'#eata"ase, /et ma-e c&an'es in s0eci.c

ta"#es) Virtual felds9

In a ata"ase esi'n w&e!e sto!in'ca#c%#ate .e#s is not 0e!mitte, o! in

sit%ations w&e!e new ca#c%#ate .e#snee to "e ae, +iews can 0!o+ie a+i!t%a# !e0!esentation o$ t&ose co#%mns

t&at a!e ca#c%#ate as t&e 5%e!/ ise:ec%te


8/32

View De.nition Views a!e c!eate t&!o%'& %se o$ a

CREATE VIEW comman t&atinco!0o!ates %se o$ t&e SEECTstatement)

Views a!e 5%e!ie %st #i-e ta"#es) To c!eate a +iew %se t&e $o##owin'

/nta:9

CREATEVIEW+iew;name ASSELECTco#%mn;name(s*

FROMta"#e;name

WHEREconition


9/32

View De.nition View e.nition is not t&e same as

c!eatin' a new !e#ation "/ e+a#%atin't&e 5%e!/ e:0!ession Rat&e!, a +iew e.nition ca%ses t&e

sa+in' o$ an e:0!ession< t&e e:0!ession iss%"stit%te into 5%e!ies %sin' t&e +iew)


10/32

View e:am0#e

E:am0#e9 C!eate a +iew wit& tit#e an/ea! an mae "/ Pa!amo%nt st%io)

Mo+ie (tit#e, /ea!, #en't&, inCo#o!,st%ioName, 0!o%ce!C=*

CREATE VIEWPa!amo%ntMo+ie AS

SELECTtit#e,/ea!

FROMMo+ie

WHEREst%ioName > ?Pa!amo%nt@ ce!t= ?Gone Wit& t&e Win@ ?Pa!amo%nt@ 1

WHEREtit#e > ?Sta! T!e- t&e Mo+ie@ 1WHEREtit#e > ?Sta! T!e- t&e Mo+ie@ AND

st%ioName > ?Pa!amo%nt@

lecture 9 - more on sql - views

Documents