SQL Extensions  «Prev 

Relational Model for Pet Store Schema

 
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