300+ [LATEST] Dbms Interview Questions and Answers

Q1. Why Is A Database Considered To Be “self-describing”?

In addition to the users’ data, a database contains a description of its own structure. This descriptive data is called “metadata.”

Q2. What Do You Understand By Dependency Preservation?

Given a relation R and a set of FDs F, dependency preservation states that the closure of the union of the projection of F on each decomposed relation Ri is equal to the closure of F. i.e.,
((PR1(F)) U … U (PRn(F)))+ = F+
if decomposition is not dependency preserving, then some dependency is lost in the decomposition.

Q3. What Is Object Oriented Model?

This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

Q4. What Is Normalization?

It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties

(1)Minimizing redundancy,
(2)Minimizing insertion, deletion and update anomalies.

Q5. Define A Surrogate Key, Describe The Ideal Primary Key And Explain How Surrogate Keys Meet This Ideal?

The ideal primary key is short, numeric and fixed. A surrogate key is a unique, DBMS-supplied identifier intended to be used as the primary key of a table. Further, the DBMS will not allow the value of a surrogate key to be changed. The values of a surrogate key have no meaning to the users and are usually hidden on forms and reports. By design, they are short, numeric and fixed and thus meet the definition of the ideal primary key.

Q6. What Is Data Independence?

Data independence me that “the application is independent of the storage structure and access strategy of data”. In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.
Two types of Data Independence:
• Physical Data Independence: Modification in physical level should not affect the logical level.
• Logical Data Independence: Modification in logical level should affect the view level.

Q7. What Is 3nf?

A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true
@X is a Super-key of R.
@A is a prime attribute of R.
In other words, if every non prime attribute is non-tritively dependent on primary key.

Q8. Define The “integrity Rules”?

There are two Integrity rules.
• Entity Integrity: States that “Primary key cannot have NULL value”
• Referential Integrity: States that “Foreign Key can be either a NULL value or should be Primary Key value of other relation.

Q9. Explain Why An Information Gap Exists In Most Organizations?

One reason that an information gap exists is the fact that systems have been developed in separate, segmented efforts. This has helped the data from being stored in an integrated database and thus the data is in an inconsistent structure. The other reason for the gap is that most systems are created to support the operational aspect of an organization. The systems were not developed for decision making.

Q10. List The Three Types Of Business Rules And Define Each Of Them?

A derivation is a statement that is derived from other knowledge. A structured assertion is a statement that expresses some aspect of the static structure of an organization. An action assertion is a statement of a constraint on the actions of an organization.

Q11. What Is Enterprise Resource Planning (erp), And What Kind Of A Database Is Used In An Erp Application?

Enterprise Resource Planning (ERP) is an information system used in manufacturing companies and includes sales, inventory, production planning, purchasing and other business functions. An ERP system typically uses a multiuser database.

Q12. What Are Some Of The Important Security Features Of A Dbms?

One of the features includes the use of views which allows the presentation of only data needed by someone and limits the capability of database updates. The use of integrity controls includes such things as domains, assertions, and checks. Also authorization rules, user-defined procedures, encryption, authentication schemes, and backups are important.

Q13. Explain The Relationship Between Entity, Entity Class, And Entity Instance?

An entity is something that can be identified in the users’ work environment, something that the users want to track. Entities of a given type are grouped into entity classes. An entity instance is the representation of a particular entity.

Q14. What Is Data Model?

A collection of conceptual tools for describing data, data relationships data semantics and constraints.

Q15. Explain The Difference Between A Dynamic And Materialized View?

A dynamic view may be created every time that a specific view is requested by a user. A materialized view is created and or updated infrequently and it must be synchronized with its associated base table(s).

Q16. What Is “trparent Dbms”?

It is one, which keeps its Physical Structure hidden from user.

Q17. Discuss Some Of The Techniques That Can Be Used To Tune Operational Performance?

Choosing primary and secondary keys can increase the speed of row selection, joining, and row ordering. Selecting the appropriate file organization for base tables and indexes can also improve performance. Clustering related rows together and maintaining statistics about tables and indexes can lead to increased efficiency.

Q18. Explain What Needs To Happen To Convert A Relation To Third Normal Form?

First you must verify that a relation is in both first normal form and second normal form. If the relation is not, you must convert into second normal form. After a relation is in second normal form, you must remove all tritive dependencies.

Q19. What Is Vdl (view Definition Language)?

It specifies user views and their mappings to the conceptual schema.

Q20. What Is The Job Of The Information Stored In Data-dictionary?

The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.

Q21. What Is Buffer Manager?

It is a program module, which is responsible for fetching data from disk storage into main memory and deciding what data to be cache in memory.

Q22. What Is A Candidate Key?

candidate key is a column in a table which has the ability to become a primary key.

Q23. What Is Record-at-a-time?

The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.

Q24. What Is Database Trigger?

A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. A database trigger can call database procedures that are also written in PL/SQL.

Q25. What Is Relational Calculus?

It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.

Q26. What Is Storage Manager?

It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.

Q27. What Is 2nf?

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

Q28. What Are The Steps To Follow When Preparing To Create A Table?

@Identify the data type, length, and precision for each attribute. @Identify the columns that can accept a null value. @Identify the columns that need to be unique. @Identify primary and related foreign keys with the parent table being created before the child. @Determine default values. @Determine where the domain values are that need to be constrained. @Create the indexes.

