Which language is used to obtain data from database in application program?

III.A Relational Database Languages

SQL is the ISO/ANSI standard for a relational database language. SQL is both a data definition and a data manipulation language. It is also both a query language and capable of expressing updates. However, SQL is not computationally complete, since it offers no support for either recursion or iteration. As a consequence, when it comes to the development of applications, SQL is often embedded in a host language, either one that is specific to the DBMS being used or a general purpose language for which a query language interface is provided.

Figure 6 shows how the schema illustrated in Fig. 1 can be declared in SQL. Notice that SQL includes constructs to declare integrity constraints (e.g., a referential integrity on from DNA_sequence to organism) and even an action to be performed if it is violated (e.g., cascading deletions, by deleting every referring tuple when a referenced tuple is deleted).

SQL can also express insertions, deletions and updates, as indicated in Fig. 7. Note in Fig. 7 that on insertion it is possible to omit null values by listing only the attributes for which values are being supplied. Note also that the order of insertion in Fig. 7 matters, since referential integrity constraints would otherwise be violated. Finally, note that, because of cascading deletions, the final statement will also delete all tuples in DNA_sequence that refer to the primary key of the tuple being explicitly deleted in organism.

After the operations in Fig. 7 the state depicted in Fig. 2 will have changed to that shown in Fig. 8.

As a relational query language, SQL always returns results that are themselves relation instances. Thus, the basic constructs in SQL cooperate in specifying aspects of the schema as well as the instantiation of a query result. Roughly, the SELECT clause specifies the names of attributes to appear in the result, the FROM clause specifies the names of relations contributing data to the result, and the WHERE clause specifies, in terms of the relations (and their attributes) mentioned in the FROM clause, the conditions which each tuple in the result must satisfy. SQL queries tend to be structured around this combination of SELECT, FROM and WHERE clauses. Figure 9 shows example SQL queries.

QueryRQ1 in Fig. 9 returns a unary table, each tuple of which records the organism_id of organisms that share the common_name of “white_clover.” Query RQ2 returns a binary table relating each common_name found in the organism table with the protein_ids produced by their identified genes. Figure 10 shows the relations instances returned by RQ1 and RQ2.

SQL also supports aggregations (e.g., COUNT and AVG, which, respectively, count the number of tuples in a result and compute the average value of a numeric attribute in the result), groupings, and sorting.

Embedding SQL into a host language is another approach to retrieving and manipulating data from relational databases. Vendors typically provide a host language for SQL of which the fragment in Fig. 11 is an illustration. The fragment in Fig. 11 uses a CURSOR construct to scan the organism relation instance for organisms with no common name. When one is found, rather than leaving the value unassigned, the program uses the UPDATE construct to set the common_name attribute to the string None.

SQL is legally defined by ISO/ANSI standards which are available from those organizations. For comprehensive treatment, a good source is Melton and Simon, 1993. A detailed treatment of the relational algebra and calculi which underpin SQL can be found in Abiteboul et al. (1995).

III.B Object-Oriented Database Languages

In the object-oriented case, the separation between the languages used for data definition, querying and procedural manipulation is more explicit than in the relational case. This is because in object-oriented databases, the syntactic style of SQL is circumscribed largely to the query part of the data manipulation language.

Also, rather than make use of vendor-specific host languages, object-oriented DBMSs either provide interfaces to general-purpose languages or else the DBMS itself supports a persistent programming language strategy to application development (i.e., one in which a distinction between the data space of the program and the database is deliberately not made, which leads to applications that need not explicitly intervene to transfer data from persistent to transient store and back again).

The de facto standard for object-oriented databases is the proposal by the ODMG consortium of vendors. The ODMG standard languages are ODL, for definition, and OQL (which extends the query part of SQL), for querying. The standard also defines interfaces for a few widely used general-purpose languages. Figure 4 could be declared in ODL as shown in Fig. 12.

Note how ODL allows inverse relationships to be named, as a consequence of which referential integrity is enforced in both directions.

