• Restriction for unauthorized access
• Redundancy control
• it provides backup and recovery
• It delivers multiple user interfaces
• It ensures data consistency
• It enforces integrity constraints
• Easy data extraction and data processing due to the use of queries
• Easy accessibility

• Data Definition Language (DDL) e.g., CREATE, DROP, ALTER, RENAME, TRUNCATE, etc. All these controlling commands are used to update the data so they are called Data Definition Language.
• Data Manipulation Language (DML) e.g., SELECT, INSERT, UPDATE, DELETE, etc. These commands are used to manipulate already updated data so it is called Data Manipulation Language.
• DATA Control Language (DCL) e.g., REVOKE and GRANT. These commands help in providing and withdrawing the user access to the database.
• Transaction Control Language (TCL) e.g., COMMIT, SAVEPOINT, and ROLLBACK. These commands are used to manage transactions in the database. They also manage the modifications made by DML.

• Hierarchical data model
• network model
• relational model
• Entity-Relationship model and so on.

A Relation Schema is described as a set of attributes which is also known as table schema. It describes the name of the table. A relation schema is called the blueprint through which we can describe, how the data is organized into tables. A relation is described as a set of tuples and a set of related attributes with identifying key attributes

See this example:

Assume, r be the relation that includes set tuples (t1, t2, t3, …, tn). Each tuple is in ordered list of n-values t=(v1,v2, …., vn).

• One-To-One: In this relationship, one record of any object can be associated with one record of another object.
• One-To-Many (many-to-one): In this relationship, one record of any object can be associated with many records of other objects and vice versa.
• Many-to-many: In this relationship, two or more records of an object can be associated with n number of records of another object.

• Inconsistent
• Data redundancy
• Not secure
• Data isolation
• Difficult in accessing data
• Concurrent access is not possible
• Data integrity
• Atomicity problem
• Limited data sharing

Data abstraction in DBMS is a method of suppressing irrelevant details from users. As database systems are constructed of complicated data structures so, it makes convenient user interaction with the database.

For example, We know that most of the users choose those systems which have an easy GUI that means no complicated processing. So, to keep the user connected and for creating access to the data easily, it is essential to do data abstraction. In addition to it, data abstraction separates the system into various layers to make the work determined and well defined.

• Physical level: It is the lowliest level of abstraction. It defines how data are reserved.
• Logical level: It is the next level of abstraction. It defines what data are reserved in the database and what the connection between those data is.
• View level: It is the highest level of data abstraction. It defines only part of the whole database.

Data Manipulation Language (DML) allows the user to manipulate or access data as systematized by the suitable data model. For example- SELECT, INSERT, UPDATE, DELETE.

Types of DML are:

Procedural DML or Low-level DML: It needs a user to tell what data is required and how to get that data.

Non-Procedural DML or High-level DML: It needs a user to tell what data are required without telling how to get those data.

It is a Procedural Query Language that includes a bunch of operations that accept one or two relations as information and deliver a new relationship. Relational algebra is the primary group of processes for the relational model. The strong point of relational algebra is that it is the same as algebra which works on numbers.

Some of the fundamental operations of relational algebra:

• project
• select
• union
• set difference
• rename, etc.

• Tuple relational calculus
• Domain relational calculus

It identifies an efficient implementation plan for assessing a query that has the lowest estimated cost. The idea of query optimization came into the existence when there were several methods, and algorithms existed for a similar task then the query occurred that which one is more efficient, and the method of defining the efficient way is called query optimization.

Benefits of query optimization:

• It decreases the time and space complexness.
• More queries can be served as due to optimization every query comparatively bears less time.
• User fulfillment as it will provide fast output.

It is a process of analyzing the provided relation schemas as per their functional dependencies. It is operated to lower the redundancy and is also used to reduce insertion, deletion, and update distractions. Normalization is viewed as an important process as it is used to bypass data redundancy, updation anomaly, insertion anomaly, deletion anomaly.

Some normal forms are:

• First Normal Form(1NF)
• Second Normal Form(2NF)
• Third Normal Form(3NF)
• Boyce & Codd Normal Form(BCNF)

E-R model is a short form of Entity-Relationship model. It includes essential objects (also called entities) and the connection among these objects. Here the main objects are the entity, relationship set, attribute of that entity, an attribute of that connection set can be mapped in the formation of an E-R diagram.

