Transcript Oracle Database System

Oracle: A Relational Database
System (RDBMS)
September 27, 2000
Soon Ae Chun
Interface Tools to access data
• SQL*Plus: a command line interface
• Developer (Developer/2000): forms,
reports, and graphical interfaces
Connectivity
• SQL*Net and Net8: allow a Oracle client
machine to communicate with Oracle data
server
• SQL*Connect and Oracle Gateways:
communicate a client machine to access nonoracle data on server machine
• Oracle Server: receives requests from client
machines and submits them to Oracle RDBMS
• Oracle ODBC drivers: connect software to
Oracle databases
Core Database Engine
• ORACLE RDBMS (Oracle Universal server)
• Integrated Data Dictionary: manage tables owned by
all users in a system
• SQL: language to access and manipulate data
• PL/SQL: a procedural extension to SQL language
SQL*Plus
• Command line tool that process user’s SQL
statements
• Requires Oracle account: username, password,
host_string
• <pegasus> sqlplus
SQL*Plus: Release 8.0.4.0.0 - Production on Tue Sep 26 13:49:50 2000
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Enter user-name: soon
Enter password:
connected to: ….
SQL> exit
To Change Oracle Password
•
•
•
•
sqlplus
username:
password
SQL> GRANT CONNECT TO
your_oracle_username IDENTIFIED BY
your_new_password ;
• http://pegasus.rutgers.edu/~soon/oracle.html
SQL*Plus Commands
•
•
•
•
Example of SQL stmt: select * from tab;
describe <table name>
list : list current sql stmt
edit : edit current sql statement (or edit <filename>)
(replace * with tname)
•
•
•
•
•
run (or /): execute the statement in the current buffer
save <filename.sql> : save current sql stmt to a file
get <filename.sql> : load sql statements into buffer
spool <filename>: send output from sql stmt to a file
start <filename.sql>: load script file with sql stmts and run
them
• help
• quit
Structured Query Language (SQL)
• the de-facto standard query language for creating
and manipulating data in relational databases
– MS Access, Oracle, Sybase, Informix, etc.
– specified by a command-line tool
– or is embedded into a general purpose programming
language, C, Pascal, Java, Perl...
• Data Definition Language (DDL) Used to create
(define) data structures such as tables, indexes,
clusters
• Data Manipulation Language (DML) is used to
store, retrieve and update data from tables
Designing A Database - A Bank
Example
• A database to track their customers and accounts.
• Data is stored in a Tables (relations) that has one
or more columns/attributes
– CUSTOMERS
Customer_Id, Name, Street, City, State, Zip
– ACCOUNTS
Customer_Id, Account_Number, Account_Type,
Date_Opened, Balance
 Customer_Id is the key for the CUSTOMERS table.
 Account_Number is the key for the ACCOUNTS table.
 Customer_Id in the ACCOUNTS table is called a Foreign Key
SQL Statements
•
•
•
•
•
•
•
•
•
•
CREATE
ALTER
INSERT
Select
delete
update
drop
commit
rollback
grant
Examples:
http://pegasus.rutgers.edu/~soon/examples.html
SQL data types
• Numeric Data Types
• Character Strings
• Date and Time
• Oracle supports the following data types:
 Numeric: BINARY_INTEGER, DEC, DECIMAL, DOUBLE
PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN,
NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN,
REAL, SMALLINT
 Date: DATE
Also stores time.
 Character: CHAR, CHARACTER, STRING, VARCHAR,
VARCHAR2
 Others: BOOLEAN, LONG, LONG RAW, RAW
SQL as Data Definition Language
• DDL is used to define the schema of the
database (a collection of database objects, e.g.
tables, views, indexes, sequences).
–
–
–
–
–
–
Create a domain
Create, Drop or Alter a table
Create or Drop an Index
Define Integrity constraints
Define access privileges to users
Define access privileges on objects
Creating Domains
 Goal is to create a standard format for data type and
size and give this a name.
 Good for standardization across all tables.
 CREATE DOMAIN d_last_name AS VARCHAR(30)
 CREATE DOMAIN d_gender AS VARCHAR(1)
 CREATE DOMAIN d_salary AS NUMBER(12,2)
 CREATE DOMAIN d_soc_sec AS VARCHAR(11)
