← Back to Past Papers

VNSGU BCA Sem 3: Database Handling (303) Practical Solutions - April 2025 Set B

Paper Information

Attribute	Value
Subject	Database Handling Using Python
Subject Code	303
Set	B
Semester	3
Month/Year	April 2025
Max Marks	25
Paper	View Paper | Download PDF

---

Questions & Solutions

Q1: Inventory Constraints & CSV Operations

Max Marks: 20

Create "Inventory" table in SQLite3 with appropriate constraints. Inventory(item_id, item_name, category, qty, unit_price) Perform below operations: A. Insert 10 records with appropriate category. B. Export table data to csv file & Load CSV file in Python. C. Find Total Inventory Value.

---

1. Concept Explanation

What are Database Constraints?

Constraints are rules enforced on data columns in a table. They are used to limit the type of data that can go into a table. Common SQLite constraints include:

• PRIMARY KEY: Uniquely identifies each record in the table.
• NOT NULL: Ensures that a column cannot have a NULL value.
• CHECK: Ensures that all values in a column satisfy a specific condition (e.g. quantity must be non-negative).

Exporting and Loading CSV Files

In standard data workflows, exporting database tables to Comma-Separated Values (CSV) files is a common technique for data portability. Pandas makes this seamless:

• df.to_csv('filename.csv', index=False) writes a DataFrame directly to disk as a CSV.
• pd.read_csv('filename.csv') loads a CSV file back into a structured DataFrame.

---

2. Algorithm & Step-by-Step Logic

1. Setup Database Connection: Connect to Inventory.db.
2. Create Table with Constraints:
   • item_id as INTEGER PRIMARY KEY.
   • item_name and category as TEXT NOT NULL.
   • qty as INTEGER CHECK(qty >= 0).
   • unit_price as REAL CHECK(unit_price > 0.0).
3. Populate Table: Insert 10 records of varying categories (e.g., Electronics, Stationery, Furniture) into the table.
4. Fetch & Export to CSV:
   • Use Pandas read_sql_query() to read all data from the database.
   • Write the data to inventory_export.csv using to_csv().
5. Load CSV in Python: Read the exported CSV file back into a new DataFrame using read_csv().
6. Calculate Total Inventory Value:
   • Multiply the quantity and unit price columns element-wise: qty * unit_price.
   • Sum the resulting Series to find the cumulative inventory valuation.
7. Display Output: Print results to the console.

---

3. Implementation Code & Output

View Solution & Output

# 📦 VNSGU BCA Sem 3 - Database Handling Using Python (Practical 303)
# 🚀 Action: Create SQLite inventory table with constraints, export to CSV, load & analyze.

import sqlite3
import pandas as pd
import os

def create_and_populate_db():
    # 1. Establish SQLite connection
    conn = sqlite3.connect("Inventory.db")
    cursor = conn.cursor()
    
    # Refresh table on subsequent runs
    cursor.execute("DROP TABLE IF EXISTS Inventory;")
    
    # 2. Create the Inventory table with constraints
    cursor.execute("""
    CREATE TABLE Inventory (
        item_id INTEGER PRIMARY KEY AUTOINCREMENT,
        item_name TEXT NOT NULL,
        category TEXT NOT NULL,
        qty INTEGER NOT NULL CHECK(qty >= 0),
        unit_price REAL NOT NULL CHECK(unit_price > 0)
    );
    """)
    print("✓ Inventory table created in SQLite database with CHECK constraints.")
    
    # 3. List of 10 records with categories
    items = [
        ("Laptop", "Electronics", 15, 45000.00),
        ("Smartphone", "Electronics", 30, 15000.00),
        ("Office Chair", "Furniture", 25, 4500.00),
        ("Study Desk", "Furniture", 10, 8000.00),
        ("Ballpoint Pen Pack", "Stationery", 120, 150.00),
        ("Notebook", "Stationery", 200, 80.00),
        ("Wireless Mouse", "Electronics", 50, 1200.00),
        ("Keyboards", "Electronics", 40, 1500.00),
        ("File Cabinet", "Furniture", 8, 12000.00),
        ("Whiteboard Markers", "Stationery", 80, 50.00)
    ]
    
    # Insert values
    cursor.executemany("""
    INSERT INTO Inventory (item_name, category, qty, unit_price)
    VALUES (?, ?, ?, ?);
    """, items)
    
    conn.commit()
    cursor.close()
    conn.close()
    print("✓ 10 records successfully inserted.\n")

