CS346 Lecture Notes
Review of Metadata Management,
Overview of SM and EX Component

Example relation: Student(ID, name, major, GPA)

Example query: 

   Select ID, name, major
   From   Student
   Where  GPA < 3.0

Query validity

System must check:

Query plan 

   1. open file for Student           // what is file name?
   2. scan records                    // index on GPA?
         if GPA < 3.0                 // where is GPA in record?
         then fetch ID, name, major   // where are ID, name, major in record?
   3. close file for Student

Metadata

All information about the data needed to process queries and updates over it

Metadata must be persistent, reliable, multi-user, etc., exactly like regular data
=> Store in database

Question: Is it a paradox to store (meta-)data about the data as data in the database?
Question: Can we use metadata to describe itself?
Terminology:

   metadata
   ~= data dictionary
   ~= schema information
   ~= system catalogs
   ~= system relations

Some of these terms are narrower -- "metadata" usually includes statistics, indexes, etc.


System structure of a real DBMS (simplified)
  • Query compiler touches metadata, no data
  • Query executor touches data, no metadata

    RedBase SM component

    RedBase metadata

    Two catalogs: relcat and attrcat (more for certain extensions)

  • relcat: one tuple per relation (see SM document for details)
  • attrcat: one tuple per attribute per relation (see SM document for details)
    Question: Can you store tuples for relcat and attrcat in relcat and attrcat?
    Question: Is it useful to put indexes on catalogs?

    Command-line utilities

  • Stand-alone programs
    1. dbcreate <DBname> (can have multiple databases)
      • create subdirectory
      • create initial relcat & attrcat for this database

    2. dbdestroy <DBname>
      • delete subdirectory

    3. redbase <DBname>
      • initialize system: create XX_Managers
      • call SM_Manager::OpenDb (cd DBname, open relcat & attrcat)
      • call RBparse -- command loop
      • call SM_Manager::CloseDb (close relcat & attrcat)

    DDL commands

  • Similar to SQL syntax
  • One SM_Manager method per command
    1. create table
      • update system catalogs
      • create file for relation

    2. drop table
      • destroy relation file
      • destroy index files
      • delete info from system catalogs

    3. create index
      • update system catalog
      • create index
      • build index for current relation contents

    4. drop index
      • update system catalog
      • destroy index

    System utilities

    1. load

      File format: 

      A1, A2, ..., An \n
A1, A2, ..., An \n
      with attributes in same order as Create Table

      for each tuple:

      • create record in file
      • create index entries

    2. help [<relname>]

      Without <relname> -- print list of all relation names (Question: How?)

      With <relname> -- print attribute info for relation (Question: How?)

    3. print <relname>

      Scan relation, print all tuples

    4. set <param> = <value>

      For your convenience only, for example:
      set tracing = "0"
      set result = "verbose"

    Read document carefully, lots of things not covered:


    EX Component