Sql is only a data manipulation language (dml).

VI Database Application Architecture

Application programs are programs that are used to interact with the database. Application programs are usually written in a host language, such as Cobol, C, C++, or Java. Examples in a banking system are programs that generate payroll checks, debit accounts, credit accounts, or transfer funds between accounts.

To access the database from an application program, DML statements need to be executed from the host language. There are two ways to do this:

By providing an application program interface (set of procedures) that can be used to send DML and DDL statements to the database, and retrieve the results. The Open Database Connectivity (ODBC) standard defined by Microsoft for use with the C language is a commonly used application program interface standard. The Java Database Connectivity (JDBC) standard provides corresponding features to the Java language.

By extending the host language syntax to embed DML calls within the host language program. Usually, a special character prefaces DML calls, and a preprocessor, called the DML precompiler, converts the DML statements to normal procedure calls in the host language.

Most users of a database system today are not present at the site of the database system, but connect to it through a network. We can therefore differentiate between client machines, on which remote database users work, and server machines, on which the database system runs.

Database applications are usually partitioned into two or three parts, as in Fig. 3. In a two-tier architecture, the application is partitioned into a component that resides at the client machine and that invokes the database system component at the server machine through query language statements. Application program interface standards like ODBC and JDBC are used for interaction between the client and the server.

In contrast, in a three-tier architecture, the client machine acts as merely a front end and does not contain any direct database calls. Instead, the client end communicates with an application server. The application server in turn communicates with a database system to access data. The business logic of the application, which says what actions to carry out under what conditions, is embedded in the application server, instead of being distributed across multiple clients. Three-tier applications are more appropriate for large applications and for applications that run on the World Wide Web.

Although the term application server can be applied to servers for any applications, the term is increasingly being used to refer to systems that manage an entire range of enterprise activities, such as financial applications, personnel management, sales, inventory/stores management, as well as business planning activities.

Indexes

Indexes are data structures that dramatically improve performance of many Data Manipulation Language (DML) commands. The files that host t hem are important to protect, because they will almost always contain a copy of at least some data from many of the table columns that you might find in a join, a where, or an order by clause of a DML statement (e.g., a select, update, or delete statement). Let’s say that you have a table such as the one below called People having columns for social security number (SSN), last name (last_name), and first name (first_name).

SSNLAST_NAMEFIRST_NAME111-11-1111BondWilliam222-22-2222BuermannScott333-33-3333IngramAaron444-44-4444KulkarniSachin555-55-5555PectorIrving666-66-6666ShaulJoshua

For optimal performance for querying by SSN, your DBA would create an index on the SSN column. She or she may put that in a tablespace separate from the underlying table by using a command such as create index. For example, to create an index called people_ssn in the idxtblspace tablespace, use the following command:

CREATE INDEX people_ssn ON People (ssn) TABLESPACE idxtblspace;

Several different types of indexes are available, but the most common one is called a b-tree. B-trees store the values of the indexed column in the leaf pages of a tree, as depicted in Figure 2.2. In this example, the leaf pages of the index are at the bottom of the diagram and each of those pages contains up to three values. As you can see in the figure, an index on the SSN column causes Oracle to store social security numbers in an additional location separate from the underlying table. The table and index may or may not be in the same file, but either way, both should be represented in your lockdown plan.

Differences from DML Triggers

We’ve already talked about one key difference between DDL and DML triggers and that is the INSTEAD OF trigger. DML triggers can be defined as INSTEAD OF triggers, firing before statement execution. However, DDL triggers are only AFTER triggers, meaning the statement executes first. Another key difference we’ve discussed is scope. DML triggers apply to specific database objects such as tables and views. DDL triggers fire all objects of a given type if the trigger conditions are met (the event the DDL trigger is supposed to fire on occurs).

Another difference is the lack of the inserted and deleted tables, as with DML triggers. These two special tables provide the rows affected by the DML operation. For INSERT statements, the added rows are in the inserted table. For UPDATE statements, the rows as they will appear when the transaction is complete appear in the inserted table, and the rows as they were originally are in the deleted table. For DELETE operations, the rows deleted are in the deleted table. They are appropriate for DML triggers, but they don’t fit DDL triggers. DDL triggers should capture the event and the query that caused the event. That doesn’t fit with either the inserted or deleted tables. As a result, SQL Server 2005 implements the EVENTDATA function to capture information of pertinent interest for a DDL trigger. We’ll look at EVENTDATA more in the next section.

