untitled

Transcription

1

2 1 2 3 SQL SQL(Structured Query Language) Oracle SQL * Plus 4 Oracle8 Oracle Oracle 5 Oracle PL/SQL Oracle PL/SQL PL/SQL 6 Oracle C Pro * C Oracle SQL SQL 7 Oracle 8 Oracle Oracle Oracle Oracle 8 SQL * Report SQL * Report SQL Oracle 9 DBMS

3 1977 Larry Ellison Bob Miner Ed Oates (Relational Software Incorporate, RSI) Oracle Oracle Oracle Oracle 1979 Oracle E.F.Codd 1983 Oracle ANSI C UNIX VMS 1984 PC Oracle 64KB Oracle 1988 Oracle 1992 Oracle Oracle 7.0 Oracle 7 Oracle 7 TPC Oracle Web Oracle Larry Ellison NC Network Computer, Apple IBM SUN Netscape Oracle Network Computer Reference Profile Oracle Internet NCA Oracle 1986 Oracle 5.0 Oracle Oracle Oracle 8i Oracle Oracle Oracle, SQL * Net C/S

4 IV SQL * Form Oracle 1. Oracle SQL * Net PC RISC 90 DBMS 2. Oracle CDE Designer/2000 Develop/2000 Oracle-Media-Object Oracle-Power-Object Oracle-Express-Object Oracle RDBMS Oracle SQL ANSI/ISO SQL89 SQL * PLUS Oracle Oracle SQL PL/SQL SQL * PLUS PL/SQL Oracle SQL PL/SQL Oracle RDBMS (1) Oracle RDBMS SQL Cache SQL (2) Oracle RDBMS (3) Oracle RDBMS ANSI/ISO Oracle RDBMS (4) Oracle RDBMS PL/SQL (Packege Procedure) (5) Oracle RDBMS (6) Oracle RDBMS (Distribute Database Management System DDBMS) (7) Oracle RDBMS (8) Oracle RDNMS Oracle RDBMS Oracle

5 V

6 Codd

7 VII DBMS /CPU SQL Oracle SQL SQL SQL SQL * Plus

8 VIII 3.12 SQL * Plus SQL * Plus SQL * Plus COPY SQL * Plus SQL SQL * Plus SQL SQL * Plus Oracle Oracle Oracle Oracle Oracle Oracle Oracle SYS SYSTEM Oracle Oracle DBMS Oracle Oracle REF

9 IX Oracle 8i Oracle 8i Java ifs Oracle intermedia Oracle WebDB Oracle 8i Oracle 8i ROWID SYS Oracle PL/SQL Oracle PL/SQL PL/SQL PL/SQL SQL PL/SQL PL/SQL SQL PL/SQL PL/SQL PL/SQL

10 X SQLCODE SQLERRM RAISE_APPLICATION_ERROR FOR %FOUND %NOTFOUND %ROWCOUNT COMMIT ROLLBACK Oracle C Pro * C Pro * C Pro * C Pro * C Pro * C Pro * C SQL PL/SQL Pro * C Pro * C Pro * C Oracle Oracle Oracle Oracle Oracle Oracle SGA PGA

11 XI Oracle Oracle Oracle Oracle SQL * Report SQL * Report SQL * Report RPF RPF RPF RPF RPF RPF RPF RPT RPT RPT

12 XII Oracle

13 business Automatic Data Processing,ADP

14 2 Oracle 8i enterprisewide departmentwide workgroup (Data Warehouse DW) Web Data Mart,DW DW Very Large Database,VLDB Redundant Array of Inexpensive Disks,RAID VLDB 100GB 100GB VLDB 10GB Ioformation 2. Data 3.

15 ~ (1) (2)

16 4 Oracle 8i (3) (4) (5) Redundancy (1) (2) (3) (4) Inconsistency

17 1 5 (1) (2) (3) (4) (5) (1) 1-5 (2) 20 60

18 6 Oracle 8i Data Model (1) Etinity Relation Model (2)

19 1 7 (Hierarchical Model) (Network Model) (Relational Model) (Object-Oriented Model) 1. Etinity Relation Model E-R P.P.Chen 1971 E-R E-R E-R E-R n n 1 m m E-R 1-1 E-R E-R E-R E-R 1-7 M 1-7

20 8 Oracle 8i E-R E-R E-R (1) (2) E-R 2. (Network Model) DASYL DBTG m n E-R (1) E-R (2) E-R 3. (Hierarchical Model) n 1 1

21 IBM IMS (Relational Model) 1-1 E-R (1) (2) 5. Object-Oriented Model CAD

22 10 Oracle 8i (1) OID OID OID RID TID OID (2) Encapsulate (State) (Behavior) (3) Object Attribute (4) Instance Inherit (5) Inherit

23 1 11 Extensibility 1-1 E-R P-P P-S PROJECT PART SUPPLIER P-P J# PROJECT P# PART P-S P# PART S# SUPPLIER 1-6 E-R 1.3 Database Management System DBMS DBMS metadata Database Administrator, DBA DBMS DBMS (1) Data Defined Language(DDL)

24 12 Oracle 8i (2) Data Manipulate Language(DML) (3) (4) (5) Data Dictionary DD DD (6) OS DBMS DBMS DBMS

25 DBMS DBMS DBMS DBMS

26 14 Oracle 8i DBMS DBMS DBMS DBMS DBMS DBMS DBMS (transaction manager) DBMS DBMS 20 DBMS DBMS DBMS DBMS DBMS

27 (1) (2) (3) / /

28 16 Oracle 8i DBMS DBMS DBMS (Structure Query Language SQL) DBMS DBMS(RDBMS) DBMS SQL (DBA) E.F.Codd RDBMS DB2 IMS (IDMS) 3GL CODASYL Database Task Group, DBTG COBOL IDMS 70 RDBMS Oracle Sybase Informix Ingres Object-Oriented, OO DBMS CAD/CAM DBMS RDBMS / Oracle Universal Server Oracle 8 RDBMS RDBMS Multi-Dimensional Database, MDD RDBMS MDD RDBMS Oracle Express

29 DBMS RDBMS (1) (2) (attribute) tuple Toyota Camry $25K Honda Accord $23K Ford Taurus $20K Volkswagen Passat $20K (1) Information Principle information coding Vehicle Identification Number, VIN (2) (3) (4) (5) (6) SQL RDBMS ANSI SQL Ingres QUEL QUEry UDL Unified Data Language ANSI

30 18 Oracle 8i SQL SELECT INSERT UPDATE DELETE CREATE ALTER DROP SQL (Data Definition Language DDL) Data Manipulation Language, DML DDL DML entity integrity rule referential integrity rule (1) primary key (2) concatenated key compound key composite key candidate key alternate key foreign key RDBMS DBMS RDBMS SQL Codd Codd DBMS Codd 12 (1) VIN (2) + + (3) Null 0 (4) (5)

31 1 19 SQL (6) (7) RDBMS (8) (9) (10) (11) 8 (12) VLDB DBMS 12 RDBMS Codd RDBMS

32 20 Oracle 8i 3. Data Model (1) Etinity Relation Model (2) (Hierarchical Model) (Network Model) (Relational Model) (Object-Oriented Model) 4. Database Management System DBMS 5. DBMS DBMS DBMS 6. RDBMS DBMS RDBMS

33 DBMS DBMS 3.

34 22 Oracle 8i DBMS DBMS Reproductable New Orleans S.B.Yao (1) (2) (3) (1) (2)

35 2 23 (3) (4) (5) (6) (7) (8) (9) (10) (11) (1) (2) 2-1 Data Flow Diagram, DFD

36 24 Oracle 8i (1) (2) DFD a. : b. c. d. e

37 2 25 DBMS 1. (1) (2) DBA (3) (4) DBMS 2. P.P.S.chen 1976 E-R 3. (1) (2) (3) (4) DBMS 1. (1) E-R DBMS (2) (3) (4) DBMS 2. E-R E-R

38 26 Oracle 8i (1) (2) n n m n 3. (1) E-R (2) 3NF (3) (4), (5) DBMS DBMS 1. (1)

39 2 27 (2) cluster I/O (3) (4) DBMS OS (5) I/O (6) 2. cost (1) CPU CPU I/O (2) (3) (4)

40 28 Oracle 8i (5) I/O CPU CPU I/O (1) (2) (3) 2. (1) reorganization (restructuring), (reformatting) DBMS RDBMS (2) DBMS DBMS (3) DBMS (4) DBMS DBMS

41 (1) (2) (3) (4) DBMS DBMS RDBMS DBMS 2.2 DBMS DBMS E.F.Codd MIS 1. CustomerAccountNumber CustomerBalance CustomerBalance CustomerAccountNumber

42 30 Oracle 8i TotalPrice=ItemPrice Quantity TotalPrice ItemPrice Quantity SID, SID Major SID SID?Major SID Major Major SID ACTIVITY SID Fee SID ) 2-2 ACTIVITY SID SID SID Activity SID SID,Activity 2-3 ACTIVITY SID 100 SID 100 (SID,Activity) (SID,Fee) (Activity Fee) 2-3

