Question

1. Using Visio, Create an ERD based on the Crow’s Foot model, using the following requirements

These are the business rules to build an external model (end user view) of an ERD for our Purchasing Department.    These are our business rules:

• A PURCHASE ORDER (PO) is issued by a purchasing AGENT.
• Each AGENT can issue many POs, but each PO is issued by a single AGENT.
• Each PO is issued to a single VENDOR. However, each VENDOR can have many POs from us.
• Each PO can request many products. HINT: This means that each PO can include many detail lines (LINE), each of which describes one PRODUCT supplied to us by the VENDOR.
• The product information is stored in a PRODUCT entity.
• The product’s vendor information is found in a VENDOR entity.
• We deal with VENDORs who supply us our raw materials PRODUCTs. And we do purchase the same PRODUCT from multiple VENDORs.
• Some of our VENDORS are under approval; therefore, we have some VENDORs from which we have not yet purchased a product.

Answer 1

In the given problem statement the entities that are present in the given database are :- Agent, Purchasing_order, Product, Vendor, each entities in the crow foot's notation are represented by a square box with the entity name as heading.

Every attributes of the entities are listed in each box of the entities, since the attributes are not mentioned in the question therefore we assume the possible attributes for each of the entities, there is an unique attribute which help to uniquely identify each records of an entity, such attribute are represented by (*) in the attribute list of the entity and are called primary key,there are also foreign key present in each entities which are used to refer the primary key of the other entity.

The relational schema of the above database based on our assumption are as follows:-

Agent (Agent_ID, Name, Address, Gender, DOB, Contact_No)

Purchasing_Order (ID, Date,Quantity, Total, Agent_ID)

Product (Product_ID, Name, Price, Type, Vendor_ID)

Vendor (Vendor_ID, Name, Contact)

In entity Purchasing_Order attribute Agent_ID is the foreign key which refers the primary key attribute Agent_ID of entity Agent.

In entity Product attribute Vendor_ID is the foreign key which refers the primary key attribute Vendor_ID of entity Vendor.

Now talking about the cardinality and the participation of the entities in the database, it is clearly mention in the question that-

Each AGENT can issue many POs, but each PO is issued by a single AGENT. which means there is one-to-many relationship between entity Agent and Purchasing_Order

Each PO is issued to a single VENDOR. However, each VENDOR can have many PO's from us. which means there is one-to-many relationship between entity Vendor and Purchasing_Order

Each PO can request many products. which means there will be one-to-many relationship between entity Purchasing_Order and Product.

We do purchase the same PRODUCT from multiple VENDOR's. which means there will be one-to-many relationship between entities Product and Vendor

Some of our VENDORS are under approval; therefore, we have some VENDOR's from which we have not yet purchased a product. which means that there will be a partial participation between the entities Vendor and Product.

By keeping these points in mind the ER diagram based on Crow Foot's method will be as follows:-

This is the ER diagram of the database Purchasing Department, sorry i was not able to design the ER diagram in the visio,but i have made the diagram in paper, hope this diagram will help you.