Part 1 – Oracle Database Fundamentals, Schema Design and Best Practices
Introduction
Most Java developers learn Spring Boot, Hibernate, and REST APIs before fully understanding how databases work internally. However, enterprise applications are fundamentally data-driven systems, and Oracle Database remains one of the most widely used databases in banking, insurance, telecom, healthcare, and large enterprise applications.
A strong backend developer should understand:
- Oracle architecture
- Schema design
- Data modeling
- Tablespaces
- User management
- Security and permissions
- Indexes and constraints
- Query optimization
- Normalization
- JDBC connectivity
- JPA and Hibernate integration
This series aims to bridge the gap between Java development and Oracle database administration concepts.
Oracle Database Learning Roadmap
| Part | Topic |
|---|---|
| Part 1 | Oracle Fundamentals and Schema Design |
| Part 2 | Oracle Users, Roles and Security |
| Part 3 | Oracle SQL and Query Optimization |
| Part 4 | Oracle JDBC Connectivity |
| Part 5 | JPA and Hibernate Fundamentals |
| Part 6 | Relationships and Joins |
| Part 7 | Composite Keys and Embedded IDs |
| Part 8 | Lazy Loading and Performance Tuning |
| Part 9 | Transactions and Locking |
| Part 10 | Production Best Practices |
What is Oracle Database?
Oracle Database is a relational database management system (RDBMS) that stores data in tables and maintains relationships among them.
Features:
- ACID transactions
- High availability
- Partitioning
- Security
- Backup and recovery
- Performance tuning
- Scalability
- PL/SQL support
Oracle Architecture
Physical Components
- Data Files
- Control Files
- Redo Log Files
Logical Components
- Tablespaces
- Segments
- Extents
- Blocks
Tablespace Concept
A tablespace is a logical storage unit that groups data files.
Common tablespaces:
| Tablespace | Purpose |
|---|---|
| SYSTEM | Database metadata |
| SYSAUX | Auxiliary components |
| USERS | User objects |
| TEMP | Sorting operations |
| UNDO | Rollback information |
Example:
CREATE TABLESPACE APP_DATA
DATAFILE 'app_data.dbf'
SIZE 500M;
Oracle User Management
Create user:
CREATE USER APP_USER
IDENTIFIED BY password;
Grant access:
GRANT CONNECT TO APP_USER;
GRANT RESOURCE TO APP_USER;
Roles and Permissions
Oracle provides role-based access.
Common roles:
| Role | Description |
|---|---|
| CONNECT | Login privilege |
| RESOURCE | Object creation |
| DBA | Full administration |
Custom role:
CREATE ROLE APP_ROLE;
GRANT SELECT, INSERT
ON EMPLOYEE
TO APP_ROLE;
GRANT APP_ROLE
TO APP_USER;
Schema Design Principles
A schema represents all objects owned by a user.
Schema includes:
- Tables
- Views
- Sequences
- Indexes
- Procedures
- Functions
Recommended naming:
| Object | Example |
|---|---|
| Table | EMPLOYEE |
| Sequence | EMPLOYEE_SEQ |
| Index | IDX_EMPLOYEE_NAME |
| Constraint | PK_EMPLOYEE |
Table Design Best Practices
Good:
EMPLOYEE
---------
EMP_ID
FIRST_NAME
LAST_NAME
EMAIL
CREATED_DATE
Avoid:
EMPLOYEE_INFO_2025
Guidelines:
- Singular or consistent naming.
- Avoid spaces.
- Avoid reserved words.
- Keep names meaningful.
Oracle Data Types
| Type | Usage |
|---|---|
| NUMBER | Numeric values |
| VARCHAR2 | Variable strings |
| CHAR | Fixed strings |
| DATE | Date values |
| TIMESTAMP | Date and time |
| CLOB | Large text |
| BLOB | Binary files |
Choosing VARCHAR2 Size
Bad:
NAME VARCHAR2(4000)
Good:
FIRST_NAME VARCHAR2(50)
Benefits:
- Reduced storage
- Better indexing
- Improved performance
NUMBER Data Type
SALARY NUMBER(10,2)
Meaning:
- 10 digits total
- 2 digits after decimal
Examples:
99999999.99
Primary Key Design
Good:
EMP_ID NUMBER
Bad:
EMAIL VARCHAR2(200)
Primary key characteristics:
- Unique
- Stable
- Non-null
- Immutable
Primary Key Strategies
| Strategy | Example |
|---|---|
| Sequence | EMP_SEQ |
| Identity | GENERATED AS IDENTITY |
| UUID | SYS_GUID() |
Example:
CREATE SEQUENCE EMP_SEQ;
Constraints
Primary Key
CONSTRAINT PK_EMP
PRIMARY KEY (EMP_ID)
Foreign Key
CONSTRAINT FK_DEPT
FOREIGN KEY(DEPT_ID)
REFERENCES DEPARTMENT(DEPT_ID)
Unique
UNIQUE(EMAIL)
Check
CHECK(SALARY > 0)
Not Null
NAME VARCHAR2(100) NOT NULL
Indexes
Indexes improve query performance.
Create:
CREATE INDEX IDX_EMP_NAME
ON EMPLOYEE(NAME);
Types:
| Type | Usage |
|---|---|
| B-Tree | Default |
| Unique | Unique values |
| Composite | Multiple columns |
| Bitmap | Low cardinality |
Composite Index Example
CREATE INDEX IDX_EMP
ON EMPLOYEE
(DEPT_ID, STATUS);
Best when:
WHERE DEPT_ID = ?
AND STATUS = ?
Normalization
First Normal Form (1NF)
- Atomic columns
- No repeating groups
Bad:
PHONES = 111,222
Good:
PHONE table
Second Normal Form (2NF)
Remove partial dependencies.
Third Normal Form (3NF)
Remove transitive dependencies.
Example
Bad:
EMP_ID
EMP_NAME
DEPT_NAME
DEPT_LOCATION
Good:
EMPLOYEE
DEPARTMENT
Query Optimization Principles
Avoid:
SELECT *
FROM EMPLOYEE;
Use:
SELECT EMP_ID, NAME
FROM EMPLOYEE;
Avoid:
WHERE UPPER(NAME) = 'JOHN'
Prefer:
WHERE NAME = 'JOHN'
Avoid:
LIKE '%JOHN%'
because indexes may not be used efficiently.
Java Connectivity Driver Types
Type 1
JDBC-ODBC bridge.
Deprecated.
Type 2
Native API driver.
Requires client installation.
Type 3
Middleware driver.
Uses application server.
Type 4
Pure Java driver.
Most commonly used.
Oracle Thin Driver:
oracle.jdbc.OracleDriver
Connection URL:
jdbc:oracle:thin:@localhost:1521/XEPDB1
Maven Dependency
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
<version>23.4.0.24.05</version>
</dependency>
JDBC Example
Connection con =
DriverManager.getConnection(
url,
username,
password);
PreparedStatement ps =
con.prepareStatement(
"select * from employee");
ResultSet rs =
ps.executeQuery();
Oracle Best Practices
Use Surrogate Keys
EMP_ID
instead of:
EMAIL
Always Create Indexes on
- Primary keys
- Foreign keys
- Search columns
Include Audit Columns
CREATED_DATE
CREATED_BY
UPDATED_DATE
UPDATED_BY
Avoid Excessive NULL Columns
Move optional data to child tables.
Use Proper Data Types
Bad:
SALARY VARCHAR2(50)
Good:
SALARY NUMBER(10,2)
What Comes Next?
In Part 2 we will cover:
- Oracle Security
- Roles and Privileges
- Object Permissions
- Profiles
- Password Policies
- Synonyms
- Grants and Revokes
In Part 3:
- Query optimization
- Execution plans
- Index selection
- Explain plan
- SQL tuning
In Part 4:
- JDBC
- Connection pools
- PreparedStatement
- Transactions
In Part 5 onwards:
- JPA
- Hibernate
- Relationships
- Composite keys
- Embedded IDs
- Lazy loading
- Fetch strategies
- N+1 problems
- Entity graphs
- Performance tuning
Conclusion
A Java developer who understands both Oracle internals and JPA persistence becomes significantly more effective at designing scalable enterprise applications.
Frameworks change frequently, but:
- Good schema design
- Proper indexing
- Normalization
- Query optimization
- Security
- Data modeling
remain essential skills throughout a backend developer’s career.
