Subscribe

Technology

Community

Features

Up Front

Comment

Share

Share

Application Development

Rapid Data Model Development

Quickly create data models for any application with Quick SQL.

By Joel Kallman

March/April 2019

Oracle Application Express (Oracle APEX) is the low-code development framework of Oracle Database. Oracle APEX enables the easy creation of modern and responsive web apps with no code. It is ideal for creating new user interfaces on top of existing data models or application logic in the database, but it is also widely used for creating opportunistic applications: brand-new applications that have loosely defined requirements and need to be up and running as soon as possible.

Every application (regardless of technology) is only as good as its data model, and a poor data model will result in a poor application. Quick SQL, a new feature in Oracle APEX Release 18, is the ideal tool for modeling data and creating a physical data model in Oracle Database. This article focuses on how to use the Quick SQL feature embedded in Oracle APEX 18, but even if you’re not an Oracle APEX user, you can still use Quick SQL online for free, via Oracle Live SQL, the free site where you can learn, practice, and share examples of SQL and PL/SQL.

Now suppose you’ve been tasked to quickly build an application to manage the employees of your organization. In this Oracle Magazine article, you’re going to build a new data model to manage these employees and then build an Oracle APEX application on top of this new data model. Employees have jobs, and they also belong to a department, so you will need to model these entities as well.

This article’s sample is built in Oracle APEX 18.1. If you’re not already running Oracle APEX 18.1 or later, you can request a free workspace at apex.oracle.com. Alternatively, you can go to the Oracle Technology Network to download the Database App Development Virtual Machine, which includes a preconfigured Oracle Database 18c Enterprise Edition database, Oracle APEX 18.2, Oracle REST Data Services, Oracle SQL Developer, and Oracle SQL Developer Data Modeler.

Designing the Initial Table

Begin your exploration of Quick SQL by modeling the departments table.

  1. In Oracle APEX, click the SQL Workshop tab.
  2. Click the Utilities icon, and then click the Quick SQL icon. (At livesql.oracle.com, click Quick SQL on the menu.)
  3. In the left text area (Quick SQL Shorthand), type departments and then press the Enter key on your keyboard. This is your table name.
  4. Type two spaces, type name, and then press Enter.
  5. Type two spaces, type cost center, and then press Enter.

The Quick SQL shorthand you entered should result in this:

departments
  name
  cost center

By indenting two spaces under departments, you are using the shorthand syntax of Quick SQL to specify the column names of the departments table. As you press Enter after each new line, Quick SQL will dynamically update the database definition language (DDL) of your data model in the right-hand text area (Oracle SQL Output). With three simple lines in Quick SQL, you now have DDL for the departments table, a primary key for the departments table, and a trigger to populate the primary key column with a globally unique ID (GUID). (At livesql.oracle.com, the default settings are different, so your DDL may not match this description exactly.)

Now update the data types and lengths for the name and cost center columns:

  1. Type vc100 after name, to change the length of the VARCHAR2 data type of the column to 100 characters.
  2. Type /nn after name vc100, which is a modifier in Quick SQL to mark a column as not null (always requiring a value).
  3. Type num after cost center to change the data type of the column to NUMBER.
  4. Click the Generate SQL button.

The Quick SQL shorthand you entered should now result in this:

departments
  name vc100 /nn
  cost center num

To complete the departments table, you will want to normalize the values in the name column to all uppercase, apply a unique constraint (to ensure that the name values of all rows in the table are unique), and supply the domain of values for the name column.

  1. Type /upper /unique at the end of the name column line, followed by /check hr, development, legal, sales, operations.
  2. Click Generate SQL.

The Quick SQL shorthand you entered should now result in this:

departments
  name vc100 /nn /upper /unique /check hr, development, legal, sales, operations
  cost center num

With this little bit of Quick SQL shorthand, you have a departments table with a named primary key, unique and check constraints, and a database trigger to generate the primary key value and normalize the name column to uppercase. Pretty easy. But it gets better.

Adding a Child Table

About Oracle Application Express

Oracle Application Express (Oracle APEX) is a high-productivity, low-code platform for creating modern, responsive, and accessible web applications. A no-cost feature of Oracle Database, it is a compelling application development platform available in all Oracle Database Cloud services.

One of the hallmarks of relational databases is the ability to declaratively express relationships between tables. These are typically expressed via referential integrity constraints and are used to ensure that the data represented in associated tables is consistent. To put this into practical terms, you will now define a table for employees, and the employees table will be a child of the parent departments table. You will not be able to insert a row into the employees table and reference a department that does not exist.

Defining a child table in Quick SQL is easy. All you need to do is indent the lines of the child table directly underneath the parent table.

  1. Start a new line after the last line of the departments table definition, indent two spaces, and type employees.
  2. On the next line, indent four spaces and type first_name. Continue adding new lines, indenting each line four spaces, and type the column names last_name, email, hire_date, city, and country.
  3. Convert the email column to lowercase, by adding /lower after the email column.
  4. Click Generate SQL.

The Quick SQL shorthand you entered should now result in this:

departments
  name vc100 /nn /upper /unique /check hr, development, legal, sales, operations
  cost center num
  employees
    first_name
    last_name
    email /lower
    hire_date
    city
    country

Review the generated DDL in the Oracle SQL Output text area. You now have a second table for employees; a department_id column with a named foreign key constraint to the employees table; and, following best practices, an index on the column of the foreign key constraint (department_id).

