Скачать презентацию February 25 2000 Database Application Design Handout 8 Скачать презентацию February 25 2000 Database Application Design Handout 8

59effecd9f3bd6db50af1f640093132c.ppt

  • Количество слайдов: 28

February 25, 2000 Database Application Design Handout #8 (C) 2000, The University of Michigan February 25, 2000 Database Application Design Handout #8 (C) 2000, The University of Michigan 1

Course information • • • Instructor: Dragomir R. Radev (radev@si. umich. edu) Office: 305 Course information • • • Instructor: Dragomir R. Radev ([email protected] umich. edu) Office: 305 A, West Hall Phone: (734) 615 -5225 Office hours: Thursdays 3 -4 and Fridays 1 -2 Course page: http: //www. si. umich. edu/~radev/654 w 00 Class meets on Fridays, 2: 30 - 5: 30 PM, 311 WH (C) 2000, The University of Michigan 2

Managing multi-user databases (cont’d) (C) 2000, The University of Michigan 3 Managing multi-user databases (cont’d) (C) 2000, The University of Michigan 3

Concurrency control • Lax and strict policies • Atomic transactions (LUWs = logical units Concurrency control • Lax and strict policies • Atomic transactions (LUWs = logical units of work) – Example: customer+salesperson • Concurrent transaction processing: interlocking • Lost update problem (C) 2000, The University of Michigan 4

Example • User A: – Read item 100 – Reduce by 5 – Write Example • User A: – Read item 100 – Reduce by 5 – Write item 100 (C) 2000, The University of Michigan • User B: – Read item 200 – Reduce by 3 – Write item 200 5

Resource locking • Locks: implicit, explicit • Example: two users (C) 2000, The University Resource locking • Locks: implicit, explicit • Example: two users (C) 2000, The University of Michigan 6

Example • User A: – Lock item 100 – Read item 100 – Reduce Example • User A: – Lock item 100 – Read item 100 – Reduce by 5 – Write item 100 (C) 2000, The University of Michigan • User B: – Lock item 100 – Read item 100 – Reduce by 3 – Write item 100 7

Example (cont’d) 1. Lock item 100 for A 2. Read item 100 for A Example (cont’d) 1. Lock item 100 for A 2. Read item 100 for A 3. Lock item 100 for B; cannot 4. Decrease 100 by 5 5. Write item 100 for A 6. Release A’s lock on 100 7. Lock item 100 for B 8. Read item 100 for B 9. Decrease item 100 by 3 10. Write 100 for B 11. Release B’s lock on 100 (C) 2000, The University of Michigan 8

Resource locking • Serizalizable transaction – 2 PL: growing phase, followed by a shrinking Resource locking • Serizalizable transaction – 2 PL: growing phase, followed by a shrinking phase • COMMIT and ROLLBACK • DEADLOCKS (C) 2000, The University of Michigan 9

Transaction isolation levels • Exclusive use • Repeatable read: mix of shared and exclusive Transaction isolation levels • Exclusive use • Repeatable read: mix of shared and exclusive locks • Dirty read: for reports which don’t necessarily need to contain the latest data (C) 2000, The University of Michigan 10

Cursor types • Forward only: changes made to earlier records are hidden • Static: Cursor types • Forward only: changes made to earlier records are hidden • Static: any changes are hidden • Dynamic: all changes are visible (C) 2000, The University of Michigan 11

Database recovery • Reprocessing: uses database saves • Rollback/Rollforward : uses transaction logs, before-images, Database recovery • Reprocessing: uses database saves • Rollback/Rollforward : uses transaction logs, before-images, and after-images (C) 2000, The University of Michigan 12

Database security • Users, groups, permissions, objects • Permissions: – CONNECT: ALTER SESSION, CREATE Database security • Users, groups, permissions, objects • Permissions: – CONNECT: ALTER SESSION, CREATE TABLE, CREATE VIEW (C) 2000, The University of Michigan 13

Application security • Usually done on the Web server • ASP script modifies SQL Application security • Usually done on the Web server • ASP script modifies SQL statement: SELECT * FROM EMPLOYEE <% WHERE EMPLOYEE. Name “=SESSION(“Employee. Name”)”%> (C) 2000, The University of Michigan 14

Sharing enterprise data (C) 2000, The University of Michigan 15 Sharing enterprise data (C) 2000, The University of Michigan 15

Enterprise DB architectures • • Teleprocessing systems Client-server systems File-sharing systems Distributed database systems: Enterprise DB architectures • • Teleprocessing systems Client-server systems File-sharing systems Distributed database systems: vertical and horizontal fragmentation (C) 2000, The University of Michigan 16

Comparing distributed DB architectures Unified database Single Nonpartitioned Nonreplicated Distributed databases Partitioned Nonreplicated + Comparing distributed DB architectures Unified database Single Nonpartitioned Nonreplicated Distributed databases Partitioned Nonreplicated + + + (C) 2000, The University of Michigan Increased parallelism Increased independence Increased flexibility Increased availability Increased cost/complexity Increased difficulty of control Increased security risk Nonpartitioned Replicated Partitioned Replicated + + + + 17

Problems in downloaded databases • • Coordination Consistency Access control Computer crime (C) 2000, Problems in downloaded databases • • Coordination Consistency Access control Computer crime (C) 2000, The University of Michigan 18

On Line Analytic Processing (OLAP) • Hypercubes, axes, dimensions, slices • Values of a On Line Analytic Processing (OLAP) • Hypercubes, axes, dimensions, slices • Values of a dimension are called members • Levels: hierarchical organization: e. g. , date, month, year • CROSSJOIN ({Existing Structure, New Construction}, {California. Children, Nevada}) (C) 2000, The University of Michigan 19

OLAP SQL CREATE CUBE Housing. Sales. Cube ( DIMENSION Time TYPE TIME, LEVEL Year OLAP SQL CREATE CUBE Housing. Sales. Cube ( DIMENSION Time TYPE TIME, LEVEL Year TYPE YEAR, LEVEL Quarter TYPE QUARTER, LEVEL Month TYPE MONTH, DIMENSION Location, LEVEL USA TYPE ALL, LEVEL State, LEVEL City, DIMENSION Housing. Category, DIMENSION Housing. Type, MEASURE Sales. Price, FUNCTION AVG, MEASURE Asking. Price, FUNCTION AVG ) 20

KDD: Data Mining (C) 2000, The University of Michigan 21 KDD: Data Mining (C) 2000, The University of Michigan 21

Association rules • X Y • 65% of all customers who buy beer and Association rules • X Y • 65% of all customers who buy beer and tomato sauce also buy pasta and chicken wings • Support (X) • Confidence (X Y) = Support(X+Y) / Support (X) (C) 2000, The University of Michigan 22

Object-oriented data processing (C) 2000, The University of Michigan 23 Object-oriented data processing (C) 2000, The University of Michigan 23

Introduction • OOP objects: encapsulated structures with attributes and methods • Interface + implementation Introduction • OOP objects: encapsulated structures with attributes and methods • Interface + implementation • Inheritance • Polymorphism • Transient and persistent objects (C) 2000, The University of Michigan 24

Final project guidelines (C) 2000, The University of Michigan 25 Final project guidelines (C) 2000, The University of Michigan 25

Checklist Introduction User interviews/needs: table, reports, queries, forms Initial data model ER model Decomposition Checklist Introduction User interviews/needs: table, reports, queries, forms Initial data model ER model Decomposition SQL code Documentation Evaluation, Future work Schedule Sustainability Snapshots Presentation Demo 26

Grading • Project: 40% - design 10% - implementation 10% - documentation 10% - Grading • Project: 40% - design 10% - implementation 10% - documentation 10% - presentation+demo 10% (C) 2000, The University of Michigan 27

Readings for next time • Kroenke – Chapter 14: Sharing Enterprise Data – Chapter Readings for next time • Kroenke – Chapter 14: Sharing Enterprise Data – Chapter 17: Object-Oriented Database Processing • YRK (optional) – Chapter 14: Java and JDBC (C) 2000, The University of Michigan 28