Hi,

I need some help on Entity Relationship Modelling. Please suggest on the following issues.

I have to draw an Entity Relationship Model for a store that sells products like
1. books,
2. gift items and
3. stationary.

What should the entity be here? all 3 of them should be treated as separate entities or should I combine them into 1 entity named 'item/product' ???

For example, I make an entity named 'Item'. Then I make an attribute for it 'Item Type' where it can have values books, stationary and gift items. But this will lead to redundancy and also inaccuracy. and it might get complex as I want to categorize books using courses/subjects. And then the gift items can be categorized on basis of occasions. similarly stationary items can be categorized into pens, pencils etc. Because I might want to check in stock that how many pencils are left. So I do need to store this info.

so I am really confused that how should I deal with it. Should I use sub typing? i.e. the super type entity is Item, which has 3 sub types, gifts, stationary and books.

Please help.

Snazzy: It is difficult to respond to your questions because the information of what you are trying to accomplish is not clear. For example, are you trying to build a products catalogue for a store, an inventory for the store or track what is sold? Depending on your answer will dictate HOW to build the E-R Diagram. So could you list the problem the way it is stated in your book? Where ALL the information is in one long statement, paragraph or multi-problems.

I am willing to assist you, but from your post, it is disjointed.

Hope this helps.

========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.

I think you should go for single entity that is ITEM
self relation may help you.
I was using following table design when i was dealing with STORE ITEMS

ITEM_ID INT PK
ITEM_DESC VARCHAR(100) DESCRIPTION
ITEM_PARENT_ID INT PARENT GROUP FK, SELF REFERNECE TO ITEM_ID
G_I CHAR(1) WHETHER GROUP OR ITEM, 'G'-GROUP AND I-ITEM


Sample data
ITEM_ID, ITEM_DESC, ITEM_PARENT_ID, G_I
1 ITEMS 1 G
2 GIFTS 1 G
3 BirthdayGifts 2 G
4 Watch 3 I
5 STATIONARY 1 G
6 Pens 5 G
7 Reynold 6 I
8 BOOKS 1 G
9 Science books 8 G
10 Gravity 9 I

You have to take care that no item_id where g_i is I is can not refered as item_parent_id. This is same as file folder concept, you can create file under file.
You can add as many groups and as many items at you wish upto n level.
Your data may look like following
ITEMS
     GIFTS
          Birthdaygifts
                         watch
     STATIONARY
          Pens
              reynold
     BOOKS
          Science
              gravity


urt

Hi, thanks for the reply.

I have one more query.

If the university shop/store wants to arrange the textbooks/books by programme, course and professor name then what should be done?

Because if I use one table for items then I can not have textbooks/books have a relation with entity 'course'.

If i use 'item' entity for all the items at the university store then this 'item' entity can not have a relation with 'course' as most of the fields will be null as other items in shop dont have anything to do with course.

So do u think sub typing is a good option here? Please suggest

Snazzy:
I am not sure where you are in the Beginning Database Design as it relates to Database Structure/Architecture. The Database Structure for your E-R Diagram was Relational, as opposed to the Database Structure Urtrivedi referenced in the last post is Hierarchial.

As the old saying goes, "There's more than one way to skin a cat..." My example is Relational in nature, means my center of attention is on Tables, Primary Keys(PK) and Foreign Keys(FK). My personal preferences: all keys, PK or FK are numeric; there are no "Nulls" or "Null Values" in the table; and I place default values and constraints in the database scheme (this may be another lesson or chapter). The following is based solely on your statement: "a store that sells products ".

 [u]StoreTbl</u> [u]ProductTbl</u>
StoreID numeric PK ProductID numeric PK;
StoreName char(50); Product;
StoreAddress char(80);
StoreCity char(30);
StoreState char(2); [u]ProductDetailTbl</u>
StoreZip char(5). ProdDetailID num PK;
                                    ProdDetailName char(40);
                                    ProductID FK;
                                    StoreID FK;

StoreTbl ProductTbl ProductDetailTbl
10 Just-In-Time 100 Books 110 TextBooks 100 10
20 Just-In-Time (West) 200 Gift Items 120 Travel 100 20
30 Just-In-Time (East) 300 Stationary 130 Cards 200 10
                                                  140 Stationary 300 30

According to the three tables above the ProductTble will act as the LookUp Table as in a dropdown list. The StoreTbl allows you to have more than one store. The relationship between the StoreTble and the ProductTbl is many-to-many, meaning: Each and every store maybe composed of one-or-more products AND Each and every Product may be stored at one-or-more stores. But this type of relationship requires a "junction" table, the ProductDetailTbl. The new table arrangement will be: StoreTbl----ProductDetailTbl-----ProductTbl. The ProductDetailTbl will allow you to list all products by StoreID or by StoreID by Product or Products by Product ID.

Hope this did not confuse you.


========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.

Snazzy:
I must have been typing at the time you sent your last post. However this is not a problem. Again there are several ways to accomplish your latest question. Here is one of them:
Create InstructorTbl(InstructorID PK, Instructor Name);
CourseTbl(CourseID PK, CourseName, SectionName);
ProgrammeTbl(ProgrammeID PK, ProgrammeName,NumberHrs, CourseID FK);
ProductDetailTbl(ProdDtlID PK, DtlDesc, ProductID).

Using the Tables:
InstructorCourseTbl(InstrCrseID PK(sequential,counter), CourseID FK, CourseName, SectionName, InstrCrseID FK)

Use the PKs and FKs to relate and build the tables you need.

Hope this helps.

========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.

Hi,

Thanks again.

But I have already made these tables; 'Product', 'Instructor', 'Program' and 'Course'.

My question is how do I link Product and Course.

Because only product="book" has a link with course. Other products like stationary dont have a link with course.

So how do I create a relation between products and course? In order to satisfy the requirement that each course has one book (where book is a product) and that depending on course start date the books are re-stocked.

Snazzy:

If you recall in one of my earlier posts, you create relationships by using PKs(Primary Keys) and FKs(Foreign Keys) [Notice: the Foreign Keys are Primary Keys in the Parent Table]. You need a ProductDetailTbl. This Table identifies the "kinds of books", the "Kinds of gift items" and the "kinds of stationary". You need to ADD to the ProductDetailTbl, that is in addition to the variables previously discussed in earlier post, add the textbook-name and text-ISBN. The resultTbl is made from 3 tables ProductTbl, ProductDetailTbl and the CourseTbl.

Hope this helps.

========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.

Hi, thanks a lot.

I figured out that I dont need a relationship between Product and Course.

What I can do is, have a relation between course and book. From this relation I can get the Book ISBN #.

Then use this number to find it in products table.
Product has details and in details table I store Product code. Where the product book would have its ISBN # as its code.

Does it sounds fine?

Snazzy:

  THAT'S IT...YOU GOT IT!!!!

  That is how relations in Relational Database Management Systems function. Later you will get into "Normalizing" the Database.

Good Luck!!! Have Fun!

========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.