DBMS Important Questions for exam 2021

DBMS Important Questions for exam

DBMS Important Questions for exam 2020

Q: 1 What is a DBMS (Database Management System)?
Answer: Database Management System (DBMS) is a collection of computer programs that control the creation, maintenance, and use of databases. This enables organizations to place database development control in the hands of database administrators (DBAs) and other specialists. A DBMS is a system software package that helps you use a collection of data records and files known as databases. This allows various user application programs to easily access the same database. DBMS can use one of various database models, such as network models or relational models. In large systems, DBMS allows users and other software to store and retrieve data in a structured manner. Instead of having to write a computer program to extract information, users can ask simple questions in the request language. As such, many DBMS packages provide a fourth generation programming language (4GL) and other application development features. This helps to determine the logical organization for the database and access and use information in the database. It provides facilities to control data access, enforce data integrity, manage concurrency, and restore databases from backup. The DBMS also provides the ability to present database information logically to users.

Q: 2 Define the Database.
Answer: A database is a collection of information that is organized so that it can be easily accessed, managed and updated. In one view, databases can be classified according to content types: bibliography, full text, numbers, and images.

In computing, databases are sometimes classified according to their organizational approach. The most common approach is a relational database, a tabular database where data is defined so that it can be reorganized and accessed in a number of different ways. Distributed databases are databases that can be distributed or replicated between different points in a network. Object-oriented programming databases are congruent with data defined in object classes and subclasses.

A computer database usually contains a collection of data records or files,

such as sales transactions, product catalogs and inventory, and customer profiles. Typically, database managers give users the ability to control read / write access, determine report generation, and analyze usage.



Q: 3 What is Data?

Answer: Data is the plural form of datum, which is originally a Latin noun meaning "something given." Today, data is used in English both as a plural noun meaning "facts or pieces of information" and as a single mass noun meaning "information".

In computing, data is information that has been translated into a form that is more convenient to move or process. Relative to computers and transmission media today, data is information that is converted into binary digital forms.


Q.4 Why is it recommended to use a DBMS? Explain by listing some of its main advantages.

Answer: Some of the main advantages of DBMS are as follows:

Controlled Redundancy: The DBMS supports a mechanism to control data redundancy in the database by integrating all data into one database and because data is stored in only one place, data duplication does not occur.
Data Sharing: Sharing data among many users simultaneously can also be done in a DBMS because the same database will be shared among all users and by various application programs.
Backup and Recovery Facility: The DBMS minimizes the pain of backing up data over and over again by providing a 'backup and recovery' feature that automatically backs up data and restores data whenever needed.
Enforcement of Integrity Constraints: Integrity Constraints are very important to be enforced on the data so that the enhanced data after putting some constraints are stored in the database and this is followed by the DBMS.
Data Independence: This means you can change the data structure without affecting the structure of any application program.


Q.5 What types of languages ​​are available in the DBMS?
Answer: Basically, there are 3 types of languages ​​in the DBMS as mentioned below:

1. DDL: DDL is a Data Definition Language used to define databases and schema structures by using several sets of SQL Questions such as CREATE, ALTER, TRUNCATE, DROP and RENAME.
2. DCL: DCL is a Data Control Language that is used to control user access in a database by using several sets of SQL Questions such as GRANT and REVOKE.
3. DML: DML is a Data Manipulation Language that is used to do some manipulations in a database such as Insertion, Deletion, etc. By using several sets of SQL Questions such as SELECT, INSERT, DELETE and UPDATE.

Q: 6 What is SQL (Structured Query Language)?
Ans. Structured Query Language, is a database computer language designed to manage data in a relational database management system (RDBMS), and was originally based on algebra and relational calculus. Its scope includes data insertion, requests, updates and deletions, creation and modification of schemes, and data access control. SQL is one of the first commercial languages ​​for the Edgar F. Codd relational model.


SQL is DSL (Data Sub Language), which is a combination of two languages. These are Data Definition Languages ​​(DDL) and Data Manipulation Languages ​​(DML). Schema changes are part of DDL, while data changes are part of DML.





