Guide to Oracle 10 g

1. Guide to Oracle 10g Chapter 1: Client/Server Databases and the Oracle 10g Relational Database

2. Objectives • Database Systems • Describe Transaction and its properties. • Describe different types of Database Systems

3. Database Systems • Consists of • DBMS • Manages physical storage and data retrieval • Database applications • Provide interface that allows users to interact with database • Server • Computer that shares resources with other computers • Server process • Program that listens for requests for resources from clients • Responds to requests • Client • Program that requests and uses server resources

4. What is a Transaction? • A Transaction is any action that reads from and/or writes to a database. • A transaction may consist of • a simple SELECT statement to generate a list of table contents; • a series of related UPDATE statements to change the values of attributes in various tables. • a series of INSERT statements to add rows to one or more tables • or combination of SELECT, UPDATE and INSERT statement.

5. What is a Transaction? • Logical unit of work that must be either entirely completed or aborted; no intermediate steps are acceptable. • If there are a series of SQL commands in a transaction, all of these statements must be completed successfully or not at all. • If any of the SQL statement fail, the entire transaction is rolled back to the original database state that existed before the transaction started.

6. What is a Transaction? • A successful transaction changes database from one consistentstate to another • One in which all data integrity constraints are satisfied • To ensure consistency, all transactions are controlled and executed by the DBMS to guarantee database integrity. • Most real-world database transactions are formed by two or more databaserequests • A database request is equivalent of a single SQL statement in an application program or transaction

7. Evaluating Transaction Results • Improper or incomplete transactions can have devastating effect on database integrity • Some DBMSs provide means by which user can define enforceable constraints based on the business rules • Other integrity rules (referential and entity integrity) are enforced automatically by the DBMS when the table structures are properly defined.

8. Transaction Properties Each transaction must display atomicity, consistency, isolation and durability. • Atomicity • All operations of a transaction must be completed. A transaction is treated as a single, indivisible, logical unit of work. • Consistency • Permanence of database’s consistent state. • If any part of a transaction violates an integrity constraint, the entire transaction is aborted.

9. Transaction Properties Each transaction must display atomicity, consistency, isolation and durability. • Atomicity • All operations of a transaction must be completed. A transaction is treated as a single, indivisible, logical unit of work. • Consistency • Permanence of database’s consistent state. • If any part of a transaction violates an integrity constraint, the entire transaction is aborted.

10. Transaction Properties (continued) • Isolation • Data used during transaction cannot be used by second transaction until the first is completed • Durability • Once transactions are committed, they cannot be undone or lost, even in the event of a system failure. • Serializability • Ensures that the schedule for the concurrent execution of several transactions yields consistent results.

11. The Transaction Log • A DBMS uses a Transactionlog to keep track of all transactions that update the database. • The information stored in this log is used by the DBMS for a recovery requirement triggered by • a ROLLBACK statement, • a program’s abnormal termination • a system’s failure such as a disk or network failure. • While the DBMS executes transactions that modify the database, it also automatically updates the transaction log. Database Systems, 9th Edition

12. The Transaction Log • Transactionlog stores: • A record for the beginning of transaction • For each transaction component: • Type of operation being performed (update, delete, insert) • Names of objects affected by transaction • “Before” and “after” values for updated fields • Pointers to previous and next transaction log entries for the same transaction • Ending (COMMIT) of the transaction Database Systems, 9th Edition

13. Database Systems, 9th Edition

14. Types of Database Systems • Personalized Database Systems • Client Server Database Systems • Distributed Database Systems

15. Personal Database Management Systems • DBMS and database applications run on same workstation • Appear to user as a single integrated application • Used primarily for creating single-user database applications • Can also be used for some multiuser applications • Should be used only for applications that are not mission critical

16. Using a Personal Database for a Multiuser Application

17. Personal Database Management Systems (continued) • Microsoft Access • Stores all data for database in a single file with an .accdb extension • Database administrator stores .accdb file on a central file server. • Each client workstation loads the entire DBMS into main memory along with the database applications to view, insert, update or print data. • Impose heavy load on client workstations and on the network. • Problems with transaction Processing

18. Client/Server Database Management Systems • DBMS server process runs on one workstation • Database applications run on separate client workstations across network • Server DBMS process runs on the database server and listens for requests from clients. • Server sends onlyrequested data back to client rather than entire database.

19. Client/Server Database Architecture

20. Distributed Processing in Client Server Architecture • Database’s logical processing is shared among two or more physically independent sites that are connected through a network • Example: the data input/output, data selection and data validation might be performed on one client and a report based on that data might be created on another client.

22. Client/Server Database Management Systems (continued) • Generate less network traffic than personal databases • Extra features to minimize chance of failure • management and control is improved • Powerful recovery mechanisms that often operate automatically • Maintain file-based transaction log on database server

23. The Oracle 10g Client/Server Database Oracle 10g- Client/server database • Server side • DBMS server process • Oracle Net • Utility that enables network communication between client and server

