Introduction To Oracle And Other Earthly Matters

1
Introduction To Oracle And Other Earthly Matters

• Lecture 3 - Feb 5, 2008,
• in database systems course, Tel Aviv University
• Presented by Jackie Assa

2
Agenda

• Bureaucracy
• Database architecture overview
• Buzzwords
• SSH Tunneling
• Intro to Oracle
• Comments on homework
• (If time permits) Intro to programming

3
Agenda

• Bureaucracy
• Database architecture overview
• Buzzwords
• SSH Tunneling
• Intro to Oracle
• Comments on homework
• (If time permits) Intro to programming

4
The Forum!

• Please Join the Forum at
• http//www.cs.tau.ac.il/system/forums/

5
Homework 1

• Submission date is 19 Feb. (No late arrivals will
  be accepted)
• Work should be done in pairs
• Please, please, please, names and ID on the
  submittals.
• Details in the course forum
• Simple SQL queries on our hr database
• Submission will include an answers document and a
  SQL script with all the queries. Name of the
  script should be ltyournamegt.sql (sent as an
  attachment)

6
Project

• Hard work, but real.
• Work in groups of 4
• Project goal to tackle and resolve real-life DB
  related development issues
• One Two stages.
• Most programming language are accepted (Check
  with me)
• Thinking out of the box will be rewarded

7
Agenda

• Bureaucracy
• Database architecture overview
• Buzzwords
• SSH Tunneling
• Intro to Oracle
• Comments on homework
• (If time permits) Intro to programming

8
DB System from lecture 1
Two tier database system
Database server(someone elsesC program)
Applications
Data files
9
A core infrastructure
10
1,2,3 tiers
11
Abstractly(DB) system layers may include
Application
DB infrastructure
DB driver
transport
DB engine
Storage
12
Why (at least 1 reason)
Tester
Gui designer
DB programmer
DBA
App programmer
13
Application layer

• Why should it actually use database?
• Persistence layer
• Access data storage
• Interfacing between systems
• Large volumes
• Scalability
• Redundancy

14
Infrastructure layer

• Goals
• Database hiding
• Schema abstraction
• Encapsulation of db mechanisms
• Reuse
• How (In two words)

Model Abstraction
15
DB driver / bridge

• Used for
• API for database connectivity
• Protocol converter
• Performance improvements
• Transaction management
• Examples
• In a minute

16
Transport

• Mainly TCP but not only
• Secure
• Efficient
• Fast but not fast enough

17
DB engine

• Total management of the DB environment including
• Security
• Scalability (clustering)
• Maintenance
• Fault tolerant (disaster management)
• Monitoring
• Services
• Large DB engines include Microsoft SQL Server,
  Oracle, SyBase, MySQL, etc.

18
DB engine (2)

• DB engine management includes
• Databases/Tables/Fields
• Creation/removal/modification/optimization
• Connections/Users/Roles
• Security/monitoring/logging
• Jobs/Processes/Threads
• Scheduling/balancing/managing

19
Storage

• NAS/SAN, Raid and other stuff(sorry not in
  this course)

20
Agenda

• Bureaucracy
• Database architecture overview
• Buzzwords
• SSH Tunneling
• Intro to Oracle
• Comments on homework
• (If time permits) Intro to programming

21
Terms

• ODBC
• ADO
• OLE-DB
• DAO
• MDAC/UDA
• JDBC
• ORM

22
ODBC (Mainly Microsoft)

• Open Database Connectivity (ODBC) is a widely
  accepted application C programming interface
  (API) for database access.
• Maximum interoperability
• Expose database capabilities, not supplement
  them, except
• File access data
• Cursor lib

23
ODBC Example architecture
24
ODBC

• Examples of common tasks
• Selecting a data source and connecting to it.
• Submitting an SQL statement for execution.
• Retrieving results (if any).
• Processing errors.
• Committing or rolling back the transaction
  enclosing the SQL statement.
• Disconnecting from the data source.

25
OLE-DB (Microsoft)

• General COM model describing how applications
  access data
• Presents a single, consistent means of access to
  the application
• Uses two models, rowset and binder

Provider DBMS
Consumer / Application
Microsoft Windows
OLE-DB Interface
Oracle DB
COM
Exchange
My app
Excel
26
OLE-DB models

• Rowset
• Binder

27
ADO (Microsoft)

• ActiveX Data Objects (ADO) provides consistent,
  high-performance access to data.
• COM based, provides ease of use, high speed, low
  memory overhead, and a small disk footprint.
• Actually a simple interface to OLE DB.
• Includes extensions such as Multidimensional
  (ADO MD), Remote Data Service (RDS), etc.
• Alas. Ado.net

28
Two words about .Net and java

• Virtual machine

29
MDAC UDA

• UDA (Universal Data Access) and/or MDAC
  (Microsoft Data Access Components) include (ADO),
  OLE DB, and (ODBC).

30
JDBC

• Java DB connectivity API
• Similar to ODBC
• Why do you need it
• Pure Java
• Simple API
• Well.Multi-platform

31
JDBC

• Supports n-Tier architectures
• Tabular data handling
• SQL but not only SQL

32
JDBC

