Top 50 Dbms Interview Questions You Must Prepare 21.Apr.2024

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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).

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

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.

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.

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

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

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.

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

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.

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.

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.

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.

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.

@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.

The advantages of stored procedures are

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

 

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.

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.

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.

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

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.

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

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

 

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.

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.

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.

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.

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.

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.

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

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

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

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.

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

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

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