database systems
TRANSCRIPT
![Page 1: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/1.jpg)
Introduction To Oracle And Other Earthly Matters
Lecture #3 - Feb 5, 2008,
in database systems course, Tel Aviv UniversityPresented by Jackie Assa
![Page 2: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/2.jpg)
Agenda
Bureaucracy…Bureaucracy… Database architecture overviewDatabase architecture overview Buzzwords SSH Tunneling Intro to Oracle Comments on homework (If time permits) Intro to
programming
![Page 3: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/3.jpg)
Agenda
Bureaucracy…Bureaucracy… Database architecture overviewDatabase architecture overview Buzzwords SSH Tunneling Intro to Oracle Comments on homework (If time permits) Intro to
programming
![Page 4: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/4.jpg)
The Forum!
Please Join the Forum at:http://www.cs.tau.ac.il/system/forums/
![Page 5: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/5.jpg)
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 <yourname>.sql (sent as an attachment)
![Page 6: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/6.jpg)
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
![Page 7: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/7.jpg)
Agenda
Bureaucracy… Database architecture overviewDatabase architecture overview Buzzwords SSH Tunneling Intro to Oracle Comments on homework (If time permits) Intro to
programming
![Page 8: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/8.jpg)
DB System from lecture #1
Data files
Database server(someone else’s
C program) Applications
connection
(ODBC, JDBC)
“Two tier database system”
![Page 9: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/9.jpg)
A core infrastructure
![Page 10: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/10.jpg)
1,2,3 tiers
![Page 11: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/11.jpg)
Abstractly(DB) system layers may include
Application
DB infrastructure
DB driver
transport
DB engine
Storage
![Page 12: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/12.jpg)
Why (at least 1 reason)
DB programmer
App programmer
DBA
Gui designerTester
![Page 13: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/13.jpg)
Application layer
Why should it actually use database? Persistence layer Access data storage Interfacing between systems Large volumes Scalability Redundancy
Application
DB infrastructure
DB driver
transport
DB engine
Storage
![Page 14: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/14.jpg)
Infrastructure layer
Goals: Database “hiding” Schema abstraction Encapsulation of db mechanisms Reuse
How: (In two words)
Application
DB infrastructure
DB driver
transport
DB engine
Storage
![Page 15: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/15.jpg)
DB driver / bridge
Used for: API for database connectivity Protocol converter Performance improvements Transaction management
Examples: In a minute…
Application
DB infrastructure
DB driver
transport
DB engine
Storage
![Page 16: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/16.jpg)
Transport
Mainly TCP but not only Secure Efficient Fast but not fast enough
Application
DB infrastructure
DB driver
transport
DB engine
Storage
![Page 17: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/17.jpg)
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.
Application
DB infrastructure
DB driver
transport
DB engine
Storage
![Page 18: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/18.jpg)
DB engine (2)
DB engine management includes: Databases/Tables/FieldsCreation/removal/modification/
optimization Connections/Users/RolesSecurity/monitoring/logging Jobs/Processes/ThreadsScheduling/balancing/managing
![Page 19: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/19.jpg)
Storage
NAS/SAN, Raid and other stuff…(sorry… not in this course)
Application
DB infrastructure
DB driver
transport
DB engine
Storage
![Page 20: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/20.jpg)
Agenda
Bureaucracy… Database architecture overview Buzzwords SSH Tunneling Intro to Oracle Comments on homework (If time permits) Intro to
programming
![Page 21: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/21.jpg)
Terms…
ODBC ADO OLE-DB DAO MDAC/UDA JDBC ORM
![Page 22: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/22.jpg)
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
![Page 23: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/23.jpg)
ODBC – Example architecture
![Page 24: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/24.jpg)
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.
![Page 25: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/25.jpg)
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
OLE-DBInterface
Consumer /Application Oracle DB
Provider DBMS
Exchange
Excel
MicrosoftWindowsCOM
My app
![Page 26: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/26.jpg)
OLE-DB models
Rowset:
Binder
![Page 27: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/27.jpg)
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
![Page 28: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/28.jpg)
Two words about .Net and java
Virtual machine
![Page 29: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/29.jpg)
MDAC… UDA
UDA (Universal Data Access) and/or MDAC (Microsoft Data Access Components) include (ADO), OLE DB, and (ODBC).
![Page 30: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/30.jpg)
JDBC
Java DB connectivity API Similar to ODBC Why do you need it:
Pure Java Simple API Well….Multi-platform
![Page 31: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/31.jpg)
JDBC
Supports n-Tier architectures Tabular data handling SQL but
not only SQL
![Page 32: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/32.jpg)
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.
![Page 33: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/33.jpg)
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
![Page 34: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/34.jpg)
Agenda
Bureaucracy… Database architecture overview Buzzwords SSH Tunneling Intro to Oracle Comments on homework (If time permits) Intro to
programming
![Page 35: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/35.jpg)
Welcome to
![Page 36: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/36.jpg)
SSH
Application
DB infrastructure
Db bridge/driver
Transport (TCP)
DB engine ServerMachine
ClientMachine
Standard way Using Tunnel
Application
DB infrastructure
Db bridge/driver
DB engine ServerMachine
ClientMachine
Tunnel machine(SSH server)
proxy
ProxyMachineTCP
SSH
TCP
![Page 37: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/37.jpg)
SSH in TAUApplication
DB infrastructure
Db bridge/driver
DB engine
Tunnel machine(SSH server)
proxy
YOUR MACHINE(HOME/LAB) define DB at 127.0.0.1, port 1555
Nova.cs.tau.ac.il
Ida.cs.tau.ac.ilPort 1521
Tera Term Pro connects to nova andforward local port 1555 to ida port 1521
For more details: http://www.cs.tau.ac.il/faq/ssh_tunnelling.htm
![Page 38: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/38.jpg)
Agenda
Bureaucracy… Database architecture overview Buzzwords SSH Tunneling Intro to Oracle Comments on homework (If time permits) Intro to
programming
![Page 39: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/39.jpg)
Products we will be using Oracle database – (express edition) SQLDeveloper JDeveloper
Free to download on oracle.comMore tutorials on http://www.tau.ac.il/~jackassa/db/
java programming.pdfJDBC.pdfprogram with PLSQL.pdfSQL foundamentals I.pdfSQL foundamentals II.pdfSQL tuning.pdf
![Page 40: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/40.jpg)
![Page 41: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/41.jpg)
![Page 42: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/42.jpg)
![Page 43: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/43.jpg)
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
![Page 44: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/44.jpg)
SQL*plus demo
Invoking (TAU):Sqlplus
http://www.tau.ac.il/~jackassa/db/cGuide.htm
![Page 45: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/45.jpg)
![Page 46: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/46.jpg)
![Page 47: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/47.jpg)
![Page 48: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/48.jpg)
![Page 49: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/49.jpg)
![Page 50: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/50.jpg)
![Page 51: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/51.jpg)
![Page 52: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/52.jpg)
![Page 53: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/53.jpg)
![Page 54: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/54.jpg)
![Page 55: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/55.jpg)
![Page 56: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/56.jpg)
![Page 57: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/57.jpg)
![Page 58: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/58.jpg)
![Page 59: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/59.jpg)
![Page 60: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/60.jpg)
![Page 61: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/61.jpg)
SQLDeveloper demo
Invoking (TAU):sqldeveloper
![Page 62: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/62.jpg)
![Page 63: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/63.jpg)
![Page 64: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/64.jpg)
![Page 65: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/65.jpg)
![Page 66: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/66.jpg)
![Page 67: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/67.jpg)
![Page 68: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/68.jpg)
![Page 69: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/69.jpg)
![Page 70: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/70.jpg)
![Page 71: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/71.jpg)
![Page 72: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/72.jpg)
![Page 73: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/73.jpg)
![Page 74: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/74.jpg)
![Page 75: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/75.jpg)
![Page 76: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/76.jpg)
![Page 77: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/77.jpg)
![Page 78: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/78.jpg)
![Page 79: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/79.jpg)
![Page 80: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/80.jpg)
Oracle JDeveloper demo
Invoking (in TAU):jdev
![Page 81: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/81.jpg)
Oracle Express Edition (XE)
Installation only at home…
![Page 82: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/82.jpg)
XE Database hands-on demo
Installation Create a user Run a script Query Other database objects Administration tasks
Similar tutorial can be found in
http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm
![Page 83: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/83.jpg)
Agenda
Bureaucracy… Database architecture overview Buzzwords SSH Tunneling Intro to Oracle Comments on homework (If time permits) Intro to
programming
![Page 84: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/84.jpg)
Additional Notes SQL functions and arithmetic conditions. Usage of quotes LIKE, SOUNDS LIKE Use the Syntax help in Query browser DESCRIBE Joins
![Page 85: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/85.jpg)
Agenda
Bureaucracy… Database architecture overview Buzzwords SSH Tunneling Intro to Oracle Comments on homework (If time permits) Intro to
programming
![Page 86: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/86.jpg)
Project Coding Tips
Layering Encapsulation Reuse Configuration Schema Testing
![Page 87: Database Systems](https://reader036.vdocuments.us/reader036/viewer/2022062514/558cd99ed8b42a885e8b4689/html5/thumbnails/87.jpg)
Thank you