Introducing the course project
Pet store schema
Pet store schema
| 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.
Location 3 | 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.
Location 5 | 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.
Location 7 | 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.
Location 9
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.
Location 10 | 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 |
