Question

For our Expenditure (Purchases-Payments) cycle, we want to store the following data about our purchases of inventory: item number date of purchase vendor number vendor address vendor name purchase price quantity purchased employee number employee name purchase order number description quantity on hand extended amount total amount of purchase a. Semantically create a conceptual design (entity-relationship diagram) with max cardinalities. b. Design a set of properly normalized relational tables to store the data, using the following format: Table Name Primary Key Foreign Key(s)(?) Other non- key attributes.

Answer 1

Solutions enlity reall (A) Entity Relationship Diagium. following the process below to Create the velchionship_diagram - List possible entities from -given list Lot attributes given. en clala Columns belong Ho below entities a. Inventroy in item. To slove all the cletuiles about Hems Item vendor the wendor who Supplies the litem Employees. The employees of the country orders - The orders item Stock placed I for

2. Assign aldribules from the cetributes to respective entities identified above. 0 Inventory Tlem litem number). item description, on hand quan: tity? 0 Item, vendor (vendor number names address) in need to vendor in all allibule the teble name itself defines the meaning of allkib no te 14 Purchase order purchase orde number/ order date total amou © Employee Cemployee number, name © -Endity Real Relationship D. am a Cconceptual model)

3. Entity Relationship Diagram (Conceptual Model)

item_description (on_hand_quantity item_number Inventory_item purchase_price name name employee_number address stocked in via quantity vendor number Employee extended_price 111 Item_Vendor makes supply 04 Purchase_Order > purchase order number total_amount order_Date

or many one with it Real Relationship anel Cardina ality: Symbol. Below are the symbols used in the Created ERDE AF one to many zero . Exactly Shows below car. Idinalities! 1 An employee can make nonov many purchase orders but order have exactly one employee , 2 @ purchase order 13_supplied by one item a vendor (exadly! but an item vendor Can har The diagram an

3. SInventory-item is Stocked in celleast مسم b) creating orders and an one to many using order Inventory cite can have many in it i normalized cilabase: follow the points listed below to create the set of normalised Hables from obtained: entity read relationship_Conceptual model o invento invenol yn item (item number) item-description, on hand-quantity 0 Item Venclor_(purchase order number lorder a clate Total amount) (3 2 employee C employee number name) Add foreign key to store relationships purchase order to employee :-(A purchar order will have one employee for purchase placing the order thus it need to @

Store its as below employee reference -pur chase order (purchase order number order duten total amount gemployee) purchase-order to item -vendor: A parcheele order will be placed to one item. vendox_for supply ing the order. This ii need to is vendore retence as below purchase order cpurchase order number order date, total amount, emplo yee, ven.dor. number c u 0 purchase Ordes -40 in Verboxy tem :-(Apurchase order will be I have ettleast having whest one Ho many inventory itemes in il with clifferent cuuclity and.- @ptended - price Shus ita many to many relationship To bes bé Sloved Separate table below ET

Order__items_cordo humbers, item nuber quantity purchase price extended-price) 3. Below is final along withe in it of table required key. Sabaul Nune primary key Inventory-item numbes item. lovegin key non key.odbib NA description quntity - on hand, extend Lamount NA Item ven vendor number dov vendor - naine vendor -addr less. Employee purchture-order number employ fee numbes NA Temployee -nar purchase purchase oreles employee-hun lale_of order number " her vendar puch purchas number ». otal amount of purchau

order order numb order number quah, quantity item est item num item number purchaere purcha- Iber 'se price