Sample solution on Relational database Assignment Help to build a Relational Database by there own for the assignment.
This research work has been conducted in order to understand the concepts of relational databases and query optimization. Queries play a major role in database management system as we can find any relevant data from the system by just writing a query and hence writing complex and sub-queries is a tedious job and not cost effective and hence, we have discussed the methods to optimize the querying process. We have covered all the major points and this research report will be very helpful for the database development and administrators to run the database in an efficient manner.
The Relational database management system (RDBMS) is the process to store, manage, optimize retrieve and access the data stored in the database. It consists of a group of tables in which each table is assigned a unique name. In the commercial data processing application, relational database model is used as the primary database model. The relational database management system is being used in various application and websites on both client and server side for data processing. MySQL, Oracle, Microsoft SQL server are the example of Relational Database Management System. The data and relation between them are organized or managed in tables in RDBMS. The place where a collection of various record and data are stored and organized in a database is called table. Each record contained in the database design table contains the same field. The relational database management system is the most significant platform for query solving and data management in the present computing field.
The relational database design and management system has mainly three artifacts. These are:
- Data Definition language (DDL): The Data definition language is used to modify create and remove the database and database objects. The common data definition language statement are CREATE, DROP and ALTER. When the table is created in the database, the DDL can modify the data by using ALTER command. If the table is no needed for long time the DROP command used to delete the table. The data definition language can also define each column name and data type associated with it. Some examples of DML statement is given below which is used to define the structure of the database:
- CREATE: CREATE is used to create the database objects. It is written as:
CREATE TABLE table_name
- ALTER: ALTER is used to alter (ADD OR DROP) database structure. It is written as:
ALTER TABLE table_name
ADD column_name datatype (incase add database object)
ALTER TABLE table_name
DROP COLUMN column_name (incase drop database object)
- DROP: DROP is used to delete the object stored in the database. It is written as:
DROP INDEX table_name. index_name
- RENAME: RENAME command is used to rename the object in the database. It is written as:
RENAME TABLE table_name TO new_table_name
- COMMENT: COMMENT is used to add comment to the data dictionary.
- TRUNCATE: TRUNCATE is used to remove all the records stored in the table. It is written as:
TRUNCATE TABLE table_name
- Data Manipulation Language (DML): Data manipulation language is used in relational database design management system to retrieve, delete, modify and insert the information stored in the database. We are using various commands in the data manipulation, these are:
- SELECT: SELECT command is used in relational database management system to retrieve the specific information key from database. The Select command written as:
- INSERT: INSERT Command is used in the relational database management system to add records or data in the previously stored data or records. It is written as:
INSERT INTO table_name
VALUES (‘data which is to be added’)
- UPDATE: The UPDATE command is used in the relational database design management system to modify the bulk or individual data or information store in the database table. It is written as:
SET column= value
- DELETE: The DELETE command is used to delete the data or information store in the database. It is written as:
DELETE FROM table_name [WHERE clause]
- Query Processor: Query Processor refers to terms by which the various query is executed, optimized etc. in the background of database. An inbuilt query processor engine is present in the MySQL and other RDBMS which allows the user to retrieve and extract the data or information from the entities and table of database by inputting the specified queries. It involves several process, like:
- Query execution
- Code generation
Query optimization is a process or function of the relational database management system (RDBMS). When we input any query the query optimizer execute the query in the most proficient way.
Users can not access the query optimizer directly. User first have to submit the query to the database server after that the parser parsed the submitted query and then the query finally passed to the query optimizer where the query optimization process occurs in the background of the database engine.
The main purpose of using query optimization is to find the efficient way to process the submitted query in shortest or minimal possible time.
Execution effectiveness of Queries
Execution effectiveness of any queries depends upon the structure of query which containing the different clauses such as join, nesting, intersection, union etc.
- Join: JOIN is the type of clause used in SQL to join two rows from two or more than two tables. It joins the rows on the basis of the common field between them. Joins are categorized in mainly four types in the SQL, these are:
- INNER JOIN: The INNER JOIN returns the all rows of the tables if there is at least one match in the tables which is to be joined.
Read more about C Programming Assignment Help
- LEFT JOIN: The LEFT JOIN returns all the rows of the left table and the matched rows from the right table.
- RIGHT JOIN: The RIGHT JOIN returns all the rows from the right table and matched rows from the left table. It is just vice versa of LEFT JOIN.
- FULL JOIN: The FULL JOIN returns all the rows if there is a match in one table.
- Nesting: Nesting is the query used in the relational database management system within another SQL query of the database and embedded within the WHERE clause. It is used with the various statement such as SELECT, UPDATE, DELETE, INSERT along with the operators like <, >, =, <=, >=, BETWEEN, IN and many more. The main purpose of using nested query is to return the data which is to be used under the main query as a condition to further restrict the data to be retrieved. Example of nested query structure is given below:
SELECT column_name [, column_name]
FROM table1 [, table2]
WHERE column_name OPERATOR
(SELECT column_name [, column_name]
FROM table1 [, table2]
Read more about Android Assignment Help
- Intersection: Intersection is the operator used in the database to intersect two datasets of the database. When we intersect the two different dataset of the database then the result of intersect is the record or data which consist in both the dataset.
The basic structure of the intersection operator is given below:
SELECT column_name1 [, column_name2]
FROM table_name1 [, table_name2]
SELECT column_name1 [, column_name2]
FROM table_name1 [, table_name2]
In the figure shown above the intersection of these two datasets is the blue shaded area.
- Union: Union is the operator using in database management system to combine the different queries. As an example:
SELECT column_names FROM table_name.
Read more about Database Security Assignment Help
Architectural Framework of Query Processor
Query processor mainly involves three points in the query execution process:
- Parsing and conversion: At first query is translated into its internal form and then it is translated into its relational algebra. The parser checks the syntax of query and verifies the relations.
- Optimization: In this the submitted query is optimized.
- Evaluation: The query execution engine follows a query execution plan and executes the plan to output the answer of query.
Query Optimization Techniques and Mechanism
The database engine uses different techniques and levels to optimize the queries. It is recommended the steps to start the query. The steps are: start with the server, then database instance and finally at last go down through the objects of database to individual statements. After this all the effects of all the changes made to individual statement.
There are various steps used to improve the runtime performance of the statement submitted or to be submitted:
- Optimize access structures:
- Design and normalization of database
- Tables: heap or index-organized tables and table or indexed clusters.
- Materialized views
- Partitioning schemes
- Rewrite the SQL statements:
- Exclude the projections which are not required.
- Reduce the quantity of work done more than once.
- Factor sub-queries which are used several times in the same statement.
- Avoid the scalar sub-queries in the SELECT list.
- Avoid implicit discussion of data types in the WHERE clause.
- Make clear what you mean and choose the proper join.
- Use the joins instead of sub-queries.
- Avoid DISTINCT unless you have to use it.
- Avoid needless sorts.
- Don’t abuse HAVING to filter rows before aggregating.
- Don’t apply the functions on indexed columns in the WHERE clause when there is no corresponding function based index.
- Attach hints when you have determined that it is correct and essential to do so.
Read more about Python Assignment Help
There are various equivalent expressions and algorithm is used for each and every operation to evaluate the queries submitted, such as:
sBalance < 2500 (Õbalance (account)) is equivalent to Õbalance (sbalance<2500(account))
It also compares the cost difference between the bad and good way of evaluating the query which can be huge. The query processor needs to estimate the cost of operations like statistics for the intermediate results to compute the cost of complex expression.
The relations generated in the database by using the two equivalent expressions have the same set of the attributes which include the same set of tuples even if there attributes differently ordered.
The query processor makes a query execution plan to properly execute the query submitted; the making of query execution plan takes several steps, including all the steps the process is called the cost based optimization.
- It uses the equivalence rules to transform any expression into an equivalent one.
- Annotating the resultant expression to get alternative plan for query.
- Choosing the cheapest plan which is based on the estimated cost.
The query optimization involves following techniques and mechanism to optimize the queries, these are:
- Statistical information for cost estimation
- Cost-based optimization algorithm
- Equivalence rules
- Optimized the nested queries used in the database
- Materialized the view and view the maintenance
Some recent developments in the query optimization in relational database are:
- Changes to parser, methods of query rewrite is improve the optimization potential
- Changes to enumeration increase the potential of query processor to choose best plan for query execution
- Changes to cost estimation improve the accuracy and likelihood of identifying the best plan
- Changes to code generation improve the quality and efficiency of expression evaluation
There are several problems in current query optimization techniques, some of them are:
- Ingres splices parameter values into syntax for non-repeated queries
- The repeated queries are compiled with the values of initial parameter
- “…where col_a > :a_value” might qualify the 500000 rows or 5 rows
- Best strategy might be an index scan or a full table scan – no way to know when query is compiled
- Poor row estimates feed into joins and result in even poorer estimates
Read more about Visual Basic Assignment Help
The possible solutions of the current problem in query optimization techniques are:
- Multi strategies query plans
- The query plan has a “fork” node above 2 access nodes for the same table (one table scan and one index)
- Test is executed in fork to determine which sub node to read rows from
- Based on comparison of host parameter value with pre-determined (by optimizer from histogram) constant
- Too complex with large number of joins
- Works for the queries with few tables, could even include different join strategies in fork
- Selectivity of predicates with functions/expression involving columns is very difficult to estimate
- Ingres doesn’t even evaluate constant functions before estimating selectivity.
- Pre evaluation of constant functions is long overdue in Ingres
- The more ambitious enhancement addressing single column function/expression on column histogram value to generate the new histogram
The query plans are represented as a tree of ‘plan nodes’ by the query optimizer. A single operation which is required to execute the query is encapsulated by a plan node. The plan nodes arranged as tree in which the intermediate results of the queries is flow bottom to top of the tree. Each plan nodes of a tree has zero or more than zero child nodes. The nodes which provide the results by scanning (index or sequential scan) the disk is called the leaves of the tree.
Read more about Programming Assignment Help
Inkster, D. (2016). Challenges in Query Optimization. 1st ed. [ebook] p.28. Available at: http://downloads.actian.com/online/media/pdf/Ingres-DBMS/The_Ingres_Optimizer.pdf [Accessed 9 Jun. 2016].
Bamnote, D. (2016). International Journal of Advanced Research in Computer Science and Software Engineering. 1st ed. [ebook] p.4. Available at: http://International Journal of Advanced Research in Computer Science and Software Engineering [Accessed 9 Jun. 2016].
Lanzelotte, R. (2016). On the Effectiveness of Optimization Search Strategies for Parallel Execution Spaces. 1st ed. p.12.
Chaudhuri, S. (2016). An Overview of Query Optimization in Relational Systems. 1st ed. [ebook] p.11. Available at: http://research.microsoft.com/pubs/76059/pods98-tutorial.pdf [Accessed 9 Jun. 2016].