PL/SQL Programming  «Prev 

Database Pet store schema

Database diagram of the pet store schema
Database diagram of the pet store schema

Table name: PRODUCT
Primary key: PRODUCT_ID
Columns:
  1. PRODUCT_NAME VARCHAR2(30)
  2. PACKAGE_ID NUMBER(10)
  3. CURRENT_INVENTORY_COUNT NUMBER(5)
  4. STORE_COST NUMBER(10, 2)
  5. SALE_PRICE NUMBER(10, 2)
  6. LAST_UPDATE_DATE DATE
  7. UPDATED_BY_USER VARCHAR2(30)
  8. PET_FLAG VARCHAR2(1)

Oracle PL/SQL Best Practices
Location 2
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:
  1. FIRSTNAME VARCHAR2(20)
  2. LASTNAME VARCHAR2(25)
  3. ADDRESS VARCHAR2(32)
  4. CITY VARCHAR2(20)
  5. STATE VARCHAR2(2)
  6. ZIP VARCHAR2(9)

Location 4
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:
  1. CUST_ID NUMBER(10)
  2. TOTAL_ITEM_AMOUNT NUMBER(10, 2)
  3. TAX_AMOUNT NUMBER(10, 2)
  4. TOTAL_SALE_AMOUNT NUMBER(10, 2)
  5. SALES_DATE DATE
  6. SHIPPING_HANDLING_FEE NUMBER(5, 2)

Location 6
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)

Location 8
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.

Table name: PET_CARE_LOG
Primary key: PRODUCT_ID and LOG_DATETIME
Columns:
  1. CREATED_BY_USER VARCHAR2(30)
  2. LOG_TEXT VARCHAR2(500)
  3. LAST_UPDATE_DATETIME DATE