1 / 28

Database Application Design

February 25, 2000. Database Application Design. Handout #8. Course information. Instructor: Dragomir R. Radev (radev@si.umich.edu) Office: 305A, West Hall Phone: (734) 615-5225 Office hours: Thursdays 3-4 and Fridays 1-2 Course page: http://www.si.umich.edu/~radev/654w00

munin
Download Presentation

Database Application Design

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. February 25, 2000 Database Application Design Handout #8

  2. Course information • Instructor: Dragomir R. Radev (radev@si.umich.edu) • Office: 305A, West Hall • Phone: (734) 615-5225 • Office hours: Thursdays 3-4 and Fridays 1-2 • Course page: http://www.si.umich.edu/~radev/654w00 • Class meets on Fridays, 2:30 - 5:30 PM, 311 WH

  3. Managing multi-user databases(cont’d)

  4. Concurrency control • Lax and strict policies • Atomic transactions (LUWs = logical units of work) • Example: customer+salesperson • Concurrent transaction processing: interlocking • Lost update problem

  5. Example • User B: • Read item 200 • Reduce by 3 • Write item 200 • User A: • Read item 100 • Reduce by 5 • Write item 100

  6. Resource locking • Locks: implicit, explicit • Example: two users

  7. Example • User B: • Lock item 100 • Read item 100 • Reduce by 3 • Write item 100 • User A: • Lock item 100 • Read item 100 • Reduce by 5 • Write item 100

  8. 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

  9. Resource locking • Serizalizable transaction • 2PL: growing phase, followed by a shrinking phase • COMMIT and ROLLBACK • DEADLOCKS

  10. 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

  11. Cursor types • Forward only: changes made to earlier records are hidden • Static: any changes are hidden • Dynamic: all changes are visible

  12. Database recovery • Reprocessing: uses database saves • Rollback/Rollforward : uses transaction logs, before-images, and after-images

  13. Database security • Users, groups, permissions, objects • Permissions: • CONNECT: ALTER SESSION, CREATE TABLE, CREATE VIEW

  14. Application security • Usually done on the Web server • ASP script modifies SQL statement: • SELECT *FROM EMPLOYEE<% WHERE EMPLOYEE.Name “=SESSION(“EmployeeName”)”%>

  15. Sharing enterprise data

  16. Enterprise DB architectures • Teleprocessing systems • Client-server systems • File-sharing systems • Distributed database systems: vertical and horizontal fragmentation

  17. Comparing distributed DB architectures Unified database Distributed databases Single Nonpartitioned Nonreplicated Partitioned Nonreplicated Nonpartitioned Replicated Partitioned Replicated Increased parallelism - + Increased independence - + Increased flexibility - + Increased availability - + Increased cost/complexity + + Increased difficulty of control + + Increased security risk + +

  18. Problems in downloaded databases • Coordination • Consistency • Access control • Computer crime

  19. 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})

  20. OLAP SQL CREATE CUBE HousingSalesCube ( 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 HousingCategory, DIMENSION HousingType, MEASURE SalesPrice, FUNCTION AVG, MEASURE AskingPrice, FUNCTION AVG)

  21. KDD: Data Mining

  22. 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)

  23. Object-oriented data processing

  24. Introduction • OOP objects: encapsulated structures with attributes and methods • Interface + implementation • Inheritance • Polymorphism • Transient and persistent objects

  25. Final project guidelines

  26. Checklist IntroductionUser interviews/needs: table, reports, queries, formsInitial data modelER modelDecompositionSQL codeDocumentationEvaluation, Future workScheduleSustainabilitySnapshotsPresentationDemo

  27. Grading • Project: 40%- design 10%- implementation 10%- documentation 10%- presentation+demo 10%

  28. Readings for next time • Kroenke • Chapter 14: Sharing Enterprise Data • Chapter 17: Object-Oriented Database Processing • YRK (optional) • Chapter 14: Java and JDBC

More Related