Mar 28: Storage, GP2 Scripts

Learning Objectives

After today's class, you should be able to:

• Summarize how tables and indexes are physically stored.
• Explain the contents of each script file required for GP2.

Lesson Outline

Data Storage [20 min]

Highlights from Chapter 5

• 5.1 Storage media

  • Data stored on disks, read in as blocks
  • Blocks are usually row-oriented
  • Column-oriented is sometimes faster

• 5.2 Table structures

  • Tables rows are usually stored unordered
  • Tables can be sorted, hashed, or clustered

• 5.3 Single-level indexes

  • Index file store values and pointers
  • Selectivity – whether to use an index

• 5.4 Multi-level indexes

  • Large indexes stored in a tree structure
  • B+trees are a balanced multi-level index

• 5.5 Other indexes

  • Hash indexes good for equality queries
  • Bitmap indexes for few distinct values
  • Logical indexes store primary key values
  • Function indexes store function results

• 5.6 Tablespaces and partitions

  • Different tables can be stored on different disks
  • The same table can be stored using multiple disks

• 5.7 Physical design

  • Physical design is independent of local design
  • CREATE INDEX and EXPLAIN statements
  • Physical design process (query profiling)

Lecture / Demo [35 min]

• GP2 Walkthrough: ER Diagram to SQL Code

Working Time [20 min]

• Create stubs for each of the GP2 files
  • Each group member create one file
  • git add and git commit the file
  • Make sure everyone can git pull

Your To-Do List

• Finish reading zyBook sections 5.1 – 5.7
• Finish working on GP2 (due Monday)