Assignment: OLTP vs. OLAP | SQL for Beginners - Software Development PDF Download

Multiple Choice Questions (MCQs)

Q1: What does OLTP stand for?
(a) Online Transaction Processing
(b) Offline Transaction Processing
(c) Online Tabular Processing
(d) Offline Tabular Processing
Ans: (a)

Q2: Which system is optimized for complex analytical queries?
(a) OLTP
(b) OLAP
(c) Both OLTP and OLAP
(d) None of the above
Ans: (b)

Q3: What is a key characteristic of OLAP systems?
(a) High transaction speed
(b) Large data volumes for analysis
(c) Row-level operations
(d) Frequent updates
Ans: (b)

Q4: Which of the following is an example of an OLTP system?
(a) An online banking system
(b) A data warehouse
(c) A business intelligence dashboard
(d) A reporting tool
Ans: (a)

Q5: How does OLTP differ from OLAP in terms of data modification?
(a) OLTP systems frequently modify data, while OLAP systems mainly read data
(b) OLAP systems frequently modify data, while OLTP systems only read data
(c) Both OLTP and OLAP frequently modify data
(d) Neither OLTP nor OLAP modify data
Ans: (a)

Q6: What type of schema is commonly used in OLAP systems?
(a) Star Schema
(b) Snowflake Schema
(c) Both (a) and (b)
(d) None of the above
Ans: (c)

Q7: Which of the following is a primary challenge in OLTP systems?
(a) Slow response time for analytical queries
(b) High redundancy
(c) Inability to handle concurrent transactions
(d) Lack of normalization
Ans: (a)

Q8: Which database operation is most frequent in OLTP systems?
(a) INSERT, UPDATE, DELETE
(b) Complex aggregations
(c) Data mining
(d) Multi-dimensional analysis
Ans: (a)

Higher Order Thinking Skills (HOTS)

Q1: Why is OLAP more suitable for decision-making than OLTP?
Ans: OLAP is optimized for complex queries and data analysis, allowing users to extract meaningful insights from large datasets. It supports multi-dimensional analysis, aggregations, and trend identification, making it ideal for decision-making.

Q2: How does indexing impact the performance of OLTP and OLAP systems?
Ans: Indexing improves OLTP performance by speeding up search and retrieval in high-transaction environments. In OLAP, indexing optimizes query performance by reducing the need to scan large datasets, improving report generation and analytical processing.

Q3: Explain how data redundancy affects OLAP and OLTP systems differently.
Ans: OLTP systems aim to minimize data redundancy through normalization to ensure data integrity and reduce storage requirements. In contrast, OLAP systems often use denormalization to optimize query performance by reducing the need for complex joins.

Q4: Why are OLTP systems typically more normalized than OLAP systems?
Ans: OLTP systems are normalized to prevent data duplication, maintain consistency, and ensure efficient updates and deletions. This structure is necessary for handling frequent transactions. OLAP systems use denormalization to improve read performance and simplify queries.

Q5: Discuss a real-world scenario where an OLAP system would be more beneficial than an OLTP system.
Ans: In a retail business, OLAP is beneficial for analyzing sales trends across multiple regions over time. While OLTP processes daily transactions, OLAP allows business analysts to generate reports, forecast sales, and identify customer buying patterns to support strategic decision-making.

Fill in the Blanks

Q1: OLAP systems are designed for ___________________  rather than transaction processing.
Ans: data analysis and reporting

Q2: OLTP databases are typically ______________ to reduce redundancy and improve transaction speed.
Ans: normalized

Q3: OLAP queries typically involve _____________ and complex joins.
Ans: aggregation

Q4: The primary purpose of an OLTP system is to ensure ________________.
Ans: data integrity and consistency

Q5: OLTP systems handle a large number of _______________ per second.
Ans: small transactions

True or False

1. OLAP systems are optimized for real-time transactional operations.
Ans: False

2. OLTP databases usually store historical data for business intelligence.
Ans: False

3. OLTP systems require high availability and low latency.
Ans: True

4. OLAP databases use denormalization to improve query performance.
Ans: True

5. OLAP systems typically store detailed row-level transactional data.
Ans: False

Hands-On Questions

Q1: Compare the schema design of an OLTP system and an OLAP system, highlighting their structural differences.
Ans: OLTP systems use highly normalized schemas, such as the third normal form (3NF), to minimize redundancy and support efficient transactions. Key tables include Customers, Orders, and Products, linked through foreign keys. OLAP systems use denormalized schemas, such as star or snowflake schemas, where a central fact table (e.g., Sales) connects to dimension tables (e.g., Time, Product, Region) to facilitate fast aggregations.

Q2: Design a simple OLTP database schema for an e-commerce website, including key tables and relationships.
Ans: 

• Customers (CustomerID, Name, Email, Address)
• Orders (OrderID, CustomerID, OrderDate, TotalAmount)
• Products (ProductID, Name, Price, Stock)
• OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, Subtotal)
• Payments (PaymentID, OrderID, PaymentMethod, AmountPaid)

The Customers table is linked to Orders, which is linked to OrderDetails, and OrderDetails is linked to Products.

Q3: Write a SQL query to perform an aggregation operation on an OLAP database, such as calculating the total sales per region.
Ans: SELECT Region, SUM(SalesAmount) AS TotalSales
FROM SalesFact
JOIN RegionDim ON SalesFact.RegionID = RegionDim.RegionID
GROUP BY Region;

Q4: Create a table that demonstrates how data is stored differently in OLTP and OLAP systems using SQL.
Ans: OLTP Example (Normalized):
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(10,2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

OLAP Example (Denormalized):
CREATE TABLE SalesFact (
SaleID INT PRIMARY KEY,
CustomerName VARCHAR(100),
ProductName VARCHAR(100),
SaleDate DATE,
SalesAmount DECIMAL(10,2));

OLTP follows normalization principles with foreign keys, while OLAP denormalizes data for faster analysis.

Q5: Analyze a sample dataset and determine whether it is better suited for OLTP or OLAP, explaining your reasoning.
Ans: Suppose a dataset contains individual customer orders with timestamps, payment details, and inventory updates. This dataset is better suited for OLTP because it requires frequent updates, quick transactions, and data integrity. Conversely, if the dataset consists of aggregated sales data per month categorized by region, it is better suited for OLAP, as it supports trend analysis and reporting.