DBMS stands for Database Management System, is a set of applications or programs that enable users to create and maintain a database. In other words, DBMS provides us an interface or tool for performing different operations such as the creation of a database, inserting data into it, deleting data from it, updating the data, etc. DBMS is a software in which data is stored in a more secure way as compared to the file-based system. A DBMS system helps a user to overcome problems like data inconsistency, data redundancy, etc. in a database. 

Some popular Database Management System- MySQL, Oracle, SQL Server, Amazon simple DB (Cloud-based), etc.

A Database is a logical, consistent and organized collection of data that it can easily be accessed, managed and updated. Databases, also known as electronic databases are structured to provide the facility of creation, insertion, updating of the data efficiently and are stored in the form of a file or set of files, on the magnetic disk, tapes and another sort of secondary devices. Database mostly consists of the objects (tables), and tables include of the records and fields. 

>

RDBMS stands for Relational Database Management Systems. It is used to maintain the data records and indices in tables. RDBMS is the form of DBMS which uses the structure to identify and access data concerning the other piece of data in the database. RDBMS is the system that enables you to perform different operations such as- update, insert, delete, manipulate and administer a relational database with minimal difficulties. Most of the time RDBMS use SQL language because it is easily understandable and is used for often.

A software application that interacts with databases, applications, and users to capture and analyze the required data. The data stored in the database can be retrieved, deleted and modified based on the client’s requirement.

The different types of DBMS are as follows:

• Relational DBMS (RDBMS): This type of DBMS, uses a structure which allows the users to access data in relation to another piece of data in a database. In this type of DBMS, data is stored in the form of tables.
• Hierarchical DBMS:  As the name suggests, this type of DBMS has a structure similar to that of a tree, wherein the nodes represent records and the branches of the tree represent fields.
• Network DBMS: This type of DBMS supports many-to-many relations wherein multiple member records can be linked.
• Object-oriented DBMS: Uses small individual software called object to store pieces of data and the instructions for the actions to be done with the data.

The advantages of DBMS are as follows:

• Sharing of Data: Multiple users can use data from the same database simultaneously.
• Integrity constraints: These constraints allow the data to be stored in a database in a refined manner.
• Redundancy control: Supports a mechanism to control the redundancy of data by integrating all the data into a single database.
• Data Independence: Allows to change the structure of the data without affecting the structure of any of the running application programs.
• Provide backup and recovery facility: Provides a feature of ‘backup and recovery’ to automatically create the data backup and restore the data as and when required.

Database language implies the queries that are used for the update, modify and manipulate the data. The different languages present in DBMS are as follows:

• DDL(Data Definition Language) – Consists of commands which are used to define the database. CREATE, ALTER, DROP, TRUNCATE, RENAME, etc. All these commands are used for updating the data that?s why they are known as Data Definition Language.
• DML(Data Manipulation Language) –  Consists of commands which are used to manipulate the data present in the database. SELECT, UPDATE, INSERT, DELETE, etc. These commands are used for the manipulation of already updated data that's why they are the part of Data Manipulation Language. 
• DCL(Data Control Language) – Consists of commands which deal with the user permissions and controls of the database system. GRANT and REVOKE. These commands are used for giving and removing the user access on the database. So, they are the part of Data Control Language.
• TCL(Transaction Control Language) – Consist of commands which deal with the transaction of the database.  COMMIT, ROLLBACK, and SAVEPOINT. These are the commands used for managing transactions in the database. TCL is used for managing the changes made by DML

Super Key :  A super key is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. No two rows can have the same value of super key attributes. 

Candidate Key :  A Candidate key is a minimal superkey, i.e., no proper subset of Candidate key attributes can be a superkey. 

Primary Key : A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important and becomes the primary key. There cannot be more than one primary key in a table..

Foreign key : A Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. 

The primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains. 

ACID stands for Atomicity, Consistency, Isolation, and Durability in a DBMS these are those properties that ensure a safe and secure way of sharing data among multiple users.

• Atomicity: This property reflects the concept of either executing the whole query or executing nothing at all, which implies that if an update occurs in a database then that update should either be reflected in the whole database or should not be reflected at all.
• Consistency: This property ensures that the data remains consistent before and after a transaction in a database.
• Isolation: This property ensures that each transaction is occurring independently of the others. This implies that the state of an ongoing transaction doesn’t affect the state of another ongoing transaction.
• Durability: This property ensures that the data is not lost in cases of a system failure or restart and is present in the same state as it was before the system failure or restart.

The process of hiding irrelevant details from users is known as data abstraction. Data abstraction can be divided into 3 levels:

• Physical Level:  it is the lowest level and is managed by DBMS. This level consists of data storage descriptions and the details of this level are typically hidden from system admins, developers, and users.
• Conceptual or Logical level:  it is the level on which developers and system admins work and it determines what data is stored in the database and what is the relationship between the data points.
• External or View level: it is the level that describes only part of the database and hides the details of the table schema and its physical storage from the users. The result of a query is an example of View level data abstraction.  A view is a virtual table created by selecting fields from one or more tables present in the database.

