1 / 40

Module 2 Database Installation and Configuration

Module 2 Database Installation and Configuration. Section 3: Database Design. Phase Of Database Design. DB Design. Conceptual Database Design The process of constructing a model of the information used in an enterprise, independent of all physical considerations Logical Database Design

dora
Download Presentation

Module 2 Database Installation and Configuration

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. Module 2 Database Installation and Configuration ITEC 450 Section 3: Database Design

  2. Phase Of Database Design ITEC 450

  3. DB Design • Conceptual Database Design • The process of constructing a model of the information used in an enterprise, independent of all physical considerations • Logical Database Design • The process of constructing a model of the information used in an enterprise based on a specific data model, but independent of a particular DBMS and other physical considerations. • Physical Database Design • The process of producing a description of the implementation of the database on secondary storage; it describes the storage structures and access methods used to archieve efficient access to the data ITEC 450

  4. Phase 2: Conceptual DB Design DBMS-independent high-level data model • Conceptual schema design • Characteristics: expressiveness, simplicity and understandability, minimalism, diagrammatic representation, formality • Approaches: centralized vs. view integration • Outcome: E-R schema • Transaction design (application ) • Technique: identify input/out and functional behavior • Categories: retrieval, update, and mixed ITEC 450

  5. Entity Relationship Model Steps to create E-R model: • Create entities • Identify key attributes • Relate each entity via relationships • Add detailed attributes • Define cardinality • Verify all business operations ITEC 450

  6. Entity Relationship Model • Entity Types An object or concept that is identified by the enterprise as having an independent existence • Attributes A property of an entity or a relationship type • Relationship Types A meaningful association among entity types ITEC 450

  7. NORMALIZATION • Normalization • A technique for producing a set of relations with desirable properties, given the data requirements of an enterprise • UNF is a table that contains one or more repeating groups • 1NF is a relation in which the intersection of each row and column contains one and only one value • 2NF is a relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key. • 3NF is a relation that is in 1NF, 2NF in which no non-primary-key attribute is transitively dependent on the primary key • BCNF is a relation in which every determinant is a candidate key • 4NF is a relation that is in BCNF and contains no trivial multi-valued dependency • 5NF is a relation that contains no join dependency ITEC 450

  8. Phase 4: Logical DB Design • System-independent mapping • From DBMS-independent EER to relational models • Tailoring the schemas to a specific DBMS • Define data types • Create specific constraints • Result of this phase: DDL statements that specify the conceptual and external level schemas (not physical design parameters yet) ITEC 450

  9. Characteristics of Relational Model ITEC 450

  10. Phase 5: Physical DB Design • Choosing specific storage structures • Storage configuration • File organization • Designing access paths • Indexing, clustering, and hashing • Criteria • Response time • Space utilization • Transaction throughput ITEC 450

  11. Decisions • Attribute data types • Physical record descriptions (doesn’t always match logical design) • File organizations • Indexes and database architectures • Query optimization Leads to Physical Design Process Inputs • Normalized relations • Volume estimates • Attribute definitions • Response time expectations • Data security needs • Backup/recovery needs • Integrity expectations • DBMS technology used ITEC 450

  12. File Organization Principles • Fast data retrieval • High throughput for I/O • Efficient use of storage space • Protection from failures or data loss • Accommodating growth ITEC 450

  13. Indexed File Organizations • Index is a data structure used to determine the location of rows in a file for quick retrieval • Indexing design: • B-tree index – a keyed, treelike index structure • Bitmap index – a separate string of zeros and ones used for a column with a very small number of distinct values • Hash Index – a transformed key value using a hash algorithm ITEC 450

  14. Module 2 Database Installation and Configuration ITEC 450 Section 4: Oracle Database Creation

  15. Assignment 3-1-1: Do Not Create A Starter Database during Installation ITEC 450

  16. Enterprise Edition ITEC 450

  17. Summary of Installation ITEC 450

  18. Creating Database ITEC 450

  19. Creating Database ITEC 450

  20. Creating Database ITEC 450

  21. DBCA Creation Demo ITEC 450

  22. Choosing Configuration • Important configuration tasks: • Choose a database type • Transactional, data warehouse, or hybrid • How should the database be managed? • OEM Grid Control or OEM Database Control • Decide on the DBA authentication method • Select a storage mechanism • OS, ASM, or raw devices • Decide on the file management method • Specified or set using OMF • Set the initial parameters (init.ora) ITEC 450

  23. DBA Authentication Methods • The DBA authentication method encompasses the method used to validate logon of users with the SYSDBA or SYSOPER role • SYSDBA: ADMIN role and can CREATE DATABASE • SYSOPER: has system privileges to start up, shut down, and back up the database, and modify database components • Two authentication methods: • OS authentication • Password file authentication ITEC 450

  24. Operating System (OS) Authentication • User logs without specifying user name/password • To set up OS authentication, follow these steps: • Create OS user for the DBA • Unix only: Create an OSDBA group • Optional: Create an OSOPER group • Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to NONE • Assign OS user to OSDBA or OSOPER group • Create Oracle user in DB with same name • To log on to SQL*Plus using OS authentication: sqlplus /nolog CONNECT /@ORACLASS AS SYSDBA ITEC 450

  25. Password File Authentication • Encrypted file contains user names and passwords • To set up password file authentication: • Create a new password file (orapwd) • Windows: PWD<sid>.ORA, in ORACLE_HOME\database • Unix: orapw<sid>.ora, typically in ORACLE_HOME/dbs • REMOTE_LOGIN_PASSWORD FILE = EXCLUSIVE • Log on to DB with SYSDBA privileges • Create the new DBA user name if needed • Grant SYSDBA or SYSOPER privilege to user ITEC 450

  26. INITIALIZATION PARAMETERS ITEC 450

  27. INITIALIZATION PARAMETERS ITEC 450

  28. INITIALIZATION PARAMETERS ITEC 450

  29. CREATING DATABASE • To create a database you can use: • Database Configuration Assistant (DBCA tool) • CREATE DATABASE command • You can use the DBCA tool to generate scripts for creating a database manually • You can use these scripts if you have multiple consistent databases to create at different sites • CREATE DATABASE gives you greater flexibility but unnecessary complexity with settings • You do need to be familiar with its syntax ITEC 450

  30. DBCA – Choose Template ITEC 450

  31. DBCA – Name DatabaseNote: Fill Global database name only, SID will be filled automatically. You will use a different SID for your assignment. ITEC 450

  32. DBCA – DBA PasswordsNote: Here is Sys and System password defined. ITEC 450

  33. DBCA – Add Sample Schemas ITEC 450

  34. DBCA – StorageNote: Here is the place to delete Redo Log Group 3 ITEC 450

  35. DBCA – Create Database ITEC 450

  36. DBCA – Database Created ITEC 450

  37. Physical Database Structure Initialization and Administration files under: • C:\app\Administrator\product\11.2.0\dbhome_1\admin\orcl450 Network files under: • C:\app\Administrator\product\11.2.0\dbhome_1\network\admin • Main types of files – data files, control files, redo log files. • Initialization files – init.ora, SPFILE • Network files – tnsnames.ora, listener.ora • Administration files – alert.log, trace files ITEC 450

  38. Starting and Stopping the Instance and Database • To shut down a running database using SQL*Plus: • Start a Command Prompt window (or shell) • Start up SQL*Plus without logging: sqlplus /nolog • Connect as SYS with SYSDBA CONNECT SYS/<password>@trial01 AS SYSDBA • Type SHUTDOWN IMMEDIATE and press Enter Database closed. Database dismounted. ORACLE instance shut down. • There are four options for SHUTDOWN: • NORMAL, TRANSACTIONAL, IMMEDIATE, ABORT ITEC 450

  39. Starting and Stopping the Instance and Database • To start DB, change step 4 in previous slide: STARTUP PFILE=ORACLE_BASE\admin\trial01\pfile\inittrial01.ora • PFILE parameter needed if you have not created the SPFILE • STARTUP options: • NOMOUNT, MOUNT, OPEN, PFILE ITEC 450

  40. The Alert Log and Trace Files • The alert log is essential because it will contain all the essential information for the smooth running of your database • All critical errors will be written to the alert log • Trace files contain more detailed log and tracing information about general processing • Trace files can be used to track down problems not causing critical failures, generally using special tools • E.g. , TKPROF Note: Some of the slides are from Oracle 10g Database Administrator: Implementation and Administration by Gavin Powell and Carol McCullough-Dieter ITEC 450

More Related