5.4 The Impala Architecture

There are three vital daemons of Impala playing a major role in its architecture.

Impalad

Statestored

Catalogd

The core part of Impala is a daemon that runs on each node of the cluster called impalad. It reads and writes to data files, accepts queries transmitted from Hue or any other connection requests from Data Analytic tools with JDBC and ODBC connections. It distributes the work to other nodes in the Impala cluster and transmits the intermediate results back to the coordinator node. The node in which job launched is known as the coordinator node. One can submit the query to Impala daemon running on any node of your cluster and that node serves as the coordinator node for that query. Coordinator node distributes the query for processing among the cluster of impalad daemons of other nodes. The other nodes transmit the partial data back to the coordinator, which then constructs the final result set for that query.

Statestore checks for Impala daemons availability on all the nodes of the cluster. A daemon process called state stored physically represents it. Only one node in the cluster needs to have this process. Impala daemons are in continuous communication with the statestore, to confirm which nodes are healthy and accept new work for processing. If an impalad daemon goes down because of any of the reason like hardware failure, network connection issue, then the statestored daemon informs all the other impalad daemons running in the cluster, so that future queries can avoid assigning tasks/queries to process. If statestore is not available, the other nodes continue running and distribute work among the other impalad daemons as usual with the assumption that all impalad daemons are running fine.

Catalog service relays the metadata changes from Impala DDL (data definition language) queries or DML (data manipulation language) queries to all nodes in the cluster. This process is represented as catalogd daemon and we need such process in one of the hosts in the cluster.

Third-party applications or any query requests will be taken by impalad then execution plan will be prepared and submit the query plan to rest of the other impalad's in the cluster. Before the execution plan, impalad will communicate with statestore and hive metastore and namenode for live impalad nodes and table metadata and file information. Impala provides command line interface, which gives interactive query results.

5.4.1 Flume

This is a distributed, reliable, and available service for efficiently collecting, aggregating, and moving large amounts of log data. It has a simple and flexible architecture based on streaming data flows. It is robust and fault tolerant with tunable reliability mechanisms. Furthermore, Flume is blessed with many failover and recovery mechanisms. It uses a simple extensible data model that allows for the online analytic application. The macrolevel architecture is given below.

Enterprises extensively use Flume's powerful streaming capabilities to land data from high-throughput streams into the HDFS. The common streaming sources are application logs, infrastructure operational data, sensor and device data, geolocation data, and social media. These different types of data can be landed in Hadoop for future analysis using interactive queries in Apache Hive.

Apache Flume is composed of six important components:

•

Events—The data units that are transferred over a channel from source to sink. The size of events is usually 4 KB.

•

Sources—They accept data from a server or an application. Sources listen for events and write events to a channel.

•

Sinks receive data and store it in HDFS repository or transmit the data to another source. It writes event data to a chosen target and removes the event from the queue.

•

Channels connect between sources and sink by queuing event data for transactions.

•

Interceptors drop data or transfer data as it flows into the system.

•

Agents are used to running the sinks and sources in the flume.

(1)

Efficiently stream data—Flume easily collects, aggregates, and moves streaming logs or event data from multiple sources into Hadoop. Flume is designed to ingest this data as it is generated for near real-time analytics. Flume is a vital module for streaming analytics and emerging as a silver bullet for sensor data aggregation or “Internet of Things (IoT)” use cases.

(2)

Built for Hadoop scale—With the growing data volumes, Flume can simply scale horizontally to handle the increased load. Furthermore, Flume can efficiently gather logs from new data sources and multiple systems. On the other hand, connectors are made available to enable Flume to stream data into multiple systems.

(3)

Always-on reliability—Flume protects against data loss and ensures that streaming data will continue to be delivered, even in the event of failure, with the built-in fault tolerance.

