Lecture'04:'Views'and'Constraints'October'6,'2010'Guest'lecturer:'Dan'Suciu'444 Fall 2010 12 Views Views are relations, except that they may not be physically stored. For presenting different information to different users Employee(ssn, name, department, project, salary) Payroll has access to Employee, others only to Developers CREATE VIEW Developers AS SELECT name, project FROM Employee WHERE department = ‘Development’3 CREATE VIEW CustomerPrice AS SELECT x.customer, y.price FROM Purchase x, Product y WHERE x.product = y.pname Example Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price) “virtual table”4 SELECT u.customer, v.store FROM CustomerPrice u, Purchase v WHERE u.customer = v.customer AND u.price > 100 We can later use the view: Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price)5 Types of Views • Virtual views: – Used in databases – Computed only on-demand – slow at runtime – Always up to date • Materialized views – Used in data warehouses – Pre-computed offline – fast at runtime – May have stale data – Indexes are materialized views (read book) We discuss only virtual views in class6 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) CustomerPrice(customer, price)7 Queries Over Views: Query Modification 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 Modified query: Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price)8 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: Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price)9 Another Example SELECT DISTINCT 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)10 Answer SELECT DISTINCT u.customer, v.store FROM CustomerPrice u, Purchase v WHERE u.customer = v.customer AND u.price > 100 SELECT DISTINCT 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 Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price)11 Applications of Virtual Views • Physical data independence. E.g. – Vertical data partitioning – Horizontal data partitioning • Security – The view reveals only what the users are allowed to know12 Vertical Partitioning SSN Name Address Resume Picture 234234 Mary Huston Clob1… Blob1… 345345 Sue Seattle Clob2… Blob2… 345343 Joan Seattle Clob3… Blob3… 234234 Ann Portland Clob4… Blob4… Resumes SSN Name Address 234234 Mary Huston 345345 Sue Seattle . . . SSN Resume 234234 Clob1… 345345 Clob2… SSN Picture 234234 Blob1… 345345 Blob2… T1 T2 T313 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 When do we use vertical partitioning ?14 Vertical Partitioning SELECT address FROM Resumes WHERE name = ‘Sue’ Which of the tables T1, T2, T3 will be queried by the system ?15 Vertical Partitioning When to do this: • When some fields are large, and rarely accessed – E.g. Picture • In distributed databases – Customer personal info at one site, customer profile at another • In data integration – T1 comes from one source – T2 comes from a different source16 Horizontal Partitioning SSN Name City Country 234234 Mary Huston USA 345345 Sue Seattle USA 345343 Joan Seattle USA 234234 Ann Portland USA -- Frank Calgary Canada -- Jean Montreal Canada Customers SSN Name City Country 234234 Mary Huston USA CustomersInHuston SSN Name City Country 345345 Sue Seattle USA 345343 Joan Seattle USA CustomersInSeattle SSN Name City Country -- Frank Calgary Canada -- Jean Montreal Canada CustomersInCanada17 Horizontal Partitioning CREATE VIEW Customers AS CustomersInHuston UNION ALL CustomersInSeattle UNION ALL . . .18 Horizontal Partitioning SELECT name FROM Cusotmers WHERE city = ‘Seattle’ Which tables are inspected by the system ? WHY ???19 Horizontal Partitioning CREATE VIEW Customers AS (SELECT * FROM CustomersInHuston WHERE city = ‘Huston’) UNION ALL (SELECT * FROM CustomersInSeattle WHERE city = ‘Seattle’) UNION ALL . . . Better:20 Horizontal Partitioning SELECT name FROM Cusotmers WHERE city = ‘Seattle’ SELECT name FROM CusotmersInSeattle21 Horizontal Partitioning Applications: • Optimizations: – E.g. archived applications and active applications • Distributed databases • Data integration22 Views and Security CREATE VIEW PublicCustomers! SELECT Name, Address! FROM Customers!Name Address Balance Mary Huston 450.99 Sue Seattle -240 Joan Seattle 333.25 Ann Portland -520 Fred is allowed to see this Customers: Fred is not allowed to see this23 Views and Security Name Address Balance Mary Huston 450.99 Sue Seattle -240 Joan Seattle 333.25 Ann Portland -520 CREATE VIEW BadCreditCustomers! SELECT *! FROM Customers! WHERE Balance < 0!Customers: John is not allowed to see >0 balances24 Constraints in SQL Constraints in SQL: • Keys, foreign keys • Attribute-level constraints • Tuple-level constraints • Global constraints: assertions The more complex the constraint, the harder it is to check and to enforce simplest Most complex25 Keys OR: CREATE TABLE Product ( name CHAR(30) PRIMARY KEY, category VARCHAR(20)) CREATE TABLE Product ( name CHAR(30), category VARCHAR(20) PRIMARY KEY (name)) Product(name, category)26 Keys with Multiple Attributes CREATE TABLE Product ( name
View Full Document
Unlocking...