Following are different types of relationship amongst tables in a DBMS system:

• One to One Relationship:  This type of relationship is applied when a particular row in table X is linked to a singular row in table Y. In Other way, One-to-One Relationship –  Used when a single row in Table A is related to a single row in Table B.
• One to Many Relationship: This type of relationship is applied when a single row in table X is related to many rows in table Y. In Other way, Used when a single row in Table A is related to many rows in table B.
• Many to Many Relationship: This type of relationship is applied when multiple rows in table X can be linked to multiple rows in table Y. In Other way, Used when many rows in table A can be related to many rows in table B.
• Self Referencing Relationship: This type of relationship is applied when a particular row in table X is associated with the same table. In Other way, Used when a record in table A is related to the same table itself.

It is a process of analyzing the given relation schemas based on their functional dependencies and primary keys to achieve the following desirable properties: 

• Minimizing Redundancy 
• Minimizing the Insertion, Deletion, And Update Anomalies Relation schemas that do not meet the properties are decomposed into smaller relation schemas that could meet desirable properties. 

The process of organizing data to avoid any duplication of data and redundancy is known as Normalization. There are many successive levels of normalization which are known as normal forms. Each consecutive normal form depends on the previous one. The following are the first three normal forms. Apart from these, you have higher normal forms such as BCNF. 

• First Normal Form (1NF) – No repeating groups within rows
• Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
• Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.

Some of the major advantages of DBMS are as follows:

• Controlled Redundancy: DBMS supports a mechanism to control the redundancy of data inside the database by integrating all the data into a single database and as data is stored in only one place, the duplicity of data does not happen.
• Data Sharing: Sharing of data among multiple users simultaneously can also be done in DBMS as the same database will be shared among all the users and by different application programs.
• Backup and Recovery Facility: DBMS minimizes the pain of creating the backup of data again and again by providing a feature of ‘backup and recovery’ which automatically creates the data backup and restores the data whenever required.
• Enforcement of Integrity Constraints: Integrity Constraints are very important to be enforced on the data so that the refined data after putting some constraints are stored in the database and this is followed by DBMS.
• Independence of Data: It simply means that you can change the structure of the data without affecting the structure of any of the application programs.

DELETE command: this command is needed to delete rows from a table based on the condition provided by the WHERE clause.

• It deletes only the rows which are specified by the WHERE clause.
• It can be rolled back if required.
• It maintains a log to lock the row of the table before deleting it and hence it’s slow.

TRUNCATE command: this command is needed to remove complete data from a table in a database. It is like a DELETE command which has no WHERE clause.

• It removes complete data from a table in a database.
• It can't be rolled back even if required. ( truncate can be rolled back in some databases depending on their version but it can be tricky and can lead to data loss). Check this link for more details
• It doesn’t maintain a log and deletes the whole table at once and hence it’s fast.

The term query optimization specifies an efficient execution plan for evaluating a query that has the least estimated cost. The concept of query optimization came into the frame when there were a number of methods, and algorithms existed for the same task then the question arose that which one is more efficient and the process of determining the efficient way is known as query optimization.

There are many benefits of query optimization:

• It reduces the time and space complexity.
• More queries can be performed as due to optimization every query comparatively takes less time.
• User satisfaction as it will provide output fast

Denormalization is the process of boosting up database performance and adding of redundant data which helps to get rid of complex data. Denormalization is a part of database optimization technique. This process is used to avoid the use of complex and costly joins. Denormalization doesn't refer to the thought of not to normalize instead of that denormalization takes place after normalization. In this process, firstly the redundancy of the data will be removed using normalization process than through denormalization process we will add redundant data as per the requirement so that we can easily avoid the costly joins.

E-R model is a short name for the Entity-Relationship model. This model is based on the real world. It contains necessary objects (known as entities) and the relationship among these objects. Here the primary objects are the entity, attribute of that entity, relationship set, an attribute of that relationship set can be mapped in the form of E-R diagram.

In E-R diagram, entities are represented by rectangles, relationships are represented by diamonds, attributes are the characteristics of entities and represented by ellipses, and data flow is represented through a straight line.

There are mainly 7 types of Keys, that can be considered in a database. I am going to consider the below tables to explain to you the various keys.

Candidate Key

This is a set of attributes which can uniquely identify a table. Each table can have more than a candidate key. Apart from this, out of all the candidate keys, one key can be chosen as the Primary key.  In the above example, since CustomerID and PanNumber can uniquely identify every tuple, they would be considered as a Candidate Key. 

Super Key

This is a set of attributes which can uniquely identify a tuple. So, a candidate key, primary key, and a unique key is a superkey, but vice-versa isn’t true.

Primary Key 

This is a set of attributes which are used to uniquely identify every tuple. In the above example, since CustomerID and PanNumber are candidate keys, any one of them can be chosen as a Primary Key. Here CustomerID is chosen as the primary key.

Unique Key 

The unique key is similar to the primary key, but allows NULL values in the column. Here the PanNumber can be considered as a unique key.

Alternate Key

Alternate Keys are the candidate keys, which are not chosen as a Primary key. From the above example, the alternate key is PanNumber 