43 (SID,Fee) (Activity Fee) (SID,Activity) (SID,Activity) (SID,Activity) 2-3 Data ACTIVITY ACTIVITY 2-2 ACTIVITY SID Activity STU-ACT Activity Fee ACT-COST 2-4

44 32 Oracle 8i 2-4 ACTIVITY STU-ACT ACT-COST 250 STU-ACT 250 RACKETBALL ACT-COST ACTIVITY ACT-COST STU-ACT DBMS DBMS DBMS ACT-COST STU-ACT Activity ACT-COST Activity STU-ACT Activity ACT-COST Activity STU-ACT Activity ACT-COST Activity Activity STU-ACT ACT-COST ACTIVITY ACTIVITY

45 2 33 E.F.Codd NF 2NF 3NF Boyce-Codd BCNF 2-5 5NF 4NF BCNF 3NF 2NF 1NF R.Fagin DK/NF DK/NF DK/NF DK/NF R.Fagin DK/NF DK/NF ACTIVITY 175, 50

46 34 Oracle 8i SID,Activity Activity Fee Activity SID,Activity Fee Fee ACTIVITY 2-4 STU-ACT ACT-COST 2 2-6(a) HOUSING SID SID Building,Building Fee Randolph 1100 SID Building Building Fee SID Fee SID Building Fee SID Building Fee SID HOUSING 2-6(a) 150 Ingersoll 1100 Carrigg (a) (b) HOUSING 2-6(b) STU- HOUSING (SID,Building) BLDG-FEE(Building,Fee) 2-2 ACTIVITY ACTIVITY SID Ativicty Activity

47 2 35 Fee ACTIVITY ACTIVITY STU-ACT(SID,Ativicty) ACT-COST(Ativicty,Fee) 3 Boyce-Codd 2-7(a) ADVISER SID Major Fname 2-7 Boyce-Codd SID Major SID Fname SID (SID,Major) Fname (SID,Fname) Major Fname Major Fname ADVISER ADVISER Perls Keynes Boyce-Codd BCNF BCNF ADVISER BCNF Fname BCNF STUDENT SID,Major,Activity 100

48 36 Oracle 8i 150 SID Major SID Major SID Activity (a) 100,MUSIC,SKIING (a) 100,ACCOUNTING,SKIING R(A,B,C) A B A C B C SID Major SID Activity Major Activity 2-8 SID Major SID Activity SID Major SID Activity BCNF 2NF SID 3NF BCNF

49 STUDENT (a) (b) STU-MAJOR (SID,Major) STU-ACT(SID,Activity) BCNF R.Fagin / DK/NF DK/NF DBMS Fagin

50 38 Oracle 8i 1 DK/NF DK/NF Fagin Fagin Fagin Fagin DK/NF DK/NF DK/NF DBMS DK/NF DK/NF DK/NF DK/NF DK/NF 2 DK/NF 2-11 STUDENT SID GradeLevel Building Fee Building Fee STUDENT (SID, GradeLevel, Building, Fee) Key: SID Contraints: Building Fee SID must not begin with digit DK/NF SID SID Building Fee Fagin Building Fee Building Building Fee Building STUDENT BLDG FEE

51 2 39 Building Fee Building STUDENT Fee 2-12 Domain Definitions SID in CDDD, where C is decimal digit not =1; D=decimal digit GradeLevel in ( FR, SO, JR, SN, GR ) Building in CHAR(4) Fee in DEC(4) Key Definitions STUDENT (SID, GradeLevel, Building) Key: SID BLDG-FEE (Building, Fee) Key: Building 2-12 / 2NF 3NF, 3NF 2NF DK/NF A B (1) A B B A A B 1 1 (2) A B B A A B 1 (3) A B B A A B A B B A 1 1 A B A B 1 B A B A 1 A B B A (1) 1 1 (2) 1 1 (3)

52 40 Oracle 8i A B 1 1 R A B B A A B C A B R A B B A 1 DK/NF A B C A B A C A B C A B A B A B A SID Building STUDENT SID Sname Fee Building SID?Fee SID Fee Fee 2-13 C D N 1 S C D D C S C E C E S 3 A B B A 2-13 E F M N E F F E E F T T E,F G E,F T E F T STUDENT SID,Major,Activity SID Major SID Activity

53 R A B A B R A B R A B R A B R R A B 1 1 S C D C S C S C S E F T T E F E F T E F T G E F G G T T 2-14 Major Activity SID Major Activity DK/NF Major Activity Activity SID 1 SID,Major,Activity CUSTOMER(CustNumber,CustName,City,State,Zip) CustNumber DK/NF Zip (City,State) CustNumber

54 42 Oracle 8i DK/NF CUSTOMER(CustNumber,CustName,Zip), CustNumber CODE(Zip,City,State) Zip City State DBMS 2 COLLEGE(CollegeName, Dean,AssitantDean) 1~3 (CollegeName,AssitantDean) CollegeName Dean COLLEGE DEAN (CollegeName,Dean) ASSISTANT-DEAN (College- Name,AssitantDean) COLLEGE AssistantDean, COLLEGE1(College,Dean, AssistantDean1,AssistantDean2,AssistantDean3) COLLEGE1 CollegeName Mary Abernathy COLLEGE AssistantDean SELECT CollegeName FROM COLLEGE1 WHERE Assistant1= Mary Abernathy OR Assistant2= Mary Abernathy OR Assistant3= Mary Abernathy OR ASSISTANT-DEAN SELECT CollegeName FROM ASSISTANT-DEAN WHERE AssistantDean= Mary Abernathy 2.3

55 2 43 DBMS E-R E-R CUSTOMER CustNumber CustName Address City State Zip ContactName PhoneNumber CustNumber (1) 2-15(b) CUSTOMER DK/NF CUSTOMER entity contains CustNumber CustName Address City State Zip ContactName PhoneNumber CUSTOMER (CustNumber, Address, City, State, Zip, ContactName, PhoneNumber) (a) (b) 2-15 (a)customer (b) CUSTOMER

56 44 Oracle 8i CustNumber CustNumber CustName Address City State Zip ContactName PhoneNumber Zip City State ContactName PhoneNumber DK/NF 2-16 CUSTOMER CustNumber ZIP-TABLE ContactName CUSTOMER (CustomerNumber, Address, Zip, ContactName) ZIP-TABLE (Zip, City, State) CONTACT (ContactName, PhoneNumber) Interrelation constraints: Zip in CUSTOMER must exist in Zip in ZIP-TABLE ContactName in CUSTOMER must = a ContactName in CONTACT 2-16 / 2-16 DK/NF CUSTOMER CUSTOMER Jones Jones DK/NF 2-17 SALES-COMMISSION 2-17(b) SALES-COMMISSION entity contains SalespersonNumber SalespersonName Phone CheckNumber CheckDate CommissionPeriod TotalCommissionSales CommissionAmount BudgetCategory (a) SALES-COMMISSION (SalespersonNumber, SalespersonName, Phone, CheckNumber, CheckDate, CommissionPeriod, TotalCommissionSales, CommissionAmount, BudgetCategory) Functional dependencies: CheckNumber is key SalespresonNumber determines SalespersonName, Phone, BudgetCategory (SalespresonNumber, CommissionPeriod) determines TotalCommissionSales, CommissionAmount (b) SALESPERSON (SalespersonNumber, SalespersonName, Phone, BudgetCategory) SALES (SalespersonNumber, CommissionPeriod, TotalCommissionSales, CommissionAmount) COMMISSION-CHECK (CheckNumber, CheckData, SalespersonNumber, CommissionPeriod) (c) 2-17 (a) SALES-COMMISSION b SALES-COMMISSION (c) / SALES-COMMISSION

57 2 45 DK/NF 2-17(c) 2-17(b) DK/NF DK/NF DK/NF (2) E-R ID( ) ID 2-18(a) LINE-ITEM ID INVOICE INVOICE LINE-ITEM 2-18(b) 2-18(b) INVOICE 1 N (a) LINE-ITEM LINE-ITEM (LineNumber, Qty, ItemNumbre, Description, Price, Extprice) (b) LINE-ITEM (invoicenumber, LineNumber, Qty, ItemNumber, Description, Price, ExtPrice) (c) 2-18 (a) (b) LINE-ITEM (c) INVOICE LINE-ITEM ID 2-18(c) INVOICE InvoiceNumber LINE-ITEM LINE-ITEM (InvoiceNumber,

58 46 Oracle 8i LineNumber) ID E-R 2 HAS-A E-R HAS-A IS-A (1) EMPLOYEE AUTO E-R 2-19 EMPLOYEE 1 1 AUTO : (a) EMPLOYEE AUTO 2-20(b) AUTO EMPLOYEE 2-20(a) EmployeeNumber AUTO 2-20(b) LicenseNumber EMPLOYEE (a) EmployeeNumber AUTO EMPLOYEE AUTO AUTO EMPLOYEE EmployeeNumber EMPLOYEE LicenseNumber AUTO EMPLYEE (EmployeeNumber, EmployeeName, Phone, ) AUTO (LicenseNumber, SerialNumber, Color, Make, Model, EmployeeNumber) (a) EMPLOYEE (EmployeeNumber, EmployeeName, Phone, LicenseNumber) AUTO (LicenseNumber, SerialNumber, Color, Make, Model, ) (b) :1 (a) EMPLOYEE AUTO (b) AUTO EMPLOYEE 2-20(a) EMOLPYEE LicenseNumber EMPLOYEE EMPLOYEE AUTO

