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.
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:
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:
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.
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
SQL Database Related Interview Questions
|SQL Server 2000 Interview Questions||PL/SQL Interview Questions|
|T-SQL Interview Questions||SQL Server 2008 Interview Questions|
|SQL Server 2005 Interview Questions||SQL Database Interview Questions|
|MySQL Interview Questions||DB2 Using SQL Interview Questions|
|PHP+MySQL Interview Questions||PostgreSQL Interview Questions|
|SQL DBA Interview Questions||SQL Interview Questions|
|Software testing Interview Questions||Oracle Application Framework Interview Questions|
SQL Database Related Practice Tests
|SQL Server 2000 Practice Tests||PL/SQL Practice Tests|
|T-SQL Practice Tests||SQL Server 2008 Practice Tests|
|SQL Server 2005 Practice Tests||SQL Database Practice Tests|
|MySQL Practice Tests||DB2 Using SQL Practice Tests|
|PHP+MySQL Practice Tests||PostgreSQL Practice Tests|
|SQL DBA Practice Tests||SQL Practice Tests|
|Software testing Practice Tests|
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.