SLIDE 1
ADVANCED TOPICS IN RELATIONAL DATABASES
Spring 2011 Instructor: Hassan Khosravi
4.2
ADVANCED TOPICS IN RELATIONAL DATABASES Spring 2011 Instructor: - - PowerPoint PPT Presentation
ADVANCED TOPICS IN RELATIONAL DATABASES Spring 2011 Instructor: - - PowerPoint PPT Presentation
ADVANCED TOPICS IN RELATIONAL DATABASES Spring 2011 Instructor: Hassan Khosravi AUTHORIZATION 4.2 Database Authorization Make sure users see only the data they are supposed to see. Guard the database against modifications by
SLIDE 2
SLIDE 3
4.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 the are authorized Select on R Insert on R Update on R Delete on R
SLIDE 4
4.4
Example
Apply(dec), Select(Sid)
Student select(SID,GPA)
SLIDE 5
4.5
SLIDE 6
4.6
How can we give the following privilege
Select students info for Stanford applications only Delete Berkeley applications only
SLIDE 7
4.7
Obtaining Privileges
Relation creator is owner
Owner has all privileges and may grant privileges
Grant privs on R to users
[with grant option]
SLIDE 8
4.8
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
SLIDE 9
4.9
ON-LINE ANALYTICAL PROCESSING (OLAP)
SLIDE 10
4.10
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
SLIDE 11
4.11
Data warehousing
Bring data from operational OLPT sources into a single warehouse for OLAP analysis
Decision Support System
Data warehouse tuned for OLAP
SLIDE 12
4.12
Star Schema
Fact table (relationships)
Updated frequently, very large
Sales transaction, course enrollment
Dimension tables (entities, objects)
Stores, items, customers Students, courses
SLIDE 13
4.13
Example
SLIDE 14
4.14
Performance
Inherently very slow:
Special indexes Extensive use of materialized views
SLIDE 15
4.15
DATA CUBES
SLIDE 16
4.16
Multi-dimensional OLAP Data Cube
Dimension data form axes of Cube
Fact data in cells
Item Stores Customers
SLIDE 17
4.17
Assuming one quantity for that
Item Stores Customers Qty and price for I32, C4, s17
SLIDE 18
4.18
Usually have defined aggregate functions that are meaningful
Sum(qty * price)
Item Stores Customers Aggregate over all items for C5, s11
SLIDE 19
4.19
Usually have defined aggregate functions that are meaningful
Sum(qty * price)
Item Stores Customers Aggregate over all stores for I 61, C5, C21
SLIDE 20
4.20
Usually have defined aggregate functions that are meaningful
Sum(qty * price)
Item Stores Customers Aggregate over all customers for I 5, s13
SLIDE 21
4.21
Usually have defined aggregate functions that are meaningful
Sum(qty * price)
Item Stores Customers Aggregate over all customers and Items for s2 Aggregate over all stores and Items for c63 Aggregate over all stores and customers for I 52
SLIDE 22
4.22
Usually have defined aggregate functions that are meaningful
Sum(qty * price)
Item Stores Customers Full aggregation
SLIDE 23
4.23
Example
SLIDE 24
4.24
SLIDE 25
4.25
Dill Down
Add for category to the group by and selection
SLIDE 26
4.26