59 2 47 AUTO EmployeeNumber AUTO EMPLOYEE AUTO LicenseNumber EmployerNumber EMPLOYEE 2-20 DBMS EMPLOYEE JOB-EVALUATION JOB-EVALUATION EMPLOYEE EMPLOYEE 1 1 JOB-EVALUATION JOB-EVALUATION JOB-EVALUATION DBMS JOB-EVALUATION JOB-EVALUATION 1 1 EMPLOYEE AUTO 1 1 (2) E-R PROFESSOR STUDENT PROFESSOR STUDENT STUDENT PROFESSOR (a) PROFESSOR STUDENT (b) DORMITORY STUDENT STUDENT DORMITORY DORMITORY STUDENT STUDENT DORMITORY 2-22(c) CUSTOMER APPOINTMENT APPOINTMENT

60 48 Oracle 8i CUSTOMER CUSTOMER APPOINTMENT APPOINTMENT STUDENT PROFESSOR 1 N (a) STUDENT DORMITORY 1 N (b) STUDENT CUSTOMER 1 N (c) APPOINTMENT (a) 1 N (b) 1 N (c) 1 N 1 N 2-22(a) ADVISES PROFESSOR ProfessorName STUDENT STUDENT PROFESSOR STUDENT STUDENT PROFESSOR E-R (a) PROFESSOR STUDENT ProfessorName STUDENT StudentNumber, STUDENT PROFESSOR STUDENT PROFESSOR STUDENT ProfessorName N

61 N 1 N 2-24 CUSTOMER APPOINTMENT APPOINTMENT ID (CustomerNumber,Date,Time) 1 N (CustomerNumber) (c) (3) 2-25(a) E-R STUDENT CLASS, CLASS STUDENT 2-25(b) 2-25 M N (a)student CLASS E-R (b)student CLASS N

62 50 Oracle 8i STUDENT CLASS STUDENT (StudentNumber) CLASS StudentNumber CLASS (ClassNumber) STUDENT 2-26 CLASS M N (b) STUDENT CLASS 2-27(b) 2-27 M N (a) STUDENT CLASS (b)student CLASS

63 2 51 STUDENT-CLASS 2-28 CLASS STU-CLASS 1 N STUDENT STU-CLASS 1 N M N 1 N STU-CLASS (StudentNumber,ClassNumber) 2-28 STUDENT CLASS 3 HAS-A HAS-A N M N (a)1 1 (b)1 N (c)m N 2-29(a) SPONSOR (a) (b) 2-30(c) (a)1:1 (b) 1:1 (c) 1:1

64 52 Oracle 8i (b) REFERRED-BY 2-31(a) 1 N (b) :N (a) REFERRED-BY (b) 1:1 M N 2-29(c) TREATED-BY 2-32(a) M N 2-32(b) 2-32 M N (a) TREATED-BY (b) M N 4 IS-A IS-A HAS-A CLIENT ClientNumber ClientName AmountDue CLIENT INDIVIDUAL-CLIENT PARTNERSHIP-CLIENT CORPORATE-CLIENT

65 2 53 INDIVIDUAL-CLIENT Address,SocialSecurityNumber PARTNERSHIP-CLIENT ManagingPartnerName,Address,TaxIdentificationNumber CORPORATE-CLIENT ContactPerson,Phone,TaxIdentificationNumber CLIENT ClientNumber CLIENT(ClientNumber,ClientName,AmountDue) INDIVIDUAL-CLIENT(ClientNumber,Address,SocialSecurityNumber) PARTNERSHIP-CLIENT(ClientNumber,ManagingPartnerName,Address, TaxIdentificationNumber) CORPORATE-CLIENT(ClientNumber,ContactPerson,Phone,TaxIdentificationNumber) CLIENT 1 1 CLIENT CLIENT ClientNumber CorporateClientNumber CORPORATE-CLIENT CorporateClient- Number CLIENT CORPORATE-CLIENT 1 1 CORPORATE-CLIENT CORPORATE-CLIENT(CorporateClientNumber,ClientNumber,ContactPerson,Phone, TaxIdentificationNumber) 2-33(a) E-R EMPLOYEE EmployeeNumber ENGINEER EmployeeNumber TRUCK LicenseNumber SERVICE InvoiceNumber CLIENT ClientNumber ENGINEER-CERTIFICATION (EmployeeNumber,CertificationName) CERTIFICATION CentificationName DK/NF 2-33(b) EMPLOYEE ENGINEER EmployeeNumber

66 54 Oracle 8i EMPLOYEE (EmployeeNumber, other nonkey EMPLOYEE attributes ) ENGINEER (EmployeeNumber, other nonkey ENGINEER attributes ) TRUCK (LicenseNumber, other nonkey TRUCK attributes, EmployeeNumber) CLIENT (ClientNumber, other nonkey CLIENT attributes, Referred By) SERVICE-CLIENT (InvoiceNumber, ClientNumber, Fee) ENGINEER-CERTIFICATION (EmployeeNumber, CertificationName, other nonkey ENGINEER-CERTIFICATION attributes) CERTIFICATION (CertificationNumber, other nonkey CERTIFICATION attributes) (b) 2-33 E-R (a) E-R (b) E-R ENGINEER TRUCK 1 1 EmployeeNumber TRUCK ENGINEER SERVICE 1 N ENGINEER( ) SERVICE SERVICE CLIENT M N Fee 1 N REFERRED-BY ReferredBy CLIENT ReferredBy ENGINEER-CENTIFICATION ID ENGINEER EmployeeNumber (EmployeeNumber, CertificationName) 1 N EmployeeNumber CERTIFICATION ENGINEER-CENTIFICATION 1 N CENTIFICATION ENGINEER-CENTIFICATION E-R

67 E-R E-R N 1 N 2-35(b) 2-35(a) 1 N STUDENT ADVISER MAJOR 2-35 STUDENT

68 56 Oracle 8i 2-35 (a) (b) (a) 1 STUDENT 1 1 N 2-37(a) 2-37(b) 2-37 (a) (b)

69 (a) 1 N M N M N 2-38(b) PART-SUPPLIER 2-38 (a) (b) A ABC100 A B 2-39

70 58 Oracle 8i M N 2-40(a) M N 2-40(b) 2-40 (a) (b)element-relationship E-R HAS-A N M N M N N M N N M N M N (IS-A ) M N

71 2 59 (transaction) SELECT INSERT UPDATE DELETE INSERT UPDATE DELETE DML SELECT INSERT UPDATE DELETE DML INSERT UPDATE DELETE DML SELECT (query) (1) OLTP OLTP DML OLTP (2) DSS DSS DSS VLDB DM DW DSS (3) DML DW (1) OLAP OLAP OLAP OLTP DSS OLAP OLTP DSS ROLAP OLAP OLAP MDD RDBMS Geographic Information System, GIS OLAP (2) VCDB (volume analysis) (sizing analysis) 1

72 60 Oracle 8i (1) (2) (3) (4) (5) (6) OLTP OLTP DSS DBA 2 DBA n b n b Oracle DBA 120% RDBMS Oracle 4GB 15% 85% 3.4GB

73 GB 5 4 GB GB 17GB NF DBMS Oracle DBA 100 8h

74 62 Oracle 8i electromechanical disadvantage (Hierarchical Storage Management, HSM) RAID

75 RAID(, Redundant Array of Indexpensive Disks) RAID SCSI RAID RAID RAID I/O RAID srtiping I/O 512 UNIX VMS DOS/NT RAID RAID RAID DSS OLTP RAID RAID RAID 7 RAID0 RAID1 mirroring duplexing RAID3 RAID5 RAID3 RAID5 3 5 RAID2 4 6 RAID RDBMS OS RAID RAID RAID

76 64 Oracle 8i Virtual File System, VFS OS RAID RDBMS OS RAID DBMS DBMS I/O DBMS CPU DSS VLDB DW OLTP OLAP CPU OLAP CPU (1) OLTP DSS (2) (3) (4) (5) (6) RAID (7) (8)

