Database Application Design

1
Database Application Design
February 25, 2000

• Handout 8

2
Course information

• Instructor Dragomir R. Radev (radev_at_si.umich.edu)
  
• Office 305A, West Hall
• Phone [phone]
• Office hours Thursdays 3-4 and Fridays 1-2
• Course page http//www.si.umich.edu/radev/654w00
  
• Class meets on Fridays, 230 - 530 PM, 311 WH

3
Managing multi-user databases(contd)
4
Concurrency control

• Lax and strict policies
• Atomic transactions (LUWs logical units of
  work)
• Example customersalesperson
• 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 (contd)

• 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 As 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 Bs 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 dont 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 EMPLOYEElt WHERE EMPLOYEE.Name
SESSION(EmployeeName)gt
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(XY) / 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 codeDocumentationEvaluat
ion, Future workScheduleSustainabilitySnapshots
PresentationDemo
27
Grading

• Project 40- design 10- implementation 10-
  documentation 10- presentationdemo 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