In the E-R diagram, entities are described by rectangles, relationships are displayed by diamonds, attributes are the features of entities and expressed by ellipses, and data flow is defined via a straight line.

Data integrity is implemented in the database system by levying a series of rules. Those set of integrity is called the integrity rules.

The two integrity rules in DBMS are:

Entity Integrity: It tells that the “Primary key can’t have a NULL value.”

Referential Integrity: It tells that “Foreign Key can be either a NULL value or the Primary Key value of other relation.

Extension: It is the number of tuples available in a table at any instance. It transforms as the tuples are updated, created, and destroyed. The existing data in the database transforms quite often. So, the data present in the database at a certain moment in time is called an extension or database state, or snapshot. It depends on time.

Intension: It is also called Data Schema and is represented as the depiction of the database, which is determined during database design and is predicted to remain unaffected. The Intension is a stable value that provides the name, structure of tables, and the constraints laid on it.

System R was invented and developed from 1974 to 1979 at IBM San Jose Research Centre. It is the first enforcement of SQL, which is the classic relational data query language, and also the foremost to demonstrate that RDBMS could deliver better transaction processing performance. It is a model that is created to show that it is feasible to build a Relational System that is used in a real-life environment to decode real-life problems.

Two major subsystems of System R:

• Research Storage
• System Relational Data System

It is one of the most valuable exercises in relational algebra. It is the most typically used way to integrate data from two or more relations. A Join is consistently performed based on a similar or related column. Most complicated queries of SQL involve the JOIN command.

There are the following types of join:

Inner joins: Inner join is of 3 categories. They are:

• Theta join
• Natural join
• Equi join

Outer joins: Outer join has three types. They are:

• Left outer join
• Right outer join
• Full outer join

Shared lock: Shared lock is needed for reading a data item. Considerable transactions keep a lock on the exact data item. When more than one transaction is authorized to read the data items it is called shared lock.

Exclusive lock: When any transaction is close to executing the write operation, then the lock on the data item is an entire lock. Because, if we permit more than one transaction then that will guide to the inconsistency in the database.

• Primary key: The Primary key is an attribute in a table that can exclusively recognize each record in a table. It is mandatory for every table.
• Candidate key: The Candidate key is an attribute or group of attributes that can exclusively recognize a tuple. The Primary key can be chosen from these attributes.
• Super key: A Super key is a group of attributes that can exclusively recognize a tuple. It is a superset of the candidate key.
• Foreign key: The Foreign key is a primary key from one table, which has a connection with another table. It works as a cross-reference between tables.

• Each column must have an atomic/single value
• To eliminate identical columns from the same table
• Make separate tables for each set of related data and recognize each row with a unique column

• If the table is in 1NF, then the table must follow the rules of 1NF.
• Every non-prime attribute is entirely functionally dependent on the main key, i.e., every non-key attribute must be dependent on the main key in a way that if any key component is deleted, then also the non_key element will be saved in the database.

3NF means Third Normal Form. A database is known as 3NF when it fulfills the following conditions:

• It is in third 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 means Boyce-Codd Normal Form. It is an evolved version of 3NF, so it is also known as 3.5NF. BCNF is more rigid than 3NF.

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

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

ACID properties are fundamental rules that have to be fulfilled by every transaction to maintain integrity. These rules and properties are:

ATOMICITY: Atomicity is more commonly called the “all or nothing rule”. This implies all are viewed as one unit, and they either run to completion or are not implemented at all.

CONSISTENCY: This property guides the uniformity of the data. Consistency indicates that the database is consistent before and after the transaction.

ISOLATION: This property declares that the number of transactions can be performed together without showing the inconsistency of the database state.

DURABILITY: This property assures that once the transaction is achieved it will be reserved in the non-volatile memory and a system collision can also not affect it anymore.

DELETE command: It is used to delete rows from a table depending on the condition that we furnish in a WHERE clause.

• DELETE command delete only those rows which are defined with the WHERE clause.
• DELETE command can be called back.
• DELETE command upholds a log, that’s why it is slow.
• DELETE uses row lock while executing the DELETE function.

TRUNCATE command: TRUNCATE command is used to terminate all rows (complete data) from a table. It is equal to the DELETE command with no where clause.

• The TRUNCATE command terminates all the rows from the table.
• The TRUNCATE command cannot be called back.
• The TRUNCATE command doesn’t hold a log. That’s why it is fast.
• TRUNCATE uses a table log while executing the TRUNCATE function.