Understanding Relational Data Model

The primary purpose of a database system is to model and automate an existing business process while maintaining data integrity. For example, imagine you're a database developer tasked with streamlining employee record managements for HR processionals. Using a relational data model, you can achieve this by organizing related pieces of information, such as employee details, performance evaluations, and payroll data, into tables and columns that reference one another. 

To start, you first need to identify the basic "things" of the business, referred to as the entities, which in this scenario could include employee details, hire and termination dates, or salary amounts. In this scenario, these could include employee details and departments. For example, you might create an employees table with columns for employee_ID, name, and department_id. Another table, departments, would contain columns for department_id, department_name, and location_id. By designing the employees table to reference existing department_id values from the departments table, you can minimize the data storage and ensure consistency, as all department_id entries in the employees table will be validated against the departments.department_id column.

A relational database is a tangible implementation of the relational data model; it is a software system used to create, manage, and manipulate data using a database management system (DBMS). The Oracle database is one such relational database management system (RDBMS), offering a robust platform for handling large volumes of data and supporting complex queries and transactions.

Entity-Relationship Diagrams

An ERD, or Entity-Relationship Diagram, is a visual representation of how data is organized and related within a relational database system. It serves as a blueprint that helps database designers and developers understand the structure of the database. Using Oracle SQL Developer, let's draw an ERD based on the Oracle sample schemas as follows:

1. Launch the SQL Developer and navigate to "File" → "Data Modeler" → "Import" → "Data Dictionary." 
2. In the Data Dictionary Import wizard that appears, select the database connection and click "Next." 
3. Check one or more schema names and click "Next."
4. Check one or more schema objects you want to import and click "Next." 
5. Review the Data Modeler design summary and click "Finish."

For example, let's consider the ERD shown in the figure below, which is based on the hr sample schema we installed in a previous guide. This ERD illustrates how entities in the hr schema are stored and organized. Each yellow box represents a table, with the list of columns inside, along with their data types and restrictions. In the departments table, for example, there are four columns: department_id, department_name, manager_id, and location_id. Observe that the department_id column is marked as 'P,' indicating that the column serves as the primary key for the table. That is, each row in the table is uniquely identified by the department_id column. On the other hand, manager_id and location_id are marked as 'F,' signifying that these two columns are referenced by other tables.

This primary-foreign key relation is the fundamental characteristic of the relational database. Foreign keys ensure that data in one table (child table) has a corresponding record in another table (parent table) referenced by the primary key. For example, the location_id column in the departments table (child) has a corresponding value in the locations table (parent.) This prevents any orphaned data, that is the departments table cannot have any location_id values that do not exist in the locations table.

The relationship between two tables can be one-to-one, one-to-many, or zero-to-many depending on your business rules. For example, you might allow any single job title to be associated with one or more employees (one-to-many). This relationship is represented as a line with crow foot symbol on the ERD.

Conversely, you would restrict an employee to having only one job title, ensuring each employee is assigned a title (one-to-one). A one-to-one relationship between two tables is where each record in the first table is associated with one and only one record in the second table. This is represented as a line with no crow foot symbol. In practice, one-to-one relationships are typically used to split large tables into smaller, more manageable entities, or isolate sensitive data into separate tables.

In the ERD shown above, notice that 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: any employee records included in the job_history table (child) must have at least one corresponding records in the employees (parent). On the other hand, dashed arrows represent optional relationships. For example, a dashed one-to-many arrow pointing from departments to employees indicates that each department may or may not have an employee, representing a zero-to-many relationship.

Creating an ERD from Scratch

You can also draw an ERD from scratch by importing each schema objects (e.g., tables, views, etc.) into the data model. Navigate to "'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 the new ERD.

On the canvas, you can drag-and-drop tables listed in the upper right corner. Oracle SQL Developer will automatically draw arrows based on the relationships defined by the constraints (primary and foreign key relationships added by DDL commands).

Database Objects

An Oracle database consists of various database objects, categorized as either schema or non-schema objects. Schema objects are those that make up the logical structure of how data will be stored and organized. These includes tables, views, indexes, and sequences. Non-schema objects, on the other hand, encompass system-level entities like roles, users, and directories that manage access, configuration, and functionality across the database. Roughly speaking, schema objects are those that can be directly owned by a specific user account, whereas non-schema objects are owned by database itself and cannot be attributed to individual users.

While there are many mores, the following list comprises some of the most essential ones for creating a functional database application:

• Schema Objects
• TABLE: A tabular structure designed for data storage, enforcing a rigid grid with explicit data types assigned to each column. 
• INDEX: An object used to accelerate data retrieval from tables. It is analogous to a phone book that organizes names alphabetically for faster searching.
• VIEW: A virtual table created from query expressions. Unlike a table, view does not physically store data; instead, it provides a dynamic window into one or more underlying tables.
• SEQUENCE: Serves as a counter commonly used to automatically generate unique identifiers for new rows when they are added to a table.
• PRIVATE SYNONYM: Serves as an alias for another database object, often used to provide an alternative name for a table or view. 
• CONSTRAINT: Establishes custom validation rules for table columns.
• Non-Schema Objects
• USERS: This is the entity who creates and owns schema objects. 
• ROLES: This is a set of one or more privileges that can be granted to a user, defining their access and capability within the database.
• PUBLIC SYNONYM: Synonyms that are owned and accessible by all users. It does not belong to any user schema. 

Unique Names and Namespaces

A namespace is a conceptual boundary in a database that encapsulates a specific group of database objects. Multiple namespaces operate concurrently, contingent upon the context in which you are working. In essence, you must assign a distinct name to each object within its dedicated namespace. 

• USER and ROLE objects are in their own collective namespace.
• PUBLIC SYNONYM objects maintain a separate namespace, exclusive to themselves.
• TABLE, VIEW, SEQUENCE, PRIVATE SYNONYM, and user-defined TYPE objects collectively exist within a unique namespace specific to a given schema.
• INDEX objects occupy their own namespace within a given schema.
• CONSTRAINT objects have an exclusive namespace within the confines of a given schema.

Aside: Describe the Table Structures and Dot Notations

It is often necessary to describe how a table and its columns are defined. To achieve this, one can use the DESCRIBE statement, commonly abbreviated as DESC. It isn't an SQL statement^[3]; it's a SQL*Plus statement which is an interactive command-line tool included with Oracle database installation. So, unlike any other SQL statements, the DESC statement does not require a semicolon at the end. 

Syntax of the DESC statement is very simple: place the table name of your interest after the keyword DESC. For example:

If you are describing a table outside of the schema where it is stored, you can use the dot notations. For example:

In this example, we are describing the employees table owned by the hr schema from another schema. So, the hr schema is specified first, followed by the table name, separated by a dot delimiter. Note that only the schema who has the read access can describe the table; otherwise, Oracle throws an error:

Observe that while the co schema has a table named customers, DESC co.customers submitted from hr schema raised an error. This is because hr doesn't have a read access to co schema objects.

The use of dot notation is not limited to this context. In general, it can specify and access any database objects within a hierarchical structure, including schemas, tables, and columns. For example, it can also be used to reference columns within a table, like table_name.column_name.