Q. 7 Explain the key concepts of Primary and Foreign Key.

Answer: A Primary Key is used to uniquely identify records in a database table while a Foreign Key is mainly used to link two or more tables together because this is a particular field in one database table that is the primary key of several other tables.

Example: There are 2 tables - Employees and Departments and both have one field / column which is the same as 'ID' where ID is the primary key of the Employee table while this is a foreign key for the Department table.

Q. 8 What is the use of DROP commands and what are the differences between DROP, TRUNCATE and DELETE commands?

Answer: The DROP command is a DDL command that is used to drop / delete existing tables, databases, indexes or views from a database.

The main differences between DROP, TRUNCATE and DELETE commands are:

DROP and TRUNCATE commands are DDL commands that are used to delete tables from a database and once a table is deleted, all privileges and indexes associated with the table can also be deleted. 2 This operation cannot be canceled and hence must be used with extreme caution.

DELETE Command, on the other hand, is a DML Command which is also used to delete rows from a table and this can be canceled.


Q. 9 Explain the concept of ACID properties in a DBMS?

Answer: The ACID property is a combination of Atomicity, Consistency, Isolation, and Durability. This property is very helpful in enabling a safe and secure way of sharing data among many users.

1. Atomicity: This is based on the concept of "all or nothing" which basically means that if there is an update that occurs in the database then the update must be available to everyone other than users and application programs or it should not be available to anyone outside the user program and application.
2. Consistency: This ensures that consistency is maintained in the database before or after any transactions that occur in the database.
3. Isolation: As the name implies, this property states that each transaction that occurs separately from others, ie transactions that have started but have not yet been completed must be separated from the others so that other transactions are not affected by this transaction. .
4. Durability: This property states that data must always be in a durable state ie any data that is committed must be available in the same state even if a failure or restart occurs in the system.

Q 10. What is the Difference Between Files and Databases? Can files qualify as a database?

Answer: The main difference between a simple file and a database is that a database has an independent way (SQL) to access information while a simple file does not fulfill the storage, management, and retrieval part of the database but is not an independent way to access the data. Many experienced programmers think that the main difference is that files cannot provide the multi-user capabilities provided by DBMS. But if we look at some old COBOL and C programs where files are the only way to store data, we can see functions such as locking, multi-user etc. provided very efficiently. So it's a matter of debate if some interviewers consider this to be the main difference between the file and the database receiving it ... getting into the debate might lose work.



Q 11. What is Data Independence?

Answer: Data independence determines that the application does not depend on the storage structure and data access strategy. This means that the ability to change the definition of a scheme at one level must not affect the definition of a scheme at the next higher level.
There are two types of data independence:

1. Independence of physical data
2. Logical data independence

Q 12. What is the Difference Between Dbms and Rdbms?

Answer: DBMS provides a systematic and organized way to store, manage and retrieve logically related information from gathering. RDBMS also provides what the DBMS provides but on top of that it provides the integrity of the relationship. So in short we can say

RDBMS = DBMS + INTEGRITY OF REFERENCES

This relationship is defined using "Foreign Key" in RDBMS. Many DBMS companies claim DBMS products are RDBMS compliant, but according to industry rules and regulations if the DBMS meets twelve CODD rules, this is really RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc.) meet all twelve CODD rules and are considered as truly RDBMS.

Q 13. What is Functional Dependency?

Answer: Functional dependency is the starting point for normalization. It exists when the relationship between two attributes allows you to uniquely determine the value of the corresponding attribute.

Q 14. Can You Explain Insert, Update and Delete Questions in DBMS?

Answer: Insert statement is used to insert a new row into the table. Update to update the data in the table. Delete statement to delete records from the table. The code snippet below for Insert, Update and Delete: -

Enter into wbEmployee SET name = 'maxwell', age = '22 ';
UPDATE wbEmployee SET age = '22 'where name =' maxwell ';
REMOVE FROM wbEmployee WHERE name = 'david';