Adding a Second Related Table, Generating Data, and Exploring Options

As you just saw, you can represent the relationship between a parent table and a child table with simple indentation in Quick SQL. But what if there are multiple relationships to the same table? You can easily represent those relationships via the /references column modifier, which is used to explicitly define a relationship between tables.

Each employee has a job. Now define the jobs in a separate table.

  1. At the beginning of the Quick SQL shorthand, enter the definition of the jobs table as 
    jobs
  name /upper /unique /check developer, analyst, manager, assistant
  2. At the end of the employees table, add the following line as a new column of the employees table: 
    job id / references jobs.

The Quick SQL shorthand you entered should result in this:

jobs
  name /upper /unique /check developer, analyst, manager, assistant
departments
  name vc100 /nn /upper /unique /check hr, development, legal, sales, operations
  cost center num
  employees
    first_name
    last_name
    email /lower
    hire_date
    city
    country
    job id /references jobs

The employees table now has an additional named foreign key constraint referencing the new jobs table.

There are several settings you can use to control the generation of the DDL and also enable additional features. You can, for example, specify a prefix to use for all object names, change the primary key value generation to use identity data types, and even generate a PL/SQL API.

Now enable the Audit Columns feature:

  1. Click the Settings link.
  2. In the Additional Columns section, select Audit Columns.
  3. In the Options section, change APEX Enabled to Yes.
  4. Click the Save Changes button.

Selection of the Audit Columns option means that every table now includes four additional columns to track who created or updated the row and when. The database triggers for the tables have also been updated to automatically populate the audit columns. Setting APEX Enabled to Yes affects the Audit Columns logic so that the authenticated username of an Oracle APEX application is correctly referenced in the database trigger code.

It’s far easier to prototype an application when you can see it with data, even if it’s dummy data. Fortunately, Quick SQL has an easy facility for generating sample data with the /insert table directive. After every table, you can append /insert NN, where NN is the number of rows of data to generate.

Now use Quick SQL to generate some sample data for the tables:

  1. After the jobs table, add /insert 4.
  2. After the departments table, add /insert 5.
  3. After the employees table, add /insert 100.
  4. Click Generate SQL.

The Quick SQL shorthand you entered should now result in this:

jobs /insert 4
  name /upper /unique /check developer, analyst, manager, assistant
departments /insert 5
  name vc100 /nn /upper /unique /check hr, development, legal, sales, operations
  cost center num
  employees /insert 100
    first_name
    last_name
    email /lower
    hire_date
    city
    country
    job id /references jobs

And there you have it. With 14 lines of Quick SQL shorthand, you have now generated a well-formed and properly named data model—shown in Figure 1—with three tables, indexed foreign-key constraints, named check constraints and unique constraints, database triggers to audit changes made to the tables, and sample data. More than 2,000 lines of SQL script. Amazing!

apex figure 1

Figure 1: The completed data model and SQL script

Creating the Oracle APEX Application

If you’re using Quick SQL within Oracle APEX, you can save and execute the statements of your generated SQL script to quickly create an Oracle APEX application on this data model.

  1. Click the Save SQL Script button.
  2. For Script Name, type QuickSQLDemo and click Save Script.
  3. Click Review and Run.
  4. Click Run, and then click Run Now.

After the tables and triggers have been created and the sample data inserted, it’s time to create your application.

  1. Click Create App from Script.
  2. For Name, type Employee Manager.
  3. In the Features region, click Check All.
  4. Click Create Application.
  5. Click the Run Application icon, and log in to your application.

You can navigate among the different pages of your application, with reports and forms on your new tables. Note that on the form for editing employees, the job and department fields are controlled via a select list, constraining the values to those defined in the related tables. With 14 lines of Quick SQL shorthand and no code, you now have a 28-page responsive web application, shown in Figure 2, complete with user feedback, activity monitoring, and access control!

apex figure 2

Figure 2: The Oracle APEX application on the new data model

Conclusion

An application is only as good as its data model. Although Quick SQL is not a replacement for the sophisticated data modeling capabilities of Oracle SQL Developer Data Modeler, rapid and concise data model development with Quick SQL is ideal for many opportunistic applications. Available as a service for all (via livesql.oracle.com) and tightly integrated with Oracle APEX, Quick SQL enables you to quickly generate complete and accurate data models, along with sample data. Using the combination of the low-code rapid application development of Oracle APEX and Quick SQL is an excellent way to design and develop your next opportunistic application.

Next Steps

DOWNLOAD Oracle Application Express 18.2.

LEARN more about Oracle APEX.

TRY
Oracle Database Cloud services.
Oracle Live SQL.

REQUEST a free Oracle APEX workspace.

Illustration by Wes Rowell

Joel Kallman

Joel R. Kallman is Senior Director of Software Development for Oracle's Server Technologies division at Oracle America, Inc.  He is responsible for the development and product management of Oracle Application Express.  Joel has been at Oracle since 1996 and is the co-creator of Oracle Application Express.  He is a contributing author of several books on Oracle technology, including Expert One on One Oracle, Beginning Oracle Programming, and Mastering Oracle PL/SQL: Practical Solutions.   Joel holds a Bachelor of Science degree in Computer and Information Science from the College of Engineering, Ohio State University.

Oracle

Integrated Cloud & Platform Services

Integrated Cloud and Platform Services