Introduction to Database SystemsCSE 444CSE 444Lecture 4: Views and ConstraintsCSE 444 - Summer 2010 1Outline•Views: Sections 8.1, 8.2, 8.3• Constraints: Sections 2.3, 7.1, 7.2• Won’t discuss updates ! In sections…2CSE 444 - Summer 2010ViewsViews are relationsexcept that they might notbe physicallystoredViews are relations,except that they might not be physically storedFor presenting different information to different usersEmployee(ssn, name, department, project, salary)CREATE VIEW Developers ASSELECT name, projectFROMEmployeeFROMEmployeeWHERE department = ‘Development’3Can give Payroll access to Employee, others only to DevelopersCSE 444 - Summer 2010ExamplePurchase(customer, product, store)Product(pname, price)CREATE VIEW CustomerPrice ASSELECTx.customer,y.priceSELECTx.customer, y.priceFROM Purchase x, Product yWHERE x.product = y.pnameCustomerPrice(customer, price) “virtual table”4CSE 444 - Summer 2010ExamplePurchase(customer, product, store)Product(pname, price)We can later use theview just like any other relation :CustomerPrice(customer, price)SELECT DISTINCT u.customer, v.storeFROMCustomerPriceuPurchasevWe can later use the view just like any other relation :FROMCustomerPriceu, Purchase vWHERE u.customer = v.customer ANDu.price > 1005CSE 444 - Summer 2010Types of ViewsWe discuss• Virtual views– Used in databasesCtdlddlttionly virtualviews in class–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 dataIndexesarematerialized views (read book)6–Indexes arematerialized views (read book)CSE 444 - Summer 2010Queries Over Views:Queries Over Views:Query ModificationCREATE VIEW CustomerPrice ASSELECTtiView:SELECTx.customer, y.priceFROM Purchase x, Product yWHERE x.product = y.pnameView:SELECT DISTINCT u.customer, v.storeFROMCustomerPriceuPurchasevQFROMCustomerPriceu, Purchase vWHERE u.customer = v.customer ANDu.price > 100Query:7CSE 444 - Summer 2010Queries Over Views:Queries Over Views:Query ModificationModified query:SELECT DISTINCT u.customer, v.storeFROM (SELECT x.customer, y.priceFROM PurchasexProductyFROM Purchase x, Product yWHERE x.product = y.pname) u, Purchase vWHERE u.customer = v.customer ANDu.price > 1008CSE 444 - Summer 2010Queries Over Views:Queries Over Views:Query ModificationModified and unnested query:SELECT DISTINCT x.customer, v.storeFROM Purchase x, Product y, Purchase v, WHERExcustomer=vcustomerANDWHEREx.customer v.customerANDy.price > 100 ANDx.product = y.pname9CSE 444 - Summer 2010Applications of Virtual Views• Increased physical data independence. E.g.– Vertical data partitioningH i t l d t titi i–Horizontal data partitioning•Logical data independenceEg•Logical data independence. E.g.– Change schemas of base relations (i.e., stored tables)• Security10– View reveals only what the users are allowed to knowCSE 444 - Summer 2010Vertical PartitioningSSN NameAddress Resume PictureResumes234234 Mary Huston Clob1… Blob1…345345 Sue Seattle Clob2… Blob2…345343 Joan Seattle Clob3… Blob3…234234 Ann Portland Clob4… Blob4…T1T2 T3SSN Name Address234234 Mary Huston345345SueSeattleSSN Resume234234 Clob1…345345Clob2SSN Picture234234 Blob1…345345Blob211345345SueSeattle. . .345345Clob2…345345Blob2…Vertical PartitioningCREATE VIEW Resumes ASSELECT T1.ssn, T1.name, T1.address,T2.resume, T3.picture FROMT1 T2 T3FROMT1,T2,T3WHERE T1.ssn=T2.ssn and T2.ssn=T3.ssn12CSE 444 - Summer 2010Vertical PartitioningSELECT addressFROM ResumesWHERE name = ‘Sue’Which of the tables T1, T2, T3 willbe queried by the system ?When do we use vertical partitioning ?13CSE 444 - Summer 2010Vertical Partitioning Applications1. Can improve performance of some queries– When queries touch small fraction of columnsOnly need to read desired columns from disk–Only need to read desired columns from disk– Can produce big I/O savings for wide tables– Potential benefit in data warehousing applications•ButR tdk l ddltf hd–Repeated key columns add a lot of overhead– Need expensive joins to reconstruct tuplesCSE 444 - Summer 2010 14Vertical Partitioning Applications2. When some fields are large and rarely accessed– E.g. Picture3. In distributed databases– Customer personal info at one site, profile at another4. In data integration– T1 comes from one source–T2 comes from a different source15CSE 444 - Summer 2010Horizontal PartitioningSSNNameCityCountryCustomersSSN Name City CountryCustomersInHoustonSSNNameCityCountry234234 Mary Houston USA345345 Sue Seattle USA234234 Mary Houston USACustomersInSeattle345343 Joan Seattle USA234234 Ann Portland USA-- Frank CalgaryCanadaSSN Name City Country345345 Sue Seattle USA345343 Joan Seattle USAgy-- Jean Montreal CanadaSSN Name City CountryFkClCdCustomersInCanada--FrankCalgaryCanada-- Jean Montreal Canada16Horizontal PartitioningCREATE VIEW Customers ASCustomersInHoustonUNION ALLCustomersInSeattleCustomersInSeattleUNION ALL. . .17CSE 444 - Summer 2010Horizontal PartitioningSELECT nameFROM CustomersWHERE city = ‘Seattle’Which tables are inspected by the system ?WHY ???18CSE 444 - Summer 2010Horizontal PartitioningBetter:CREATE VIEW Customers AS(SELECT * FROM CustomersInHoustonBetter:(WHERE city = ‘Houston’)UNION ALL(SELECT*FROMCustomersInSeattle(SELECT FROM CustomersInSeattleWHERE city = ‘Seattle’)UNION ALL. . .Other techniques exist: read DBMS documentation19CSE 444 - Summer 2010qHorizontal PartitioningSELECT nameFROM CustomersWHEREit‘S ttl ’WHEREcity = ‘Seattle’SELECTnameSELECT nameFROM CustomersInSeattle20CSE 444 - Summer 2010Horizontal Partitioning Applications• Performance optimization– Especially for data warehousing– E.g. archived applications and active applications• Distributed and parallel databases• Data integration21CSE 444 - Summer 2010Views and SecurityFredis notNameAddressBalanceCustomers:Fredis notallowed tosee thisNameAddressBalanceMary Houston 450.99SueSeattle-240SueSeattle240Joan Seattle 333.25Ann Portland -520CREATE VIEW PublicCustomersSELECT Name AddressFred isallowed tosee this22SELECT Name, AddressFROM CustomersViews and SecurityCtWilmaisName Address BalanceCustomers:Wilma isnot allowedto see >0balancesMary Huston 450.99Sue Seattle -240JoanSeattle333 25balancesJoanSeattle333.25Ann Portland -520CREATE VIEW BadCreditCustomersSELECT *23FROM CustomersWHERE Balance < 0Types of Constraints in SQLConstraints in SQL:•
View Full Document
Unlocking...