77 2 65 (9) RDBMS OS (10) RDBMS Oracle Sybase Informix CA Ingres NT Oracle,UNIX Windows NT UNIX Sun Solaris Oracle UNIX Oracle MVS VMS Intel Sun Solaris Sun RISC SPARC Windows NT Compaq DEC HP DEC Windows NT DEC Alpha Intel Compaq DEC Solaris Windows NT Windows NT 14 Oracle 4 Solaris 20 Oracle Sun Compaq HP DEC/VMS DEC/VMS Windows NT UNIX 10GB NT UNIX Windows NT UNIX Linux UNIX Windows NT Network Operating System, NOS /CPU RAID CPU RAM DBMS DBMS DBMS DBMS UNIX

78 66 Oracle 8i DBMS DBMS DBMS DBMS Oracle System Global Area, SGA SGA Oracle init.ora CPU RDBMS I/O VLDB OLAP/MDD CPU VLDB DW CPU GB CPU CPU RDBMS Oracle RDBMS (1) Symmetric MultiProcessor, SMP ) (2) Massive Parallel Processor, MPP SMP ( Sun ) CPU SMP 64 MPP MPP RDBMS Sybase Informix CPU Oracle MTS CPU (1) CPU Oracle

79 2 67 (2) I/O DBMS DBA Oracle KEEP (3) Oracle DBA SYSTEM (4) I/O I/O DSS Oracle DB-BLOCK-SIZE (5) Oracle contention (1) (2) (3) (4) (5) DBMS (6) (7) (8) RAID1

80 68 Oracle 8i (9) RAID3 RAID5 (10) RAID0 RAID DBMS DBMS (1) (2) (3) 2 3 SID SID

81 2 69 SID Activity 4 5 E.F.Codd 1NF 2NF 3NF Boyce-Codd BCNF 5NF 4NF BCNF 3NF 2NF 1NF R.Fagin DK/NF DK/NF DK/NF 7 A B (1) A B B A A B 1 1 (2) A B B A A B 1 (3) A B B A A B 8 COLLEGE(CollegeName,Dean,AssitantDean) 1~3 (CollegeName,AssitantDean) CollegeName Dean 9 E-R 10 1

82 70 Oracle 8i (1) OLTP (2) DSS (3) (1) OLTP DSS (2) (3) (4) (5) (6) RAID (7) (8) (9) RDBMS OS (10)

83 3 SQL SQL(Structured Query Language) Oracle SQL * Plus Oracle 3.1 Oracle 2200 (1) (2) (3) (4) (5) (6) Oracle RDBMS SQL * Plus Oracle SQL * Plus E.F.Codd 70

84 72 Oracle 8i IBM ALPHA 1979 Oracle V2 (4GL) SQL 1974 IBM SAN JOSE SQL 1976 SQL 1979 Oracle SQL 1982 IBM SQL/DS 1985 DB ANSI SQL 3.2 SQL EMP JONES SAL 2975 EMP DEPT EMP DEPT Table DEPTNO DNAME LOC 40 OPERATIONS BOSTON 30 SALES CHICAGO 20 RESEARCH DALLAS 10 ACCOUNTING NEW YORK EMP Table EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK DEC ALLEN SALESMAN FEB-81 1, WORD SALESMAN FEB-81 1, JONES MANAGER APR-81 2, MARTIN SALESMAN SEP-81 1,250 1,400 30

85 3 SQL BALKE MANAGER MAY-81 2, Oracle SQL SQL * Plus SQL SQL SQL 17 alter audit commit* comment create delete drop grant insert lock no audit rename revoke rollback* select update validate SQL SQL SQL Oracle SQL * Plus SQL * Plus SQL SQL SQL * # / accept append break btitle change clear column compute connect copy define del describe disconnect document edit exit get help host input list newpage pause quit remark run save set show spool sqlplus start timming ttitle undefine SQL SQL * Plus SQLPLUS<cr> SQL * PLUS ENTER USERNAME: SCOTT<cr> ENTER PASSWORD: <cr> SQL> SQLPLUS SQL SQL * Plus SQL> HELP SELECT SQL> HELP CREATE SQL> HELP TOPICS TAB DTAB COL CATALOG DESCRIBE

86 74 Oracle 8i char (w) numeric (w, d) w d date raw SQL SQL SQL SQL SQL SQL EMP SQL> SELECT * FROM EMP; SQL> SELECT ENAME FROM EMP; ENAME EMP

87 3 SQL 75 SQL> SELECT EMPNO, ENAME, JOB FROM EMP; SQL> SELECT JOB, ENAME, EMPNO FROM EMP; WHERE WHERE SQL> SELECT ENAME FROM EMP WHERE DEPTNO = 10; ORDER BY SQL> SELECT ENAME FROM EMP; SQL> SELECT ENAME FROM EMP ORDER BY ENAME; ORDER BY DESC SQL> SELECT * FROM DEPT ORDER BY DEPTNO DESC; WHERE (1) WHERE ENAME = SMITH (2) WHERE DEPTNP = 20 (3) WHERE EMP.DEPTNO = DEPT.DEPTNO =!= <> > >= < <= IN( ) BETWEEN AND LIKE

88 76 Oracle 8i % - IS NULL NOT NOT IN IS NOT NULL IN OR SQL> SELECT ENAME, JOB FROM EMP WHERE JOB IN ( CLERK, ANALYST ); NOT IN SQL> SELECT ENAME, JOB FROM EMP WHERE JOB NOT IN ( CLERK, ANALYST ); NOT BETWEEN SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3000; WHERE LIKE S SQL> SELECT ENAME, DEPTNO FEOM EMP WHERE ENAM LIKE S% ; K SQL> SELECT ENAME, DEPTNO FROM EMP WHERE ENAME LIKE %K ; W SQL> SELECT ENAME, DEPTNO; FROM EMP; WHERE ENAME LIKE w--- ; % NOT LIKE SALES SQL> SELECT ENAME, JOB FROM EMP WHERE JOB NOT LIKE SALES% ; NULL

89 3 SQL 77 Oracle SQL> SELECT ENAME, JOB FROM EMP WHERE COMM IS NULL; SQL> SELECT ENAME, JOB FROM EMP WHERE COMM IS NOT NULL; WHERE SQL> SELECT ENAME, JOB FROM EMP WHERE DEPTNO = 20; SQL> SELECT ENAME, JOB FROM EMP WHERE DEPTNO = 20 AND JOB!= CLERK AND OR SQL> SELECT ENAME, JOB FROM EMP WHERE DEPTNO = 20 AND JOB!= CLERK ; SQL> SELCT ENAME, JOB FROM EMP WHERE DEPTNO = 20 OR JOB!= CLERK ; WHERE AND OR AND OR WHERE DEPTNO = 30 AND JOB = SALESMAN OR SAL > SQL + - * / SQL SELECT WHERE ORDER BY

90 78 Oracle 8i FROM 5% SQL> SELECT, SAL, COMM, COMM/SAL FROM EMP WHERE COMM > 0.05 * SAL ORDER BY COMM/SAL DESC; 12 * (SAL + COMM) 12 * SAL + COMM d 6-MAR =8-MAR h 6-MAR /24 = 6-MAR-87 and 2hrs s 6-MAR /(24 * 60 * 60) = 6-MAR-87 and 15secs SELECT SQL> SELECT ENAME EMPLOYEE FROM EMP WHERE DEPTNO = 10; SELECT SELECT 5% SQL> SELECT ENAME, SAL, COMM, COLL/SAL C/S RATIO FROM EMP WHERE COMM > 0.05 * SAL ORDER BY COMM/SAL DESC; ORDER BY SQL SQL SQL SQL SQL SQL LIST L SQL LIST4 SQL CHANGE C... C/ (...) / ( ANALYST ) / INPUT I SQL APPEND A DEL RUN SQL / SQL

91 3 SQL 79 EDIT SQL DESCRIBE 3-2 SQL> INSERT INTO DEPT VALUES (10, ACCOUNTING, NEW YORK ); INSERT SQL> INSERT INTO DEPT (DNAME, DEPTNO) VALUES ( ACCOUNTING, 10); VALUES SQL> INSERT INTO EMP (EMPNO, ENAME, DEPTNO) SELECT ID, NAME, DEPARTMENT FROM OLD-EMP WHERE DEPARTMENT IN (10, 20, 30, 40); INSERT & SQL> INSERT INTO DEPT VALUES (&DEPTNO, &DNAME, &LOC); SQL * Plus SQL> INSERT INTO DEPT VALUES (&DEPTNO, &DNAME, &LOC ); INSERT VALUES NOT NULL

92 80 Oracle 8i SQL> INSERT INTO DEPT VALUES (50, EDUCATION, NULL); - - STONE SQL> INSERT EMP (EMPNO, ENAME, HIREADATE) VALUES (7963, STONE, 07-APR-87 ); SYSDATE KOHN SQL> INSERT INTO EMP (EMPNO, ENAME, HIREDATE) VALUES (7600, KOHN, SYSDATE); SQL UPDATE MARTIN SQL> UPDATE EMP SET JOB = MANAGER WHERE ENAME = MARTIN ; WHERE SET UPDATE SQL> UPDATE EMP SET JOB = MARKET REP WHERE JOB = SALESMAN ; UPDATE SET 40 SQL> UPDATE EMP SET DEPTNO = 40, JOB = MARKET REP WHERE JOB = SALESMAN ; DELETE

