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.