This is a guide on relational databases.

Check out Audible on Amazon and listen to the newest books!

Oracle Databases: A Comprehensive Guide

Oracle Databases represent one of the most robust and widely-used database management systems in the world. This comprehensive document explores the many facets of Oracle's database technology, from infrastructure grids to cloud services, and from relational database concepts to the sample schemas used for learning. Whether you are new to Oracle or looking to expand your knowledge, this guide will walk you through the essential components and features that make Oracle a powerful tool for managing data in modern enterprises.

Infrastructure Grids and Information Management

The first focus area in Oracle's technology stack is infrastructure grids. These grids allow organizations to use low-cost servers and storage while still delivering a high quality of service in the critical areas of high availability, manageability, and performance. This means that businesses can achieve enterprise-grade reliability without necessarily investing in expensive, high-end hardware. In the realm of information management, Oracle focuses on content management, information integration, and information lifecycle management. There is now also support for advanced data types such as XML, TEXT, spatial data, multimedia, medical imaging, and semantic technology. This broad support enables organizations to work with virtually any type of data they might encounter.

Looking at application development, Oracle now has the capability to manage and use all major application development environments, including PL/SQL, Java, .NET, PHP, SQL Developer, and Application Express. The Oracle Cloud is an enterprise cloud for business and is based on Open Java and SQL standards. It provides an integrated collection of application and platform cloud services, making it easier for businesses to move their operations to the cloud while maintaining compatibility with existing systems.

Features of Oracle Database 12c

When examining the features of Oracle Database 12c, it is helpful to organize them by category. Within manageability, the main features include database replay, which allows you to record a load on one database and replay it on another, for instance during upgrade testing. The SQL performance analyzer assesses the performance impact of any system change, which could result in a change to a SQL execution plan. Automated SQL tuning helps optimize queries without manual intervention. Real-time database operations monitoring automatically monitors composite operations. The Enterprise Manager Database Express 12c is used for managing and monitoring your 12c database. In the area of high availability, Oracle Database 12c focuses on reducing downtime and data loss, improving online operations, and catering for faster database upgrades. The main features within the performance area are focused on secure files, compression for OLTP (online transaction processing), RAC optimizations, and the result cache.

Other features of Oracle Database 12c include manageability, with main features such as database replay, SQL performance analyzer, automatic SQL tuning, real-time database operations monitoring, and Enterprise Manager Database Express 12c. The security features include unique server configurations, a focus on data encryption and data masking, and a focus on auditing. In information integration, the focus is on integrated data throughout the enterprise and advanced information lifecycle management.

Oracle Fusion Middleware

The Oracle Fusion Middleware product set contains a portfolio of products that span a range of tools and services, from Java and developer tools, through integrated services, business intelligence, collaboration, and content management. Oracle Fusion Middleware can enable you to maximize the processes and applications that drive your business today and provide a foundation for innovation for the future. The security features include unique server configurations, data encryption and masking, and auditing. Information integration includes integrated data throughout the enterprise and advanced information lifecycle management. Oracle Fusion Middleware is a portfolio of leading, standards-based, and customer-proven software products. The development tools span services such as Enterprise Performance Management, Business Intelligence, Content Management, SOA & Process Management, Application Server, and Grid Infrastructure in order to provide improved Enterprise Management and Identity Management.

Oracle Cloud and Enterprise Manager Cloud Control

Enterprise Manager Cloud Control is a management tool providing monitoring and management capabilities for Oracle and non-Oracle components. It provides a complete, integrated, and business-driven cloud management solution in a single product. By using Enterprise Manager Cloud Control, you can create and manage a complete set of cloud services, including Infrastructure as a Service, Database as a Service, and Platform as a Service. You can manage all phases of the cloud lifecycle. You can manage the entire cloud stack from application to disk, including engineered systems with integrated capabilities. You can monitor the health of all components, the hosts they run on, and key business processes supported by these components. You can identify, understand, and resolve business problems through the unified and correlated management of user experience, business transactions, and business services across all packaged and custom applications.

The Oracle Cloud is an enterprise cloud for business, providing an integrated collection of application and platform cloud services based on best-in-class products and on Open Java and SQL standards. Applications and databases deployed in the cloud are portable and can easily be moved to or from a private cloud or an on-premise environment.

Essential Characteristics of the Oracle Cloud

The Oracle Cloud consists of many different services that share some common characteristics. The five essential characteristics are:

