Building a Mini Relational Database System in C++

1. Paged File Manager (PFM): The base layer. It abstracts how files and pages are handled on disk.
2. Record-Based File Manager (RBFM): The middle layer. It deals with inserting, updating, and deleting variable-length records inside pages.
3. Relation Manager (RM): The icing on the cake. This is the interface users see—it handles tables, tuples, and catalogs.

In your assignment, you’re tasked with fleshing out the RM layer and linking it to your existing RBFM layer. It’s like building an API over a file system and ensuring it behaves like a relational database.

Meet the Catalog: Your Database's Brain

The catalog is the internal memory of your database. It stores metadata about every table and every column. And you’re expected to:

• Create and persist two system tables: Tables and Columns.
• Store schema information.
• Prevent direct user modifications to system tables.

Real-world analogy? The catalog is like your DBMS’s address book. Without it, your database doesn’t know who’s who or what’s what.

Memory Discipline and Statelessness

This is where things get serious.

Most DBMS assignments enforce a strict no-caching policy:

• You’re not allowed to load all records into memory.
• Every operation (insert/update/delete) must be disk-persistent.
• Use system memory sparingly—only a page or two at a time.

Why? Because this models how real-world databases scale for massive data volumes.

Pro Tip: Build your API so that it opens a file, performs an operation, and closes it. Don’t hold file handles open longer than necessary unless explicitly required.

Catalog Engineering: Your Schema, Your Rules

Here’s the fun part—designing your catalog. Think of it as defining the blueprint for every other table your system will support.

createCatalog(): The Genesis Function

This method is your system's birth moment. It creates:

• A Tables file to hold metadata about all tables.
• A Columns file to hold metadata about columns in those tables.

Your first records will actually describe the Tables and Columns tables themselves. Yes, the catalog describes itself—how very meta.

Example Entry:

• Tables: (1, "Tables", "Tables")
• Columns: (1, "table-id", TypeInt, 4, 1), ...

Be sure to use your RBFM’s insertRecord() to insert these records. Avoid hardcoding writes to disk.

deleteCatalog(): Clean-Up with Precision

This function should:

• Delete Tables and Columns files from disk.
• Remove all their metadata entries.

Make sure to:

• Return an error if the catalog doesn’t exist.
• Avoid accidentally deleting user tables during cleanup.

getAttributes(): Dynamic Schema Fetching

Before inserting or querying a table, your system must know what its schema looks like. This function:

• Reads the Columns catalog.
• Filters entries by table-id.
• Constructs a vector of Attribute objects.

Watch Out: Don’t assume the order of column insertion. Respect the column-position field to build your schema correctly.

Mastering Table and Tuple Operations

createTable() and deleteTable()

These functions handle user tables.

• createTable():
  • Generate a unique table ID.
  • Write to Tables and Columns.
  • Create a new file via RBFM.
• deleteTable():
  • Remove entries from the catalog.
  • Delete the corresponding file.

Pro Insight: Consider adding a system_flag to your Tables schema to prevent users from deleting system tables.

insertTuple() and Data Encoding

Here’s where memory formatting matters.

In C++, you’ll need to:

• Encode null indicators.
• Serialize attributes into a byte buffer.
• Call RBFM’s insertRecord() with the schema and buffer.

Practical Tip: Always allocate your buffer as:

int bufferSize = calculateMaxSize(schema); void* data = malloc(bufferSize);

Encode null bits first, then attribute values. Don’t mix up order or padding, or your records will corrupt.

deleteTuple() and updateTuple()

Things get tricky here:

• After deletion, compact the page so free space stays centralized.
• After an update, if the record grows:
  • Move it to a new page.
  • Leave a tombstone at the old location.

Critical Rule: You must not change a record’s RID, even after updates. Many students lose marks here because indexes or scans rely on consistent RIDs.

Scan Iterators: Reading Like a Pro

No database is complete without the ability to scan and filter records. That’s where iterators come in.

scan() and RM_ScanIterator

Your job here is to:

• Expose a high-level scan function.
• Internally rely on RBFM’s iterator.

This lets users:

• Filter based on a condition attribute.
• Project only specific columns.

Usage Pattern:

Never store all tuples in memory. Always fetch one tuple at a time on demand.

close(): Resource Management 101

Always provide a close() function in your iterator:

• Release internal state.
• Reset file handles and buffers.

Database performance hinges on proper resource cleanup.

Going Beyond: Schema Evolution & Advanced Features

If your assignment includes extra credit or bonus challenges, take them. They teach advanced DBMS behaviors and make your submission stand out.

addAttribute()

Adding a new attribute to an existing table means:

• Updating the Columns catalog.
• Marking the new field as NULL in all pre-existing records.

Do not modify existing data files. This is called lazy schema evolution—used in systems like Apache Hive and MongoDB.

dropAttribute()

Dropping is trickier. You:

• Remove the column entry from the catalog.
• Adjust record reading logic to ignore the dropped field.

The actual data remains on disk but is hidden from view. This avoids reshuffling files, preserving performance.

Design Consideration: Store a schema version in each record or file. Your read logic can then adapt based on versioning.

Pro Tips: Field-Tested Tricks for Success

These insights are from real student experiences:

Validate Every Layer

Before you test the full RM pipeline:

• Validate PFM reads/writes.
• Validate RBFM record structure.
• Only then build and test RM.

Debug with printTuple()

Use this often:

printTuple(schema, data, std::cout);

It saves hours of confusion when records silently break.

Reuse Common Utilities

• prepareRecord()
• parseRecord()
• buildNullIndicator()

Keep your code DRY (Don’t Repeat Yourself). Many functions overlap across insert/update/read.

Conclusion: More Than Just an Assignment

This kind of project is far more than just another grade. It’s a miniature exploration into the world of database internals. You’ll walk away with:

• A better understanding of file formats.
• Confidence managing memory and records at a byte level.
• The ability to design extensible data systems.

So the next time you launch MySQL or PostgreSQL, you’ll understand what’s happening under the hood.

If you’ve got this assignment on your plate, take it seriously. It's your gateway into advanced systems thinking—and a golden opportunity to build something you’ll be proud to put on your resume.