def export_and_calculate():
    # 4. Read from SQLite database
    conn = sqlite3.connect("Inventory.db")
    df_db = pd.read_sql_query("SELECT * FROM Inventory", conn)
    conn.close()
    
    # Export DataFrame to CSV
    csv_filename = "inventory_export.csv"
    df_db.to_csv(csv_filename, index=False)
    print(f"✓ Exported database data to {csv_filename}.")
    
    # 5. Load CSV file back in Python
    if os.path.exists(csv_filename):
        df_csv = pd.read_csv(csv_filename)
        print("✓ Loaded CSV file back into Pandas DataFrame.\n")
    else:
        print("❌ Error: CSV file not found.")
        return
        
    print("--- DATA LOADED FROM CSV ---")
    print(df_csv.to_string(index=False))
    print("-" * 45 + "\n")
    
    # 6. Find Total Inventory Value
    # Formula: Value = Sum of (Quantity * Unit Price) for all rows
    df_csv['item_value'] = df_csv['qty'] * df_csv['unit_price']
    total_value = df_csv['item_value'].sum()
    
    print("--- ITEM-WISE VALUATION ---")
    for idx, row in df_csv.iterrows():
        print(f"Item: {row['item_name']:<20} | Value: Rs. {row['item_value']:>10.2f}")
    print("-" * 45)
    print(f"Total Inventory Value: Rs. {total_value:,.2f}")
    print("-" * 45 + "\n")

if __name__ == "__main__":
    create_and_populate_db()
    export_and_calculate()

Expected Terminal Output:

✓ Inventory table created in SQLite database with CHECK constraints.
✓ 10 records successfully inserted.

✓ Exported database data to inventory_export.csv.
✓ Loaded CSV file back into Pandas DataFrame.

--- DATA LOADED FROM CSV ---
 item_id          item_name    category  qty  unit_price
       1             Laptop Electronics   15     45000.0
       2         Smartphone Electronics   30     15000.0
       3       Office Chair   Furniture   25      4500.0
       4         Study Desk   Furniture   10      8000.0
       5 Ballpoint Pen Pack  Stationery  120       150.0
       6           Notebook  Stationery  200        80.0
       7     Wireless Mouse Electronics   50      1200.0
       8          Keyboards Electronics   40      1500.0
       9       File Cabinet   Furniture    8     12000.0
      10 Whiteboard Markers  Stationery   80        50.0
---------------------------------------------

--- ITEM-WISE VALUATION ---
Item: Laptop               | Value: Rs.  675000.00
Item: Smartphone           | Value: Rs.  450000.00
Item: Office Chair         | Value: Rs.  112500.00
Item: Study Desk           | Value: Rs.   80000.00
Item: Ballpoint Pen Pack   | Value: Rs.   18000.00
Item: Notebook             | Value: Rs.   16000.00
Item: Wireless Mouse       | Value: Rs.   60000.00
Item: Keyboards            | Value: Rs.   60000.00
Item: File Cabinet         | Value: Rs.   96000.00
Item: Whiteboard Markers   | Value: Rs.    4000.00
---------------------------------------------
Total Inventory Value: Rs. 1,571,500.00
---------------------------------------------

---

💡 Common Examination Mistakes & Tips

Key Pitfalls to Avoid

1. Omitting Constraints: Examiners grade heavily on database design. Be sure to specify CHECK(qty >= 0) and CHECK(unit_price > 0) in SQL to enforce integrity.
2. Missing AUTOINCREMENT: Let SQLite manage item IDs automatically by using INTEGER PRIMARY KEY AUTOINCREMENT. This prevents duplicate ID conflict issues.
3. Hardcoding Values in Sum: Do not compute the sum manually. Leverage the vectorized execution of Pandas (df['qty'] * df['unit_price']) followed by .sum() to calculate the aggregate value.