Here is a specific example. Flume is used to log manufacturing operations. When one run of product comes off the assembly line, it generates a log file about that run. Even if this occurs hundreds or thousands of times per day, the large volume log file data can stream through Flume into a tool for immediate analysis with Apache Storm. The other option is to aggregate months or years of production runs in HDFS and the batched data get analyzed using Apache Hive.

5.4.5 The Key Differentiators

The distinct advantage of Kafka's model is that every topic has both the properties. It can scale processing and is also multisubscriber. Kafka has stronger ordering guarantees than a traditional messaging system. A traditional queue retains records in-order on the server, and if multiple consumers consume from the queue, then the server hands out records in the order they are stored. However, although the server hands out records in order, the records are delivered asynchronously to consumers, so the records may arrive out of order to different consumers. This effectively means the ordering of the records is lost in the presence of parallel consumption.

Kafka solves this problem. By having a notion of parallelism—the partition—within the topics, Kafka is able to provide both ordering guarantees and load balancing over a pool of consumer processes. This is achieved by assigning the partitions in the topic to the consumers in the consumer group so that exactly one consumer in the group consumes each partition. Since there are many partitions, this balances the load over many consumer instances. Any message queue that allows publishing messages decoupled from consuming them is effectively acting as a storage system for the in-flight messages. Data written to Kafka is written to disk and replicated for fault tolerance. Kafka allows producers to wait on acknowledgment so that a write is not considered complete until it is fully replicated and guaranteed to persist even if the server written to fails. This enables Kafka to scale well and hence Kafka will perform the same whether there are 50 KB or 50 TB of persistent data on the server.

In Kafka, a stream processor is anything that takes continual streams of data from input topics, performs some processing on this input, and produces continual streams of data to output topics. It is possible to do simple processing directly using the producer and consumer APIs. However, for more complex transformations, Kafka provides a fully integrated streams API. This allows building applications that do nontrivial processing that compute aggregations off of streams or join streams together. This facility helps solve the hard problems such as handling out-of-order data, reprocessing input as code changes, performing stateful computations, etc.

The streams API builds on the core primitives Kafka provides: it uses the producer and consumer APIs for input, uses Kafka for stateful storage, and uses the same group mechanism for fault tolerance among the stream processor instances. A distributed file system like HDFS allows storing static files for batch processing. Effectively a system like this allows storing and processing historical data from the past. A traditional enterprise messaging system allows processing future messages that will arrive after one subscribes. Applications built in this way process future data as it arrives. Kafka combines both of these capabilities and this combination elevates Kafka as a key streaming application platform and data pipelines. By combining storage and low-latency subscriptions, streaming applications can treat both past and future data the same way. That is, a single application can process historical and stored data but rather than ending when it reaches the last record it can keep processing as future data arrives.

Apache Kafka is a distributed “publish–subscribe” messaging system and a robust queue that can handle a high volume of data and enables in passing messages from one endpoint to another. Kafka is suitable for both offline and online message consumption. Kafka messages are persisted on the disk and replicated within the cluster to prevent data loss. Kafka is built on top of the ZooKeeper synchronization service. It integrates very well with Apache Storm and Spark for real-time streaming data analysis. The advantages of Kafka are:

•

Reliability—Kafka is distributed, partitioned, replicated, and fault tolerance.

•

Scalability—Kafka messaging system scales easily without down time.

•

Durability—Kafka uses distributed commit log which means messages persist on disk as fast as possible, hence it is durable.

•

Performance—Kafka has high-throughput for both publishing and subscribing messages. It maintains stable performance even many TB of messages are stored.

Kafka is very fast and guarantees zero downtime and zero data loss. Kafka is a unified platform for handling all the real-time data feeds. Kafka supports low-latency message delivery and gives guarantee for fault tolerance in the presence of machine failures. It has the ability to handle a large number of diverse consumers. Kafka performs 2 million writes per second. Kafka persists all data to the disk, which essentially means that all the “writes” go to the page cache of the OS (RAM). This makes it efficient to transfer data from page cache to a network socket. The macrolevel Kafka architecture is given below.

Kafka has four core APIs:

•

The producer API allows an application to publish a stream of records to one or more Kafka topics.

•