Q29. What Are The Advantages Of Using Stored Procedures?

The advantages of stored procedures are

greater security,
decreased network traffic,
the fact that SQL can be optimized and
code sharing which leads to less work, standardized processing, and specialization among developers.

Q30. Describe The Three Levels Of Data Abstraction?

The are three levels of abstraction:
• Physical level: The lowest level of abstraction describes how data are stored.
• Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
• View level: The highest level of abstraction describes only part of entire database.

Q31. Name And Briefly Describe The Five Sql Built-in Functions?

COUNT: computes the number of rows in a table. SUM: totals numeric columns. AVG: computes the average value. MAX: obtains the maximum value of a column in a table. MIN: obtains the minimum value of a column in a table.

Q32. What Is A Checkpoint And When Does It Occur?

A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.

Q33. What Is An Entity Type?

It is a collection (set) of entities that have same attributes.

Q34. You Have Been Given A Set Of Tables With Data And Asked To Create A New Database To Store Them. When You Examine The Data Values In The Tables, What Are You Looking For?

Multivalued dependencies,
Functional dependencies,
Candidate keys,
Primary keys and
Foreign keys.

Q35. Name And Describe Three Types Of Binary Relationships?

1:1 – a single entity instance of one type is related to a single-entity instance of another type.
1:N – a single entity instance of one type is related to many-entity instances of another type.
M:N – many-entity instances of one type relate to many-entity instances of another type.

Q36. What Is A Sql View? Briefly Explain The Use Of Views?

A SQL view is a virtual table built from other tables or views. Views are used to

hide columns or rows,
the results of computed columns,
hide complicated SQL syntax,
layer built-in functions,
provide a level of indirection between application programs and tables,
assign different sets of processing permissions to tables, and
to assign different sets of triggers to the same table.

Q37. What Is The Inconsistent Values Problem? Include An Example Not Used In The Text?

The inconsistent values problem occurs when different users or data sources use slightly different forms of the same data value. One example is where automobiles are specified as “Ford, 2-door, Red” in one cell and “Red Ford 2-door’ in another.

Q38. What Is The Difference Between Horizontal And Vertical Partitioning?

Horizontal partitioning is where some rows of a table are placed into the base relations at one site and other rows are placed at another site. Vertical partitioning is where some columns of a table are placed into the base relations at one site and other columns are placed at another site but each all of these relations must share a common domain.

Q39. Describe Web Services?

Web Services are improving the ability of computers to communicate over the Internet. These services use XML programs and usually run in the background. Easier integration of applications may be possible because developers do not need to be familiar with the technical details with applications that are being integrated. UDDI is a technical specification for creating a distributed registry of Web services and businesses that are open to communicating through Web services.

Q40. Explain The Meaning Of Each Of The Traction Levels Supported By Sql Server?

The strictest isolation level is SERIALIZABLE. With it, SQL Server places a range lock on the rows that have been read. This level is the most expensive to use and should only be used when absolutely required. The next most restrictive level is REPEATABLE READ, which me SQL Server places and holds locks on all rows that are read. It is possible to make dirty reads by setting the isolation level to READ UNCOMMITTED, which is the least restrictive level. READ COMMITTED is the default isolation level.

Q41. In Ole Db, What Is The Difference Between An Interface And An Implementation?

An OLE DB interface is specified by a set of objects, and the properties and methods that they expose, and OLE DB defines standardized interfaces. An object need not expose all of its properties and methods in a given interface. An OLE DB implementation defines how the object supports the interface. The implementation is completely hidden from the user. Thus developers of an object are free to change the implementation whenever they want, but they should not change the interface without consulting their users.

Q42. Provide An Overview Of Xml?

XML Is used to structure and manipulate data involved with a browser and is becoming the standard for ec ommerce. XML uses tags that are similar to HTML in that they use the angle brackets, but XML describes the content whereas HTML describes the appearance. The XML schema standard was published in May 2001 by W3C.

Q43. Advantages Of Dbms?

• Redundancy is controlled.
• Unauthorised access is restricted.
• Providing multiple user interfaces.
• Enforcing integrity constraints.
• Providing backup and recovery.

Q44. Write An Sql Select Statement To Display All The Columns Of The Student Table But Only Those Rows Where The Grade Column Is Greater Than Or Equal To 90?

SELECT * FROM STUDENT WHERE Grade >= 90;

Q45. What Is An Entity?

It is a ‘thing’ in the real world with an independent existence.

Q46. How Is The Data Structure Of System R Different From The Relational Structure?

Unlike Relational systems in System R
• Domains are not supported
• Enforcement of candidate key uniqueness is optional
• Enforcement of entity integrity is optional
• Referential integrity is not enforced

Q47. What Is A Query?

A query with respect to DBMS relates to user commands that are used to interact with a data base. The query language can be classified into data definition language and data manipulation language.

Q48. What Is An Extension Of Entity Type?

The collections of entities of a particular entity type are grouped together into an entity set.

Q49. Name The Sub-systems Of A Rdbms?

I/O, Security, Language Processing, Process Control, Storage Management, Logging and Recovery, Distribution Control, Traction Control, Memory Management, Lock Management.

Q50. What Is Ddl Interpreter?

It interprets DDL statements and record them in tables containing metadata.