By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,300 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,300 IT Pros & Developers. It's quick & easy.

Data Modeling

P: n/a
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.

--
ja******@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:

1) Customer Entity identifier is Cust_ID.
2) Customer Price Type can not be Null, exists in Pricing.
3) Daily_Sales Entity identifier is a composite of three identifiers,
Transaction_No, Store_ID and Part_No.
4) Daily_Sales Customer ID entity can not be Null, exists in
Customer
5) Employee Entity identifier is EmployeeID.
6) Inventory Entity identifier is Part_No.
7) Inventory VendorID entity can not be Null, exists in
Vendor
8) Invoice Entity identifier is a composite of Store_No and
Transaction_No.
9) Invoice_Line_Item Entity identifier is Item_No.
10) Invoice_Line_Item, Store Number and Transaction
Number can not be Null.
11) Pricing Entity identifier is Price_Type.
12) Purchase_Order Entity identifier is PO_Number.
13) PO_Line_Item Entity identifier is Part_No.
14) PO_Line_Item PO_Number can not be Null.
15) Store Entity identifier is Store_ID.
16) Transaction Entity identifier is Transaction_No.
17) 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;
/
--
ja******@usa.com

remove the ? to reply.
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.