Architechare of DBMS

o The DBMS design depends upon its architecture. The basic client/server
architecture is used to deal with a large number of PCs, web servers, database
servers and other components that are connected with networks.
o The client/server architecture consists of many PCs and a workstation which are
connected via the network.
o DBMS architecture depends upon how users are connected to the database to
get their request done.

Types of DBMS Architecture

1-Tier Architecture

o In this architecture, the database is directly available to the user. It means the
user can directly sit on the DBMS and uses it.
o Any changes done here will directly be done on the database itself. It doesn't
provide a handy tool for end users.

2-Tier Architecture

o The 2-Tier architecture is same as basic client-server. In the two-tier

architecture, applications on the client end can directly communicate with the
database at the server side. For this interaction, API's like: ODBC, JDBC are used.
o The user interfaces and application programs are run on the client-side.

-Tier Architecture

o The 3-Tier architecture contains another layer between the client and server. In
this architecture, client can't directly communicate with the server.
o The application on the client-end interacts with an application server which
further communicates with the database system.
Explain data information
Introduction
All business enterprises in the world relies on the data which is being gathered from
various sources, stored for future use and analyzed for effective decision making. The business
managers determine the set of rules that must be applied to data before it is stored in the
database. This ensures the integrity of data. For example, no employees should be recruited if
the age is below 18. Such rules have to be enforced on the data stored. The data which satisfies
the conditions should be stored for future analysis. If the data gathered fails to satisfy the
condition set, it must be rejected. This ensures that the data stored in the table will be valid,
and maintains integrity.
The business rules applied to data will vary form organization to organization. The rules
applied to data gathered and processed by saving bank system will differ from the business
rules applied to data gathered and processed by an inventory system.
Business rules which are applied on data being stored in a table are called as Constraints. The
constraints super controls the data being entered into a table for permanent storage.
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy
and reliability of the data in the table. If there is any violation between the constraint and the
data action, the action is aborted. Constraints can be column level or table level.
The whole purpose of constraints is to maintain the data integrity during an
update/delete/insert into a table.

Characteristics of DBMS
 A database management system is able to store any kind of data in a database.
 The database management system has to support ACID (atomicity,
consistency, isolation, durability) properties.
 The Database management system allows so many users to access databases
at the same time.
 Backup and recovery are the two main methods which allow users to protect
the data from damage or loss.
 It also provides multiple views for different users in a single organization.
 It follows the concept of normalization which is helpful to minimize the
redundancy of a relation.
 It also provides users query language, helpful to insert, retrieve, update, and
delete the data in a database.
 Explain and its DBA
A database administrator, or DBA, is responsible for maintaining, securing, and operating
databases and also ensures that data is correctly stored and retrieved.

In addition, DBAs often work with developers to design and implement new features and
troubleshoot any issues. A DBA must have a strong understanding of both technical and
business needs.

The role of DBA is becoming increasingly important in today’s information-driven business

environment. Thoroughout the world, more and more organizations depend on data to
discover analytical insights on market conditions, new business models, and cost-cutting
measures. The global cloud computing market is also expected to expand as companies
move their business operations to the cloud. Consequently, the need for qualified DBAs will
only continue to grow.

Different types of DBAs

 System administrators are responsible for the overall management and upkeep of a
computer system, including installing and configuring software, applying security patches,
and monitoring system performance.
 Database architects design databases to meet the specific needs of an organization.
 Database analysts collect and analyze data to help improve database performance. They
may also be responsible for developing reports and providing recommendations to
database administrators.
 Data modelers create and maintain data models that depict the relationship between data
elements. Data modeling is a critical component of effective database design.
 Application DBAs are responsible for administrating databases that support applications.
Specific tasks include installing and configuring applications, ensuring that data is
synchronized correctly between databases, and troubleshooting application-related issues.
 Task-oriented DBAs focus on a particular area of database administration, such as backup
and recovery, security, or performance tuning. They typically have in-depth knowledge of
a specific database management system (DBMS).
 Performance analysts monitor database performance and identify areas where
improvement is needed. They may also be responsible for creating performance reports
and providing recommendations to database administrators.
 Data warehouse administrators manage databases that store data for business intelligence
