DBMS Basic VIVA Questions 31. What is Joins? The process of forming pairs of rows by matching the contents of related columns is called joining the tables. 32. What are the types of Joins? INNER JOIN Inner join shows matches only when they exist in both tables. LEFT OUTER JOIN Left join will display all records in left table of the SQL statement. RIGHT OUTER JOIN Right join will display all records in right table of the SQL statement. 33. What is trigger? The concept of a trigger is relatively straightforward. For any event that causes a change in the contents of a table, a user can specify an associated action that the DBMS should carry out. The three events that can trigger an action are attempts to INSERT, DELETE, or UPDATE rows of the table. 34. What are the different types of storage? Primary storage: This category includes storage media that can be operated on directly by the computer central processing unit (CPU), such as the computer main memory and smaller but faster cache memories. Secondary storage: This category includes magnetic disks, optical disks, and tapes. These devices usually have a larger capacity, cost less, and provide slower access to data than do primary storage devices. 35. What is indexing? A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indices can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. 36. What is Tree Structured Indexing? The data entries are arranged in sorted order by search key value, and a hierarchical search data structure is maintained that directs searches to the correct page of data entries. 37. What is Hash Based indexing? We can organize records using a technique called hashing to quickly find records that have a given search key value. For example, if the file of employee records is hashed on the name field, we can retrieve all records about Joe. In this approach, the records in a file are grouped in buckets, where a bucket consists of a primary page and, possibly, additional pages linked in a chain. The bucket to which a record belongs can be determined by applying a special function, called a hash function, to the search key. Given a bucket number, a hash-based index structure allows us to retrieve the primary page for the bucket in one or two disk l/Os. On inserts, the record is inserted into the appropriate bucket, with ‘overflow’ pages allocated as necessary. 38. What is Functional dependency? Functional dependency describes the relationship between attributes in a relation. For example, if A and B are attributes of relation R, and B is functionally dependent on A ( denoted A B), if each value of A is associated with exactly one value of B. ( A and B may each consist of one or more attributes.) 39. Explain 1NF,2NF,3NF? Normalization is the process of successively reducing relations with anomalies to produce smaller and well structured relations. First normal form (1NF) is a relation that has a primary key and in which there are no repeating groups. Second normal form (2NF) a relation in first normal form in which every non key attribute is fully functionally dependent on the primary key. Third normal form (3NF)a relation that is in second normal form and has no transitive dependencies. 40. What is BCNF? Boyce-Codd normal form (or BCNF or 3.5NF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if for every one of its non-trivial [dependencies] X → Y, X is a superkey—that is, X is either a candidate key or a superset thereof. 41. Compare 3NF & BCNF ? Boyce Codd normal form (also known as BCNF) is a normal form –that is a form that provides criteria for determining a table’s degree of vulnerability to logical inconsistencies and anomalies. 3NF is a normal form that is also used in database normalisation. It is thought that a table is in 3NF if and only if 1) the table is in second normal form (or 2NF, which is a first normal code, or 1NF, that has met the criteria to become a 2NF), and 2) every non-prime attribute of the table is non-transitively dependent on every key of the table (meaning it is not directly dependent on every key). There is another postulation of 3NF that is also used to define the differences between 3NF and the BCNF. 42. What is Transaction? A transaction is a collection of operations that performs a single logical function in a database application 43. What is Recovery? What are its techniques? Process of restoring database to a correct state in the event of a failure. The Recovery techniques are as follows: Deferred Update • Updates are not written to the database until after a transaction has reached its commit point. Immediate Update • Updates are applied to database as they occur. • Need to redo updates of committed transactions following a failure. Shadow Paging. • Maintain two page tables during life of a transaction: current page and shadow page table. • When transaction starts, two pages are the same. • Shadow page table is never changed thereafter and is used to restore database in event of failure. 44. What is locking? Explain locking techniques. DBMS products use sophisticated locking techniques to handle concurrent SQL transactions for many simultaneous users. A shared lock is used by the DBMS when a transaction wants to read data from the database. Another concurrent transaction can also acquire a shared lock on the same data, allowing the other transaction to also read the data. An exclusive lock is used by the DBMS when a transaction wants to update data in the database. When a transaction has an exclusive lock on some data, other transactions cannot acquire any type of lock (shared or exclusive) on the data. 45. What is deadlock? Its Prevention & Avoidance? A deadlock is a situation wherein two or more competing actions are each waiting for the other to finish, and thus neither ever does. 46. What is two phase locking? According to the two-phase locking protocol, a transaction handles its locks in two distinct, consecutive phases during the transactions execution: 1. Expanding phase (number of locks can only increase): locks Care acquired and no locks are released. 2. Shrinking phase: locks are released and no locks are acquired. 47. What is GRANT privileges? The GRANT statement is used to grant the privileges on the database objects to specific users. Normally the GRANT statement is used by owner of the table or view to give other users access to the data. The GRANT statement includes list of the privileges to be granted, name of the table to which privileges apply and user id to which privileges are granted. E.g. 1) Give user ABC full access to employee table: GRANT Select, Insert, Delete, update on employee to ABC 48. What is REVOKE privileges? In most SQL based databases, the privileges that you have granted with the GRANT statement can be taken away with the REVOKE statement. The structure of the REVOKE statement is much similar to that of the GRANT statement. A REVOKE statement may take away all or some of the privileges granted to a user id. E.g. Revoke Select, Insert on employee from ABC. 49. What is Serializability? In concurrency control of databases, transaction processing (transaction management), and various transactional applications, both centralized and distributed, a transaction schedule is serializable, has the Serializability property, if its outcome (the resulting database state, the values of the databases data) is equal to the outcome of its transactions executed serially, i.e., sequentially without overlapping in time. 49. What are Locking parameters? Lock size, Number of locks, Lock timeout 50. what are levels of Locking? Database level locking: Locking can be implemented at various levels of the Database. In its crudest form, the DBMS could lock the entire database for each transaction. Page level locking: Many DBMS products implement locking at the page level. In this scheme, the DBMS locks individual blocks of data (pages) from the disk as they are accessed by a transaction. Row level locking: Over the last several years, most of the major commercial DBMS systems have moved beyond page-level locking to row-level locks. 50. What is Authorization and Integrity manager? It is the program module, which tests for the satisfaction of integrity constraint and checks the authority of user to access data.
Posted on: Tue, 27 Jan 2015 13:37:30 +0000
Trending Topics
Recently Viewed Topics
© 2015