SQL Extensions  «Prev 

Relational Model for Pet Store Schema

 
Database diagram of the pet store schema including tables, columns, and realtionships
Database diagram of the pet store schema including tables, columns, and realtionships

Table name: PRODUCT <-> Primary key: PRODUCT_ID


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)

This is a 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)
(3) Relationship: A customer buys zero, one, or many customer sales. A customer sale is purchased by one customer.
(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)
(6) Relationship: A customer sale includes zero, one, or many items purchased. A sales item is part of one customer sale.
(7) 
Table name: SALES_ITEM
Primary key: SALES_ID and PRODUCT_ID
Columns:
SALE_AMOUNT NUMBER(10, 2)
(8) Relationship: A product can be a sales item in zero, one, or many customer sales. A sales item is always one product.
(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.
(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