or decision-support applications. They are responsible for extracting data correctly,
transforming the data, and loading it into the data warehouse.
 Cloud DBAs are responsible for administrating databases hosted in a cloud computing
environment, provisioning and managing database instances, setting up replication and
high availability, and monitoring database performance.
Explain relational model
The relational model for database management is an approach to logically represent and
manage the data stored in a database. In this model, the data is organized into a collection of
two-dimensional inter-related tables, also known as relations. Each relation is a collection
of columns and rows, where the column represents the attributes of an entity and the rows (or
tuples) represents the records.

The use of tables to store the data provided a straightforward, efficient, and flexible way to
store and access structured information. Because of this simplicity, this data model provides
easy data sorting and data access. Hence, it is used widely around the world for data storage
and processing.

Explain normalization
A large database defined as a single relation may result in data duplication. This
repetition of data may result in:

o Making relations very large.

o It isn't easy to maintain and update data as it would involve searching many
records in relation.
o Wastage and poor utilization of disk space and resources.
o The likelihood of errors and inconsistencies increases.

So to handle these problems, we should analyze and decompose the relations with
redundant data into smaller, simpler, and well-structured relations that are satisfy
desirable properties. Normalization is a process of decomposing the relations into
relations with fewer attributes.
Characteristics of SQL
o SQL is easy to learn.
o SQL is used to access data from relational database management systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and manipulate it when needed.
o SQL is used to create and drop the database and table.
o SQL is used to create a view, stored procedure, function in a database.
o SQL allows users to set permissions on tables, procedures, and views.

 Advantages of SQL :
SQL has many advantages which makes it popular and highly demanded.
It is a reliable and efficient language used for communicating with the
database. Some advantages of SQL are as follows:
1. Faster Query Processing –
Large amount of data is retrieved quickly and efficiently. Operations
like Insertion, deletion, manipulation of data is also done in almost no
time.
2. No Coding Skills –
For data retrieval, large number of lines of code is not required. All
basic keywords such as SELECT, INSERT INTO, UPDATE, etc are used
and also the syntactical rules are not complex in SQL, which makes it
a user-friendly language.
 Standardized.Language–
Due to documentation and long establishment over years, it provides a
uniform platform worldwide to all its users.
Disadvantages of SQL :
Although SQL has many advantages, still there are a few disadvantages.
Various Disadvantages of SQL are as follows:
1. Complex Interface –
SQL has a difficult interface that makes few users uncomfortable
while dealing with the database.
2. Cost –
Some versions are costly and hence, programmers cannot access it.
3. Partial Control –
Due to hidden business rules, complete control is not given to the database.
Data types of SQL.
SQL supports the following data types for its column and parameter
declarations.

 CHARACTER [(SIZE)] or CHAR [(SIZE)]

 VARCHAR (SIZE)
 BOOLEAN
 NUMBER [(p[,s])]
 RAW
 FLOAT(p)
 DATE
 TIME
 TIMESTAMP
 CLOB [(length)] or CHARACTER LARGE OBJECT [(length)] or CHAR LARGE
OBJECT [(length)]
 BLOB [(length)] or BINARY LARGE OBJECT [(length)]


 CHARACTER [(SIZE)] or CHAR [(SIZE)]

 The CHARACTER data type accepts character strings, including Unicode,
of a fixed length. The length of the character string should be specified in
the data type declaration.
 For example, CHARACTER(n) where n represents the desired length of the
character string. If no length is specified during the declaration, the
default length is 1.
 The minimum length of the CHARACTER data type is 1 and it can have a
maximum length up to the table page size. Character strings that are
larger than the page size of the table can be stored as a Character Large
Object (CLOB).
Operator in SQL.
SQL operators are categorized in the following categories:

1. SQL Arithmetic Operators

2. SQL Comparison Operators
3. SQL Logical Operators
4. SQL Set Operators
5. SQL Bit-wise Operators
6. SQL Unary Operators

The Addition Operator in SQL performs the addition on the numerical data of the database
table. In SQL, we can easily add the numerical values of two columns of the same table by
specifying both the column names as the first and second operand. We can also add the
numbers to the existing numbers of the specific column.

Syntax of SQL Addition Operator:

1. SELECT operand1 + operand2;

SQL Subtraction Operator (-)

