Transcript Database Application Design February 25, 2000 Handout #8 (C) 2000, The

February 25, 2000
Database Application Design
Handout #8
(C) 2000, The
University of Michigan
1
Course information
•
•
•
•
•
•
Instructor: Dragomir R. Radev ([email protected])
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
(C) 2000, The
University of Michigan
2
Managing multi-user databases
(cont’d)
(C) 2000, The
University of Michigan
3
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 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 of Michigan
6
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
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
– 2PL: 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 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: 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, and after-images
(C) 2000, The
University of Michigan
12
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 statement:
SELECT *
FROM EMPLOYEE
<% WHERE EMPLOYEE.Name “=SESSION(“EmployeeName”)”%>
(C) 2000, The
University of Michigan
14
Sharing enterprise data
(C) 2000, The
University of Michigan
15
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
+
+
+
(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, The
University of Michigan
18
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 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
)
20
KDD: Data Mining
(C) 2000, The
University of Michigan
21
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
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
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%
- presentation+demo 10%
(C) 2000, The
University of Michigan
27
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