Explain keys.
Super key 
 A super key is a set of one or more attributes (columns) that allow us to identify each 
tuple (records) uniquely in a relation (table).
 For example, the enrollment_no, roll_no, semester with department_name of a student 
is sufficient to distinguish one student tuple from another. So {enrollment_no} and 
{roll_no, semester, department_name} both are super key.
Candidate key
 Candidate key is a super key for which no proper subset is a super key.
 For example, combination of roll_no, semester and department_name is sufficient to 
distinguish one student tuple from another. But either roll_no or semester or 
department_name alone or combination of any two columns is not sufficient to 
distinguish one student tuple from another. So {roll_no, semester, department_name} is 
candidate key.
 Every candidate key is super key but every super key may not candidate key.
Primary key
 A Primary key is a candidate key that is chosen by database designer to identify tuples 
uniquely in a relation.
Alternate key
 An Alternate key is a candidate key that is not chosen by database designer to identify 
tuples uniquely in a relation.
Foreign key
 A foreign key is a set of one or more attributes whose values are derived from the 
primary key attribute of another relation.
What is relational algebra? Explain relational algebraic 
 Relational algebra is a language for expressing relational database queries.
 Relation algebra is a procedural query language.
 Relational algebraic operations are as follows:
 Operation: Selects tuples from a relation that satisfy a given condition.
 It is used to select particular tuples from a relation.
 It selects particular tuples but all attribute from a relation.
What is normalization? What is the need of it? OR
What is normalization? Why normalization process is needed? 
 Database normalization is the process of removing redundant data from your tables to 
improve storage efficiency, data integrity, and scalability. 
 In the relational model, methods exist for quantifying how efficient a database is. These 
classifications are called normal forms (or NF), and there are algorithms for converting a 
given database between them.
 Normalization generally involves splitting existing tables into multiple ones, which must 
be re-joined or linked each time a query is issued. 
Need of Normalization
 Eliminates redundant data
 Reduces chances of data errors
 Reduces disk space
 Improve data integrity, scalability and data consistency.
Explain different types of normal forms with example. OR
Explain 1NF, 2NF, 3NF, BCNF, 4NF and 5NF with example.
 A relation R is in first normal form (1NF) if and only if all underlying domains contain 
atomic values only. OR
 A relation R is in first normal form (1NF) if and only if it does not contain any composite 
or multi valued attributes or their combinations.
Cid Name Address TypeofAccountHold
Society City
C01 Riya SaralSoc, Aand Saving, Current, Salary
Explain different search algorithm for selection operation. OR
Explain linear search and binary search algorithm for selection 
 There are two scan algorithms to implement the selection operation:
1. Linear search
2. Binary search
Linear search
 In a linear search, the systems scans each file block and tests all records to see whether 
they satisfy the selection condition.
 For a selection on a key attribute, the system can terminate the scan if the requires 
record is found, without looking at the other records of the relation.
 The cost of linear search in terms of number of I/O operations is br where br is the 
number of blocks in file.
 Selection on key attribute has an average cost of br/2.
 It may be a slower algorithm than any another algorithm. 
 This algorithm can be applied to any file regardless of the ordering of file or the 
availability of indices or the nature of selection operation.
Binary search
 If the file is ordered on attribute and the selection condition is an equality comparison 
on the attribute, we can use a binary search to locate the records that satisfy the 
 The number of blocks that need to be examined to find a block containing the required 
record is log(br).
 If the selection is on non-key attribute more than one block may contain required 
records and the cost of reading the extra blocks has to be added to the cost estimate.
Explain various steps involved in que
What is database Index?
 Indexes are special lookup tables that the database search engine can use to speed up 
data retrieval.
 A database index is a data structure that improves the speed of data retrieval operations 
on a database table.
 An index in a database is very similar to an index in the back of a book.
 Indexes are used to retrieve data from the database very fast. 
 The users cannot see the indexes, they are just used to speed up searches/queries.
 Updating a table with indexes takes more time than updating a table without (because 
the indexes also need an update).
 Syntax:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
 Example:
CREATE INDEX idx_studentname
ON student (studentname);
 Indexing is a way to optimize the performance of a database by minimizing the number 
of disk accesses required when a query is processed. 
 It is a data structure technique which is used to quickly locate and access the data in a 
Explain the structure of Index in database.
 Indexes are created using a few database columns.
 The first column is the Search key that contains a copy of the primary key or candidate
Explain different Indexing Methods (Types).
Different indexing methods are:
 Primary Indexing
 Dense Indexing
 Parse Indexing
 Secondary Indexing
 Clustering Indexing
Primary Indexing
 If the index is created on the primary key of the table, then it is known as primary index.
 These primary keys are unique to each record.
 As primary keys are stored in sorted order, the performance of the searching operation is 
quite efficient.
 Student (RollNo, Name, Address, City, MobileNo) [RollNo is primary key]
CREATE INDEX idx_StudentRno
ON Student (RollNo);
 The primary index can be classified into two types: 
 Dense index 
 Sparse index
Dense Index
Index Table Main Table
What is transaction? List and explain ACID property of 
transaction with example.
 A transaction is a part of program execution that accesses and updates various data 
 A transaction can be defined as a group of tasks in which a single task is the minimum 
processing unit of work, which cannot be divided further.
 A transaction is a logical unit of work that contains one or more SQL statements. 
 A transaction is an atomic unit (transaction either complete 0% or 100%). 
 A database transaction must be atomic, meaning that it must be either entirely 
completed or aborted. 
ACID property
 Either all operations of the transaction are properly reflected in the database or none 
 Means either all the operations of a transaction are executed or not a single operation is 
 For example consider below transaction to transfer Rs. 50 from account A to account B:
1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)
 In above transaction if Rs. 50 is deducted from account A then it must be added to 
account B.
 Execution of a transaction in isolation preserves the consistency of the database.
 Means our database must remain in consistent state after execution of any transaction.
 In above example total of A and B must remain same before and after the execution of 
 Although multiple transactions may execute concurrently, each transaction must be 
unaware of other concurrently executing transactions. 
 Intermediate transaction results must be hidden from other concurrently executed 
 In above example once your transaction start from step one its result should not be 
access by any other transaction until last step (step 6) is completed.
 DATE 23-6-2020
click here to view