Question

factory (including the name and the name of the director) needs to establish a management database to store the following information: I) A factory has a number of workshops, each workshop has the workshop number, workshop director's name, address and telephone number. II) There are more than one workers in each workshop, and each worker has a staff number, name, age, sex type of work. III) A workshop produces a variety of products, each of which has the product number and price. IV) A workshop produces a variety of parts, and one kind of parts may be manufactured by different workshops. Each part has the part number, weight and price. V) A product is consist of a variety of parts, and one kind of parts can be used to assemble a variety of products. VI) Products and parts are stored in the warehouses. VII) A factory has multiple warehouses, each of which has the warehouse number, name and phone of the warehouse director.

(1) Give the ER diagram for this management database.

(2) Mapping the ER diagram into the conceptual schema, and indicate the primary keys and foreign keys.

Answer 1

1) ER Diagram:

Please find below the steps to create ER Diagram for the given system:

Step 1: List entities along with attributes:

a) Workshop(workshop_no, director_name, address, telephone)

b) Worker(Staff_no, Name, Age, Sex, Work_Type)

c) Product(Product_name, Price)

d) Part(Part_no, Weight, Price)

e) Warehouse(Warehouse_no, Name, Phone)

Step 2: List the relationship between the entities:

a) Worker works under one workshop and a workshop has many workers. It is M:1 relationship.

b) Workshop produces number of products and it is assumed that one product is produced by one workshop only. It is 1:M relationship.

c) Workshop produces number of parts and a part is produced at many workshops. It is a M:N relationship.

d) Product consists of many Parts and a Part is used in assembling many Products. It is M:N relationship.

e) Warehouse has many Products and Parts. It is assumed that a kind of product and part is stored in only one warehouse. It is 1:M relationship.

Step 3: Create ER diagram:

2) ER Diagram with Primary and Foreign key

Please follow below steps to create ERD with keys in it:

Step 1: Resolve relationship by adding new entities OR adding foreign keys:

**Primary key is bold and foreign key is marked italic

a) Worker will have the workshop_id as foreign key where he works

Worker(Staff_no, name, age, sex, work_type, workshop_no)

b) Part need to store its workshop where it is stored:

Part(Part_no, Weight, Price, Warehouse_no)

c) Products will store the warehouse_no where these are stored:

Product(Product_name, Price, Warehouse_no)

d) Product will store the workshop where the products are created:

Product(Product_name, Price, Warehouse_no, workshop_no)

e) Parts to workshop is a M:N relationship to be stored as separate table as below:

Workshop_Parts(Workshop_no, Part_no)

f) Product and part is M:N relationship which needs to be stored as:

Product_Parts(Product_name, Part_no)

Step 2: Create ERD model:

PK denotes Primary key and FK denotes Foreign key: