Important Database Topics | IBPS PO Prelims & Mains Preparation - Bank Exams PDF Download

What are the levels of Abstraction?

• Physical level: lowest level of abstraction which describes how data is stored. 
• Logical level: it describes what data is stored and relationship among that data. 
• View level: it describes a part of entire database and user interaction (GUI) with it.

What is Extension and Intension?

• Extension: it is the number of tuples present in table at any instance. It is time dependent. 
• Intension: A constant value that gives the name, structure and constraints associated with a table. 

Difference between a Data Warehouse and Data Mart?

• Data Warehouse: It contains multiple subject areas and is for enterprise. It holds very detailed historical information. E.g. Enterprise Data. 
• Data Mart: It is for a specific functional area and holds information in a summarized form. It is easy to build. Eg. Departmental Data. 

What is XML?

XML stands for Extensible Markup Language. It is used to manipulate and structure data related to a browser. XML is self defining as it contains the structure of the data along with the data. It uses tags similar to HTML (HyperText Markup Language) but XML describes the content whereas HTML describes appearance. It provides for a clear separation between document structure & materialization. It is becoming the standard for e-commerce these days.  

Difference between Static (embedded) SQL & Dynamic (interactive) SQL?

• Static SQL: It is the process of hard coding SQL statements. How database will be accessed is pre-determined. These statements do not change unless we modify the source code. It is more quick and efficient. 
• Dynamic SQL: It is the process of generating SQL statements/ Accessing Database at run time. They can be different each time. It is slow and less efficient. 

4 types of Indexes?

• Unique primary index: it is used to find and store a row & is unique. 
• Non unique primary index: it is used to find and store a row based on unique primary index and is not unique in nature. 
• Unique secondary index: It is unique for each row and is used to find table rows. 
• Non unique secondary index: Again, used to find table rows but not unique in nature. 

Mains objectives of Normalization

• Simplify constraints. 
• Minimize data redundancy
• Better design
• Make it easy to insert, update and delete data. 

Maximum and Minimum Cardinality

• Maximum Cardinality refers to max no. of instances of an entity that can be associated with each instance of another entity. 
• Minimum Cardinality refers to min no. of instances that can be associated with each instance of another entity. 

Difference between Structured and Unstructured data?

• Structured: This is factual data with objects/events. Data like - numeric, character and dates is structured data. It is stored in table form.
• Unstructured: This is usually the multimedia data like photos, maps, images and sound clips. It is usually stored on web servers.

Indexing and its different kinds?

Indexing is a technique by which specific data can be retrieved quickly. They can also be created to enforce uniqueness and to facilitate sorting. Its different kinds are as follows:

• Binary Search: In this the target value is compared to the middle element in an array. The half in which value is not found is discarded and middle element of remaining half is compared again until the value is successfully found. 
• B Tree: In B-Tree, internal nodes can have variable no. of child nodes. Any insertion or deletion of data changes the no. of child nodes. Each internal node has a no. of keys. E.g. If there are 3 internal nodes with keys X & Y, left subtree will have values less than X, middle would have values between X & Y and right sub tree would have values greater than Y. 
• Inverted Index: Unlike regular index as in books which maps pages to topics, Inverted index consists of a list of unique words that appear in a document and for each word, a list of documents in which it appears. It is used for full text searches like in search engines. 
• Database Index: You can create an index on a field in table which will have field value and a pointer to record it is related to. These indexes need additional storage space. 
• BitMap Index:Typically used in Oracle databases, it is used for columns having low distinct values. Eg. Column called SEX  which will have only 2 values - Male & Female. 

What is a Phantom Deadlock?

In distributed systems, these are the deadlocks that are falsely detected due to delay in propagating local information. They don't actually exist and lead to unnecessary aborts.

What is cold back up & hot back up (in Oracle)?

• Cold Backup: During cold backup, database is not running & not available to users. Hence, there is no change in the data files during back up and DB is in sync when restored. 
• Hot Backup: Database remains open and available to users. Hence there may be changes and upon restore, changes in log files are re-applied to bring DB in sync.

Difference between an exclusive lock & a shared lock?

• Exclusive lock: It prohibits other users from reading the locked resource. 
• Shared lock: It allows other users to read the locked resource without updating it. 

What is market basket analysis?

Its a data mining technique which predicts the sets of products to be purchased together. It is based on conditional probability.

What are Save points in Oracle?

SAVE POINTS can be used to divide a transaction into smaller parts and we can rollback only these part without having to roll back the whole transactions. Maximum of 5 SAVE POINTS are allowed. 

What are snap shots in Oracle DB?

These are read only copies of master data maintained at a remote site and are periodically refreshed to reflect changes made in the master data.