Getting Started with Oracle Database

Ever since the invention of the writing system, the history of civilization has been intertwined with the development of the information systems. These systems, from the earliest writings to modern databases, have facilitated the accumulation and transmission of knowledge.

Until the mid-20th century, handwritten documents served as the primary means of information storage and retrieval. However, the advent of computers marked a turning point, leading to a rapid shift from physical methods to digital data. This digital revolution wasn't merely a change in data storage format; it was an explosion in information. The volume and velocity of data recording skyrocketed, creating an urgent need for efficient systems to manage and record it all.

In response, in the early 1970s, following Codd's presentation of relational database theory at IBM Research, Oracle introduced its first commercial database product. Today, Oracle Database is arguably one of the most important bases of modern government and business administration. It is integral to various organizational functions, including transaction processing, data warehousing, and business intelligence applications.

Oracle Database is basically a proprietary software that is developed and marketed by Oracle Corporation, requiring payment for software usage licenses. However, Oracle also provides a no-cost alternative: the Express Edition. 

Designed for individual learners and small businesses, Oracle Database XE does come with certain resource restrictions. Its latest release, 21c, supports up to two cores for CPU processing, 2 gigabytes of RAM, and 12 gigabytes of data storage. To exceed these limitations, upgrading to the Standard or Enterprise Edition is necessary. Nevertheless, the Express Edition would be more than enough for running a small application or practicing SQL and PL/SQL for Oracle certifications.

How to Install Oracle Database XE on Your PC?

System requirements for Oracle Database XE are as follows:

• Operating System: Windows 10 x64 - Pro, Pro for Workstations, Enterprise, and Education editions
• Disk Space: 8.5 gigabytes minimum for Oracle software, plus 2 gigabytes or more for temporary storage
• RAM: 2 gigabytes RAM minimum

Before proceeding, make sure that you are logged on to Windows with Administrator privileges. If you are logged in as a domain user, ensure that you are connected to the network before you install Oracle Database XE. Also check if the ORACLE_HOME or TNS_ADMIN environment variables have been set on your machine. If so, delete them. 

Let's navigate to the software downloads page on Oracle's website and download the Oracle Database XE for Windows (ZIP). Once the download is complete, extract the ZIP file. Inside, you'll find the "setup" icon. Double-clicking it will start the Oracle XE installation process. Click "Next" to proceed.

Read the license agreements for Oracle Database 21c Express Edition. Then select "I accept the terms in the license agreement," and click "Next" to continue.

In the Destination Folder window, you can either accept the default location or click "Change" to select a different installation directory. If you select a different location, please make sure that the directory folder names do not contain any spaces or non-roman alphabet characters.

This directory will serve as the location for storing software source files, as well as database files and logs.

• C:\app\<username>\product\21c\: Oracle Base. This is the root of the Oracle Database XE directory tree.
• C:\app\<username>\product\21c\dbhomeXE: Oracle Home. This home is where the Oracle Database XE is installed. It contains the directories of the Oracle Database XE executables and network files.
• C:\app\<username>\product\21c\oradata\XE: Database files.
• C:\app\<username>\product\21c\diag\rdbms\XE\XE\trace: Diagnostic logs.
• C:\app\<username>\product\21c\cfgtoollogs\: Database installation, creation, and configuration logs.

Next, in the Oracle Database Information window, enter and confirm the single database password to use for the SYS, SYSTEM, and PDBADMIN database accounts. Password recovery is not easy, and any unauthorized access or file modification without proper knowledge could result in data corruption. So, please select a secure yet memorable password. 

Lastly, in the Summary window, please review the installation settings and click "Install" to begin database installation, if you are okay with them. Otherwise, click Back and modify the settings as necessary.

After a few minutes, the installation will be finished. Make a note of the connection strings provided for multitenant container database, pluggable database, and EM Express URL. Click "Finish" to close the installer.

Connecting to Oracle Database XE

When you install Oracle Database XE, Windows user that you are currently logged in is automatically added to the ORA_DBA operating system group, which grants you the SYSDBA privileges. Open the Windows Command Prompt and enter the following commands to connect to the database, after replacing <oracle_home> with the path to your Oracle Home. If you didn't changed the default installation directory, it should be C:\app\<username>\product\21c\dbhomeXE. 

cd <oracle_home>\bin
sqlplus / as sysdba

