Database Management System (DBMS)

01. What is DBMS?

A database-management system (DBMS) consists of a collection of interrelated data and a collection of programs to access that data. The data describe one particular enterprise.

02. What is main Purpose of DBMS?
A main purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored and maintained.

03. Describe DBMS Application.
Databases are widely used. Here are some representative applications:
*Banking: For customer information, accounts, and loans, and banking transactions.
*Airlines: For reservations and schedule information.
*Universities: For student information, course registrations, and grades.
*Credit card transactions: For purchases on credit cards and generation of monthly Statements.
*Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about the communication networks.
*Finance: For storing information about holdings, sales, and purchases of financial instruments such as stocks and bonds.
*Sales: For customer, product, and purchase information.
*Manufacturing: For management of supply chain and for tracking production of items in factories, inventories of items in warehouses/stores, and orders for items.
*Human resources: For information about employees, salaries, payroll taxes and benefits, and for generation of paychecks.

04. What is Problem of file processing system?
A file-processing system has a number of Major disadvantages:
*Data redundancy and inconsistency: Storing the information several time leads to waste of storage space is called data redundancy.
*Difficulty in accessing data.
*Data isolation: Because data are scattered in various files, and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult.
*Integrity problems: The data values stored in the database must satisfy certain types of consistency constraints.
*Atomicity problems
*Concurrent-access anomalies.
*Security problems: Not every user of the database system should be able to access all the data.

05. What is data abstraction and level?
Data abstraction is the simplest of principles to understand. Data abstraction and encapsulations are closely tied together, because a simple definition of data abstraction is the development of classes, objects, types in terms of their interfaces and functionality, instead of their implementation details.
The levels of abstraction are:
•Physical level. The physical level describes complex low-level data structures in detail.
•Logical level. The logical level thus describes the entire database in terms of a small number of relatively simple structures.
View level. The view level of abstraction exists to simplify their interaction with the system. The system may provide many views for the same database.

06. What is database instance and schema?
*Instance: The collection of information stored in the database at a particular moment is called an instance of the database. *Schema: The overall design of the database is called the database schema.
07. What is data Model?
A data model is a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints.

08. Draw an Entity relationships diagram.
 

09. Describe DDL and DML
A database schema by a set of definitions expressed by a special language called a data-definition language (DDL).
A data-manipulation language (DML): A data-manipulation language is a language that enables users to access or manipulate data as organized by the appropriate data model.

10. What is Entity and Entity Set?
An entity is a “thing” or “object” in the real world that is distinguishable from all other objects. For example, each person in an enterprise is an entity.
An entity set is a set of entities of the same type that share the same properties, or attributes. The set of all persons who are customers at a given bank, for example, can be defined as the entity set customer.

11. What is Relationship and Relationship set?
A relationship is an association among several entities.
A relationship set is a set of relationships of the same type.

12. What are the Mapping Cardinalities?
Mapping cardinalities, or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set.
For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following:
• One to one. An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A.
• One to many. An entity in A is associated with any number (zero or more) of entities in B. An entity in B, however, can be associated with at most one entity in A.
• Many to one. An entity in A is associated with at most one entity in B. An entity in B, however, can be associated with any number (zero or more) of entities in A.
• Many to many. An entity in A is associated with any number (zero or more) of entities in B, and an entity in B is associated with any number (zero or more) of entities in A.

13. What are Super, Candidate and Primary Key?
*A super key is a set of one or more attributes that, taken collectively, allow us to identify uniquely an entity in the entity set. For example, the customer-id attribute of the entity set customer is sufficient to distinguish one customer entity from another. Thus, customer-id is a super key.
*Minimal super keys are called candidate keys.
*The term primary key use to denote a candidate key that is chosen by the database designer as the principal means of identifying entities within an entity set.

14. What weak and strong entity set?
An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary key is termed a strong entity set.

15. What is trigger and function of trigger?
A trigger is a statement that the system executes automatically as a side effect of a modification to the database. To design a trigger mechanism, we must meet two requirements:
1. Specify when a trigger is to be executed. This is broken up into an event that causes the trigger to be checked and a condition that must be satisfied for trigger execution to proceed.
2. Specify the actions to be taken when the trigger executes.

16. What is Transaction and what is the property of it?
A transaction is a unit of program execution that accesses and possibly updates various data items.
Transactions are required to have the ACID properties:
*Atomicity: Either all operations of the transaction are reflected properly in the database, or none are.
*Consistency: Execution of a transaction in isolation preserves the consistency of the database.
*Isolation: Even though multiple transactions may execute concurrently & each transaction is unaware of other transactions executing concurrently in the system.
*Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.

18. What is the Concurrent Control?
When several transactions execute concurrently in the database, the consistency of data may no longer be preserved. It is necessary for the system to control the interaction among the concurrent transactions, and this control is achieved through one of a variety of mechanisms called concurrency-control schemes.

17. What is deadlock and ways of preventing deadlock?
A deadlock is a situation which occurs when a process enters a waiting state because a resource requested by it is being held by another waiting process, which in turn is waiting for another resource. If a process is unable to change its state indefinitely because the resources requested by it are being used by other waiting process, then the system is said to be in a deadlock.
There are two approaches to deadlock prevention:
One approach ensures that no cyclic waits can occur by ordering the requests for locks, or requiring all locks to be acquired together.
The other approach is closer to deadlock recovery, and performs transaction rollback instead of waiting for a lock, whenever the wait could potentially result in a deadlock.

21. What is integrity constraint?
Integrity constraints are used to ensure accuracy and consistency of data in a relational database.

Describe the Syntax of Insert, Update and Delete.
INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
  VALUES ( expression1_1, expression1_2, .... ),   ( expression2_1, expression2_2, .... ), ....
DELETE syntax
DELETE FROM table_name
  [ WHERE expression ]
  [ LIMIT limit_amount ]

UPDATE syntax
UPDATE table_name
  SET col_name1 = expression1, col_name2 = expression2, ....
  [ WHERE expression ]
  [ LIMIT limit_amount ]


19. Draw diagram of Implementation of Atomicity and Durability

20. State of transaction and draw a diagram.

A transaction must be in one of the following states:
• Active, the initial state; the transaction stays in this state while it is executing
• Partially committed, after the final statement has been executed
• Failed, after the discovery that normal execution can no longer proceed
• Aborted, after the transaction has been rolled back and the database has been restored to its state prior to the start of the transaction

• Committed, after successful completion