Oracle OCP SQL Introduction

Copyright©OracleCorporation,2001.Allrightsreserved.IntroductionI-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•ListthefeaturesofOracle9i•Discussthetheoreticalandphysicalaspectsofarelationaldatabase•DescribetheOracleimplementationoftheRDBMSandORDBMSI-3Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9iScalabilityReliabilitySingledevelopmentmodelCommonskillsetsOnemanagementinterfaceOnevendorI-4Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9iI-5Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9iApplicationServerBusinessIntelligenceBusinessintelligenceTransactionalAppsTransactionalAppsPortalsPortalsAPACHEIntegrationIntegrationI-6Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9iDatabaseMultimediaMultimediaObjectRelationalDataObjectRelationalDataMessagesMessagesDocumentsXMLDocumentsXMLI-7Copyright©OracleCorporation,2001.Allrightsreserved.RelationalandObjectRelationalDatabaseManagementSystem•Relationalmodelandobjectrelationalmodel•User-defineddatatypesandobjects•Fullycompatiblewithrelationaldatabase•Supportofmultimediaandlargeobjects•High-qualitydatabaseserverfeaturesI-8Copyright©OracleCorporation,2001.Allrightsreserved.OracleInternetPlatformClientsSystemmanagementNetworkservicesDatabasesApplicationserversDevelopmenttoolsInternetapplicationsPresentationandbusinesslogicBusinesslogicanddataAnybrowserAnyFTPclientAnymailclientJavaSQLPL/SQLI-9Copyright©OracleCorporation,2001.Allrightsreserved.SystemDevelopmentLifeCycleStrategyandanalysisDesignBuildanddocumentTransitionProductionI-11Copyright©OracleCorporation,2001.Allrightsreserved.DataStorageonDifferentMediaElectronicspreadsheetFilingcabinetDatabaseI-12Copyright©OracleCorporation,2001.Allrightsreserved.RelationalDatabaseConcept•Dr.E.F.Coddproposedtherelationalmodelfordatabasesystemsin1970.•Itisthebasisfortherelationaldatabasemanagementsystem(RDBMS).•Therelationalmodelconsistsofthefollowing:–Collectionofobjectsorrelations–Setofoperatorstoactontherelations–DataintegrityforaccuracyandconsistencyI-13Copyright©OracleCorporation,2001.Allrightsreserved.DefinitionofaRelationalDatabaseArelationaldatabaseisacollectionofrelationsortwo-dimensionaltables.OracleserverTableName:EMPLOYEESTableName:DEPARTMENTS……I-14Copyright©OracleCorporation,2001.Allrightsreserved.DataModelsModelofsysteminclient’smindEntitymodelofclient’smodelTablemodelofentitymodelTablesondiskOracleserverI-15Copyright©OracleCorporation,2001.Allrightsreserved.•Createanentityrelationshipdiagramfrombusinessspecificationsornarratives•Scenario–“...Assignoneormoreemployeestoadepartment...”–“...Somedepartmentsdonotyethaveassignedemployees...”EntityRelationshipModelEMPLOYEE#*number*nameojobtitleDEPARTMENT#*number*nameolocationassignedtocomposedofI-16Copyright©OracleCorporation,2001.Allrightsreserved.EntityRelationshipModelingConventionsEntitySoftboxSingular,uniquenameUppercaseSynonyminparenthesesAttributeSingularnameLowercaseMandatorymarkedwith“*”Optionalmarkedwith“o”UniqueIdentifier(UID)Primarymarkedwith“#”Secondarymarkedwith“(#)”EMPLOYEE#*number*nameojobtitleDEPARTMENT#*number*nameolocationassignedtocomposedofI-18Copyright©OracleCorporation,2001.Allrightsreserved.RelatingMultipleTables•Eachrowofdatainatableisuniquelyidentifiedbyaprimarykey(PK).•Youcanlogicallyrelatedatafrommultipletablesusingforeignkeys(FK).TableName:EMPLOYEESTableName:DEPARTMENTSPrimarykeyPrimarykeyForeignkey…I-19Copyright©OracleCorporation,2001.Allrightsreserved.RelationalDatabaseTerminology123456I-20Copyright©OracleCorporation,2001.Allrightsreserved.RelationalDatabasePropertiesArelationaldatabase:•Canbeaccessedandmodifiedbyexecutingstructuredquerylanguage(SQL)statements•Containsacollectionoftableswithnophysicalpointers•UsesasetofoperatorsI-21Copyright©OracleCorporation,2001.Allrightsreserved.CommunicatingwithaRDBMSUsingSQLSELECTdepartment_nameFROMdepartments;SQLstatementisentered.OracleserverStatementissenttoOracleServer.I-22Copyright©OracleCorporation,2001.Allrightsreserved.RelationalDatabaseManagementSystemUsertablesDatadictionaryOracleserverI-23Copyright©OracleCorporation,2001.Allrightsreserved.SQLStatementsSELECTINSERTUPDATEDELETEMERGECREATEALTERDROPRENAMETRUNCATECOMMITROLLBACKSAVEPOINTGRANTREVOKEDataretrievalDatamanipulationlanguage(DML)Datadefinitionlanguage(DDL)TransactioncontrolDatacontrollanguage(DCL)I-24Copyright©OracleCorporation,2001.Allrightsreserved.TablesUsedintheCourseEMPLOYEESDEPARTMENTSJOB_GRADESI-25Copyright©OracleCorporation,2001.Allrightsreserved.Summary•TheOracle9iServeristhedatabaseforInternetcomputing.•Oracle9iisbasedontheobjectrelationaldatabasemanagementsystem.•Relationaldatabasesarecomposedofrelations,managedbyrelationaloperations,andgovernedbydataintegrityconstraints.•WiththeOracleServer,youcanstoreandmanageinformationbyusingtheSQLlanguageandPL/SQLengine.