The Subtraction Operator in SQL performs the subtraction on the numerical data of
the database table. In SQL, we can easily subtract the numerical values of two columns
of the same table by specifying both the column names as the first and second
operand. We can also subtract the number from the existing number of the specific
table column.

Syntax of SQL Subtraction Operator:

1. SELECT operand1 - operand2;

SQL Multiplication Operator (*)

The Multiplication Operator in SQL performs the Multiplication on the numerical data of the
database table. In SQL, we can easily multiply the numerical values of two columns of the
same table by specifying both the column names as the first and second operand.

Syntax of SQL Multiplication Operator:

1. SELECT operand1 * operand2;

SQL Division Operator (/)

The Division Operator in SQL divides the operand on the left side by the operand
on the right side.

Syntax of SQL Division Operator:

1. SELECT operand1 / operand2;

SQL Modulus Operator (%)

The Modulus Operator in SQL provides the remainder when the operand on the
left side is divided by the operand on the right side.

Syntax of SQL Modulus Operator:

1. SELECT operand1 % operand2;

SQL Clauses

. GROUP BY
o SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY
statement is used with the SQL SELECT statement.
o The GROUP BY statement follows the WHERE clause in a SELECT statement and
precedes the ORDER BY clause.
o The GROUP BY statement is used with aggregation function.

Syntax

1. SELECT column
2. FROM table_name
3. WHERE conditions
4. GROUP BY column
5. ORDER BY column

. HAVING

o HAVING clause is used to specify a search condition for a group or an

aggregate.
o Having is used in a GROUP BY clause. If you are not using GROUP BY clause
then you can use HAVING function like a WHERE clause.

Syntax:

1. SELECT column1, column2

2. FROM table_name
3. WHERE conditions
 4. GROUP BY column1, column2
5. HAVING conditions
6. ORDER BY column1, column2;
. ORDER BY
o The ORDER BY clause sorts the result-set in ascending or descending order.
o It sorts the records in ascending order by default. DESC keyword is used to sort the
records in descending order.

Syntax:

1. SELECT column1, column2

2. FROM table_name
3. WHERE condition
ORDER BY column1, column2... ASC|DESC
Constraints:
All business enterprises in the world relies on the data which is being gathered from
various sources, stored for future use and analyzed for effective decision making. The business
managers determine the set of rules that must be applied to data before it is stored in the
database. This ensures the integrity of data. For example, no employees should be recruited if
the age is below 18. Such rules have to be enforced on the data stored. The data which satisfies
the conditions should be stored for future analysis. If the data gathered fails to satisfy the
condition set, it must be rejected. This ensures that the data stored in the table will be valid,
and maintains integrity.
The business rules applied to data will vary form organization to organization. The rules
applied to data gathered and processed by saving bank system will differ from the business
rules applied to data gathered and processed by an inventory system.
Business rules which are applied on data being stored in a table are called as Constraints. The
constraints super controls the data being entered into a table for permanent storage.
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy
and reliability of the data in the table. If there is any violation between the constraint and the
data action, the action is aborted. Constraints can be column level or table level.
The whole purpose of constraints is to maintain the data integrity during an
update/delete/insert into a table.

FOR EXAMPAL: NOT NULL:

NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t
provide value for a particular column while inserting a record into a table, it takes NULL value
by default. By specifying NULL constraint, we can be sure that a particular column(s) cannot
have NULL values. The NOT NULL constraint can only be applied at column level.

Syntax: <Column_Name> <Datatype>(<Size>) NOT NULL;

Example:

CREATE TABLE STUDENT

(ROLL_NO NUMBER(5) NOT NULL, STUD_NAME VARCHAR2(15),
STUD_AGE NUMBER(3) NOT NULL, STUD_ADDRESS VARCHAR2(2));
View:
For data security reasons, after a table is created and populated with data, it is necessary to
prevent all users from accessing all columns of a table. This will lead to creating several
tables and assigning them to the specific users as required. This will solve the problem of
data security but will conversely lead to the new problem of data redundancy. To reduce th e
redundant data to the minimum, we can create the new object called View.
The view is the virtual table which is created by selecting the information from one or more
existing table. The table from which the view is created is called as the Base Table or Parent
Table. A view is nothing more than a SQL statement that is stored in the database with an
associated name. A view is actually a composition of a table in the form of a predefined SQL
query.
A view can contain all rows of a table or select rows from a table. A view can be created from
one or many tables which depend on the written SQL query to create a view. The view does
not contain its own data but the data in the view is derived from the base table. We can perform
all
DML operations on views.
When DML operations are performed on the views, the changes are also reflected back to the
base table.
Views, which are a type of virtual tables, allow users to do the following −
 Structure data in a way that users or classes of users find natural or intuitive.
 Restrict access to the data in such a way that a user can see and (sometimes) modify
