UniVerse and SQL Databases - SQL Database

Comparing UniVerse with conventional SQL at the database level involves twomajor areas: the concept and structure of the database itself, and the data model onwhich it is based. The differences between UniVerse and SQL are summarized in thefollowing table, and then discussed in greater detail.

Database Concepts and Structures

UniVerse SQL associates a database with a schema, which is created using a CREATE SCHEMA statement, and defines that database in the SQL catalog tables. In UniVerse SQL, a database comprises one or more tables in a schema.

Data Models

UniVerse uses a three-dimensional file structure, commonly called a nonfirst-normal-form (NF2) data model, to store multivalued fields. This enables a single table to contain information that would otherwise be scattered among several related tables. Related multivalued columns can be grouped together in an association, which can be thought of as a “table within a table” or a nested table. Conventional SQL uses a two-dimensional table structure called a first normal form(1NF). Instead of using multivalued columns, it tends to use smaller tables that arerelated to one another by common key values. However, the UniVerseimplementation of SQL has added enhancements that allow you to store and processmultivalued columns.
The implications of these differences in data modeling and the relational design ofSQL are discussed further under “Table and File Structures”.

UniVerse Tables and Files

Tables are implemented as UniVerse files and can be accessed by UniVersecommands as well as by SQL statements. UniVerse tables and files share the following characteristics:

  • The CREATE TABLE statement is similar in function to the UniVerse

CREATE.FILE command.

  • Each UniVerse table or file actually comprises two files: a data file and adictionary.
  • The data structures of tables and files are comparable, although UniVersefiles are described as containing fields and records, and tables as containingcolumns and rows. Under the UniVerse implementation of SQL, tables cancontain multivalued columns.
  • Both tables and files can be accessed using either SQL statements orUniVerse commands and processes.

There are also differences. The following table summarizes the relationship betweentables and UniVerse files

Table Comparison of Traditional UniVerse Files to Tables

Table and File Structures

UniVerse is a nonfirst-normal-form database that permits more than one value in acell (a row-and-column position that can hold more than one data value). StandardSQL works with first-normal-form databases, which store only one value for everyrow and column (singlevalued columns), but UniVerse SQL can store and processmultivalued columns also.
SQL is relationally oriented and allows you to access multiple tables by joining themon common values or keys as if they were one table. For example, using SQL aretailer can inquire about an inventory item (in an INVENTORY table) and itssupplier (in a DISTRIBUTOR table), provided that the INVENTORY table has adistributor code column that can be used to join it to the DISTRIBUTOR table.
UniVerse without SQL is designed primarily for accessing one file at a time, althoughyou can use the TRANS function or the Tfile correlative to extract information froma second file. But with UniVerse SQL you can use a SELECT statement to joinmultiple tables and UniVerse files in any combination.

Security and Authorization

In addition to the operating system’s security provisions (controlling read/writeaccess to files), SQL allows you to grant or revoke privileges based on user, table,and operation (retrieving or selecting data, and inserting, modifying, and deletingrows).
SQL also provides three levels of database privilege. From the lowest to the highest,they are as follows:

  • CONNECT lets you create your own tables and do whatever you want withthem (including granting your “owner” privilege to other users).
  • RESOURCE lets you create your own schemas plus do everything allowedunder CONNECT.
  • DBA (a sort of superuser level) lets you do everything, including reading orwriting to anyone else’s tables.

Data Integrity

In UniVerse, data integrity is provided by certain conversion operations (such as dateconversions) that flag illegal values by returning an error STATUS code. SQL hasmany additional data integrity constraints, including referential integrity and checksfor null values, empty columns, nonunique values, and user-defined conditions suchas avalue ranges.

Primary Keys

The UniVerse file structure has a single-column primary key (record ID), whereasSQL allows for either single-column or multicolumn primary keys.

Data Categories and Data Types

Unlike a field in a UniVerse file, a column in a table is defined as belonging to aparticular data type. A data type defines a column in terms of the valid set of datacharacters that can be stored in the column, the alignment of the data, conversioncharacteristics, and so on. The UniVerse SQL Reference discusses data typesextensively.

Data types can be grouped into seven data categories. The following table summarizes these data categories

Table UniVerse SQL Data Categories

UniVerse SQL Data Types

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Database Topics