SQL/Schemata

Last updated

The SQL/Schemata, or Information and Definition Schemas, part of the SQL standard is defined by ISO/IEC 9075-11:2008. SQL/Schemata defines the information schema and definition schema, providing a common set of tools to make SQL databases and objects self-describing. These tools include the SQL object identifier[ clarify ], structure[ clarify ] and integrity constraints, security and authorization specifications[ clarify ], features and packages[ clarify ] of ISO/IEC 9075, support of features provided by SQL-based DBMS implementations, SQL-based DBMS implementation information and sizing items[ clarify ], and the values supported[ clarify ] by the DBMS implementations. [1] SQL/Schemata defines a number of features, some of which are mandatory.

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data where there are relations between different entities/variables of the data. SQL offers two main advantages over older read/write APIs like ISAM or VSAM. First, it introduced the concept of accessing many records with one single command; and second, it eliminates the need to specify how to reach a record, e.g. with or without an index.

SQL:2008 is the sixth revision of the ISO and ANSI standard for the SQL database query language. It was formally adopted in July 2008. The standard consists of 9 parts which are described in detail in SQL.

In relational databases, the information schema (information_schema) is an ANSI-standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information which some databases make available through non-standard commands, such as:

 => SELECT count(table_name) FROM information_schema.tables;  count   -------  99    => SELECT column_name, data_type, column_default, is_nullable  FROM information_schema.columns WHERE table_name='alpha';  column_name | data_type | column_default | is_nullable   -------------+-----------+----------------+-------------  foo | integer | | YES  bar | character | | YES    => SELECT * FROM information_schema.information_schema_catalog_name;  catalog_name   --------------  johnd

Contents

See also

SQL:2003 is the fourth revision of the SQL database query language. The standard consists of 9 parts which are described in detail in SQL. It was updated by SQL:2006.

A data dictionary, or metadata repository, as defined in the IBM Dictionary of Computing, is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format". Oracle defines it as a collection of tables with metadata. The term can have one of several closely related meanings pertaining to databases and database management systems (DBMS):

Related Research Articles

Database organized collection of data

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques.

Object-relational database database management system

An object-relational database (ORD), or object-relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data types and methods.

The Call Level Interface (CLI) is an application programming interface (API) and software standard to embed Structured Query Language (SQL) code in a host program as defined in a joint standard by the International Organization for Standardization (ISO) and International Electrotechnical Commission (IEC): ISO/IEC 9075-3:2003. The Call Level Interface defines how a program should send SQL queries to the database management system (DBMS) and how the returned recordsets should be handled by the application in a consistent way. Developed in the early 1990s, the API was defined only for the programming languages C and COBOL.

In database systems, isolation determines how transaction integrity is visible to other users and systems. For example, when a user is creating a Purchase Order and has created the header, but not the Purchase Order lines, is the header available for other systems/users to see?

Referential integrity

Referential integrity is a property of data stating that all of its references are valid. In the context of relational databases, it requires that if a value of one attribute (column) of a relation (table) references a value of another attribute, then the referenced value must exist.

HSQLDB

HSQLDB is a relational database management system written in Java. It has a JDBC driver and supports a large subset of SQL-92, SQL:2008, and SQL:2011 standards. It offers a fast, small database engine which offers both in-memory and disk-based tables. Both embedded and server modes are available.

An XML database is a data persistence software system that allows data to be specified, and sometimes stored, in XML format. This data can be queried, transformed, exported and returned to a calling system. XML databases are a flavor of document-oriented databases which are in turn a category of NoSQL database.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view. In some NoSQL databases, views are the only way to query data.

Null (SQL) special marker and keyword in SQL

Null is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. In SQL, NULL is a reserved word used to identify this marker.

The object-relational impedance mismatch is a set of conceptual and technical difficulties that are often encountered when a relational database management system (RDBMS) is being served by an application program written in an object-oriented programming language or style, particularly because objects or class definitions must be mapped to database tables defined by a relational schema.

Simple Features is both an Open Geospatial Consortium (OGC) and International Organization for Standardization (ISO) standard ISO 19125 that specifies a common storage and access model of mostly two-dimensional geometries used by geographic information systems.

Database administration is the function of managing and maintaining database management systems (DBMS) software. Mainstream DBMS software such as Oracle, IBM DB2 and Microsoft SQL Server need ongoing management. As such, corporations that use DBMS software often hire specialized information technology personnel called database administrators or DBAs.

International standards in the ISO/IEC 19770 family of standards for IT asset management (ITAM) address both the processes and technology for managing software assets and related IT assets. Broadly speaking, the standard family belongs to the set of Software Asset Management standards and is integrated with other Management System Standards.

The SQL/MED extension to the SQL standard is defined by ISO/IEC 9075-9:2008. SQL/MED provides extensions to SQL that define foreign-data wrappers and datalink types to allow SQL to manage external data. External data is data that is accessible to, but not managed by, an SQL-based DBMS. This standard can be used in the development of federated database systems.

The following tables compare general and technical information for a number of available database administrator tools. Please see individual product articles for further information. This article is neither all-inclusive nor necessarily up to date.

SQL:1999 was the fourth revision of the SQL database query language. It introduced a large number of new features, many of which required clarifications in the subsequent SQL:2003. In the meanwhile SQL:1999 is deprecated.

SQL:2016 or ISO/IEC 9075:2016 is the eighth revision of the ISO (1987) and ANSI (1986) standard for the SQL database query language. It was formally adopted in December 2016. The standard consists of 9 parts which are described in some detail in SQL.

References

  1. ↑ ISO/IEC 9075-11:2008: Information and Definition Schemas (SQL/Schemata), 2008, p. 1


This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.