DBMS MCQ
What is a Database?
A database is an organized collection of data, general stores, and accessed electronically from a computer system.
What is DBMS?
The Database Management System(DBMS) is the software that interacts with end-users, applications, and the database itself to capture and analyze the data.
The DBMS software additionally encompasses the core facilities provided to administer the database. The sum total of the database, the DBMS, and the associated applications can be referred to as a “database system”. Check out the frequently asked interview questions on DBMS.
Problems with File System
- Data redundancy and inconsistency.
- Difficulty in accessing data
- Data isolation
- Integrity problem
- Atomicity problem
- Concurrent access anomalies
Instance and Schemas
- The collection of information stored in the database at a particular moment is called an instance of the database.
- The overall design of the database is called the database schema.
Types of Databases
- Commercial database
- Multimedia database
- Deductive database
- Temporal database
- Geological Info System
Characteristics of Database Management System
- Provides security and removes redundancy
- Self-describing nature of a database system
- Insulation between programs and data abstraction
- Support of multiple views of the data
- Sharing of data and multiuser transaction processing
- Database Management Software allows entities and relations among them to form tables.
- It follows the ACID concept ( Atomicity, Consistency, Isolation, and Durability).
What is Transaction?
- To remove this partial execution problem, we increase the level of atomicity and bundle all the instructions of a logical operation into a unit called a transaction.
- So, formally - ‘A transaction is a set of logically related instructions to perform a logical unit of work’.
What is a Schedule?
- When two or more transactions are executed together or one after another, then they can be bundled up into a higher unit of execution called schedule.
- A schedule of N transactions T1, T2, T3...TN, is an ordering of the operations of the transactions. Operations from different transactions can be interleaved in the scheduled S.
- However, the schedule for a set of transactions must contain all the instructions of those transactions, and for each transaction T, that participates in the schedule S, the operation of T, in S must appear in the same order in which they occur in T.
Basics of RDBMS
- The domain is a set of atomic values.
- By atomic we mean that each value in the domain is indivisible as far as the formal relational model is concerned.
- A common method of specifying a domain is to specify a data type from which the data forming the domain are drawn.
- Table(Relation): A relation is a set of tupes/rows/entities/records.
- Tuple: Each row of a relation/table is called a tuple.
- Degree: Number of columns/attributes of a relation.
- Cardinality: Number of rows/types/record of a relational instance.
Properties of RDBMS
- Cells contain atomic values.
- Values in a column are of the same kind.
- Each row is unique.
- Each column has a unique name
- No two tables can have the same name in a relational schema.
- The sequence of rows is insignificant.
- The sequence of columns is insignificant.
Useful Resources
- Database Testing
- Difference between RDBMS and DBMS
- Complete Technical Interview Guide
- Complete DBMS Tutorial by Scaler
DBMS MCQ Questions
Which of the following command is used to change data in table?
INSERT
UPDATE
MERGE
NONE
What is rows of a relation known as?
Degree
Entity
Tuple
None
What is the name of the query that is placed within a WHERE or HAVING clause of another query?
Multi query
Subquery
Super query
Master query
What is the number of tuples of a relation known as?
Column
Cardinality
Entity
None
What is the use of COUNT in SQL?
Returns number of distinct value
Returns total values
Returns number of groups
Returns number of columns
Which command is used to remove a stored function from the database?
DROP
DELETE
ERASE
REMOVE
Which data structure is used in Hierarchical model records?
Graph
Tree
Linked list
Stacks
Which is AS clause used for?
Rename
Selection
Join
Projection
Which is the lowest level of abstraction that describes how the data are actually stored?
Physical
Abstract
View
User
Which normal form deals with multivalued dependency?
1NF
2NF
3NF
4NF
Which normalization form is based on the transitive dependency?
1NF
2NF
3NF
BCNF
Which of the following allows to uniquely identify a tuple?
Schema
Attribute
Super key
Domain
Which of the following can replace the below query?
SELECT name, course_id
FROM instructor, teaches
WHERE instructor_ID= teaches_ID;
Select name, course_id from instructor natural join teaches;
Select name,course_id from teaches,instructor where instructor_id=course_id;
Select name, course_id from instructor;
Select course_id from instructor join teaches;
What is DBMS?
Collection of many programs to access data
Collection of interrelated data
Collection of commands
All
Which of the following is a command of DDL?
Alter
Delete
Create
All of the above
Which of the following is known as minimal super key?
Primary key
Candidate key
Foreign key
None
Which of the following is not a SQL command?
DELETE
ORDER BY
SELECT
WHERE
Which of the following is preserved in execution of transaction in isolation?
Atomicity
Isolation
Durability
Consistency
Which of the following is the full form of DDL?
Data definition language
Data derivation language
Dynamic data language
Detailed data language
Which of the following is the full form of NTFS?
New Tree File System
New Technology FIle System
New Table File System
Both B and C
Which of the following is the full form of TCL?
Ternary control language
Transaction control language
Transaction central language
Transmission control language
Which of the following is the property of transaction that protects data from system failure?
Atomicity
Isolation
Durability
Consistency
Which of the following normal forms contains information about a single entity?
1NF
2NF
3NF
4NF
Which of the following operator is used to compare a value to a list of literals values that have been specified?
ANY
BETWEEN
IN
ALL
Which of the following SQL command is used for removing (or deleting) a relation form the database?
Drop
Delete
Rollback
Remove
Select the correct command to modify a column in a table.
Update
Alter
Drop
Set
After which operation is the modify operation done?
Look-up
Insert
Delete
All
During transaction before commit which of the following statement is done automatically in case of shutdown?
Rollback
Commit
View
Flashback
For performing tasks like creating the structure of the relations, deleting relation, which of the following is used?
Data definition language
Data derivation language
Dynamic data language
Detailed data language
How is ER diagram represented?
Circle
Ellipse
Triangle
Square
How many levels are there in architecture of database?
2
3
4
5
Identify the concurrency based protocol?
Lock based protocol
Two phase locking protocol
Timestamp ordering protocol
All
Maximum children of a B-tree of order m?
m/2
m - 1
m
m + 1
NATURAL JOIN can also be termed as -
Combination of Union and cartesian product
Combination of Selection and cartesian product
Combination of Projection and cartesian product
None
Primary key can be?
NULL
NOT NULL
Both NULL and NOT NULL
Depends on situation
Rectangles in ER diagram represents?
Tables
Attributes
Tuples
Entity sets
Select the correct advantages of view.
Derived columns
Data security
Hiding of complex queries
All of the above
Select the correct command to find the number of values in a column.
ADD
SUM
TOTAL
COUNT
A relational database developer refers to a record as?
Attribute
Tuple
Relation
None
Select the correct database object which does not exist physically.
Base table
View
Index
None
Select the correct definition of relation.
Subset of a cartesian product of list of domains
Subset of a cartesian product of list of tuple
Subset of a cartesian product of list of attributes
Subset of a cartesian product of list of relations
Select the correct definition of Relational calculus?
It is a high level language
It is a procedural language
It is a non-procedural language
It is a data definition language
Select the correct foreign key constraint?
Referential integrity
Entity integrity
Domain integrity
None
Select the correct properties of entities?
Table
Groups
Attributes
Switchboards
Select the definition of the correct key which is used to represent relation between two tables?
Candidate key
Foreign key
Primary key
Super key
Select the relational algebra operations.
Union
Select
Rename
All of the above
Select the valid SQL type.
NUMERIC
CHARACTER
FLOAT
All of the above
To select some particular columns, which of the following command is used?
PROJECTION
SELECTION
JOIN
UNION
Total view of a database is known as?
Physical view
Internal view
Conceptual view
External view
What is a table joined with itself called?
Join
Self join
Outer join
Equi join
