Database Management Systems: Transactions, Indexes, and Data Warehouses, Slides of Introduction to Database Management Systems

Download Database Management Systems: Transactions, Indexes, and Data Warehouses and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems

1

Objectives

• What is the difference between transaction processing

and analysis?

• How do indexes improve performance for retrievals

and joins?

• Is there another way to make query processing more

efficient?

• How is OLAP different from queries?

• How are OLAP databases designed?

• What tools are used to examine OLAP data?

• What tools exist to search for patterns and correlations

in the data?

2

Binary Search

• Given a sorted list of names.
• How do you find Jones.
• Sequential search
  • Jones = 10 lookups
  • Average = 15/2 = 7.5 lookups
  • Min = 1, Max = 14
• Binary search
  • Find midpoint (14 / 2) = 7
  • Jones > Goetz
  • Jones < Kalida
  • Jones > Inez
  • Jones = Jones (4 lookups)
• Max = log 2 (N)
  • N = 1000 Max = 10
  • N = 1,000,000 Max = 20 4

Adams

Brown

Cadiz

Dorfmann

Eaton

Farris

1 Goetz

Hanson

3 Inez

4 Jones

2 Kalida

Lomax

Miranda

Norman

14 entries

Pointers and Indexes

8 ID Index

SQL CREATE INDEX

10

CREATE INDEX ix_Animal_Category_Breed

ON Animal (Category, Breed)

Indexed Sequential Storage

• Common uses
  • Large tables.
  • Need many sequential lists.
  • Some random search--with

one or two key columns.

• Mostly replaced by B+-Tree.

11 Address

• 1 A ID Pointer
• 2 A
• 3 A
• 4 A
• 5 A
• 6 A
• 7 A
• 8 A
• 9 A
• 10 A
  • Carpenter A LastName Pointer
  • Eaton A
  • Farris A
  • Gibson A
  • Hopkins A
  • James A
  • O'Connor A
  • Reasoner A
  • Reeves A
  • Shields A
    • A11 1 Reeves Keith 1/29/ LastName Index
    • A22 2 Gibson Bill 3/31/
    • A32 3 Reasoner Katy 2/17/
    • A42 4 Hopkins Alan 2/8/
    • A47 5 James Leisha 1/6/
    • A58 6 Eaton Anissa 8/23/
    • A63 7 Farris Dustin 3/28/
    • A67 8 Carpenter Carlos 12/29/
    • A78 9 O’Connor Jessica 7/23/
    • A83 10 Shields Howard 7/13/
      • 1 Reeves Keith 1/29/ ID LastName FirstName DateHired
      • 2 Gibson Bill 3/31/
      • 3 Reasoner Katy 2/17/
      • 4 Hopkins Alan 2/8/
      • 5 James Leisha 1/6/
      • 6 Eaton Anissa 8/23/
      • 7 Farris Dustin 3/28/
      • 8 Carpenter Carlos 12/29/
      • 9 O'Connor Jessica 7/23/
      • 10 Shields Howard 7/13/
• 1 A ID Pointer
• 2 A
• 3 A
• 4 A
• 5 A
• 6 A
• 7 A
• 8 A
• 9 A
• 10 A - A - A - A - A - A - A - A - A - A - A
  • Carpenter A LastName Pointer
  • Eaton A
  • Farris A
  • Gibson A
  • Hopkins A
  • James A
  • O'Connor A
  • Reasoner A
  • Reeves A
  • Shields A

Problems with Indexes

• Each index must be updated when rows are

inserted, deleted or modified.

• Changing one row of data in a table with many

indexes can result in considerable time and

resources to update all of the indexes.

• Steps to improve performance

– Index primary keys

– Index common join columns (usually primary keys)

– Index columns that are searched regularly

– Use a performance analyzer

15

Data Warehouse

16

OLTP Database

3NF tables

Operations

data

Predefined

reports

Data warehouse

Star configuration

Daily data

transfer

Interactive

data analysis

Flat files

Extraction, Transformation, and

Loading (ETL)

18

Data warehouse:

All data must be

consistent.

Customers

Convert Client

to Customer

Apply standard

product numbers

Convert

currencies

Fix region codes

Transaction data

from diverse

systems.

OLTP v. OLAP

19

Categor y OL T P OL AP

Dat a st or age 3NF t ables M ult idimensional cubes

I ndexes Few M any

Joins M any M inimal

Duplicat ed dat a Nor malized,

limit ed duplicat ion

Denor malized DBM S

Updates Const ant , small dat a Over night , bulk

Quer ies Specific Ad hoc

Sales Date: Time Hierarchy

21

Year

Quarter

Month

Week

Day

Levels

Roll-up

To get higher-level totals

Drill-down

To get lower-level details

OLAP Computation Issues

Quantity Price QuantityPrice* 3 5.00 15. 2 4.00 8. 5 9.00 45.00 or 23. 22

Compute Quantity*Price in base query, then add to get $23.

If you use Calculated Measure in the Cube, it will add first and

multiply second to get $45.00, which is wrong.

Star Design

24

Sales

Quantity

Amount=SalePrice*Quantity

Fact Table

Products

Customer

Location

Sales Date

Dimension Tables

OLAP Data Browsing

25