24. The Oracle 10g Client/Server Database (continued) • Oracle Application Server • Used to create World Wide Web pages that allow users to access Oracle databases • Oracle client products: • SQL*Plus • Oracle 10gDeveloper Suite • Enterprise Manager

25. Distributed Databases • Stores logically related database over two or more physically independent sites which are connected via a computer network. • A Database is composed of database fragments which are located at different sites and can be replicated among various sites. Each database fragment is managed by its local database process.

27. Distributed Databases • Distributed processing does not require a distributed database, but a distributed database requires distributed processing. Each database fragment is managed by its own local database process. • Distributed processing may be based on a single database located on a single computer. Copies or parts of the database processing functions must be distributed to all storage sites. • Both distributed processing and distributed require a network to connect all components.

28. Characteristics of Distributed Management Systems (continued) • A fully distributed database management system must perform all of the functions of a centralized DBMS as follows: • Receive an end user’s request. • Validate, analyze and decompose the request. • Map the request’s logical-to-physical data components • Decompose the request into several disk I/O operations. • Search for, locate, read and validate the data. • Ensure database consistency, security and integrity. • Validate the data for the conditions, if any, specified by the request. • Present the selected data in the required format. • In addition to that, a distributed DBMS must handle all necessary functions imposed by the distribution of data and processing transparently to the end user.

30. DDBMS Components • Must include (at least) the following components: • Computer workstations: (sites or nodes) that form the network system. The distributed database system must be independent of the computer system hardware. • Network hardware and software : components that reside in each workstation. Allow all sites to interact and exchange data. Distributed database can also work on multiple platform. • Data processoror data manager • Software component residing on each computer that stores and retrieves data located at the site • May be a centralized DBMS

31. DDBMS Components • Communications media : that carry the data from one workstation to another. DDBMS must be able to support several types of communication media. • Transaction processor(application processor, transaction manager) • Software component found in each computer that requests data. • Receives and processes the application’s data requests (remote and local). Database Systems, 8th Edition

32. Types of Distributed Systems • Single-Site Processing, Single-Site Data (SPSD) • Multiple-Site Processing, Single-Site Data (MPSD) • Multiple-Site Processing, Multiple-Site Data (MPMD) (continued)

33. Single-Site Processing, Single-Site Data (SPSD) • All processing is done on single CPU or host computer (single processor or multi processor server, mainframe, midrange, or PC) • All data storage and data processing are stored on host computer’s local disk • Processing cannot be done on end user’s side of system • Typical of most mainframe and midrange computer DBMSs • DBMS is located on host computer, which is accessed by dumb terminals connected to it

34. TP and DP are embedded with the DBMS located on a single computer. The DBMS runs under a time-sharing, multi-tasking OS which allows several processes to run concurrently on a host computer accessing a single DP.

35. Multiple-Site Processing, Single-Site Data (MPSD) • Multiple processes run on different computers sharing single data repository • MPSD scenario requires network file server running conventional applications that are accessed through a network (LAN) • Many multiuser accounting applications, running under personal computer network fit such a description.

37. Multiple-Site Processing, Single-Site Data (MPSD) • The TP on each workstation acts only as a redirector to route all network data requests to the file server. • The end user sees the file server as just another hard disk because only the data storage I/O is handled by the file server’s computer. • The end user must make a direct reference to the file server in order to access remote data. • All record and file locking activities are done at the end-user location. • All data selection, search and update functions take place at the workstation, thus requiring that entire files travel through the network for processing at the workstation. Such a requirement increases network traffic, slows response time and increases communication costs.

38. Multiple-Site Processing, Single-Site Data (MPSD) • Suppose the file server computer stores a CUSTOMER table containing 10,000 rows, 50 of which have balance greater than $1,000. Suppose site A issue the following query: SELECT * FROM CUSTOMER WHERE CUS_BALANCE > 1000; • All 10,000 CUSTOMER rows must travel through the network to be evaluated at site A.

39. Multiple-Site Processing, Single-Site Data (MPSD) • A variation of the multiple-site processing, single-site data approach is know as client/server architecture, which is similar to that of the network fileserver except that all database processing is done at the server site, thus reducing network traffic. • Although both the network fileserver and the client/server systems performs multiple-site processing, the latter’s processing is distributed. • Network fileserver approach requires the database to be located at a single site, whereas the Client/server architecture is capable of supporting data at multiple sites.

40. Multiple-Site Processing, Multiple-Site Data (MPMD) • Fully distributed database management system • Support for multiple data processors and transaction processors at multiple sites • Depending on the level of support for various types of centralized DBMs, DDBMs are classified as either homogeneous or heterogeneous • HomogeneousDDBMSs • Integrate only one type of centralized DBMS over a network • Same DBMS will be running on different server platforms .

41. Multiple-Site Processing, Multiple-Site Data (MPMD) (continued) • Heterogeneous DDBMSs • Integrate different types of centralized DBMSs over a network • Fully heterogeneous DDBMSs • Support different DBMSs that may even • Support different data models (relational, hierarchical, or network) running under • Different computer systems, such as mainframes and microcomputers