1 / 49

Why Oracle ?

Why Oracle ?. By Mazmur Tarigan Email : Oluckysat@gmail.com. Database. Foxpro, DB-plus, Clipper : .dbf Microsoft Access :.mdb Microsoft Exel:.xls Paradox :.db MySQL Sybase MS-SQL Server * Oracle *

Download Presentation

Why Oracle ?

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. Why Oracle ? By Mazmur Tarigan Email : Oluckysat@gmail.com

  2. Database • Foxpro, DB-plus, Clipper : .dbf • Microsoft Access :.mdb • Microsoft Exel:.xls • Paradox :.db • MySQL • Sybase • MS-SQL Server * • Oracle * * Mendapat perhatian khusus bagi software development di Indonesia. Dan akan dilihat perbandingannya By Mazmur Tarigan

  3. Platform comparison • SQL Server 2000 only works on Windows-based platforms, including Windows 9x, Windows NT and Windows 2000. • In comparison with SQL Server 2000, Oracle 9i Database supports all known platforms, including Windows-based platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on. By Mazmur Tarigan

  4. Hardware Requirements Processor Pentium 166 MHz or higher Memory 32 MB RAM (minimum for Desktop Engine),64 MB RAM (minimum for all other editions),128 MB RAM or more recommended Hard disk space 270 MB (full installation),250 MB (typical),95 MB (minimum),Desktop Engine: 44 MBAnalysis Services: 50 MB minimum and 130 MB typicalEnglish Query: 80 MB Hardware requirements • To install SQL Server 2000, you should have the Intel or compatible platforms and the following hardware: By Mazmur Tarigan

  5. Hardware Requirements Processor Pentium 166 MHz or higher Memory RAM: 128 MB (256 MB recommended)Virtual Memory: Initial Size 200 MB, Maximum Size 400 MB Hard disk space 140 MB on the System Driveplus 4.5 GB for the Oracle Home Drive (FAT)or 2.8 GB for the Oracle Home Drive (NTFS) To install Oracle 9i Database under the UNIX Systems, such as AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, and Sun Solaris, you should have the following hardware: Hardware Requirements Memory A minimum of 512 MB RAM Swap Space A minimum of 2 x RAM or 400 MB, whichever is greater Hard disk space 4.5 GB Hardware requirements… • Oracle 9i supports Intel or compatible platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on. • To install Oracle 9i under the Intel or compatible platforms, you should have the following hardware: By Mazmur Tarigan

  6. Operating System Enterprise Edition Standard Edition Personal Edition Developer Edition Desktop Engine SQL Server CE Windows CE No No No No No Yes Windows 9x No No Yes No Yes No Windows NT 4.0 Workstation with Service Pack 5 No No Yes Yes Yes No Windows NT 4.0 Server with Service Pack 5 Yes Yes Yes Yes Yes No Windows NT 4.0 Server Enterprise Edition with Service Pack 5 Yes Yes Yes Yes Yes No Software requirements • SQL Server 2000 comes in six editions: Enterprise, Standard, Personal, Developer, Desktop Engine, and SQL Server CE (a compatible version for Windows CE) and requires the following software: By Mazmur Tarigan

  7. Platform Operating System Version Required Patches Windows-based Windows NT 4.0 Service Pack 5 Windows-based Windows 2000 Service Pack 1 Windows-based Windows XP Not Necessary AIX-Based AIX 4.3.3 Maintenance Level 09 and IY24568,IY25282, IY27614, IY30151 AIX-Based AIX 5.1 AIX 5L release 5.1 ML01+ (IY22854),IY26778, IY28766, IY28949, IY29965, IY30150 Compaq Tru64 UNIX Tru64 5.1 5.1 patchkit 4 Compaq Tru64 UNIX Tru64 5.1A 5.1A patchkit 1 HP-UX HP-UX version 11.0 (64-bit) Sept. 2001 Quality Pack, PHCO_23792,PHCO_24148, PHKL_24268, PHKL_24729,PHKL_ 25475, PHKL_25525, PHNE_24715,PHSS_23670, PHSS_24301, PHSS_24303,PHSS_24627, PHSS_22868 Linux SuSE Linux Enterprise Server 7(or SLES-7) with kernel 2.4.7,and glibc 2.2.2 Not Necessary Sun Solaris Solaris 32-Bit 2.6 (5.6), 7 (5.7)or 8 (5.8) Not Necessary Sun Solaris Solaris 64-Bit 8 (5.8) Update 5 Software requirements… • Oracle 9i Database comes in three editions: Enterprise, Standard and Personal and requires the following software: By Mazmur Tarigan

  8. Performance comparison • It is very difficult to make the performance comparison between SQL Server 2000 and Oracle 9i Database. The performance of your databases depends rather from the experience of the database developers and database administrator than from the database's provider. You can use both of these RDBMS to build stable and efficient system. However, it is possible to define the typical transactions, which used in inventory control systems, airline reservation systems and banking systems. After defining these typical transactions, it is possible to run them under the different database management systems working on the different hardware and software platforms. By Mazmur Tarigan

  9. Number of CPUs Oracle9i Standard Edition SQL Server 2000 Standard Edition 1 $15,000 $4,999 2 $30,000 $9,998 4 $60,000 $19,996 8 $120,000 $39,992 16 $240,000 $79,984 32 $480,000 $159,968 Price comparison • Compare pricing for SQL Server 2000 Standard Edition and Oracle9i Standard Edition: By Mazmur Tarigan

  10. Number of CPUs Oracle9i Enterprise Edition Oracle9i Enterprise Edition with OLAP or Data Mining Oracle9i Enterprise Edition With OLAP and Data Mining SQL Server 2000 Enterprise Edition 1 $40,000 $60,000 $80,000 $19,999 2 $80,000 $120,000 $160,000 $39,998 4 $160,000 $240,000 $320,000 $79,996 8 $320,000 $480,000 $640,000 $159,992 16 $640,000 $960,000 $1,280,000 $319,984 32 $1,280,000 $1,920,000 $2,560,000 $639,968 Price comparison… • Compare pricing for SQL Server 2000 Enterprise Edition (which include OLAP and Data Mining) and Oracle9i Enterprise Edition with OLAP and/or Data Mining: By Mazmur Tarigan

  11. Feature PL/SQL T-SQL Indexes B-Tree indexes,Bitmap indexes,Partitioned indexes,Function-based indexes,Domain indexes B-Tree indexes Tables Relational tables,Object tables,Temporary tables,Partitioned tables,External tables,Index organized tables Relational tables,Temporary tables Triggers BEFORE triggers,AFTER triggers,INSTEAD OF triggers,Database Event triggers AFTER triggers,INSTEAD OF triggers Procedures PL/SQL statements,Java methods,third-generation language(3GL) routines T-SQL statements Arrays Supported Not Supported T-SQL vs PL/SQL • The dialect of SQL supported by Microsoft SQL Server 2000 is called Transact-SQL (T-SQL). The dialect of SQL supported by Oracle 9i Database is called PL/SQL. PL/SQL is more powerful language than T-SQL. This is the brief comparison of PL/SQL and T-SQL: By Mazmur Tarigan

  12. Feature SQL Server 2000 Oracle 9i Database database name length 128 8 column name length 128 30 index name length 128 30 table name length 128 30 view name length 128 30 stored procedure name length 128 30 max columns per index 16 32 max char() size 8000 2000 max varchar() size 8000 4000 max columns per table 1024 1000 max table row length 8036 255000 max query size 16777216 16777216 recursive subqueries 40 64 constant string size in SELECT 16777207 4000 constant string size in WHERE 8000 4000 SQL Server 2000 and Oracle 9i limits Here you can find some SQL Server 2000 and Oracle 9i Database limits: By Mazmur Tarigan

  13. Introduction

  14. Pengenalan Oracle • Oracle Server adalah relational database management system(RDBMS) • RDBMS adalah perangkat lunak untuk membuat dan mengelola database, sering juga disebut database engine. By Mazmur Tarigan

  15. RDBMS By Mazmur Tarigan

  16. Database By Mazmur Tarigan

  17. Kemampuan • Dapat bekerja di lingkungan client/server(pemprosesan tersebar) • Menangani manajemen space dan basis data yang besar • Mendukung akses secara simultan • Performansi pemrosesan transaksi yang tinggi • Menjamin ketersediaan yang terkontrol • Lingkungan yang terreplikasi By Mazmur Tarigan

  18. Arsitektur Oracle • Oracle Database * • Oracle Instance (memory, process, dan files) * Oracle Database akan dikupas berkaitan dengan tujuan training sekarang untuk database developer. By Mazmur Tarigan

  19. … Oracle Database • Physical Database Structure • Datafiles : menyimpan table dan objek database • Redo Log Files : menyimpan info yang dibutuhkan untuk proses Recovery, yaitu bila terjadi crash pada sistem, maka Oracle berusaha memperbaikinya melaui Redo Log Files • Control Files : Berisi info berupa konfigurasi, lokasi data dan redo-log files, start database instance dan lainnya. By Mazmur Tarigan

  20. … Oracle Database (1) • Logical Database Structure • Tablespace : mengelompokkan objek database menjadi satu kesatuan. Tablespace menyimpan data dalam datafiles yang secara fisik tersebar dalam satu atau beberapa disk. • Data Schema : kumpulan dari objek database yang disebut sebagai schema objects. By Mazmur Tarigan

  21. … Oracle Database (2) • Data schema : • Table : berisi nama table, kolom, baris(record) yang merupakan basic dari database. • Cluster: himpunan tabel yang secara fisik disimpan bersama sebagai sebuah tabel yang mempunyai kolom bersama. • Index : digunakan untuk mencari data secara lebih cepat ketimbang pencarian sekuensial. • View : tampilan dari satu atau beberapa tabel yang dikoleksi melalui instruksi SQL. • Stored Procedure : instruksi SQL yang dibungkus dalam bentuk program dan dieksekusi seperti fungsi atau prosedur dalam bahasa pemrograman. • Triggers : instruksi SQL seperti prosedur yang dijalankan berdasarkan suatu kejadian(event) • Sequence : secara otomatis membuat nomor urut(tersusun) dan tunggal(unique) yang digunakan oleh aplikasi untuk menjamin integritas data. By Mazmur Tarigan

  22. Create Database • Untuk memulai membuat sebuat database : Start  oracle – orahome90  configuration and migration tools  Database Configuration Assistant * Sebaiknya create database dilakukan oleh DBA saja, dalam hal ini instruktur By Mazmur Tarigan

  23. Create Database (1) By Mazmur Tarigan

  24. Create Database (2) By Mazmur Tarigan

  25. Create Database (3) By Mazmur Tarigan

  26. Create Database (4) By Mazmur Tarigan

  27. Create Database (5) By Mazmur Tarigan

  28. Create Database (5)… By Mazmur Tarigan

  29. Create Database (6) By Mazmur Tarigan

  30. Create Database (7) By Mazmur Tarigan

  31. Create Database (8) By Mazmur Tarigan

  32. Create Database (9) By Mazmur Tarigan

  33. Create Database (10) By Mazmur Tarigan

  34. Create Database (11) By Mazmur Tarigan

  35. Create Database (12) By Mazmur Tarigan

  36. Create Database (12)… By Mazmur Tarigan

  37. Operate Database By Mazmur Tarigan

  38. SQL * Plus • Aplikasi pada oracle server yang digunakan untuk memanipulasi instruksi SQL, pemrograman PL/SQL dan menjalankan instruksi Administrasi database. • Memulai SQL * Plus dengan mengaktifkannya dari menu Start  oracle – orahome90  application development  SQL Plus By Mazmur Tarigan

  39. SQL * Plus (1) By Mazmur Tarigan

  40. SQL * Plus (2) • User name adalah login yang diperlukan untuk mendapat akses database • Password umumnya diberikan oleh DBA(DB-Administrator) • Connect String adalah teks yang diperlukan untuk melakukan koneksi, umumnya adalah nama server. By Mazmur Tarigan

  41. SQL * Plus (3) By Mazmur Tarigan

  42. SQL * Plus (4) • Beberapa cara untuk menjalankan SQL adalah sebagai berikut : • Dengan semi-kolom pada akhir baris : • SQL> select * from tab; • Dengan tanda / pada baris berikutnya : • SQL> select * from tab 2 / By Mazmur Tarigan

  43. Table (Basic) • Data Definition Language (DDL) • CREATE TABLE • ALTER TABLE • DROP TABLE By Mazmur Tarigan

  44. Table – Create table … Table name : mahasiswa Structure : Nim char(8), Nama varchar2(25), Jk char(1), Alamat varchar2(50), Kota varchar2(10) SQL> create TABLE mahasiswa( 2 Nim char(8), 3 Nama varchar2(25), 4 jk char(1), 5 alamat varchar2(50), 6 kota varchar2(10) 7 ); Table created. * Coba masukkan data mahasiswa yang sama By Mazmur Tarigan

  45. Table – Create table … SQL> Insert into mahasiswa values(‘0001’,’Nurlela’,’P’,’Jl. Damar’,’Padang’); SQL> Insert into mahasiswa values(‘0002’,’Azijah’,’P’,’Jl. Yamin’,’Pariaman’); SQL> Insert into mahasiswa values(‘0001’,’Yanto’,’L’,’Jl. Damar’,’Padang’); SQL> Select * from mahasiswa; SQL> drop TABLE mahasiswa; Table dropped. SQL> create TABLE mahasiswa( 2 Nim char(8), 3 Nama varchar2(25), 4 jk char(1), 5 alamat varchar2(50), 6 kota varchar2(10), 7 CONSTRAINT PK_MHS PRIMARY KEY(Nim) 8 ); Table created. * Coba lagi memasukkan data yang sama By Mazmur Tarigan

  46. Case Study • Createlah tabel : • Mahasiswa • Matakuliah • Nilai Sesuai dengan struktur tabel yang sudah anda buat. • Masukkan data kedalam masing-masing table, minima : • 5 record untuk table mahasiswa • 3 record untuk table matakuliah • 15 record untuk table Nilai By Mazmur Tarigan

  47. SQL Dasar • Searching : Select dengan operator ( = , !=, < >, < , >, <=, >=, LIKE ) • Penentuan Baris : Where (OR, AND, NOT). Juga bisa menggunakan BETWEENdan IN • Pengurutan : ORDER BY • Kolom Nilai secara unik : DISTINCT • Operasi Aritmatika : (+, -, *, /, %) By Mazmur Tarigan

  48. SQL Dasar • Pengelompokan : GROUP BY • Subquery : NESTED SQL • Penambahan baris : INSERT • Pengubahan baris : UPDATE • Penghapusan Baris : DELETE • Fungsi Agregate Dasar : AVG,COUNT, MAX, MIN, SUM By Mazmur Tarigan

  49. SQL Dasar • Fungsi – fungsi String : • ASCII : ASCII(char) • CHAR : CHAR(ascii) • LOWER • UPPER • LTRIM • RTRIM • REPLICATE : REPLICATE(‘AIM’,’3’) • SUBSTRING : SUBSTRING(‘STMIK JAYA NUSA’,7,4) By Mazmur Tarigan

More Related