exactly what they need and no more.
 Summarize data from various tables which can be used to generate reports.

Syntax: The basic CREATE VIEW syntax is as follows

CREATE VIEW view_name AS SELECT column1, column2.....

FROM table_name WHERE [condition];

You can include multiple tables in your SELECT statement in a similar way as you use them
in a normal SQL SELECT query.

Example:
Consider the CUSTOMERS table having the following records −

Cust_Id Name Age Address Salary

1 Ajay 15 Delhi 5000
2 Amol 23 Pune 10000
3 Atul 25 Nagpur 20000
4 Ashwika 7 mumbai 30000
5 Nilesh 23 Pune 15000

SQL > CREATE VIEW CUSTOMERS_VIEW AS

SELECT name, age FROM CUSTOMERS;
Indexi
is a data structure technique which allows you to quickly retrieve records from a database file.
An Index is a small table having only two columns. The first column comprises a copy of the
primary or candidate key of a table. Its second column contains a set of pointers for holding
the address of the disk block where that specific key value stored.

An index –

 Takes a search key as input

 Efficiently returns a collection of matching records.

An index –

 Takes a search key as input

 Efficiently returns a collection of matching records.

Indexing in Database is defined based on its indexing attributes. Two main types of indexing
methods are:

Primary Index in DBMS

Primary Index is an ordered file which is fixed length size with two fields. The first field is the
same a primary key and second, filed is pointed to that specific data block. In the primary
Index, there is always one to one relationship between the entries in the index table.

Secondary Index in DBMS

The secondary Index in DBMS can be generated by a field which has a unique value for each
record, and it should be a candidate key. It is also known as a non-clustering index.
This two-level database indexing technique is used to reduce the mapping size of the first level.
For the first level, a large range of numbers is selected because of this; the mapping size always
remains small.
 Joins:
Sometimes it is necessary to retrieve data from multiple tables as though they were the single
entity. Then the single SQL statement can manipulate the data from all the tables. This can be
achieved by using joins. As the name suggests, JOIN means to combine something. In case of
SQL, JOIN means "to combine two or more tables". The SQL JOIN clause takes records from
two or more tables in a database and combines it together. The tables are joined on the columns
that have the same data type and size (width). The tables in the database can be related with
each other with the help of Keys. The purpose is to bind the data together, across tables, without
repeating all of the data in every table.

Exampla

Full Outer Join:

In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join tables
have all the records from both tables. It puts NULL on the place of matches not found.

Syntax

1. SELECT table1.column1, table1.column2, table2.column1,....

2. FROM table1
3. FULL JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

2. FROM EMPLOYEE
3. FULL JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
What are functions?
Functions are methods used to perform data operations. SQL has many in-built functions used
to perform string concatenations, mathematical calculations etc.

SQL functions are categorized into the following two categories:

1. Aggregate Functions
2. Scalar Functions

Aggregate SQL Functions

The Aggregate Functions in SQL perform calculations on a group of values and then return a
single value. Following are a few of the most commonly used Aggregate Functions:

Function Description
SUM() Used to return the sum of a group of values.
COUNT() Returns the number of rows either based on a condition, or without a condition.
AVG() Used to calculate the average value of a numeric column.
MIN() This function returns the minimum value of a column.
MAX() Returns a maximum value of a column.
FIRST() Used to return the first value of the column.
LAST() This function returns the last value of the column.

Consider the following table.

EMP_ID ENAME SAL

1 ASK 10000
2 AMOL 15000
3 ATUL 10000
4 NILESH 18000
5 AMIT 20000

1 SUM()

Used to return a total sum of numeric column which you choose.

Syntax:
Select Sum(Column_Name) From <Table_Name>;
Example:
SELECT MAX(SAL) FROM EMP;
Output:
20000