Introduction to Oracle and SQL

1
Introduction to Oracle and SQL

• Contact Details
• Janet Francis
• www.staffs.ac.uk/jsf1
• j.francis_at_staffs.ac.uk
• [phone] (working hours)
• Room K319

2
Oracle

• What is it
• Relational Database Management System (RDBMS)
• Support for On Line Transaction processing (OLTP)

3
Oracle Information and Help
4
Oracle Documentation

• Useful Oracle documentation
• Oracle9i App. Developers Guide Oracle9i
  Server Concepts manual
• SQL Reference SQLPlus User Guide
• PL/SQL Users Guide Performance Tuning
• Guide and Reference
• System Release Bulletins
• Support CDs
• Support Alerts
• Journals and Magazines
• Oracle Texts
• - Oracle9i the Complete Reference
• - Oracle for Beginners
• - SQL for Dummies

5

• Information and Help
• Web sites
• - www.ioug.org
• - govt.us.oracle.com
• - otn.oracle.com (Oracle Technology
  Network OTN)
• - www.eoug.com
• - www.ukoug.org
• - www.lazydba.com
• List servers - ORACLE-L
• - dwlist_at_datawarehousing.com
• Oracle Support - Metalink
• - System based on local calls to
  regional centres

6

• Oracle and SQL
• Oracle is a Relational Database Management
  System (RDBMS)
• - Has a variety of associated tools and
  applications
• Developer 6i (Forms and Reports) Designer 6i
• Express (OLAP) products Applications products
• Java Development tools
  (JDeveloper) Application (Web) Servers
• Portal Wireless and Mobile
• Uses SQL to manipulate data
• - SQL is used to
• Retrieve data
• Update, insert, delete data
• Control transactions
• Define database structures
• Maintain security
• - SQL is the only way into Oracle data

7
Client/Server Architecture
Database Server (UNIX/NT)

Oracle Net
PC/Workstation Client
Oracle Net
Network TCP/IP protocol
Oracle RDBMS
Oracle or 3rd party Development Tool
8
3-Tier Architecture
Database Server (UNIX/NT)
Application Server (UNIX/NT)

Oracle Net
Thin Client (or PC/Workstation)
Oracle Net
Network link
Network TCP/IP protocol
Oracle RDBMS
Oracle Application Server
Browser
9
Relational Database Model

• Advantages
• More flexible
• Relationships implemented by content address
• More controlled redundancy
• Disadvantage
• Slow?

10
Dept and Emp!

• What are they?
• What are they used for?

11

• EMP DEPT tables

DEPTNO DNAME LOC ------ --------------
-------- 10 ACCOUNTING NEW YORK 20
RESEARCH DALLAS 30 SALES
CHICAGO 40 OPERATIONS BOSTON
DEPT Table
EMP Table
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO ----- -------- --------- -----
--------- ----- ----- ------ 7369 SMITH CLERK
7902 17-DEC-80 800 20 7499
ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 7521 WARD SALESMAN 7698 22-FEB-81
1250 500 30 7566 JONES MANAGER 7839
02-APR-81 2975 20 7654 MARTIN
SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 7782 CLARK MANAGER 7839 09-JUN-81
2450 10 7788 SCOTT ANALYST 7566
09-DEC-82 3000 20 7839 KING
PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500
0 30 7876 ADAMS CLERK 7788 12-JAN-83
1100 20 7900 JAMES CLERK 7698
03-DEC-81 950 30 7902 FORD
ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
12
OLTP Database

• Performs the basic business functions of the
  enterprise
• Via online or batch processing
• Objective is to process transactions in timely
  fashion and
• ensure validity of data
• Performs a mix of read/write processing
• Creating (inserting), reading, updating,
  deleting data
• Must also provide
• Concurrency control
• Usually via locking and/or timestamping
• Support for transaction design and control
• Security and Integrity
• Backup and recovery

13
Decision Support Database/Warehouse

• Provides results for querying, reporting,
• Online Analytical processing
• Essentially read only
• Produces output to complex queries in timely
  fashion
• Requires a range of tools or customised
  applications
• Tools must be user-friendly and intuitive
• Make full use of a business data model
• Tend to be huge with some very large tables
• Oracle9i can support up to 512 petabytes of
  data

14

• Advantages of the Database Approach
• Centralised control
• Enforcement of standards
• Data is highlighted as a valuable resource
• Standardisation of security and integrity e.g.
  backup
• Balancing requirements DBA
• Management has better visibility all data in
  one place
• More integrated processing fewer programs
• Controlled (reduced) redundancy of data
• Reduced inconsistencies
• More sophisticated validation
• Data Independence (Program/Data Independence)
• Reduced program maintenance
• Physical data independence
• Protection from changes in hardware etc.
• Logical data independence
• Protection from changes to schema (data model)

15
Disadvantages of the Database Approach

• Cost of initial conversion - purchase
• - installation
• - training
• - new applications
• - data entry
• Operating costs may be higher - DBMSs are
  resource hungry
• e.g. 60 of code in IMS is
  concerned with security
• Success may be its own undoing - easy access
  ----------gt
• performance degradation
• Reduced redundancy - all eggs in one basket
• Politics - naming conventions
• Some Comments
• Bandwagon effect cf. IBM
• How important is the information?
• Insurance/banks have a keen interest

16
People Issues

• Motivating staff to maintain old systems
• Migration and the complexities involved
• Organisation priorities ever changing and
  interrupting plans
• Recruitment people with Oracle experience are
  rare
• Staff have increasing value on job market as they
  are trained
• User support many extra skills needed
• User access new tools need new thinking and new
  controls
• Disciplines standards, methodologies, QA etc.
• Technical support high reliance on a few
  individuals
• Getting the right mix of job content