For any given query, there may be a number of different ways to execute it. The process of choosing a suitable one for processing a query is known as query optimization.FormsThe two forms of query optimization are as follows −Heuristic optimization − Here the query execution is refined based on heuristic rules for reordering the individual operations.Cost based optimization − the overall cost of executing the query is systematically reduced by estimating the costs of executing several different execution plans.ExampleSelect name from customer, account where customer.name=account.name and account.balance>2000;There are two evaluation plans −Πcustomer.name(σcustomer.name=account.name ^ account.balance>2000(customerXaccount)Πcustomer.name(σcustomer.name=account.name(customerXσ account.balance>2000(account)Cost evaluator evaluates the cost ... Read More

The activities involved in parsing, validating, execution and optimizing a query is called Query Processing.StepsThe steps involved in query processing and optimization are as follows −A sequence of primitive operations that can be used to evaluate a query is called query execution plan or query evaluation plan.The query execution engine takes a query evaluation plan, executes that plan and produces the desired output. The different execution plans for a given query can have different costs based on the number of disks. It is the responsibility of the system to construct a query evaluation plan which minimizes the cost of query ... Read More

Join operation combines two relations with respect to a condition, It is denoted by ⋈. Joins are of different types, Theta join, Natural join, Outer join (Left outer join, Right outer join, Full outer Join).ExampleConsider an example as given below −Step 1Querycreate a table student (name char(30), regno number(10));OutputTable created.Step 2Queryinsert into student values (‘hari’, 1); Insert into student values (‘subbu’, 2); Insert into student values (‘srinu’, 3);Output3 rows created.Step 3Queryselect * from student;OutputNameRegnoHari1Subbu2Srinu3Step 4QueryCreate table marks(regno number(10), total number(10));Outputtable created.Step 5Queryinsert into marks values (1, 400); Insert into marks values(2, 450); Insert into marks values (3, 300);Output3 rows created.Step ... Read More

Join dependency is a constraint which is similar to functional dependency or multivalued dependency. It is satisfied if and only if the relation concerned is the join of a certain number of projections. Such type of constraint is called join dependency.Let's consider a special class of join dependencies which help to capture data dependencies present in a hierarchical data structure.Example 1The above hierarchical organization informs regarding ward and patients currently admitted to a ward depend only on the hospital but not the facilities present in that hospital. Since hospitals have multiple wards, functional dependencies are not adequate to describe the ... Read More

To understand why database management systems are necessary, let's look at a typical "file processing system" supported by conventional operating systems.ExampleTake an application in a saving bank. Saving accounts and customer records are kept in permanent system files.Application programs are written to manipulate files to perform some tasks like follows −Debit or credit an account.Add a new account.Find an account balance.Generate monthly statements.Development of the system proceeds as new application programs must be written as the need arises, new permanent files are created as required, but over a long period of time files may be in different formats and application ... Read More

If a table has data redundancy and is not properly normalized then it will be difficult to handle and update. If a relation contains composite or multivalued attributes it violates the first normal form.A table is in 1Nf if the following conditions exist −There are only single valued attributes.Attribute domain does not change.There is a unique name for every attribute or column.The order in which data is stored does not matter.ExampleConsider the table given below −IdNameCourse1AC1, C22EC33BC2, C3The above table is in un normalized form, the course attribute is having multivalued attributes which violates 1NF.Now we represent the above table ... Read More

Let's take the dependencies F= {AB->CDEF, AF->ED, F->B} which ONE of the following is the highest normal form that a table R=ABCDEF could satisfy.Select any one from the following −No normal form would be satisfied.Boyce-Codd Normal Form1st Normal Form2nd Normal Form3rd Normal FormGiven Functional dependencies of F are as follows −AB ---> CDEFAF ---> EDF ---> BNow in R.H.S (Right Hand Side) of Functional dependencies of F, attributes B, C, D, E, F are all present but attribute A is missing. So, attribute A must be the part of Super Key.We have to find the closure of A (A+), A+ ... Read More

In the database management system there are several rules to manage the tables and do the changes like insertion, deletion, updatation and there are different stages at which the tables are organized and these stages are known as normal forms.It is a standard structure of relational databases in which a relation may be inserted in another relation.Five normal formsThere are five normal forms which are explained below −First normal formThe first normal form defines the definition of the table. The rules of the first normal form are as follows −Each column must have a single value.Each column has a unique ... Read More

It is also called as project join normal form. A relation R is in 5NF if it is in 4NF and there is no join dependency. No join dependency means lossless-join decomposition.Decomposition is lossless-join decomposition if it preserves all the data in original relation and does not result in additional tuples.A relation R satisfies join dependency iff R is equal to the join of R1, R2, .....Rn where Ri are a subset of the set of attributes of R.Relation RDeptSubjectNameCSECAmmuCSECAmarCSEJavaAmarITCbhanuHere, dept ->-> subjectdept->-> nameThe above relation is in 4NF. Anomalies can occur in relation in 4NF if the primary key ... Read More

A relation R is in 4NF if it is in BCNF and there is no non-trivial multivalued dependency.For a dependency A->B, if for a single value of A, multiple values of B exist, then the relation will be a multi-valued dependency.ExampleConsider the following tableRegnoPhonenoQualification1P1Diploma1P1B.Tech1P1M.Tech1P2Diploma1P2B.Tech1P2M.TechHere, regno->-> phonenoregno->-> qualification.Both are non trivial MVDThe given relation is in BCNF [since no functional dependency exists]. But the above table is not in 4NF [since there is a non trivial MVD].AnomaliesIt also suffers with anomalies which are as follows −Insertion anomaly: If we want to insert a new phoneno for regno3 then we have to ... Read More