Relational Database Management System (RDBMS) fundamentals tutorial

New to database administration or development? Here is a quick guide that can get you started in the right direction by providing background on the relational model and practical advice for working with relational database management systems (RDBMS).

TABLE OF CONTENTS
  [IMAGE] The relational model
  [IMAGE] Database structure
  [IMAGE] Database design
  [IMAGE] Normalization
  [IMAGE] Working with an RDBMS
  [IMAGE] More Learning Guides

  The relational model[IMAGE] Return to top

Because much confusion stems from vague database-related terms, a good starting point is to know the terminology. First described by E. F. Codd at IBM in 1970, a relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. The best backgrounder on the relational model is this chapter from Chris Date's new book, Database in Depth: Relational Theory for Practitioners.

Definitions of more database-related terms are available from our sister site WhatIs.com:

• Data
• A database
• A relational database management system
• SQL
• Oracle
• DB2
• SQL Server

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database design and architecture How to determine your SQL database through needs analysis Breaking down the contenders in the SQL database market Diving deeper into the SQL database features The MySQL open source database in the enterprise What managers should consider when starting a database scaling project How to use V$SEGMENT_STATISTICS to find the most accessed Oracle table Can I install an Oracle client on Windows 7? How to use the Oracle Database Upgrade Assistant (DBUA) Can I specify Oracle column order in my database table? Can I have a single Oracle 11g RAC instance across multiple databases? Oracle tutorials, tips and FAQs Part 3: The final ERP transition: Crossing the finish line successfully Oracle tutorial library: SearchOracle.com's learning guides What managers should consider when starting a database scaling project Part 2: Maintaining your place in the race -- ERP project management Oracle virtualization introduction guide Revving your engines: Tuning up your ERP project plan Oracle Openworld 2009: Here's what to expect Oracle OpenWorld 2009 Special Report SearchOracle.com podcasts Do I need to buy an Oracle license for a development database? Oracle tutorials, tips and FAQs Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary E. F. Codd  (SearchOracle.com) extent  (SearchOracle.com) flexfield  (SearchOracle.com) foreign key  (SearchOracle.com) multidimensional database  (SearchOracle.com) object-oriented database management system  (SearchOracle.com) quad tree  (SearchOracle.com) relational online analytical processing  (SearchOracle.com) row  (SearchOracle.com) splay tree  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

relational model is not without controversy, pitting purists versus theorists versus practitioners versus flat-out critics. For some background, check out these links:

• Introduction to relational databases: Theoretical foundation
• Relational model
• The relational data model, normalization, relational operations and languages
• Codd's laws
• Fabian Pascal answers integrity, normalization, and relational model questions
• What is an Oracle relational database?
• Ordering of rows or columns in a relational table
• RDBMS versus XDBMS
• Why normalized tables help your business
• Fresh thinking on databases for object development
• XML data management: Setting some matters straight, Part III
• Know thy database: Understanding the current state and shape of the objects
• Moving forward with relational: Looking for objects in the relational model
• Understanding NULLs: A contradiction in terms
• OO for application development, not database management
• Date defends relational model

  Database structure[IMAGE] Return to top

As mentioned above, database terminology sometimes gets in the way of database administration. The terms "database" and "instance" are important to know when using Oracle systems, and it is also important to know that different vendors will use the terms to mean different things. Here are some examples:

• Explanation in layman terms of an Oracle "instance"
• Database instance and listener explained
• Can I create more than one instance for a database?
• Can I start another instance for the same database?
• Why can't an instance open more than one database?
• What do you need to consider when combining applications into one Oracle instance? Answer #1, Answer #2
• What is the difference between active-active and active-passive configurations?
• How important is it to have the multiple instances on similar hardware?

• Explanation of the differences between a logical and physical data model
• What are the logical and physical activities within the database?
• What are consistent gets and physical gets?

  Database design[IMAGE] Return to top

Most of the time, database performance and ease of use is only as good as its design. And good design can, like everything else so far, be open to considerable debate. Here are a few resources to help you decide for yourself:

