Abstract * Foil Index for this file
Given by Gang Cheng, C.W. Ou, Geoffrey C. Fox at CPS616 Basic Information Track for Computational Science on Winter-Spring Semester 96. Foils prepared 4 April 1996
Abstract * Foil Index for this file
| This starts with a summary of four major modules (this is first one of the four) |
| What is a Relational Database |
| A Phone-List Example for Motivation and Illustration of Concept |
| Major Schema Objects and Structures in Oracle with examples |
| Data Integrity and Data Types in Oracle |
| Data Concurrency and Consistency in Oracle |
This table of Contents
Abstract
Full HTML Index
Addon
| Instructor: Geoffrey Fox |
| Version 13 March 96 |
| teamed with Gang Cheng, Chao-Wei Ou |
| Syracuse University |
| 111 College Place |
| Syracuse |
| New York 13244-4100 |
Full HTML Index
Addon(need password etc)
| This starts with a summary of four major modules (this is first one of the four) |
| What is a Relational Database |
| A Phone-List Example for Motivation and Illustration of Concept |
| Major Schema Objects and Structures in Oracle with examples |
| Data Integrity and Data Types in Oracle |
| Data Concurrency and Consistency in Oracle |
Full HTML Index
| The Oracle/web part of CPS616 consists of three presentation Sub-modules and One Lab Session with a Hands-on Tutorial |
Module 1 - A Technical Introduction to Oracle RDBMS
|
Module 2 - Data Access and Database Programming in Oracle
|
Full HTML Index
Addon
Module 3 - Integration of Web and Oracle RDBMS
|
Module 4 - Hands-on Lab Session
|
| Handout Materials are also Associated with Each Module for Details |
Full HTML Index
| Major features in database and information management |
| large databases and space management control |
| many concurrent database users |
| high transaction processing performance |
| high availability |
| controlled availability |
| industry accepted standards |
| manageable security |
| database enforced integrity |
| client/server (distributed processing) environments |
| distributed database systems |
| portability, compatibility and connectability |
Full HTML Index
| Through out the lectures, we use a simple example to illustrate important concepts, features and syntax in Oracle7 |
| This example will also be used to demonstrate the Oracle/Web integration and in the lab session to build a web application with Oracle server |
| This simple application is a phone list database, such as NPAC phone list |
| Basic requirement is to build such a database so as to locate a person's phone number with different query criteria |
Full HTML Index
| Database design is a major area in RDBMS and system analysis. We will not discuss this key database issue, and assume that the phone list database is represented in two tables which represent two entities in the modeled application: person entity and phone entity |
| Table 1 - personal information. |
| Table name : person_info_table |
Full HTML Index
Addon
| Table 2 - phone number information. |
| Table name : phone_list_table |
Full HTML Index
Basic Assumptions in the Entity-Relationship (E-R) Model:
|
Full HTML Index
Addon
| A database model is a set of definitions describing how real-world data is conceptually represented as information in the computer |
The relational model - database collects related information in a set of tables.
|
| In the example problem, people and phone numbers are entities; |
| Title, uid etc. are attributes of the person entity; type is attribute of phone number entity |
| pid is used to set up relationships between entities |
Full HTML Index
| Simple Attribute Tables for: |
| Person Phone Room |
| While a set of Tables could describe Many:Many(1) relationships |
Full HTML Index
| Rule 1: First Normal Form Rule - a table must have no multi-valued columns |
| example: in phone_list_table |
| pid phone no type |
3,4 315-443-9209 office
|
Full HTML Index
Rule 2: Access rows by content only rule - we can only retrieve rows/columns by their content (attribute values that exist), i.e., there is no order defined for the rows or columns
|
Full HTML Index
| Rule 3: The uniqueness rule - two rows in a table cannot be identical in all column values at once |
| Example: the two rows are not allowed to coexist in the phone_list_table |
| pid phone no type |
| 3 315-443-9209 office |
| 3 315-443-9209 office |
Full HTML Index
| All base RDBMS operations and SQL operators are built on relational algebra theory |
| Set operations - union, intersection, difference, product |
| Native relational operations - project, select, join (outer join, left outer join, right outer join, theta join), division |
Full HTML Index
| Database Structures - well-defined objects represented as tablespace and other schema objects that store the data of a database |
| Database Operations - actions to manipulate the data and their structures |
| Integrity Rules - relational rules to govern proper operations on data and their structures |
Full HTML Index
Addon
| Tablespace - a database is divided into logical storage units called tablespace |
| A tablespace is used to group related logical structure together |
| Tablespaces commonly group all of an application's objects to simplify certain administrative operations |
| It is Database Administrator (DBA)'s job to define tablespaces and assign them to users |
Full HTML Index
Addon
| This figure illustrates |
| Each Oracle DB is logically divided into one or more tablespaces |
| One or more data files are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace |
| The combined size of a tablespace's data files is the total storage capacity of the tablespace (SYSTEMS has 2M while USERS has 4M) |
| The combined storage capacity of a database's tablespaces is the total storage capacity of the database (6M in example above) |
Full HTML Index
| Table - the basic unit of data storage in Oracle. The tables of a database hold all of the user-accessible data |
| Each table is defined with a table name and set of columns |
| Each column is given a column name, a datatype (CHAR, VARCHAR2, DATE or NUMBER), and a width |
| Once a table is created, valid rows of data can be inserted into it. The table's rows can then be queried, deleted, or updated |
Full HTML Index
Addon
| To create the two tables for the phone-list database, we would have |
Full HTML Index
| View - customized presentation of the data in one or more tables |
| Views do not actually contain or store data |
| They derive their data from the tables on which they are based, referred to as the base table |
| Like tables, views can be queried, updated, inserted into, and deleted from, but with some restrictions |
| All operations performed on a view actually affect the base tables of the view |
Full HTML Index
| Provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table. |
| For example, a view of a table can be created so that columns with sensitive data (for example, SSN) are not included in the definition of the view |
| Hide data complexity. |
| For example, a single view can be used to create a join, which is a display of related columns or rows in multiple tables. |
| However, the view hides the fact that this fact that this data actually originates from several tables |
Full HTML Index
Simplify commands for the user.
|
Present the data in a different perspective from that of the base table.
|
Store complex queries.
|
Full HTML Index
| Suppose we'd like to create a view (virtual table) called staff_info_table which has all the personal information of NPAC staff. |
| This view will use the base table person_info_table with the position column has only staff related value |
Full HTML Index
| Sequence - a sequence generates a serial list of unique numbers within a single numeric column of a database's tables |
| Sequences simplify application programming by automatically generating unique numerical "labels" for the rows of tables |
| Use them for attributes such as pid -- person ID in phone list where must generate a unique label for every row |
Full HTML Index
| Procedure or Function - a set of SQL and PL/SQL statements grouped together as an executable unit to perform a specific task |
| Procedures and functions allow user to combine the ease and flexibility of SQL with the procedural functionality of a structured programming language |
| Using PL/SQL, such procedures and functions can be defined and stored in the database for reuse |
Package - provides a method of encapsulating and storing related procedures, functions, and other package constructs together as a unit in the database.
|
| more details about procedure/function/package will be covered in 'PL/SQL' section in second module |
Full HTML Index
| Synonym - an alias for a table, view, sequence, or program unit |
| A synonym is not actually an object itself, but instead is a direct reference to an object |
A synonym can be public or private.
|
Full HTML Index
Synonyms are used to
|
For example, to allow all users to share the WOW PL/SQL package called htp which is currently owned by a user 'cps616'. A public synonym must be created and certain permissions must be granted:
|
Full HTML Index
| Index, Cluster (described later) - are optional structures associated with tables, which can be created to increase the performance of data retrieval |
| Indices are created on one or more columns of a table. An Oracle index provides a faster access path to efficiently locate requested rows |
Create an index in Oracle - An Example
|
Full HTML Index
An Index can improve the performance of queries that select a small percentage of rows from a table. A general guideline is to create indexes on tables that are queried for less than 2% - 4% of the table's rows, with the following assumptions:
|
Full HTML Index
| Consider indexing columns that are used frequently in WHERE clauses |
| Consider indexing columns that are used frequently to join tables in SQL statements |
| Only index columns when only a few rows have the same value in the columns indexed |
| Do not index columns with few distinct values |
| Do not index columns that are frequently modified |
| Do not index columns that only appear in WHERE clauses with functions or operator |
| Consider indexing foreign keys (see later) of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statement access the parent and child tables |
| When choosing whether to index a column, consider whether the performance gain for queries is worth the performance loss for INSERT, UPDATE, and DELETE statements and use of the space required to store the index |
Full HTML Index
Clusters are groups of one or more tables physically stored together because they share common columns and are often used together.
|
Data Dictionary - each Oracle server has a data dictionary which is a set of tables and views that are used as a read-only reference about the database (meta-data),
|
| For example, you can see your disk space quota: select * from user_ts_quotas; |
Full HTML Index
| It is very important to guarantee that data adheres to certain rules which are determined by application |
| For example, assume that a business rule says that no row in the phone_list_table should be allowed unless its person_id value found in the person_id column in the person_info_table (this is to ensure that a person's entity must exist first before the phone number information can be established) |
| If an insert or update statement attempts to violate this integrity rule, Oracle must roll back the invalid statement and return an error to the application |
| In order to achieve this protection, Oracle provides integrity constraints |
| For example, a foreign key (see later) can be defined on the column person_id of phone_list_table which references to the primary key person_id of the person_info_table |
Full HTML Index
Integrity Constraints - a declarative way to define a business rule for a column of a table.
|
Following integrity constraints are supported by Oracle:
|
Full HTML Index
| For example, to enable necessary Integrity constraints, we should redefine the two tables in our example as follows : |
Full HTML Index
CHAR - stores fixed length character strings. 1 - 255 chars
|
VARCHAR2 - stores variable-length character strings. 1 - 2000 chars
|
| CHAR and VARCHAR2 have different comparison semantics in string comparisons |
Full HTML Index
NUMBER - stores fixed and floating point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision
|
DATE - stores point-in-time values (e.g. dates and times) in a table. It stores the year (including the century), the month, the day, the hours, the minutes, and the seconds.
|
ROWID - every row in a non-clustered table is assigned a unique ROWID that corresponds to the physical address of a row's row piece
|
Full HTML Index
Addon
LONG - store variable-length character data containing up to 2 GB
|
| RAW and LONG RAW - for data that is not to be interpreted by Oracle. Intended for binary data or byte strings |
| Oracle allows some restricted implicit datatype conversions and provides a set of SQL functions for explicitly datatype conversions |
| See associated resources for more details on Oracle datatypes |
Full HTML Index
Addon
Any RDBMS must have the software mechanisms to achieve the following important requirements of an information management system :
|
Concurrency control - simultaneous access of the same data by many users
|
| Oracle resolves such issues by using various types of locks and a multi-version consistency model based on the concept of a transaction |
Full HTML Index
Addon
| Transaction - a logical unit of work that comprises one or more SQL statements executed by a single user |
| A transaction begins with the user's first executable SQL statement |
| A transaction ends when it is explicitly committed or rolled back |
| Transactions provide the database user or application developer with the capability of guaranteeing consistent changes to data, as long as the SQL statements within a transaction are grouped logically |
| A transaction should consist of all of the necessary parts for one logical unit of work - no more and no less |
| Data in all referenced tables are in a consistent state before the transaction begins and after it ends. |
| Transactions should consist of only the SQL statements that comprise one consistent change to the data |
Full HTML Index
| Guarantees that the set of data seen by a statement is consistent with respect to a single point-in-time and does not change during statement execution (atomic operations or statement-level read consistency) |
| Ensure that readers of database data do not wait for writers or other readers of the same data |
| Ensure that writers of database data do not wait for readers of the same data |
| Ensure that writers only wait for other writers if they attempt to update identical rows in concurrent transactions |
| The simplest way to think of Oracle's implementation of read-consistency is to imagine each user operating a private copy of the database, hence called the multi-version consistency model |
Full HTML Index
| When an update (written) occurs, the original data values changed by the update remains part of an uncommitted transaction, any user that later queries the modified data views the original data values |
| Only when a transaction is committed are the changes of the transaction made permanent |
| Only statements that start after another user's transaction is committed see the changes made by the committed transaction |
A transaction is key to Oracle's RDBMS for providing read-consistency. This unit of committed (or uncommitted) SQL statements :
|
Full HTML Index
| Locking - control concurrent access to data |
| Intended to prevent destructive interaction between users accessing table data |
Locks are used to achieve two important database goals
|
Full HTML Index
Oracle provides automatic (implicit) locking and manual (explicit) locking
|