Starting with version 12c, Oracle introduced the concept of the pluggable database, also known as the multitenant architecture. In this architectural model, there is a single comprehensive database, called the container database (CDB), which functions as the root database. Residing in this container database, there are multiple pluggable databases, each possessing the full functionality of a standalone database, including its own users, objects, tables, applications, and more. However, the container database itself doesn't contain any database objects.

In earlier versions like 11g, every database required its own dedicated server. For example, if your company maintains databases for HR, customer orders, sales history, and product media, a separate server needs to be allocated for each. However, this approach is highly inefficient, burdening database administrators (DBAs) with heavy workloads and companies with significant amount of server costs.

To address this, starting from release 12c, Oracle re-designed its architecture to allow a single container database to have multiple pluggable databases. This change enables the hosting of many databases, each serving a specific task, within a single root container residing in a single server, which reduces both workload for DBAs and server costs.

Entering the commands mentioned earlier connects you to the root container CDB$ROOT of the pluggable database as database user SYS.

By querying the V$DATABASE view, which provides information about the current database connection, you can check if you're connected to a container database (CDB): 

Now, let's retrieve a list of PDBs within the CDB by executing the query: SELECT NAME FROM V$PDBS; 

Installing Oracle Sample Schemas

For beginners to Oracle Database, utilizing sample databases can be greatly helpful in practicing SQL and PL/SQL. Oracle provides several ready-made databases for its database learners. 

Visit the GitHub repository and download the "Source code (zip)" file for the latest release of the sample schemas. After downloading the file, extract it, and relocate all the contents to the directory path: C:\app\<username>\product\21c\dbhomeXE\demo\schema\, if you accepted the default directory path for Oracle database installation.

Next, open a new Windows Command Prompt and cd into the schema folder:

cd <oracle_home>\demo\schema\db-sample-schemas-23.3\<schema_folder>

Connect to the CDB with a privileged user:

sqlplus / as sysdba

Then, alter session with the PDB name by entering an SQL command:

alter session set container=<pdb_name>

If you don't remember the exact PDB name, you may query SELECT NAME FROM V$PDBS;, before altering your session.

Lastly, run the <schema>_install.sql script:

@hr_install.sql

For example, to install HR schema (the hr_install.sql file under the human_resources folder):

During the installation, you are prompted for:

• Password: Enter an Oracle Database compliant password
• Tablespace: If you do not enter a tablespace, the default database tablespace is used.

Note that if the schema already exists, it is removed/dropped and a fresh HR schema is installed.

You may install additional sample schemas. Oracle XE supports up to three pluggable databases. cd to the second sample schema and connect as sysdba:

Next, create a new PDB by using the seed as a template:

create pluggable database <pdb_name> from PDB$SEED 
file_name_convert = ('<database_file>\pdbseed', '<database_file>\<pdb_name>');

Replace <pdb_name> and <database_file> with your desired PDB name and the correct database file directory. If you didn't change the default installation directory path, <database_file> should be C:\app\<username>\product\21c\oradata\XE.

For Oracle certification preparation, I would recommend installing two sample schemas and reserving one spot, as the exam addresses creating and managing schema objects; you can practice the topics using a personal schema without modifying the provided samples.

Installing Oracle SQL Developer

Having a frontend engine for writing and executing SQL commands can enhance your productivity in DB programming. Oracle SQL Developer is a powerful and versatile frontend application designed to interact with Oracle Database.

Head over to the SQL Developer download page on Oracle's website and download the version for Windows 64-bin with JDK included. Once the download is complete, extract the ZIP file to your preferred destination. You'll find the "sqldeveloper" icon in the folder. Clicking it will open the application; no further installation is required.

On the upper left corner, click the "+" button to add a new database connection. Then in the New / Select Database Connection window, enter database name, username, and password for the new connection. For example, if you installed the HR schema from the previous section, you may enter HR for the name and username. Then, enter the password that you set during the installation. Optionally, you can also set a color for the database. Next, select "Service name" radio button and enter your PDB name. If you click "Test" button, SQL developer will check if the connection is valid. Lastly, click "Connect" for the new database connection.

Now, let's take a brief look at how to use the SQL Developer. On the menu bar, click "SQL Worksheet" button to open a new SQL worksheet. This worksheet is the main place where you can write your SQL codes.

Right click on the left side of the worksheet and select "Toggle Line Numbers," for better interface.

