狠狠撸

飩� Database - Collected form of data
飩� Database (management) system - A computer based record
keeping system.
飩� Database softwares examples:
飩� MySQL (open Source)
飩� ORACLE Database
飩� MS SQL Server
飩� SQLite (open Source)
飩� MariaDB
飩� PostgreSQL (open Source)
INTRODUCTION

鈥揇BMS is a software whose purpose is to store
databases, maintaining databases and using
databases.
鈥揑ts prime purpose is to perform operations on
databases and to provide data when required.
鈥揇BMS reduces Data Redundancy.
鈥揑t improves data security.
-Inconsistency can be avoided.
鈥� it stores data in organized and Integrated form.
鈥揇ata remains error free.
鈥揇atabase follows a standard.
AIM OF DBMS

飪楧ata remains in the form of tables
Table : combination of rows and columns which
is also known as Relation .
Imagine a database has three tables- Suppliers,
Items and Shipments :
Suppliers (SuppNo., Supp_name, Status, City)
Items (ItemNo., Item_name, Price)
Shipments (SuppNo., ItemNo., Qty_Supplied
Relational Database Model

Relational Model Terminology
鈥揇omain: The pool of values for a column
鈥揟uple : Rows of a table
鈥揂ttribute : Columns of a table
鈥揇egree : Number of attributes or columns
鈥揅ardinality : Number of tuples or rows

Referential Integrity
Referential Integrity: A system of rules which is used by a
DBMS to ensure that there is a valid relationship between
related tables or not.
The rules are:
1. You can't delete a record from a primary table if matching records
exist in a related table.
2. You can't change a primary key value in the primary table if that
record has related records.
3. You can't enter a value in the foreign key field of the related table
that doesn't exist in the primary key of the primary table.
4. However, you can enter a Null value in the foreign key, specifying
that the records are unrelated..

MySQL
鈥揗ySQL is an open source RDBMS which makes use of
SQL for ADDING, DELETING and MODIFYING data from
data bases.
鈥揗ySQL was developed by MySQL AB company which is
now a part of Sun Microsystems.
鈥� SERVER : which responds to the requests of clients.
鈥揅LIENTS : these are the programs which are attached to
database server and send requests to server.

SQL(structured Query Language)
飪糏n order to access data within the MySQL database, all
programmers and users must use SQL.
飪糞QL is the set of commands that is recognized by all
RDBMS.
飪糀ll RDBMS like Mysql,Ms Access,Oracle,and SQL server
use sql as a standard database language

Classification of SQL statements

Using Database
鈥ollowing command is used to use a Database mysql>
USE <database name >;
For ex -
mysql> USE school;
A message will come saying- 鈥渄atabase changed鈥�
See the Commands
carefully

Table Creation
鈥o create a table in Database, following command is used-
mysql> CREATE TABLE <Table Name> (<Col1> <DataType(Size)>,
<Col2><DataType(size)>, .
. . );
For ex-
mysql>create table student (Roll INT(4) Primary Key, Name CHAR(20),
(Age INT(2), City CHAR(10) ) ;
A message will come saying- 鈥淨uery OK鈥�
Primary key restrict a column to have unique values
only.

Insertion of a record in Table
Syntax to insert a record in a Table is-
mysql> INSERT INTO <TableName> (<Col1> <Col2> <Col3> <Col4>
VALUES (<val1>,<val2>,<val3>,<val4>,. . .);
We can change the order of columns as-
Here, we can insert values without specifying column names provided the order of
values for columns should be same as in table.

Dropping a Table
鈥� To drop a table in Database, following command
is
mysql> DROP Table <Table Name>;
For ex -
mysql>drop table <Student>
A message will come saying- 鈥淨uery OK鈥� now if you want to
see the structure of the table you cant see because it has
already been deleted.

Modification in Table structure
鈥� To modify structure of a table in Database,
following command is used-
mysql>ALTER TABLE <Table name> ADD/MODIFY(<Col>
<type(size)>, . . . .)
For ex-mysql> Alter Table Student Add (class INT(2));
A message comes saying 鈥淨uery OK鈥� .
Again run the DESC command-
鈥� A new column has been add.
鈥� Columns can be added.
鈥� Column size can be changed.

Accessing a Table
Here * means all
columns and without
condition it will displays
all records.
Here only those records will
display where city is
Barabanki.
Syntax to access Data from a table is-
mysql> SELECT <Col Names> FROM <Table Name>
WHERE <Condition>

Here Name and class of only
those records are displayed
which are not from
Barabanki.
Here columns have been
rearranged.
Accessing a Table
Syntax to access Data from a table is-
mysql> SELECT <Col Names> FROM <Table Name>
WHERE <Condition>

Updating a record in Table
Syntax to update a record in a Table is-
mysql> UPDATE <TableName> SET <ColName>=<NewValue>
WHERE
<Condition>
In this table, age of
meera is to be set 6. and
city of roll 1004 and 1005
is to be set as Lucknow.

Deletion of a record from a Table
鈥� Syntax to delete a record from a Table is-
mysql>DELETE FROM<TableName>WHERE <Condition>
To delete all records from a table, following command will be used-
Viewing records
after deletion.

Distinct keyword
l cities in the table.
Viewing Tables in a Database
Displays all tables in a Databse.

Table from another Table
See the example carefully
Syntax for creation of a table from another table is -
mysql>CREATE TABLE <TableName>
AS (SELECT <Cols> FROM <ExistingTable>
WHERE <Condition>);

Pattern Matching
With 鈥榣ike鈥� two symbols are to
be used 鈥�%鈥� and 鈥榑鈥�.
鈥�%鈥檙epresent multiple
characters whereas 鈥榑鈥�
represents one charachetr .
In above example all the names starting with 鈥楽鈥� are
shown.
In example given below all the names having 鈥榰鈥� as
second character are shown.

Other SQL Commands
鈥� Select * from Student where city in (鈥楯aipur鈥�,鈥橝jmer鈥�);
鈥� Select * from Student where city Not in(鈥楯aipur鈥�,鈥橝jmer鈥�);
鈥� Select * from Student where age between 5 and 7;
鈥� Select * from Student Order by name DESC ;
鈥� Select 5 * 6 from DUAL ;

AGGREGATE FUNCTIONS
Aggregation is an operation that computes a single value
from all the values of an attribute.
SQL provides five functions that apply to an attribute of a
relation and produce some aggregatation of that column
-SUM: computes the sum of values in a column.
-AVG: Computes the average of value in an attribute.
-MIN/MAX: Computes the min/max value in an attribute.
-COUNT: Computes the number of values in an
attribute(including duplicates unless they are explicitly
eliminated with DISTINCT)

JOINS
鈥� Join is a query which combine rows of two or more
tables.
鈥� In a join-query, we need to provide a list of tables in
FROM Clause.
鈥� The process of combining multiple tables in order to
retrieve data is called joining. For ex-
SELECT * FROM emp1, dept;
鈥� Unrestricted join or Cartesian product of both the
tables gives all possible concatenations of all the rows
of both the tables.

EQUI JOIN AND NATURAL JOIN
To get the details about the departments and their in- charges,
query will be-
mysql> SELECT name, deptname from emp1, dept
where emp1.empcode=dept.deptic;
When both the tables have same field name, then to show a
field from particular table, use the following pattern to access a
field- <Table name>.<Field Name>
Ex- emp1.empcode
This is an example of Equi-
Join, in which columns are
compared for equality and it
is also an example of
Natural- Join, in which only
one of the identical columns
exists.

INNER JOIN
Inner join only takes that rows
from Cartesian Product Table
that satisfy the join condition.

LEFT JOIN
鈥� When we use LEFT-JOIN, it
returns all rows from first
table whether it has
matching rows in second
table or not.
鈥� It shows NULL in columns
for the unmatched rows of
first table.
mysql>SELECT <Col List>
FROM <table1> LEFT JOIN
<table2>
ON <joining

RIGHT JOIN
鈥� When we use RIGHT-JOIN, it
returns all rows from second
table whether it has matching
rows in first table or not.
鈥� It shows NULL in columns for
the unmatched rows of second
table.
mysql>SELECT <Col List> FROM
<table1> RIGHT JOIN <table2>
ON <joining
Condition>

Data Base Management 1 Database Management.pptx

狠狠撸

Data Base Management 1 Database Management.pptx

More Related Content

Similar to Data Base Management 1 Database Management.pptx (20)

More from PreeTVithule1 (7)

Recently uploaded (20)

Data Base Management 1 Database Management.pptx