oracle for software developers. what is a relational database? data is represented as a set of two-...
TRANSCRIPT
![Page 1: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/1.jpg)
Oracle for Software Oracle for Software DevelopersDevelopers
![Page 2: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/2.jpg)
What is a relational database?What is a relational database?
Data is represented as a set of two-dimensional tables. (rows and columns)
One or more of the columns is defined as a primary key and this column or combination of columns will uniquely define the row.
Tables are related by the shared column values.
![Page 3: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/3.jpg)
What is a relational database? What is a relational database? (continued)(continued)
Operations in a relational database are processed in groups of data.
The output of the operation is itself a table (or relation or result set), which can be processed further by relational operations.
![Page 4: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/4.jpg)
Relational TablesRelational Tables
vac ioc1
vac ioc2
ps ioc2
ioc1 Test 1 Bldg A 128.0.0.1
ioc2 Test 2 Bldg A 128.0.0.2
ioc3 Test 3 Bldg A 128.0.0.3
![Page 5: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/5.jpg)
Oracle databaseOracle database
Two basic concepts must be understood in order to make sense of the Oracle architecture: databases and instances.
A database is a set of data as defined previously. A database instance is a set of memory structures
and background processes shared by all users that access a database.
You access the Oracle database through the instance.
![Page 6: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/6.jpg)
11--33 Copyright Oracle Corporation, 1999. All rights reserved.®
Oracle ArchitectureOracle Architecture
SGARedo log bufferDatabase buffer cacheShared pool
LGWR DBW0PMON SMON RECO CKPT ARC0
Threads
Threads
Oracle Database
11--33 Copyright Oracle Corporation, 1999. All rights reserved.®
Oracle ArchitectureOracle Architecture
SGARedo log bufferDatabase buffer cacheShared pool
LGWR DBW0PMON SMON RECO CKPT ARC0
Threads
Threads
Oracle Database
![Page 7: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/7.jpg)
SQL - Interface to the SQL - Interface to the databasedatabase
SQL is a data sub-language: the purpose of SQL is to provide an interface to a relational database such as Oracle. All SQL statements are instructions to the database.
![Page 8: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/8.jpg)
SQL*PLUSSQL*PLUS
Oracle utility that allows SQL execution against an Oracle database.
Useful for database administrators, software developers and end-users.
Available as a client tool on the desktop or accessible at the OS command line interface for both Unix an NT.
![Page 9: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/9.jpg)
Oracle ArchitectureOracle Architecture
USERS and SCHEMAS TABLE INDEX VIEW PACKAGE PACKAGE BODY PROCEDURE SEQUENCE SYNONYM TYPE
![Page 10: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/10.jpg)
Users and SchemasUsers and Schemas
User - A user account is not a physical structure in the database, but it does have important relationships to the objects in the database; users own database objects.
Schema - The set of objects owned by a user account.
![Page 11: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/11.jpg)
Tables, Columns, and DatatypesTables, Columns, and Datatypes
Table – The storage mechanism for data within an Oracle database that represents a set of records of the same structure, also called rows.
Column - A table contains columns which are descriptive attributes and have a defined data type.
![Page 12: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/12.jpg)
Tables, Columns, and Datatypes Tables, Columns, and Datatypes (continued)(continued)
VARCHAR2(size) Variable-length character string having maximum length size bytes. Maximum size is 4000.
NUMBER( p,s) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD.
![Page 13: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/13.jpg)
ConstraintsConstraints
A constraint ensures that a specific condition is meet by all rows in a table.
PRIMARY KEY - Specifies that column(s) are the table prime key and must have unique values. Index is automatically generated for column.
NULL/NOT NULL - NOT NULL specifies that a column must have some value. NULL (default) allows NULL values in the column.
![Page 14: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/14.jpg)
Constraints Constraints (continued)(continued)
UNIQUE - Specifies that column(s) must have unique values
FOREIGN KEY - Specifies that column(s) are a table foreign key and will use referential uniqueness of parent table. Index is automatically generated for column. Foreign keys allow deletion cascades and table / business rule validation.
![Page 15: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/15.jpg)
Constraints Constraints (continued)(continued)
DEFAULT - Specifies some default value if no value entered by user.
DISABLE - You may suffix DISABLE to any other constraint to make Oracle ignore the constraint, the constraint will still be available to applications/tools and you can enable the constraint later if required.
![Page 16: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/16.jpg)
IndexesIndexes
An index is a database structure used to quickly find a row in a table.
An index entry consists of a key value and a RowId. The key value is the value of a column or columns in a row.
![Page 17: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/17.jpg)
SynonymsSynonyms
Mask the underlying complexity of the path to an object.
Can be used to provide pointers for tables, views, procedures, functions, packages, and sequences.
![Page 18: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/18.jpg)
ViewsViews
A view appears to be a table containing columns and is queried in the same manner that a table is queried. Conceptually, a view can be thought of as a mask overlaying one or more tables, such that the columns in the view are found in one or more underlying tables.
![Page 19: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/19.jpg)
User viewsUser views
USER_TABLESUSER_TAB_COLUMNSUSER_INDEXESUSER_SYNONYMSUSER_VIEWSUSER_SEQUENCES
![Page 20: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/20.jpg)
DDLDDL
Create tableAlter tableDrop tableCreate indexCreate synonymCreate sequence
![Page 21: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/21.jpg)
SQL CommandsSQL Commands
SELECTUPDATEINSERTDELETE
![Page 22: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/22.jpg)
Data ConsistencyData Consistency
RollbackCommitSet Transaction
![Page 23: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/23.jpg)
Connecting to the databaseConnecting to the database
Net8 – Oracle transparent protocol network layer.
![Page 24: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/24.jpg)
Connecting to the database Connecting to the database (continued)(continued)
Client configuration via tnsnames.ora file. iroquoisdbprod = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world) (PROTOCOL = TCP) (Host = IROQUOISDB) (Port = 1521) ) ) (CONNECT_DATA = (SID = PROD) (GLOBAL_NAME = PROD.world) ) )
![Page 25: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/25.jpg)
Connecting to the database Connecting to the database (continued)(continued)
Future plans are to go to a LDAP model for SQL*NET name resolution.
![Page 26: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/26.jpg)
Embedded SQLEmbedded SQL
C/C++ - Pro*CJava – JDBC SQLJPERL - DBI FORTRAN – Pro*FortranOCIOracle Tools (Developer 2000,PL/SQL)ODBC
![Page 27: Oracle for Software Developers. What is a relational database? Data is represented as a set of two- dimensional tables. (rows and columns) One or more](https://reader035.vdocuments.us/reader035/viewer/2022081503/56649e235503460f94b10a42/html5/thumbnails/27.jpg)
ResourcesResources
http://w3.one.net/~jhoffman/sqltut.htmhttp://cisnet.baruch.cuny.edu/holowczak/ora
cle/sqlplus/tutorial.htmlhttp://www.spnc.demon.co.uk/ora_sql/sqlm
ain.htm
http://technet.oracle.com/