After writing an SQL query, you can click either "Run Statement" or "Run Script" button. The "Run Statement" button will run the selected SQL statement in the worksheet, while the "Run Script" button will run the entire SQL script.

What are Relational Databases?

In general, databases serve the purpose of facilitating existing business processes or modeling real-world systems. For example, a common scenario involves a business with a central hub for storing all employee information. Consider an HR professional is assigned with the tasks of implementing a payroll services. To facilitate his tasks, the company would likely construct a data model, reflecting the payroll operations. In developing this data model, the company must first identify the basic "things" of the business, referred to as entities. These entities can be job titles, timekeeping records, or performance review of current employees. 

Relational databases are a type of database that stores entities in a structured way, organized into tables. These tables are like spreadsheets and contain rows and columns. Each row represents a single record, and each column represents a specific attribute of that record.

Here's a key feature of relational databases: the tables (entities) can be linked together based on shared data points. This allows you to easily access and analyze data across different tables. For example, imagine a database storing employees in one table and their job titles in another table. By linking these two tables based on a job ID, you can see an employee's current job title.

In RDBMS development, as you identify your entities, you'll need to determine the relationships between them. These relationships can be one-to-one, one-to-many, or zero-to-many depending on your business rules. For example, you'll likely allow any single job title to be associated with one or more employees. Conversely, you wouldn't allow any employee to have more than one title.

How to Draw ERDs Using SQL Developer?

An ERD, or Entity-Relationship Diagram, is a visual representation of the relationships between the entities in a database. It demonstrates the logical structure of your data model. 

In Oracle SQL Developer, navigate to the "View" → "Data Modeler" → "Browser". This will open a new tab with the Data Modeler tools on the bottom left corner.

Next, in the Browser tab, right-click on "Relational Models" and select "New Relational Model." This will create a blank canvas for your ERD. Then you can drag-and-drop tables shown on the upper right corner.

If you want to draw an ERD based on an existing database schema, go to "File" → "Data Modeler" → "Import" → "Data Dictionary." This will open the Data Dictionary Import Wizard.

In the wizard, select the database connection and click "Next."

Next, select which schema you want to import and click "Next."

In the schema, select the objects you want to include in your ERD and click "Next."

Review the Data Modeler design summary and click "Finish."

In this sample HR schema, there are seven tables:

• EMPLOYEES: Table containing employee details
• DEPARTMENTS: Table containing department information
• JOBS: Table containing job titles and salary ranges
• JOB_HISTORY: Table containing start and attrition dates of former employees 
• LOCATIONS: Table containing office locations
• COUNTRIES: Table containing country IDs, names, as well as IDs of the associated regions
• REGIONS: Table containing region IDs and names

The example ERD illustrates the relationship between these seven entities. Each entity is represented as a yellow box and each relationship is depicted as an arrow connecting two boxes with some symbols to detail the nature of the relationship. For example, each employee must have one and only one job title, whereas a job title can be assigned to one or potentially more than one employees. This one-to-many relationship is represented between EMPLOYEES and JOBS by an arrow displaying a single bar (indicating one) to a crow's foot (representing many) on the side of the line connecting to JOBS.

In the ERD, some of the arrows are solid, while others are dashed. The solid arrows mean the relationship in the direction of the arrow is mandatory. For example, the arrow between the EMPLOYEES and JOB_HISTORY indicates mandatory one-to-many relationship. That is, every employee recorded in the EMPLOYEES table must have at least one JOB_HISTORY record and every JOB_HISTORY record must have one and only one associated employee record in the EMPLOYEES table. 

On the other hand, the dashed arrows represents optional relationship. For example, a dashed one-to-many arrow pointing EMPLOYEES from DEPARTMENTS indicates that each department may or may not have an employee. This is called a zero-to-many relationship. 

What is SQL?

SQL, or Structured Query Language, is a domain-specific language designed for creating and interacting with a relational databases. It is important to note that SQL serves as a mechanism for handling databases, rather than being the database itself. SQL commands can operate independently or be integrated within applications written in other languages. Common programming languages like Java, Python, and others have the capability to issue SQL statements to a relational database. These languages transfer their own data into SQL statements, send the SQL statements to the database for execution, and then capture the returning data from the database for further processing.

There has been ongoing discussions regarding whether SQL qualifies as a Turing complete programming language. Turing completeness refers to a language's capability to theoretically compute anything that any other computer can compute, provided enough time and memory. It's a benchmark for general-purpose programming languages. 