Two OQL queries over the gene class in Fig. 12 are given in Fig. 13. Query OQ1 returns a set of complex values, i.e., name-cited pairs, where the first element is the standard_name of an instance of the gene class and the second is the list of strings stored as the value of the citation attribute for that instance. Query OQ2 returns the common_name of organisms associated with genes that have alleles. Note the use of the COUNT aggregation function over a collection value. Note, finally, the denotation g.organism_id.common_name (known as a path expression). Path expressions allow a navigational style of access.

For ODMG-compliant DBMSs, the authoritative reference on ODL and OQL is (Cattell et al., 2000). A more formal treatment of some of the issues arising in object-oriented languages can be found inAbiteboul et al. (1995).

III.C Object-Relational Database Languages

The proposed standard for object-relational database languages is SQL-99. Figure 14 shows how Fig. 4 could be specified in SQL-99. Note the use of ROW TYPE to specify a complex domain, the use of REF to denote tuple identifiers and the use of type constructors such as SET and LIST. Note also that, unlike ODL (cf. Fig. 12), in SQL-99 inverse relationships are not declared. Note, finally, how gene is modeled as including operations, as indicated by the keyword FUNCTION introducing the behavioral part of the specification of gene.

Two SQL-99 queries over the gene type in Fig. 14 are given in Fig. 15. Query ORQ1 returns a binary table relating the standard_name of each gene with the common_name of organisms where the gene is found. Query ORQ2 returns the common_name of organisms associated with genes that have alleles. Note that in SQL-99 path expressions use the symbol -> to dereference identifiers and (not shown in Fig. 15) the symbol ‘..’ to denote attributes in row types.

SQL-99 is legally defined by ISO/ANSI standards which are available from those organizations.

12.9.1 Report Period Tables

Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses, so there is no way to get disagreements in the DML.

The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Something_Report_Periods

(something_report_name CHAR(10) NOT NULL PRIMARY KEY

 CHECK (something_report_name LIKE < pattern >),

 something_report_start_date DATE NOT NULL,

 something_report_end_date DATE NOT NULL,

CONSTRAINT date_ordering

CHECK (something_report_start_date <= something_report_end_date),

etc);

These report periods can overlap or have gaps. Avoid period names that are language dependent; they will have trouble porting. If possible the periods name should sort in temporal order. Again, I like the MySQL convention of using double zeroes for months and years, That is, ‘yyyy-mm-00’ for a month within a year and ‘yyyy-00-00’ for the whole year. The advantages are that it will sort with the ISO-8601 date format and will go to the top of each year and month within the year.

Overlapping periods are useful for reporting things like sales promotions. You can quickly see if the overlap between your “Bikini Madness Week” and “Three Day Suntan Lotion Promotion” helped increase total sales.

35.6.2.1 Report Period Table

Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses, so there is no way to get disagreements in the DML.

The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Something_Report_Periods

(something_report_name CHAR(10) NOT NULL PRIMARY KEY

CHECK (something_report_name LIKE < pattern >),

something_report_start_date DATE NOT NULL,

something_report_end_date DATE NOT NULL,

 CONSTRAINT date_ordering

CHECK (something_report_start_date <= something_report_end_date),

etc);

These report periods can overlap or have gaps. Giving a period a name is harder than you might think. I like the MySQL convention of using double zeroes for months and years, That is ‘yyyy-mm-00’ for a month within a year and ‘yyyy-00-00’ for the whole year. The advantages are that it will sort with the ISO-8601 data format required by Standard SQL and it is language independent. The pattern for validation is ‘[12][0-9][0-9][0-9]-00-00’ and ‘[12][0-9][0-9][0-9]-[01][0-9]-00’.

Top five toughest questions

1.

Which type of database language is used to create, modify, and/or delete tables?

Data Definition Language (DDL)

Data Manipulation Language (DML)

Database Management System (DBMS)

Structured Query Language (SQL)

A database contains an entry with an empty primary key. What database concept has been violated?

Entity Integrity

Normalization

Referential Integrity

Semantic Integrity

Which vulnerability allows a third party to redirect static content within the security context of a trusted site?

Cross-Site Request Forgery (CSRF)

Cross-Site Scripting (XSS)

PHP Remote File Inclusion (RFI)

SQL Injection

Which language allows CORBA (Common Object Request Broker Architecture) objects to communicate via a message interface?

Distributed Component Object Model (DCOM)

Interface Definition Language (IDL)