93 3 SQL 81 WHERE WHERE MARTIN SQL> DELETE FROM EMP WHERE EMPNO = 7654; COMMIT SQL COMMIT SQL> COMMIT SQL COMMIT ALTER, AUDIT, COMMENT, CONNECT, CREATE, DISCONNECT, DROP, EXIT, GRANT, NOAUDIT, QUIT, REVOKE, RENAME SQL * Plus SET INSERT UPDATE DELETE SQL> SET AUTOCOMMIT ON SQL ROLLBACK ROLLBACK COMMIT SQL> ROLLBAK EMP DEPTNO DEPT 3.5 Oracle 254 SQL SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME CHAR(14), LOC CHAR(13)); A Z a z # -

94 82 Oracle 8i 30 Oracle Oracle CHAR(N) N VARCHAR2(N) N 2000 NUMBER(N, D) N D DATE DATE Oracle LONG 2G RAW(IN) 255 LONG RAW ROW 2G ROWID block.row.file EMP NOT NULL DEPT DEPTNO SQL> CREATE TABLE DEPT (DEPTNO NUMBER (2) NOT NULL, DNAME CHAR (14), LOC CHAR (13)); SQL> ALTER TABLE DEPT ADD (HEADCNT NUMBER (3)); ALTER TABLE SQL> ALTER TABLE DEPT MODIFY DNAME CHAR (20); Oracle CURRVAL Oracle NEXTVAL

95 3 SQL 83 ROWID ROWNUM ORDER BY ORDER BY SELECT SQL> CREATE VIEW MANAGERS AS SELECT ENAME, JOB, SAL FROM EMP WHERE JOB = MANAGER ; SELECT SQL> SELECT * FROM MANAGERS; CREATE VIEW viewname (alias, alias...) AS query; SQL> CREATE VIEW MYDEPT (PERSON, TITLE, SALARY) AS SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = 10; WITH CHECK OPTION SQL> CREATE VIEW DEPT20 AS SELECT ENAME, JOB, SAL, DEPTNO FROM EMP WHERE DEPTNO = 20 WITH CHECK OPTION; SQL> UPDATE DEPT20 SET DEPTNO = 30 WHERE ENAME = WARD ; AS CREATE TABLE AS

96 84 Oracle 8i SQL> CREATE TABLE EMP2 AS SELECT * FROM EMP; SQL DROP TABLE SQL DROP VIEW 3.6 SQL * Plus SQL 2000 SQL> SELECT DEPTON, ENAME, SAL FROM EMP WHERE SAL > 2000; SQL * Plus SQL* Plus MON MAR 12 page1 SAMPLE REPORT for HITECH CORP DEPARTMENT NAME SALARY 10 KING $5,000 CLARK $2,450 * * * * * * * sum $7, FORD $3,000 SCOTT $3,000 JONES $2,975 * * * * * * * sum $8, BLAKE $2,850 * * * * * * * sum $2,850 STRICTLY CONFIDENTIAL

97 3 SQL 85 SQL> COLUMN DEPTNO HEADING DEPARTMENT SQL> COLUMN ENAME HEADING NAME SQL> COLUMN SAL HEADING SALARY SQL> COLUMN SAL FORMAT 99, SQL> TTITLE SAMPLE REPORT for HITECH CORP. SQL> BTITLE STRICTLY CONFIDENTIAL SQL> BREAK ON DEPTNO SQL> COMPUTE SUM OF SAL ON DEPTNO SQL> RUN SQL * Plus SQL * Plus TTITLE BTITLE TTITLE TTITLE BTITLE LEFT RIGHT CENTER SQL> TTITLE OFF SQL> BTITLE OFF HEADING SQL> COLUMN ENAME HEADING EMPLOYEE SQL> COLUMN ENAME HEADING EMPLOYEE NAME SQL> COLUMN ENAME CLEAR LIKE SQL> COLUMN ENAME FORMAT A15 SQL> COLUMN SAL FORMAT 99, SQL> COLUMN COMM LIKE SAL BREAK ON BREAK ON ORDER BY OEDER BY BREAK ON SQL> BREAK ON DEPTNO SQL> SELECT DEPTNO, ENAME FROM EMP ORDER BY DEPTNO; BREAK ON BREAK ON 1 ON 2

98 86 Oracle 8i SQL> BREAK ON DEPTNO SKIP2 SQL> SELECT DEPTNO ENAME FROM EMP ORDER BY DEPTNO; BREAK CLEAR BREAK BREAK ON BREAK ON PAGE BREAK ON REPORT BREAK BREAK ON PAGE ON REPORT SQL> BREAK ON DEPTNO SKIP 2 SQL> COMPUTE SUM OF SAL ON DEPTNO SQL> SELECT DEPTNO, ENAME, SAL FROM EMP ORDER BY DEPTNO; BREAK SKIP COMPUTER AVG COUNT MAX MIN STD VAR NUMBER COMPUTER COMPUTER OF ON COMPUTE CLEAR COMPUTER SQL * Plus SHOW SHOW SHOW SHOW ALL SET SET SET SQL> SHOW AUTOCOMMIT SQL> SET AUTOCOMMIT ON SET SET AUTOCOMMIT { OFF ON IMMEDIATE } ON IMMEDIATE OFF SET FEEDBACK { OFF ON } ON OFF ON SET HEADING { OFF ON } ON OFF ON

99 3 SQL 87 SET PAGESIZE { N } SET PAUSE { OFF ON Text } ON OFF OFF SET BUFFER SQL SQL SQL SQL * Plus SET SQL * Plus LOGIN.SQL SET NULL 30 NO DATE SQL> SET NULL NO DATA SQL> SELECT ENAME, COMM FROM EMP WHERE DEPTNO = 30; SQL SAVE.SQL RESEARCH.SQL SQL> INPUT 1. SELECT EMPNO, ENAME, JOB 2. FROM EMP 3. WHERE JOB = ANALYST 4 SQL> SAVE RESEARCH SQL * Plus SQL> EDIT EDIT SQL * Plus EDIT.SQL SQL> EDIT RESEARCH SQL> RUN START SQL RUN SQL> GET RESEARCH SQL> RUN START SQL * Plus SQL GET START.SQL SPOOL

100 88 Oracle 8i SQL> SPOOL TRYFILE SQL> SPOOL OFF SQL> SPOOL OUT SQL * Plus SQL * Plus SQL SQL SQL> EDIT TRYFILE SET ECHO OFF SET AUTOCOMMIT OFF SET PAGESIZE 25 INSERT INTO EMP (EMPNO, ENAME, HIREDATE) VALUES (9999, GEIGER, SYSDATE); INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (3333, SAMSON, 20); SPOOL NEW-EMP SELECT * FROM EMP WHERE DEPTNO = 20 OR DEPTNO IS NULL / SPOOL OFF SET AUTOCOMMIT OFF SQL> START TRYFILE SQL * Plus SELECT WHERE ORDER BY SBUSTR

