| Lesson 3 | Introducing the course project |
| Objective | Describe the Pet Store schema and test your current SQL skill. |
PL/SQL Stored Objects Course Project
What will you do with the course project?
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:
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
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.
Imagine you own an on-line pet store called House-O-Pets.com. You use an Oracle database schema to keep track of
- inventory,
- customers, and
- customer purchases.
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.
- Table name: PRODUCT| Primary key: PRODUCT_ID| Columns:| PRODUCT_NAME VARCHAR2(30)| PACKAGE_ID NUMBER(10)| CURRENT_INVENTORY_COUNT NUMBER(5)| STORE_COST NUMBER(10, 2)| SALE_PRICE NUMBER(10, 2)| LAST_UPDATE_DATE DATE| UPDATED_BY_USER VARCHAR2(30)| PET_FLAG VARCHAR2(1)
- Recursive relationship where one product (a package) is made up of one or more products. If any product is included in a package, its package ID will contain the product ID of the package. Products not included in a package contain null values in the PACKAGE_ID column.
- Table name: CUSTOMER| Primary key: CUST_ID| Columns:| FIRSTNAME VARCHAR2(20)| LASTNAME VARCHAR2(25)| ADDRESS VARCHAR2(32)| CITY VARCHAR2(20)| STATE VARCHAR2(2)| ZIP VARCHAR2(9)
- Relationship: A customer buys zero, one, or many customer sales. A customer sale is purchased by one customer.
- Table name: CUSTOMER_SALE| Primary key: SALES_ID| Columns:| CUST_ID NUMBER(10)| TOTAL_ITEM_AMOUNT NUMBER(10, 2)| TAX_AMOUNT NUMBER(10, 2)| TOTAL_SALE_AMOUNT NUMBER(10, 2)| SALES_DATE DATE| SHIPPING_HANDLING_FEE NUMBER(5, 2
- Relationship: A customer sale includes zero, one, or many items purchased. A sales item is part of one customer sale.
- Table name: SALES_ITEM| Primary key: SALES_ID and PRODUCT_ID| Columns:| SALE_AMOUNT NUMBER(10, 2)
- Relationship: A product can be a sales item in zero, one, or many customer sales. A sales item is always one product.
- Relationship: A product that is a pet can have zero, one, or many log entries. A pet care log entry is always related to one product.
- Table name: PET_CARE_LOG| Primary key: PRODUCT_ID and LOG_DATETIME| Columns:| CREATED_BY_USER VARCHAR2(30)| LOG_TEXT VARCHAR2(500)| LAST_UPDATE_DATETIME DATE
Primary key: PRODUCT_ID
Columns: 1) PRODUCT_NAME VARCHAR2(30) | PACKAGE_ID NUMBER(10)| CURRENT_INVENTORY_COUNT NUMBER(5)| STORE_COST NUMBER(10, 2)| SALE_PRICE NUMBER(10, 2)| LAST_UPDATE_DATE DATE| UPDATED_BY_USER VARCHAR2(30)| PET_FLAG VARCHAR2(1)
Pet Store Table Layout
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.
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.