PRATHMIK SHIKSHAKO NA UTCHHTAR PAGAR DHORAN BABAT LATTER DATE 23-6-2020
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
operation.
Relational algebra is a language for expressing relational database queries.
Relation algebra is a procedural query language.
Relational algebraic operations are as follows:
Selection:-
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?
Normalization
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.
1NF
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.
Example
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
operation.
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
condition.
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
database.
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.
Transaction
A transaction is a part of program execution that accesses and updates various data
items.
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
Atomicity
Either all operations of the transaction are properly reflected in the database or none
are.
Means either all the operations of a transaction are executed or not a single operation is
executed.
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.
Consistency
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
transaction.
Isolation
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
transactions.
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.
PRATHMIK SHIKSHAKO NA UTCHHTAR PAGAR DHORAN BABAT LATTER
DATE 23-6-2020