Introduction to Oracle

1. Introduction to Oracle Database Systems Lecture by Ty Rasmey

2. Before Database Database architecture overview Buzzwords Intro to Oracle Comments on homework Agenda

3. Information was kept in files: • Each field describes one piece of information about student • Fields are separated by commas • A record is a collection of related fields • Each record is a separate line Before Database

4. Proliferation of data management programs to deal with different file formats Redundant data stored in files Data files may contain inconsistent data Problems with Files

5. Databasestores all organizational data in a central location Good database design eliminates redundant data to reduce the possibility of inconsistent data Single application called the database management system (DBMS) performs all routine data handling operations Database administrator (DBA): person responsible for installing, administering, and maintaining the database Database Approach

6. hierarchical model network model relational model object oriented model. Types of database models

7. Early Databases – Hierarchical Structure

8. Relational Databases

9. Object-oriented Example Students Courses

10. Entity:an object about which you want to store data • Relationships:links that show how different records are related • Key Fields: establish relationships among records in different tables • Five main types of key fields: • primary keys • candidate keys • foreign keys • composite keys Relational Database Terms

11. Primary key • Value must be unique for each record • Serves to identify the record • Present in every record • Can’t be NULL • Should be numeric Primary Keys

12. Candidate key • Any field that could be used as the primary key • Should be a unique, unchanging numeric field Candidate Keys

13. Foreign key:a field in a table that is a primary key in another table Foreign key creates a relationship between the two tables Foreign key value must exist in the table where it is a primary key Foreign Keys

14. Composite key: a unique key that you create by combining two or more fields Usually comprised of fields that are primary keys in other tables Composite Keys

15. Before Database Database architecture overview Buzzwords Intro to Oracle Comments on homework Agenda

16. Grid Architecture: With grid computing, groups of independent, modular hardware and software components can be connected and rejoined on demand to meet the changing needs of businesses. • Application Architecture: • Client/server • Multitier Database Architecture

17. Client/server database • Takes advantage of distributed processing and networked computers by distributing processing across multiple computers • DBMS server process runs on one workstation, and the database applications run on separate client workstations across the network • Preferred for database applications that retrieve and manipulate small amounts of data from databases containing large numbers of records because they minimize network traffic and improve response times • Organizations generally use a client/server database if the database will have more than 10 simultaneous users and if the database is mission critical Client/Server Database Management Systems

18. Client/Server Database Architecture

19. Oracle12 C is the latest release of Oracle Corporation’s relational database All Oracle server- and client-side programs use Oracle Net, a utility that enables the network communication between the client and the server The Oracle Client/Server Database

20. SQL*Plus • for creating and testing command-line SQL queries and executing PL/SQL procedural programs • Oracle 11g Developer Suite • for developing database applications including the following Developer tools: • Forms Builder • for creating custom user applications • Reports Builder • for creating reports for displaying, printing, and distributing summary data • Enterprise Manager • for performing database administration tasks such as creating new user accounts and configuring how the DBMS stores and manages data Client-SideUtilities

21. To avoid creating tables that contain redundant data, group related items that describe a single entity together in a common table Do not create tables that duplicate values many times in different rows When creating a database and inserting data values, you must specify the data type for each column Recall that primary key fields should use a number data type to avoid typographical, punctuation, and case variation errors Design Principles

22. connection (ODBC, JDBC) DB System from lecture #1 “Two tier database system” Database server(someone else’sC program) Applications Data files

23. 1,2,3 tiers

24. Abstractly (DB) system layers may include Application DB infrastructure DB driver Transport DB engine Storage

25. Why? Gui designer Tester App programmer DBA DB programmer

26. Bureaucracy… Database architecture overview Buzzwords Intro to Oracle Comments on homework Agenda

27. Terms… • ODBC • ADO • OLE-DB • MDAC/UDA • JDBC • ORM

28. Various standards have been developed for accessing database servers. • Some of the important standards are • ODBC (Open Database Connectivity) is the early standard for relational databases. • OLE DB is Microsoft’s object-oriented interface for relational and other databases. • ADO (Active Data Objects) is Microsoft’s standard providing easier access to OLE DB data for the non-object-oriented programmer. ODBC, OLEDB and ADO

29. ODBC • Open Database Connectivity (ODBC) is a standard software API method for using database management systems (DBMS) • Maximum interoperability

30. ODBC Examples of common tasks: • Selecting a data source and connecting to it. • Submitting an SQL statement for execution. • Retrieving results (if any). • Processing errors. • Committing or rolling back the transaction enclosing the SQL statement. • Disconnecting from the data source.

31. MDAC… UDA • UDA (Universal Data Access) and/or MDAC (Microsoft Data Access Components) include (ADO), OLE DB, and (ODBC).

32. JDBC • Java DB connectivity API • Similar to ODBC • Why do you need it: • Pure Java • Simple API • Well….Multi-platform

33. JDBC • API includes: • DriverManager, Connection, Statement, PreparedStatement, CallableStatement, ResultSet, SQLException, DataSource • JDBC Type Driver: • Type 1 - (JDBC-ODBC Bridge) drivers. • Type 2 - native API for data access which provide Java wrapper classes • Type 3 - 100% Java, makes use of a middle-tier between the calling program and the database.. • Type 4 - They are also written in 100% Java and are the most efficient among all driver types. Calls directly into the vendor-specific database protocol.

34. JDBC Types Type 1 Type 2 Type 3 Type 4

35. ORM • Object-Relational mapping is a programming technique for converting data between incompatible type systems in relational databases and object-oriented programming languages. • For example: Hibernate, EJB3.0, JDO

36. Bureaucracy… Database architecture overview Buzzwords Intro to Oracle Comments on homework Agenda

37. Products we will be using • Oracle database – (at home express edition) • SQLDeveloper

38. Host: localhost/orasrv Port: 1521 SID: xe/csodb/other? Schema system/hr/specificuser/ Use the connection guide (link on the course slides page) for instruction on how to create a DB user: TAU HR user / password: hr_readonly / tiger Server settings..

39. Schema Objects • In Oracle Database, a database schema is a collection of logical data structures, or schema objects. A database user owns a database schema, which has the same name as the user name. • Schema objects are user-created structures that directly refer to the data in the database. The database supports many types of schema objects, the most important of which are tables and indexes.

40. Tables & Indexes • A table describes an entity such as employees. You define a table with a table name, such as employees, and set of columns. In general, you give each column a name, a data type, and a width when you create the table. • You can optionally specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. This constraint forces the column to contain a value in every row. • An index is an optional data structure that you can create on one or more columns of a table. Indexes can increase the performance of data retrieval. • Indexes are useful when applications often query a specific row or range of rows. • Indexes are logically and physically independent of the data. Thus, you can drop and create indexes with no effect on the tables or other indexes. All applications continue to function after you drop an index.

41. Relational Database Management System (RDBMS) • The relational model is the basis for a relational database management system (RDBMS). Essentially, an RDBMS moves data into a database, stores the data, and retrieves it so that applications can manipulate it. An RDBMS distinguishes between the following types of operations: • Logical operations In this case, an application specifies what content is required. For example, an application requests an employee name or adds an employee record to a table.

42. RDBMS(Cont’d) • Physical operations • In this case, the RDBMS determines how things should be done and carries out the operation. For example, after an application queries a table, the database may use an index to find the requested rows, read the data into memory, and perform many other steps before returning a result to the user. The RDBMS stores and retrieves data so that physical operations are transparent to database applications. • Oracle Database is an RDBMS. An RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism is called an object-relational database management system (ORDBMS). Oracle Database has extended the relational model to an object-relational model, making it possible to store complex business models in a relational database.

43. Oracle Physical Structures • Datafiles (*.dbf) The physical database structures are the files that store the data. When you execute the SQL command CREATE DATABASE, the following files are created: • Data files Every Oracle database has one or more physical data files, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the data files. • Control files Every Oracle database has a control file. A control file contains metadata specifying the physical structure of the database, including the database name and the names and locations of the database files. • Online redo log files Every Oracle Database has an online redo log, which is a set of two or more online redo log files. An online redo log is made up of redo entries (also called redo records), which record all changes made to data. • The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.

44. Logical Structures • This section discusses logical storage structures. The following logical storage structures enable Oracle Database to have fine-grained control of disk space use: • Data blocks • At the finest level of granularity, Oracle Database data is stored in data blocks. One data block corresponds to a specific number of bytes on disk. • Extents • An extent is a specific number of logically contiguous data blocks, obtained in a single allocation, used to store a specific type of information.

45. Logical Structures (cont’d) • Segments • A segment is a set of extents allocated for a user object (for example, a table or index), undo data, or temporary data. • Tablespaces • A database is divided into logical storage units called tablespaces. A tablespace is the logical container for a segment. Each tablespace contains at least one data file.

46. Logical Structures (cont’d) • Schema Overview • A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. Schema objects include structures like tables, views, and indexes.

47. Database Structure • An Oracle database server consists of a database and at least one database instance (commonly referred to as simply an instance). Because an instance and a database are so closely connected, the term Oracle database is sometimes used to refer to both instance and database. In the strictest sense the terms have the following meanings: • Database A database is a set of files, located on disk, that store data. These files can exist independently of a database instance. • Database instance An instance is a set of memory structures that manage database files. The instance consists of a shared memory area, called the system global area (SGA), and a set of background processes. An instance can exist independently of database files.

48. Oracle Express Edition (XE) Installation next chapter….

49. XE Database DEMO • Installation • Create a user • Run a script • Query • Other database objects • Administration tasks

50. Database Homepage Demo Demo..