1. What you mean by DBMS ?

A database management system is collection of software or programs which help user

in creation and maintainance of a database hence it also known as computerized record
system

Example

A small startup company and industries managing a huge amount of data becomes a mess.
So, software like DBMS brought a revolution in many fields regarding efficient information
management.

It is basically a set of programs which allow users to store, modify or update, and retrieve
information from the database as per the user requirements.

DBMS also provides security and protection to databases. It acts as a middle layer between
the database and user.

The different types of DBMS are My SQL, Oracle, MYSQL Server, Microsoft Access, SQL,
DB2 etc.

Functions of DBMS

The functions of DBMS are explained below −

 The motive behind the development of the DBMS is to manage the information as a
whole.
 Its main objective is to make the data easily accessible, rapid, less costly and flexible
for its users.
 It removes the duplication of data and minimizes data redundancy.
 The extra space covered by duplicate data is used for other purposes and there is no
wastage of space.
 The DBMS is easy to use and learn.
 DBMS packages are user-friendly and flexible.
 There is data independence because the user can do changes at any level of the
database without impact on other levels like hardware and software processes.
 DBMS is economically good for everyone.
 Anyone can use, store and manipulate data at an affordable price.
 It can be accessible by multiple users and helps to recover data in case it is not saved
by the user. The system can easily recover the data in no time and it maintains
accuracy and integrity.
 It prevents unauthorized access to the data and the data remains private. Through
centralized control, DBMS makes it sure that proper security is provided to the data.
 It is useful for retrieving, analyzing and storing the data.

2. Write advantages of DBMS over a file system ?

 Data Redundancy and Inconsistency:
Redundancy means repeating the data in a system. In a normal file system, there is a high
chance that there can be various files of the same data used by different users for specific
purposes. If any user changes the data in its files, then the changes are not reflected in all
files. This creates inconsistency in the data, and it may lead to the failure of the system. But
in the DBMS, there is only one repository of data, and multiple users can use it. If any user
changes the data, then it is reflected to each user as they are using the same repository.

 Data Sharing:

In the normal file system, data sharing is too difficult because file sharing is a complex task.
In DBMS, all the data is centralized, so data sharing is a very easy task.

 Data Concurrency:

When more than one user accesses the database simultaneously, then it is called concurrency.
In a file system, when multiple users are using the files at the same time, then there may be a
chance of anomalies in the data due to changes, and it does not provide any method to detect
anomalies. But in DBMS, we have a locking system to detect the anomalies so we can protect
the data.

 Data Searching:

To search the data in a file system, we have to write a specific program and run it. In DBMS,
we have query languages by which we can write small queries to get the data we want from
the database. We can use various query languages, like MySQL, Oracle, etc., for a database
to search and retrieve the data.

 Data Integrity:

When we insert new data into the database, we require some specific constraints on the data
like integer or not null, etc. The file system does not provide any system to check the
constraints, whereas DBMS has the functionality to check the constraints on the data, and it
allows user defined data types.

 System Crashing:

There are various reasons by which our system can crash. Once a system crashes, the file
system loses all its files and data, and we can not recover it again. But DBMS has a recovery
manager which can recover the data if the system crashes.

 Data Security:

In the file system, we have only password protection to save the files and data, but it is not
reliable. In DBMS, we have special algorithms and features to secure the data.

 Backup:

For the backup of data, DBMS creates a subsystem, whereas the file system does not create
any system for data backup.
  Interfaces:

DBMS provides various kinds of the interface to users like application interfaces, graphical
interfaces, etc.

 Maintenance:

The file system is not maintained easily because it is decentralized, whereas DBMS is a
centralized system, so it becomes easy to maintain it.

3. Explain the roll of DBA ?

A database administrator (DBA) is a person or group in charge of implementing DBMS in an
organization. The DBA job requires a high degree of technical expertise. DBA consists of a
team of people rather than just one person.

The primary role of Database administrator is as follows −

 Database design
 Performance issues
 Database accessibility
 Capacity issues
 Data replication
 Table Maintenance

Responsibilities of DBA
The responsibilities of DBA are as follows −

 Makes the decision concerning the content of the database.

 Plans the storage structure and access strategy.
 Provides the support to the users.
 Defines the security and integrity checks.
 Interpreter backup and recovery strategies.
 Monitoring the performance and responding to the changes in the requirements.

Skills required for DBA

The skills required to be a successful DBA are as follows −

 Database designing.
 Knowledge of Structured Query Language (SQL).
 Know about distributed architecture.
 Knowledge on different operating servers.
 Idea on Relational Database Management System (RDBMS).
 Ready to face challenges and solve the problems quickly.
4. Explain data model in detail ?
Data Model is the modeling of the data description, data semantics, and consistency constraints
of the data. It provides the conceptual tools for describing the design of a database at each level
of data abstraction. Therefore, there are following four data models used for understanding the
structure of the database

