Database Operations - Database Management Systems - Lecture Slides, Slides of Introduction to Database Management Systems

Download Database Operations - Database Management Systems - Lecture Slides and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems

1

Objectives

• How does a DBMS store data for efficient retrieval?

• How does a DBMS interact with the file system?

• What are the common database operations?

• What options does a DBMS have for storing tables?

• How is one data row stored?

• How can you improve performance by specifying

where data is stored?

• How does a DBA control file storage?

• What performance issues might arise at Sally’s Pet

Store?

2

Physical Data Storage

• Some database systems let the

designer choose how to store data.

• Rows for each table.
• Columns within a table.
• The choice influences

performance and storage

requirements.

• The choice depends on the

characteristics of the data being

stored.

• Index
  • Most database systems use an

index to improve performance.

• Several methods can be used to store an index.
• An index can speed data

retrieval.

• Maintaining many indexes on a

table can significantly slow down

data updates and additions.

• Choose indexes carefully to

speed up certain large jobs.

4

Table Operations

• Retrieve data
  • Read entire table.
  • Read next row/sequential.
  • Read arbitrary/random row.
• Store data
  • Insert a row.
  • Delete a row.
  • Modify a row.
• Reorganize/pack database
  • Remove deleted rows.
  • Recover unused space.

5

LastName FirstName Phone Adams Kimberly (406) 987- Adkins Inga (706) 977- Allbright Searoba (619) 281- Anderson Charlotte (701) 384- Baez Bessie (606) 661- Baez Lou Ann (502) 029- Bailey Gayle (360) 649- Bell Luther (717) 244- Carter Phillip (219) 263- Cartwright Glen (502) 595- Carver Bernice (804) 020- Craig Melinda (502) 691-

Data Storage Methods

• Sequential

• Fast for reading entire table.
• Slow for random search.

• Indexed Sequential (ISAM)

• Better for searches.
• Slow to build indexes.

• B +-Tree

• Similar to ISAM.
• Efficient at building indexes.

• Direct / Hashed

• Extremely fast searches.
• Slow sequential lists.

7

Sequential Storage

• Common uses
  • When large portions of the data

are always used at one time.

e.g., 25%

• When table is huge and space is

expensive.

• When transporting / converting

data to a different system.

8

ID LastName FirstName DateHired

Reeves

Gibson

Reasoner

Hopkins

James

Eaton

Farris

Carpenter

O’Connor

Shields

Keith

Bill

Katy

Alan

Leisha

Anissa

Dustin

Carlos

Jessica

Howard

Insert into Sequential Table

• Insert Inez:
  • Find insert location.
  • Copy top to new file.
  • At insert location, add row.
  • Copy rest of file.

10

ID LastName FirstName DateHired 8 6 7 2

Carpenter Eaton Farris Gibson

Carlos Anissa Dustin Bill

12/29/ 8/23/ 3/28/ 3/31/ 4 5 9 3 1 10

Hopkins James O’Connor Reasoner Reeves Shields

Alan Leisha Jessica Katy Keith Howard

2/8/ 1/6/ 7/23/ 2/17/ 1/29/ 7/13/ ID LastName FirstName DateHired 8 6 7 2

Carpenter Eaton Farris Gibson

Carlos Anissa Dustin Bill

12/29/ 8/23/ 3/28/ 3/31/ 11 Inez Maria 1/15/ 4 5 9 3 1 10

Hopkins James O’Connor Reasoner Reeves Shields

Alan Leisha Jessica Katy Keith Howard

2/8/ 1/6/ 7/23/ 2/17/ 1/29/ 7/13/

Pointers

• When data is stored on drive (or RAM).

• Operating System allocates space with a function call.
• Provides location/address.
  • Physical address
  • Virtual address (VSAM)
    • Imaginary drive values mapped to physical locations.
  • Relative address
    • Distance from start of file.
    • Other reference point.

11

Data

Address

Key value Address / pointer

Volume

Track

Cylinder/Sector

Byte Offset

Drive Head

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.

13 Address

 - 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

Linked List

• Separate each element/key.
• Pointers to next element.
• Pointers to data.
• Starting point.

14

B87 (^) Carpenter B29 A

B38 (^) Gibson 00 A B29 (^) Eaton B71 A

B71 (^) Farris B38 A

A63 7 Farris Dustin 3/28/

A67 8 Carpenter Carlos 12/29/

A58 6 Eaton Anissa 8/23/

A22 2 Gibson Bill 3/31/

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

16

Adams

Brown

Cadiz

Dorfmann

Eaton

Farris

1 Goetz

Hanson

3 Inez

4 Jones

2 Kalida

Lomax

Miranda

Norman

14 entries

B-Tree

• Store key values
• Utilize binary search (or

better).

• Trees
  • Nodes
  • Root
  • Leaf (node with no children)
  • Levels / depth
  • Degree (maximum number of

children per node)

17

Hanson

Dorfmann Kalida

Brown Farriis Inez Miranda

Adams Cadiz Eaton Goetz Jones Lomax Norman

A B C D E F G H I J K L M N

Inez

< Key^ Data >=

B

-Tree Example

• Degree 3
  • At least m/2 = 1.5 (=2) children.
  • No more than 3 children.
• Search keys (e.g., find 692)
  • Less than
  • Between
  • Greater than
• Sequential links.

19

data

B

-Tree Insert

• Insert 257
  • Find location.
  • Easy with extra space.
  • Just add element.

20