ارجو المساعده (home Work) (erm, Normalization)

The following scenario is about a bookshop company selling books. Study the user requirements and design the conceptual schema using ERM.

Consider the CBM (Computer Books by Mail) Corporation, The system required by the new management for marketing to individuals as well as librarians and supplying the majority of books from inventory will be much more complex. The following narrative is taken from the old-style systems specification. The lines are numbered for easy reference.

The following scenario is about a bookshop company selling books. Study the user requirements and design the conceptual schema using ERM.


Consider the CBM (Computer Books by Mail) Corporation, The system required by the new management for marketing to individuals as well as librarians and supplying the majority of books from inventory will be much more complex. The following narrative is taken from the old-style systems specification. The lines are numbered for easy reference.

The following scenario is about a bookshop company selling books. Study the user requirements and design the conceptual schema using ERM.


Consider the CBM (Computer Books by Mail) Corporation, The system required by the new management for marketing to individuals as well as librarians and supplying the majority of books from inventory will be much more complex. The following narrative is taken from the old-style systems specification. The lines are numbered for easy reference.

Orders will be received by mail, or taken over the phone by
the inward WATS line. Phone orders will be taken down in a
standard form, or entered directly into a CRT using a stand
dard format. Each order will be scanned to see that all im-
portant information is present, that the title exists (or can
be identified), that the author is correct (or can be iden-
tified), and that the book is available (i.e., not out of
print). If the order is defective, it is routed to a super-
visor to see if, e.g., “The Programming of Management”, by
Does Jane, should really be “The Management of Programming”,
by Jane Doe. Where payment is included, the amount is to be
checked for correctness (if not correct, a request for fur-
ther payment or a credit should be produced). Small dis-
crepancies can be ignored. Where payment is not with the order,
the customer file must be checked to see if the order comes
from a person or organization in good credit standing; if not,
the person must be sent a confirmation of the order and a re-
quest for prepayment. If the customer is new to us, an addition
must be made to the customer file. For orders with payment
or good credit, inventory is then to be checked to see if the
order can be filled. If it can, a shipping note with an in-

voice (marked “paid” for prepaid orders) is prepared and sent
out with the books. If the order can only be part-filled, a
shipping note and invoice is prepared for the part shipment,
with a confirmation of the unfilled part (and paid invoice
where payment was sent with the order), and a back-order rec-
ord is created. Back orders are to be filled as soon as
the books are received from the publisher. Where the order
is for a book not held in inventory, the orders are batched
for purchase requisition on the publisher when a quantity
discount has been earned. Returned books are examined for
damage, and entered back into stock, with a credit or refund
being issued to the customer as appropriate. Where the re-
turned book is not an inventory item, and the publisher al-
lows returns, it is sent back to the publisher.
When a shipment of books is received from a publisher,
its contents are to be checked against the original purchase
order, and discrepancies queried. The titles in the ship-
ment are checked against the back orders for priority ship-
ment; and the remainder entered into inventory. Inventory
control policy calls for a reorder level on each title equal
to the (average orders over the previous four weeks) X (de-
livery time from publishers) plus a 50% safety factor.
Thus if sales of a title average 10 per week and the estimated
delivery time is 3 weeks, an order will be placed with the
publisher when the total copies in hand (and on order) have
fallen to 45 (3X10X150%). The safety factor may be varied
from time to time by management, being increased for titles
whose sales are rising and vice versa. The quantity for each
order is determined by taking the product of the average order
rate and delivery time, as above, multiplying by a bulk fac-
tor (normally 3), and rounding up to the next higher discount
break point, unless that increases the order by more than 25%.
Thus in the case above, the normal order would be 3 X 10 X 3
(bulk factor), or 90 copies. If the publisher offers an ad-
ditional discount for orders of 100 or more, 100 would be
ordered. If the discount is only offered for 120 or more,
90 would be ordered, since to order 120 would increase the
order by the excessive amount of 33%. The bulk factor may
be varied by management for each title from time to time.
The calculation of average order rate includes not only or-
ders that were filled, but frustrated demand, such as back
orders, orders without payment, and inquiries that were not
converted to orders because the book could not be supplied
from stock.
When payments for books supplied are received, they
are matched with the appropriate invoice. Where several
invoices are outstanding for an account, and the payment
does not match any one of them exactly, it is applied to
the oldest invoice first. Frequently a customer will send

one payment to cover several invoices. Where any invoice
is more than 30 days overdue, a statement of all invoices
outstanding is sent to the customer. When any invoice is
more than 60 days overdue, a strongly worded letter is pro-
duced for the Vice-President’s signature.
When invoices are received from publishers, they are
checked against the receipt-of-shipment records, and entered
into accounts payable. If the discount for prompt pay-
ment given by the publisher exceeds, on an annualized basis,
the marginal cost of funds (as specified from time to time
by management), the system should produce a payment check
on the last day the discount is available. For example,
if 2 ½% is offered for payment in 30 days, this is equiva-
lent to 30% per year. The system should write a check on
the 29th day.
Reports of invoices sent out, by day, by week, by month,
payments received by day, week, month, amounts overdue by
various periods, stock-outs, back orders, and purchases
from publishers, should all be produced regularly. On-
demand analyses of sales by title, by subject, by publisher,
with trend information, should be available on an immediate
basis, together with information on publisher delivery times
and purchasing trends. Immediate access to inventory figures
of quantity-on-hand, quantity-on-order, and expected date
of delivery are all very desirable, as is the facility to
give a customer immediate information as to the status of
his particular order. If a customer calls up and says,
‘I sent you a check for $10 five weeks ago, for Bloggs’
book,’ we would like to be able to tell him what day we
shipped the book to him, or on what date we will be able
to ship it.