DBMS 2 Mark (Important) Questions with Answer
2 Marks (IMP)
1. Define DBMS. (repeater 2018)
A Database Management System (DBMS) is software designed to store, retrieve, define, and manage data in a database.
2. Define Data Model. List categories.
Data modeling is the process of diagramming data flows. When creating a new or alternate database structure, the designer starts with a diagram of how data will flow into and out of the database.
3. Define Data Independence. Mention their types.
Data independence is the ability to modify one level of a DBMS without affecting the next higher level’s data structure or access methods. It’s of two types, physical and logical.
4. Who is endusers ? Mention their categories.
- End users are the people whose jobs require access to a database for querying, updating and generating reports.
- Database Administrators (DBA)
- Database Designers.
- System Analysts.
- Application Programmers / Back-End Developers.
- Naive Users / Parametric Users.
- Sophisticated Users.
- Casual Users / Temporary Users.
5. What is the role played by DBA?
A Database Administrator (DBA) is an individual or person responsible for controlling, maintaining, coordinating, and operating a database management system. Managing, securing, and taking care of the database systems is a prime responsibility.
6. What is schema and instance ?
- A database schema defines how data is organized within a relational database; this is inclusive of logical constraints such as, table names, fields, …
- Schema refers to the overall description of any given database. Instance basically refers to a collection of data and information that the database stores at any particular moment.
7. Define Metadata ?
Metadata is simply defined as data about data. It means it is a description and context of the data. It helps to organize, find and understand data.
8. Define data and database.
- Data : means known facts that can be recorded and that have implicit meaning.
- Database : It is a collection of logically related data.
- Database example: MySQL, SQL Server, MongoDB, Oracle Database, PostgreSQL, Informix, Sybase, etc.
9. What are the basic task of DBMS?
- Data Dictionary Management,
- Data Storage Management,
- Data Transformation and Presentation,
- Security Management,
- Multi user Access Control,
- Backup and Recovery Management,
- Data Integrity Management,
- Database Access Languages and Application Programming Interfaces and
- Database Communication interfaces
10. What are database system utilities? Give example.
A DBMS also provides a set of utilities for managing and controlling database activities. Examples of database utilities include reorganization, runstats, backup and copy, recover, integrity check, load data, unload data and repair database.
11. List the responsibilities of DBA.
- Creating and maintaining database standards and policies.
- Supporting database design, creation, and testing activities.
- Managing the database availability and performance, including incident and problem management.
- Administering database objects to achieve optimum utilization.
12. Mention , when it is not suitable to use DBMS.
- Small scale application
- Real time data processing
- Static data
- Budget constraints
- Single user application
13. Define attribute and entity and give example.
- entity : In a database management system (DBMS), an entity is a piece of data that is stored in the database. An entity can be a person, place, thing, or even an event.
Attribute : In a database management system (DBMS), an attribute is a piece of data that describes an entity. For example, in a customer database, the attributes might be name, address, and phone number.
14. Define primary key and foreign key.
- A primary key is used to ensure that data in the specific column is unique. A column cannot have NULL values.
- A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It is a column (or columns) that references a column (most often the primary key) of another table.
15. How primary key is useful ?
A primary key is used to ensure that data in the specific column is unique. A column cannot have NULL values.
16. What is weak entity? Give an example.
- The weak entity in DBMS do not have a primary key and are dependent on the parent entity. It mainly depends on other entities.
- Continuing our previous example,Professor is a strong entity, and the primary key is Professor_ID. However, another entity is Professor_Dependents, which is our Weak Entity.
17. What is an entity type? Give an example.
- Describes the type of the information that is being mastered. An entity type typically corresponds to one or several related tables in database.
- Examples of an entity are a single person, single product, or single organization.
18. What is relation and tuple? Give example.
- A relational database includes tables containing rows and columns.
- A tuple, also known as a record or row, is a basic unit of data in a relational database management system (DBMS).
19. Define the term entity give example.
A single unique object in the real world that is being mastered. Examples of an entity are a single person, single product, or single organization. Entity type. A person, organization, object type, or concept about which information is stored.
20. Give different DBMS languages.
- Data definition language (DDL) …
- Data manipulation language (DML) …
- Data control language (DCL) …
- Transaction control language (TCL)
21. Write syntax and example of CREATE TABLE command.
- CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) );
- CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table.
22. What is domain of relation? Give example.
- A domain is a unique set of values that can be assigned to an attribute in a database.
- For example, a domain of strings can accept only string values.
23. Explain Projection in Relational algebra.
Relational algebra refers to a procedural query language that takes relation instances as input and returns relation instances as output. It performs queries with the help of operators. A binary or unary operator can be used.
24. What is SQL? Write a syntax for DELETE command.
- SQL stands for Structured Query Language. It is used for storing and managing data in relational database management system (RDBMS). It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables.
- syntax : delete from table epm1 where id=1;
25. Explain COMMIT & ROLL BACK.
- The COMMIT statement lets a user save any changes or alterations on the current transaction. These changes then remain permanent.
- The ROLLBACK statement lets a user undo all the alterations and changes that occurred on the current transaction after the last COMMIT.
26. What is domain integrity constraints?
Domain integrity constraint contains a certain set of rules or conditions to restrict the kind of attributes or values a column can hold in the database table.
27. Write a syntax for UPDATE command.
Syntax : UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition];
28. What is PL/SQL? Write a basic structure of Pl/SQL.
- PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database.
Syntax :
Declare
<declaration section>
Begin
<executable section>
Exception
<exception handling>
End;
Commit v/s rollback.
29. What is relationship type? What are their types.
There are three types of relationships that can exist between two entities.
Types : One-to-One Relationship. One-to-Many or Many-to-One Relationship. Many-to-Many Relationship.
30.List various aggregate function in SQL.
- COUNT FUNCTION
- SUM Function
- AVG function
- numeric type.
- MAX Function
- MIN Function
31. What is query? Give example.
A query can either be a request for data results from your database or for action on the data, or for both. A query can give you an answer to a simple question, perform calculations, combine data from different tables, add, change, or delete data from a database.
32. Name the built-in functions of SQL.
String Functions (LEN, SUBSTRING, REPLACE, CONCAT, TRIM) Date and Time Functions (datetime, datetime2, smalldatetime) Aggregate Functions (COUNT, MAX, MIN, SUM, AVG).
33. Define domain. Give example.
- domain defines the permitted range of values for an attribute of an entity.
- For example, let’s consider a database for a university that stores information about students. One of the tables in the database might be called “Students” and could have an attribute called “Major” that stores the student’s declared major.
34. What are domain constraints ? Give example.
- In database management, a domain constraint is a type of data validation rule that defines the allowable values for a particular attribute or column in a database table.
- Attribute: Gender Domain Constraint: The only allowable values for the “Gender” attribute are “Male” or “Fem
35. List the datatypes in SQL.
- string, numeric, and date and time. Exact numerics, Unicode character strings.
- Approximate numerics, Binary strings.
- Date and time, Other data types.
- Character strings.
- Bigint, numeric.
- Bit, smallint.
- Decimal, smallmoney.
- Int, tinyint
36. Define Cursor.
Whenever DML statements are executed, a temporary work area is created in the system memory and it is called a cursor.
37. Define functional dependencies.
Functional dependency in DBMS refers to a relationship that is present between attributes of any table that are dependent on each other.
38. Define 1 NF , 2NF , 3NF and BCNF.
- A relation will be 1NFif it contains an atomic value. · It states that an attribute of a table cannot hold multiple values. It must hold only single-valued.
- A relation is said to be in the 2nd Normal Form in DBMS (or 2NF) when it is in the First Normal Form but has no non-prime attribute functionally dependent on any candidate key’s proper subset in a relation.
- A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
- BCNF(Boyce Codd Normal Form) in DBMS is an advanced version of 3NF (third normal form). A table or a relation is said to be in BCNF in DBMS if the table or the relation is already in 3NF
39. Define the term normalization.
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency
40. What are different cases of NULL values.
- The value unknown (value exists but is not known)
- Value not available (exists but is purposely withheld)
- Attribute not applicable (undefined for this tuple)
41. What is the advantages of normalization.
Reduces redundant data. Provides data consistency within the database. More flexible database design. Higher database security.
42. List the ACID properties of Transaction.
ACID refers to the four key properties of a transaction: atomicity, consistency, isolation, and durability.
43. Define transaction. Give example.
- The transaction is a set of logically related operation. It contains a group of tasks.
- A transaction is an action or series of actions. It is performed by a single user to perform operations for accessing the contents of the database
- Examples of Transactions are making a purchase, bill pay, money transfer, stock trade, address change, and others. With each type of Transaction, different type of details are involved.
44. List the types of locks.
- Shared Lock.
- Exclusive Lock.
45. What do you mean by redundancy? Why is must be controlled.
- controlled redundancy is necessary technique to use redundant fields in a database
- Redundancy is controlled when the DBMS ensures that multiple copies of the same data are consistent
46. Define database recovery?
Recovery is the rebuilding of a database or table space after a problem such as media or storage failure, power interruption, or application failure.
47. What is deadlock?
In a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks.
48. What do you mean by controlled redundancy?
- controlled redundancy is necessary technique to use redundant fields in a database
- Redundancy is controlled when the DBMS ensures that multiple copies of the same data are consistent.
49. Expand A.C.I.D.
ACID (atomicity, consistency, isolation, and durability)
50. Explain the reason for failure of transaction.
database failures such as network failure, system crash, natural disasters, carelessness, sabotage(corrupting the data intentionally), software errors, etc.
51. Define Database Architecture
Database architecture refers to the overall design and structure of a database management system (DBMS). It encompasses the components, their relationships, and the rules that govern how data is stored, retrieved, and managed.
51. Define Database Architecture
Database architecture refers to the overall design and structure of a database management system (DBMS). It encompasses the components, their relationships, and the rules that govern how data is stored, retrieved, and managed.
52. What are the types of database architectures?
1. Single-tier architecture:
In a single-tier architecture, all components (application, database, and user) reside on the same machine. This is the simplest architecture, but it is not scalable and can be inefficient for large databases or multiple users.
Helpfull