Introduction to Database Systems CSE 444 Lecture 4: Views and ConstraintsReview from Friday 2" Compute,"for"each"product,"the"total"number"of"sales"in"‘September’ "" SELECT Product.name, count(store) FROM Product LEFT OUTER JOIN Purchase ON Product.name = Purchase.prodName and Purchase.month = ‘September’ GROUP BY Product.name Prod uct(n ame,"category)"Purchase(prodName,""month,"store)"Name Category Gizmo Gadget Camera Photo OneClick Photo Product(ProdName Month Store Gizmo Nov Wiz Camera Sept NULL Camera Sept Wiz Purchase(Views vs Tables 3" Views"are"relaBons"except"that"they"may"not"be"physically"stored." Why"do"we"need"views?" Example:" Employee(ssn,"name,"department,"project,"salary)"CREATE"VIEW""Developers"AS"""" "SELECT" "name,"project"""" "FROM""" "Employee""" "WHERE" "department"="‘Development’"Example 4"CREATE"VIEW""CustomerPrice""AS""""""""""SELECT""x.customer,"y.price""""""""""FROM"""""Purchase"x,"Product"y""""""""""WHERE"""x.product"="y.pname"Purchase(customer,"product,"store)"Product(pname,"price)"“virtual"table”"Example 5"SELECT"" "u.customer,"v.store"FROM""" "CustomerPrice"u,"Purchase"v"WHERE"" "u.customer"="v.customer""""""and"u.price">"100"Purchase(customer,"product,"store)"Product(pname,"price)"CustomerPrice(customer,"price)"6"Types of Views Virtual"views:" Used"in"databases" Computed"only"on_demand"–"slow"at"runBme" Always"up"to"date" Materialized"views" Used"in"data"warehouses" Pre_computed"offline"–"fast"at"runBme" May"have"stale"data" Indexes"are"materialized"views"(read"book)"7"Queries Over Views: Query Modification SELECT u.customer, v.store FROM CustomerPrice"u, Purchase v WHERE u.customer = v.customer and u.price > 100 CREATE VIEW CustomerPrice AS SELECT x.customer, y.price FROM Purchase x, Product y WHERE x.product = y.pname View: Query: Purchase(customer, product, store) Product(pname, price)CREATE"VIEW""CustomerPrice""AS""SELECT""x.customer,"y.price""FROM"""Purchase"x,"Product"y""WHERE"x.product"="y.pn ame"SELECT"" "u.customer,"v.store"FROM"""""CustomerPrice"u,"Purchase"v"WHERE""u.customer"="v.customer""""""and"u.price">"100"SELECT"" "u.customer,"v.store"FROM"""""CustomerPrice"""""""""""""""""u,""Purchase"v""WHERE""u.customer"="v.customer""""""and"u.price">"100"8"Modified(query:"""SELECT""x.customer,"y.price"""FROM"" "Purchase"x,"Product"y"""WHERE "x.product"="y.pname"SELECT"" "u.customer,"v.store"FROM"""""(SELECT"""x.customer,"y.price""""""""""""""""" "FROM""""Purchase"x,"Product"y""""""""""""""""" "WHERE""x.product"="y.pname)""u,"Purchase"v"WHERE""u.customer"="v.customer""""""and"u.price">"100"9"Queries Over Views: Query Modification SELECT"" "x.customer,"v.store"FROM""" "Purchase"x,"Product"y,"Purchase"v,""WHERE"" "x.customer"="v.customer""""""and"y.price">"100"""""and"x.product"="y.pname"Modified(and(unnested(query:"10"Applications of Virtual Views Physical"data"independence."E.g." VerBcal"data"parBBoning" Horizontal"data"parBBoning" Security" Handle"different"access"rights" The"view"reveals"only"what"the"users"are"al lowed"to"know"11"Vertical Partitioning SSN( Name( Address( Resume( Picture(234234" Mary" Huston" Clob1…" Blob1…"345345" Sue" Seajle" Clob2…" Blob2…"345343" Joan" Seajle" Clob3…" Blob3…"234234" Ann" Portland" Clob4…" Blob4… "Resumes"SSN( Name( Address(234234" Mary" Huston"345345" Sue" Seajle"""."."."SSN( Resume(234234" Clob1…"345345" Clob2…"SSN( Picture(234234" Blob1…"345345" Blob2…"T1"T2" T3"12"Vertical Partitioning CREATE"VIEW""Resumes""AS""""""""""SELECT"""T1.ssn,"T1.name,"T1.address,""""""""""""""""""""""""""T2.resume,"T3.picture"""""""""""FROM"""""T1,T2,T3""""""""""WHERE"""T1.ssn=T2.ssn"and"T2.ssn=T3.ssn"Why"use"verBcal"parBBoning?"SELECT "address"FROM"""" "Resumes"WHERE""""name"="‘Sue’"Which"of"the"tables"T1,"T2,"T3"will"be"queried"by"the"system"?"13"Vertical Partitioning When"to"do"this:" When"some"fields"are"large,"and"rarel y"accessed" E.g."Picture" In"distributed"databases" Customer"personal"info"at"one"site,"customer"profile"at"another" In"data"integraBon" T1"comes"from"one"source" T2"comes"from"a"different"source"14"Horizontal Partitioning SSN( Name( City( Country(234234" Mary" Huston" USA"345345" Sue" Seajle" USA"345343" Joan" Seajle" USA"234234" Ann" Portlan d" USA"__" Frank" Calgary" Canada"__" Jean" Montreal" Canada"Customers"SSN( Name( City( Country(234234" Mary" Huston" USA"CustomersInHuston"SSN( Name( City( Country(345345" Sue" Seajle" USA"345343" Joan" Seajle" USA"CustomersInSea@le"SSN( Name( City( Country(__" Frank" Calgary" Canada"__" Jean" Montreal" Canada"CustomersInCanada"15"Horizontal Partitioning CREATE"VIEW""Customers""AS""""""CustomersInHuston""""""""""UNION"ALL""""""CustomersInSeajle""""""""""UNION"ALL"""""""."."."SELECT"""name"FROM""""Customers"WHERE""city"="‘Seajle’"Which tables are inspected by the system ?16"Horizontal Partitioning CREATE"VIEW""Customers""AS""""""(SELECT"*"FRO M"CustomersInHuston"""""""WHERE"city"="‘Huston’)""""""""""UNION"ALL""""""(SELECT"*"FRO M"CustomersInSeajle"""""""WHERE"city"="‘Seajle’)""""""""""UNION"ALL"""""""."."."Better:17"Horizontal Partitioning SELECT"name"FROM""""Customers"WHERE"""city"="‘Seajle’"SELECT"name"FROM""""CustomersInSeajle"18"Horizontal Partitioning OpBmizaBons:" E. g."archived"app li caBons"and"acBve"applicaBon s" Distributed"databases" Data"integraBon"19"Views and Security CREATE "VIEW"Pu blicCusto mers""""""""""SELECT"" "Name,"Address""""""""""FROM""""""Customers"Name( Address( Balance(Mary" Huston" 450.99"Sue" Seajle" _240"Joan" Seajle" 333.25"Ann" Portland" _520"Customers:(Fred"is"not"allowed"to"see"this"Fred is allowed to see this20"Views and Security Name( Address( Balance(Mary" Huston" 450.99"Sue" Seajle" _240"Joan" Seajle" 333.25"Ann" Portland" _520"CREATE "VIEW"BadCreditCustomers""""""""""SELECT" "*""""""""""FROM" "Customers""""""""""WHERE" "Balance"<"0"John"is"not"allowed"to"see">0"balances"Customers:(Views and Updates 21" Food"for"th ought:" What"happens"when"we"insert"a"tuple"to"a"view?" Update"a"tuple"from"a"view?" Can"we"always/ever"do"this?"22"Constraints in SQL Constraints"in"SQL:" Keys,"foreign"k eys" Ajribute_level"constraints" Tuple_level"constraints"
View Full Document
Unlocking...