• alter table … add/modify …
• create table tname ( colname, datatype, not null, ...);
• create table .. as <sql stmt>
• drop table tname
• create index <indexname> on tname (colname, colname);
• drop index <indexname>
• create sequence <seqname> increament by .. start with …maxvalue …
cycle;
• drop sequence
• create view <vname> as <sql select stment = select <colnames>
from tname where <condition> (read only, no insert, update or delete)
• drop view
Create, modify, drop Tables, views,
and sequences
CREATE TABLE employee
(fname
minit
lname
ssn
bdate
address
sex
salary
superssn
dno
VARCHAR2(8),
VARCHAR2(2),
VARCHAR2(9),
VARCHAR2(9) NOT NULL,
DATE,
VARCHAR2(27),
VARCHAR2(1),
NUMBER(7) NOT NULL,
VARCHAR2(9),
NUMBER(1) NOT NULL) ;
CREATE TABLE dependant (
Last_Name
d_last_name NOT NULL,
First_name
VARCHAR(18) NOT NULL,
Soc_Sec
d_soc_sec NOT NULL,
Date_of_Birth
DATE,
Employee_Soc_Sec
d_soc_sec NOT NULL );
• CREATE TABLE emp_department_1 AS
SELECT fname, minit, lname, bdate FROM
employee WHERE dno = 1 ;
• create table high_pay_emp as
select * from employee where salary > 50000
• Drop table <table_name>
Specifying Primary and Foreign keys:
• CREATE TABLE order_header (
order_number NUMBER(10,0) NOT NULL,
order_date DATE,
sales_person VARCHAR(25),
bill_to VARCHAR(35),
bill_to_address VARCHAR(45),
bill_to_city VARCHAR(20),
bill_to_state VARCHAR(2),
bill_to_zip VARCHAR(10),
PRIMARY KEY (order_number) );
Foreign key
• CREATE TABLE order_items (
order_number NUMBER(10,0) NOT NULL,
line_item NUMBER(4,0) NOT NULL,
part_number VARCHAR(12) NOT NULL,
quantity NUMBER(4,0),
PRIMARY KEY (order_number, line_item),
FORIEGN KEY (order_number)
REFERENCES order_header (order_number),
FOREIGN KEY (part_number)
REFERENCES parts (part_number) );
Creating
indexes/views/sequences
• CREATE INDEX items_index ON order_items
(order_number, line_item) ASC ;
• drop index index_name
• create view emp_dno1 as
select fname, lname, deptno from emp where
deptno=4001
Specifying Constraints on
Columns and Tables
 Constraints on attributes:
 NOT NULL - Attribute may not take a
NULL value
 DEFAULT - Store a given default value i
 PRIMARY KEY - Indicate which
attribute(s) form the primary key
 FOREIGN KEY - Indicate which
attribute(s) form a foreign key.
 UNIQUE - Indicates which
attribute(s) must have unique
values.
Referential Integrity
Constraint
• Specify the behavior for child tuples when a
parent tuple is modified.
• Action to take if referential integrity is
violated:
 SET NULL - Child tuples foreign key is
set to NULL - Orphans.
 SET DEFAULT - Set the value of the
