Oracle PL/SQL e-commerce DB project: design schemas, manage data, run queries, and modularize code for easy reuse & testing. . เผหโ๐๏ธ ๏ฝกโ ๐ โ ๐ฆน.โงห
- ๐๏ธ E-Commerce Database Management System 1.0
- ๐ Table of Contents
- ๐ Project Summary
- ๐ฏ Project Features
- ๐ Project Requirements
- ๐ Run This Project
- ๐งช Expected Output
- โฃ๏ธ A Detailed Breakdown of the 10 Phases
- ๐ Phase 1: Automatic Data Type Detection (Java Program)
- ๐๏ธ Phase 2: Entity-Relationship (ER) Modeling
- ๐งฌ Phase 3: Entity-Relationship (ER) Mapping & Generating SQL Insert Statements
- ๐๏ธ Phase 4: SQL DDL & Populating the Database
- ๐ฑ Phase 5: โจIntroductoryโจ SQL Retrieval Queries & Query Processing
- ๐ Phase 6: โจIntermediateโจ Retrieval Queries & Understanding Query Processing
- ๐ Phase 7: Intermediate Retrieval Queries + Transactions & Serialization
- โ๏ธ Phase 8: PL/SQL Triggers & PL/SQL Procedures
- ๐ Phase 9: Java Database Connectivity (JDBC) with Prepared Statements
- ๐งผ Phase 10: Normalization
This 16-week long project for Data Models and Database Management Systemsโ CSCI4125 demonstrates practical use of Oracle PL/SQL to design, implement, and manage a relational database system with business logic and data consistency/accuracy. It showcases schema design, advanced SQL querying, and reusable/modular PL/SQL development through a realistic e-commerce data model.
๐ Data Processing & Automation
- Scalable to millions of records
- Build data import/export workflows
- Generate SQL INSERT statements from raw text files
- Automatic data type detection for any dataset with the use of Java
- Solidfy understanding of query logic by building flat file models in Java
๐๏ธ Database Design & Architecture
- Entity-Relationship (ER) modeling + mapping
- Design and implement an e-commerce database (customers, orders, products, reviews)
๐๏ธ Business Logic & Operations
- Write complex SQL queries and aggregations
- Develop modular PL/SQL procedures, functions, and triggers to enforce business logic
๐ฉ๐ผโ๐ป Developer Experience
- No installation required โ runs on free Oracle LiveSQL
- Runtime execution output via DBMS_OUTPUT
๐ฆ Oracle account
๐ฆ SQL Developer (if going the SQL Developer route)
๐ฆ Free Tier Oracle Database (if going the SQL Developer route)
๐ Create an Oracle Cloud account and sign up for the Oracle Cloud Free Tier
https://www.oracle.com/cloud/free/
โญ๏ธ You will never be charged unless you explicitly upgrade to a different tier. You database will deactivate after being dormant for a certain number of days, and even then, they'll warn you via email to restart your database to keep it.
๐ Create a database in Oracle Cloud
๐ Download SQL Developer
https://www.oracle.com/database/sqldeveloper/
Set a new database connection
o Name: Name your connection. I used my project title.
o Username: ADMIN
o Password: Password. I used my Oracle Cloud password.
CONNECTION TYPE: Cloud wallet Browse > add wallet from Oracle Cloud that you got in [Oracle Cloud Setup](#oracle-cloud-setup) o Click the โTestโ box at the bottom. If everything is correct, the โStatusโ in the lower left-hand corner should say โSuccessโ.
o Click the โSaveโ box at the bottom to save your connection.
o Click the โConnectโ box.
Now, do the same steps but for a user account.
๐ Use SQL Developer to run SQL scripts
Running files
Drag each .sql script (in the order below) into the SQL Developer window and press the Run button. Check the Script Output tab for output.
- setup.sql โ Creates tables + populates all data except LineItem + adds Inventory column
- triggers.sql โ Creates inventory management system trigger + inserts LineItem data (trigger fires here)
- will intentionally throw custom user errors when a product has no stock/not enough stock left
- procedures.sql โ Defines Invoice procedure + runs test calls
- queries.sql โ Optional test queries
โญ๏ธ If you mess up at any point, run setup.sql again and start over.
This project uses PL/SQL.
You can run all scripts for free on Oracle LiveSQL.
๐ Open LiveSQL/FreeSQL
https://freesql.com/
๐ Create a workspace (free)
Click โLogin / Sign Upโ โ create an Oracle account if you donโt have one.
๐ How to run the files:
Drag setup.sql into the SQL Worksheet window and press run. If it was successful, you will see the tables appear in the Navigator pane and results in the Query Results!
Drag each .sql file (in the order below) into the SQL Worksheet window and press the Run button. Check the Query Results tab for output.
- setup.sql โ Creates tables + populates all data except LineItem + adds Inventory column
- triggers.sql โ Creates inventory management system trigger + inserts LineItem data (trigger fires here)
- will intentionally throw custom user errors when a product has no stock/not enough stock left
- procedures.sql โ Defines Invoice procedure + runs test calls
- queries.sql โ Optional test queries
When running schema.sql, you should see output similar to the following in the DBMS Output panel:
// TODO: picture here
| PRODUCT COUNT: SHOULD BE 20 | |
|---|---|
| 1 | 20 |
| REVIEW COUNT: SHOULD BE 12 | |
| 2 | 12 |
| ... |
Phase 1 Input: .txt or .csv file of data
Phase 1 Output: .csv of data types (built upon in Phase 3)
Java program that does a simple evaluation for the data types of values in a file. Scalable, volume-independent, and data-agnostic.
My Entity-Relationship Model for the request quoted below.
Entities (in squares), attributes (in ovals) and keys (underlined), and the relationships (in triangles) (including cardinality and participation) among the online retail store.
Our database keeps track of customers and the orders they make. For each customer, we store a unique customer ID, name, and date of birth. We also allow customers to store multiple shipping addresses. We only allow New Orleanians to be customers so an address is just the street address; we donโt store city, state or zip.
Once a customer registers their account, they can place as many orders as they want. However, an order is only made by one customer, no split orders. For each order, we store a unique confirmation number, the order date, the cost of products in the order, the shipping cost, and the total order cost. The cost of the products is computed from the sum of all the items and their quantities. The shipping cost is determined based on the cost of the products. If the order is at least $35, we offer free shipping. Otherwise, we charge a standard $10 for shipping. The total cost is the sum of the product costs and the shipping cost.
Orders may include many products and a product may be part of many orders. For each product, we store a unique ID, name, price, and the inventory. When a product is included in an order, we also record the quantity of the product included in the order; this quantity also needs to be subtracted from the inventory.
We also store anonymous reviews for products. For each review, we store a number, which is only unique for each product, a rating (1 to 5), and the review text, which is limited to 255 characters.
We also have a rewards program where customers can receive credit for referring other customers. A current customer can refer as many people they want (for more credits), but only one person can receive credit for referencing another customer.
Developing a complete logical schema (ER Map) based on Phase 2's ER Model.
Phase 3 Input: .txt file of data
Phase 3 Output: .csv of SQL insert statements (built on Phase 1)
Phase 3 builds on the previous Phase 1, which was a Java program that determines data types of values in a file. Phase 3 takes our data from a text file and automatically generates SQL insert statements for the data.
- Less Copy/Pasting SQLโ automates SQL insert statements for you to copy/paste into a
.sqlfile. - Scalabilityโ works with thousands or millions of records to populate.
- Even Your Grandma Could Do Itโ a non-CS end-user can pass in data without needing to write any SQL.
SQL DDL script to define my tables Customer, Address, Product, Review, Orders, and LineItem.
Phase 4 builds on our previous phase. Here, we are defining a DDL to run in SQL Developer. Then, we can move on to Part 2 to populate our database, also in SQL Developer.
Phase 4 Input: six .txt files of data.
Phase 4 Output: six SQL scripts containing INSERT statements, using Phase 3's Java program.
Here, we populate our 6 brand new tables by putting our input into our Phase 3 Java code that automates SQL insert statements, taking the output from that program, and putting it into SQL Developer.
If you want to verify that all records were populated, you can run SELECT COUNT(*) FROM [table name] in SQL Developer. This should return 20 for Customer, 15 for Address, 20 for Product, 12 for Review, 50 for Orders, and 100 for Lineitem.
Run SQL scripts in SQL Developer in the order of the following to guarantee referential integrity:
Product,
Review,
Customer,
Address,
Orders,
LineItem
Introductory SQL Retrieval Query Input
Introductory SQL Retrieval Query Code
Introductory SQL Retrieval Query Results
Introductory SQL Retrieval Query Input
Flat File Model (Java Program) of SQL Retrieval Query
SQL Retrieval Query Result For Java Program to Replicate
Flat File Model Result
Query #8: Find the name of the product with the highest price. Do not hardcode any prices or other values โ you must use SQL without assuming you know the current database snapshot.
One of benefits of SQL is the program-data independence supported by the relational model (or the DBMS). To fully understand the power of SQL, it is helpful to know how the equivalent could be implemented in the flat file model. In this task, we wrote a Java method that is the flat file model solution for Query #8 in Part 1.
Intermediate SQL Retrieval Query Code
Intermediate SQL Retrieval Query Code Results
Phase 6 is based on Inter SQL: Intermediate Retrieval Queries. Wrote queries (without hardcoding) to find the following:
- Find the names of all customers who have at least one address
- For each product id, list the total quantity sold
- Find the names of all products that do not have any reviews
- Find the names of all customers who were referred by 'Margot Robbie'. Note: You must use the string โMargot Robbieโ and not hardcode the ID.
Flat File Model (Java Program) of SQL Retrieval Queries #1 & #2
SQL Retrieval Query Result For Java Program to Replicate
Flat File Model Result
Query #1: Find the names of all customers who have at least one address
Query #2: For each product id, list the total quantity sold
One of benefits of SQL is the program-data independence supported by the relational model (or the DBMS). To fully understand the power of SQL, it is helpful to know how the equivalent could be implemented in the flat file model. In this task, we wrote a Java method that is the flat file model solution for Queries #1 and #2 in Part 1.
More Intermediate SQL Retrieval Query Code
Intermediate SQL Retrieval Query Code Results
More Inter SQL: Intermediate Retrieval Queries. Wrote queries (without hardcoding) to find the following:
- Retrieve the names of all products which have sold a total quantity greater than 30.
- Retrieve the names of all customers who were referred by someone who was referred by the customer with ID C15. Ex. Return CustomerA who was referred by CustomerB where CustomerB was referred by customer C15
- Find the customer name's that have ordered the least expensive product
- Find the avg review rating for each product that has received a review (do no include products that have not received a review).
- List the total cost for each order (quantity * price for all products for each order ID) where the order cost is greater than 1000.
- List customers that have spent more than $1000 on orders
- Find the total number of orders placed by customers who were referred by Margot Robbie; use the regular expression function REGEXP_LIKE.
- Find all customer names where both the first name and last name begin with a vowel
Precedence graphs for S1 and S2
Consider the three transactions T1, T2, and T3, and the schedules S1 and S2 given below. Draw the serializability (precedence) graphs for S1 and S2, and state whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule(s).
T1: R1(X), R1(Z), W1(X) T2: R2(Z), R2(Y), W2(Z), W2(Y) T3: R3(X), R3(Y), W3(Y)
S1: R1(X), R2(Z), R1(Z), R3(X), R3(Y), W1(X), W3(Y), R2(Y), W2(Z), W2(Y) S2: R1(X), R2(Z), R3(X), R1(Z), R2(Y), R3(Y), W1(X), W2(Z), W3(Y), W2(Y)
SQL trigger that will maintain the inventory for products.
Wrote a trigger that will fire when we INSERT a row into the Lineitem table. This trigger will check the value of Inventory for the corresponding ProductID.
Example invoice return
PL/SQL procedure that generates an invoice for a given customer ID and order date
Wrote a PL/SQL procedure that generates an invoice for a given customer ID and order date. The invoice will compute some of the derived columns we saw in Phase 2, specifically product cost, shipping cost, and total cost.
Java application that generates an invoice for a given customer ID and order date.
Generated invoices when passing in Customer ID and Order Date; handles no invoice as well.
Wrote a Java application that generates an invoice for a given customer ID and order date. The invoice will compute some of the derived columns we saw in Phase 2, specifically product cost, shipping cost, and total cost.
Part 1 Info: New PRODUCT Relation
Removed any partial key dependencies to create PRODUCT Relation in Second Normal Form
Removed any transitive dependencies to create PRODUCT Relation in Third Normal Form
Since we've originally designed the web store database, fields have been added to the original PRODUCT relation. We start to notice some data inconsistencies (due to update anomalies caused by data redundancy). Our job is to evaluate and fix these problems.
Part 2 Info: New CUSTOMER relation to store (multiple) payment methods for customers.
Proper 1NF to allow multiple payment methods for each customer.
Someone in our organization created the new CUSTOMER relation above to store (multiple) payment methods for customers. To allow multiple payment methods for each customer, we had to alter the relation so that it is in proper 1NF.
Redundancies and decomposition on functional dependencies.
With 1NF introducing redundancies (payment methods), we have to decompose on functional dependencies. Decompose on this functional dependency:
CustomerID โ Name, DateOfBirth, ReferrerID
Resulting decomposition:
CUSTOMER(CustomerID, Name, DateOfBirth, ReferrerID)
CUSTOMER_PAYMENT(CustomerID, PaymentMethod)
New relation(s) in proper 3NF.
Using the functional dependencies identified, we then show the new relation(s) in proper 3NF.
Updated schema for the entire web store in proper 3NF.
Using information gathered from parts 1 and 2, we drew the updated schema for the entire web store in proper 3NF.
Note: Parts of Phase 10 may be incorrect due to not saving professor feedback :(