Foreign Key 

An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers. in the above example, the CustomerID from the Customers Table is referred to the CustomerID from the Customer_Payment Table.

Composite Key 

A composite key is a combination of two or more columns that identify each tuple uniquely. Here, the CustomerID and Date_of_Payment can be grouped together to uniquely identify every tuple in the table.

A stored procedure is a group of SQL statements that have been created and stored in the database. The stored procedure increases the reusability as here the code or the procedure is stored into the system and used again and again that makes the work easy, takes less time in processing and decreases the complexity of the system. So, if you have a code which you need to use again and again then save that code and call that code whenever it is required.

When you say an application has data independence, it implies that the application is independent of the storage structure and data access strategies of data.

1NF is the First Normal Form. It is the simplest type of normalization that you can implement in a database. The primary objectives of 1NF are to:

• Every column must have atomic (single value)
• To Remove duplicate columns from the same table
• Create separate tables for each group of related data and identify each row with a unique column

2NF is the Second Normal Form. A table is said to be 2NF if it follows the following conditions:

• The table is in 1NF, i.e., firstly it is necessary that the table should follow the rules of 1NF.
• Every non-prime attribute is fully functionally dependent on the primary key, i.e., every non-key attribute should be dependent on the primary key in such a way that if any key element is deleted, then even the non_key element will still be saved in the database.

3NF stands for Third Normal Form. A database is called in 3NF if it satisfies the following conditions:

• It is in second normal form.
• There is no transitive functional dependency.
• For example: X->Z

Where:
X->Y
Y does not -> X
Y->Z so, X->Z

BCMF stands for Boyce-Codd Normal Form. It is an advanced version of 3NF, so it is also referred to as 3.5NF. BCNF is stricter than 3NF.

A table complies with BCNF if it satisfies the following conditions:

• It is in 3NF.
• For every functional dependency X->Y, X should be the super key of the table. It merely means that X cannot be a non-prime attribute if Y is a prime attribute.

Indexes are data structures responsible for improving the speed of data retrieval operations on a table. This data structure uses more storage space to maintain extra copies of data by using additional writes. So, indexes are mainly used for searching algorithms, where you wish to retrieve data in a quick manner.

The differences between clustered and non-clustered index are :

1. A clustered index is faster and non clustered index is relatively slower.
2. A clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index and non clustered index does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching.
3. A clustered index - one table can only have one clustered index and in non clustered index one table can only have many non clustered indexes.

Triggers

• A special kind of stored procedure that is not called directly by a user. In fact, a trigger is created and is programmed to fire when a specific event occurs.
• A trigger cannot be called or execute directly by a user.  Only when the corresponding events are fired, triggers are created.
• You cannot schedule a trigger.
• Cannot directly call another trigger within a trigger.
• Parameters cannot be passed as input
• Cannot return values.
• Transactions are not allowed within a trigger.

Stored Procedures

• A group of SQL statements which can be reused again and again. These statements are created and stored in the database.
• Can execute stored procedures by using the exec command, whenever we want.
• You can schedule a job to execute the stored procedure on a pre-defined time.
• Call a stored procedure from another stored procedure.
• Parameters can be passed as input
• Can return zero or n values.
• You can use transactions within a stored procedure.

A Join is one of the SQL statements which is used to join the data or the rows from 2 or more tables on the basis of a common field/column among them.

There are 4 types of SQL Joins:

• Inner Join: This type of join is used to fetch the data among the tables which are common in both tables.
• Left Join: This returns all the rows from the table which is on the left side of the join but only the matching rows from the table which is on the right side of the join.
• Right Join: ​​​​​​​This returns all the rows from the table which is on the right side of the join but only the matching rows from the table which is on the left side of the join.
• Full Join: This returns the rows from all the tables on which the join condition has been put and the rows which do not match hold null values.

UNION and UNION ALL are used to join the data from 2 or more tables but UNION removes duplicate rows and picks the rows which are distinct after combining the data from the tables whereas UNION ALL does not remove the duplicate rows, it just picks all the data from the tables.

DROP command is a DDL command which is used to drop/delete the existing table, database, index, or view from the database.

The major difference between DROP, TRUNCATE and DELETE commands are:

DROP and TRUNCATE commands are the DDL commands which are used to delete tables from the database and once the table gets deleted, all the privileges and indexes that are related to the table also get deleted. These 2 operations cannot be rolled back and so should be used only when necessary.

DELETE command, on the other hand, is a DML Command which is used to delete rows from the table and this can be rolled back.

In Network model - Each parent node can have multiple children nodes and vice versa. In Hierarchical Database Model A top-down structure where each parent node can have many child nodes. But, a child node can have only a single parent node. 

In Network model - Supports one-to-one, one-to-many, and many-to-many relationships. In Hierarchical Database Model Supports one-tone and one-to-many relationships.

CLAUSE in SQL is used to limit the result set by mentioning a condition to the query. So, you can use a CLAUSE to filter rows from the entire set of records.

Example: WHERE HAVING clause.

Note: Whenever GROUP BY is not used, HAVING behaves like a WHERE clause.