473,320 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Data Modeling

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 20 '05 #1
0 1248

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Redd | last post by:
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...
1
by: Diego Buendia | last post by:
I'm facing the next problem: I have a table with two columns (among others) modeling category and subcategory data for each row. I need to summarize info on this two columns, but with the next...
1
by: Markus Seibold | last post by:
Hi, I am looking for an open source (free) data modeling tool for PostgreSQL. It should - if possible - support: - E-R-Modelling - relational data model / modeling - (GUI SQL interface to...
0
by: DB2 DBA wanted | last post by:
Maines Paper & Food Service is a $2 billion/yr food distribution company with 9 distribution centers in the US. We are currently interviewing for the position detailed below. Relocation to...
41
by: laimis | last post by:
Hey guys, I just recently got introduced to data mappers (DTO mapper). So now I have a SqlHelper being used by DTOMapper and then business layer is using DTOMapper when it needs to persist...
1
by: Mike | last post by:
I'm starting up on big project that will take much of my time in next year or two. Until now I was mostly self-employed on small projects so I didn't spent so much time modeling system before...
4
by: shalafi | last post by:
Looking for a good, cheap (preferrably free) UML modeling tool. Used Jumli in the past, has everything i want but it isnt terribly stable and doesnt look as if they will have a new version anytime...
8
by: rAinDeEr | last post by:
Hi, I have a table with the following 2 Fields - one is the description name and the second is its description to be stored in a table. description name description...
20
by: hippomedon | last post by:
Hello everyone, I'm looking for some advice on whether I should break the normalization rule. Normally, I would not consider it, but this seems to be a special case. I have created an...
2
by: manugm1987 | last post by:
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> <style type="text/css"><!--...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.