1 / 26

Spring 2011 Instructor: Hassan Khosravi

ADVANCED TOPICS IN RELATIONAL DATABASES. Spring 2011 Instructor: Hassan Khosravi. Authorization. Database Authorization. Make sure users see only the data they are supposed to see. Guard the database against modifications by malicious users. Users have privileges

isleen
Download Presentation

Spring 2011 Instructor: Hassan Khosravi

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADVANCED TOPICS IN RELATIONAL DATABASES Spring 2011 Instructor: Hassan Khosravi

  2. Authorization

  3. Database Authorization • Make sure users see only the data they are supposed to see. • Guard the database against modifications by malicious users. • Users have privileges • Can only operate on data for which they are authorized • Select on R • Insert on R • Update on R • Delete on R

  4. Example • Apply(dec), Select(Sid) • Student select(SID,GPA)

  5. How can we give the following privilege • Select students info for Stanford applications only • Delete Berkeley applications only

  6. Obtaining Privileges • Relation creator is owner • Owner has all privileges and may grant privileges • Grant privs on R to users • [with grant option]

  7. Revoking Privileges • Cascade: also revoke privileges granted from privileges being revoked transitively, unless also granted from another source • Restrict: disallow if cascade would revoke any other privileges

  8. ON-LINE ANALYTICAL PROCESSING (OLAP)

  9. Two broad types of database activities • OLTP: Online Transaction Processing • Short transactions • Simple queries • Touch small portion of data • Frequent updates • OLAP: Online Analytical Processing • Long transactions • Complex queries • Touch large portions of data • Infrequent updates

  10. Data warehousing • Bring data from operational OLPT sources into a single warehouse for OLAP analysis • Decision Support System • Data warehouse tuned for OLAP

  11. Star Schema • Fact table (relationships) • Updated frequently, very large • Sales transaction, course enrollment • Dimension tables (entities, objects) • Stores, items, customers • Students, courses

  12. Example

  13. Performance • Inherently very slow: • Special indexes • Extensive use of materialized views

  14. Data Cubes

  15. Multi-dimensional OLAPData Cube • Dimension data form axes of Cube • Fact data in cells Customers Item Stores

  16. Assuming one quantity for that Customers Item Qty and price for I32, C4, s17 Stores

  17. Usually have defined aggregate functions that are meaningful • Sum(qty * price) Customers Item Aggregate over all items for C5, s11 Stores

  18. Usually have defined aggregate functions that are meaningful • Sum(qty * price) Customers Item Aggregate over all stores for I 61, C5, C21 Stores

  19. Usually have defined aggregate functions that are meaningful • Sum(qty * price) Customers Item Aggregate over all customers for I 5, s13 Stores

  20. Usually have defined aggregate functions that are meaningful • Sum(qty * price) Aggregate over all stores and customers for I 52 Customers Aggregate over all stores and Items for c63 Item Aggregate over all customers and Items for s2 Stores

  21. Usually have defined aggregate functions that are meaningful • Sum(qty * price) Customers Item Full aggregation Stores

  22. Example

  23. Drill Down • Add for category to the group by and selection

  24. Roll Up

More Related