• On-demand self-service – the focus is on provisioning, monitoring, and management control

• Resource pooling – implies the sharing and a level of abstraction between consumers and services

• Rapid elasticity – allows you to quickly scale up or down as needed

• Measured service – the focus is on metering utilization for either internal charge-back or external billing

• Broad network access – allows access through a browser on any network device

Oracle Cloud Service Types

The Oracle Cloud provides three types of services. Software as a Service (SaaS) refers to applications that are delivered to end users over the Internet, for example the Oracle CRM On Demand product. Platform as a Service (PaaS) refers to an application development and deployment platform delivered as a service to developers, allowing them to quickly build and deploy SaaS applications to end users. Typically you will find databases, middleware, and development tools all delivered as a service via the Internet. Infrastructure as a Service (IaaS) refers to the hardware, servers, storage, and network, and will also include the associated software such as operating systems, virtualization, and clustering.

Cloud Deployment Models

The four types of cloud deployment models are the private cloud, public cloud, community cloud, and hybrid cloud. The private cloud is used by a single organization and is typically controlled and managed, and hosted in a private data center. It can be outsourced to a third-party service provider. A public cloud is where multiple organizations use a private cloud on a shared basis, which is hosted and managed by a third-party service provider. The community cloud is where a group of related organizations want to make use of a common cloud environment. The cloud is managed by the participating organizations or by a third-party service provider, and is either hosted internally or externally. Lastly, the hybrid cloud is where a single organization wants to adopt both private and public clouds for a single application.

Relational and Object Relational Databases

The Oracle server supports both relational and object relational database models and has extended the data modeling capabilities to provide object-oriented programming, complex and user-defined data types, complex business objects, and full compatibility with the relational world. It also supports multimedia and large objects. With high-quality database server features included for performance and functionality, OLTP applications benefit from better sharing of run-time data structures, larger buffer caches, and deferrable constraints. Data warehouse applications benefit from enhancements such as parallel execution of INSERT, DELETE, and UPDATE operations, partitioning, and parallel-aware query optimization. The Oracle model supports client, server, and web-based applications that are both distributed and multi-tiered.

Understanding Data and Databases

Every organization has information which it needs to store. Some examples include a library that would keep a list of members, books, due dates, and fines. Another example would be a company that needs to keep information about its employees, its departments, and salaries. These pieces of information are called data. Data can be stored in various media and in different formats. It could be a hard copy document stored in a filing cabinet, or it could be data stored in an electronic spreadsheet or in a database. A database is an organized collection of information. To manage a database, you would need a database management system. This is a program that stores, retrieves, and modifies data in the database on request.

Types of Databases

There are four types of databases: hierarchical, network, relational, and object relational. The hierarchical database model organizes the data into a tree-like structure. Each parent has one or more child records, also known as a one-to-many relationship. The network database model is similar to a hierarchical database, with the difference being that it has a many-to-many relationship. A relational database model is a collection of data items stored as tables. A table consists of rows and columns from which data can be accessed in many different ways. Almost all relational database systems use SQL, Structured Query Language. This is the language used for querying and maintaining the database. The object relational database management system model is similar to a relational database, but it includes objects, classes, and inheritances. These are supported in the database schema and in SQL.

Relational and Object Relational Database Management Systems

A relational database management system distinguishes between two types of operations. A logical operation is where an application specifies what data is required. An example of this would be where an application requests a customer name, or would add a new customer record to a table. The second is a physical operation. The database determines how the request should be handled, and then carries out the operation. An example of a physical operation would be where the application queries a table, an index is used to find the requested rows in the table, and the data is read into memory. These physical operations are transparent to the application. The object relational database management system makes use of the object-oriented database model. It supports objects, classes, and inheritance. In today's lesson, we looked at the different types of databases and focused on relational and object relational database management systems as supported by the Oracle server.

Relational Database Concepts

Common models used at the time were hierarchical and network, and even simple flat file data structures. Soon relational database management systems became popular, especially for their ease of use and flexibility in structure. The relational model consists of the following components: a collection of objects or relations that store data; a set of operators that can act on the relations to produce other relations; and data integrity for accuracy and consistency.

