RelationalDBDesign  
prev next prev next
  Course navigation
 
Lesson 5
Objective
Petstore DataModel - course project
Describe pet store schema and assess your current SQL skill level.
    What will you do with the course project?
The course project, a pet store, acts as the basis for many of the exercises and examples. By the end of the course, you will have completed these kinds of tasks for the course project:
  1. Build a SQL*Plus report that shows the history of purchases at the pet store
  2. Create queries to display the pet care instructions for various animals in the store
  3. Add a new column to a table when new data must be stored
  4. Assign read and write privileges to different employees
  5. Update data to reflect the latest inventory count
The tables and data you use for the pet store are described later in this module.
Imagine you own an online pet store called House-O-Pets.com. You use an Oracle database schema to keep track of inventory, customers, and customer purchases. Because you are selling real live pets, you have a real pet store as well, with several employees to take care of the animals. You get orders from customers all around the country. You have rabbits, puppies, kittens, fish, and birds in your store.
Your inventory includes pet supplies for all these animals. Customers can purchase any item individually. In addition, two packages that include a set of related items sell at a discount. For example, the Puppy Package comes with a puppy (of course), dog food, a chew toy, and a dog collar.
To help your employees care for the animals, your database contains a pet care log, where any employee can record information about the animals, such as how often to feed the birds.
Database Pet Store Schema
The pet store schema shown above contains a set of related tables.
See the epet_data.doc files that you downloaded earlier for a complete listing of each table’s columns and data. In addition to the five tables, there is a view called MONTHLY_SALES that lists total sales for each month/year combination. There is also a sequence called PETSTORE_SEQ that generates unique primary key values for the PRODUCT, CUSTOMER, and CUSTOMER_SALE tables.
In the next module, PL/SQL variable declaration will be discussed.
  Course navigation