❖ + What is database - A database is a collection of data that is organized which is also called structured data.

+ Database Management System (DBMS) - Database management system is a software which is used to manage the
It can be managed through Database Management System (DBMS).
database. Ex: (MYSQL, SQL, ORACAL). Database provides and interface to perform various operations like – 1> Database
In It can be accessed or stored in a computer system.
creation 2> Updating data 3> Store data in it. It provides protection and security to the database. In the of multiusers. It
a database data is organized into tables. Consisting of rows and column and it is index so data can be updated, expanded
also maintains data consistency. Database Management Systems (DBMS) are software systems used to store, retrieve,
and deleted easily. Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text,
and run queries on data. A DBMS serves as an interface between an end-user and a database, allowing users to create,
numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc. Word 'Data' is originated from the
read, update, and delete data in the database. A database management system (DBMS) is a software package designed to
word 'datum' that means 'single piece of information.' It is plural of the word datum. In computing, Data is information that
define, manipulate, retrieve and manage data in a database. A DBMS generally manipulates the data itself, the data
can be translated into a form for efficient movement and processing. Data is interchangeable. A database is an organized
format, field names, record structure and file structure. It also defines rules to validate and manipulate this data. Database
collection of structured information, or data, typically stored electronically in a computer system. A database is usually
management systems are set up on specific data handling concepts, as the practice of administrating a database evolves.
controlled by a Database management system (DBMS). Together, the data and the DBMS, along with the applications that
The earliest databases only handled individual single pieces of specially formatted data. Today’s more evolved systems
are associated with them, are referred to as a database system, often shortened to just database. Data within the most
can handle different kinds of less formatted data and tie them together in more elaborate ways.
common types of databases in operation today is typically modelled in rows and columns in a series of tables to make
Advantages of DBMS –
processing and data querying efficient. The data can then be easily accessed, managed, modified, updated, controlled, and
Controls database redundancy - It can control data redundancy because it stored all the data in one single database file and that
organized. Most databases use structured query language (SQL) for writing and querying data.
recorded data is placed in the database.
Data sharing - In DBMS the authorized users of an organization can share the data among multiple users.
+ Entity - An entity in DBMS (Database management System) is a real-world thing or a real-world object which is distinguishable from
Easy maintenance - It can be easily maintainable give to the centralized nature of the database system.
other objects in the real world. For example, a car is an entity. An attribute of an entity gives us information about the characteristic
features of an entity. An Entity is a real-world thing can be distinctly identified like a person, place or a concept it is an object which is Reduce time - It reduces development time and maintenance need.
distinguishable from other if we cannot distinguish it from others than it is an object but not an entity. Backup - It provides backup and recovery subsystems which creates automatic backup of data from hardware and software failure and
E.g.: - in a school database student, teacher, course can be considered as Entities. restores data if required. Multiple user interface - It provides different types of user interface like graphical user interface or application
Types of DBMS Entities – 1> Strong Entity 2> Weak Entity. user interface.
> Strong Entity - The strong entity has a primary key. Weak entities are dependent on strong entity. Its existence is not Dis-advantages of DBMS –
dependent on any other entity. Strong Entity is represented by a single rectangle - Cost of hardware and software - It is required a high speed of data processor and large memory size to run DBMS.
>Weak Entity - The weak entity in DBMS do not have a primary key and are dependent on the parent entity. It mainly Size- It occupied the space of disc and large memory to run them efficiently.
depends on other entities. Weak Entity is represented by double rectangle – Complexity - Database system create additional complexity and requirement.
Higher impact of failure - Failure is highly impact the database because in most of the organization all the data store in a single
database and if the database is damage due to electric failure of database corruption the data will be forever.
+ Architecture of DBMS –
1> 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. + Attributes - Attributes in DBMS are represented by elliptical shapes. An entity may contain any number of attributes
2> The client/server architecture consists of many PCs and a workstation which are connected via the network. while one of the attributes is considered to be a primary key attribute. There are different types of attributes in DBMS:
3> DBMS architecture depends upon how users are connected to the database to get their request done. There are three Simple, Composite, Single Valued, Multi-Valued, Stored, Derived, Key, and Complex attributes. In general, an attribute is a
types of architecture in DBMS. characteristic. In a database management system (DBMS), an attribute refers to a database component, such as a table. It
1> One tire architecture (1 – tire ) :- In these Architecture the database is directly available to the user it means the user can also may refer to a database field. Attributes describe the instances in the column of a database. In relational databases,
directly seat on the DBMS and uses it. Any changes done here will directly be done on the database itself. It does not provide attributes are the describing characteristics or properties that define all items pertaining to a certain category applied to all
a handy tool for the end user. The one tire architecture is used for development of the local application, we are programmers cells of a column. The rows, instead, are called tuples, and represent data sets applied to a single entity to uniquely identify
can directly communicate with the database.
each item. Attributes are, therefore, the characteristics of every individual tuple that help describe its unique properties.
2> Two tire Architecture ( 2 – tire ) :- The two tire Architecture is a basic client server. In the two tire architecture, application on the
Beyond the self-explanatory simple or single-valued attributes, there are several types of attributes available. –
client end can directly communicate with the database at the server site for these interaction is possible only Oracle Database
Composite attribute: is an attribute composed of several other simple attributes. For example, the Address attribute of an
Connectivity (ODBC) and Java Database Connectivity (JDBC)The user interfaces and application program are run the client side. The
employee entity could consist of the Street, City, Postal code and Country attributes.
server side is responsible for to provides the functionality query programming and transaction management. To communicate with the
DBMS client side application stablishes the connection with the server side. Multivalued attribute: is an attribute where more than one description can be provided. For example, an employee entity may
3> Three tire Architecture ( 3 – tire ) :- The three tire architecture contains another where between the client and server. In these have more than one Email ID attributes in the same cell.
architecture clients cannot directly communicate with the server. The application on the client end interacts with an application server Key attribute or primary attribute: is an ID, key, letter or number that uniquely identifies that item. For example, it can be the
which further communicate with the database system. End user has no idea about the existence of the database beyond the application number of a certain invoice (e.g., the individual ID of that invoice). A table that contains a single key attribute is considered a
serve. The database also have no idea about any user beyond the application. The three-tire architecture are used in case of large way strong entity. However, a table might contain more than one key attribute if it’s derived from other tables.
application. Derived attribute: as the name implies, these are derived from other attributes, either directly or through specific formula
results. For example, the Age attribute of an Employee could be derived from the Date of Birth attribute. In other instances, a
formula might calculate the VAT of a certain payment, so that whenever the cell with the attribute Payment is filled, the cell
+ DBMS users - There are a number of users who can access or retrieve the data on demand using the application and the
with the derived attribute VAT automatically calculates its value.
interfaces provided by the Database Management System (DBMS).
Types of Users –
Native users − The native users need not be aware of the presence of the database system. They are end users of the + Type of relationships - A relational database collects different types of data sets that use tables, records, and columns. It is
database who works through a menu driven application program, where the type and range of response is always indicated used to create a well-defined relationship between database tables so that relational databases can be easily stored. For
to the user. example, of relational databases such as Microsoft SQL Server, Oracle Database, MYSQL, etc.
Online users − Online users may communicate with databases directly through an online terminal or indirectly through user There are three types of relationship - 1) One to one relationship. 2) One to many or many to one relationship. 3) Many to many
interface and application programs. relationships.
Sophisticated Users − They are those users who interact with the system without writing the program instead they form their 1> One to one (1:1) - As above example one to one relationship is used to create a relationship between two tables in which a single row
request in database query language. They are the SQL programmers, who are going to deal directly with the database. They of the first table can only be related to one and only second table similarly. The row of second table can also be related to any one row of
write queries to delete or select or insert and update the database. the first table.
Specialized Users − Specialized users who write specialized database applications that do not fit into the fractional database 2> One to many (1: N/N:1) - As above example one to many relations is used to create a relationship between two tables. In these single
rows of the first table can be related to one or many rows of second tables can only one row in the first table it is also known as many to
processing framework.
one relationship.
Application Programmer − The application programmer users who are responsible for developing the application programs 3> Many to Many (N:N) - In many to many relationships each record of first table can related to any record in the second table.
or user interface. The application programs could be written in high level language. For example − Java, .net, php, etc., Similarly, each record of the second table can also related to more than one record of the first table.
Database Administrator (DBA) − It is a person or the group in charge of implementing the database system within the E.G.: There are many people involved in each project and every person can involve more than one project.
organization. The DBA has all the privileges allowed by the DBMS and can assign or remove the privileges from the users.