• What makes a good database?
• The entity relationship diagram
• Candidate key explained
• Restricting cyclical references in trees
• Wide or deep?
• Table design: Go across or go down?
• Is denormalization always good?
• Adding database redundancy to reduce SQL complexity, increase performance, and reduce your workload
• One requirement of a good data model: Simplicity
• Effective Oracle by design
• Methods of depicting a data model
• Modeling project life cycles, an excerpt from "Designing efffective database systems"
• Review: Oracle Designer
• Modeling the data warehouse and data mart
• Designing a data warehouse
• A data warehouse conceptual data model for multidimensional aggregation

  Normalization[IMAGE] Return to top

The rules of normalization are critical to database design, and with controversy continuing into this section, here are plenty of viewpoints on how normalization and denormalization affect the design and use of a database:

• Database normalization tips
• Database normalization and design techniques
• Normalization and performance: Never the twain shall meet!
• The effect of normalization on query simplicity
• Your integrity, normalization, and relational model questions answered
• How does normalization affect good design for OLAP purposes?
• Normalization and tracking systems
• Are there any CASE tools that teach normalization of tables?
• What you don't know about denormalization can hurt you, Part I
• What you don't know about denormalization can hurt you, Part II
• 13 reasons why normalized tables help your business
• Optimizing database performance: Denormalization and clustering

  Working with an RDBMS[IMAGE] Return to top

For practical matters, sometimes you have questions that are pretty basic, but you don't know where to start to look for an answer if you can't find it in the Oracle documentation. For those types of issues, a great resource is our repository of expert answers, which you can search. SearchOracle.com's panel of experts have answered hundreds of basic questions, and here is a very small sampling.

Oracle features explained:

• Anonymous blocks and REF cursors
• Audit Trail
• CKPT
• .dbf and .ora data files
• Data sources: What, how and why
• Differences between versions
• Error messages
• Execution plan
• Index rebuilds
• LTRIM
• Partitions, indexes and parallelism
• Primary keys and unique indexes?
• OPS, RAC and the grid
• RAC versus OPS
• Service name and database name
• The sequence of USER PROCESS, SERVER PROCESS, INSTANCE and SESSION start
• SMON
• SQL*Net
• V$ and other dictionary tables

• Create a control file
• Create a database
• Create a data dictionary
• Create an object
• Create a password file
• Create a table
• Create a table partition
• Embed PowerPoint files, video clips, and photos
• Export a table to XML
• Get a list of tablespaces
• Read dump files
• Rebuild an index
• Set the default text editor
• Upload CSV files
• View a schema

To retrieve data, you need to know the structured query language (SQL). We have a complete SQL Learning Guide, but for a basic understanding of what it can do for you, read below:

• Retrieving the last row inserted into a table
• Does Oracle retrieve records in the same order they were inserted?
• Order of selected rows without an ORDER BY clause
• Fun with SQL
• PL/SQL: What, when, and where
• A query language for XML
• Is it time to say goodbye to SQL?
• Top 10 SQL performance tips

  More Learning Guides[IMAGE] Return to top

• Crash Course: Oracle basics This guide helps you to research, compare or work with the Oracle DBMS. It provides Oracle basics for versions 8.x, 9.x and 10g.
  
  
• Solving common Oracle errors guide Solve your Oracle errors quickly and easily with help from this fast guide. It covers every expert response pertaining to Oracle errors on SearchOracle.com.
  
  
• Oracle database performance tuning guide Newbies and experts will benefit from this guide; you'll establish a smart approach to tuning, use and interpret Oracle's tuning utilities and identify specific problem areas.
  
  
• Oracle PL/SQL tutorial This fast guide to PL/SQL can help both newbies and veterans become better acquainted with the language and hone their developer skills.
  
  
• Oracle database security guide This guide covers a wide variety of topics on many aspects of Oracle security to help you lock down your data.
  
  
• Fast Guide: Becoming an Oracle Certified Professional Get expert technical advice on where to begin your certification studies, passing the final exam and putting your certification into effect in the workplace.
  
  
• Guide to Oracle freeware and shareware A library of freeware, shareware and inexpensive tools to help with Oracle administration and development.
  
  
• Learning Guide: Backup and recovery This guide covers everything from the basics of creating a backup and recovery plan to the specifics of using RMAN and other methods of hot and cold backups.
  
  
• SQL guide for Oracle users Whether you are a newbie or a seasoned expert looking to tweak query performance, this learning guide can help you figure out how to get the data you need by looking at solutions to real-world problems.