Data Modeling
Date: Thu, 3 Jun 2004 17:23:29 -0500
Message-ID: <gBNvc.2566$1s1.1086_at_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?_at_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?_at_usa.com
remove the ? to reply. Received on Fri Jun 04 2004 - 00:23:29 CEST