Transcript SQL

DATABASE CONCEPTS
Definition: Database:- A database is a collection of interrelated data
stored together to serve multiple applications;
 Database Management System (DBMS):-Database
management system is basically record computer based
record keeping system.
 Advantage of DBMS:- Database systems help:1. Reduce Data Redundancy,
2. Controlled Data Inconsistency,
3. Facilitate To Sharing Of Data,
4. Standardization Of Data,
5. Data Security.
6. Integrated Data.
Database Security:
Data Security refers to protection of data against
accidental or intentional discloser to unauthorized
persons, or unauthorized modification or
destruction.
Database Privacy:
Privacy of data refers to the rights of individuals
and organizations to determine for themselves
when, how, and to what extent information about
them is to be transmitted to others.
DATA ABSTRACTION
Definition:- A good database system
ensures easy, smooth and efficient data
structures in such a way so that every type
of database user:
End users (Computer untrained users, at
view level ),
Application system (at logical level),
Storage system analyst (at internal level or
physical implementation ),
is able to access its desired information
efficiently
Data Abstraction Type
(Various levels of database
Implementation )
There are three levels of data abstraction:-
1. Internal Level (Physical level)
2. Conceptual level
3. External level (View level)

Internal level (Physical Level):- The lowest level of data
abstraction, the internal level, is the one closet to physical
storage. It describes:
How the data are actually stored in storage device or medium?
What will be the storage technique?

What will be the starting address of the database?


Conceptual Level:- It is next higher level of internal level.
In this level data abstraction describe:



What data are actually stored in the database?
What all the constitute the database?
What are the relationships between the data entities?
External level (View Level):- This is the level closet to the
users and is concerned with the way in which the data are
viewed by individual users . It describes:
What is the way of viewing information to the concerned user?
Three levels of Data Abstraction Diagrams
View 1
View 2
View 3
External Level
External Level
Conceptual
Level
Conceptual Level
Internal Level
Physical Level
....
View --- n
Fig. Illustration of various level with example
Teacher
School Principal
External level
(Individual User Views)
View 1
View 2
School_record
Student_detail
(Application programs are used
to fetch the desired information)
Conceptual
Roll_No
Name
Class
Grade
Address
Number(3) Not Null
Char(10)
Number(3)
Char(2)
Vachar2(10)
Internal
Student_record
Roll_No
Name
Class
Grade
Address
Length=40
Type= Byte(6),
Type= Byte(10),
Type= Byte(6),
Type= Byte(2),
Type= Byte(10),
offset=0,index=Ix
offset=0
offset=0
offset=0
offset=0
Data Independence
The ability to modify a scheme definition in one level
without affecting a scheme definition in the next
higher level is called Data Independence.
There are two levels of data independence:
1.
Physical Data Independence:- It is refers to ability to modify the
scheme followed at the physical level without affecting the scheme
followed by the conceptual levels.
2.
Logical Data Independence:- It is refers to ability to modify the
conceptual scheme without causing any changes in the scheme
followed at the view levels.
Note:•
It is more difficult to achieve logical data independence rather than the
physical level data independence.
•
The abstract data types in modern programming language implement
concept of data independence to large extent.
DATA MODELS
Model refers to the representation way of data or
information in the database management system.
The three data models that are used for
database management are: Relational data model.
 Hierarchical data model.
 Network data model.
Relational data model
 Relational data model:- The relational represents
data and relationships among data by a collection of tables known as
relations
Roll_No#
Student_Name
Class
Grade
110001
Shyam Sunder
11
A
110002
Krishna
12
A+
 Network data model:- Network Data model is
represented by collection of records and relationships among data
are represented by link.
 Hierarchical data model:-The hierarchical model is
similar to the network model in the sense that data and relationships
among data are represented by records and links respectively. It is
differs from the network model in that the records are organized as
collections of trees rather than arbitrary graphs.
Relational Database Terminology
 The relational model was propounded by E.F. Codd of IBM and has
since been acknowledged as a very important concept in DBMS
technology.
 The relational model has established itself as the primary data model