The consumer API allows an application to subscribe to one or more topics and process the stream of records produced to them.

•

The streams API allows an application to act as a stream processor, consuming an input stream from one or more topics, and producing an output stream to one or more output topics, effectively transforming the input streams to output streams.

•

The connector API allows building and running reusable producers or consumers that connect Kafka topics to existing applications or data systems. For example, a connector to a relational database might capture every change to a table.

This is a messaging broker often used in place of traditional brokers in the Hadoop environment because it is designed for higher throughput and provides replication and greater fault tolerance. Kafka works in combination with Apache Storm, Apache HBase, and Apache Spark for real-time analysis and rendering of streaming data. Kafka can message geospatial data from a fleet of long-haul trucks or sensor data from heating and cooling equipment in office buildings. In short, Kafka brokers massive message streams for low-latency analysis in enterprise Apache Hadoop. Apache Kafka supports a wide range of use cases as a general-purpose messaging system for scenarios where high-throughput, reliable delivery, and horizontal scalability are important. Apache Storm and Apache HBase both work very well in combination with Kafka.

5.4.6 Zookeeper

The various contributing modules of a distributed application can run on networked multiple nodes in a compute cluster to complete a particular task in a fast and efficient manner. Generally complicated tasks, which would take hours to complete an application by running in a single node, can be done in minutes by distributing the application modules across the cluster nodes. The time to complete the task can be further reduced by automated provisioning of servers, configuration management, application deployment, and delivery. There can be multiple clients for a distributed application as shown in the figure below.

The prominent challenges of distributed applications include:

•

Race condition—It is all about two or more machines performing a particular task try to access shared resources.

•

Deadlock—Two or more operations waiting for each other to complete indefinitely.

•

Inconsistency—Partial failure of data.

Apache ZooKeeper is one such solution for effective and efficient coordination between multiple services. ZooKeeper is a new solution used by a cluster of nodes to coordinate among themselves and maintain shared data with robust synchronization techniques. The common services provided by ZooKeeper are as follows:

•

Naming service—Identifying the nodes in a cluster by name.

•

Configuration management—Latest and up-to-date configuration information of the system for a joining node.

•

Cluster management—Joining/leaving of a node in a cluster and node status at the real time.

•

Leader election—Electing a node as the leader for coordination purpose.

•

Locking and synchronization service—Locking the data while modifying it. This mechanism helps in automatic fail recovery while connecting other distributed applications like Apache HBase.

•

Highly reliable data registry—The availability of data even when one or a few nodes are down.

The race condition and deadlock issues are handled using fail-safe synchronization approach. Another main drawback is the data inconsistency and the ZooKeeper service resolves it with atomicity.

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).

Database manipulation language

The component of the database most visible and recognizable by database professionals, as well as applications developers and possibly applications users, is the data manipulation language. This component of the database can take on many forms, the most common being a programming language–like interface, which provides the ability to retrieve and store information within the database previously specified by the database design language.

The data manipulation language need not, however, take on a textual and procedural view only. The data manipulation language can be visual, as in the spatial data management system, where information is described using icons and is retrieved using pictures that can be zoomed in on for greater detail about an item—for example, given that we have a map of the United States used as the top-level view for the querying of business information, we may wish to find out what universities are within the southern region of Massachusetts closest to Cape Cod. We would first select the type of icons we wish depicted—for example, only show regions with universities by selecting the university icons. The visual display would then highlight cities where major universities are located. To isolate a particular university or to find out more about the area where a university is located, we begin by selecting the area, say southeastern New England around Cape Cod, by encircling the region. The display would then expand this area, again only depicting the universities. To select a particular university select a university icon (Figure 2.28). If we selected the University of Massachusetts at Dartmouth, we may next get an aerial view of the university. To discover more information we could select a building, then a department, or possibly even a particular professor or course offering. In such a way the majority of information needed could be extracted and displayed in visual form. There are, however, limitations with this method. Not all information lends itself to visual-only representation. We may be forced to place only a subset of the totally available information in such a system and use a separate database interface for more textual information.