Object Linking and Embedding (OLE)

Object Management Guidelines (OMG)

Which database high-availability option allows multiple clients to access multiple database servers simultaneously?

Database commit

Database journal

Replicated database

Shadow database

2.6.1 Query-Based EPSs

Query-based EPSs typically support an EPL extended from the relational database language SQL to query event data. The queries expressed in a query-based EPL are often referred to as continuous/continual queries [27]. In contrast to traditional non-persisting queries that work on persistent data, continuous queries are stored persistently in the database and applied to event streams. The processing paradigm in such systems is:

Define queries in an SQL-like language

Process queries

Results of the processing step are only selectively stored in the database

In order to handle unbounded input streams, a common feature among these query-based languages is “the extensive operations on sliding windows” [28]. Sliding windows are used to divide the event stream into segments so that these segments can then be manipulated and analyzed without the system running into unlimited wait time and memory usage. There are different types of sliding windows [29]:

Time-driven model: The window is reevaluated only at the end of each time step. CQL [30] adopts this model.

Tuple-driven model: The window is reevaluated every time a new tuple arrives. StreamSQL [31] adopts this model.

Since CQL and StreamSQL adopt different sliding window models, not all queries that can be expressed in CQL can also be expressed in StreamSQL, and vice versa [29]. In any one particular query-based language, it is important to stick to the consistent semantics so that all implementations using this EPL work in a consistent manner and generate expected results.

Query-based EPLs are considered as good at defining patterns of “low-level aggregation views according to event types defined as nested queries” [32]. However, any of these languages have shortcomings when expressing event pattern types. For example, CQL does not have the ability of expressing windows with a variable slide parameter [29]. Additionally, when detecting occurrences of the same event pattern type, different query-based EPLs may generate different results, but the user does not have the power to control which result should be generated [33].

A more recent solution is Azure Stream Analytics from Microsoft, which supports CEP over streaming data in the cloud [34]. CEP processing is realized through an SQL-like language to specify streaming analytics tasks, such as correlation, aggregation, sliding windows and calculations over those windows, comparing current conditions to historical values, and so on [34].

6.7.4.3 Case-based machine translation

Case-based machine translation was first proposed in the 1990s by Japanese scholars. This method is based on case-based reasoning (CBR). In CBR, the problem or situation is referred to as the target case, while the memory of the problem or situation is called the source case. In simple terms, CBR is a solving strategy based on the hint of the target case to acquire the source case and solving the problem under the guidance of the source case. Therefore, the general idea of translation is as follows: A corpus consisting of a bilingual translation unit is created in advance. Then the search unit chooses a searching and matching algorithm, and the optimal matching unit is searched in the corpus.

If we want to translate the source language text “S,” the translation examples of S that need to be found in the bilingual corpus is similar to S. According to S', T is the translation case. And the translation result T is finally acquired. In general, the case-based machine translation system includes several steps, such as the pattern retrieval of candidate instances, sentence similarity computation, bilingual word alignment, and analogical translation. How to find the most similar translation examples from the source language text is the key problem of the case-based translation method. So far, researchers have not found a simple way to calculate the similarity between sentences. In addition, the evaluation of the sentence similarity problem still requires a lot of human engineering, language psychology, and other knowledge.

Case-based machine translation method is almost not needed to analyze and understand the source language. It only needs a relatively large sentence-aligned bilingual corpus, so it is easy to get the knowledge acquisition. If there are similar sentences in the corpus, the case-based method can get a good translation, and the more similar the sentences are, the better the translation effect is, and the higher the quality of the translation will be.

There is one more advantage for the case-based translation method. The knowledge representation of an instance pattern can be expressed in a concise and convenient way to express a large amount of human language.

However, the shortcomings of case-based machine translation are obvious. When a similar sentence is not found, the translation declares a failure. This requires that the corpus must cover a wide range of linguistic phenomena, for example, like the PanEBMT system of Carnegie Mellon University [25], which contains about 2,800,000 English and French bilingual sentence pairs. Although researchers use the PanEBMT system in a number of ways, for open text test, the coverage of translation of PanEBMT is only 70%. In addition, it is not easy to establish a high-quality, large bilingual sentence-aligned corpus, especially for minority languages.

