1 / 55

Oracle: A Relational Database System (RDBMS)

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

matthew-mendoza
Download Presentation

Oracle: A Relational Database System (RDBMS)

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. Outline • Background • SQL*PLUS • SQL • DDL • DML • DCL • Examples

  3. 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

  4. 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

  5. Interface Tools to access data • SQL*Plus: a command line interface • Developer (Developer/2000): forms, reports, and graphical interfaces

  6. 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

  7. SQL*Plus • Command line tool that process user’s SQL statements • Requires Oracle account DDL Data Definition SQL DML Data Manipulation DCL Data Control

  8. Same as your pegasus password Password: MBAgrad2004

  9. Changing SQL*Plus password passw command Getting help Help command

  10. 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

  11. Typing a SQL command Saving SQL command in a file Editing SQL command in a file

  12. vi Editor Executing the SQL command in a file

  13. Editing SQL command in a notepad Copy and paste the command

  14. 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

  15. 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

  16. 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

  17. Create Table

  18. Create, modify, drop Tables, views, and sequences CREATE TABLE employee ( emp_number char(4), fname VARCHAR2(8), minit VARCHAR2(2), lname VARCHAR2(9), ssn VARCHAR2(9) NOT NULL, bdate DATE, address VARCHAR2(27), sex VARCHAR2(1), salary NUMBER(7) NOT NULL, superssn VARCHAR2(9), dno NUMBER(1) NOT NULL, PRIMARY KEY (emp_number)) ; 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 );

  19. 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.

  20. 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.

  21. 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).

  22. Foreign Key

  23. Primary key • 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) );

  24. 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) );

  25. 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

  26. Example : Create/ Drop Table • 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> • Drop table high_pay_emp

  27. Other Constraints

  28. 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.

  29. 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.

  30. 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) );

  31. 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) );

  32. Drop

  33. 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

  34. Alter

  35. Given a table - students CREATE TABLE student (studentid NUMBER(5,0), first_name VARCHAR2(25), last_name Varchar2(10), major VARCHAR2(15), gpa NUMBER(6,3) default 4.0, tutorid NUMBER(5,0), home_phone varchar2(10));

  36. Changing Table Components with ALTER • Changing Attributes:ALTER TABLE student MODIFY last_name VARCHAR(35); • alter table student modify gpa NUMBER(6,3) default 0.0; • Adding Attributes:ALTER TABLE student ADD admission DATE; • Removing Attributes (not widely implemented):ALTER TABLE student DROP column home_phone

  37. 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

  38. DML

  39. SQL DML • commit • delete • insert • rollback • select • update

  40. 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+");

  41. Delete and Update commands • DELETE FROM <table name> WHERE <where-clause> • If the WHERE clause is omitted, all rows in the table will be deleted. • UPDATE <table name> SET<column name> = <expression> WHERE <where-clause> • UPDATE employee SET salary = salary * 1.03 WHERE dno = (SELECT dno FROM department WHERE dname = 'MARKETING');

  42. 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 Student 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 Student TO Mary • To revoke privileges : REVOKE

  43. A Bank Example

  44. 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

  45. 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

  46. 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

  47. 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

  48. 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

  49. 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

  50. Create, modify, drop Tables, views, and sequences CREATE TABLE employee (fname VARCHAR2(8), minit VARCHAR2(2), lname VARCHAR2(9), ssn VARCHAR2(9) NOT NULL, bdate DATE, address VARCHAR2(27), sex VARCHAR2(1), salary NUMBER(7) NOT NULL, superssn VARCHAR2(9), dno 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 );

More Related