foreign key to some default value.
 CASCADE - Child tuples are updated (or
deleted) according to the action take
on the parent tuple.
Example
CREATE TABLE order_items (
order_number
NUMBER(10,0) NOT NULL,
line_item
NUMBER(4,0) NOT NULL,
part_number
VARCHAR(12) NOT NULL,
quantity
NUMBER(4,0),
PRIMARY KEY (order_number, line_item),
FORIEGN KEY (order_number)
REFERENCES order_header (order_number)
ON DELETE SET DEFAULT
ON UPDATE CASCADE,
FOREIGN KEY (part_number)
REFERENCES parts (part_number)
);
Constraints with name
CREATE TABLE order_header (
order_number NUMBER(10,0) NOT NULL,
order_date DATE,
sales_person VARCHAR(25),
bill_to VARCHAR(35),
bill_to_address VARCHAR(45),
bill_to_city VARCHAR(20),
bill_to_state VARCHAR(2),
bill_to_zip VARCHAR(10),
CONSTRAINT order_header_pk
PRIMARY KEY (order_number) );
Removing Schema Components
with DROP
DROP SCHEMA schema_name CASCADE
DROP SCHEMA schema_name RESTRICT
DROP TABLE table_name
DROP TABLE table_name CASCADE
DROP TABLE table_name RESTRICT
DROP INDEX index_name
DROP CONSTRAINT table_name.constraint_name
Changing Schema Components
with ALTER
Changing Attributes:
ALTER TABLE student ALTER last_name VARCHAR(35);
ALTER TABLE student ALTER gpa DROP DEFAULT
ALTER TABLE student ALTER gpa SET DEFAULT 0.00;
Adding Attributes:
ALTER TABLE student ADD admission DATE;
Removing Attributes (not widely implemented):
ALTER TABLE student DROP home_phone;
SQL DML
•
•
•
•
•
•
commit
delete
insert
rollback
select
update
Data Manipulation Language
INSERT INTO tablename (column1, column2, ... columnX)
VALUES (val1, val2, ... valX);
Examples:
INSERT INTO employee (first_name, last_name, street, city, state, zip)
VALUES ("Buddy", "Rich", "123 Sticks Ln.", "Fillville", "TN", "31212");
INSERT INTO stocks (symbol, close_date, close_price)
VALUES ("IBM", "03-JUN-94", 104.25);
INSERT INTO student_grades (student_id, test_name, score, grade)
VALUES (101, "Quiz 1", 88, "B+");
Designing A Database - A Bank
Example
• A database to track their customers and accounts.
• Tables
– CUSTOMERS
Customer_Id, Name, Street, City, State, Zip
– ACCOUNTS
Customer_Id, Account_Number, Account_Type,
Date_Opened, Balance
 Customer_Id is the key for the CUSTOMERS table.
 Account_Number is the key for the ACCOUNTS table.
 Customer_Id in the ACCOUNTS table is called a Foreign Key
Relationships, Domain
• Relationships
The relationship between CUSTOMERS and
ACCOUNTS is by Customer_Id.
Since a customer may have more than one account
at the bank, we call this a One to Many
relationship. (1:N).
• Domains
A domain is a set of values that a column may
have. Domain also includes the type and length or
size of data found in each column.
Customer -Domain
Column
Customer_Id (Key)
Name
Street
City
State
Zip
Domain
Data Type
Integer
Character
Character
Character
Character
Character
Size
20
30
30
25
2
5
Accounts -- Domain
Column
Domain
Data Type Size
Customer_Id (FK)
Account_Number (Key)
Account_Type
Date_Opened
Balance
Integer
Integer
Character
Date
Real
20
15
2
12,2
Example: Customer Table
Customer_Id
Name
Address City
State
Zip
1001
Mr. Smith
123 Lexington
Smithville
KY
91232
1002
Mrs. Jones
12 Davis Ave.
Smithville
KY
91232
1003
Mr. Axe
443 Grinder Ln.
Broadville
GA
81992
1004
Mr. & Mrs. Builder
661 Parker Rd.
Streetville
GA
81990
Example: Accounts Table
Customer ID
Account_Number
Account_Type
Date Opened
Balance
1001
9987
Checking
10/12/89
4000.00
1001
9980
Savings
10/12/89
2000.00
1002
8811
Savings
01/05/92
1000.00
1003
4422
Checking
12/01/94
6000.00
1003
4433
Savings
12/01/94
9000.00
1004
3322
Savings
08/22/94
500.00
1004
1122
Checking
11/13/88
800.00
Business Rules
• Business rules allow us to specify constraints on what data
can appear in tables and what operations can be performed
on data in tables. For example:
– An account balance can never be negative.
– A Customer can not be deleted if they have an existing
(open) account.
– Money can only be transferred from a "Savings"
account to a "Checking" account.
– Savings accounts with less than a $500 balance incur a
service charge.
• How do we enforce business rules ?
 Constraints on the database
 Applications