DBMS Interview Questions and Answers

DBMS Interview Questions and Answers

Data: It is raw, just a set of facts that by itself doesn't convey anything.

Eg: Student scores 90 marks.

Information: We need to understand the pattern between factual data and give it meaning.

Eg: Student got an A grade.

Knowledge: Synthesis of data and information lead us to answer the HOW question and take business decision.

Eg: Student is a Top Performer.

Database: It is a shared collection of logically related data, design to meet the needs of an organization.

DBMS: Collections of programs to access data. It is a software system that enables the users to define, create, maintain and control access to the database.

Functions of DBMS:

• Data management: Store, retrieve and update
• Transaction support: Modify in the database in successful or not
• Recovery
• Security
• Integrity: Maintain accuracy
• Concurrency control: Simultaneous access provided to users
• Utilities: Import, export, manage, backup, etc

Keys: It is used to identify any record or row of data from the table. It is also used to establish and identify relationships between tables.

Candidate Key: It is a minimal set of attributes (columns) that can be used to uniquely identify a single tuple (row) in a relation.

Primary Key: It is a candidate key that is selected to uniquely identify a tuple in a relation.

Foreign Key: It is a set of one or more columns in the child table whose values are required to match with the corresponding columns in the parent table.

Data Integrity

• Entity Integrity (row): Primary Key
• Domain Integrity (column): Datatypes, check constraint
• Referential Integrity: Foreign Key

ER Model: It is a graphical representation of entities and their relationship which help in understanding data independent of the actual database implementation.

Entity: Real-world object which has an independent existence.

Attribute: Property that describes an entity.

Cardinality of Relationship

• 1-1: 1 Employee allocated one computer and not shared.
• 1-M: 1 Employee allocated many computers and not shared.
• M-M: Many Employees allocated many computer and shared.

Normalization

It is a technique to remove or reduce redundancy (duplicate) from a table.

First Normal Form (1NF)

Table should not contain any multivalued attribute.

Second Normal Form (2NF)

• Table must be in 1NF.
• All non-prime attributes should be fully functionally dependent on the candidate key.
• OR
• There should be no partial dependency.

Third Normal Form (3NF)

• Table must be in 2NF.
• There should be no transitive dependency in the table.

Need for 2NF: Insert, update, delete anomalies

Need for 3NF: Cause problem in duplicate rows. 

Eg: If 2 students score the same marks then their grades will also be the same, here the rows will be duplicates.

Transaction: It is a set of operations used to perform a logical unit of work. A transaction generally represented a change in the database.

ACID Properties

• Atomicity: Either all the statements will run or none.
• Consistency: Before and after transactions, sum of money should be the same.
• Isolation: Convert parallel transaction into the serial transaction, because the serial schedule is consistent.
• Durability: Changes made in the database must be permanent.

DDL: Data Definition Language

It changes the structure of the table like creating, deleting, altering, etc.

• create
• alter 
• drop
• truncate

DML: Data Manipulation Language

It is used to modify the database tables.

• insert
• update
• delete

DCL: Data Control Language

It is used to grant and revoke authority for any database user.

• grant
• revoke

TCL: Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

• commit
• rollback
• savepoint

Delete vs Drop vs Truncate

Delete is used to remove a complete table or a particular row (using where clause).

Drop is used to remove the complete table or database.

Truncate is used to delete tables and set constraints to default.

Stored Procedure: It is a SQL statement that you can save and can be reused again.

Eg: Create Procedure Procedure_Name AS SQL_Statement GO;

DBMS Abstraction

• View: User Interface with which users interact.
• Logical: Fields, attributes, relations.
• Physical: Block of storage where data actually stored.

CompareKarein