Compare to other general-purpose languages, such as Python or Java, SQL has a smaller set of functionalities. In particular, SQL lacks in features like conditional statements and recursion, which are considered essential for achieving Turing completeness. That being said, it cannot have an access to individual data points for data evaluation and operations. Unlike general-purpose programming languages, which execute logic procedurally in order they were programmed, SQL operates as a set-based language. It classifies data into specific sets (attributes) and performs operations on these sets as a whole. Consequently, there were certain programming tasks SQL couldn't handle. 

Nevertheless, SQL serves as the industry standard for working with relational databases. It excels at data retrieval, manipulation, and organization by applying operations to sets of data at once, rather than recursively applying them to individual data points.

SQL commands can be broadly categorized into Data Definition Language (DDL), Data Manipulation Language (DML), and Transaction Control Language (TCL). DDL consists of those SQL statements that are used to build database objects. Specifically, DDL statements are used to:

• Create, alter, and drop database objects, such as tables, views, or users.
• Issue privileges to users, defining the level of access users have.
• Add comments on a particular object to be stored in the database and associated with that object.
• Initiate performance analysis on objects using built-in tools.

The following list briefly describes DDL statements that are commonly used:

• CREATE: Creates a user, table, view, index, synonym or other objects in the database.
• ALTER: Used to modify the structure, name, or other attributes of an existing database object, with exceptions for SESSION and SYSTEM. ALTER SESSION changes settings for the current session, lasting only until the connection ends. ALTER SYSTEM modifies system-wide parameters, effective until the database restarts. Technically, neither ALTER SESSION nor ALTER SYSTEM is a DDL statement; they fall under a different category.
• DROP: Removes a database object from the database that has already been created with the CREATE statement.
• RENAME: Changes the name of an existing database object.
• TRUNCATE: Removes all records from an existing table. 
• GRANT: Provides privileges to user objects and enables them to perform specific tasks in the database.
• REVOKE: Removes privileges that have been issued with the GRANT statement.
• FLASHBACK: Restores an earlier version of a table or database.
• PURGE: Irrevocably removes database objects from the recycle bin.
• COMMENT: Add comments to the data dictionary for an existing database object.

Note that the keywords listed above are not really statements or commands by themselves, but become one when combined with other reserved keywords. For example, CREATE TABLE or CREATE SEQUENCE is an SQL statement. In practice, however, many professionals call them CREATE statement. It is also worth noting that the term command and statement are used interchangeably by Oracle's official documentation. Either is fine!

DML refers to SQL statements used to retrieve and manipulate data stored in the database. The following list briefly summarizes each DML statement that is commonly used in practice:

• SELECT: Retrieves data from a table or view
• INSERT: Adds data records to a database table, either directly or through a view.
• UPDATE: Modifies existing data records in a table, either directly or through a view.
• DELETE: Removes existing data records in a table, either directly or through a view. 
• MERGE: Performs a combinations of INSERT, UPDATE, and DELETE statements in a single statement, depending on some condition.

In addition to DDL and DML, there are three SQL statements that are categorized as TCL. 

• COMMIT: Saves a set of DML modifications performed in the current database session.
• ROLLBACK: Undoes a set of DML modifications performed during the current database session.
• SAVEPOINT: Marks a point in a session to prepare for a future ROLLBACK to enable ROLLBACK to restore data at the selected point in a session.

What is PL/SQL?

PL/SQL stands for Procedural Language/SQL. It is a procedural extension of SQL, developed by Oracle in the early 1990s. As mentioned earlier, due to its non-procedural nature, SQL lacks some essential features to be considered as a programming language. PL/SQL fills this gap by offering a procedural approach to data processing. 

Unlike SQL, PL/SQL operates as a procedural language. Similar to other general-purpose programming languages, PL/SQL supports variable assignment, exception handling, and the creation of functions or procedures for specific tasks. At the same time, PL/SQL integrates SQL for data processing tasks, executing SQL commands procedurally. Despite having tis distinct syntax, the core of data processing in PL/SQL remains SQL-centric.

This characteristic of PL/SQL takes some advantages over using other programming languages with SQL. It is tightly integrated with SQL and directly access to database objects. Particularly, when performance is an important issue, PL/SQL can be compiled directly into machine code for specific server platform. This offers the best speed. In general, DB programming refers to creating functions and procedures using PL/SQL to carry out various tasks.