Trados is desktop computer auxiliary translation software, which is based on a translation memory (TM) base and term base, which provides a complete set of tools for creating, editing, and checking high-quality translation [26]. The company of Trados GmbH was founded in 1984 by Hummel and Knyphausen in Germany. The company began to develop translation software in the late 1980s and released the first batch of Windows software in the early 1990s; they developed MultiTerm and Workbench Translators in 1992 and 1994, respectively. In 1997, thanks to Microsoft using Trados software for localization translation, the company became the desktop TM software industry leader in the late 1990s. Trados was acquired by SDL in June 2005.

SDL Trados Studio 2014 can work in the team to collect their translation in order to establish a language database (TM). In this database, the software is determined to be reused. When translators translate new content and meet with the translated sentences that are similar to or in the same sentence, the software automatically puts forward suggestions of reusable content. The features of SDL Trados Studio are as follows:

Based on the principle of TM, it is currently the world’s most famous professional translation software and has become the standard of professional translation.

It supports 57 languages two-way translation.

It greatly improves work efficiency, reduces costs, improves quality.

Its background is a powerful neural network database to ensure the security of the system and information.

It supports all popular document formats; users do not need to do layout (DOC, RTF, HTML, SGML, XML, FrameMaker, RC, AutoCAD DXF, etc.).

It improves the auxiliary functions, such as time, measurement, form, automatic replacement, and other fixed format, helping customers greatly improve work efficiency.

The interface is clear. Both the original and the translation are clearly displayed on both sides. It is able to customize the environment in a variety of ways: Keyboard shortcuts, layout, color, and text size can be customized, so as to maximize the comfort and work efficiency.

It provides the most extensive file format support, from the Office2013 Microsoft file to the complex XML file.

The study of cased-based machine translation, one of the main aspects of research, is to focus on how to improve the translation of the translation system under the relatively small number of the cases or on how to reduce the size of the case model to maintain the effectiveness of the translation. To achieve this goal, we need to extract as much linguistic knowledge as possible from the database of case patterns, including syntax, lexical knowledge, and semantic knowledge.

The Embedded SQL Environment

SQL statements can be embedded in a wide variety of host languages. Some are general-purpose programming languages such as COBOL, C++, or Java. Others are special-purpose database programming languages such as the PowerScript language used by PowerBuilder or Oracle's SQL/Plus, which contains the SQL language elements discussed in Chapter 14 as well as Oracle-specific extensions.

The way in which you handle source code depends on the type of host language you are using: Special-purpose database languages such as PowerScript or extensions of the SQL language (for example, SQL/Plus) need no special processing. Their language translators recognize embedded SQL statements and know what to do with them. However, general-purpose language compilers are not written to recognize syntax that isn't part of the original language. When a COBOL1 or C++ compiler encounters a SQL statement, it generates an error.

The solution to the problem has several aspects:

Support for SQL statements is provided by a set of program library modules. The input parameters to the modules represent the portions of a SQL statement that are set by the programmer.

SQL statements embedded in a host language program are translated by a precompiler into calls to routines in the SQL library.

The host language compiler can access the calls to library routines and therefore can compile the output produced by the precompiler.

During the linking phase of program preparation, the library routines used to support SQL are linked to the executable file along with any other library used by the program.

To make it easier for the precompiler to recognize SQL statements, each one is preceded by EXEC SQL. The way in which you terminate the statement varies from one language to another. The typical terminators are summarized in Table 15-1. For the examples in this book, we will use a semicolon as an embedded SQL statement terminator.

Table 15-1. Embedded SQL statement terminators

LanguageTerminatorAdaSemicolonC, C++SemicolonCOBOLEND-EXECFortranNoneMUMPSClose parenthesisPascalSemicolonPL/1Semicolon

Java and JDBC

Java is an unusual language, in that it is pseudo-compiled. (Language tokens are converted to machine code at runtime by the Java virtual machine.) It also accesses databases in its own way: using a library of routines (an API) known as Java Database Connectivity, or JDBC. A JDBC driver provides the interface between the JDBC library and the specific DBMS being used.

JDBC does not require that Java programs be precompiled. Instead, SQL commands are created as strings that are passed as parameters to functions in the JDBC library. The process for interacting with a database using JDBC goes something like this:

Create a connection to the database.

Use the object returned in Step 1 to create an object for a SQL statement.

Store each SQL command that will be used in a string variable.

Use the object returned in Step 2 to execute one or more SQL statements.

Close the statement object.

Close the database connection object.

If you will be using Java to write database applications, then you will probably want to investigate JDBC. Many books have been written about using it with a variety of DBMSs.

Arbitrary code execution

We can find a number of areas for security flaws in the languages we use to talk to databases. Generally, these are concentrated on SQL, as it is the most common database language in use. In the default SQL language, a number of built-in elements are possible security risks, some of which we can control access to and some of which we cannot.

In these language elements, we may find a number of issues related to bugs in the software we are using, or issues spawned by not using secure coding practices, that might allow us to execute arbitrary code within the application. For example, a flaw allowing us to conduct a buffer overflow, as we discussed earlier in this chapter, might enable us to insert attack code into the memory space used by the database or the operating system, and compromise either or both of them.

Our best defenses against such attacks are twofold. From the consumer side, we should stay current on the version and patch levels for our software. From the vendor side, we should mandate secure coding practices, in all cases, in order to eliminate the vulnerabilities in the first place, as well as conducting internal reviews to ensure that such practices are actually being followed.

8.3 SQL Extensions for GIS

In 1991, the National Institute for Science and Technology (NIST) set up the GIS/SQL work group to look at GIS extensions. Their work is available as “Towards SQL Database Language Extensions for Geographic Information Systems” at http://books.google.com.

In 1997, the Open Geospatial Consortium (OGC) published “OpenGIS Simple Features Specifications for SQL,” which proposes several conceptual ways for extending SQL to support spatial data. This specification is available from the OGC website at http://www.opengis.org/docs/99-049.pdf. For example, PostGIS is an open-source, freely available, and fairly OGC-compliant spatial database extender for the PostgreSQL Database Management System. SQL Server 2008 has its spatial support: there is Oracle Spatial, and the DB2 spatial extender. These extensions add spatial functions such as distance, area, union, intersection, and specialty geometry data types to the database, using the OGC standards.

The bad news is that they all have the feeling of an OO add-on stuck to the side of the RDBMS model. The simple truth is that GIS is different from RDBMS. The best user interface for GIS is graphical, while RDBMS works best with the linear programming language Dr. Codd required in his famous 12 rules.

7.1 CREATE PROCEDURE

The ANSI/ISO Standard 4GL programming language in SQL is the SQL/PSM (Persistent Stored Modules). The defining document is ISO/IEC 9075-4:2003, Information technology—Database languages—SQL—Part 4: Persistent Stored Modules (SQL/PSM).

However, many SQL products have had their own procedure languages, so you should look at what your particular vendor has given you. Oracle has a proprietary language called PL/SQL, which is very close to SQL/PSM. IBM now supports both PL/SQL and SQL/PSM.

T-SQL is the proprietary 4GL language with Microsoft and Sybase SQL Server. It is a simple one-pass compiler not intended for application development work. It has its roots in 16-bit UNIX C compilers that first implemented SQL Server.

Informix 4GL is the proprietary application language for Informix. It is based on Algol and is “under the hood” in many mainframe software packages.

The SQL/PSM was based on the ADA programming language, but looks a lot like other modern block structured programming languages. Each module starts with a header that tells the SQL/PSM compiler as much as it can about the code; the body is a block and can be exited with a return statement. Blocks are nested inside each other.

The blocks declare local variables at the start of the block, has code in the middle, and error handlers at the end of the block. The local variables are the usual SQL data types, but schema objects cannot be created in the SQL/PSM. The control of flow structures are the usual nested blocks (BEGIN-END and BEGIN ATOMIC-END for transactions), selection of control flow (IF-THEN-ELSE, CASE), and iterations (WHILE, etc.) mixed with SQL statements.

The error handler is an interrupt-driven model. When an exception is raised, control jumps to the appropriate error handler no matter where it happens in the block.

This book uses simple SQL/PSM for procedures, but does not attempt to teach the language. It needs a book of its own.

Which language is used to extract data from a database?

What is the best language for database application?