A database management system has the following elements. The first is kernel code. The kernel code manages the memory and storage for the database system. The second is a repository of metadata. In Oracle, the metadata repository is called a data dictionary. The data dictionary is structured in tables and views and contains information about the database, such as the definition of all schema objects, for example, tables, views, indexes, procedures, packages, triggers, and so on. It contains the space allocated for, and currently being used by, your schema objects. It contains default values for columns. It contains integrity constraint information. It contains the names of the Oracle users and their privileges and roles that are granted to them. Auditing information is stored in the data dictionary, as well as other general database information. The third element is query language. This language enables applications to access the data. In the case of Oracle, SQL is used, Structured Query Language.

Major Aspects of the Relational Model

The major aspects of the relational model are structures, operations, and integrity rules. Structures are well-defined objects that store or access the data of a database. Operations are clearly defined actions that enable applications to manipulate the data and the structure of the database. Integrity rules govern operations on the data and on the structures of the database. A relational database is a collection of relations or two-dimensional tables that are controlled by the Oracle server. For example, an EMPLOYEES table contains information pertaining to the employee, and a DEPARTMENTS table contains information that pertains specifically to the departments.

Data Models

Models are the cornerstone of design. As engineers would build a model of a car to work out any details before putting it into production, in the same manner, system designers develop models to explore ideas and to improve the understanding of the database design. A model helps to communicate concepts that are in people's minds and can be used for the following: to communicate, categorize, describe, specify, investigate, evolve, analyze, and initiate. The objective is to produce a model that fits a multitude of these uses. The model should be understood by the end user and contain sufficient detail for a developer to build a database system.

A data model is the first step in a database design. The data model starts with a conceptual model, it then evolves into a logical model, and then finally into a physical schema. A conceptual model is made up of concepts, and it is used to help people understand and simulate the subject that is represented by the model. A logical model describes in as much detail as possible the data. This is based on the conceptual model. The logical model includes entities and the relationship among them, including the attributes for each entity.

The physical schema is based on a logical model. A physical schema is the description of how data is stored in the database management system, for example, in tables, indexes, constraints, and so on. The Oracle SQL Developer Data Modeler is a free tool provided with SQL Developer. It is provided to enhance productivity and to simplify data modeling tasks. With the Data Modeler, users can create, browse, and edit logical, relational, physical, multi-dimensional, and data type models. One can forward engineer a logical model into a physical model, and reverse engineer by taking a physical model and generating a logical model. The Oracle SQL Developer Data Modeler can be used in both a traditional and cloud computing environment. In this video, we covered the data model, describing the three main components: the conceptual model, the logical model, and physical design.

Entity Relationship Model

In an effective system, data is divided into discrete categories or entities. An entity relationship model is an illustration of the various entities in a business and the relationships among them. An ER model is derived from business specifications or narratives and is built during the analysis phase of the system development lifecycle. ER models separate information required by a business from the activities performed within the business. Although businesses can change their activities, the type of information tends to remain constant. Based on that, the data structures tend to remain constant. Some of the benefits of ER modeling are that it documents information for the organization in a clear, precise format; it provides a clear picture of the scope of the information required; it provides an easily understood pictorial map for database design; and lastly, it offers an effective framework for integrating multiple applications.

Components of an ER Model

The components in an ER model are an entity, attributes, and a relationship. An entity is an aspect of significance about which information must be known. Examples are departments, employees, and orders. The attribute is something that describes or qualifies an entity. For example, for the EMPLOYEE entity, the attributes will be the employee number, name, job title, hire date, department number, and so on. Each of the attributes is either required or optional. This state is called optionality. A relationship is a named association between entities, showing optionality and degree. An example is the relationship between the EMPLOYEES and DEPARTMENTS tables.

Entity Relationship Modeling Conventions

To represent an entity in a model, use the following conventions: a singular, unique entity name (entity name must be in uppercase); use a soft box; optional synonym names in uppercase with parentheses. To represent an attribute, use the following conventions: a singular name in lowercase; an asterisk tag for mandatory attributes – these values must be known; a capital letter O tag for optional attributes – these values may be known.

Looking at relationships, each direction of the relationship contains the following: a label – an example would be "taught by" or "assigned to;" an optionality – either it must be or may be; and thirdly, a degree – either one and only one, or one or more. The symbols in an ER model are: a dashed line defines an optional element indicating maybe; a solid line defines a mandatory element indicating must be; the crow's foot is the degree element indicating one or more; and a single line is the degree element indicating one and only one.

