Northwind Port to Oracle DB
Motivation
Recognizing the need for a more comprehensive learning tool in my databases and SQL class, I ported the Northwind database to Oracle. This project addressed the lack of robust schemas for beginners and provided a more engaging learning experience for my classmates struggling with basic SQL concepts.
Project Overview
The Northwind Database simulates a global gourmet food import/export company. Originally created by Microsoft for Access, it's widely used in other platforms like SQL Server or MySQL. This port to Oracle DB maintains the database's integrity while adapting it to Oracle's specific features.
Use your favorite search engine and you will find plethora of analysis done not only by programmers, but also by data analyst, as a side note, during my research I came across with something quite funny.
Technologies and Skills Demonstrated
- SQL and PL/SQL
- Oracle Express Edition and SQL*Plus
- Database migration and porting
- Script development for database setup and data population
- Character encoding management (UTF-8)
- User and schema management in Oracle
Key Features
- Automated schema and user creation
- Custom tablespace allocation
- Preserved regional character support
- Modified table and field names for improved readability
- Detailed setup instructions for Windows environments
Future Improvements
Optimize data insertion process using BULK INSERT for improved performance
Here's a little example of what you can find in the project:
-- Create tablespace
CREATE TABLESPACE northwind DATAFILE 'northwind.dbf' SIZE 100m;
-- Create user
CREATE USER Northwind IDENTIFIED BY root;
-- Set default tablespace and quota
ALTER USER Northwind DEFAULT TABLESPACE northwind
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON northwind;
-- Grant necessary privileges
GRANT CREATE SESSION TO Northwind;
GRANT CONNECT TO Northwind;
GRANT RESOURCE TO Northwind;
COMMIT;