Discovering Database Things You Can Do with Oracle9i - dummies

Discovering Database Things You Can Do with Oracle9i

By Carol McCullough-Dieter, Ned Dana

You just installed Oracle9i. Now, it’s time to learn something about databases and what they can do! Take a look at three scenarios to get a feel for what databases do in real life. Let your imagination run wild, and think about what you want your database to do for you.

Keeping track of a fishbowl (the easy example)

Why are aquarium fish so nervous when their world is quiet, rocking them in an endless cradle of gentle bubbles and waves? Well, maybe you would be nervous, too, if 12-foot eyeballs kept popping up in your picture window.

Say your neighbor has a small aquarium with one little gold guppy named Wesley, who was born on January 1, 2000. The aquarium used to contained four guppies, but Wesley ate the other three. Your mission, if you choose to accept it, is to design a set of tables that accurately depicts the world of the aquarium.

When you create a set of related tables and all the database things that go with these tables, you create a schema.

Continuing with the aquarium example, here are the facts that you have to work with:

  • You have one aquarium that holds one gallon of water, if your neighbor’s 11-year-old doesn’t get to it with his Ninja-stealth-throwing stars.
  • Someone feeds Wesley once a day, whether he asks for it or not.
  • Your neighbor changes the water once every 14 days without fail.
  • Three fish died. Their headstones in the garden read:

• Fish Two (black-and-tan female guppy), born 12/11/99, died 3/15/00

• Fish Three (red-and-white male guppy), born 2/18/00, died 4/8/00

• Fish Four (transparent guppy), born 3/1/00, died on an unknown date under very suspicious circumstances while you were fish-sitting during your neighbor’s vacation.

You can easily track all this information in a simple two-table schema, as shown in Figure 1.

Figure 1: This water world has two related tables: AQUARIUM and FISH.

Running a pet shop (the medium example)

Your neighbor buys fish food at the pet shop around the corner from his house. The owner stocks the pet shop with bird cages, dog collars, flea powder, and a few hundred other items — including birds, rabbits, and even a monkey. Here are sundry activities that the shopkeeper handles with her database:

  • Logging every item, purchase price, selling price, and number in stock
  • Tallying tax on every sale
  • Adjusting in-stock numbers on all inventory as sales occur
  • Creating monthly accounting statements
  • Creating annual tax reports
  • Tracking names and addresses of customers
  • Printing customized letters for advertising to customers
  • Printing mailing labels

Keeping tabs on the pet shop — or any business that does similar tasks — is a great use for Oracle9i! The power of the relational model allows you to store your data one time and use it in a variety of ways.

For your information, if you were to try implementing the pet-shop system by using only the Oracle9i database and the tools that are part of the basic package, you would have to do some advanced programming or get some additional tools. The portions that you cannot do easily with your Oracle9i database are

  • Automatically adjusting inventory. This requires programming a database trigger.
  • Tallying tax on every sale. You need a database trigger or an additional tool (such as Oracle Forms) to calculate tax at the time of the sale.

Tracking endangered species globally (the hard example)

Tracking endangered species globally is a worthy cause, for sure. If you were creating a database design for the effort, here are the features that you might want to include:

  • Information-gathering from around the world by using the Internet
  • Massive text archiving of news articles about both positive and negative activities
  • Full-text search capabilities
  • Pictures of cute little animals to adopt by sending monthly payments
  • Letters from cute little animals to their sponsors
  • A Save the Guppies program, with a multibillion-dollar government grant
  • Engraved ID tags for all the animals, identifying them as members of endangered species
  • A clearinghouse of resources available to people and organizations, cross-referenced by needs, skills, locations, and funding
  • Maps showing populations of animals, color coded by level of danger of extinction
  • Internet search capabilities linked to maps of areas — clicking the map zooms you to a more detailed map
  • Maps that allow you to click an area and go to a screen that shows all the data about the animals, resources, organizations, and any other items related to the area that you select in the map
  • Automatic coffee dispensers in every cubicle at state offices

As you find out more about databases, you discover that you can fit many pieces of information into the database world. Just remember to allow the databases, like art, to imitate life. When you start expecting life to imitate your database, come up for air. You could turn into one of those nerds who write database books.