Downloaded 39 times
Uploaded bySalman Memon
Introduction of Oracle
This document introduces Oracle9i and relational database concepts. It discusses Oracle9i features like scalability and reliability. It also explains that a relational database consists of tables related through primary and foreign keys that can be accessed using SQL. The Oracle database server allows storage and querying of data across these tables.
In this document
Powered by AI
Overview of the lesson objectives, including Oracle9i features and relational database aspects.
Key features of Oracle9i, focusing on scalability, reliability, multimedia support, and integration capabilities.
Explanation of relational and object relational database management systems, including user-defined data types and support for multimedia.
Components of Oracle's Internet platform, including network services and development tools for applications.
Overview of the phases in the system development life cycle: strategy, design, build, and transition.
Comparison of data storage methods, focusing on electronic spreadsheets, filing cabinets, and relational databases.Dr. E.F. Codd's relational model concept, emphasizing relations, integrity, and two-dimensional tables.Models of data systems, entity relationship diagrams, and their use in defining database entities and attributes.
Explanation of primary keys and foreign keys in relational databases and how to relate multiple tables.
Terminology in relational databases, properties like SQL access, table structures, and data dictionaries.
Various SQL statements used in database management, including data retrieval and manipulation commands.
List of tables used in the course along with a summary of Oracle9i's role in internet computing and relational database management.
Introduction of Oracle
- 1. I Copyright © OracleCorporation, 2001. All rights reserved. Introduction
- 2. I-2 Copyright ©Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • List the features of Oracle9i • Discuss the theoretical and physical aspects of a relational database • Describe the Oracle implementation of the RDBMS and ORDBMS
- 3. I-3 Copyright ©Oracle Corporation, 2001. All rights reserved. Oracle9i Scalability Reliability Single development model Common skill sets One management interface One vendor
- 4. I-4 Copyright ©Oracle Corporation, 2001. All rights reserved. Oracle9i
- 5. I-5 Copyright ©Oracle Corporation, 2001. All rights reserved. Oracle9i Application Server Business IntelligenceBusiness intelligence Transactional AppsTransactional Apps PortalsPortals A P A C H E IntegrationIntegration
- 6. I-6 Copyright ©Oracle Corporation, 2001. All rights reserved. Oracle9i Database MultimediaMultimedia Object Relational DataObject Relational Data MessagesMessages Documents XML Documents XML
- 7. I-7 Copyright ©Oracle Corporation, 2001. All rights reserved. Relational and Object Relational Database Management System • Relational model and object relational model • User-defined data types and objects • Fully compatible with relational database • Support of multimedia and large objects • High-quality database server features
- 8. I-8 Copyright ©Oracle Corporation, 2001. All rights reserved. Oracle Internet Platform Clients Systemmanagement Network services Databases Application servers Developmenttools Internet applications Presentation and business logic Business logic and data Any browser Any FTP client Any mail client JavaJava SQLSQLSQLSQL PL/SQLPL/SQLPL/SQLPL/SQL
- 9. I-9 Copyright ©Oracle Corporation, 2001. All rights reserved. System Development Life Cycle Strategy and analysis Design Build and document Transition Production
- 10. I-11 Copyright ©Oracle Corporation, 2001. All rights reserved. Data Storage on Different Media Electronic spreadsheet Filing cabinet Database
- 11. I-12 Copyright ©Oracle Corporation, 2001. All rights reserved. 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
- 12. I-13 Copyright ©Oracle Corporation, 2001. All rights reserved. Definition of a Relational Database A relational database is a collection of relations or two-dimensional tables. OracleOracle serverserver Table Name: EMPLOYEES Table Name: DEPARTMENTS … …
- 13. I-14 Copyright ©Oracle Corporation, 2001. All rights reserved. Data Models Model of system in client’s mind Entity model of client’s model Table model of entity model Tables on disk Oracle server
- 14. I-15 Copyright ©Oracle Corporation, 2001. All rights reserved. • 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 . . .” Entity Relationship Model EMPLOYEEEMPLOYEE #*#* numbernumber ** namename oo job titlejob title DEPARTMENTDEPARTMENT #*#* numbernumber ** namename oo locationlocation assigned to composed of
- 15. I-16 Copyright ©Oracle Corporation, 2001. All rights reserved. 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” Unique Identifier (UID) Primary marked with “#” Secondary marked with “(#)” EMPLOYEEEMPLOYEE #*#* numbernumber ** namename oo job titlejob title DEPARTMENTDEPARTMENT #*#* numbernumber ** namename oo locationlocation assigned to composed of
- 16. I-18 Copyright ©Oracle Corporation, 2001. All rights reserved. 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: EMPLOYEES Table Name: DEPARTMENTS Primary key Primary keyForeign key …
- 17. I-19 Copyright ©Oracle Corporation, 2001. All rights reserved. Relational Database Terminology 1 2 3 4 5 6
- 18. I-20 Copyright ©Oracle Corporation, 2001. All rights reserved. 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
- 19. I-21 Copyright ©Oracle Corporation, 2001. All rights reserved. Communicating with a RDBMS Using SQL SELECT department_name FROM departments; SELECT department_name FROM departments; SQL statement is entered. Oracle server Statement is sent to Oracle Server.
- 20. I-22 Copyright ©Oracle Corporation, 2001. All rights reserved. Relational Database Management System User tables Data dictionary Oracle server
- 21. I-23 Copyright ©Oracle Corporation, 2001. All rights reserved. SQL Statements SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE Data retrieval Data manipulation language (DML) Data definition language (DDL) Transaction control Data control language (DCL)
- 22. I-24 Copyright ©Oracle Corporation, 2001. All rights reserved. Tables Used in the Course EMPLOYEES DEPARTMENTS JOB_GRADES
- 23. I-25 Copyright ©Oracle Corporation, 2001. All rights reserved. Summary • The Oracle9i Server is the database for Internet computing. • Oracle9i is based on the object relational database management system. • Relational databases are composed of relations, managed by relational operations, and governed by data integrity constraints. • With the Oracle Server, you can store and manage information by using the SQL language and PL/SQL engine.
Editor's Notes
- #2 Schedule:TimingTopic 60 minutesLecture 60 minutesTotal
- #3 Lesson Aim In this lesson, you gain an understanding of the relational database management system (RDBMS) and the object relational database management system (ORDBMS). You are also introduced to the following: SQL statements that are specific to Oracle iSQL*Plus, which is used for executing SQL and for formatting and reporting purposes
- #4 Oracle9i Features Oracle offers a comprehensive high-performance infrastructure for e-business. It is called Oracle9i. Oracle9i includes everything needed to develop, deploy, and manage Internet applications. Benefits include: Scalability from departments to enterprise e-business sites Robust, reliable, available, secure architecture One development model, easy deployment options Leverage an organization’s current skillset throughout the Oracle platform (including SQL, PL/SQL, Java, and XML) One management interface for all applications Industry standard technologies, no proprietary lock-in
- #5 Oracle9i There are two products, Oracle9i Application Server and Oracle9i Database, that provide a complete and simple infrastructure for Internet applications.
- #6 Oracle9i Application Server The Oracle9i Application Server (Oracle9iAS) runs all your applications. The Oracle9i Database stores all your data. Oracle9i Application Server is the only application server to include services for all the different server applications you will want to run. Oracle9iAS can run your: Portals or Web sites Java transactional applications Business intelligence applications It also provides integration between users, applications, and data throughout your organization. Instructor Note Apache is used to maintain an open-source HTTP server software product for various modern desktop and server operating systems. It provides a secure, efficient and extensible server with HTTP services in synchronization with the current HTTP standards. Oracle9iAS is powered by Apache, which is the de facto industry standard.
- #7 Oracle9i Database The roles of the two products are very straightforward. Oracle9i Database manages all your data. This is not just the object relational data that you expect an enterprise database to manage. It can also be unstructured data like: Spreadsheets Word documents PowerPoint presentations XML Multimedia data types like MP3, graphics, video, and more The data does not even have to be in the database. Oracle9i Database has services through which you can store metadata about information stored in file systems. You can use the database server to manage and serve information wherever it is located. Instructor Note XML (the Extensible Markup Language) was first ratified by the W3C (World Wide Web Consortium) as the standard for information exchange on the Internet in February 1998. Since then it has been rapidly gaining momentum as the development community has begun to appreciate its potential and as vendors have started to deliver tools to support it. XML specifies a rigorous, text-based way to represent the structure inherent in data so that it can be authored and interpreted unambiguously.
- #8 About the Oracle Server The Oracle9i server supports both the relational and object relation models. The Oracle server extends the data modeling capabilities to support an object relational database model that brings object-oriented programming, complex data types, complex business objects, and full compatibility with the relational world. It includes several features for improved performance and functionality of online transaction processing (OLTP) applications, such as better sharing of run-time data structures, larger buffer caches, and deferrable constraints. Data warehouse applications will benefit from enhancements such as parallel execution of insert, update, and delete operations; partitioning; and parallel-aware query optimization. Operating within the Network Computing Architecture (NCA) framework, Oracle9i supports client-server and Web-based applications that are distributed and multitiered. Oracle9i can scale tens of thousands of concurrent users, support up to 512 petabytes of data (a petabyte is 1,000 terabytes), and can handle any type of data, including text, spatial, image, sound, video, and time series as well as traditional structured data. For more information, see Oracle9i Concepts. Instructor Note The Object Relation Database Management System features are available with release 8 and higher. Oracle7 is a relational database management system and Oracle8, 8i, and 9i are object relational database management systems.
- #9 Oracle Internet Platform Oracle offers a comprehensive high-performance Internet platform for e-commerce and data warehousing. This integrated platform includes everything needed to develop, deploy, and manage Internet applications. The Oracle Internet Platform is built on three core pieces: Browser-based clients to process presentation Application servers to execute business logic and serve presentation logic to browser-based clients Databases to execute database-intensive business logic and serve data Oracle offers a wide variety of the most advanced graphical user interface (GUI) driven development tools to build business applications, as well as a large suite of software applications for many areas of business and industry. Stored procedures, functions, and packages can be written by using SQL, PL/SQL, or Java.
- #10 System Development Life Cycle From concept to production, you can develop a database by using the system development life cycle, which contains multiple stages of development. This top-down, systematic approach to database development transforms business information requirements into an operational database. Strategy and Analysis Study and analyze the business requirements. Interview users and managers to identify the information requirements. Incorporate the enterprise and application mission statements as well as any future system specifications. Build models of the system. Transfer the business narrative into a graphical representation of business information needs and rules. Confirm and refine the model with the analysts and experts. Design Design the database based on the model developed in the strategy and analysis phase. Build and Document Build the prototype system. Write and execute the commands to create the tables and supporting objects for the database. Develop user documentation, Help text, and operations manuals to support the use and operation of the system.
- #11 System Development Life Cycle (continued) Transition Refine the prototype. Move an application into production with user acceptance testing, conversion of existing data, and parallel operations. Make any modifications required. Production Roll out the system to the users. Operate the production system. Monitor its performance, and enhance and refine the system. Note: The various phases of the system development life cycle can be carried out iteratively. This course focuses on the build phase of the system development life cycle.
- #12 Storing Information Every organization has some information needs. A library keeps a list of members, books, due dates, and fines. A company needs to save information about employees, departments, and salaries. These pieces of information are called data. Organizations can store data on various media and in different formats, such as a hard-copy document in a filing cabinet or data stored in electronic spreadsheets or in databases. A database is an organized collection of information. To manage databases, you need database management systems (DBMS). A DBMS is a program that stores, retrieves, and modifies data in the database on request. There are four main types of databases: hierarchical, network, relational, and more recently object relational.
- #13 Relational Model The principles of the relational model were first outlined by Dr. E. F. Codd in a June 1970 paper called “A Relational Model of Data for Large Shared Data Banks.” In this paper, Dr. Codd proposed the relational model for database systems. The more popular models used at that time were hierarchical and network, or even simple flat file data structures. Relational database management systems (RDBMS) soon became very popular, especially for their ease of use and flexibility in structure. In addition, a number of innovative vendors, such as Oracle, supplemented the RDBMS with a suite of powerful application development and user products, providing a total solution. Components of the Relational Model Collections of objects or relations that store the data A set of operators that can act on the relations to produce other relations Data integrity for accuracy and consistency For more information, see E. F. Codd, The Relational Model for Database Management Version 2 (Reading, Mass.: Addison-Wesley, 1990).
- #14 Definition of a Relational Database A relational database uses relations or two-dimensional tables to store information. For example, you might want to store information about all the employees in your company. In a relational database, you create several tables to store different pieces of information about your employees, such as an employee table, a department table, and a salary table.
- #15 Data Models Models are a cornerstone of design. Engineers build a model of a car to work out any details before putting it into production. In the same manner, system designers develop models to explore ideas and improve the understanding of the database design. Purpose of Models Models help communicate the concepts in people’s minds. They can be used to do the following: Communicate Categorize Describe Specify Investigate Evolve Analyze Imitate The objective is to produce a model that fits a multitude of these uses, can be understood by an end user, and contains sufficient detail for a developer to build a database system.
- #16 ER Modeling In an effective system, data is divided into discrete categories or entities. An entity relationship (ER) model is an illustration of various entities in a business and the relationships between them. An ER model is derived from business specifications or narratives and built during the analysis phase of the system development life cycle. ER models separate the information required by a business from the activities performed within a business. Although businesses can change their activities, the type of information tends to remain constant. Therefore, the data structures also tend to be constant. Benefits of ER Modeling Documents information for the organization in a clear, precise format Provides a clear picture of the scope of the information requirement Provides an easily understood pictorial map for the database design Offers an effective framework for integrating multiple applications Key Components Entity: A thing of significance about which information needs to be known. Examples are departments, employees, and orders. Attribute: Something that describes or qualifies an entity. For example, for the employee entity, the attributes would be the employee number, name, job title, hire date, department number, and so on. Each of the attributes is either required or optional. This state is called optionality. Relationship: A named association between entities showing optionality and degree. Examples are employees and departments, and orders and items.
- #17 ER Modeling (continued) Entities To represent an entity in a model, use the following conventions: Soft box with any dimensions Singular, unique entity name Entity name in uppercase Optional synonym names in uppercase within parentheses: ( ) Attributes To represent an attribute in a model, use the following conventions: Use singular names in lowercase. Tag mandatory attributes, or values that must be known, with an asterisk: *. Tag optional attributes, or values that may be known, with the letter o. Relationships
- #18 ER Modeling (continued) Relationships Each direction of the relationship contains: A label, for example, taught by or assigned to An optionality, either must be or may be A degree, either one and only one or one or more Note: The term cardinality is a synonym for the term degree. Each source entity {may be | must be} relationship name {one and only one | one or more} destination entity. Note: The convention is to read clockwise. Unique Identifiers A unique identifier (UID) is any combination of attributes or relationships, or both, that serves to distinguish occurrences of an entity. Each entity occurrence must be uniquely identifiable. Tag each attribute that is part of the UID with a number symbol: # Tag secondary UIDs with a number sign in parentheses: (#)
- #19 Relating Multiple Tables Each table contains data that describes exactly one entity. For example, the EMPLOYEES table contains information about employees. Categories of data are listed across the top of each table, and individual cases are listed below. Using a table format, you can readily visualize, understand, and use information. Because data about different entities is stored in different tables, you may need to combine two or more tables to answer a particular question. For example, you may want to know the location of the department where an employee works. In this scenario, you need information from the EMPLOYEES table (which contains data about employees) and the DEPARTMENTS table (which contains information about departments). With an RDBMS you can relate the data in one table to the data in another by using the foreign keys. A foreign key is a column or a set of columns that refer to a primary key in the same table or another table. You can use the ability to relate data in one table to data in another to organize information in separate, manageable units. Employee data can be kept logically distinct from department data by storing it in a separate table. Guidelines for Primary Keys and Foreign Keys You cannot use duplicate values in a primary key. Primary keys generally cannot be changed. Foreign keys are based on data values and are purely logical, not physical, pointers. A foreign key value must match an existing primary key value or unique key value, or else be null. A foreign key must reference either a primary key or unique key column.
- #20 Terminology Used in a Relational Database A relational database can contain one or many tables. A table is the basic storage structure of an RDBMS. A table holds all the data necessary about something in the real world, such as employees, invoices, or customers. The slide shows the contents of the EMPLOYEES table or relation. The numbers indicate the following: 1. A single row or table representing all data required for a particular employee. Each row in a table should be identified by a primary key, which allows no duplicate rows. The order of rows is insignificant; specify the row order when the data is retrieved. 2. A column or attribute containing the employee number. The employee number identifies a unique employee in the EMPLOYEES table. In this example, the employee number column is designated as the primary key. A primary key must contain a value, and the value must be unique. 3. A column that is not a key value. A column represents one kind of data in a table; in the example, the salary of all the employees. Column order is insignificant when storing data; specify the column order when the data is retrieved. 4. A column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other. A foreign key refers to a primary key or a unique key in the same table or in another table. In the example, DEPARTMENT_ID uniquely identifies a department in the DEPARTMENTS table. 5. A field may have no value in it. This is called a null value. In the EMPLOYEES table, only employees who have a role of sales representative have a value in the COMMISSION_PCT (commission) field. 6.A field can be found at the intersection of a row and a column. There can be only one value in it.
- #21 Properties of a Relational Database In a relational database, you do not specify the access route to the tables, and you do not need to know how the data is arranged physically. To access the database, you execute a structured query language (SQL) statement, which is the American National Standards Institute (ANSI) standard language for operating relational databases. The language contains a large set of operators for partitioning and combining relations. The database can be modified by using the SQL statements.
- #22 Structured Query Language Using SQL, you can communicate with the Oracle server. SQL has the following advantages: Efficient Easy to learn and use Functionally complete (With SQL, you can define, retrieve, and manipulate data in the tables.)
- #23 Relational Database Management System Oracle provides a flexible RDBMS called Oracle9i. Using its features, you can store and manage data with all the advantages of a relational structure plus PL/SQL, an engine that provides you with the ability to store and execute program units. Oracle9i also supports Java and XML. The Oracle server offers the options of retrieving data based on optimization techniques. It includes security features that control how a database is accessed and used. Other features include consistency and protection of data through locking mechanisms. The Oracle9i server provides an open, comprehensive, and integrated approach to information management. An Oracle server consists of an Oracle database and an Oracle server instance. Every time a database is started, a system global area (SGA) is allocated, and Oracle background processes are started. The system global area is an area of memory used for database information shared by the database users. The combination of the background processes and memory buffers is called an Oracle instance.
- #24 SQL Statements Oracle SQL complies with industry-accepted standards. Oracle Corporation ensures future compliance with evolving standards by actively involving key personnel in SQL standards committees. Industry-accepted committees are the American National Standards Institute (ANSI) and the International Standards Organization (ISO). Both ANSI and ISO have accepted SQL as the standard language for relational databases.
- #25 Tables Used in the Course The following main tables are used in this course: EMPLOYEES table, which gives details of all the employees DEPARTMENTS table, which gives details of all the departments JOB_GRADES table, which gives details of salaries for various grades Note: The structure and data for all the tables are provided in Appendix B. Instructor Note This course uses the Sample Schema that is shipped with the Oracle9i database. The full Sample Schema consists of several subschemas. One of the subschemas is called HR (Human Resources). This course uses the HR portion of the Sample Schema. Some of the rows from the HR schema are removed to simplify examples in this courseware. Have the students turn to Appendix B to review the tables used in the course.
- #26 Summary Relational database management systems are composed of objects or relations. They are managed by operations and governed by data integrity constraints. Oracle Corporation produces products and services to meet your relational database management system needs. The main products are the Oracle9i Database Server, with which you can store and manage information by using SQL, and the Oracle9i Application Server with which you can run all of your applications. SQL The Oracle Server supports ANSI standard SQL and contains extensions. SQL is the language used to communicate with the server to access, manipulate, and control data.