RELATIONAL DATABASE
Storing and manipulating records
DATABASE
May be defined as a collection of
interrelated data stored together to serve
multiple application
It is computer based record keeping system.
It not only allows to storebut
also allows us modification of data as
per requirements.
DBMS
A DBMS refers to Database Management
System
It is a software that is responsible for storing,
manipulating, maintaining and utilizing
database.
A database along with the a DBMS is referred
to as a database system.
There are various DBMS software available in
the market like :-
Oracle, MS SQL Server, MySQL, Sybase,
PostgreSQL, SQLite
PURPOSE OF
DBMS
RELATIONAL DATABASE MODEL
In relational database model data is
organized into table (i.e. rows and columns).
These tables are also known as relations.
A row in a table represent relationship among
a set of values.
A column represent the field/attributes
related to
relation under which information will be stored.
For example if we want to store details
of students then : Roll, Name, Class, Section,
etc. will be the column/attributes and the
collection of all the column information will
become a Row/Record
KEY
S
Alternate Key
In case of multiple candidate keys, one of them
will be selected as Primary Key and rest of the
column will serve as Alternate Key
A Candidate Key which is not a primary key is an
Alternate Key.
Foreign key
Used to create relationship between two tables.
It is a non-key attribute whose value is derived
from
the Primary key of another table.
Foreign key column will for the value in Primary
key of another table, if present then entry will be
allowed otherwise data will be rejected.
Primary Key column table from where values will
be derived is known as Primary Table or Master
Table or Parent Table and Foreign key column
table will be Foreign Table or Detail Table or
T H E R E L AT I O N A L MO
DELTERMINOLOGIES
Relation • relation is a table that is data arranged in rows and columns
Domain • domain is a pool of values from which the actual values appearing in
a given column are drawn
Tuple • the rows of tables
Attribut • the columns of tables
es • the number of attributes in a relation
Degree • the number of rows in a
relation
Cardinal
Primary
key
• Primary key is a set of one or more attributes that can
uniquely identify tuples within the relation
• When a primary key is made-up of two or more attributes it is
called composite primary key
Candidate
key
• All attributes combinations inside a relation that can serve as
KEY primary key or candidate keys as they are candidates for
the primary key post
S Alternate key
•A candidate key that is not the primary key is called as
alternate key
Foreign key
•A non key attribute whose values are derived from the
primary key of some other table
BRIEF HISTORY OF MYSQL
MySQL is freely available open source RDBMS
Can be downloaded from [Link]
In MySQL information is stored in Tables.
Provides features that support secure
environment for storing, maintaining and
accessing data.
It is fast, reliable, scalable alternative to many of
the commercial RDBMS today.
Create and supported by MySQL AB, a
company based in Sweden. This company is now
subsidiary of Sun Microsystems. On April
2009 Oracle Corp. acquires Sun Microsystems.
The chief inventor of MySQL was Michael
Widenius(a.k.a Monty). MySQL has been named
after Monty’s daughter My. The logo of MySQL is
MYSQL DATABASE SYSTEM
MySQL database system refers to the
combination of a MySQL server instance
and MySQL database.
It operates using Client/Server architecture
in which the server runs on the machine
containing the database and client connects to
server over a network
MySQL is a multiuser database system, meaning
several users can access the database
MYSQL DATABASE SYSTEM
The Client
Are the programs that connect to MySQL server and
sends requests to the server and receives the
response of Server.
Client may be the MySQL prompt or it may be Front-end
programming which connect to server programmatically
like connecting to MySQL using Python Language or
Java or any other language
Listens for client requests and access
database contents and send response
to the client.
FEATURES OF MYSQL
Speed
MySQL runs very fast.
Ease of Use
Can be managed from command line or GUI
Cost
Is available free of cost. It is Open Source
Query language Support
Supports SQL
Portability
Can be run on any platform and supported by
various
Compilers
Data Types
Supports various data types like Numbers, Char
etc.
FEATURES OF MYSQL
Security
Offers privileges and password systems that is very flexible and
secure.
Scalability and Limits
Can handle large databases. Some of real life MySQL databases
contains millions of records.
Connectivity
Clients can connect to MySQL using several protocols
Localization
The server can provide error message to client in many language
Client and Tools
Provides several client and utility programs. Like mysqldump
and mysqladmin. GUI tools like MySQL Administration and Query
Enter the
STARTING password
MYSQL given
during
installatio
n
Click on Start All Programs MySQL
MySQL Server MySQL Command Line
Client
mysql> prompt
means now MySQL
is ready to take
your command
and execute
To exit from MySQL type exit or quit in
front of mysql prompt
SQL AND MYSQL
SQL stands for Structured Query Language.
Is a language that enables you to create and
operate on relational databases.
MySQL uses SQL in order to access
databases.
It is the standard language used by almost all
the database s/w vendors.
Pronounced as SEQUEL
Original version was developed by
IBM’s
Almanden Research Center
Latest ISO standard of SQL was released in
2008 and named as SQL:2008
MYSQL AND SQ
L
The SQL is a language
SQL stands for
that enables you to
structured query
create and operate on
language and it is a set
relational databases,
of commands that is
which are set of related
recognized by nearly all
information stored in
rdbmss.
tables.
PROCESSING CAPABILITIES OF SQL
DDL (Data Definition Language)
DML (Data Manipulation
Language)
Embedded DML
View
Authorization
Integrity
Transaction Control
PROCESSINGCAPABILITIES OFSQ
L
Data Interactive embedded View Authorization Integrity transacti
Data data definition on
definitio
manipulatio manipulat control
n n language ion
languag language
e
provides includes a query embedded form of SQL DDL also it includes comments it provides forms includes
comments for language SQL includes for of commands for
defining based on both is a designed for commands for specifying access integrity checking specifying the
relations, relational use defining rights to beginning
deleting algebra and the within general views which are relations and views and ending of
relations, tuple purpose tables. transactions
creating relational programming along with
indexes and calculus. languages like commands
modifying COBOL, Pascal, C,
C++, etc
relations.
C L A S S I F I C AT I O N O F S Q L S TAT E M E N T S
SQL provides many different types of commands used for
different purposes.
Data Transaction
Data
manipulatio control Session
definition
n language language control
language
(DML) (TCL) commands
(DDL)
commands commands
commands
D ATA D E F I N I T I O N COMMA
NDS
• The DDL commands allows you to perform task related
to the data definition.
Create, alter, and drop Grant and
Maintenance
schema objects revoke
commands
privileges and
roles
DATA DEFINITION LANGUAGE
It allows to create database objects like
creating a table, view or any other database
objects.
The information about created objects are
stored in special file called DATA DICTIONARY
DATA DICTIONARY contains metadata i.e.
data about data.
While creating a table DDL allows to specify –
name of table, attributes, data types of
each attribute, may define range of values
that attributes can store, etc
Major commands of DDL are – CREATE,
ALTER, DROP,TRUNCATE,RENAME
In DBMS, a data dictionary is a
centralized repository that stores
metadata, or data about the data,
including definitions, data types,
relationships, and constraints within
a database, ensuring data
consistency and facilitating
database management.
D ATA M A N I P U L AT I O N C O M M A N D
S
DML commands are used
A DML is a language
to manipulate the data,
that enables users to
DMS are basically of for example inserting a
access or manipulate
two types: tuple into a table
data as organized by
modifying a tuple in a
the appropriate data
table deleting a tuple in
model.
a table.
non procedural DMLs
procedural DMLs which
user 2 specify what data
are used to specify
is needed without
what data is needed
specifying how to get it
and how to get it.
DATA MANIPULATION LANGUAGE
It allows to perform following operation
on table
Retrieval of information stored in table
Insertion of new data in table
Modification of existing data in table
Deletion of existing data from table
DML is of 2 type
Procedural DML (in this we specify what data
is needed and how to get it)
Non-Procedural DML (in
this we specify what data is needed
without specifying how to get it)
Main DML commands
are – SELECT, INSERT, UPDATE
AND DELETE
TRANSACTION CONTROL
COMMANDS
• A transaction is one complete unit of work, for example, preparing
report card for a student is a transaction but it involves many steps
like open students table read data from it, open marks table read
data from it, calculate percentage and grade write the computed
result to result file and finally close these three tables.
• Some TCL commands are:
oCOMMIT - make all the changes made by statements issued,
permanent
oROLLBACK - endos all changes since the beginning of a transaction
oSAVE POINT - marks a point up to which all earlier statements have
been successfully completed
oSET TRANSACTION - establishes properties for the current
transactions.
JUST A MINUTE…
What is Database? What are the advantages
of Database System?
What is DDL and DML? Give examples of
command belonging to each category
What is the difference between Primary key
and Candidate key
What is Primary Key? What are the restriction
imposed by Primary Key? How many
primary key can be applied on a Table?
What is Degree and Cardinality of table?