A second type of interface is related more toward business uses of databases. This type of interface uses a company's typical paper forms for information about inventory, sales, employee records, and so forth as the interface presented to the users of the database. An application or user simply selects the proper form, say an employee record form, and selects which employee or group of employee records to look at by typing in information on the form.

Figure 2.29 shows a form that may be used by a business to represent customers or suppliers. The form shows the company's major information, such as the company's name, address, phone number, fax machine number, and e-mail address, and possibly some information about the type of product or service it produces or supplies. In addition, the form may include some extra fields, which can be used to aid in finding information. In the example screen of Figure 2.29, there is a separate field called Find on the bottom of the screen. In this field a user could input parameters to be looked for or qualifiers to aid in a search—for example, if we wished to select all companies in Boston, Massachusetts, that are stored in our database, there are two potential ways to do this. The first is to enter the names Boston and Massachusetts in the appropriate city and state fields and select Go on the bottom right of the screen. This would indicate to the database to match any records that have these qualities in these particular fields. To find additional entries with the same fields one would select the Next field on the lower-right corner. An additional means to recover the same records is to type All, Boston, and Massachusetts in the Find field of the form.

A third form of nontraditional data manipulation language is the query by example, or QBE, type of facility. In a query by example environment the user requests basic information about a record of interest—for example, a company name. The system then returns a template, which may or may not fit what is being requested. This template can then be used by the user to further refine the query and to receive additional examples to use in formulating a more precise query. The QBE interface developed for the relational model is closely tied to the forms-based interface. The examples come back in the form of tables, and the user fills in known quantities. The database then attempts to fill in a response table using this information as the restriction information.

Other data manipulation languages are based on functional evaluation. In these types of languages the users request information from the database through the use of function calls. The function calls may return text, graphics, video, sound, or a variety of data formats. The form returned is dependent on the data formats of the function called and the parameters' data types. This form of query interface is most prevalent in object-oriented databases and in multimedia and hypermedia databases. The information that is passed between the database and the applications is in the native form of the application, not in the base form of the database. This type of interface is desirable in applications where data come in nontextual forms that nevertheless are stored and managed by a database management system.

The most prevalent form of data manipulation language today is still by far the textual and procedural languages, such as Structured Query Language (SQL) and Object Query Language (OQL). In these languages the queries are formed much like a program in any programming language. The query writer has some reserved words that provide some given functionality. The typical query includes reserved words to select multiple records, a single record, or a subset of a record; to specify where the record is to come from; and any qualifiers on the access and retrieval of the requested information. In languages of this form the queries take on the structure and execution flow of the program—for example, if we are looking at a relation Order, of the form order number, order date, customer number, product ordered, quantity ordered, unit cost, total cost, and want to find all orders (the entire tuple) from the XYZ Company for bookbindings since May 1995, the following query could be used, given that the XYZ Company has the customer number I101:

In this query we first set an internal variable to range over all values of the search relation. Second, we request the search look to retrieve all the attributes of the relation Order in the same order in which they are stored. Third, we specify where to look for these attributes, namely in relation Order. And, finally, we restrict the selection to find and copy into our result relation only those tuples that have the company number attribute stored with the value of ‘C101’, the value for attribute order date greater than the end of April (odate > ‘4-30-95’), and only the parts named ‘bindings’.

The procedural languages such as SQL also have operators to insert new tuples in the database, to create new relations, to modify existing relations, to delete relations, and to update the contents of a relation. An insert of a tuple into the previous relation could be readily performed by issuing the following instruction:

To delete the same tuple from the database requires that we first find the proper tuple, and then remove it from the relation. The code may appear as follows:

A simpler means would be to refer to the intended tuple by its primary key only. Since in a relational database the primary key by definition must uniquely define a tuple in a relation, then this alone can be used to find and delete the proper tuple. The reformed deletion operation would be as follows:

What the reader should realize from this discussion is that there is no one correct means of retrieving information from a database. There are, however, standard means [1]. The important concept is that database retrieval is different from conventional programming language processes. There is a language of informational access, which has evolved and continues to evolve along with database technology. These languages, however, will continue to be different from their programming language counterparts primarily due to the differences in the requirements for persistent data beyond the point of a program's existence and the requirements for consistency and correctness of information beyond the scope of any single process or program.

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