The Area being Modelled is :-
Transportation and Shipments.

Comments from Mengmeng on Sunday, 24th. April 2004 :-

ERD
Question:

Dear Barry Williams,
I try to send the following email to your two accouts of "dba_question" and "contact us",
but I get a reject message.
So I come this site again.

But I could not find a place to attach my ERD which I made with using Microsoft Access.
Do you have any other email address I can send email with an attachment to you?
And also I found the web page that you asked about comment of your website only can stay
2 seconds then disappear.

It is hard to finish reading that whole page in 2 seconds.

The following is my reply:-

Dear Barry Williams,
Thanks for the ERD you designed for me.
It is the best design among around 10 ERDs I designed recently.
I really appreciate your help.

There is one thing that I did not correctly describe the business rules last time.
The original one is �if customer does not pay after Wilson Company send the �Charge Invoice� to
them three times, Wilson Company will send the penalty charge to the Customers.�

I misunderstood the rules.
The correct one should be as the follows: Wilson Company sends the �Charge Invoice� to a customer
one time after the shipment leave port of loading.
Then Wilson uses the date that the shipment expects to arrive destination minus
�the particular amount of days� in order to get the �Check_Date�.

Wilson Company will check if the customer has paid or not on the �Check_Date�.
If the customer has paid before or on the �Check_Date�, Wilson Company will inform the shipping
service company to release the shipment when it arrives the destination.
If customer has not paid, Wilson Company will inform the shipping service company to hold the shipment
when the shipment arrive the destination.
This holding will be kept until the customer pays the charge.
So in the table of Business Rule, some customers may have 7 days, and others may have 3days :-
RuleID RuleDes
R001   7
R002   3

For example, for the shipment order 001, the arrive date is 07-Feb-2004, the RuleID is R002, so the
check date should be 04-Feb-2004.
I added this rule in the ERD based on your design.
I am not sure what I did is correct or not.
Could you help me with this?

I changed a little bit to fit the Wilson Company�s requirement; such as I changed �Address� into �Port�.
I hope you do not mind about it.
By the way, what kind of software you are using to draw so beautiful ERD.
I just drew my ERD with using Microsoft Access.
Please click the tool then relationships, you will see the ERD.
Once again I appreciate all you help.
And I am waiting for your relply.
Regards,
Mengmeng


The requirement is to design a database system for the Wilson international transportation agency.
After Wilson Company gets a shipment order from a customer, it gives this shipment to the three different
service companies at the same time.

The three companies are shipping Company for international shipping service, Truck Company for local
truck service, and Broker Company for document clearance service.

Each month, the Service Company will send to the Wilson Company one invoice that shows how many services
they completed in one month, and how much they charged for each shipment.

Since the transportation agency gives around 10 shipments to the three service companies, the 10 shipments
will be shown on the invoice, and the fee for each shipment will also be indicated.

The Wilson Company will calculate the cost of each shipment by summing the charge of shipment from three
service companies.

The Wilson Company will send �Charge Invoices� to customer to charge the shipment from the customer.

If the customer does not pay after Wilson Company send the �Charge Invoice� to them three times, the
Wilson Company will send a penalty charge to the Customer.

If the customer then pays the fee, the Wilson Company will calculate the profit by using the received fee
minus the cost fee for each shipment.

A. DESIGN NOTES :
A.1 The Detail reflects the Requirements.

B. The Things of Interest, ('THINGS'), include :-
B.1
B.2
B.3

C. These THINGS are Related as follows :-
C.1
C.2

D. Other Characteristics of these THINGS include :-
D.1

E. Sample Data includes :-
E.1 To be determined

F. Typical Enquiries include :-
F.1 To be Determined