101 3 SQL 89 (DNAME, 1, 4) 6 SQL> SELECT ENAME FROM EMP WHERE LENGTH (ENAME) = 6; SQL SQL * Plus 15 INITCAP (ename) jack smith Jack Smith smith-jones Smith-Jones smith, jack Smith, Jack LENGTH (ename) LENGTH ( ALLEN ) = 5 LENGTH ( KING ) = 4 SUBSTR (job, s, l) s 1 SUBSTR ( CLERK, 1, 4) = CLER SUBSTR ( SALESMAN, 1, 4) = SALE LOWER UPPER LEAST GREATEST ADD-MONTHS ADD-NONTHS (hiredate, 5) MONTHS-BETWEEN MONTHS-BETWEEN (sysdate, hiredate) NEXT-DAY NEXT-DAY (hiredate, friday ) SQL 20 SQL> SELECT ENAME, SAL, NEXT-DAY (SYSDATE, FRIDAY ) AS-OF FROM EMP WHERE DEPTNO = 20; Oracle TO-CHAR TO-CHAR (

102 90 Oracle 8i SQL> SELECT ENAME, TO-CHAR (HIREDATE, Dy, Mon dd, yyyy ) HIRED FROM EMP WHERE DEPTNO = 10; ENAME HIRED CLARK Tue Jun 09, 1981 KING Tue Nov 19, 1981 MILLER Sat Jan 23, 1982 TO-DATE TO-CHAR Oracle TO-DATE SQL> INSERT INTO EMP (EMPNO, ENAME, HIREDATE) VALUES (7999, ASMS, TO-DATE ( , MMDDYYHHMISS )); DD 12 DY FRI DAY FRIDAY DDSPTH TWELFTH MM 03 MON MAR MONTH MARCH YY 87 YYYY 1987 FM FX FM TO-CHAR FX TO-CHAR Mar 12, 1987 Mon dd, yyyy MAR 12, 1987 MON dd, yyyy Thursday MARCH 12 Day MONTH dd Mar 1211: 00 am Mon dd hh: mi am Thu, the twelfth Dy, the ddspth LEAST

103 3 SQL 91 SQL> SELECT ENAME, EMPNO, MGR, LEAST (EMPNO, MGR) LOWNUM FROM EMP WHERE EMPNO < MGR; ABS (COMM-SAL) GREATEST (SAL, COMM) ROUND (SAL, 0) SIGN (COMM-SAL) COMM-SAL < 0-1 COMM-SAL = 0 0 COMM-SAL > 0 +1 TRUNC (COMM-SAL) Oracle NVL NVL ( 1 2) SQL> SELECT ENAME, COMM + SAL, NVL (COMM, 0) + SAL FROM EMP WHERE DEPTNO = 30; DROUP BY SQL> SELECT SUM (COMM) FROM EMP; SQL> SELECT ENAME, SUM (COMM) FROM EMP; MIN MAX COUNT SQL> SELECT MIN (ENAME) FROM EMP; SQL> SELECT MIN (HIREDATE) FROM EMP; SQL> SELECT MIN (SAL) FROM EMP; COUNT COUNT DISTINCT SQL> SELECT COUNT (JOB) FROM EMP; SQL> SELECT COUNT (DISTINCT JOB)

104 92 Oracle 8i FROM EMP; AVG SQL> SELECT AVG (SAL) FROM EMP; STDDEV, SQL> SELECT STDDEV (SAL) FROM EMP; SUM SQL> SELECT SUM (SAL) FROM EMP; GROUP BY SQL> SELECT DEPTNO, SUM (SAL), AVG (SAL) FROM EMP GROUP BY DEPTNO; SQL> SELECT DEPTNO, COUNT (*) FROM EMP GROUP BY DEPTNO; SQL> SELECT DEPTNO, JOB, COUNT (*) FROM EMP GROUP BY DEPTNO, JOB; HAVING HAVING WHERE WHERE SELECT HAVING GROUP 9000 SQL> SELECT DEPTNO, SUM (SAL) FROM EMP GROUP BY DEPTNO HAVING SUM (SAL) > 9000; SELECT 1 2 N FROM 1 2 N WHERE GROUP BY 1 2 N HAVING ORDER BY 1 2 N

105 3 SQL SQL> SELECT DEPTNO, SUM (SAL) FROM EMP WHERE JOB!= CLERK GROUP BY DEPTNO HAVING SUM (SAL) > 8000 ORDER BY SUM (SAL); SQL> SELECT EMPNO, ENAME, JOB, EMP, DEPTNO, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; WHERE WHERE SQL> SELECT ENAME, EMP.DEPTNO, LOC FROM EMP, DEPT; (+) SQL> SELECT ENAME, DEPT.DEPTNO, LOC FROM EMP, DEPT WHERE EMP.DEPTNO (+) = DEPT.DEPTNO; FROM SQL> SELECT WORKER.ENAME, MANAGER.ENAME, MANAGER FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO; WHERE SQL> SELECT ENAME, SAL

106 94 Oracle 8i FROM EMP, SALGRADE WHERE GRADE = 3 AND SAL BETWEEN LOSAL AND HISAL; SQL (UNION) (INTERSECT) (MINUS) ACCOUNT VIEW ENAME SAL JOB CLARE 2,450 MANAGER KING 5,000 PRESIDENT MILLER 1,300 CLERK SALES VIEW ENAME SAL JOB ALLEN 1,600 SALESMAN WORD 1,2500 SALESMAN MARTIN 1,250 SALESMAN BLAKE 1,500 SALESMAN TURNER 1,500 SALESMAN JAMES 950 CLERK RESEARCH VIEW ENAME SAL JOB SMITH 800 CLERK JONES 2,975 MANAGER SCOTT 3,000 ANALYST ADAMS 1,100 CLERK FORD 3,000 ANALYST

107 3 SQL SQL> SELECT ENAME, SAL FROM ACCOUNT WHERE SAL > 2000 UNION SELECT ENAME, SAL FROM RESEARCH WHERE SAL > 2000 UNION SELECT ENAME, SAL FROM SALES WHERE SAL > 2000 SQL> SELECT JOB FROM ACCOUNT INTERSECT SELECT JOB FROM RESEARCH INTERSECT SELECT JOB FROM SALES; SQL> SELECT JOB FROM ACOUNT MINUS SELECT JOB FROM SALES; WHERE SMITH SQL> SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = SMITH );

108 96 Oracle 8i SMITH ORDER BY CLARK CLARK SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = CLARK ) OR SAL > (SELECT SAL FROM EMP WHERE ENAME = CLARK ); SCOTT SQL> SELECT ENAME, JOB, SAL FROM EMP, DEPT WHERE LOC = NEW YORK AND EMP.DEPTNO = DEPT.DEPTNO AND SAL > (SELECT SAL FROM EMP WHERE ENAME = SCOTT ); SCOTT SCOTT SCOTT SQL> SELECT ENAME, MIN (HIREDATE) FROM EMP; SQL> SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE = (SELECT MIN (HIREDATE) FROM EMP); 3.9 SQL

109 3 SQL 97 Oracle DEPT GRANT REVOKE EMP GRANT SELECT INSERT UPDATE DELETE ALTER INDEX SCOTT SCOTT SQL> GRANT SELECT, INSERT ON EMP TO SCOTT; SQL> GRANT ALL ON EMP TO SCOTT; SELECT ALLEN EMP EMPLOYEE EMPLOYEE SQL> SELECT * FROM SCOTT.EMP; SQL> CREATE SYNONYM EMPLOYEE FOR ALLEN.EMP; SQL> GRANT ALL ON EMP TO SCOTT WITH GRANT ONTION; WITH ADMIN OPTION SQL> GRANT CREATE SESSION TO SCOTT WITH ADMIN OPTION; SQL> REVOKE INSERT ON EMP FROM SCOTT;

110 98 Oracle 8i Oracle ACCTS-PAY SQL> CREATE ROLE ACCTS-PAY; SQL> GRANT CREATE SESSION TO ACCTS-PAY; SQL> GRANT INSERT ON EMP TO ACCTS-PAY; SQL> GRANT ACCTS-PAY TO SCOTT; Oracle DBA RESOURCE CONNECT EXP-FULL-DATABASE IMP-FULL-DATEBASE 3.10 RDBMS SQL> CREATE INDEX EMP-ENAME ON EMP (ENAME); SQL> DROP INDEX EMP-ENAME; 50 SQL EMP SQL> CREATE UNIQUE INDEX EMP-EMPNO ON EMP (EMPNO); 3.11

111 3 SQL 99 INSERT UPDATE DELETE FOR UPDATE SELECT Oracle DML INSERT UPDATE DELETE FOR UPDATE SELECT LOCK TABLE DDL Oracle Oracle 3-5 DML Oracle DML Statement Row Locks? Mode of Table Lock SELECT FROM table INSETR INTO table RX UPDATE table RX DELETE FROM table RX SELECT FROM table FOR UPDATE OF LOCK TABLE table IN ROW SHARE MODE ROW EXCLUSIVE MODE SHARE MODE SHARE EXCLUSIVE MODE EXCLUSIVE MODE RS RS RX X SRX X RS: rowshare RX: row exclusive S: share SRX: share row exlusive X: exclusive 3-5 DML LOCK TABLE 3-6 Oracle 3-6 LOCK TABLE

112 100 Oracle 8i lockmode ROW SHARE EXCLUSIVE ROW EXCLUSIVE EXCLUSIVE SHARE ROW EXCLUSIVE SHARE SHARE UPDATE ROW SHARE SHARE FOR UPDATE SELECT SHARE SHARE ROW EXCLUSIVE ROW EXCLUSIVE EXCLUSIVE SHARE ROW EXCLUSIVE FOR UPDATE SELECT SHARE SHARE ROW EXCLUSIVE ROW EXCLUSIVE EXCLUSIVE EXCLUSIVE 3.12 SQL * Plus DBA EXECUTE AUTOTRACE COPY SQL * Plus glogin.sql login.sql SQL * Plus glogin.sql login.sql Oracle SQL * Plus SQL * Plus / SQL glogin.sql login.sql glogin.sql ORACLE_HOME/sqlplus/admin Oracle SQL * Plus SQL PL/SQL SQL * Plus SQL * Plus glogin.sql Windows 95/NT 4.0 glogin.sql %ORACLE_HOME%\PLUSnn nn SQL * Plus SQL * Plus 3.3 %ORACLE_HOME%\PLUS33 1 SQL * Plus SQL * Plus SQLPATH SQL * Plus SQLPATH login.sql SQL * Plus SQLPATH login.sql SQL * Plus SQL * Plus SQL * Plus login.sql SQLPATH SQLPATH=.: HOME:<other_directory> ; export SQLPATH _(Bourne/Korn shell)