for commercial data processing application.
 Relation:- A Relation is a table i.e. data arranged in rows and columns.
 Domain:- A Domain is pool of values from which the actual values
appearing in given column are drawn. Exp. Roll_No# , Student_Name
etc.
 Tuple:-The rows of tables (relations) are generally referred to as Tuple.
 Attributes:-The columns of tables (relations ) are generally referred to
as attributes.
 Degree:- The number of columns (attributes ) in a relation determine
the degree of a relation.
 Cardinality:- The number of tuples (rows) in a relation is called the
cardinality of the relation
Database Keys
It is important to be able to specify how rows in a
relation are distinguished conceptually, rows are
distinct from one another.
Database perform following keys:1.
2.
3.
4.
5.
Primary Key
Candidate Key
Alternate Key
Foreign Key
Referential Integrity
Definition of Keys
 Primary Key:- A primary key is a set of one or more attributes
that can uniquely identify tuples (rows) within the relation/table.
For example, Roll_no# is a primary key of “student_rec” table.
 Candidate Key:- All attributes combines inside a relation that
can serve as a primary key are Candidate Keys as they are
candidates for the primary key position.
 Alternative Key:- A candidate key that is not the primary key is
called an alternative key. For example, student_name is a
alternative key in “student_rec” table.
 Foreign Key:- A non key attribute, whose values are derived
from the primary key of some other table, is known as Foreign
Key in its current table.
 Referential Integrity:-Referential Integrity is a system of rules
that a DBMS uses to ensure that relationships between records
in related tables are valid, and that users don’t accidentally
delete or change related data.
View:- View is a (virtual) table that does not really
exist in its own right but it is instead derived from
one or more underlying base table(s).
Views are like windows through which you view
desired information that is actually stored in a base
table.
Normalization:Normalization is the process of transformation of the
conceptual schema (logical data structures) of the
database into a computer representable from.
In other words, the normalization process helps in
attaining good database design thereby avoiding
undesirable things like repetition of information,
inability to represent information, loss of
information etc.
Type of Normalization
 First Normalization:-
A relation R is in first
normal form(1NF) if and only if all underlying
domains of the relation contain atomic (indivisible )
values.
 I-NF perform following:1. Removing all repeating groups form the relation.
2. Decompose non-atomic attributes to atomic attributes.
3. All key attributes defined and all attributes depends on
primary key.
Fig.—
 Second Normal Form (2NF):-A relation
R is in Second Normal Form(2NF) if and only
if it is 1NF and every non key attribute is fully
dependent on the primary key.
2NF perform following:1. In I-NF. and Includes no partial dependencies.
2. Still possible to exhibit transitive dependencies.
Fig.—
 Third Normal Form (3NF):-A relation R is
in Third Normal Form(3NF) if and only if it
is 2NF and every non key attribute is non
transitively dependent upon the primary
key. 2NF perform following: In 2-NF and Contains no transitive dependencies
Boyce - Codd Normal Form (BCNF):-A relation R is
in Boyce - Codd Normal Form(2NF) if and only if it is
3NF and all of its determinants are candidate keys .
BCNF perform following:•In 3NF.
•Every determinant in the table is a
candidate key.
Advantage of
Normalization
 It reduces data redundancies.
 It help eliminate data anomalies.
 It produces controlled redundancies to link
tables.
Need of Normalization:- Normalization is
needs for : Most databases to grow by adding new attributes
and new relations.
 For improving a efficiency of database.
 Minimizing the need for rewriting the application
programs (Front end)
Front End and Back End:
Front End:- A front end refers to the client side end i.e. the
end at which request is made. Some popular front end
software are:





Visual Basic (VB)
ASP (Active Server Page)
Visual C++
Power builder
MS-Access
Back End:-A Back End refers to the server side, where the
client requests are processed. Some popular back end
software are:



SQL Server.
Oracle
Sybase
My SQL etc.
Oracle
Many DBMSs available in the market that are
capable to storing and maintaining the
database of an enterprise. But Oracle is one
of the most popular DBMS’ s that is being
used by millions of organizations across the
world.
Oracle falls under the category of Relational
Database Management System (RDBMS).
History of Oracle:- The RDBMS Oracle was
developed by a company called Relational
Software Incorporation (RSI), which was formed
in year 1977 by Larry Ellison.
Version of Oracle
Year
Product Status
Product Name
1979
1st Commercial Product
SQL RDBMS
1983
VAX-Mode Database
Oracle (In this duration company name changed to
Oracle Corporation)
1986
Client-Server database
Oracle with Server Database
1988
1st PL/SQL
Procedural Language with structural query
language
1995
1st 64-Bit RDBMS
Oracle7
1997
1st Web database
Oracle8
1999
1st Database with JAVA
Oracle8i
2001
1st Internet developer suite
Oracle 9i with SQLJ
2003
1st Enterprise Grid
Computing
Oracle10
2006
1s database auditing controls
Oracle11i
Features of Oracle
Some key features of Oracle are:• Client/Server (Distributed processing) environment.
• Large databases and space management.
• High availability
• Control availability
• Manageable security
• Portability
• Convertibility
• Many concurrent database users
• Openness, Industry Standard
•
The Oracle Server
The Oracle Server is an object-relational database
management system (ORDBMS) that provides an
open, comprehensive and integrated approach to
information management.
Oracle server show in fig.:-
Oracle Sever Consists of :a. An Oracle database and
b. An Oracle instance
 Oracle database:-The term database is used to
refer to the physical storage of information. The
database is stored on the disks attached to the
server.
 Oracle Instance:-The term instance refers to the
software executing on the server that provides access
to information stored in the database. The instance
runs on the computer or server.
Oracle Instance
• Every time a database is started, a”
System Global Area (SGA)” is allotted
and Oracle background processes are
started. Therefore, Oracle instance has
two types of processes:• User Processes:- it executes the code of an
application program or an Oracle Tool..
• Oracle Processes:- its are server process that
perform work for the user process and
background processes (oracle background
processes: RECO, PMON, SMON, DBWR, LGWR,
ARCH ).
Fig.
SGA
SGA:- System Global Area (SGA) is an area of memory
used for database information shared by the database
users. SGA consists of three major memory
structures :-
1.
2.
3.
Database buffer cache
Shared Pool
Redo Log buffer.
Background Processes for Oracle Instance, are:1.
2.
3.
4.
5.
6.
Database Writer (DBWR)
Log Writer (LGWR)
System Monitor (SMON)
Process Monitor (PMON)
Archiver (ARCH)
Recoverer (RECO).
Client/Server Architecture
• Client /Server computing architectures
generally has a client tier and a server
tier, but there can be more tiers also.
• Client/Server model classified into two
type:1. Two-Tier Computing Model:2. Three-Tire Computing Model
Two-Tier Computing Model
Three-Tire Computing Model
Some important Definitions
•
•
•
Data Dictionary:- The result of compilation of
DDL statements is a set of tables which are stored
in a special file called data dictionary or directory.
A Data Dictionary is a file that contains “Metadata”.
Metadata:- Data about data called Metadata.
Client:- Client is a end user which request to
central control unit (server) and get desired
information from the server.
1.
2.
•
•
Fat Client:- The client application, which is itself
responsible for its processing power and application logic.
Thin Client:- The Client application that does not have
much of processing power. It only provides a user
interface.
Table Space:- A logical storage unit that
comprises of one or more data files.
Transaction:- A Transaction is one complete unit
of work.
Accessing Oracle Database
Oracle provide a wide range of tools for accessing database:
SQL* Plus:- It is a simplest tool for accessing a
Oracle Database. It provide a shell (CommandLine Interpreter) for Oracle (SQL relational query
language and programming language PL/SQL).
 Designer/2000.
SQL*Plus
Oracle
Developer
 Developer/2000 etc.
Financials
2000
In the following fig. describe
Oracle
Server
Oracle
other tools which is
Web Server
Office
helpful for accessing of
Programmer
Power
2000
Builder
Oracle Database.
(C&JDBC)
Fig. The Oracle Tools
SQL( Structured Query Language)






