Problem : Consider the database of an online bookstore.

• Every book has a title, isbn, year and price. The store also keeps the author and publisher for any book.

• For authors, the database keeps the name, address and the url of their homepage.

For publishers, the database keeps the name, address, phone number and the url of their website.

• The store has several warehouses, each of which has a code, address and phone number.

• The warehouse stocks several books. A book may be stocked at multiple

warehouses. (In previous sentence, we are not referring to a particular copy of the book. Consider for example “the complete book” for our course. This book may be stocked at multiple warehouses.)

• The database records the number of copies of a book stocked at various

warehouses.

• The bookstore keeps the name, address, email-id, and phone number of its

customers.

• A customer owns several shopping basket. A shopping basket is identified by a basketID and contains several books.• Some shopping baskets may contain more than one copy of same book. The database records the number of copies of each book in any shopping basket.

Design an ER diagram for such a bookstore. You are NOT required to identify keys or constraints for this problem.

Answer

STEP 1

Identify all the entities

-AUTHOR

-PUBLISHER

-BOOK

-CUSTOMER

-SHOPPING_BASKET

-WAREHOUSE

STEP 2

find the relations

1.Each book is written by a author

2.Each book has a publisher

3. Some shopping baskets may contain more than one copy of same book

4. The warehouse stocks several books

5. A customer owns several shopping basket

STEP 3

Identify the key attribute 

*AUTHOR- name

*PUBLISHER- name

*BOOK- ISBN

*CUSTOMER- email

*SHOPPING_BASKET- basket_ID

*WAREHOUSE- code

STEP 4 

Identify other relevant attributes

*AUTHOR- name,address,URL

*PUBLISHER- name,address,URL,phone

*BOOK- ISBN,year,title,price

*CUSTOMER- email, name,address,phone

*SHOPPING_BASKET- basket_ID

*WAREHOUSE- code, address,phone

M2

M3

 M1

Advertisements