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 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 4.3

Example  Apply(dec), Select(Sid)  Student select(SID,GPA) 4.4

4.5

 How can we give the following privilege  Select students info for Stanford applications only  Delete Berkeley applications only 4.6

Obtaining Privileges  Relation creator is owner  Owner has all privileges and may grant privileges  Grant privs on R to users  [with grant option] 4.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 4.8

ON-LINE ANALYTICAL PROCESSING (OLAP) 4.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 4.10

Data warehousing  Bring data from operational OLPT sources into a single warehouse for OLAP analysis  Decision Support System  Data warehouse tuned for OLAP 4.11

Star Schema  Fact table (relationships)  Updated frequently, very large  Sales transaction, course enrollment  Dimension tables (entities, objects)  Stores, items, customers  Students, courses 4.12

Example 4.13

 Performance  Inherently very slow:  Special indexes  Extensive use of materialized views 4.14

DATA CUBES 4.15

Multi-dimensional OLAP Data Cube  Dimension data form axes of Cube  Fact data in cells Customers Item Stores 4.16

Customers Item Qty and price for I32, C4, s17 Stores  Assuming one quantity for that 4.17

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

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

 Usually have defined aggregate functions that are meaningful  Sum(qty * price) Customers Item Aggregate over all customers for I 5, s13 Stores 4.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 4.21

 Usually have defined aggregate functions that are meaningful  Sum(qty * price) Customers Item Full aggregation Stores 4.22

Example 4.23

4.24

Dill Down  Add for category to the group by and selection 4.25

Roll Up 4.26