Column-store vs row-store in DBMS Theory - Hands-On Comparison

Understanding Column-store vs Row-store Databases

📖 Scenario: You work in a company that manages a database of customer orders. You want to understand how data is stored differently in column-store and row-store databases to decide which is better for your needs.

🎯 Goal: Build a simple representation of data storage in both column-store and row-store formats using dictionaries and lists to see how data is organized differently.

📋 What You'll Learn

Create a dictionary representing a row-store database with customer order data

Create a dictionary representing a column-store database with the same data

Add a variable to hold the number of orders

Write a loop to access and display data from both stores

💡 Why This Matters

🌍 Real World

Understanding how databases store data helps in choosing the right database type for applications like analytics or transaction processing.

💼 Career

Database administrators and developers need to know storage formats to optimize queries and storage efficiency.

1

Create a row-store dictionary

Create a dictionary called row_store with these exact entries representing orders: 1: {'customer': 'Alice', 'product': 'Book', 'quantity': 2}, 2: {'customer': 'Bob', 'product': 'Pen', 'quantity': 5}, 3: {'customer': 'Charlie', 'product': 'Notebook', 'quantity': 3}.

DBMS Theory

# Create the row_store dictionary with order data
# Your code here

Need a hint?

Use a dictionary where each key is the order number and the value is another dictionary with keys 'customer', 'product', and 'quantity'.

2

Create a column-store dictionary

Create a dictionary called column_store with keys 'customer', 'product', and 'quantity'. Each key should map to a list of values in the order of the orders: ['Alice', 'Bob', 'Charlie'], ['Book', 'Pen', 'Notebook'], and [2, 5, 3] respectively.

DBMS Theory

row_store = {
    1: {'customer': 'Alice', 'product': 'Book', 'quantity': 2},
    2: {'customer': 'Bob', 'product': 'Pen', 'quantity': 5},
    3: {'customer': 'Charlie', 'product': 'Notebook', 'quantity': 3}
}
# Create the column_store dictionary with lists of values
# Your code here

Need a hint?

Use a dictionary where each key is a column name and the value is a list of all values for that column.

3

Add a variable for total orders

Create a variable called total_orders and set it to the number of orders in row_store using the len() function.

DBMS Theory

row_store = {
    1: {'customer': 'Alice', 'product': 'Book', 'quantity': 2},
    2: {'customer': 'Bob', 'product': 'Pen', 'quantity': 5},
    3: {'customer': 'Charlie', 'product': 'Notebook', 'quantity': 3}
}

column_store = {
    'customer': ['Alice', 'Bob', 'Charlie'],
    'product': ['Book', 'Pen', 'Notebook'],
    'quantity': [2, 5, 3]
}
# Create total_orders variable with the count of orders
# Your code here

Need a hint?

Use the len() function on row_store to find how many orders there are.

4

Loop through orders and access data

Write a for loop using order_id in range(1, total_orders + 1). Inside the loop, access the customer and product from row_store[order_id] and also access the same data from column_store using order_id - 1 as the index. Assign these to variables row_customer, row_product, col_customer, and col_product respectively.

DBMS Theory

row_store = {
    1: {'customer': 'Alice', 'product': 'Book', 'quantity': 2},
    2: {'customer': 'Bob', 'product': 'Pen', 'quantity': 5},
    3: {'customer': 'Charlie', 'product': 'Notebook', 'quantity': 3}
}

column_store = {
    'customer': ['Alice', 'Bob', 'Charlie'],
    'product': ['Book', 'Pen', 'Notebook'],
    'quantity': [2, 5, 3]
}

total_orders = len(row_store)
# Loop through orders and access customer and product from both stores
# Your code here

Need a hint?

Use a for loop with range(1, total_orders + 1). Access row-store data with row_store[order_id] and column-store data with column_store['customer'][order_id - 1].