guide to oracle 10 g n.
Skip this Video
Loading SlideShow in 5 Seconds..
Guide to Oracle 10 g PowerPoint Presentation
Download Presentation
Guide to Oracle 10 g

play fullscreen
1 / 52

Guide to Oracle 10 g

648 Views Download Presentation
Download Presentation

Guide to Oracle 10 g

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Guide to Oracle 10g Chapter 1: Client/Server Databases and the Oracle 10g Relational Database

  2. Objectives • Develop an understanding of the purpose of database systems • Describe the purpose and contents of an entity-relationship model • Explain the steps in the normalization process • Describe the differences between personal and client/server databases Guide to Oracle 10g

  3. Objectives (continued) • Understand the Northwoods University student registration database and the Clearwater Traders sales order database Guide to Oracle 10g

  4. Introduction • If an organization needs to store and maintain a large amount of data that must be viewed and updated by many users at the same time, it often uses a client/server database such as Oracle 10g. Guide to Oracle 10g

  5. Database Systems • Data files consist of: • Fields • Describe characteristics • Also called columns • Records • Group of related fields Guide to Oracle 10g

  6. Database Systems (continued) • Problems with storing data in files: • Proliferation of data management programs • Presence of redundant data • Inconsistent data • Database • Stores all organizational data in central location • Eliminates redundant data to reduce possibility of inconsistent data Guide to Oracle 10g

  7. Database Systems (continued) • Database management system (DBMS) • Application that performs all routine data-handling operations • Provides central set of common functions for managing database • Inserting • Updating • Retrieving • Deleting data values Guide to Oracle 10g

  8. Database Approach to Data Processing Guide to Oracle 10g

  9. Database Systems (continued) • Database administrator • Person responsible for installing, administering, and maintaining the database • Often called DBA Guide to Oracle 10g

  10. Overview of Relational Databases • Table • Matrix with columns and rows • Columns • Represent different data fields • Characteristics or attributes about entity • Rows • Contain individual records • Attributes about a specific instanceof entity Guide to Oracle 10g

  11. Overview of Relational Databases (continued) Guide to Oracle 10g

  12. Overview of Relational Databases (continued) • Entity • Object about which you want to store data • Different tables store data about each different entity • Relationships • Links that show how different records are related Guide to Oracle 10g

  13. Overview of Relational Databases (continued) • Key fields • Establish relationships among records in different tables • Main types of key fields • Primary • Candidate • Surrogate • Foreign • Composite Guide to Oracle 10g

  14. Primary Keys • Column in relational database table whose value must be unique for each row • Serves to identify individual occurrence of entity • Every row must have a primary key • Cannot be NULL • NULL • Value is absent or unknown • No entry is made for that data element Guide to Oracle 10g

  15. Candidate Keys • Any column that could be used as the primary key • Should be a column that is unique for each record and does not change Guide to Oracle 10g

  16. Surrogate Keys • Column created to be record’s primary key identifier • Has no real relationship to row to which it is assigned other than to identify it uniquely • Surrogate key values automatically generated using a sequence Guide to Oracle 10g

  17. Foreign Keys • Column in table that is a primary key in another table • Creates relationship between two tables • Value must exist in table where it is the primary key Guide to Oracle 10g

  18. Composite Keys • Unique key that is created by combining two or more columns • Usually comprises fields that are primary keys in other tables Composite Key Guide to Oracle 10g

  19. Database Design • Main tasks involved with design of database: • Developing entity-relationship (ER) model • Normalizing database tables Guide to Oracle 10g

  20. Entity-Relationship Model • Designed to help you identify which entities need to be included in database • Composed of • Squares representing entities • Lines representing relationships • Types of relationships: • One to one (1:1) • One to many (1:M) • Many to many (N:M) Guide to Oracle 10g

  21. Entity-Relationship Model (continued) • One to one (1:1) • Each occurrence of a specific entity is found only once in each set of data • Rare in relational databases • One to many (1:M) • Instance can only appear once in one entity, but one or more times in the other entity Guide to Oracle 10g

  22. Entity-Relationship Model (continued) • Many to many (N:M) • Instance can occur multiple times in each entity • Cannot be represented in physical database • Broken down into series of two or more 1:M relationships through use of linking tablein process of normalization Guide to Oracle 10g

  23. Normalization • Step-by-step process used to determine which data elements should be stored in which tables • Purpose • Eliminate data redundancy • Several levels of normalization • Forms Guide to Oracle 10g

  24. Normalization (continued) • Unnormalizeddata • Does not have a primary key identified • Contains repeating groups • First normal form (1NF) • Repeating groups removed • Primary key field identified Guide to Oracle 10g

  25. Example of Unnormalized Data Guide to Oracle 10g

  26. Normalization (continued) • Second normal form (2NF) • In 1NF • No partial dependencies • Partial dependency • Fields within the table are dependent only on part of the primary key Guide to Oracle 10g

  27. Normalization (continued) • Basic procedure for identifying partial dependency: • Look at each field that is not part of the composite primary key • Make certain you are required to have bothparts of the composite field to determine the value of the data element Guide to Oracle 10g

  28. Normalization (continued) • Third normal form (3NF) • In 2NF • No transitive dependencies • Transitive dependency • Field is dependent on another field within the table that is notthe primary key field Guide to Oracle 10g

  29. Database Systems • Consists of • DBMS • Manages physical storage and data retrieval • Database applications • Provide interface that allows users to interact with database • Server • Computer that shares resources with other computers Guide to Oracle 10g

  30. Database Systems (continued) • Server process • Program that listens for requests for resources from clients • Responds to requests • Client • Program that requests and uses server resources Guide to Oracle 10g

  31. Personal Database Management Systems • DBMS and database applications run on same workstation • Appear to user as a single integrated application • Used primarily for creating single-user database applications • Can also be used for some multiuser applications • Should be used only for applications that are not mission critical Guide to Oracle 10g

  32. Personal Database Management Systems (continued) • Microsoft Access • Stores all data for database in a single file with an .mdb extension • Database administrator stores .mdb file on a central file server Guide to Oracle 10g

  33. Using a Personal Database for a Multiuser Application Guide to Oracle 10g

  34. Personal Database Management Systems (continued) • Transaction processing • Grouping related database changes into units of work that must either all succeed or all fail • DBMS can use the transaction log to reverse—or roll back—the changes Guide to Oracle 10g

  35. Client/Server Database Management Systems • DBMS server process runs on one workstation • Database applications run on separate client workstations across network • Server sends onlyrequested data back to client rather than entire database Guide to Oracle 10g

  36. Client/Server Database Architecture Guide to Oracle 10g

  37. Client/Server Database Management Systems (continued) • Generate less network traffic than personal databases • Extra features to minimize chance of failure • Powerful recovery mechanisms that often operate automatically • Maintain file-based transaction log on database server Guide to Oracle 10g

  38. Client/Server Database Management Systems (continued) • Preferred for • Database applications that retrieve and manipulate small amounts of data from databases containing large numbers of records • Mission-critical applications • Web-based database applications that require increased security and fault tolerance Guide to Oracle 10g

  39. The Oracle 10g Client/Server Database • Oracle 10g • Latest release of Oracle Corporation’s relational database • Client/server database • Server side • DBMS server process • Oracle Net • Utility that enables network communication between client and server Guide to Oracle 10g

  40. Client/Server Architecture for Oracle 10g DBMS Guide to Oracle 10g

  41. The Oracle 10g Client/Server Database (continued) • Oracle Application Server • Used to create World Wide Web pages that allow users to access Oracle databases • Oracle client products: • SQL*Plus • Oracle 10gDeveloper Suite • Enterprise Manager Guide to Oracle 10g

  42. The Database Cases • Fictional organizations: • Clearwater Traders • Northwoods University • Design principles: • Convert all tables to third normal form • Include primary key as foreign key in table on “many” side of relationship • Specify data type for each column Guide to Oracle 10g

  43. The Clearwater Traders Sales Order Database • Clothing and sporting goods through mail-order catalogs • Wants to begin accepting orders using Web site • Required data consists of information for: • Customers • Orders • Items • Shipments Guide to Oracle 10g

  44. The Clearwater Traders Sales Order Database (continued) • Tables: • CUSTOMER • ORDER_SOURCE • ORDERS • CATEGORY • ITEM Guide to Oracle 10g

  45. The Clearwater Traders Sales Order Database (continued) • Tables (continued): : • ORDER_LINE • SHIPMENT • INVENTORY • SHIPMENT_LINE • COLOR Guide to Oracle 10g

  46. Visual Representation of the Clearwater Traders Database Guide to Oracle 10g

  47. The Northwoods University Student Registration Database • Student registration system • Data items consist of information about: • Students • Courses • Instructors • Student Enrollment Guide to Oracle 10g

  48. The Northwoods University Student Registration Database (continued) • Tables: • LOCATION • FACULTY • STUDENT • TERM • COURSE • COURSE_SECTION • ENROLLMENT Guide to Oracle 10g

  49. Visual Representation of the Northwoods University Database Guide to Oracle 10g

  50. Summary • Relational databases • Store data in tabular format • Create relationships that link related data using key columns • Primary key • Column that uniquely identifies specific record • Foreign key • Creates relationship between two tables Guide to Oracle 10g