How Oracle 12c Databases Work
Databases have evolved over the years but have stabilized into the relational model. Oracle 12c uses this model. A relational database management system (RDBMS) stores data in logical structures called tables.
A table is a logical container of similar data. A table definition is the set of rules or characteristics for each row of data stored in table. When data is loaded into a database, it is loaded as rows within one or more tables based on the characteristics of the data.
Each row of unique data exists only once in a table. For example, there will only be one row of data for each individual customer in a table. Each row is identified by its primary key, which is a unique identifier for that specific row.
This uniqueness is a fundamental component of the relational aspect of databases. Below, each customer exists as a single row in the CUSTOMER table as identified by its unique primary key (CUST_ID) and is defined by columns specific to that table.
Tables are joined together by connecting the primary key of one table to a related table where it is defined as a foreign key. You implement relationships between tables by joining a row of one table to one or more rows in another table. (Remember: This is a relational database).
Now let’s establish a relationship between a customer stored in a CUSTOMER table is joined to an ORDER table via the foreign key relationship.
The CUSTOMER table contains a single row for each customer, and each customer has a unique primary key identifier. The ORDER table stores all the orders for the company and each order is identified by its primary key ORDER_ID.
A customer may have zero (new customer), one, or many (repeat customer) orders, and each order must have a customer. (You can’t have an order without a customer.) Each row in the ORDER table is tied to the CUSTOMER table by the CUST_ID column which acts as the foreign key joining the ORDER and CUSTOMER table.
Databases have hundreds or even thousands of tables, keys, and relationships, and tables can have millions of rows of data consuming gigabytes or even terabytes of disk storage. To speed access when searching for a specific row of data, an index is created on one or more columns in a table. Indexes work similarly to the indexes in a book.
On a daily basis, it is common for thousands of rows of data to be inserted, updated, or deleted within the database. The RDBMS software, managed by the database administrator (DBA), supports the overall management, functioning, and performance of the database.
Structured Query Language (SQL) and Oracle 12c
Structured Query Language (SQL) is the language used to query (SELECT), create (INSERT), modify (UPDATE), and remove (DELETE) data in a database.
SQL is the core language that DBAs and database application developers work in; you need a working knowledge of SQL to effectively manage a database. SQL is further defined as data manipulation language (DML) for querying, creating, modifying, and removing data and data definition language (DDL) for changing database and table structure.
Oracle offers a programming extension of SQL called PL/SQL used to implement application logic within an Oracle database.
Oracle gained a big advantage early in the database market by successfully implementing row locking inside tables. That is, when one or more people update the same row in a database at the same time, the RDBMS software ensures that everyone’s changes are made without conflicts and the data is visible with read consistency throughout the life of the transaction. Not all database vendors handled this issue cleanly.
How to find the right database for the job
The traditional saying, “If all you have is a hammer, everything looks like a nail,” is appropriate to the selection of IT toolsets. Whether people are storing the addresses, phone numbers, and e-mails for a family reunion or they’re maintaining an online shopping application, they need a data store.
The size, complexity, and scope of that data store determines whether a database is even necessary and, if so, what characteristics (and vendor) of the database are appropriate.
The data requirements for people and businesses vary greatly in size, complexity, and importance. Different technologies exist based on these factors to meet users’ data needs.
Small, individual, or limited-use data stores, such as address information, are best served by a Microsoft Excel spreadsheet or a Microsoft Access database because both are inexpensive and easy to use.
Small or medium size data stores, such as those found at small or medium sized businesses, could make good use of Microsoft SQL Server, Oracle MySQL, or Oracle Enterprise or Standard Edition. These implementations are commonly small, less complex databases where keeping costs low is an important factor.
Medium to large data stores, such as those found at medium and large businesses, are commonly the realm of Microsoft SQL Server, IBM DB2, and Oracle Enterprise Edition. This medium to large database market is what many people think of as a typical database environment.
Large and extremely large data stores, such as those found at large and multinational businesses, are commonly served by Oracle Enterprise Edition and IBM DB2. The large and complex database environments are special creatures and require very specialized software and hardware.
Review your current computer system environment and projected future needs to make the best decision for your mission. Vendors might try to upsell you on their product, so do your homework before making a decision. However, understand that as your data requirements change, your database environment might require change.