First Commercial SQL was released in 1979 by Relational Software
Incorporation (RSI) which is today known as Oracle Corporation.
Thus, Oracle is the pioneer RDBMS that started using SQL.
Structured Query Language (SQL) is a language that enables you to
create and operate on relational databases.
The Original version of SQL was developed by IBM’s San Jose
Research Laboratory (Now Almanden Research Center ). SQL,
originally called “Sequel” was implemented as a part of System R
Project in early 1970s. The “Sequel” name changed to SQL.
In 1986, the American National Standards Institute (ANSI)
published an SQL standard that was updated again in 1992.
SQL has clearly established itself as the standard relational database
language.
SQL is the set of commands that is recognized by nearly all
RDBMSs.
SQL
(Structural
Query
Language)
DDL
(Data
Definition
Language)
1. Create
2. Alter
3. Drop
DML
(Data
Manipulation
Language)
1. Insert
2. Delete
3. Update
DRL
(Data
Retrieval
Language)
1. Select
DCL
( Data
Control
Language)
1. Grant
2. Revoke
1.
2.
3.
4.
TCL
( Transaction
Control
Language)
Commit
Rollback
Savepoint
Set Transaction
Oracle Elements
The Basic elements of oracle are:-
1. Schema objects
3. Data types
2. Literals
3. Comments
1. Schema objects:- A schema refers to the collection
of objects or collection of logical structures of
data . Schema Objects are the logical structures
that directly refer to the database’s data.
Some Schemas Objects are:1. Tables. 2. Views
3. Clusters
4. Stored Functions
5. Stored Procedures
6. Packages
7. Triggers
8. Indexes
Data Type
1.
2.
3.
4.
5.
6.
7.
8.
VARCHAR2(size)
NUMBER (p, s)
INTEGER (size)
DATE
CHAR (size)
BOOLEAN
LONG
LOB
SQL Structural query
Language
It is classified into five types:• DDL(Data Definition Language ):- Create ,
Alter, Drop
• DML(Data Manipulation Language): Insert ,
delete, update
• DCL(Data Control Language):-Grant, Revoke
• TCL(Transaction Control Language):Commit, Rollback, Savepoint
• DRL(Data Retrieval Language):- Select
Joins
A join lets us you extract columns from more than one tables.
A join is a query that combines rows from two or
more tables. A join can combine views also.
Note:- In unrestricted join or Cartesian product of two tables, all possible
concatenations are formed of all rows of both the tables.
Example. Display details of employee like department name and
employee no, employee name from emp and dept table along with.
SQL>SELECT DNAME, ENAME, EMPNO FROM EMP, DEPT;
Using Table Aliases :- A table Alias is a
temporary label given along with table name
in FROM clause.
Example. Display details of employee like department name,
department no, employee no, employee name from emp
and dept table along with.
Type of Joins
SQL support following type of Joins Equi Joins:- The Join , in which columns are compared
for equality, is called Equi-Join.
For Example:- Display details like department
number, department name, employee name, employee
number , job and salary . And ORDER the rows by
employee number with department number
SQL> SELECT D.DEPTNO, DNAME, E.DEPTNO,ENAME,
EMPNO,JOB,SAL FROM EMP E,DEPT D WHERE
E.DEPTNO=D.DEPTNO ORDER BY E.DEPTNO,EMPNO;
 Non-Equi Joins:- A Non-Equi join is a query that
specifies some relationship other than equality between
columns.
Example: SQL> SELECT EMPNO, ENAME,JOB,SAL, DNAME
FROM EMP,DEPT WHERE JOB='MANAGER‘;


Natural Join:- The Join in which only one of the
identical columns (combining form joined tables)
exists, is called Natural Join.
For Example:- Display details like department number,
department name, employee name, employee number , job
and salary with only one identical column . And ORDER
the rows by employee number with department number
SQL> SELECT E.DEPTNO, DNAME,ENAME, EMPNO,JOB,SAL FROM
EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO ORDER BY
E.DEPTNO,EMPNO;

Self Join:- A table joined with itself is called Self
Join.

SQL>SELECT E1.*, E2.EMPNO, E2.JOB,E2.DEPTNO WHERE
E1.EMP<>E2.EMP