113 3 SQL 101 set SQLPATH=(. HOME <other_directory>) _C shell Windows 95/NT 4.0 SQLPATH ORACLE_ HOME\DBS ORACLE_HOME Windows 95 C:\ORAWIN 95 NT 4.0 C:\ORANT Oracle C: Oracle C: Windows 95/NT 4.0 login.sql %ORACLE_HOME%\DBS SQLPATH Windows 95/NT 4.0 SQLPATH (1) Start Run (2) regedit.exe/regedit32.exe Windows 95/NT 4.0 (3) OK (4) HKEY_LOCAL_MACHINE (5) SOFTWARE (6) Oracle (7) SQLPATH (8) Edit String Value Data SQLPATH (9) OK (10) Registry Exit (11) Windows NT 4.0 SQLPATH SQL * Plus SQL * Plus SQL glogin.sql login.sql _editor SQL * Plus vi glogin.sql login.sql define_editor = vi; Oracle 8.1.x Edit Editor Define editor Notepad vi vi 2. / SQL * Plus SQL * Plus sqlplus [[ -S[ILENT]] [ logon ] [ start ] -? SQL * Plus -S[ILENT] SQL * Plus SQL * Plus [logon] username [/password] [@connect_string] / /NOLOG [start] SQL * Plus SQL * Plus SQL PL/SQL [.exe] [arg...] / SQL * Plus

114 102 Oracle 8i SQL * Plus SQL * Plus SQL> (1) SQL / (2) PL/SQL (3) SQL PL/SQL SQL * Plus SQL * Plus SQL ; SQL * Plus PL/SQL PL/SQL PL/SQL / 3 SQL SQL SQL * Plus SQL SQL SQL * Plus SQL PL/SQL SQL * Plus SQL * Plus 3-1 SQL * Plus 3-1 SQL SQL * Plus APPEND A CHANGE / C / CHANGE / C / CLEAR BUFFER CL BUFF DEL ( ) INPUT I INPUT I LIST L SQL * Plus LIST n L n n n LIST * L * LIST m n L m n m n LIST LAST L LAST LIST SQL PL/SQL LIST SQL ; 3-2

115 3 SQL SQL SQL> LIST 1 SELECT empno, deptno, job, sal, comm 2 FROM emp 3 WHERE comm. IS NOT NULL SQL> SELECT empno, empname, deptno, job, sal, comm 2 FROM emp 3 WHERE comm IS NOT NULL; SELECT empno, empname, deptno, job, sal, comm. ERROR at line 1: ORA-00904: invalid column name 4 SQL * Plus SQL * Plus SQL * Plus DESCRIBE SQL SQL * Plus / SQL * Plus SQL * Plus SQL>DESCRIBE EMP Name Null? Type EMPNO NOT NULL NUMBER(4) ENAME JOB MGR HIREDATE VARCHAR2(10) VARCHAR2(9) NUMBER(4) DATE SAL NUMBER(7, 2) COMM NUMBER(7, 2) DEPTNO NOT NULL NUMBER(2) SQL> LIST NO lines in SQL buffer. SQL * Plus INPUT SQL * Plus 3-5

116 104 Oracle 8i 3-5 SQL * Plus SQL> INPUT 1 DESCRIBE EMP 2 SQL> L 1* DESCRIBE EMP SQL * Plus 3-6 SQL * Plus SQL> RUN 1* DESCRIBE EMP DESCRIBE EMP * ERROR at line 1: ORA-00900: invalid SQL statement SQL> SAVE test Created file test. SQL> GET test 1* DESCRIBE EMP 3-6 SQL * Plus SQLPATH 5 SQL * Plus SQL * Plus HOME END SQL * Plus SQL * Plus SQL * Plus EDIT EIDT EDIT [file_name [.ext]] glogin.sql login.sql _editor file_name SQL * Plus SQL * Plus DEFINE _editor DEFINE _editor = emacs; _editor EDIT Windows 95 Notepad)) EDIT SQL * Plus SQL * Plus EDIT SQL * Plus SQL afiedt.buf SQLPATH EDIT C:\MYDIR\MYFILE.SQL

117 3 SQL 105 SQL> EDIT Wrote file afiedt.buf editfile SQL * Plus SQL> SHOW editfile editfile afiedt.buf SQL> SET editfile buffer.ext SQL> SHOW editfile editfile buffer.txt EDIT SQL SQL * Plus SQL> CLEAR BUFFER buffer cleared SQL> EDIT Nothing to save..sql SQL * Plus file_name.sql.sql SUFFIX SQL> SHOW SUFFIX suffix SQL SQL> SET SUFFIX sh SQL> SHOW SUFFIX suffix sh SUFFIX.lst.lis 6 SQL * Plus SQL PL/SQL (1) (2) SQL (3) SQL SQL PL/SQL SQL * Plus RUN / RUN R[UN] RUN SQL PL/SQL SELECT empno, ename, FROM emp RUN 3-10

118 106 Oracle 8i 3-10 RUN SQL> RUN 1* SELECT empno, ename FROM emp EMPNO ENAME 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FPRD 7934 MILLER 14 rows selected. RUN RUN / RUN SQL PL/SQL / SQL> / EMPNO ENAME 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FPRD 7934 MILLER 14 rows selected. RUN / SQL SQL * Plus PL/SQL ( at )

