What is transaction?
A transaction is a set of operations that are performed on database as a single unit of work. A transaction can be rolled back upto a certain point is called SAVEPOINT. A transaction must have ACID properties
What are ACID properties?
ACID means Atomic, Consistent, Isolated, Durable.
Atomic : It means a transaction is either failed or completed with all its modification.
Consistent : At the end of transaction, all data must be in consistent state.
Isolated : Transaction must be independent from one another.
Durable : When transaction is done, all modifications performed by it must be permanent in the system.
COMMIT ROLLBACK statement?
COMMIT command is used to save changed. ROLLBACK command is used to revert changes up to a save point.
What is Concurrency?
Concurrency is the ability of a database to allow multiple users to process multiple transactions.
What are the common concurrency problems?
Concurrency control is important as multiple users shared same database and making changes to it so poor control may lead to many problems.
Common Problems are
Lost Updates : This problem occur when two or more transaction updating at the same time and one transaction lost or overwritten.
Uncommitted Data : When in two transaction, one transaction is rolled back and second transaction already accessed the uncommitted data.
Inconsistent Retrievals : When first transaction access the data before data is being updated by the other transaction.
What is Serializability ?
A transaction must be serializable. It means the result of running transaction is same as the result of running them serializable.
What is locking mechanism?
The locks provide a method for securing the data that is being used so no anomalies can occur like lost data or additional data.
Locks are different types
Read Locks : Data is available for read only. No other transaction can modify its data.
Write Locks : No other transaction can write it while the transaction who is accessing it writing on it.
Exclusive Write Locks : It is more like write locks. The only thing different is that only original transaction can modify data.
What is deadlock?
Deadlock is a state in which each item of group is waiting for other including itself. In deadlock two transactions are stuck in such a way that none can complete.
What is Data Modeling?
Data modeling is the process of creating data model for the data to be stored in the database.
Types of Data Modeling Technique
1. ER Model
2. UML
Data Models are of Three Types
- Conceptual Model: This model describe what data contains. In this there is basically a collection and organizing of data.
- Logical Model : This model contains how system will implement data..
- Physical Model : This model is created by developers and DBA. It actually defines the actual structure of database.
What is normalization?
Normalization is a technique in database designing that organizes tables in such a way that reduce redundancy and dependency of data.
What are the rules for 1NF?
- Each table cell should contain one single value.
- Each record (row) must be unique.
What are the rules for 2NF?
- Table must be in 1NF.
- There must be one column primary key. i.e. each record have unique key.
What are the rules for 3NF?
- Table must be in 2NF.
- There must not be any transitive functional dependency.
What are storage engines?
Storage engines are MYSQL components, that can handle the SQL operations for different table types to store and manage information in a database. Some Storage engines are InnoDB, MyISAM, MEMORY etc