1 / 23

Introduction to Relational Databases and Oracle Implementation

This lesson provides an overview of the theoretical and physical aspects of a relational database and describes the Oracle implementation of the RDBMS and ORDBMS. It also discusses the use and benefits of SQL and PL/SQL in the Oracle product set.

lcarey
Download Presentation

Introduction to Relational Databases and Oracle Implementation

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

  2. Objectives • After completing this lesson, you should be able to do the following: • Discuss the theoretical and physical aspects of a relational database • Describe the Oracle implementation of the RDBMS and ORDBMS • Describe how SQL and PL/SQL are used in the Oracle product set • Describe the use and benefits of PL/SQL

  3. Strategy and Analysis Design Build and Document Transition Production System Development Life Cycle

  4. Database Electronic spreadsheet Filing cabinet Data Storage on Different Media SALGRADE GRADE LOSAL HISAL --------- --------- --------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 DEPT DEPTNO DNAME LOC --------- -------------- ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

  5. Relational Database Concept • Dr. E. F. Codd proposed the relational model for database systems in 1970. • It is the basis for the relational database management system (RDBMS). • The relational model consists of the following: • Collection of objects or relations • Set of operators to act on the relations • Data integrity for accuracy and consistency

  6. Table Name: EMP Table Name: DEPT EMPNO ENAME JOB DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Definition of a Relational Database • A relational database is a collection of relations or two-dimensional tables. Database

  7. Server Data Models Model ofsystemin client’smind Entity model ofclient’s model Table modelof entity model Tables on disk

  8. Entity Relationship Model • Create an entity relationship diagram from business specifications or narratives • Scenario • “. . . Assign one or more employees to a department . . .” • “. . . Some departments do not yet have assigned employees . . .” DEPARTMENT #* number * name o location EMPLOYEE #* number * name o job title assigned to composed of

  9. Entity Relationship Modeling Conventions Entity Soft box Singular, unique name Uppercase Synonym in parentheses Attribute Singular name Lowercase Mandatory marked with “*” Optional marked with “o” DEPARTMENT #* number * name o location EMPLOYEE #* number * name o job title assigned to composed of Unique Identifier (UID) Primary marked with “#” Secondary marked with “(#)”

  10. 2 3 4 6 5 1 Relational Database Terminology EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------------- ------------ --------------------- -------- ---------------- ----------- -------------- ----------- 7839 KING PRESIDENT 17-NOV-81 5000 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

  11. Primary key Foreign key Primary key Relating Multiple Tables • Each row of data in a table is uniquely identified by a primary key (PK). • You can logically relate data from multiple tables using foreign keys (FK). Table Name: EMP Table Name: DEPT EMPNO ENAME JOB DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

  12. Relational Database Properties • A relational database • Can be accessed and modified by executing structured query language (SQL) statements • Contains a collection of tables with no physical pointers • Uses a set of operators

  13. SQL statementis entered Statement is sent to database SQL> SELECT loc 2 FROM dept; Database Data is displayed LOC ------------- NEW YORK DALLAS CHICAGO BOSTON Communicating with a RDBMS Using SQL

  14. Relational Database Management System Server User tables Data dictionary

  15. Oracle8: Object Relational Database Management System • User-defined data types and objects • Fully compatible with relational database • Support of multimedia and large objects • High-quality database server features

  16. Defining an Object • An object • Is a person, place, or thing • Knows things about itself and performs actions • Has an identity I am a clock. I knowmy time zone, and Ican display time.

  17. Using an Object Model • Objects model a problem to solve. • The model is stated in terms of the interactions between objects. • Object models closely resemble the real world. Buy Sell Customers Business

  18. Database Oracle Complete Solution Applications HR Financials Manufacturing ... Oracle Developer Discoverer Oracle Designer SQL* Plus SQL PL/SQL Oracle7/8 Data dictionary Data tables

  19. SQL Statements Data retrieval • SELECT • INSERT • UPDATE • DELETE • CREATE • ALTER • DROP • RENAME • TRUNCATE • COMMIT • ROLLBACK • SAVEPOINT • GRANT • REVOKE Data manipulation language (DML) Data definition language (DDL) Transaction control Data control language (DCL)

  20. About PL/SQL • PL/SQL is an extension to SQL with design features of programming languages. • Data manipulation and query statements of SQL are included within procedural units of code.

  21. PL/SQL Environment PL/SQL engine PL/SQL Procedural Statement Executor PL/SQL block PL/SQL block SQL SQL Statement Executor Oracle Server

  22. DEPT DEPTNO DNAME LOC --------- -------------- ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SALGRADE GRADE LOSAL HISAL --------- --------- --------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 Tables Used in the Course EMP EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- --------- --------- 7839 KING PRESIDENT 17-NOV-81 5000 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

  23. Summary • Relational databases are composed of relations, managed by relational operations, and governed by data integrity constraints. • The Oracle Server allows you to store and manage information by using the SQL language and PL/SQL engine. • PL/SQL is an extension to SQL with design features of programming languages.

More Related