Download Utilities and Queries - Lecture Notes | EECS 484 and more Study notes Database Management Systems (DBMS) in PDF only on Docsity! Minirel Part-2
Utilities and Queries
..And then there was one.
EECS 484
Winter 09
Design the whole System!
* Create tables
¢ Insert records into tables
* Query the tables
This time, we give you the Buffer Manager!
... sort of
You have...
Sort
Hash Indexing
Buffer Manager
Relation and Attribute Catalog definitions
Catalogs
Meta data
i.e. info regarding attributes and relations
How would they be stored??
Relations!
Classes for Catalogs • RelDesc: attributes, indexes on a relation typedef struct { char relName[MAXNAME]; // relation name int attrCnt; // number of attributes int indexCnt; // number of indexed attrs } RelDesc; class RelCatalog : public HeapFileScan { public: // get relation descriptor for a relation const Status getInfo(const string & rName, RelDesc& record); // add information to catalog const Status addInfo(RelDesc & record); … INSERT INTO RELNAME(ATTRNAMELIST) VALUES (VALUELIST) • const Status Updates::Insert(const string & relation, const int attrCnt, const attrInfo attrList[] ) typedef struct { char relName[MAXNAME]; // relation name char attrName[MAXNAME]; // attribute name int attrType; // INTEGER, FLOAT, or STRING int attrLen; // length of attribute in bytes void *attrValue; // ptr to binary value (used // by the parser for insert into // statements) } attrInfo; INSERT INTO RELNAME(ATTRNAMELIST) VALUES (VALUELIST) • const Status Updates::Insert(const string & relation, const int attrCnt, const attrInfo attrList[] ) typedef struct { char relName[MAXNAME]; // relation name char attrName[MAXNAME]; // attribute name int attrType; // INTEGER, FLOAT, or STRING int attrLen; // length of attribute in bytes void *attrValue; // ptr to binary value (used // by the parser for insert into // statements) } attrInfo; class HeapFile { protected: File* file; HeaderPage* headerPage; int headerPageNo; public: // initialize HeapFile(const string & name, Status& returnStatus); // insert record into file const Status insertRecord(const Record & rec, RID& outRid); Join Queries
¢ The “WHERE rel1.attr1 = rel2.attr2” kind
* 3 join algorithms
¢ Index on either join attribute: Index Nested Loop
¢ Not equi-join: Simple Nested Loop
¢ Equi-join, neither indexed: Sort Merge
Sort Merge
k = 80% of available buffers
n = number of tuples that can fit on the k buffers
Use Heapfilescan, storing n tuples at a time
Sort tuples, store in temporary files [runs]
Merge all temporary files, upto k Heapfilescans
Issues
— Duplicates [back and forth]
— Result stored in result
Bootstrapping Catalogs
attrcat and relcat are relations
You must add data for themselves into the
tables
2 records into relcat
A record for each attribute of the relations
into attrcat [9=3+6 |
