What are the differences between Primary Index, Secondary Index, and Clustering Index?
Indexing is a data structure technique to efficiently retrieve records from the database files based on some attributes on which the indexing has been done. Indexing in database systems is similar to what we see in books.
Indexing is defined based on its indexing attributes. Indexing can be of the following types −
Primary Index< − Primary index is defined on an ordered data file. The data file is ordered on a key field. The key field is generally the primary key of the relation.
Secondary Index − Secondary index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.
Clustering Index − Clustering index is defined on an ordered data file. The data file is ordered on a non-key field.
State the steps involved in query processing. Why is the query optimization needed?
Write short notes on the following:
Write the difference between procedural and non-procedural query language.
Let T1, T2, T3 be transactions that operate on the same data items A, B, C. Let r1(A) means that T1 reads A, w1(A) means that T1 writes A and so on for T2 and T3. Consider the following schedule: S1: r2(C), r2(B), w2(B), r3(C), r1(A), w1(A), w3(B), w3(C), r2(A), r1(B), w1(B), w2(A). Is the schedule serializable and why?
Consider the following tables:
DEPT (DCODE, DNAME)
EMP (ECODE, ENAME, BASIC, DCODE, DT_JN)
Write down the SQL statements for the following: