Oracle a relational database system rdbms
Download
1 / 50

Oracle: A Relational Database System RDBMS - PowerPoint PPT Presentation


  • 149 Views
  • Uploaded on

Oracle: A Relational Database System (RDBMS). Vandana Janeja 10 th February 2004 For : Database Systems 22:198:603 Prof. Vijay Atluri Adapted from material by Dr.Richard Holowczak and Dr.Soon Chun. Outline. Background SQL*PLUS SQL DDL DML DCL Examples. ODBC Driver. Oracle

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Oracle: A Relational Database System RDBMS' - Anita


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Oracle a relational database system rdbms

Oracle: A Relational Database System (RDBMS)

Vandana Janeja

10th February 2004

For : Database Systems22:198:603

Prof. Vijay Atluri

Adapted from material by Dr.Richard Holowczak and Dr.Soon Chun


Outline
Outline

  • Background

  • SQL*PLUS

  • SQL

    • DDL

    • DML

    • DCL

    • Examples


Components

ODBC Driver

Oracle

Sybase

Access

Components

Oracle Server

Front end application

VB, Access, Excel etc.

Sybase

Server

Access DB

Other details at: http://cimic.rutgers.edu/~holowcza/present/oracle97/index.htm


Core database engine
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


Interface tools to access data
Interface Tools to access data

  • SQL*Plus: a command line interface

  • Developer (Developer/2000): forms, reports, and graphical interfaces


Connectivity
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 non-oracle 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


Sql plus
SQL*Plus

  • Command line tool that process user’s SQL statements

  • Requires Oracle account

DDL

Data Definition

SQL

DML

Data Manipulation

DCL

Data Control


Same as your pegasus password

Password: MBAgrad2004


Changing sql plus password
Changing SQL*Plus password

passw command

Getting help

Help command


Structured query language sql
Structured Query Language (SQL)

  • The standard query language for creating and manipulating and controlling 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

  • Data Control Language used to control the access to the database objects created using DDL and DML


Sql as data definition language
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, Drop or Alter a table

    • Create or Drop an Index

    • Define Integrity constraints

    • Define access privileges to users

    • Define access privileges on objects



Create modify drop tables views and sequences
Create, modify, drop Tables, views, and sequences

Table emp

Name Type

----------------------------------------------------------------

EMPID NUMBER(5)

FNAME VARCHAR2(20)

LNAME VARCHAR2(20)

SEX VARCHAR2(1)

SSN VARCHAR2(9)

SALARY NUMBER(8)

DEPTNO NUMBER(5)

CREATE TABLE emp (

empid NUMBER(5),

fname VARCHAR2(20),

lname VARCHAR2(20),

sex VARCHAR2(1),

ssn VARCHAR2(9),

salary NUMBER(8),

deptno NUMBER(5) );


Data types
Data Types

  • A table is made up of one or more columns

  • Each column is given a name and a data type that reflects the kind of data it will store.

  • Oracle supports four basic data types

    • CHAR

    • NUMBER

    • DATE

    • RAW.

    • There are also a few additional variations on the RAW and CHAR data types.


  • VARCHAR2

    • Character data type.

    • Can contain letters, numbers and punctuation.

    • The syntax : VARCHAR2(size) where size is the maximum number of alphanumeric characters the column can hold.

    • In Oracle8, the maximum size of a VARCHAR2 column is 4,000 bytes.

  • NUMBER

    • Numeric data type.

    • Can contain integer or floating point numbers only.

    • The syntax : NUMBER(precision, scale) where precision is the total size of the number including decimal point and scale is the number of places to the right of the decimal.

    • For example, NUMBER(6,2) can hold a number between -999.99 and 999.99.


  • DATE

    • Date and Time data type.

    • Can contain a date and time portion in the format: DD-MON-YY HH:MI:SS.

    • No additional information needed when specifying the DATE data type.

    • the time of 00:00:00 is used as a default.

    • The output format of the date and time can be modified

  • RAW –

    • Free form binary data.

    • Can contain binary data up to 255 characters.

    • Data type LONG RAW can contain up to 2 gigabytes of binary data.

    • RAW and LONG RAW data cannot be indexed and can not be displayed or queried in SQL*Plus.

    • Only one RAW column is allowed per table.

  • LOB –

    • Large Object data types.

    • These include BLOB (Binary Large OBject) and CLOB (Character Large OBject).

    • More than one LOB column can appear in a table.

    • These data types are the prefferred method for storing large objects such as text documents (CLOB), images, or video (BLOB).


Create modify drop tables views and sequences1
Create, modify, drop Tables, views, and sequences

Table emp

Name Type

----------------------------------------------------------------

EMPID NUMBER(5)

FNAME VARCHAR2(20)

LNAME VARCHAR2(20)

SEX VARCHAR2(1)

SSN VARCHAR2(9)

SALARY NUMBER(8)

DEPTNO NUMBER(5)

CREATE TABLE emp (

empid NUMBER(5),

fname VARCHAR2(20),

lname VARCHAR2(20),

sex VARCHAR2(1),

ssn VARCHAR2(9),

salary NUMBER(8),

deptno NUMBER(5) );


Insert
Insert

  • INSERT INTO emp VALUES (501,'JOHN','DOE','M','500000001',30000,4001);


Insert1

http://cimic.rutgers.edu/~vandana/DatabaseClass/series.htm

Insert

  • INSERT INTO emp VALUES (502,'JOHN','SMITH','M','500000002',40000,4001);

  • INSERT INTO emp VALUES (503,'SEAN','LEE','M','500000003',30000,4001);

  • INSERT INTO emp VALUES (504,'EVAN','SEAN','M','500000004',50000,4002);

  • INSERT INTO emp VALUES (505,'REBECCA','SEAN','F','500000005',30000,4002);

  • INSERT INTO emp VALUES (506,'TIM','DUNCAN','M','500000006',30000,4002);

  • NSERT INTO emp VALUES (507,'ROBERT','DUVAL','M','500000007',30000,4002);

  • INSERT INTO emp VALUES (508,'CLINT','JOHNSON','M','500000008',30000,4002);

  • INSERT INTO emp VALUES (509,'SARRAH','MCMILLAN','F','500000009',60000,4003); INSERT INTO emp VALUES (510,'DAVID','LIMB','M','500000010',30000,4003);

  • INSERT INTO emp VALUES (511,'DAVID','BOWE','M','500000011',30000,4003);

  • INSERT INTO emp VALUES (512,'SMITH','CLARK','M','500000012',50000,4003);

  • INSERT INTO emp VALUES (513,'TED','KENNEDY','M','',30000,4003);

  • INSERT INTO emp VALUES (514,'RONALD','REAGAN','M','500000014',30000,4003); INSERT INTO emp VALUES (515,'FRANKLIN','ROSSEVELT','M','500000015',30000,4003); INSERT INTO emp VALUES (516,'George','BUSH','M','500000016',30000,4004);

  • INSERT INTO emp VALUES (517,'SAM','MALONE','M','',30000,4004);

  • INSERT INTO emp VALUES (518,'NANCY','REAGAN','F','500000018',30000,4004);

  • INSERT INTO emp VALUES (519,'HILLARY','CLINTON','F','500000019',30000,4004); INSERT INTO emp VALUES (520,'MARRY','GEORGIA','F','500000020',30000,4004);



Changing table components with alter
Changing Table Components with ALTER

  • Changing Attributes:alter table emp modify (lname varchar(30) not null); describe emp;

  • Adding Attributes:alter table emp add (manager varchar(11)); describe emp;

  • Adding Constraints

    alter table emp add constraint pk_empid primary key (empid); describe emp;

  • select constraint_name, constraint_type, search_condition from user_constraints where table_name = 'EMP';

  • Dropping constraints

    ALTER TABLE emp DROP CONSTRAINT pk_empid;



Another table dept
Another table Dept

  • CREATE TABLE dept (deptno NUMBER(5) NOT NULL, name VARCHAR2(20) NOT NULL, building VARCHAR2(20), CONSTRAINT pk_deptno PRIMARY KEY (deptno) );


Insert2
Insert

  • INSERT INTO dept VALUES (4001, 'SHOES', 'BUILDING I');

  • INSERT INTO dept VALUES (4002, 'WOMAN CLOTHING', 'BUILDING II');

  • INSERT INTO dept VALUES (4003, 'MEN CLOTHING', 'BUILDING II');

  • INSERT INTO dept VALUES (4004, 'KITCHEN APPLIANCES', 'MAIN BUILDING');


Adding foreign key constraint
Adding Foreign Key constraint

  • alter table emp add CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE CASCADE


Create table syntax
Create Table syntax

  • CREATE TABLE <tabname> (

    Attr_name data_type(size) constraint,

    …………….

    PRIMARY KEY (Attr_name , Attr_name ),

    FORIEGN KEY (fk1)

    REFERENCES Other_tab1(fk1),

    FOREIGN KEY (fk2)

    REFERENCES Other_tab2 (fk2) );



Specifying constraints on columns and tables
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.



Removing schema components with drop
Removing Schema Components with DROP

DROP TABLE table_name

DROP TABLE table_name CASCADE

DROP TABLE table_name RESTRICT

DROP INDEX index_name

DROP CONSTRAINT table_name.constraint_name



Sql dml
SQL DML

  • Insert

  • Select

  • Delete

  • update

  • commit

  • rollback



Dcl data control language
DCL: Data Control Language

  • Controlling Access to database objects such as tables and views

  • Example : Granting “Mary” the access to Table “student” (for inserting, updating and deleting)

    • GRANT INSERT, UPDATE, DELETE ON Emp TO Mary

    • GRANT <privileges> ON <object name>      TO <grantee> [ <comma> <grantee> ... ]        [ WITH GRANT OPTION ]

    • WITH GRANT OPTION: allows the grantee to further grant privileges

    • Can be limited to a column of a table, Ex: GRANT UPDATE(name) ON emp TO Mary

    • To revoke privileges : REVOKE


Syntax of commands
Syntax of commands

  • alter table … add/modify …column

  • 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


