Describe the Pet Store schema and test your current SQL skill.
The course project, a Pet Store, acts as the basis for most of the exercises and examples. By the end of the course, you will complete the following tasks for the course project:
- Create a function that calculates sales tax on retail sales
- Create a procedure that updates a sales record by calculating sums and adding shipping and handling fees
- Incorporate the sales tax function into the sales procedure you created
- Write a trigger that prevents unauthorized updates of the Pet Care Log
The tables and data you use for the Pet Store are described later in this module.
Imagine you own an on-line pet store called House-O-Pets.com. You use an Oracle database schema to keep track of
- customers, and
- customer purchases.
Since 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, you have two packages sold at a discount that include a set of related items. For example, the Puppy Package comes with a puppy, 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.
The Pet Store Schema shown in the graphic above contains a set of related tables.
See the petstore.sql file 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. The next module covers functions, the first of the PL/SQL stored objects that you will create during this course.