Mar 28, 1) Define Database. A prearranged collection of figures known as data is called database. 2) What is DBMS? Database Management Systems. Commonly asked DBMS interview questions | Set 1 Source: terney.info courses/spring01/G/modpdf Ans: An SQL Join is used to combine data from two or more tables, based on a common field between them. Technical interview questions and answers section on "DBMS Basics" with freshers can download DBMS Basics quiz questions with answers as PDF files and.
|Language:||English, Spanish, French|
|Distribution:||Free* [*Registration Required]|
dbms interview questions and answers for freshers | database management system dbms interview questions with answers pdf, interview questions for dbms. Mar 27, interview questions? If not, then dig into some technical insights for DBMS. Interview Questions. Download DBMS Interview Questions PDF. + Dbms Interview Questions and Answers, Question1: What is a DBMS? Question2: What is Relationship set? Question3: What is Relationship type?.
What Is E-r Model? E-R model is a short name for Entity Relationship model. This model is based on real world. It contains basic objects known as entities and relationship among these objects. What Is Extension And Intension?
It is the number of tuples present in a table at any instance. This is time dependent. It is a constant value that gives the name, structure of table and the constraints laid on it. A database model represents the relationship between one or more databases. The relationship is known as the relational database model. It is an extension of the normal databases without relations. It provides flexibility and allows one database to be in relation with another database.
It can access the data from many databases at one time over the network. Table consists of some properties that are known as attributes. These consist of the representation of entity in the table. They are represented by columns in the table. Entity is referred to the store data about any particular thing. It is the smallest unit inside the table. Relation in the relational database model is defined as the set of tuples that have the same attributes.
Tuple represents an object and also the information that the object contains. Objects are basically instances of classes and used to hold the larger picture.
Relation is described as a table and is organized in rows and columns. The data referenced by the relation come in the same domain and have the same constraints as well. Relations in the relational database model can be modified using the commands like insert, delete etc. Domain describes possible values grouped together that can be given for an attribute.
It is considered the same way as a constraint on the value of attribute. A domain can be attached to an attribute but only if the attribute is an element of specified set. For example: Hence, domain is of high importance. Advantages Of Dbms? Relational database means the relationship between different databases. In relational database user can store and access all the data through the tables which are related to each other.
Relationship between the store data is called base relations and implementation of it is called as tables. When these are implemented they are termed as views or queries.
Derived relations are more useful then base relation, as they can have more information from many relations, but they act as a single relation. Disadvantage In File Processing System?
What Are Constraints In Database? Constraints are kind of restrictions that are applied to the database or on the domain of an attribute.
For example an integer attribute is restricted from and not more than that. They provide the way to implement the business logic and the rules in database. Constraint also used to restrict the data that can be stored in the relations. Domain constraint can be applied to check the domain functionality and keep it safe.. The two principal rules for the relational model are as follows:.
Define The Integrity Rules? Primary key uniquely identify a relationship in a database, whereas foreign key is the key that is in other relation and it has been referenced from the primary key from other table. Primary key remains one only for the table, whereas there can be more than one foreign key. Stored procedure stored inside the database.
This also includes the executable code that usually collects and customizes the operations like insert, encapsulation, etc. These stored procedures are used as APIs for simplicity and security purposes. The implementation of it allows the developers to have procedural extensions to the standard SQL syntax. What Is System R? It is a prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be used in a real life environment to solve real life problems, with performance at least comparable to that of existing system.
Its two subsystems are.
Index is a way to provide quick access to the data and structure. It has indexes maintain and can be created to combine attributes on a relation. Index allows the queries to filter out the searches faster and matching data can be found earlier with simplicity. For example it is same as the book where by using the index you can directly jump to a defined section.
In relational database there is a provision to give multiple indexing techniques to optimize the data distribution. There are many relational operators that are used to perform actions on relational database. These operators are as follows:. Normalization is very essential part of relational model. It consists of set of procedures that eliminates the domains that are non-atomic and redundancy of data that prevents data manipulation and loss of data integrity.
Normal forms are the common form of normalization. It helps in reducing redundancy to increase the information overall. It has some disadvantages as it increases complexity and have some overhead of processing. What Is A View?
A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary. Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database.
Hence accounts for logical data independence. What Is Data Model? A collection of conceptual tools for describing data, data relationships data semantics and constraints. Analytical processing databases are not very normalized. The operations which are used are read most databases.
It is used to extract the data that are ancient and accumulated over long period of time. For this purpose de-normalization occurs that provide smart business applications.
Dimensional tables in star schema are good example of de-normalized data. The de-normalized form must be controlled while extracting, transforming, loading and processing. There should be constraint that user should not be allowed to view the state till it is consistent.
Non-first normal form NFA — it describes the definition of the database design which is different from the first normal form. It keeps the values in structured and specialized types with their own domain specific languages. The query language used in this is extended to incorporate more support for relational domain values by adding more operators.
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.
Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command. This is a primary file organization technique that provides very fast access to records on certain search conditions. A transaction is a logical unit of database processing that includes one or more database access operations.
What are the different phases of Transaction? It is one, which keeps its physical structure hidden from user. What are the primitive operations common to all record management System?
Explain the differences between structured data and unstructured data. Structured data are facts concerning objects and events.
The most important structured data are numeric, character, and dates. Structured data are stored in tabular form. Unstructured data are multimedia data such as documents, photographs, maps, images, sound, and video clips.
Unstructured data are most commonly found on Web servers and Web-enabled databases. What are the major functions of the database administrator? Managing database structure, controlling concurrent processing, managing processing rights and responsibilities, developing database security, providing for database recovery, managing the DBMS and maintaining the data repository. A dependency graph is a diagram that is used to portray the connections between database elements.
Explain the difference between an exclusive lock and a shared lock? An exclusive lock prohibits other users from reading the locked resource; a shared lock allows other users to read the locked resource, but they cannot update it.
Explain the "paradigm mismatch" between SQL and application programming languages. SQL statements return a set of rows, while an application program works on one row at a time. What are the advantages of using stored procedures? 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. Explain the difference between attributes and identifiers.
Entities have attributes. Attributes are properties that describe the entity's characteristics. Entity instances have identifiers. Identifiers are attributes that name, or identify, entity instances. Enterprise Resource Planning ERP is an information system used in manufacturing companies and includes sales, inventory, production planning, downloading and other business functions. An ERP system typically uses a multiuser database.
Describe the difference between embedded and dynamic SQL? These statements do not change unless the source code is modified. The statements generated do not have to be the same each time. A join allows tables to be linked to other tables when a relationship between the tables exists. A subquery is a query that is composed of two queries. Compare a hierarchical and network database model? The hierarchical model is a top-down structure where each parent may have many children but each child can have only one parent.
This model supports one-to-one and one-to-many relationships. The network model can be much more flexible than the hierarchical model since each parent can have multiple children but each child can also have multiple parents. This model supports one-to-one, one-to-many, and many-to-many relationships. 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. 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 transitive dependencies. A unique primary index is unique and is used to find and store a row. A nonunique primary index is not unique and is used to find a row but also where to store a row based on its unique primary index. A unique secondary index is unique for each row and used to find table rows.
A nonunique secondary index is not unique and used to find table rows. Explain minimum and maximum cardinality? Minimum cardinality is the minimum number of instances of an entity that can be associated with each instance of another entity. Maximum cardinality is the maximum number of instances of an entity that can be associated with each instance of another entity. What is deadlock?
How can it be avoided? How can it be resolved once it occurs? Deadlock occurs when two transactions are each waiting on a resource that the other transaction holds. Deadlock can be prevented by requiring transactions to acquire all locks at the same time; once it occurs, the only way to cure it is to abort one of the transactions and back out of partially completed work.
Explain what we mean by an ACID transaction. An ACID transaction is one that is atomic, consistent, isolated, and durable. Durable means that database changes are permanent. Consistency can mean either statement level or transaction level consistency.
With transaction level consistency, a transaction may not see its own changes. Atomic means it is performed as a unit. Under what conditions should indexes be used? Indexes can be created to enforce uniqueness, to facilitate sorting, and to enable fast retrieval by column values.
A good candidate for an index is a column that is frequently used with equal conditions in WHERE clauses. It is the process of defining a set of subclasses of an entity type where each subclass contain all the attributes and relationships of the parent entity and may have additional attributes and relationships which are specific to itself.
It is the process of finding common attributes and relations of a number of entities and defining a common super class for them. What is meant by Proactive, Retroactive and Simultaneous Update? Proactive Update: The updates that are applied to database before it becomes effective in real world.
Retroactive Update: The updates that are applied to database after it becomes effective in real world. Simultaneous Update: The updates that are applied to database at the same time when it becomes effective in real world. Redundant array of inexpensive or independent disks. The main goal of raid technology is to even out the widely different rates of performance improvement of disks against those in memory and microprocessor.
Raid technology employs the technique of data striping to achieve higher transfer rates. A schedule S is serial if, for every transaction T participating in the schedule, all the operations of T is executed consecutively in the schedule, otherwise, the schedule is called non-serial schedule.
What are conflict serializable schedules? A schedule S of n transactions is serializable if it is equivalent to some serial schedule of the same n transactions. A schedule is said to be view serializable if it is view equivalent with some serial schedule. A key of a relation schema is called as a foreign key if it is the primary key of some other relation to which it is related to.
What are the disadvantages of using a dbms? What is Lossless join property? It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition. What is a Phantom Deadlock? In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.
What is a checkpoint and When does it occur? By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes. What is schema? Organized data void of inconsistent dependency and redundancy within a database is called normalization. Advantages of normalizing database are: No duplicate entries Saves storage space Boasts the query performances.
Boosting up database performance, adding of redundant data which in turn helps rid of complex data is called denormalization. Manipulating data in a database such as inserting, updating, deleting is defined as Data Manipulation Language.
They are: Syntax is: It helps in modification of an existing object of database. Its syntax is: ALTER objecttype objectname parameters.
It destroys an existing database, index, table or view. DROP objecttype objectname. Full recordings of two tables is Union All operator.
A distinct recording of two tables is Union. A database object which helps in manipulating data row by row representing a result set is called cursor. They types of cursor are: Implicit cursor: Declared automatically as soon as the execution of SQL takes place without the awareness of the user. Explicit cursor: A query contained by a query is called Sub-query.
Group-clause uses aggregate values to be derived by collecting similar data. Functions which operate against a collection of values and returning single value is called aggregate functions 24 Define Scalar functions.
Scalar function is depended on the argument given and returns sole value. Restrictions that are applied are: Only the current database can have views. You are not liable to change any computed value in any particular view. Full-text index definitions cannot be applied.
Temporary views cannot be created. Temporary tables cannot contain views. A 'correlated subquery' is a sort of sub query but correlated subquery is reliant on another query for a value that is returned. In case of execution, the sub query is executed first and then the correlated query. Storage and access of data from the central location in order to take some strategic decision is called Data Warehousing.
Enterprise management is used for managing the information whose framework is known as Data Warehousing. Joins help in explaining the relation between different tables.
They also enable you to select data with relation to data in another table. The various types are: Blank rows are left in the middle while more than equal to two tables are joined. Blank rows are left at the specified side by joining tables in other side.
Indexes help in improving the speed as well as the query performance of database. The procedure of boosting the collection of indexes is named as Index hunting. Index hunting helps in improving the speed as well as the query performance of database. The followed measures are achieved to do that: