Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Question Unanswered: ERD for Business Rules

    Hello everyone! I am a college student majoring in database management and need some help. I need to know what a Crow's foot ERD would look like including all entities, relationships, optionalities, connectivities, and cardinalities for the following business rules I created below:

    • A customer may generate many invoices.
    • Each invoice is generated by only one customer.
    • Some customers have not (yet) generated an invoice.
    • Customers generate invoices over the company Web site.
    • Available invoices are paid by the customer’s credit card.
    • Unavailable invoices can be placed on Back Order if requested by the customer.
    • Unavailable invoices are not paid until they become available in inventory.
    • Invoices are printed upon payment.
    • Items on paid invoices are pulled form inventory for shipment.
    • A shipping charge is added to every invoice.
    • Each shipping container includes its appropriate invoice.
    • Credit card charges are transmitted to CC Bank.
    • Promotional items are sent to customers who make purchases.
    • Many products are held in inventory for sale to customers.
    • One customer may order many products.
    • A product can remain on the shelf for four weeks without a sale.
    • Products without a sale in four weeks of stocking are pulled form inventory.
    • Potential customers can be encouraged to buy products through advertisements in FineScale Modeler magazine.
    • Customer data are recorded when potential customers request information.
    • Products are ordered directly form the manufacturers.
    • Plastic models are ordered from separate manufacturers than decals.
    • Not all manufacturers have received orders, but should remain in the company database.
    • All produce orders are placed though the manufacturer’s Web sites.
    • Product totals are automatically debited form the commercial bank account at CC Bank.
    • When minimum quantity is met, orders are automatically sent to the manufacturer’s Web site.
    • Products ordered are dependent upon the specified minimum quantity before reorder.
    • Various reports can be printed to determine customer purchases by product category and amount, product turnover, revenues by product and customer, etc.

    Please help in any way you can. I am confused on this one.

    Thanks,

    Am

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry!

    the bad news is that nobody is going to reply to the substance of your question. there are dozens of people here who could propose a reasonable solution - why should they? would you?

    the potentially good news is that if you have a decent try at solving your own problem but you get stuck on some detail of your solution and post your almost-solution-but-stuck you will get more/smarter help here than from any other source!

    ...nobody here does your work for you (!!for free!! but if you think you can afford my consultancy rates, send me PM).

    note: if you could avoidality the jargonality, my bloodishness-pressureality would negatively tendancera.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jun 2006
    Posts
    3

    Post

    Here is what I have been able to come up with so far. I in no way, Izy, was asking for someone to do my work for me, I was simply asking for some help. So maybe you could take your expertise elsewhere if you do not think you can help me.

    Thank you.

    Am

    My work so far is attached.
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yesterday was a bad-hair day!

    moving along (and admitting i don't use this notation), there seems to be some conflict between the text and the diagram.
    One customer produces one or more invoices (diag allows 0)
    One customer purchases one or more products (diag allows 0)
    One invoice is comprised of one or more products (diag allows 0)
    we are verging on the metaphysical: can a customer exist before placing the first order; can an invoice exist with zero products? the answer is necessarily YES from a db point of view (or your FKs won't exist when you need them). so that part of the diag looks correct but i worry about the "one or more" text.

    will you be able to implement the products:manufacturers relationship as shown in the diag? you don't provide enough info to determine if a given product may be sourced from multiple manufacturers (as sugested by diag) if YES, you might need an intermediate table, if NO you might want to drop a webbed foot.

    finally that zero on the manufacturer! can a product exist without a manufacturer?

    ...but i repeat that your notation is completely alien for me so i might be reading the whole thing wrong.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jun 2006
    Posts
    3

    Wink

    Izy,

    The notation is from my text. It is a crow's foot ERD, and the 0s you are talking about are actually circles/ovals notating options. Again, this type of notation was from my text, so it was really hard for me to follow as well. Maybe that will help better understand what I was trying to do there.

    Am

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    (of course) i tried some google before opening my big mouth: best i understand is that the "oh" can really be read as a zero - permitting zero matching records
    options being
    0¦ (1-to-0or1)
    ¦ (1-to-1)
    webbed-foot (1-to-N)
    0-webbed-foot (1-to-0orN).

    all this can be no more than my ignorance of this notation, but it is the TEXT that seems to be wrong. "one or more" needs to be "zero or more" (...or so it seems to me).

    and the double-webbed-foot: can you go many-many without an intermediate table? show me how...

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •