Programming Assignment Unit 7
An Assignment submitted in partial fulfillment
of the requirements for the degree of
Bachelor of Science
in
Computer Science
CS 2203 – Databases
Term 1 - 2025
University of The People
October 20, 2024,
Database Design for Online Marketplace Platform
a) Importance of Transactions
Transactions are essential for maintaining data integrity and consistency within an online
marketplace platform. They ensure that all operations related to a single logical unit of work are
completed successfully before the changes are committed to the database. This is particularly
important in critical operations such as processing orders and updating inventory levels.
For example, when a user places an order, multiple actions must occur: the order must be
recorded, the inventory must be updated to reflect the purchased items, and the payment must be
processed. Using transactions allows us to group these actions into a single unit. If any part of
this process fails—such as insufficient inventory or a payment error—the entire transaction can
be rolled back, ensuring that the database remains in a consistent state. This prevents issues like
overselling products or creating orphaned records.
To implement this, I would use SQL commands like BEGIN TRANSACTION, COMMIT,
and ROLLBACK. For instance:
Sql
BEGIN TRANSACTION;
INSERT INTO Orders (user_id, product_id, quantity) VALUES (?, ?, ?);
UPDATE Inventory SET stock = stock - ? WHERE product_id = ?;
IF (payment_successful) THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
This ensures that either all changes are applied or none at all, thereby preserving data integrity.
b) Role of Static, Dynamic, and Embedded SQL
In the context of the online marketplace platform, different types of SQL play distinct roles:
Static SQL: These are predefined SQL statements that do not change at runtime. They
are typically used for operations that require consistent queries, such as retrieving product
listings or user information. Static SQL is efficient because it can be optimized at
compile time.
Dynamic SQL: This type allows for more flexibility as SQL statements can be
constructed at runtime based on user input or other variables. For example, users may
search for products using various criteria (e.g., category, price range). Dynamic SQL
enables the application to build these queries on-the-fly, accommodating various search
parameters.
Embedded SQL: This involves integrating SQL statements directly within application
code (e.g., Java or C#). It allows developers to write SQL commands alongside
programming logic, facilitating seamless interaction between the application and the
database. For instance, when processing user registrations or login attempts, embedded
SQL can validate credentials efficiently.
Each type of SQL contributes to executing database operations effectively while catering to
different use cases within the online marketplace.
c) Utilizing JDBC and ODBC
To establish connections with the database for the online marketplace platform, I would utilize
both JDBC (Java Database Connectivity) and ODBC (Open Database Connectivity):
JDBC: This is a Java-based API that allows Java applications to interact with databases.
It provides methods for querying and updating data in a database. To connect to the
database using JDBC, I would load the appropriate driver and establish a connection as
follows:
java
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/marketplace",
"username", "password");
Using JDBC enables seamless integration of database operations within Java applications,
allowing for efficient execution of transactions and queries.
ODBC: This is a standard API for accessing database management systems (DBMS). It
enables applications written in different programming languages to connect to various
databases. To use ODBC in a platform-independent manner, I would configure an ODBC
Data Source Name (DSN) and utilize it in my application code:
c
SQLHENV henv;
SQLHDBC hdbc;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
SQLDriverConnect(hdbc, NULL, "DSN=marketplace;UID=username;PWD=password;",
SQL_NTS);
Using ODBC allows for broader compatibility across different programming environments while
maintaining robust access to the underlying database.
Conclusion
In summary, transactions play a vital role in ensuring data integrity within an online marketplace
platform by allowing grouped operations that can be committed or rolled back as needed. The
use of static, dynamic, and embedded SQL enhances operational efficiency by catering to
various application needs. Additionally, leveraging JDBC and ODBC facilitates seamless
database connectivity across different programming environments. Together, these elements
contribute to building a robust and scalable online marketplace platform capable of handling
large volumes of data efficiently.
References
Date, C. J., & Darwen, H. (2006). Database Design and Relational Theory: Normal
Forms and All That Jazz. O'Reilly Media.
Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.).
Pearson.
GeeksforGeeks. (2022). Difference between Static SQL and Dynamic SQL. Retrieved
from https://www.geeksforgeeks.org/difference-static-dynamic-sql/