Sql plus commands
SQL*Plus Commands

  • Example of SQL stmt: select * from tab;

  • describe <table name>

  • list : list current sql stmt

  • edit : edit current sql statement (or ! Editorname <filename>) where editor name is vi or pico

  • input : add one or more lines to sqlplus buffer

  • spool : start directing output of sql statements to a file

  • spool off : turn the spool off

  • 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


Typing a SQL command

Saving SQL command in a file

Editing SQL command in a file


vi Editor

Executing the SQL command in a file


Editing SQL command in a notepad

Copy and paste the command


Basic vi primer for more man vi
Basic vi primerFor more - “man vi”

  • Esc key to toggle between edit and insert mode

  • <- -> down-arrow up-arrow arrow keys move the cursor

  • h j k l same as arrow keys

  • x delete a character

  • dw delete a word

  • dd delete a line

  • 3dd delete 3 lines

  • u undo previous change

  • ZZ exit vi, saving changes

  • :q!CR quit, discarding changes

  • :wq write and save changes



Designing a database a bank example
Designing A Database - A Bank Example

  • A database to track their customers and accounts.

  • Tables

    • CUSTOMERSCustomer_Id, Name, Street, City, State, Zip

    • ACCOUNTSCustomer_Id, Account_Number, Account_Type, Date_Opened, Balance

      • Customer_Id is the keyfor the CUSTOMERS table.

      • Account_Number is the key for the ACCOUNTS table.

      • Customer_Id in the ACCOUNTS table is called a Foreign Key


Customer
Customer

Column Data Type Size

Customer_Id (Key) Integer 20

Name Character 30

Street Character 30

City Character 25

State Character 2

Zip Character 5


Accounts
Accounts

Column Data Type Size

Customer_Id (FK) Integer 20

Account_Number (Key) Integer 15

Account_Type Character 2

Date_Opened Date

Balance Real 12,2


Example customer table
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
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

  • 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


  • CREATE TABLE students (studentid NUMBER(5,0), name VARCHAR2(25), major VARCHAR2(15), gpa NUMBER(6,3), tutorid NUMBER(5,0));

    INSERT INTO students VALUES (101, 'Bill', 'CIS', 3.45, 102);

    INSERT INTO students VALUES (102, 'Mary', 'CIS', 3.10, NULL);

    INSERT INTO students VALUES (103, 'Sue', 'Marketing', 2.95, 102);

    INSERT INTO students VALUES (104, 'Tom', 'Finance', 3.5, 106);

    INSERT INTO students VALUES (105, 'Alex', 'CIS', 2.75, 106);

    INSERT INTO students VALUES (106, 'Sam', 'Marketing', 3.25, 103);

    INSERT INTO students VALUES (107, 'Jane', 'Finance', 2.90, 102);

    Example table COURSES:Create table courses(studentid NUMBER(5,0) NOT NULL, coursenumber VARCHAR2(15) NOT NULL, coursename VARCHAR2(25), semester VARCHAR2(10), year NUMBER(4,0), grade VARCHAR2(2));

    INSERT INTO courses VALUES (101, 'CIS3400', 'DBMS I', 'FALL', 1997, 'B+');

    INSERT INTO courses VALUES (101, 'CIS3100', 'OOP I', 'SPRING', 1999, 'A-');

    INSERT INTO courses VALUES (101, 'MKT3000', 'Marketing', 'FALL', 1997, 'A'); INSERT INTO courses VALUES (102, 'CIS3400', 'DBMS I', 'SPRING', 1997, 'A-'); INSERT INTO courses VALUES (102, 'CIS3500', 'Network I', 'SUMMER', 1997, 'B'); INSERT INTO courses VALUES (102, 'CIS4500', 'Network II', 'FALL', 1997, 'B+'); INSERT INTO courses VALUES (103, 'MKT3100', 'Advertizing', 'SPRING', 1998, 'A'); INSERT INTO courses VALUES (103, 'MKT3000', 'Marketing', 'FALL', 1997, 'A'); INSERT INTO courses VALUES (103, 'MKT4100', 'Marketing II', 'SUMMER', 1998, 'A-');


    StudentID VARCHAR2(25), major VARCHAR2(15), gpa NUMBER(6,3), tutorid NUMBER(5,0));

    Name

    Major

    GPA

    TutorId

    101

    Bill

    CIS

    3.45

    102

    102

    Mary

    CIS

    3.1

    103

    Sue

    Marketing

    2.95

    102

    104

    Tom

    Finance

    3.5

    106

    105

    Alex

    CIS

    2.75

    106

    106

    Sam

    Marketing

    3.25

    103

    107

    Jane

    Finance

    2.9

    102


    Some example queries
    Some Example queries VARCHAR2(25), major VARCHAR2(15), gpa NUMBER(6,3), tutorid NUMBER(5,0));

    • Average GPA of all students

      • SELECT AVG(gpa) FROM students; AVG(GPA) ---------- 3.12857143

    • Average GPA of Finance and CIS students:

      • SELECT AVG(gpa) FROM students WHERE major = 'CIS' OR major = 'Finance'; AVG(GPA) ---------- 3.14

    • Select all students whose major is CIS

      • Select * from students where major =‘CIS’


    ad