Skip to content

VNSGU BCA Sem 2: Desktop Publishing (205_02) Practical Solutions - 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.

flowchart TD
A["Import sqlite3"]
B["Connect to inventory.db"]
C["Execute CREATE TABLE"]
D["Commit & Close"]

A --> B
B --> C
C --> D
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.

flowchart TD
start[Start]
db[Fetch Data > 42000]
file[Open order.csv]
csv[Write Header & Rows]
finish[Close Resources]

start --> db
db --> file
file --> csv
csv --> finish
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?
  2. A: It opens a connection to an SQLite file. If the file doesn't exist, it creates a new one.
  3. Q: How do you handle file headers in the csv module?
  4. A: Use writer.writerow(['col1', 'col2']) before calling writer.writerows() to add the column titles.
  5. Q: What is the purpose of newline='' in open() for CSV?
  6. A: It prevents empty lines between rows on Windows systems by disabling internal newline translation.
  7. Q: How do you filter records in SQL for high-value items?
  8. A: Use the WHERE clause, e.g., SELECT * FROM Item WHERE Bill_Amount > 42000.
  9. Q: What is the difference between csv.writer and csv.DictWriter?
  10. A: csv.writer works with lists/tuples, while csv.DictWriter works with dictionaries, mapping keys to CSV headers.
  11. Q: Why is Item_id set as PRIMARY KEY?
  12. 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

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

Last Updated: April 2025