Transcript here - The Smartpath Information Systems

BASIC RDBMS CONCEPTS
Prepared by
The Smartpath Information Systems
www.thesmartpath.in
Index
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
what is RDBMS
Database concepts
Explanation
Database three tier architecture
Relational Model
E-R Diagram
E-R Diagram (Explanation)
E-R Diagram (Explanation)
Rules for RDBMS
Keys in RDBMS
Keys in RDBMS(continue)
SQL in RDBMS
Basic Datatypes in SQL
Index
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
Operators in SQL - Arithmetic
Operators in SQL - Comparison
Operators in SQL - Logical
Operator with Examples - comparison operator
Operator with Examples - LIKE operator
Operator with Examples - AND operator
Operator with Examples - IN operator
RDBMS Databases - MYSQL
RDBMS Databases - MS SQL Server
RDBMS Databases - Oracle
What is RDBMS
RDBMS - Relational Database Management System
It is database management system based on relational model , which is
used to manage relational database. Relational model is organization of
data in tables which are interrelated.
Relational Database
It is organized collection of tables. Data is stored in tables. Tables are
related to each other using one or more fields.
.
Database concepts
Table
A table is collection of rows and columns. In RDBMS table is called Relation
and rows are called as tuple.
Record
A single row in a table is called record. It is also referred as tuple. It is a
collection of fields.
Column
It is collection of field values of same type.
Field
It contains data. The smallest entity of table is field.
Explanation
For Example
table STUDENT
student_id
Student_name
marks
1101
abc
80
Row
1101
column
Student_id
1101
1102
Abc
80
Database three tier architecture
Relational model
In relational model , each table is related to every other table in database.
There is one common field in tables which is used to relate them. Dept_no
field that relates department table and professor table.
E-R Diagram
The Entity Relationship Diagram
Explanation
Entity
An Entity represents a real world thing. For example student and class are
entities. In E-R diagram rectangle shape is used to represent entity.
Student
Attribute
The characteristics of an entity are called attributes. Student is entity . It has
name , id , course he/she opts for are attributes.
first_name
Explanation
Relationship
It describes how two entities are related to each other
Enrolls
Student
in Class
key attribute
It is the main characteristic of an entity. It is used to relate tables
student_name
Student
student_id
key attribute
Rules for RDBMS
Codd's twelve rules are a set of thirteen rules (numbered zero to twelve)
proposed by Edgar F. Codd, a pioneer of the relational model for databases,
designed to define what is required from a database management system in
order for it to be considered relational, i.e., a relational database management
system (RDBMS)They are sometimes referred to as "Codd's Twelve
Commandments".
A DBMS is said to be Relational Database management If it follows Codd’s
rules. Practically all rules are not followed , but RDBMS follows maximum
of he Codd’s rules
Keys in RDBMS
Primary Key
An attribute that is used to access the table data is called primary key. It must
Contain Unique values. It uniquely identifies a record in a table.
Candidate Key
It is set of attributes from which primary key is selected. One of them is made as
Primary key and rest are candidate keys. They are candidate for primary key.
It uniquely identifies a record.
Composite key
When more than one attributes are used to uniquely identify a record in a table
It becomes composite key.
Keys in RDBMS
Super Key
It is a set of attributes that uniquely identifies each record in a table. It is a
superset of candidate key.
Secondary Key
The candidate keys which are not selected as primary key , are called as
secondary key or alternate key.
SQL in RDBMS
Structure Query Language(SQL) is a programming language used for storing
and managing data in RDBMS. SQL was the first commercial language
introduced for E.F Codd's Relational model It is used to perform all types
of database operations. Almost all database use SQL as standard language
for manipulation of data. It is case sensitive language.
SQL is set of three data languages. These are
DDL - Data Definition Language. It is set of commands for defining table in
database
DCL - Data Control Language. It is set of commands used for controlling
access to database
DML - Data Manipulation Language. It is set of commands used to manipulate
table data.
Basic Data types in SQL
Char
Takes Fixed length string
1 byte to 1000 bytes
Varchar
Takes variable length string
1 byte to 4000 bytes
Varchar2
Takes variable length string
1 byte to 4000 bytes
Number
It stores fixed and floating point
numbers
up to 38 digit
precision
Date
It stores date values in dd-mm-yy
format
as required
Boolean
It takes true or false value
Only two values
T or F
Operators in SQL - Arithmetic
Operators in SQL – comparison
> Greater than
Checks if the value of left
operand is greater than the
value of right operand
(a > b)
< Less than
Checks if the value of left
operand is less than the value
of right operand
(a < b)
<> Not equal to
Checks if are equal or not.
(a <> b)
= Equal to
Checks if the values of two
operands are equal.
(a = b)
Operators in SQL - logical
ALL
The ALL operator is used to compare a value to all values in
another value set.
ANY
The ANY operator is used to compare a value to any applicable
value in the list according to the condition.
BETWEEN
The BETWEEN operator is used to search for values that are
within a set of values, given the minimum value and the
maximum value.
IN
The IN operator is used to compare a value to a list of literal
values that have been specified.
LIKE
The LIKE operator is used to compare a value to similar values
using wildcard operators.
Operators with examples
Table student
Student id
Student
name
course
marks
1101
John
oca
80
1102
Rohit
scjp
85
1103
Ramesh
mca
82
comparison operator <=
SELECT student_name , stu_id from student where marks >= 85
output –
David
1102
Operators with examples
Like operator
SELECT * from student where student_name LIKE ‘r %’ ;
* means all fields.
output – 2 rows in which names begin with letter ‘r’
1102
Rohit
scjp
85
1103
Ramesh
mca
82
Operators with examples
AND operator – It is used to join two or more conditions. Output are the rows
That satisfy all conditions given in statement
SELECT student_id from student where marks > 80 AND student_name
LIKE ‘r%’ ;
output – we have given only one field to select i.e... Student id. Those student
ids who have name begin with ‘r’ and marks greater than 80.
1102
1103
Operators with examples
IN operator
SELECT course from student where name IN (‘ramesh’ , ‘rohit’ );
It searches rows and takes only those values of course in which name is
ramesh and rohit.
Output
SCJP
MCA
Equivalently : SELECT course from student where name = ‘ramesh OR
name = ‘rohit’;
For multiple OR conditions we can use IN operator.
RDBMS Databases
The following Databases are Relational Database Management System
MySQL
MySQL is an open source SQL database, which is developed by Swedish
company MySQL AB. MySQL supports many different platforms including
Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X.
MySQL has free and paid versions , depending on its usage (noncommercial/commercial) and features. MySQL comes with a very fast,
multi-threaded, multi-user, and robust SQL database server.
RDBMS Databases
MS SQL Server
Microsoft SQL Server is a relational database management system
developed by Microsoft. As a database server, it is a software product
with the primary function of storing and retrieving data as requested by
other software applications which may run either on the same computer
or on another computer across a network (including the Internet).
MS SQL Server is a Relational Database Management System developed by
Microsoft Inc. Its primary query languages are:
T-SQL.
ANSI SQL.
RDBMS Databases
ORACLE
It is a very large and multi-user database management system. Oracle is a
relational database management system developed by 'Oracle Corporation'.
Oracle works to efficiently manage its resource, a database of information,
among the multiple clients requesting and sending data in the network.
It is an excellent database server choice for client/server computing. Oracle
supports all major operating systems for both clients and servers, including
MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors.