DBMS Interview Questions
Whether you’re going from academia to your first development career or brushing up on DBMS skills for refresher courses, interviews can be nerve-racking!
However, an interview is an excellent opportunity to shine and impress potential employers, resulting in a job offer. Preparation is essential – having worked with various hiring managers over the years, I know they look for proof of specific knowledge and insights during interviews.
We’ll go over the questions and answers in this article. Now, let’s begin!
DBMS Interview Questions and Answers:
1. What is the Database Management System (DBMS)?
The Database Management System (DBMS) is a software system that is used to define, create, maintain, and control access to the data stored in a database.
2. What are the components of the DBMS?
The components of the DBMS include the DDL compiler, query compiler, pre compiler, stored data manager, and database system utilities.
3. What is the entity relationship model (ER) in DBMS?
The entity relationship model (ER) in DBMS describes data as entities, attributes, and relationships.
4. What is an entity in the ER model?
An entity in the ER model is a thing in the Real world with an independent existence, such as a house, person, or employee.
5. What are attributes in the ER model?
Attributes in the ER model are properties that describe the entities.
6. What are the different types of attributes in the ER model?
The different types of attributes in the ER model include composite attributes, simple attributes, single valued attributes, derived attributes, and stored attributes.
7. What is a composite attribute in the ER model?
A composite attribute in the ER model can be divided into further paths, while a simple attribute cannot be further divided.
8. What is a single valued attribute in the ER model?
A single valued attribute in the ER model has a single value or just one value for a particular entity.
9. What is a multi-valued attribute in the ER model?
A multi-valued attribute in the ER model can have multiple values for a particular entity.
10. What is a derived attribute in the ER model?
A derived attribute in the ER model is an attribute that can be derived from other attributes.
11. What is a stored attribute in the ER model?
A stored attribute in the ER model is an attribute from which the value of other attributes is derived.
12. What is a complex attribute in the ER model?
A complex attribute in the ER model has both multivalued and composite components.
13. What is a null value in the ER model?
A null value in the ER model is something that is not applicable or unknown and can be used when an entity does not have an applicable value for an attribute.
14. What is the concept of relationships in an E-R diagram or E-R model?
The concept of relationships in an E-R diagram or E-R model refers to the connections between different entities.
15. What is an entity type in the ER model?
An entity type in the ER model is a collection of entities with the same attributes.
16. What is an entity set in the ER model?
An entity set in the ER model is a collection of entities of a particular entity type at a specific point in time.
17. What is a key attribute in the ER model?
A key attribute in the ER model is a unique key that can identify each entity.
18. What is a value set of attributes in the ER model?
A value set of attributes in the ER model is a set of values that can be assigned to a particular attribute.
19. What is the degree of relationship in the ER model?
The degree of relationship in the ER model refers to the number of entity types that participate in a relationship, which tells how many entities are associated or linked together.
20. What are the different relationship constraints in the ER model?
The different relationship constraints in the ER model include the cardinality ratio, which is the maximum number of relationship instances that an entity can participate in.
21. What are the possible cardinality ratios for binary relationships in the ER model?
The possible cardinality ratios for binary relationships in the ER model include ones to one or one to one, one to n or one to many, n to one or many to one, and m to n or many to many relationships.
22. What is the E-R model?
The E-R model is a framework for understanding relationships between entities, their degree of relationship, and the constraints they impose.
23. What is cardinality ratio?
Cardinality ratio is a concept that refers to the maximum number of relationship instances an entity can participate in.
24. What is participation constraints?
Participation constraints are another constraint that specifies whether the existence of an entity depends on its being related to another entity.
25. What are the two types of participation constraints?
The two types of participation constraints are total participation and partial participation.
26. What is the sample database application discussed earlier?
The sample database application discussed earlier includes four entity types: employee, department, and project.
27. What is the cardinality ratio for these Relationships?
The cardinality ratio for these Relationships is m, where one employee can work on any number of projects and one project can have any number of employees to work on.
28. What is the one-to-one or many-to-many relationship?
In the one-to-one or many-to-many relationship, one employee can work on any number of projects and one project can be controlled by maximum one department.
29. What are the attributes of one-to-one or one-to-n relationship types?
Attributes of one-to-one or one-to-n relationship types can be migrated to one of the participating entity types.
30. What must be known to create an E-R diagram?
To create an E-R diagram, one needs to know the entity types, attributes for each entity type, relationships, and relationship constraints. Relationship types can also have attributes, such as one-to-one, one-to-n, or n-to-one Relationship types.
31. What is the cardinality ratio in ER diagrams?
The cardinality ratio is a concept that refers to the maximum number of relationships instances an entity can participate in.
32. What are participation constraints in ER diagrams?
Participation constraints are another constraint that specifies whether the existence of an entity depends on its being related to another entity.
33. What are the two types of participation constraints?
The two types of participation constraints are total participation and partial participation.
34. What is the sample database application discussed earlier?
The sample database application discussed earlier includes four entity types: employee, department, and project.
35. What is the min max notation in ER diagrams?
The min max notation associates a pair of integer numbers with each participation of an entity type in a relationship type, and is used to specify structural constraints, such as cardinality ratio and participation constraints.
DBMS Training
The enhanced ER model or EER model is the entity-relationship diagram that includes two terminologies: generalization and specialization.
37. What is generalization in ER diagrams?
Generalization is a bottom-up approach where two lower-level entities combine to form a higher-level entity.
38. What is specialization in ER diagrams?
Specialization is a top-down approach where an entity is divided into subclasses.
39. How do generalization and specialization help to understand the concepts of relationships in ER diagrams?
Both generalization and specialization help to understand the concepts of relationships in ER diagrams by providing a way to organize and structure the Relationships between entities.
40. What is the first step in database design?
The first step in database design is requirements collection and analysis, which involves interviewing or asking database users about their requirements and trying to understand and document the data requirements of those users.
41. What is the purpose of role names and alternative notations in ER diagrams?
Role names and alternative notations are crucial in understanding the Relationships between entities in ER diagrams.
They help to distinguish the meaning of each participation and provide a more comprehensive understanding of the Relationship between entities.
42. What is the database design process?
The database design process involves several steps, including the collection and analysis of user data requirements, functional requirements, conceptual design, logical design, physical design, and physical design.
43. What is functional requirements in database design?
Functional requirements involve user defined operations that must be applied to the database, such as retrieval and updation of data.
44. What is the next step in the database design process?
The next step in the database design process is the logical design, which involves the actual implementation of the database using commercial DBMS like Microsoft SQL Server or Akul.
45. What is the final step in the database design process?
The final step in the database design process is the physical design, where internal storage structures, indexes, access parts of files, and application programs are designed and implemented as transactions corresponding to these transaction specifications.
46. What are weak entity types in database design?
Weak entity types are entity types that do not have key attributes of their own, while strong entity types have their own key attributes.
47. What do entities of weak entity types identify themselves by?
Entities of weak entity types identify themselves by relating to entities of another entity, known as the identifying entity type or owner entity type.
48. What symbols are used in the ER diagram?
Symbols used in the ER diagram include entity, attribute, multi-valued attribute, composite attribute, derived attribute, and identifying relationship.
49. What is a multi-valued attribute in the ER diagram?
A multi-valued attribute contains a set of values for a particular entity.
50. What is a composite attribute in the ER diagram?
A composite attribute can be divided into further attributes.
51. What is a derived attribute in the ER diagram?
A derived attribute is an attribute whose value is derived from another attribute.
52. What is an identifying relationship in the ER diagram?
An identifying relationship is a relationship between a weak entity type and its owner entity type.
53. What is the company database used for?
The company database is a crucial tool for tracking the company’s departments, projects, and dependents.
54. What are the initial requirements for the company database?
The initial requirements for the company database include identifying department names, numbers, locations, and the manager’s start date, the number of projects, each with unique names, numbers, and locations, employee details, and the number of dependents such as spouses, parents, or children.
55. What are the four entity types identified in the company database?
The four entity types identified in the company database are department, project, employee, and dependent.
56. What are the key attributes of the department entity in the company database?
The key attributes of the department entity in the company database are department name and department number.
57. What are the unique attributes of the project entity in the company database?
The unique attributes of the project entity in the company database are project names and numbers.
58. What is the dependent entity in the company database?
The dependent entity in the company database is an entity that has a partial key, the dependent name, which can be combined with a strong entity type to uniquely identify its entities.
59. What are the attributes of the dependent entity in the company database?
The attributes of the dependent entity in the company database are employee, dependent name, sex, birth date, and relationship to the employee.
60. What does the DDL compiler do in the DBMS?
The DDL compiler processes schema definitions specified in the data definition language and stores them in the catalog.
61. What does the query compiler do in the DBMS?
The query compiler handles high-level queries by creating a database access code and calling a runtime database processor for execution.
62. What does the pre compiler do in the DBMS?
The pre compiler extracts DML commands from an application program written in a host programming language like C, C++, or Java, and sends these commands to the DML compiler.
63. What does the DML compiler do in the DBMS?
The DML compiler compiles the extracted commands into an object code for access to the database.
64. What is the Role of the catalog in the DBMS?
The catalog contains information such as file size, data types, storage details, and constraints.
65. What does the stored data manager in the DBMS do?
The stored data manager controls access to the information stored on the disk and performs data transfer between the disk and main memory.
DBMS Training
66. What are common utility functions in DBMS?
Common utility functions in DBMS include loading, backup, file Reorganization, performance monitoring, sorting files, monitoring user access, and performing other functions.
67. What does loading do in DBMS?
Loading loads existing data files into the database, reformats the data based on the source format and target of the destination data file format or structure, creates a backup copy of the database, and provides incremental backups.
68. What does file Reorganization do in DBMS?
File Reorganization improves performance by reorganizing a database file into a different file organization.
69. What does performance monitoring do in DBMS?
Performance monitoring monitors database usage and provides complete statistics to the database administrator, helping them decide whether to reorganize files to improve performance.
70. What are other tools available to database designers, administrators, and users?
Other tools available to database designers, administrators, and users include case tools, data dictionary or data repository systems, application development environments, and communication software.
71. What are application development environments?
Application development environments provide an environment for developing database applications.
72. What does communication software do?
Communication software allows users to remotely access the database through computer terminals, workstations, or local personal computers.
73. What is the database system environment?
The database system environment includes various component modules, database system utilities, and tools available to database designers and administrators.
74. What is the initial use of decentralized DBMS architecture?
Decentralized DBMS architecture was initially used by mainframe computers for processing system functions.
75. What led to the development of client server architecture?
As hardware prices declined, personal computers and workstations replaced mainframe terminals, leading to the development of client server architecture.
76. What does the DBMS architecture consist of?
The DBMS architecture consists of various components, including the database system utilities, and tools available to database designers and administrators.
77. What is the difference between decentralized and client server architecture?
Decentralized architecture was used by mainframe computers, while client server architecture was developed as personal computers and workstations replaced mainframe terminals.
78. What is client server architecture?
Client server architecture was developed to handle large numbers of personal computers, workstations, printers, files servers, etc. connected via a network. The goal was to define specialized servers with specific functionalities.
79. What are the two main types of basic client server architectures?
The two main types of basic client server architectures are two-tire and three-tire.
80. What is the difference between two-tire and three-tire client server architectures?
In two-tire client server architectures, the user interfaces and application programs moved to the client side, while query and transaction functionality remained on the server side.
In three-tire client server architectures, there is an additional intermediate layer between the client and the database server, called the application server or web server.
81. What is Open Database Connectivity (ODBC)?
Open Database Connectivity (ODBC) provides an application programming interface (API) that allows programs from the client side to call the DBMS on the server side.
82. What is the main purpose of client server architecture?
The main purpose of client server architecture is to define specialized servers with specific functionalities, such as connecting multiple personal computers to file servers, which maintain the files of the client machines, or a printer server that can be connected to various printers and forward print requests from the client.
83. How does the two-tire architecture work?
The two-tire architecture is a system that establishes a connection between the client and the server, allowing the client program to communicate with the database on the server side.
84. How does the three-tier architecture work?
The three-tier architecture involves the user interface, application rules, and data acting as the three-tires. The user interface, application rules, and data act as the three-tires.
85. What is the DBMS architecture?
DBMS (Database Management System) architecture Refers to the overall design and organization of a database management system, including its hardware and software components.
86. How has the DBMS architecture evolved over time?
The DBMS architecture has evolved over time, with the introduction of client server architectures to better accommodate the needs of various users.
87. What is the difference between a client and a server in a client server architecture?
In a client server architecture, a client is a machine that provides user interface capabilities and local processing, allowing users to access additional functionality that does not exist in their machine. A server is a machine that provides services to the client machines like access to files, printing, or the database.
88. What is SQL?
SQL is a combination of the data definition language (DML) and the view definition language (VDL).
89. What is the difference between high level and low level DMLs?
High level DMLs specify complex database operations concisely, while low level DMLs are embedded in general purpose programming languages like C, C++, and Java. High level DMLs can retrieve many records in a single DML statement, while low level DMLs use looping to retrieve and process each record from a set of records.
90. What is the purpose of using DML in a database?
The purpose of using DML in a database is to manipulate data, including retrieval, update, modification, insertion, or deletion.
91. What is the role of DML in the three-schema architecture?
In a true three-schema architecture, DML is used to specify user views and their mappings to the conceptual schema.
92. What are the types of DMLs?
The types of DMLs or data manipulation languages vary depending on the specific requirements of the database management system.
93. What are the different languages provided by DBMS?
DBMS provides various languages, including data definition language, storage definition language, view definition language, and data manipulation language.
94. What are the different interfaces provided by DBMS?
DBMS provides interfaces such as menu-based interfaces, form-based interfaces, graphical user interfaces, natural language interfaces, parametric users, and database administrator interfaces.
95. What is the purpose of natural language interfaces?
Natural language interfaces accept requests in natural languages and try to understand them using their own schema and dictionary of important words.
96. What is the purpose of parametric users?
Parametric users, such as reservation agents or bank tellers, have small operations that must be performed repeatedly.
97. What is the purpose of database administrator interfaces?
Database administrator interfaces provide privileged commands that can only be used by database administrators and their staff.
98. How are DBMS classified?
DBMS is classified based on various criteria, including data models, number of users, cost of DBMS, number of sites, types of access path, and generality.
99. What is the most popular data model in DBMS?
The most popular data model in DBMS is the relational data model, which represents databases as collections of tables with fields or attributes.
100. What is the object-oriented database model?
The object-oriented database model is based on real-world situations and defines databases in terms of objects, their properties, and operations. However, this model is not widely used.
To summarize, a DBMS (Database Management System) is a software system that allows users to define, create, maintain, and control access to databases.
The DBMS architecture has developed over time, with the advent of client-server architectures to better meet the needs of varied users.
These architectures provide improved interaction between clients and servers, resulting in more efficient and effective system management.
There are two sorts of basic client server architectures: two-tire and three-tire, with the three-tierarchitecture Requiring an additional intermediary layer between the client and the database server.
Open Database Connectivity (ODBC) is an application programming interface (API) that enables client-side programs to communicate with the server-side database management system.
Overall, DBMS architecture is critical for managing and organizing data in a database management system.
I hope you can rock and bang in your next interview.
All the Best!!!
DBMS Course Price
Saniya
Author