• API includes
• DriverManager, Connection, Statement,
  PreparedStatement, CallableStatement, ResultSet,
  SQLException, DataSource
• JDBC 2.0 includes
• Transactions, improved connections, and more
• JDBC Type Driver
• Type 1 - (JDBC-ODBC Bridge) drivers.
• Type 2 - native API for data access which provide
  Java wrapper classes
• Type 3 - 100 Java, use vendor independent
  Net-protocol to access a vendor independent
  remote listener. This listener converts vendor
  independent calls to vender dependent ones.
• Type 4 - They are also written in 100 Java and
  are the most efficient among all driver types.

33
ORM

• Object-Relational mapping is a programming
  technique for converting data between
  incompatible type systems in relational databases
  and object-oriented programming languages.
  (wikipedia)
• For example Hibernate, EJB3.0, JDO

34
Agenda

• Bureaucracy
• Database architecture overview
• Buzzwords
• SSH Tunneling
• Intro to Oracle
• Comments on homework
• (If time permits) Intro to programming

35
Welcome to
The travels of a query
36
SSH
Standard way
Using Tunnel
Client Machine
Client Machine
Application
Application
DB infrastructure
DB infrastructure
Db bridge/driver
Db bridge/driver
TCP
proxy
Transport (TCP)
SSH
Tunnel machine (SSH server)
Proxy Machine
Server Machine
TCP
DB engine
Server Machine
DB engine
37
SSH in TAU
Application
YOUR MACHINE (HOME/LAB) define DB at 127.0.0.1,
port 1555
DB infrastructure
Db bridge/driver
Tera Term Pro connects to nova and forward local
port 1555 to ida port 1521
proxy
Tunnel machine (SSH server)
Nova.cs.tau.ac.il
Ida.cs.tau.ac.il Port 1521
DB engine
For more details http//www.cs.tau.ac.il/faq/ssh_
tunnelling.htm
38
Agenda

• Bureaucracy
• Database architecture overview
• Buzzwords
• SSH Tunneling
• Intro to Oracle
• Comments on homework
• (If time permits) Intro to programming

39
Products we will be using

• Oracle database (express edition)
• SQLDeveloper
• JDeveloper
• Free to download on oracle.com
• More tutorials on http//www.tau.ac.il/jackassa/d
  b/
• java programming.pdf
• JDBC.pdf
• program with PLSQL.pdf
• SQL foundamentals I.pdf
• SQL foundamentals II.pdf
• SQL tuning.pdf

40
(No Transcript)
41
(No Transcript)
42
(No Transcript)
43
Things to contemplate on when connecting

• Host
• Port
• SID
• Driver
• Schema
• User
• password

• localhost/plab-156
• 1521
• xe/taudb/other?
• Thin oracle.jdbc.driver.OracleDriver
• hr/user/system
• ?

http//www.tau.ac.il/jackassa/db/cGuide.htm
44
SQLplus demo

• Invoking (TAU)
• Sqlplus

http//www.tau.ac.il/jackassa/db/cGuide.htm
45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
(No Transcript)
49
(No Transcript)
50
(No Transcript)
51
(No Transcript)
52
(No Transcript)
53
(No Transcript)
54
(No Transcript)
55
(No Transcript)
56
(No Transcript)
57
(No Transcript)
58
(No Transcript)
59
(No Transcript)
60
(No Transcript)
61
SQLDeveloper demo

• Invoking (TAU)
• sqldeveloper

62
(No Transcript)
63
(No Transcript)
64
(No Transcript)
65
(No Transcript)
66
(No Transcript)
67
(No Transcript)
68
(No Transcript)
69
(No Transcript)
70
(No Transcript)
71
(No Transcript)
72
(No Transcript)
73
(No Transcript)
74
(No Transcript)
75
(No Transcript)
76
(No Transcript)
77
(No Transcript)
78
(No Transcript)
79
(No Transcript)
80
Oracle JDeveloper demo

• Invoking (in TAU)
• jdev

81
Oracle Express Edition (XE)

• Installation only at home

82
XE Database hands-on demo
Similar tutorial can be found in
http//st-curriculum.oracle.com/tutorial/DBXETuto
rial/index.htm

• Installation
• Create a user
• Run a script
• Query
• Other database objects
• Administration tasks

83
Agenda

• Bureaucracy
• Database architecture overview
• Buzzwords
• SSH Tunneling
• Intro to Oracle
• Comments on homework
• (If time permits) Intro to programming

84
Additional Notes

• SQL functions and arithmetic conditions.
• Usage of quotes
• LIKE, SOUNDS LIKE
• Use the Syntax help in Query browser
• DESCRIBE
• Joins

85
Agenda

• Bureaucracy
• Database architecture overview
• Buzzwords
• SSH Tunneling
• Intro to Oracle
• Comments on homework
• (If time permits) Intro to programming

86
Project Coding Tips

• Layering
• Encapsulation
• Reuse
• Configuration
• Schema
• Testing

87
Thank you
88
database server (Oracle on plab-156)
89
Client Application (your app)
90
Driver (Oracle)
91
Firewall
92
SSH client (Tera-term)
93
SSH Server (Nova)