Hi,

how would the ER diagram for the database of an online bookshop look like? I only know a few records that can be included in the Database so far such as Book details eg. Title, Author, ISBN, and then Customer details such as name, email etc., AND ORDER details etc. but the DB may be bigger than this. so any1 have a ER diagram for this kind?



thanks

Well, what kinds of data would you need?

Customer data

Sales Order Data

Supplier Data

Purchase Order data

Shipment Data

Break down each class of data into more discrete information. For instance, the customer data would include, at a minimum, a name (first, last, middle initial) address (Street, PO Box, city, state/Province, Postal Code, Country) Telephone number(s), etc. Probably, the simplest way of joining the customer to other tables would be to add a surrogate primary key (customer number) as well.

The sales order data would include a sales order number, timestamp, customer reference, requested delivery date, shippment method, etc. Possibly the tax rate, as well.

Note that I did NOT include the details of the order here. Each item of an order (you CAN order more than one book from the book store...) would be stored in a separate child table. Call it SalesOrder_Detail, maybe. It would include a foreign key to the parent sales order table, an item or line number within the order (1-N), and the combination (AKA a Compound Key) of these two fields would form the unique key (PK) for this table. In addition, it would need to describe the detail of the order line item - the quantity, the as-sold price each, and the part number (ISBN Number, maybe) and possibly a discount percentage rate. In a normalized database you would NOT include the line item total as a separate field, as it is a derived, or calculated field.

You could find some more basic information on table design and normalization in the database installments of this tutorial.

I'll leave it up to you to refine the other classes of data you'll need to store.

loquin, a very generous response to a RFH* question

i trust buddy appreciates it

RFH = request for homework

thanks loquin

i see Loquin thanks I think I have managed to create my tables thanks to ur tutorials, BUT my problem now is how to normalise data see my thread normalisation help

i have attached the normalisation table there.

oh yeah R937 this is not a RTF. I am trying to learn database for educational purposes.

Check out this Data Model which will give you a Kick-Start :-
http://www.databaseanswers.org/data_...tore/index.htm

B.Dimple
Senior DBA