Design a database for a retail store that orders different products from various

Question

Design a database for a retail store that orders different products from various vendors and sells them to the customers.

The data requirements: maintain the customer information, product inventory, product vendors, orders placed with vendors and sales to the customers.

Functional requirements: product inventory information, monthly sales, profits from sales

Present a conceptual database design using Entity-Relationship diagram (ERD). Determine the attributes of all the data sources from the functional requirements. Show all the entity types with possible primary keys and attributes identify the relationships with cardinality numbers and any attributes applicable to relationships.

Explanation / Answer

Customer(custid, fname, lname, contact)
prod_inv(proid, poname, curr_stock)
pro_vend(venid, venname, vencontact, proid)
orderven(pur_id, prodid, vendid, qty, price, tot, ord_date)
sale_cust(saleid, custid, prodid, qty, price, tot, sale_date)

select a.prodid, a.tot-b.tot from sale_cust a,orderven b where a.prodid=b.proid;

select prodid,sum(tot), month(sale_date) from sale_cust group by sale_date;

select sale_cust.tot - orderven.tot from sale_cust, orderven where sale_cust.prodid = orderven.prodid;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.