XPost: comp.databases.theory, comp.databases.oracle.misc, comp.d   
   tabases.ms-sqlserver   
   From: javatek?@usa.com   
      
   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:   
      
   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   
      
   [continued in next message]   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)