Beyond data modeling

1
(No Transcript)
2
Beyond data modeling

• Model must be normalised purpose ?
• Outcome is a set of tables logical design
• Then, design can be warped until it meets the
  realistic constraints of the system
• Eg what business problem are we trying to solve?
  see handout riccardi p. 113, 127

3
Realistic constraints

• Users cannot cope with too many tables
• Too much development required in hiding complex
  data structure
• Too much administration
• Optimisation is impossible with too many tables
• Actually RDBs can be quite slow!

4
Key practical questions

• What are the most important tasks that the DB
  MUST accomplish efficiently?
• How must the DB be rigged physically to address
  these?
• What coding practices will keep the coding clean
  and simple?
• What additional demands arise from the need for
  resilience and security?

5
Analysis - Three Levels of Schema
External Schema 2
External Schema
External Schema 1
Tables
Logical Schema
Disk Array
Internal Schema
6
4 way trade-off
Security
Ease of use
Performance
Clarity of code
7
Key decisions

• Oracle offers many different ways to do things
• Indexes
• Backups
• Good analysis is not only about knowing these gt
  understanding whether they are appropriate
• Failure to think it through gt unworkable model
• Particularly, predicting performance must be done
  properly
• Ok on the technical side, tricky on the business
  side

8
Design optimisation

• Sources of problems
• Network traffic
• Excess CPU usage
• But physical I/O is greatest threat (different
  from physical I/O)
• Disks still the slowest in the loop
• Solution minimise or re-schedule access
• Also try to minimise the impact of Q4 (e.g.
  mirroring, internal consistency checks)

9
Using scenarios for analysis

• Define standard situation for DB use
• Analyse their specific requirements
• Understand the implications for DB design
• Compare and contrast new problems with old ones

10
Categories of critical operations

• Manual transaction processing complex DE by
  small number of operators
• Automatic transaction processing large number of
  concurrent users performing simple DE
• High batch throughput automatic batch input into
  DB of very large number of complex transactions
• Data warehousing large volumes of new data
  thrown on top every day at fixed intervals
  intensive querying

11
Manual transaction processing

• Insurance telemarketing broker
• Data entry
• Retrieving reference info
• Calculations
• On-line human-computer interaction!!
• Instant validation (field by field)
• Drop-down lists (DE accelerators)
• Quick response time
• Critical issue user-friendly front end, but
  minimise traffic between interface and back end!

12
Automatic transaction processing

• Large number of user performing simple tasks
• Real-time credit card system (e.g. authorisation)
  or check out (EPOS)
• Human interaction at its most simple eg typing
  a code or swiping a card
• Minimum validation, no complex feed back
• Large numbers mean potential problems are
• Connection opening / closing rate
• Contention between concurrent users
• SQL engine pbs data consistency costs
• Design with multiple servers

13
Automatic transaction processing

• Another eg on-line shopping
• What specific problems would arise from shopping
  cart type applications?
• How do you handle lost customers?

14
Internet Databases

• In between types 1 and 2
• Many concurrent sessions
• Reduced interaction front end back end
• Internet Extra response time (2 secs!)
• In practice, many sites are quite slow
• Key issues
• thin client
• Reduced dialogue
• Management of sessions (eg coockies) to avoid
  multiple restarts

15
High batch throughput

• Eg mobile phone network operator
• Real time huge volume of simultaneous complex
  transactions
• Number checks
• Account info
• Price info
• Pattern checks
• Large processing capacity required need to
  tackle all transactions together in batches
• DB query may not be only solution (or quickest)
• Move customer account to cache
• Copy updated figures for accounts to a log and
  updated accounts in slack periods (2.5GB an
  hour!)
• Indexing or partitioning for quicker access

16
Data warehouse

• Huge store of data
• Large volume added every day
• 99 new data, 1 corrections to existing data
• Substantial analysis required prior to
  development
• What to include
• How to aggregate and organise it
• Where data comes from
• Real Oracle territory because schedule is lax
  ie not a real time application
• Key issues
• Getting partitioning right
• Deciding how many summary levels
• Deciding what to hold and what to recalulate

17
Partitioning

• Oldest trick in the book to speed up retrieval
  (eg?)
• Smaller bunch of data
• Well labeled so it can be easily found
• Smaller index
• Data manipulation maintenance, copy and
  protection far easier
• Break down big problem (eg table) into small ones

18
Conclusion Key issues

• At one end very large numbers of small
  transactions
• Threat of network or process contention
• At other end small number of processes with
  complex data crunching and time constraints
• Design of DB and application must reflect these
  constraints

19
I/O Trap

• Reading existing data
• Changing existing data
• Update existing records
• Adding new records
• Deleting records
• All these involve going to disk gt slowest device
  by considerable margin
• Minimise / reschedule physical I/O