1) Relational Data Model: This type of model designs the data in the form of rows and
columns within a table. Thus, a relational model uses tables for representing data and in-
between relationships. Tables are also called relations. This model was initially described by
Edgar F. Codd, in 1969. The relational data model is the widely used model which is
primarily used by commercial data processing applications.

2) Entity-Relationship Data Model: An ER model is the logical representation of data as

objects and relationships among them. These objects are known as entities, and relationship is
an association among these entities. This model was designed by Peter Chen and published in
1976 papers. It was widely used in database designing. A set of attributes describe the
entities. For example, student_name, student_id describes the 'student' entity. A set of the
same type of entities is known as an 'Entity set', and the set of the same type of relationships
is known as 'relationship set'.

3) Object-based Data Model: An extension of the ER model with notions of functions,

encapsulation, and object identity, as well. This model supports a rich type system that
includes structured and collection types. Thus, in 1980s, various database systems following
the object-oriented approach were developed. Here, the objects are nothing but the data
carrying its properties.

5. Explain database architechture ?

 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.
 The client/server architecture consists of many PCs and a workstation which are connected
via the network.
 DBMS architecture depends upon how users are connected to the database to get their
request done.

Types of DBMS Architecture

Database architecture can be seen as a single tier or multi-tier. But logically, database
architecture is of two types like: 2-tier architecture and 3-tier architecture.

1-Tier Architecture

 In this architecture, the database is directly available to the user. It means the user can
directly sit on the DBMS and uses it.
 Any changes done here will directly be done on the database itself. It doesn't provide a
handy tool for end users.
 The 1-Tier architecture is used for development of the local application, where programmers
can directly communicate with the database for the quick response.

2-Tier Architecture

 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.
 The user interfaces and application programs are run on the client-side.
 The server side is responsible to provide the functionalities like: query processing and
transaction management.
 To communicate with the DBMS, client-side application establishes a connection with the
server side.
Fig: 2-tier Architecture

3-Tier Architecture

 The 3-Tier architecture contains another layer between the client and server. In this
architecture, client can't directly communicate with the server.
 The application on the client-end interacts with an application server which further
communicates with the database system.
 End user has no idea about the existence of the database beyond the application server. The
database also has no idea about any other user beyond the application.
 The 3-Tier architecture is used in case of large web application.

6. Write a note on data independence ?

 Data independence can be explained using the three-schema architecture.
 Data independence refers characteristic of being able to modify the schema at one
level of the database system without altering the schema at the next higher level.

There are two types of data independence:

1. Logical Data Independence

 Logical data independence refers characteristic of being able to change the conceptual
schema without having to change the external schema.
 Logical data independence is used to separate the external level from the conceptual
view.
  If we do any changes in the conceptual view of the data, then the user view of the data
would not be affected.
 Logical data independence occurs at the user interface level.

2. Physical Data Independence

 Physical data independence can be defined as the capacity to change the internal
schema without having to change the conceptual schema.
 If we do any changes in the storage size of the database system server, then the
Conceptual structure of the database will not be affected.
 Physical data independence is used to separate conceptual levels from the internal
levels.
 Physical data independence occurs at the logical interface level.

7. Explain difference types of attributes in ER model ?

Types of attributes
The different types of attributes are as follows −
Composite attribute

It can be divided into smaller sub parts, each sub part can form an independent attribute.

For example −

Name
FirstName MiddelName LastName
Simple or Atomic attribute

Attributes that cannot be further subdivided are called atomic attributes.

For example −

Phone number
PIN code

Single valued Attribute

Attributes having a single value for a particular item is called a single valued attribute.

For example: Room Number

Multi-valued Attribute

Attribute having a set of values for a single entity is called a multi-valued attribute.
For example −

e-mail
Tel.No
Hobbies

Derived Attributes or stored Attributes

When one attribute value is derived from the other is called a derived attribute.

For example: Age can be derived from date of birth, where,

 Age is the derived attribute.

 DOB is the stored attribute.

Complex Attribute

Nesting of composite and multi-valued attributes forms a complex attribute.

For example

If a person has more than one house and each house has more than one phone. Then, that
attribute phone is represented as a complex attribute.
8. Explain how to create database & table use it ?
A table is used to organize data in the form of rows and columns and used for both storing
and displaying records in the structure format. It is similar to worksheets in the spreadsheet
application. A table creation command requires three things:

 Name of the table

 Names of fields
 Definitions for each field

MySQL allows us to create a table into the database mainly in two ways:

1. MySQL Command Line Client

2. MySQL Workbench

MySQL Command Line Client

MySQL allows us to create a table into the database by using the CREATE TABLE
command. Following is a generic syntax for creating a MySQL table in the database.

1. CREATE TABLE [IF NOT EXISTS] table_name(

2. column_definition1,

3. column_definition2,
4. ........,

5. table_constraints
6. );

9. Explain integrity constraint in detail ?

  ntegrity constraints are a set of rules. It is used to maintain the quality of information.
 Integrity constraints ensure that the data insertion, updating, and other processes have to
be performed in such a way that data integrity is not affected.
 Thus, integrity constraint is used to guard against accidental damage to the database.

