Understanding Relational Data Model

The primary purpose of a database is to support an existing business process and model an real-world system. For example, imagine an HR professional responsible for processing monthly payrolls. To streamline this task and manage its associated data efficiently, the company would likely construct a data model that reflects its actual payroll operations. In developing this data model, the company must first identify the basic "things" of the business, referred to as the entities, which in this scenario can be employee names, timekeeping records, or salary amounts, for example. 

The relational data model is a conceptual structure of entities organized in the form of tables and relations between the tables. Key components include tables, columns, and relationships. For example, consider one table with columns for employee ID, name, and department, and another table with columns for employee ID, salary amount, and pay date. By relating these two tables based on the shared employee IDs, the database can minimize the data storage and ensure data consistency. A relational database is an implementation of the relational data model. It is a software system used to create, manage, and manipulate data using a database management system (DBMS).

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 is 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."

In the previous article, we installed Oracle sample schemas CO, HR, and SH. Following the instructions above, we can draw an ERD as below. This ERD shows how entities in the HR schema is stored and organized. Each yellow box represents a table. Within a box, we can see the list of columns, 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 DEPARTMENT_ID column is marked as P. This means that the DEPARTMENT_ID column serves as the primary key for the table. That is, each row in the table is uniquely identified by the column value. On the other hand, MANAGER_ID and LOCATION_ID are marked as F, meaning the two columns are referenced by other tables. 

This primary key-foreign key relation is the fundamental characteristic of the relational databases. 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. Conversely, you would typically restrict an employee to having only one job title, ensuring each employee is assigned a title.

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. Put simply, 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.

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. On an ERD, this relationship would be shown 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.

If you want to draw an ERD from the draft, 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. 

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 between the tables.

Database Objects

The database objects are foundational units for managing a relational database. While there are many mores, the following list comprises some of the most essential ones for creating a functional database application:

• TABLE: This is a tabular structure designed for data storage, enforcing a rigid grid with explicit data types assigned to each column. This ensures consistency and predictability in data representation.
• INDEX: It accelerates data retrieval from tables by referencing specific values and their location, just like a phone book that organizes names alphabetically for faster searching.
• VIEW: This is a virtual table created from query expressions. Unlike a table object, a VIEW object does not physically store data; instead, it provides a dynamic window into one or more underlying tables, presenting a customized perspective based on a pre-defined query.
• SEQUENCE: A SEQUENCE object works as a counter commonly used to generate unique identifiers for new rows as they are added to a table.
• SYNONYM: It serves as an alias for another database object, often used to provide an alternative name for a table or view. 
• CONSTRAINT: This establishes custom validation rules within a table to safeguard data quality and maintain referential integrity.
• USERS: The USERS object can create and own schema objects, such as tables, views, or indexes. 
• 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.

Schema and Schema Objects

In Oracle Database, schema objects are logical structure of data storage and can be defined by using SQL's Data Definition Language (DDL.) They include the following types of database objects:

• Schema Objects
• Tables
• Constraints
• Indexes
• Views
• Sequences
• Private synonyms
• Non-schema Objects
• Users
• Roles
• Public synonyms

Any database object is either a schema object or non-schema object. 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.

Schema objects in Oracle do not directly correspond one-to-one with physical files (.dbf files on disk) that store their information. Instead, Oracle logically organizes them into a schema. In a database, each schema is owned by a database user and has the same name as that user. Each user owns a single schema and has access to their own schema, as well as to others' schemas where privileges are granted. Conceptually, schemas might seem similar to user accounts, but there is a subtle distinction: a user account contains the database objects owned by a user, whereas a schema is the set of objects housed therein.

Naming Rules for Database Objects

The following naming rules apply to tables, views, indexes, and all the other database objects, including a table's constraints and columns:

• Length: Names must be between 1 and 30 characters in length.
• First Character: The first character must be a letter.
• Subsequent Characters: Allowed characters include letters, numbers, $, _, and #. No other special characters can be used.
• Case Sensitivity: Names are not case-sensitive.
• Reserved Words: Reserved words used in SQL statements (e.g., SELECT, CREATE) are prohibited. Refer to the Oracle SQL Language Reference Manual for the complete list of reserved words.

To override the rules described above, it is possible to create database objects with double quotations. Quoted names vary from non-quoted names in several ways; they can include any characters, have to be double quoted for retrieval, and are case-sensitive. However, Oracle strongly discourages overriding the naming rules. It may result in compatibility issues, as not all database tools, applications, and programming languages seamlessly support quoted identifiers, not to mention increased risk of typos for quotations.

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.