Q 15. What are Entity Sets?

Answer: This is a collection of all entities of a certain entity type in the database.

Q 16. What is the E-R Model?

Answer: The E-R model is a short name for the Entity Relationship model. This model is based on the real world. It contains basic objects (known as entities) and relationships between these objects

Q 17. What Do You Understand With Relationships in the Relational Database Model?

Answer: Relationships in the relational database model are defined as sets of tuples that have the same attributes. Tuples represent an object and also the information that contains the object. Objects are basically instances of classes and are used to hold larger images. Relationships are explained as tables and arranged in rows and columns. Data referenced by relations come in the same domain and has the same constraints. Relationships in the relational database model can be modified using commands such as insert, delete etc.

Q 18. What are the Two Principles of the Relational Database Model? What's the Difference Between Them?

Answer: The two main rules for the relational model are as follows:

Entity integrity: This is used to maintain integrity at the entity level
Referential integrity: used to maintain integrity on all values ​​that have been referred.
The differences between them are as follows:

Entity integrity says that in a database every entity must have a unique key; on the other hand referential integrity tells us that in the database every table of values ​​for all foreign keys will remain valid.
Referential integrity is based on entity integrity but not vice versa.
For example: if a table is present and there is a set of columns in which one column has a set of parent keys then to ensure that the table does not contain duplicate values, a unique index is defined in the column containing the parent key.

Q 19. What Do You Understand With Database Normalization?

Answer: Normalization is a very important part of the relational model. It consists of a set of procedures that eliminate non-atomic domains and data redundancy which prevents data manipulation and loss of data integrity. Normal form is a common form of normalization. This helps in reducing redundancy to improve overall information. This has some disadvantages because it increases complexity and has some processing overhead.

Q 20. What Do You Understand Data Independence?

Answer: Data independence tells about the independence of data in the application. Usually it relates to the storage structure and represents the ability to modify the schema definition. That does not affect the definition of the schema that is being written at a higher level. There are two types of data independence:

Independence of physical data: allows modifications to be made at the physical level and does not affect the logical level.
Logical data independence: allows modifications to be made at the logical level and affects the display level.


NOTE: Logic Data Independence is more difficult to achieve.


Q 21. Determine the B-tree.

Ans:- Data structures in the form of trees that store sorted data and search, insertion, sequential access, and deletion are permitted in logarithmic time.

Q 22. What is functional dependency? And explain all types of functional dependencies.
Answer: - Functional dependency is when one attribute determines another attribute in the DBMS system. Functional Dependency plays an important role in finding differences between good and bad database designs.



NOTES: - The functional dependency in is denoted by x-y between to set the f x & y attribute.

Functional dependency type: -

1. Multivalued dependency:-Multivalued dependencies occur in situations where there are several independent multivalued attributes in one table. Multivalued dependency is a complete constraint between two sets of attributes in a relation. This requires certain tuples to be present in a relationship.

2. Trial functional dependency:
Trivial dependence is a set of attributes called trivial if the attribute set is included in that attribute.

3. Non-trivial functional dependencies in the DBMS
Functional dependency which is also known as non-trivial dependence occurs when A-> B applies where B is not part of A. In a relationship, if attribute B is not a subset of attribute A, then it is considered as non-trivial dependency.

4. Transitive dependence:
Transitive is a type of functional dependency that occurs when t is indirectly formed by two functional dependencies.


Q 23. What are the key types in the DBMS?

Answer: - DBMS has seven types of keys, each of which has a different function:

1. Super keys: - Super keys are a group of single or multiple keys that identify rows in a table. Super keys may have additional attributes that are not needed for unique identification.

2.Candidate key: - A candidate key is a column of attributes or a set of attributes in a relation / table that qualifies for the unique of each tuple (row) of a relation can have one or more than one candidate key. The candidate key is also not the key to dinner.

3. Primary key: -A column or group of columns in a table that helps us uniquely identify each row in that table called the primary key. This DBMS cannot be duplicated. The same value cannot appear more than once in a table.

