VNSGU BCA Sem 2: Desktop Publishing (205_02) Practical Solutions - April 2025 Set B

Paper Details

• Subject: Desktop Publishing (DRA) / Python SQLite
• Subject Code: 205_02
• Set: B
• Semester: 2
• Month/Year: April 2025
• Max Marks: 25
• Time Recommendation: 45 Minutes
• Paper: View Paper | Download PDF

---

Questions & Solutions

All questions are compulsory

Q1A: Item Database Management

Max Marks: 10

Write a Python Script to do following:

1. Create Item(Item_id, Item_name, Qty, Bill_Amount) table.
2. Insert minimum 10 records in table.

1. Database Connection & Table Creation

Initialize the SQLite database and define the item structure.

Hint

Use sqlite3.connect() to create the database file. Ensure Item_id is the PRIMARY KEY.

View Solution & Output

import sqlite3

# [1] Connect and setup table
conn = sqlite3.connect('inventory.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS Item (
        Item_id INTEGER PRIMARY KEY,
        Item_name TEXT NOT NULL,
        Qty INTEGER,
        Bill_Amount REAL
    )
''')

print("Item table created successfully!")
conn.close()

Step-by-Step Explanation:

1. Initialization: Import sqlite3 and establish a connection to 'inventory.db'.
2. Logic Flow: Define the Item table schema with Item_id, Item_name, Qty, and Bill_Amount.
3. Completion: Execute the CREATE TABLE command and print a success message.

2. Bulk Data Insertion

Insert 10 sample records into the database.

Hint

Using executemany() is more efficient for batch operations than multiple execute() calls.

View Solution & Output

import sqlite3

conn = sqlite3.connect('inventory.db')
cursor = conn.cursor()

# [2] Prepare 10 records
items = [
    (1, 'Laptop', 5, 250000.0),
    (2, 'Mouse', 50, 25000.0),
    (3, 'Keyboard', 20, 30000.0),
    (4, 'Monitor', 10, 85000.0),
    (5, 'Printer', 5, 45000.0),
    (6, 'Webcam', 15, 37500.0),
    (7, 'Headset', 25, 50000.0),
    (8, 'Scanner', 3, 21000.0),
    (9, 'Router', 8, 32000.0),
    (10, 'Hard Drive', 12, 66000.0)
]

cursor.executemany('INSERT INTO Item VALUES (?, ?, ?, ?)', items)
conn.commit()

print(f"{len(items)} records inserted successfully!")
conn.close()

Step-by-Step Explanation:

1. Initialization: Create a list of 10 item tuples with sample data.
2. Logic Flow: Use executemany() to insert the batch into the Item table and commit() the transaction.
3. Completion: Print the total count of inserted records and close the database connection.

Q1B: Export Filtered Orders to CSV

Max Marks: 10

Write a Python Script to generate order.csv file having details of items whose Bill amount is more than Rs. 42000.

Hint

Fetch data from SQLite using a WHERE clause, then use Python's csv module to write the results.

View Solution & Output

import sqlite3
import csv

# [1] Fetch filtered data
conn = sqlite3.connect('inventory.db')
cursor = conn.cursor()

cursor.execute('SELECT * FROM Item WHERE Bill_Amount > 42000')
rows = cursor.fetchall()

# [2] Write to CSV
with open('order.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerow(['Item_id', 'Item_name', 'Qty', 'Bill_Amount']) # Header
    writer.writerows(rows)

print("order.csv generated successfully with high-value items.")
conn.close()

Step-by-Step Explanation:

1. Initialization: Import sqlite3 and csv modules and connect to 'inventory.db'.
2. Logic Flow: Execute a SELECT query to filter items with Bill_Amount > 42000 and fetch the results.
3. Completion: Open 'order.csv', write the header and filtered rows using csv.writer, and close all resources.

Concept Deep Dive: CSV vs Database

While databases are great for structured storage and querying, CSV (Comma Separated Values) files are universal for data exchange. Python's csv module acts as a bridge, allowing you to export database results for use in Excel or other reporting tools.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What does sqlite3.connect() do?
   • A: It opens a connection to an SQLite file. If the file doesn't exist, it creates a new one.
2. Q: How do you handle file headers in the csv module?
   • A: Use writer.writerow(['col1', 'col2']) before calling writer.writerows() to add the column titles.
3. Q: What is the purpose of newline='' in open() for CSV?
   • A: It prevents empty lines between rows on Windows systems by disabling internal newline translation.
4. Q: How do you filter records in SQL for high-value items?
   • A: Use the WHERE clause, e.g., SELECT * FROM Item WHERE Bill_Amount > 42000.
5. Q: What is the difference between csv.writer and csv.DictWriter?
   • A: csv.writer works with lists/tuples, while csv.DictWriter works with dictionaries, mapping keys to CSV headers.
6. Q: Why is Item_id set as PRIMARY KEY?
   • A: To ensure each item has a unique identifier and to prevent duplicate entries.

Common Pitfalls

• Resource Leak: Always use with open(...) or explicitly close() files and database connections.
• Data Types: Ensure numeric values like Bill_Amount are stored as REAL or INTEGER, not strings, otherwise the comparison > 42000 might fail or behave unexpectedly.

---

Quick Navigation

• ← Back to DRA Papers
• ← Back to Semester 2
• ← Back to All Past Papers

Related Solutions

Set	Link
Set A	Solutions
Set B	Current Page
Set C	Solutions
Set D	Solutions
Set E	Solutions
Set F	Solutions

---

Last Updated: April 2026