Some final notes: the term cardinality is a synonym for the term degree. Each source entity may or must be in a relation to one and only one, or one or more, with the destination entity. When reading an ER diagram, the convention is to read clockwise. A unique identifier is a combination of attributes or relationships, or both, that serve to distinguish occurrences of an entity. Each entity occurrence must be uniquely identifiable. Tag each attribute that is part of the UID with a hash sign, and tag the secondary UIDs with a hash sign in parentheses.

Relating Multiple Tables

Each table contains data that describes exactly one entity. An example of this is the EMPLOYEES table. It contains information about the employees. Categories of data are listed across the top of each table. For example, in the EMPLOYEES table this would be the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and DEPARTMENT_ID. The individual cases are listed below. An example of this would be the EMPLOYEE_ID is 100; the FIRST_NAME, Steven; LAST_NAME, King; and the DEPARTMENT_ID of 90. Each row of data in a table can be uniquely identified by a primary key. By using a table format, you can readily visualize, understand, and use information. Data about different entities is stored in different tables, and you may need to combine two or more tables to answer a particular question. For example, you may want to know the location of the department where an employee works. To do this, you need information from the EMPLOYEES table and the DEPARTMENTS table.

In a relational database, we can relate data in one table to data in another table by the use of foreign keys. A foreign key is a column or set of columns that refers to the primary key in the same, or another table. The example in this slide shows the EMPLOYEES and DEPARTMENTS tables. The DEPARTMENTS table has a primary key on the DEPARTMENT_ID column. In the EMPLOYEES table, we have a column containing the DEPARTMENT_ID to which the employee belongs. We create a foreign key on this DEPARTMENT_ID, which in turn refers back to the primary key on the DEPARTMENTS table. The ability to relate data in one table to data kept in another allows you to organize information into separate manageable units. The employee data can be kept logically distinct from the department data by storing it in separate tables.

Guidelines for Primary and Foreign Keys

Some guidelines when creating primary and foreign keys are that you cannot have duplicate values in a primary key. The primary key generally cannot be changed. Foreign keys are based on data values and they are purely logical pointers. A foreign key value must match an existing primary key value or unique key value, otherwise it should be null. Lastly, a foreign key must reference either a primary key or a unique key column.

Relational Database Terminology

In an Oracle relational database, we have a user – the HR user, and the HR user owns objects such as tables, indexes, and so on. When the HR user creates objects within the database, the metadata is known as a schema. A schema defines attributes of the database, such as tables, columns, and properties. A schema will exist per database user that has created objects in the database. A relational database can hold one or more tables. A table is the basic storage structure of a relational database and holds all the data necessary about something in the real world, such as employees, invoices, and customers.

The numbers in the slide indicate the following: number one shows a single row, representing all the data required for a particular employee. Each row in a table should be identified by a primary key. A primary key permits no duplicate rows. The order of the rows is insignificant, as we will specify the row order when the data is retrieved. Number two shows a column or attribute containing the employee number. This number identifies a unique employee in the EMPLOYEES table. In this example, the employee number is designated as the primary key. A primary key must contain a value, and the value must be unique. Number three shows a column that is not a key value. A column represents one kind of data in a table. In this example, the data is the salaries of all employees. Number four shows a column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other and refers to a primary key or unique key in the same table, or in another table. In this example, the DEPARTMENT_ID uniquely identifies a department in the DEPARTMENTS table. In number five, a field can be found at the intersection of a row and a column, and there can only be one value in it.

Schema Object Types

Schema object types include indexes, partitions, views, sequences, synonyms, and PL/SQL subprograms and packages. An index is a database object intended to improve the performance of SELECT queries. Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces. Each piece of the database object is called a partition. Each partition has its own name and may optionally have its own storage characteristic. A view is a representation of a SQL statement that is stored in memory, so that it can be reused. A view is a logical entity or logical table that is essentially a SQL statement stored in the database in the SYSTEM tablespace. The Oracle SEQUENCE function allows you to create auto-numbering fields by using sequences. An Oracle Sequence is an object that is used to generate incrementing or decrementing numbers.

In Oracle PL/SQL, the term synonym refers to a schema object which is created by a user to access an object that is owned by another user. In the process of creating a synonym, the user seeking the object access must first create a synonym for the required object. The owner user must then grant access on the synonym to the seeking user. In PL/SQL, a package is a group of programmatic constructs combined into a single unit.

Using SQL to Query a Database

In a relational database, we do not specify the access route to tables and we do not need to know how the data is arranged physically. SQL is a set of statements which all programs and users can use to access data in the Oracle Database. SQL is an ANSI standard for using relational databases and is compliant with the ISO standard SQL:1999. It is efficient and easy to learn and use. Applications in Oracle tools allow users to access the database without using SQL directly. These applications in turn must use SQL when it executes the user's request.