4. Alternative key: - All keys that are not primary keys are called alternative keys. This is a candidate key that is not currently the primary key. However, tables may have a single or multiple choices for primary keys.

5. Foreign key: - Foreign key is a column that is added to make a relationship with another table. Foreign keys help us maintain data integrity and also allow navigation between two instances of different entities. Every relationship in the model needs to be supported by a foreign key.

6. Compound key: Compound key has many fields that allow you to uniquely recognize certain records. It is possible that each column may not be unique by itself in the database. However, when combined with other columns or composite key combination columns it becomes unique.



7. Composite key: - A key that has many attributes to identify rows in a table is uniquely called a composite key. The difference between a compound and a composite key is that each part of the combined key can be a foreign key, but the composite key may or may not be part of a foreign key.

Q 24. Explain three architectural schemes.
Answer: - This architecture has three levels:

1. External level or user display
2. Conceptual or logical level
3. Internal or physical level



1. External level
This is also called display level. The reason this level is called "display" is because some users can see the desired data from this level that is taken internally from the database with the help of conceptual and internal level mapping.

Users do not need to know the details of the database schema such as data structures, table definitions, etc. users only pay attention to data that is returned to the display level after being retrieved from the database (at the internal level).

The external level is the "top level" of the Three Level DBMS Architecture.

2. Conceptual level
This is also called the logical level. All database designs such as data relationships, data schemes, etc. Described in this level.

Database constraints and security are also applied at this level of architecture. This level is managed by DBA (database administrator).

3. Internal level
This level is also known as the physical level. This level explains how data is actually stored in a storage device. This level is also responsible for allocating space to data. This is the lowest level of architecture.



Q 25. What is Data Independence? And everything illustrates.

Answer: - The database sees that the extraction of any three levels of change at any level can affect other level schemes (Database structure)

this should not lead to a redesign, re-implementation of a database called independence data.

NOTES: - The ability to modify the schema definition at one level without affecting the definition at a higher level is called data independence.

Type of Data Independence

In DBMS there are two types of data independence

• Independence of physical data
• Logical data independence.
• Independence of Physical Data

Physical data independence helps you separate the conceptual level from the internal / physical level. This allows you to provide a logical description of the database without the need to specify a physical structure. Compared to Logical Independence, it's easy to achieve physical data independence.

With Physical Independence, you can easily change the structure or physical storage device with effects on conceptual schemes. Any changes made will be absorbed by the mapping between conceptual and internal levels. Physical data independence is achieved by the internal database level and then the transformation from the conceptual database level to the internal level.

Logical Data Independence
Logical data independence refers to the ability to modify the conceptual / schema level without changes in the schema followed at the level of logical data independence display ensuring that the application program. a conceptual modification is a logical structure of the database.

Q 26. What are the mapping and types?
Ans:-

Mapping:-

Mapping is the process of changing one level to another. In this process, data at one level is related to data at another level.

There are two types of mapping:

• Conceptual / Internal Mapping
• External / Conceptual Mapping

1. Conceptual / Internal Mapping:

Conceptual / internal mapping defines correspondence between conceptual display and store database.
This determines how conceptual notes and fields are represented at the internal level.
It connects conceptual schemes with internal schemes.
If the store database structure is changed.
If changes are made to the definition of a storage structure-then the conceptual / internal mapping must be changed accordingly, so that the conceptual scheme can remain invariant.
There might be a mapping between conceptual and internal levels.

2. External / Conceptual Mapping:

External / conceptual mapping defines correspondence between certain external views and conceptual views.
It connects every external scheme with a conceptual scheme.
The difference that can exist between these two levels is analogous to the difference that exists between the conceptual display and the stored database.
Example: fields can have different data types; fields and record names can be changed; several conceptual fields can be combined into one single external field.
A number of external displays can exist at the same time; a number of users can share given external displays: various external displays can overlap.
There may be some mapping between the external and conceptual levels

Thanks for watching any quarry please comment thankyou,