Lesson 3	Petstore DataModel - course project
Objective	Describe the Pet Store Schema and assess your current SQL skill level.

PetStore Data Model

What will you do with the course project?

The course project is a pet store and 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:

Build a SQL*Plus report that shows the history of purchases at the pet store
Create queries to display the pet care instructions for various animals in the store
Add a new column to a table when new data must be stored
Assign read and write privileges to different employees
Update data to reflect the latest inventory count

The tables and data you use for the pet store are described later in this module.

Oracle PL/SQL Programming

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.

Inventory

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, 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.

Tables of Petsstore — Database containing tables 1)PRODUCT, 2) PET_CARE_LOG, 3) CUSTOMER, 4) CUSTOMER_SALE and 5) SALE_ITEM

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

Database Pet Store Schema

The pet store schema shown above contains a set of related tables.
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.