Mechanical Engineering company relational schema

[dreamygirl]

Good morning ,

I'm facing a difficulty defining some enty type in this case :
1. You are a systems analyst. You have been assigned to design a database management system (DBMS) for Mechanical Engineering (ME) company. ME orders its parts from two types of vendors-local and non-local vendors. Vendors are tracked by vendor #, name, address, and telephone #. Parts are ordered by means of a purchase order. Local vendors can supply both assembled and sub-parts, while non-local vendors can supply only subparts. For each order, ME would like keep track of information such as order #, quantity ordered, date requested and date shipped. A single part is ordered on each purchase order. A part can be an assembled part or a sub-part. An assembled part can contain two or more sub-parts and each sub-part can be used in two or more assembled parts. Parts should have: part#, description, and cost.
ME has requested that the DBMS that you design should provide capability to list the sub-parts that make up an assembled part and from
which vendor it is ordered.

I have identified the following entity types :
1- Vendor
2- Part
3-Order

But its mentioned that vendors have 2 types :local and non local
the same is for the parts : assembled and subparts.

Should I add a comosite attirbute to both of the relations called : type ?

Another question is : If an attribute exists in a relationship and it's a primary key in an entity type that is particpating in the this relationship , what should i do in this case while creating the relational table for the relationship?

thanks in advance :)

[timothybard]

To answer your questions:
1) I would add an attribute to the Vendor table to track local or non-local
2) I would not add anything to the parts table to track assembled and subparts; that should be tracked in a different fashion...
3) An attribute being a primary key of an entity doesn't change how you build a relationship between entities. Are you running into a problem of some sort? You may have issues adding records if your relationship is incorrect or you try to add bad data, but the actual building of the relationships is the same whether the attribute is a primary key or not a primary key.

The questions that you need to answer are:
1) How should assembled and subparts parts be tracked?
2) How do you know which subparts make up an assembled part?

These questions are related and answering one should lead you to the answer of the other.

[dreamygirl]

hmmm , but its mentioned that the schema should provide capability to list the sub-parts that make up an assembled part and from which vendor it is ordered.

so i have to add the attribute "type" to both of them right ?

anyway this is the schema i came up with :

http://alfaris.net/up/v.php?p=33/alfaris_net_1241543364.jpg

is it right ?