+ Centralized Systems - A centralized database is stored at a single location such as a mainframe computer. It is maintained and
+ Data Models - Data Model is the modelling of the data description, data semantics, and consistency constraints of the data. modified from that location only and usually accessed using an internet connection such as a LAN or WAN. The centralized database is
It provides the conceptual tools for describing the design of a database at each level of data abstraction. Therefore, there are used by organisations such as colleges, companies, banks etc.
following four data models used for understanding the structure of the database: Advantages of centralized system: -
1) Relational Data Model: This type of model designs the data in the form of rows and columns within a table. Thus, a -The data integrity is maximized as the whole database is stored at a single physical location. This means that it is easier to
relational model uses tables for representing data and in-between relationships. Tables are also called relations. This model coordinate the data and it is as accurate and consistent as possible.
was initially described by Edgar F. Codd, in 1969. The relational data model is the widely used model which is primarily used -The data redundancy is minimal in the centralized database. All the data is stored together and not scattered across
by commercial data processing applications. different locations. So, -it is easier to make sure there is no redundant data available.
2) Entity-Relationship Data Model: An ER model is the logical representation of data as objects and relationships among -Since all the data is in one place, there can be stronger security measures around it. So, the centralized database is much
them. These objects are known as entities, and relationship is an association among these entities. This model was designed more secure. -Data is easily portable because it is stored at the same place.
by Peter Chen and published in 1976 papers. It was widely used in database designing. A set of attributes describe the entities. -The centralized database is cheaper than other types of databases as it requires less power and maintenance.
For example, student_name, student_id describes the 'student' entity. A set of the same type of entities is known as an 'Entity -All the information in the centralized database can be easily accessed from the same location and at the same time.
set', and the set of the same type of relationships is known as 'relationship set'. Disadvantages of centralized system: -
3) Object-based Data Model: An extension of the ER model with notions of functions, encapsulation, and object identity, as -Since all the data is at one location, it takes more time to search and access it. If the network is slow, this process takes
well. This model supports a rich type system that includes structured and collection types. Thus, in 1980s, various database even more time.
systems following the object-oriented approach were developed. Here, the objects are nothing but the data carrying its -There is a lot of data access traffic for the centralized database. This may create a bottleneck situation.
properties. -Since all the data is at the same location, if multiple users try to access it simultaneously it creates a problem. This may
4) Semi structured Data Model: This type of data model is different from the other three data models (explained above). The reduce the efficiency of the system.
semi structured data model allows the data specifications at places where the individual data items of the same type may -If there are no database recovery measures in place and a system failure occurs, then all the data in the database will be
have different attributes sets. The Extensible Markup Language, also known as XML, is widely used for representing the semi destroyed.
structured data. Although XML was initially designed for including the markup information to the text document, it gains
importance because of its application in the exchange of data.
+ Data Server - A data server (DS) is a software program/platform used to provide database services like storing, processing
and securing data. These database services are consumed by other software programs or components. Sometimes the
+ Transaction Server Architecture - A transaction server is a specialized type of server that manages the operations of computer hardware, where the database is running, is also referred to as a database server. Therefore, the data server can
software-based transactions or transaction processing. It manages application and database transactions on a network or be seen as the combination of software and hardware platform that runs the installed database and provides relevant
Internet, within a distributed computing environment. A transaction server may also be referred to as a transaction processing services.
system (TPS) or as a part of one composite TPS solution. A data server is also known as a database server.
A transaction server primarily enables transactions to be processed within distributed computing applications. Typically, a A data server contains the installation of the database system. The hardware configuration of a data server varies for
transaction server is a combination of hardware, software and network components that altogether ensures completion of different databases. The database management system (DBMS) is accessed by other software or by front-end tools like
each transaction. A transaction server works when an application or application server requests for a specific data object SQL+ or Toad (for Oracle). The primary function of a data server is to store data and provide access to the other systems.
residing on a database or database server on the network or Internet. The transaction server acts as an intermediary server The basic access mechanism is to use query languages like SQL. The basic structures of query languages are similar with
that can ensure that the application or user receives the requested data from the database or the completion of that some variations depending upon the data system. A data system takes queries as input and converts it into a form
transaction. The transaction server is also the name of a Microsoft Server (Viper) or Microsoft transaction server (MTS), which understandable by the database, and then executes it to produce the desired output. Some of the widely used database
provides similar functionality. It provides transaction processing services on the COM/DCOM based software components. servers include Oracle, SQL Server and DB2.
A database server is a machine running database software dedicated to providing database services. It is a crucial component
in the client-server computing environment where it provides business-critical information requested by the client systems.
+ Client-Server Systems Architecture - In client-server architecture many clients connected with one server. The server is + Parallel Systems - Speedup & Scale up
centerlines.it provides services to all clients. All clients request to the server for different Service. The server displays the Speedup in Parallel (Systems) Database - Speedup is the effect of applying an increasing number of resources to a fixed
results according to the client’s request. amount of work to achieve a proportional reduction in execution times:
Client/server architecture is a computing model in which the server hosts (computer), send and manages most of the Speedup = (Small system elapsed time [single machine])/(large system elapsed time[parallel machine]).
resources and works to be required by the client. In this type of architecture has one or more client computers attached to Speedup results in resource availability for other tasks. For example, if queries usually take ten minutes to process in one CPU
a central server over a network. This system shares different resources. and running in parallel in more CPUs reduces the time, then additional queries can run without introducing the contention
Client/server architecture is also called as a networking computing model and client-server network because all the that might occur were they to run concurrently.
requests and demands are sent over a network. Linear Speedup -
Basically, client-server model defines how the server provides services to clients Server is a centralized computer Speedup is linear if the speedup is N. That is, the small system elapsed time is N times larger than the large system elapsed
that provides services to all attach clients. For example, file server, web server, etc. each the basic work of server to time (N is number of resources, say CPU). For example, if single machine does the job in 10 seconds and if parallel machine
provide services to each client. The client can be a laptop computer, tablets, and smartphones, etc. The server has many (10 single machines) does the same job in 1 second, then the speedup is (10/1)=10 (refer to the equation above) which is
types of relationship with clients. Many servers have one too many relationships with clients.in one too many relationships equal to N which is the size of larger system. The speedup is achieved due to the 10 times powerful system.
many clients connected with one server. When one client wants to communicate with the server. The server may be Sub-Linear Speedup -
accepted or rejects the request of clients. When the server computer accepts the request of clients than server maintains a Speedup is sub-linear if speedup is less than N (which is usual in most of the parallel systems).
connection according to a defined protocol. The protocol rules over the network.
That must be followed for any network connection. If the one client wants to send an email over the network. It
requests the server, the SMTP (the protocol that is SMPT is standing for simple mail transfer protocol that used to transfer Scaleup in Parallel (Systems) Database –
a mail over the network. SMTP is a set of commands or commands that check authentication and the transfer of email. Scaleup is the ability to keep the same performance levels (response time) when both workload (transactions) and resources
(CPU, memory) increase proportionally.
When configuring the settings for your e-mail program, you usually need to set the SMTP server to your local Internet Scaleup = (Small system small problem elapsed time[single machine])/(large system large problem elapsed time[parallel
Service Provider’s SMTP settings. After all process, the server will transfer e-mail to the desired client. machine]).
For example, if 50 users consume close to 100 percent of the CPU during normal processing, then adding more users
would cause the system to slow down due to contention for limited CPU cycles. However, by adding more CPUs, we can
support extra users without degrading performance.
+ Parallel Database Architectures –
Linear Scaleup -
A parallel DBMS is a DBMS that runs across multiple processors or CPUs and is mainly designed to execute query Scaleup is linear if the resources increase in proportional with the problem size (it is very rare). According to the equation given
operations in parallel, wherever possible. The parallel DBMS link a number of smaller machines to achieve the same above, if small system small problem elapsed time is equal to large system large problem elapsed time, then Scaleup = 1 which
throughput as expected from a single large machine. is linear.
In Parallel Databases, mainly there are four architectural designs for parallel DBMS. They are as follows: Sub-linear Scaleup –
Scaleup is sub-linear if large system large problem elapsed time is greater than small system small problem elapsed time, then
> Shared Memory Architecture > Shared Disk Architecture
the scaleup is sub-linear.
> Shared Nothing Architecture > Hierarchical
- Shared Memory Architecture - Shared Memory Architecture- In Shared Memory Architecture, there are multiple CPUs
that are attached to an interconnection network. They are able to share a single or global main memory and common disk + Distributed Database - A distributed database is a collection of multiple interconnected databases, which are spread
arrays. It is to be noted that, in this architecture, a single copy of a multi-threaded operating system and multithreaded physically across various locations that communicate via a computer network.
DBMS can support these multiple CPUs. Also, the shared memory is a solid coupled architecture in which multiple CPUs A distributed database is basically a database that is not limited to one system, it is spread over different sites, i.e, on
share their memory. It is also known as Symmetric multiprocessing (SMP). This architecture has a very wide range which multiple computers or over a network of computers. A distributed database system is located on various sites that don’t share
starts from personal workstations that support a few microprocessors in parallel via RISC. physical components. This may be required when a particular database needs to be accessed by various users globally. It
- Shared Disk Architecture - In Shared Disk Architecture, various CPUs are attached to an interconnection network. In needs to be managed such that for the users it looks like one single database.
this, each CPU has its own memory and all of them have access to the same disk. Also, note that here the memory is not Homogeneous Database –
shared among CPUs therefore each node has its own copy of the operating system and DBMS. Shared disk architecture is In a homogeneous database, all different sites store database identically. The operating system, database management
a loosely coupled architecture optimized for applications that are inherently centralized. They are also known as clusters. system, and the data structures used – all are the same at all sites. Hence, they’re easy to manage.
- Shared Nothing Architecture - Shared Nothing Architecture is multiple processor architecture in which each processor Heterogeneous Database:
has its own memory and disk storage. In this, multiple CPUs are attached to an interconnection network through a node. In a heterogeneous distributed database, different sites can use different schema and software that can lead to problems in
Also, note that no two CPUs can access the same disk area. In this architecture, no sharing of memory or disk resources is query processing and transactions. Also, a particular site might be completely unaware of the other sites. Different computers
done. It is also known as Massively parallel processing (MPP). may use a different operating system, different database application. They may even use different data models for the
- Hierarchical - A hierarchical database is a data model in which data is stored in the form of records and organized into database. Hence, translations are required for different sites to communicate.
a tree-like structure, or parent-child structure, in which one parent node can have many child nodes connected through links. Examples of distributed databases
As the name suggests, the hierarchical database model is most appropriate for use cases in which the main focus of Though there are many distributed databases to choose from, some examples of distributed databases include Apache Ignite,
information gathering is based on a concrete hierarchy, such as several individual employees reporting to a single Apache Cassandra, Apache HBase, Couchbase Server, Amazon Simple DB, Cluster point, and Foundation DB.
department at a company.
The schema for hierarchical databases is defined by its tree-like organization, in which there is typically a root “parent”
directory of data stored as records that links to various other subdirectory branches, and each subdirectory branch, or child Design Phases - there are four 4 phases.
record, may link to various other subdirectory branches. > Conceptual design - When every data requirement is stored and analyzed, the next thing that we need to do is creating a
conceptual database plan. Here, a highly leveled conceptual data model is used. This phase is called conceptual design.
When the conceptual design phase is in progress, the basic data modeling operations can be deployed to define the high-
+ Overview of the Design Process - Database design can be generally defined as a collection of tasks or processes that
level user operations that are noted during analysis of the functions.
enhance the designing, development, implementation, and maintenance of enterprise data management system. Designing > Logical Design - The logical phase of database design is also called the data modeling mapping phase. This phase gives
a proper database reduces the maintenance cost thereby improving data consistency and the cost-effective measures are us a result of relation schemas. The basis for these schemas is the ER or the Class Diagram.
greatly influenced in terms of disk storage space.
To create the relation schemas is mainly mechanical operation. There are rules for transferring the ER model or class
The main objectives behind database designing are to produce physical and logical design models of the proposed database
diagram to relation schemas.
system. To elaborate this, the logical model is primarily concentrated on the requirements of data and the considerations
> Normalization - Normalization is, in fact, the last piece of the logical design puzzle. The main purpose of normalization is
must be made in terms of monolithic considerations and hence the stored physical data must be stored independent of the
to remove superfluity and every other potential anomaly during the update.
physical conditions. On the other hand, the physical database design model includes a translation of the logical design model
Normalization in database design is a way to change the relation schema to reduce any superfluity. With every
of the database by keep control of physical media using hardware resources and software systems such as Database
normalization phase, a new table is added to the database.
Management System (DBMS).
> Physical Design - The last phase of database design is the physical design phase. In this phase, we implement the
database design. Here, a DBMS (Database Management System) must be chosen to use. For instance, different DBM
+ The Entity-Relationship Model - Entity relationship (ER) models are based on the real-world entities and their systems have different names for every datatype and they have different data types.
relationships. It is easy for the developers to understand the system by simply looking at the ER diagram. ER models are
normally represented by ER-diagrams.
ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data Basic Data types in SQL - Data types are used to represent the nature of the data that can be stored in the database table. For example,
elements and relationship for a specified system. in a particular column of a table, if we want to store a string type of data then we will have to declare a string data type of this column.
It develops a conceptual design for the database. It also develops a very simple and easy to design view of data. Data types mainly classified into three categories for every database.
>String Data types –
In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram.
1> Char data type - A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column
length in characters - can be from 0 to 255. Default is 1
+ Entity Set - is a collection or a group of ‘entities’ sharing exactly the ‘same set of attributes. All entities can be distinctly 2> VARCHAR (size) - A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the
identified in an entity set. This is because all the entities have a different set of value for some set of attributes. We further maximum column length in characters - can be from 0 to 65535
classify the entity set into two basic categories Strong and Weak entity set. 3> BINARY (size) - Equal to CHAR (), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1
The collection of entity sets and their relationship sets together forms a database. In this section, we will discuss the entity 4> VARBINARY (size) - Equal to VARCHAR (), but stores binary byte strings. The size parameter specifies the maximum column length in
set and we will also learn how an entity set is different from an entity? Further, we will discuss the classification of the bytes.
entity set. >Numeric Data types –
1> BIT (size) - A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The
default value for size is 1.
+ Relationship Sets - There are three different types of degree of relationships, they are as follows −
2> TINYINT (size) - A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies
> Unary relationship >Binary relationship >Ternary relationship the maximum display width (which is 255).
> Unary relationship - It is the relationship between the instances of a single entity type. It is also called a recursive 3> SMALLINT (size) - A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter
relationship. specifies the maximum display width (which is 255).
Example - A person is married is a one-to-one relationship between the instances of Person entity type of unary 4> MEDIUMINT (size) - A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215.
relationship. The size parameter specifies the maximum display width (which is 255).
> Binary relationship - It is the relationship between the instances of two different entity types. Two entities will participate >Date and time Data types –
in the relationship. 1> DATE - A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'
Example - Person and events are two different entity types which are related by using the relationship called “attends”. 2> DATETIME (fsp) - A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-
>Ternary relationship - A simultaneous relationship between the instances of three entity types with unique attributes is 01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic
called a ternary relationship. initialization and updating to the current date and time.
Example - Consultant, client and contract are three different entities with different attributes. 3> YEAR - A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000.
>

MySQL 8.0 does not support year in two-digit format.

 Types of SQL Commands (DDL, DML, DCL, DQL, TCL) – DML – Data Manipulation Language -
DDL – Data Definition Language - The SQL commands that deal with the manipulation of data present in the database belong to DML or Data Manipulation Language and
DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals this includes most of the SQL statements. It is the component of the SQL statement that controls access to data and to the database.
with descriptions of the database schema and is used to create and modify the structure of database objects in the database. DDL is a set Basically, DCL statements are grouped with DML statements.
of SQL commands used to create, modify, and delete database structures but not data. These commands are normally not used by a List of DML commands:
general user, who should be accessing the database via an application. INSERT : It is used to insert data into a table.
List of DDL commands: UPDATE: It is used to update existing data within a table.
CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers). DELETE : It is used to delete records from a database table.
DROP: This command is used to delete objects from the database. LOCK: Table control concurrency.
ALTER: This is used to alter the structure of the database.
RUNCATE: This is used to remove all records from a table, including all spaces allocated for threcords are removed. DCL – Data Control Language –
COMMENT: This is used to add comments to the data dictionary. DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database
RENAME: This is used to rename an object existing in the database. system.
List of DCL commands:
DQl – Data Query Language – GRANT: This command gives users access privileges to the database.
DQL statements are used for performing queries on the data within schema objects. The purpose of the DQL Command is to get some REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.
schema relation based on the query passed to it. We can define DQL as follows it is a component of SQL statement that allows getting
data from the database and imposing order upon it. It includes the SELECT statement. This command allows getting the data out of the TCL – Transaction Control Language
database to perform operations with it. When a SELECT is fired against a table or tables the result is compiled into a further temporary Though many resources claim there to be another category of SQL clauses TCL – Transaction Control Language. So we will see in detail
table, which is displayed or perhaps received by the program i.e., a front-end. about TCL as well. TCL commands deal with the transaction within the database.
SELECT: It is used to retrieve data from the database. List of TCL commands:
COMMIT: Commits a Transaction.
ROLLBACK: Rollbacks a transaction in case of any error occurs.
SAVEPOINT:Sets a savepoint within a transaction.
WHERE Clause - The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by
SET TRANSACTION: Specify characteristics for the transaction.
joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should
use the WHERE clause to filter the records and fetching only the necessary records.
The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which
we would examine in the subsequent chapters. Working with Views - Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database.
Syntax: SELECT column1, column2, column We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table
FROM table_name or specific rows based on certain condition.
WHERE [condition] Creating View on Tables - Database views are created using the CREATE VIEW statement. Views can be created from a single table,
multiple tables or another view.
To create a view, a user must have the appropriate system privilege according to the specific implementation.
DISTINCT Clause - SQL DISTINCT clause is used to remove the duplicates columns from the result set.
SYNTAX: -
The distinct keyword is used with select keyword in conjunction. It is helpful when we avoid duplicate values present in the CREATE VIEW view_name AS
specific columns/tables. The unique values are fetched when we use the distinct keyword. SELECT column1, column2.....
The distinct keyword is used in conjunction with select keyword. It is helpful when there is a need of avoiding duplicate values FROM table_name
present in any specific columns/table. When we use distinct keyword only the unique values are fetched. WHERE [condition];
Syntax: SELECT DISTINCT column1, column2
FROM table_name Updating Views - The SQL UPDATE VIEW command can be used to modify the data of a view.
All views are not updatable. So, UPDATE command is not applicable to all views. An updatable view is one which allows
Using Column Aliases - SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often performing a UPDATE command on itself without affecting any other table.
used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS
keyword.
Aliases are the temporary names given to table or column for the purpose of a particular SQL query. It is used when name of
column or table is used other than their original names, but the modified name is only temporary.

SQL Functions – There are two types of SQL Function

1> Single row function 2> Multi row function

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:
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.

Character Functions:
SQL provides a rich set of character functions that allow you to get information about strings and modify the
contents of those strings. in multiple ways. Character functions are of the following two types:
1. Case-Manipulative Functions (LOWER, UPPER and INITCAP)
2. Character-Manipulative Functions (CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM and REPLACE)