Data Modeling

From: Redd <javatek?_at_usa.com>
Date: Thu, 3 Jun 2004 17:23:29 -0500
Message-ID: <gBNvc.2566$1s1.1086@bignews4.bellsouth.net>

The following is a technical report on a data modeling project that was recently assigned to me by my professor. I post it so that anyone else who is studying databases and data modeling can have an example to go by with their study of databases. I was assinged to come up with a data model, but I choose the Autoparts sales and inventory management schema. It you would like the SQL code to generate the schema or if you would like the ERWin diagram of this model just email or reply to the post. I can post the SQL code by will have to email the ERWin file. I also have a SQL file to populate the schema with test data. I left out the Business rules for now. Any comments or question let me know. This model was targeted for Oracle 9i but with ERWin can generate schema for SQL server or any database.

--

javatek?@usa.com

remove the ? to reply.

Final Technical Report
ITEC 2160 Database Processing
April 2004

AUTOPARTS SALES & INVENTORY MANAGEMENT SYSTEM Abstract / Introduction:

The selling of automotive aftermarket replacement parts is both a retail enterprise selling to consumers and a wholesale operation selling parts to repair shops and other resellers. In consideration of the diverse customer base, this data model will be developed so that sales of auto parts either retail or wholesale can be recorded and tracked electronically. In addition, stock inventory levels will be monitored electronically with this model and an automated stock replenishment mechanism implemented. Due to time constraints this model will be a stripped down version only pertaining to the sales and inventory functions. The current design does allow for further development and the addition of other components.

Since the heart of an auto parts store is in its inventory of parts we will begin with an explanation of how the inventory is managed. By the use of the MinQuantity field in the Inventory table the system will be able to indicate a low stock level once the quantity in stock is equal to or less than the minimum quantity specified by the MinQuantity attribute. At this point a View for Inventory can be generated to display inventory items whose quantity levels are below their specified minimum. This Reorder_View can be used by the system to generate purchase orders to vendors whose contact data and address are stored in the table named 'Vendor'. Just as the Inventory table contains all the attributes for any given part the Vendor tabled does the same for all vendors.

To complete the description of the model that will be created we will now discuss the sales tracking process. Once a sale is made, a record of that sale is recorded in the Daily_Sales table. A transaction number to be associated with this sale will be generated then used as part of the identifier for this sale. The store number and part number are also part of this unique identifier. From this record a sales invoice/receipt can be created and printed. But for all purposes this process of generated a sales record then generating a receipt will appear to be simultaneous. At the end of each day the contents of the Daily_Sales table will be appended to the Sales_History table and Daily_Sales will be truncated.

In support of the sales tracking and invoice generating process five other entities must be created, they are; 'Customer', 'Employee', 'Store', 'Pricing', and 'Transaction.' The Customer table will record the customer data, Employee contains store employee data, the store number and address attributes are recorded in Store , Pricing contains information on how to price retail and wholesale sales, and Transaction is used to record the Transaction number.

There are two sequences created to provide for surrogate keys, they are:
Trans and PO_Number. Trans is the sequence that is implemented to created the Transaction number for the Transaction entity and sequence PO_Number was created to provide a unique identifier for purchase orders.

In Part 3 of this assignment we continue to refine the Data Model and confirm that referential integrity actions work as defined. As of this point the model has been implemented into Oracle 9i and data entered into tables. The functionality of triggers, views and other components are being tested.

Entities/Description

Inventory: Contains information on all items to be sold. Customer: Customer information both retail and wholesale buyers. Vendor: Suppliers of items in Inventory. Employee: Contains store employee information.

Store:          Store location and description.
Invoice:      Receipt given to customer for items purchased.
Invoice_Line_Item:     Individual  items purchased per invoice.
Reorder_View:   A view of Inventory for rows where
                              Quantity <= MinQuantity
Purchase Order:  Orders placed to vendor to replenish
                              stock created from reorder records.
PO_Line_Item:     Individual items purchased per Purchase
                                Order
Pricing:                 Contains information for customer pricing
                              and discount.
Daily_Sales:         Records information on daily sales
                               transactions, truncated nightly.
Sales_History:     Maintains historic data on sales
                               transactions to be appended nightly with
                               Daily Sales activity.
Transaction:         Transaction number generated by a
                                sequence to provide unique identifier for
                                a Sale.

Surrogate Keys:

SEQUENCE Trans
This sequence was created to provide for a unique identifier for the Transaction entity, which is then inserted into Daily_Sales as a Foreign Key, part of the composite unique identifier for Daily_Sales.

SEQUENCE PO_Number
This sequence is needed to provide for a unique identifier for the Purchase_Order entity.

Constraints:

Customer Entity identifier is Cust_ID.
Customer Price Type can not be Null, exists in Pricing.
Daily_Sales Entity identifier is a composite of three identifiers, Transaction_No, Store_ID and Part_No.
Daily_Sales Customer ID entity can not be Null, exists in Customer
Employee Entity identifier is EmployeeID.
Inventory Entity identifier is Part_No.
Inventory VendorID entity can not be Null, exists in Vendor
Invoice Entity identifier is a composite of Store_No and Transaction_No.
Invoice_Line_Item Entity identifier is Item_No.
Invoice_Line_Item, Store Number and Transaction Number can not be Null.
Pricing Entity identifier is Price_Type.
Purchase_Order Entity identifier is PO_Number.
PO_Line_Item Entity identifier is Part_No.
PO_Line_Item PO_Number can not be Null.
Store Entity identifier is Store_ID.
Transaction Entity identifier is Transaction_No.
Vendor Entity identifier is VendorID.

Check Constraints:

Daily_Sales.IT
CHAR(1) DEFAULT 'F' NOT NULL
CHECK (IT IN ('T', 'F'))
The Insert or update must be either 'T' or 'F' representing a logical True or False.

Inventoy.OnOrder
CHAR(1) DEFAULT 'F' NOT NULL
CHECK (OnOrder IN ('T', 'F'))
The Insert or update must be either 'T' or 'F' representing a logical True or False.

Domains:

Simple Domains:

FirstName - Varchar, Average Width = 20, Not Null LastName -- Varchar, Average Width = 20, Not Null

Street        -- Varchar,  Average Width = 20,  Not Null
City          --  Varchar,  Average Width = 20,  Not Null
State         --  Varchar,  Average Width =   2,  Not Null
Zip            --  Varchar,  Average Width =   9,  Not Null


UPDATE:
        On update to Vendor cascade to Inventory.
        On update to Pricing cascade to customer


INSERTION:
             When inserting into Daily_Sales Tax cannot be
             Null.
   Cannot insert into Inventory if Vendor_ID does
             not exists in Vendor.


DELETION:
             If delete to Invoice cascade to
             Invoice_Line_Items
   If delete to Purchase_Order cascade to
             PO_Line_Items.

Retrieval And Reports:

The following SQL statement will provide a list of customers by zip code to provide for a targeted mailing.

Select FirstName,LastName,Address,City,State,Zip From Customer
Order by Zip Desc;

Another SQL statement to retrieve customer data for a targeted mailing for a specific postal code.

Select FirstName,LastName,Address,City,State,Zip From Customer
where Zip = 38119
Order by LastName;

The following SQL statement will produce a vendor list with the specific items in inventory that the vendor supplies.

Select A.Vendor_Name, A.VendorID,B.Part_No,B.Description, B.Quantity
From Vendor A, Inventory B
Where A.VendorID = B.VendorID;

Another retrieval statement to provide a list of Inventory Items provided for a specific vendor.

Select A.Vendor_Name, A.VendorID,B.Part_No,B.Description, B.Quantity
From Vendor A, Inventory B
Where A.VendorID = B.VendorID AND A.VendorID = 59120;

Views:

SQL code to create the view Reorder_View. It is by this view that allows for the creating of purchase orders by what ever application code that is created to process the rows of Reorder_View. This view only displays those rows in Inventory whose minimum quantity have fallen below the reorder threshold.

CREATE OR REPLACE VIEW Reorder_View AS SELECT I.Part_No, I.Description, I.Quantity, I.MinQuantity, I.ReorderQuantity,I.Cost, I.VendorID, I.OnOrder

             FROM Inventory I
             WHERE MinQuantity >=   Quantity and OnOrder = 'F';

Triggers:
The following trigger code is an Insert trigger that fires before insert into Daily_Sales. Its function is to retrieve the next value from the Trans sequence and insert that value into the Transaction entity. This newly generated number is then used for the Transaction number for the row to be inserted into Daily_Sales. This trigger generates the new number if the new value for the field IT is 'T'. The 'T' indicating that this insert is a new transaction. If the new value for the IT filed is 'F', indicating that the new insert is not a new transaction, then the current value for the sequence is inserted for Transaction_No into Daily_Sales.

create or replace trigger TI_DailycheckTrans   BEFORE INSERT
  on Daily_Sales
  REFERENCING OLD AS old NEW AS new
  for each row

begin
If :new.IT = 'T' THEN

insert into Transaction (Transaction_No) values (Trans.NextVal);
SELECT TRANS.CURRVAL INTO :new.Transaction_no from Dual;
else

SELECT TRANS.CURRVAL INTO :new.Transaction_no from Dual;
END IF;
END;
/

--

javatek?@usa.com

remove the ? to reply. Received on Thu Jun 03 2004 - 17:23:29 CDT