Types of Integrity Constraint

1. Domain constraints

 Domain constraints can be defined as the definition of a valid set of values for an attribute.
 The data type of domain includes string, character, integer, time, date, currency, etc. The
value of the attribute must be available in the corresponding domain.

Example:

2. Entity integrity constraints

 The entity integrity constraint states that primary key value can't be null.
  This is because the primary key value is used to identify individual rows in relation and if the
primary key has a null value, then we can't identify those rows.
 A table can contain a null value other than the primary key field.

Example:

3. Referential Integrity Constraints

 A referential integrity constraint is specified between two tables.

 In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of
Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table
2.

Example:
4. Key constraints

 Keys are the entity set that is used to identify an entity within its entity set uniquely.
 An entity set can have multiple keys, but out of which one key will be the primary key. A
primary key can contain a unique and null value in the relational table.

Example:

10. Explain different operation on table ?

Operation on Table
1. Create table
2. Drop table
3. Delete table
4. Rename table

SQL Create Table

SQL create table is used to create a table in the database. To define the table, you should
define the name of the table and also define its columns and column's data type.

Syntax

1. create table "table_name"

2. ("column1" "data type",

3. "column2" "data type",

4. "column3" "data type",

5. ...
6. "columnN" "data type");
Drop table

A SQL drop table is used to delete a table definition and all the data from a table. When this
command is executed, all the information available in the table is lost forever, so you have to
very careful while using this command.

Syntax

1. DROP TABLE "table_name";

SQL DELETE table

In SQL, DELETE statement is used to delete rows from a table. We can use WHERE
condition to delete a specific row from a table. If you want to delete all the records from the
table, then you don't need to use the WHERE clause.

Syntax

1. DELETE FROM table_name WHERE condition;

11. Explain all DML statements with example ?

DML is an abbreviation of Data Manipulation Language.

The DML commands in Structured Query Language change the data present in the SQL
database. We can easily access, store, modify, update and delete the existing records from the
database using DML commands.

Following are the four main DML commands in SQL:

1. SELECT Command
2. INSERT Command
3. UPDATE Command
4. DELETE Command

SELECT DML Command

SELECT is the most important data manipulation command in Structured Query Language.
The SELECT command shows the records of the specified table. It also shows the particular
record of a particular column by using the WHERE clause.

Current Time 0:00

/
Duration 18:10
Â

Syntax of SELECT DML command

 1. SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM Name_o
f_table;

Here, column_Name_1, column_Name_2, ….., column_Name_N are the names of those

columns whose data we want to retrieve from the table.

If we want to retrieve the data from all the columns of the table, we have to use the following
SELECT command:

1. SELECT * FROM table_name;

INSERT DML Command

INSERT is another most important data manipulation command in Structured Query
Language, which allows users to insert data in database tables.

Syntax of INSERT Command

1. INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 , column_Name3

, .... column_NameN ) VALUES (value_1,

UPDATE DML Command

UPDATE is another most important data manipulation command in Structured Query
Language, which allows users to update or modify the existing data in database tables.

Syntax of UPDATE Command

1. UPDATE Table_name SET [column_name1= value_1, ….., column_nameN = value_

N] WHERE CONDITION;

DELETE DML Command

DELETE is a DML command which allows SQL users to remove single or multiple existing
records from the database tables.

This command of Data Manipulation Language does not delete the stored data permanently
from the database. We use the WHERE clause with the DELETE command to select specific
rows from the table.

Syntax of DELETE Command

1. DELETE FROM Table_Name WHERE condition;

12. Explain aggregate function ?

Types of SQL Aggregation Function

Count():

Count(*): Returns total number of records .i.e 6.

Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary): Return number of distinct Non Null values over the column
salary .i.e 4

Sum():

sum(salary): Sum all Non Null values of Column salary i.e., 310
sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.

Avg():

Avg(salary) = Sum(salary) / count(salary) = 310/5

Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4
Min():

Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max(salary): Maximum value in the salary i.e., 80.

13. Explain group by having clause in detail ?

HAVING WHERE
1. The HAVING clause is used in database 1. The WHERE clause is used in database
systems to fetch the data/values from the systems to fetch the data/values from the
groups according to the given condition. tables according to the given condition.
2. The HAVING clause is always executed 2. The WHERE clause can be executed
with the GROUP BY clause. without the GROUP BY clause.
3. The HAVING clause can include SQL 3. We cannot use the SQL aggregate function
aggregate functions in a query or statement. with WHERE clause in statements.
4. Whereas, we can easily use WHERE clause
4. We can only use SELECT statement with
with UPDATE, DELETE, and SELECT
HAVING clause for filtering the records.
statements.
5. The HAVING clause is used in SQL 5. The WHERE clause is always used before
queries after the GROUP BY clause. the GROUP BY clause in SQL queries.
6. We can implements this SQL clause in 6. We can implements this SQL clause in row
column operations. operations.
7. It is a post-filter. 7. It is a pre-filter.
8. It is used to filter the single record of the
8. It is used to filter groups.
table.