119 3 SQL 107 START START file_name [.ext] [arg1 arg2...] file_name [.ext] SQL * Plus.sql SQL * Plus START SQL * Plus SQLPATH C:\MYDIR\MYFILE.SQL SQL SQL * Plus PL/SQL EXIT QUIT SQL * Plus [arg1 arg2...] &1 &2...( &&1 &&2...) SQL * Plus &1 &2 START SQL * Plus DELTBL.SQL SQL> START ( at SQL * Plus SQL * Plus START SQL * Plus SQL * Plus sqlplus [.ext] [arg1 arg2...] SQL * Plus (1) EXECUTE SQL * Plus 3.2 EXECUTE SQL * Plus PL/SQL EXECUTE PL/SQL EXECUTE SQL> VARIABLE id NUMBER Define a bind variable SQL> EXECUTE :id := ADD_CASES(10); PL/SQL procedure successfully completed. SQL> PRINT id ID 10 SQL> EXECUTE :ID := ADD_CASES(3); PL/SQL procedure successfully completed. SQL> PRINT id ID 0

120 108 Oracle 8i ADD_CASES :id (2) SQL * Plus/SQL SAVE SQL * Plus SQL SAVE SAV[E] file_name [.ext] [CRE[ATE] [REP[LACE] APP[END]] file_name [.ext] SQL SQL * Plus SQL * Plus.sql SAVE SQLPATH CRE[ATE] REP[LACE] SQL SAVE...REPLACE APP[END] SQL SAVE...APPEND (3) SQL * Plus/SQL SQL * Plus SQL GET GET file_name [.ext] [LIS[T] NOL[IST] GET SQL * Plus / SQL file_name SQL.sql LIS[T] SQL * Plus LIST NOL[IST] GET SQL * Plus SQLPATH Windows 95/NT SQL * Plus SQL * Plus Windows Start In SQL * Plus SQL * Plus COPY Oracle 7.2 CREATE TABLE...UNRECOVERABLE AS SELECT... COPY COPY SQL * Plus COPY (1) / (2) (3) LONG LONG SELECT (4) Oracle Oracle

121 3 SQL 109 COPY COPY {FROM username TO username FROM username TO username {APPEND CREATE INSERT REPLACE} destinaiton_table [(column, column, column...)] USING query username [/password] / Oracle FROM username /password TO username/password FROM TO SQL * Plus SQL * Plus database_specification Net 8 SQL * Net V2 COPY FROM TO destination_table [(column, column, column...)] COPY USING query COPY SELECT FROM username FROM SQL * Plus FROM TO username TO SQL * Plus TO COPY (1) APPEND COPY APPEND (2) CREATE COPY (3) INSERT COPY INSERT USING (4) REPLACE COPY COPY SQL * Plus SET COPY (1) LONG (2) COPYCOMMIT (3) ARRAYSIZE LONG LONG LONG

122 110 Oracle 8i LONG COPY SQL * Plus COPY SQL * Plus SET COPYCOMMIT n SQL * Plus n SET ARRAYSIZE COPY VAX/VMX OpenVMX) COPY (column, column,...) COPY (NUMBER) 38 (DECIMAL) SQL * Plus SQL SQL SQL * Plus SQL SQL * Plus SCOTT SYSTEM 3-13 SQL 3-13 SQL * Plus SQL SQL> CONNECT SYSTEM Enter password: ******* Connected. SQL> SET PAGESIZE 0 SQL> SET HEADING OFF SQL> SET FEEDBACK OFF SQL> SET VERIFY OFF SQL> SET ECHO OFF SQL> SPOOL DELTBL.SQL SQL> SELECT DELETE TABLE_NAME ; 2 FROM DBA_TABLES 3 WHERE OWNER = SCOTT ; DELETE ACCTS; DELETE ACCT_ADDRS; DELETE BONUS; DELETE CITIES; DELETE COMPANY_SUMMARY; DELETE CUSTOMER; DELETE DEPT;

123 3 SQL 111 DELETE DUMMY; DELETE EMP; DELETE FUNDS; DELETE FUND_CONTRIB; DELETE FUND_XACT; DELETE F_EMPCOMP; DELETE F_XACT_TYPE; DELETE INVINFO; DELETE INVREQUEST; DELETE ITEM; DELETE ORD; DELETE ORDER_HISTORY; DELETE PRICE; DELETE PRODUCT; DELETE SALES_REVENUE; DELETE SALGRADE; DELETE STOCK_HISTORY; SQL> SPOOL OFF DELTBL.SQL DELETE ACCTS; DELETE ACCT_ADDRS; DELETE BONUS; DELETE CITIES; DELETE COMPANY_SUMMARY; DELETE CUSTOMER; DELETE DEPT; DELETE DUMMY; DELETE EMP; DELETE FUNDS; DELETE FUND_CONTRIB; DELETE FUND_XACT; DELETE F_EMPCOMP; DELETE F_XACT_TYPE; DELETE INVINFO; DELETE INVREQUEST; DELETE ITEM; DELETE ORD; DELETE ORDER_HISTORY;

124 112 Oracle 8i DELETE PRICE; DELETE PRODUCT; DELETE SALES_REVENUE; DELETE SALGRADE; DELETE DELTBL.SQL (1) SET PAGESIZE 0 (2) SET HEADING OFF SET HEADING OFF SET PAGESIZE 0 (3) SET FEEDBACK OFF (4) SET VERIFY OFF SQL * Plus SQL (5) SET ECHO OFF DELETE.SQL SQL (6) SPOOL DELTBL.SQL SQL * Plus DELTBL.SQL SQL * Plus (.lst.lis) (7) SPOOL OFF DELTBL.SQL SYSTEM SCOTT SQL * Plus DELTBL.SQL SQL * Plus Oracle SQL * Plus SQL SQL * Plus PRODUCT_USER_PROFILE SYSTEM 3-14 PRODUCT_ USER_PROFILE 3-15 PRODUCT_USER_PROFILE 3-14 PRODUCT_USER_PROFILE PRODUCT VARCHAR2(30) USERID ATTRIBUTE SCOPE NUMERIC_VALUE CHAR_VALUE DATE_VALUE LONG_VALUE VARCHAR2(30) VARCHAR2(240) VARCHAR2(240) NUMBER(15,2) VARCHAR2(240) DATE LONG

125 3 SQL PRODUCT_USER_PROFILE PRODUCT SQL * Plus % NULL USERID (%) (%) ATTRIBUTE SCOPE SQL SQL * Plus ( ALTER) SQL * Plus (NULL) SQL * Plus NUMERIC_VALUE SQL * Plus (NULL) SQL * Plus CHAR_VALUE DATE_VALUE DISABLED SQL * Plus (NULL) SQL * Plus LONG_VALUE SQL * Plus (NULL) SQL * Plus SYSTEM PUPBLD.SQL PRODUCT_USER_PROCEDURE Windows 95 Oracle PUPBLD.SQL C:\ORAWIN95\DBS C:\ORAWIN95 Oracle Windows 95 ORACLE_HOME UNIX ORACLE_HOME/sqlplus/admin Oracle UNIX crdb Windows 95/NT builddall PUPBLD.SQL SYSTEM PUPBLD.SQL PRODUCT_USER_ PROFILE PUPBLD.SQL SQL * Plus Warning: Product user profile information not loaded! Error in disabling roles in product user profile. 1. SQL SQL SQL * Plus SYSTEM 3-16

126 114 Oracle 8i 3-16 SQL * Plus PRODUCT USERID ATTRIBUTE CHAR_VALUE SCOPE NUMERIC_VALUE DATE_VALUE LONG_VALUE SQL* Plus DISABLED NULL( ) NULL( ) NULL( ) NULL( ) SCOTT SQL * Plus SYSTEM SQL * Plus 3-17 SQL 3-17 SCOTT HOST INSERT INTO product_user_profile (product, userid, attribute, scope, numeric_value, char_value, date_value, long_value) VALUES ( SQL * Plus, SCOTT, HOST, NULL, NULL, DISABLED, NULL, NULL); SQL * Plus SQL * Plus PRODUCT_USER_PROFILE PRODUCT_USER_PROFILE SQL * Plus 2. SQL SQL SCOTT HOST DELETE [FROM] product_user_profile WHERE userid = SCOTT and attribute = HOST ; PRODUCT_USER_PROFILE SQL * Plus (1) COPY (2) EDIT (3) EXECUTE (4) EXIT (5) GET (6) HOST( HOST UNIX! VMS ) (7) QUIT (8) PASSWORD

127 3 SQL 115 (9) RUN (10) SAVE (11) SET( SQL * Plus SET SET ROLE SET TRANSACTION ) (12) SPOOL (13) START( SQL * Plus SQL SQL (1) ALTER (2) ANALYZE (3) AUDIT (4) CONNECT (5) CREATE (6) DELETE (7) DROP (8) GRANT (9) INSERT (10) LOCK (11) NOAUDIT (12) RENAME (13) REVOKE (14) SELECT (15) SET ROLE (16) SET TRANSACTION (17) TRUNCATE (18) UPDATE PL/SQL (1) BEGIN (2) DECLARE BEGIN DECLARE SQL * Plus EXECUTE EXECUTE 3. SET ROLE SQL SET ROLE SQL * Plus PRODUCT_USER_PROFILE SET ROLE SQL * Plus SQL * Plus 4. PRODUCT_USER_PROFILE UserID Attribute ROLES Char_Value UserID PUBLIC % %

128 116 Oracle 8i PUBLIC PUBLIC Oracle SYSTEM PRODUCT_USER_PROFILE SYSTEM SQL * Plus PRODUCT_USER_PROFILE SQL * Plus SYSTEM PRODUCT_USER_PROFILE SQL * Plus Oracle RDBMS SQL SQL * Plus 3.3 SQL * Plus SQL SET TIME SET TIMING SET TIME SQL * Plus ON SQL * Plus OFF SET TIME SET TI[ME] [OFF ON] OFF 3-19 SET TIME 3-19 SQL> SHOW TIME time OFF SQL> SET TIME ON 01:17:57 SQL> SELECT t1.dname, t2.name, t2.sal, t2.job 01:18:11 2 FROM dept t1, emp t2 01:18:23 3 WHERE t1.deptno = t2.deptno; DNAME ENAME SAL JOB RESEARCH SMITH 800 CLERK SALES ALLEN 1600 SALESMAN SALES WARD 1250 SALESMAN RESEARCH JONES 2975 MANAGER SALES MARTIN 1250 SALESMAN SALES BLAKE 2850 MANAGER ACCOUNTING CLARK 2450 MANAGER RESEARCH SCOTT 3000 ANALYST ACCOUNTING KING 5000 PRESIDENT SALES TURNER 1500 SALESMAN RESEARCH ADAMS 1100 CLERK SALES JAMES 950 CLERK RESEARCH FORD 3000 ANALYST ACCOUNTING MILLER 1300 CLERK 14 rows selected. 01:18:43 SQL> 01:18:43 01:18:23 WHERE Oracle

129 3 SQL 117 SQL * Plus SET TIMING SET TIMING SET TIMI[NG] [OFF ON] OFF SET TIMING ON SQL * Plus SQL PL/SQL SET TIMING OFF 3-20 SET TIMING 3-20 SQL> SHOW TIMING timing OFF SQL> SET TIMING ON SQL> SELECT t1.dname, t2.name, t2.sal, t2.job 2 FROM dept t1, emp t2 3 WHERE t1.deptno = t2.deptno; DNAME ENAME SAL JOB RESEARCH SMITH 800 CLERK SALES ALLEN 1600 SALESMAN SALES WARD 1250 SALESMAN RESEARCH JONES 2975 MANAGER SALES MARTIN 1250 SALESMAN SALES BLAKE 2850 MANAGER ACCOUNTING CLARK 2450 MANAGER RESEARCH SCOTT 3000 ANALYST ACCOUNTING KING 5000 PRESIDENT SALES TURNER 1500 SALESMAN RESEARCH ADAMS 1100 CLERK SALES JAMES 950 CLERK RESEARCH FORD 3000 ANALYST ACCOUNTING MILLER 1300 CLERK 14 rows selected. real: SET TIME SET TIMING SQL * Plus SQL PL/SQL SQL * Plus 3.3 SQL * Plus 3.3 AUTOTRACE AUTOTRACE SQL * Plus SQL * Plus DML SELECT INSERT UPDATE DELETE Oracle SQL_TRACE EXPLAIN PLAN TKPROF SQL SYS PLUSTRCE.SQL SQL PLUSTRCE.SQL PLUSTRCE (1) PLUSTRACE (2) PLUSTRACE V SESSTAT V STATNAME V SESSION