The tasks that can be performed using SQL include querying data, insert, update, and delete of rows in a table; we can create, replace, alter or drop objects; we can control the access to the database and its objects. SQL statements guarantee database consistency and integrity. SQL combines all the tasks listed into one consistent language, and it thereby enables you to work with data at a logical level.

The SQL statements supported by Oracle comply with industry standards and Oracle ensures future compliance by actively involving key personnel in the SQL standards committee. These industry-accepted committees are ANSI and ISO. Both have accepted SQL as the standard language for relational databases.

SQL Statement Categories

Looking at the SQL statements, the SELECT, INSERT, UPDATE, DELETE, and MERGE statements retrieve data from the database, enter new rows, change existing rows, and remove unwanted rows from tables in the database. This is collectively known as Data Manipulation Language, or DML. The second set of statements, CREATE, ALTER, DROP, RENAME, TRUNCATE, and COMMENT, set up, change, and remove data structures from tables. This is collectively known as Data Definition Language, or DDL. The GRANT and REVOKE statements provide or remove access rights to both the Oracle Database and its structures, also known as Data Control Language, DCL. The COMMIT, ROLLBACK, and SAVEPOINT statements manage the changes made by DML statements. Changes to the data can be grouped together into logical transactions.

Human Resources Schema

The Oracle Database provides a set of interlinked sample schemas. Some of these schemas are HR, OE, OC, PM, IX, and SH. The sample schemas are based on the following design principles: simplicity and ease of use, relevance for typical users, and extensibility. These sample schemas are based on a fictitious sample company that sells goods through various channels. The company operates worldwide to fill orders for products and has several divisions, each of which is represented by a sample schema in the database.

The HR schema is the human resources division, and it tracks information about the company employees and facilities. The OE schema is the order entry division, and it tracks product inventories and the sales of products through various channels. The PM schema is the product media division, and it maintains description and detailed information about each product sold by the company. The IX schema is the information exchange division; it manages shipping through B2B applications. The SH schema is the sales division and it facilitates business decisions by tracking business statistics. Depending on how the database is created, these schemas may or may not exist in the database. It is not recommended for these schemas to be created in a production environment. Should the sample schemas not exist, they can be installed manually, taking into consideration that due to dependencies between the schemas, they will need to be created in a specific sequence. Refer to the sample schemas guide in the Oracle Database online documentation for how to install these schemas.

HR Schema Tables

In this learning path, we will make use of the Oracle human resources sample schema. The HR schema consists of the following tables. The REGIONS table contains rows representing regions – for example, America and Asia. The COUNTRIES table contains rows for each country associated with a region. The LOCATIONS table contains addresses of specific offices, warehouses, and production sites of the company in a particular country. The DEPARTMENTS table shows details about the departments in which the employees work. The EMPLOYEES table contains details about each employee working in a department; some employees may not be assigned to any department. The JOBS table contains job types that can be held by an employee. The JOB_HISTORY table contains a job history for the employees. When an employee changes departments within a job or changes jobs within a department, a new row is inserted into this table.

Looking in a bit more detail at some of the tables, the first table is the COUNTRIES table. In the top block you will see the definition of the table, and in the bottom block you will see the data that exists in this table. In the COUNTRIES table we have a COUNTRY_ID, a COUNTRY_NAME, and the REGION_ID. The next table is the DEPARTMENTS table. In the DEPARTMENTS table we have the DEPARTMENT_ID, the DEPARTMENT_NAME, the MANAGER_ID, and the LOCATION_ID. The last table is the EMPLOYEES table. This table holds all the information specific to an employee, such as the employee ID, their first and last name, their e-mail address, a phone number for the employee, a hire date, a job ID, their salary, a commission percentage, the manager ID, and the department ID.

Conclusion

This comprehensive guide has explored the many facets of Oracle Databases, from the foundational concepts of infrastructure grids and information management to the practical details of the Human Resources sample schema. By understanding the features of Oracle Database 12c, the capabilities of Oracle Cloud and Enterprise Manager Cloud Control, and the principles of relational and object relational database design, you are well-equipped to work effectively with Oracle's powerful database technology. The sample schemas provide a hands-on way to practice and apply these concepts, making it easier to translate theoretical knowledge into practical skills.