9fKzj2sC

1Components of DBMS
2DBMS have several components, each performing very significant tasks in the database management system environment.
3The database management system can be divided into five major components, they are:
4
5Hardware
6Software
7Data
8Procedures
9Database Access Language
10
11Hardware
12Hardware means computer, hard disks, I/O channels for data, and any other physical component involved before any data is successfully stored into the memory.
13
14Software
15This is the main component, as this is the program which controls everything. The DBMS software is more like a wrapper around the physical database, which provides us with an easy-to-use interface to store, access and update data.
16
17Data
18Data is that resource, for which DBMS was designed. The motive behind the creation of DBMS was to store and utilise data.
19
20Procedures
21Procedures refer to general instructions to use a database management system. This includes procedures to setup and install a DBMS, To login and logout of DBMS software, to manage databases, to take backups, generating reports etc.
22
23Database Access Language
24Database Access Language is a simple language designed to write commands to access, insert, update and delete data stored in any database.
25------------------------------------------------------------------------------------
26Transaction property
27The transaction has the four properties. These are used to maintain consistency in a database, before and after the transaction.
28
29Atomicity
30It states that all operations of the transaction take place at once if not, the transaction is aborted.
31
32Abort: If a transaction aborts then all the changes made are not visible.
33Commit: If a transaction commits then all the changes made are visible.
34
35Consistency
36The integrity constraints are maintained so that the database is consistent before and after the transaction.
37The transaction is used to transform the database from one consistent state to another consistent state.
38
39Isolation
40It shows that the data which is used at the time of execution of a transaction cannot be used by the second transaction until the first one is completed.
41
42Durability
43The database should be durable enough to hold all its latest updates even if the system fails or restarts.
44------------------------------------------------------------------------------------
45States of Transaction
46In a database, the transaction can be in one of the following states 
47-
48Active state
49The active state is the first state of every transaction. In this state, the transaction is being executed.
50For example: Insertion or deletion or updating a record is done here. But all the records are still not saved to the database.
51
52Partially committed
53In the partially committed state, a transaction executes its final operation, but the data is still not saved to the database.
54
55Committed
56A transaction is said to be in a committed state if it executes all its operations successfully. In this state, all the effects are now permanently saved on the database system.
57
58Failed state
59If any of the checks made by the database recovery system fails, then the transaction is said to be in the failed state.
60
61Aborted
62If any of the checks fail and the transaction has reached a failed state then the database recovery system will make sure that the database is in its previous consistent state. 
63two operations:
64Re-start the transaction
65Kill the transaction
66------------------------------------------------------------------------------------
67DDL
68DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.
69
70CREATE - to create a database and its objects like (table, index, views, store procedure, function, and triggers)
71ALTER - alters the structure of the existing database
72DROP - delete objects from the database
73TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
74COMMENT - add comments to the data dictionary
75RENAME - rename an object
76
77DML
78DML is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.
79
80SELECT - retrieve data from a database
81INSERT - insert data into a table
82UPDATE - updates existing data within a table
83DELETE - Delete all records from a database table
84
85DCL
86DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.
87
88GRANT - allow users access privileges to the database
89REVOKE - withdraw users access privileges given by using the GRANT command
90
91TCL
92TCL is short name of Transaction Control Language which deals with a transaction within a database.
93
94COMMIT - commits a Transaction
95ROLLBACK - rollback a transaction in case of any error occurs
96SAVEPOINT - to rollback the transaction making points within groups
97SET TRANSACTION - specify characteristics of the transaction
98------------------------------------------------------------------------------------
99Log-Based Recovery
100The log is a sequence of records. Log of each transaction is maintained in some stable storage so that if any failure occurs, then it can be recovered from there.
101If any operation is performed on the database, then it will be recorded in the log.
102But the process of storing the logs should be done before the actual transaction is applied in the database.
103------------------------------------------------------------------------------------
104ER model
105ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified system.
106It develops a conceptual design for the database. It also develops a very simple and easy to design view of data.
107In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram.
1081. Entity:
109An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles.
110Consider an organization as an example- manager, product, employee, department etc. can be taken as an entity.
111a. Weak Entity
112
113An entity that depends on another entity called a weak entity. The weak entity doesn't contain any key attribute of its own. The weak entity is represented by a double rectangle.
114
1152. Attribute
116The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute.
117For example, id, age, contact number, name, etc. can be attributes of a student.
118
1193. Relationship
120A relationship is used to describe the relation between entities.
121a. One-to-One Relationship
122
123When only one instance of an entity is associated with the relationship, then it is known as one to one relationship.
124For example, A female can marry to one male, and a male can marry to one female.
125
126b. One-to-many relationship
127
128When only one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then this is known as a one-to-many relationship.
129For example, Scientist can invent many inventions, but the invention is done by the only specific scientist.
130
131c. Many-to-one relationship
132
133When more than one instance of the entity on the left, and only one instance of an entity on the right associates with the relationship then it is known as a many-to-one relationship.
134For example, Student enrolls for only one course, but a course can have many students.
135
136d. Many-to-many relationship
137
138When more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship.
139For example, Employee can assign by many projects and project can have many employees.
140-----------------------------------------------------------------------------------
141Checkpoint
142The checkpoint is a type of mechanism where all the previous logs are removed from the system and permanently stored in the storage disk.
143The checkpoint is like a bookmark. While the execution of the transaction, such checkpoints are marked, and the transaction is executed then using the steps of the transaction, the log files will be created.
144When it reaches to the checkpoint, then the transaction will be updated into the database, and till that point, the entire log file will be removed from the file. Then the log file is updated with the new step of transaction till next checkpoint and so on.
145The checkpoint is used to declare a point before which the DBMS was in the consistent state, and all transactions were committed.
146-----------------------------------------------------------------------------------
147<Basic Commands>
148UPDATE command is used to update any record of data in a table.
149UPDATE table_name SET column_name = new_value WHERE some_condition;
150
151DELETE command is used to delete data from a table.
152DELETE FROM table_name;
153
154In our student table if we want to delete a single record, we can use the WHERE clause to provide a condition in our DELETE statement.
155DELETE FROM student WHERE s_id=103;
156
157Insert command is used to insert data into a table.
158INSERT INTO table_name VALUES(data1, data2, ...)
159
160To create a database in RDBMS, create command is used
161CREATE DATABASE <DB_NAME>;
162
163CREATE TABLE Student(student_id INT, name VARCHAR(100),age INT);
164------------------------------------------------------------------------------------
165DBMS Database Models
166A Database model defines the logical design and structure of a database and defines how data will be stored, accessed and updated in a database management system.
167
168Hierarchical Model
169This database model organises data into a tree-like-structure, with a single root, to which all the other data is linked. The heirarchy starts from the Root data, and expands like a tree, adding child nodes to the parent nodes. 
170
171Network Model
172This is an extension of the Hierarchical model. In this model data is organised more like a graph, and are allowed to have more than one parent node.
173
174Entity-relationship Model
175In this database model, relationships are created by dividing object of interest into entity and its characteristics into attributes.
176
177Relational Model
178In this model, data is organised in two-dimensional tables and the relationship is maintained by storing a common field.
179------------------------------------------------------------------------------------
180<What is DBMS>
181Database Management system i.e. DBMS is a collection of programs for managing data and simultaneously it support different types of users to create, manage, retrieve, update and store information.
182------------------------------------------------------------------------------------
183ACID Properties
184A transaction is a very small unit of a program and it may contain several lowlevel tasks.
185Atomicity − This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none.
186
187Consistency − The database must remain in a consistent state after any transaction.
188
189Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts.
190
191Isolation - This property ensures that multiple transactions can occur concurrently without leading to inconsistency of database state.
192------------------------------------------------------------------------------------
193Advantages and Disadvantages of DBMS
194"A database management system (DBMS) is a collection of programs that manages the database structure and controls access to the data stored in the database". 
195
196Advantages of Database Management System (DBMS)
197
198 1. Improved data sharing
199An advantage of the database management approach is, the DBMS helps to create an environment in which end users have better access to more and better-managed data.
200
2012. Improved data security
202DBMS provides a framework for better enforcement of data privacy and security policies.
203
2044. Minimized data inconsistency
205Data inconsistency exists when different versions of the same data appear in different places.
206
2075. Improved data access
208The DBMS makes it possible to produce quick answers to ad hoc queries.
209
2106. Improved decision making
211 Better-managed data and improved data access make it possible to generate better-quality information, on which better decisions are based.
212
213 Disadvantages of Database Management System (DBMS):
214
215 1. Increased costs
216 one of the disadvantages of dbms is Database systems require sophisticated hardware and software and highly skilled personnel.
217
2182. Management complexity
219Database systems interface with many different technologies and have a significant impact on a company’s resources and culture.
220
221 3. Maintaining currency
222To maximize the efficiency of the database system, you must keep your system current. Therefore, you must perform frequent updates and apply the latest patches and security measures to all components
223
2244. Frequent upgrade/replacement cycles
225DBMS vendors frequently upgrade their products by adding new functionality.
226------------------------------------------------------------------------------------
227Key plays an important role in relational database; it is used for identifying unique rows from table. It also establishes relationship among tables.
228Types of keys in DBMS
229
230Primary Key – A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table.
231
232Super Key – A super key is a set of one of more columns (attributes) to uniquely identify rows in a table.
233
234Candidate Key – A super key with no redundant attribute is known as candidate key
235
236Alternate Key – Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternate or secondary keys.
237
238Composite Key – A key that consists of more than one attribute to uniquely identify rows (also known as records & tuples) in a table is called composite key.
239
240Foreign Key – Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.
241------------------------------------------------------------------------------------
242Failure Classification
243To find that where the problem has occurred, we generalize a failure into the following categories:
244
2451. Transaction failure
246The transaction failure occurs when it fails to execute or when it reaches a point from where it can't go any further. If a few transaction or process is hurt, then this is called as transaction failure.
247
2482. System Crash
249System failure can occur due to power failure or other hardware or software failure. Example: Operating system error.
250
2513. Disk Failure
252It occurs where hard-disk drives or storage drives used to fail frequently.
253It was a common problem in the early days of technology evolution.
254------------------------------------------------------------------------------------
255Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.