Question

Project 2: Database System Design

Background:

An department store is currently developing a new database system to store all of its information. The new system would enable the store to enter data regarding inventory levels, ordering, and so on.

Currently, the data for inventory and sales tracking are stored in one file that has the following format:

Item List: ItemID, ItemName, Item Description, Price, Cost,

Supplier List: SupplierID, SupplierName,

Supply order List: SupplyOrderID, ShippingTime, DeliveryDate,

Inventory List: InventoryID, QuantityOnHand,

Customer List: CustomerID, CustomerLastName, CustomerFirstName,

Customer order List: OrderID, PurchaseDate,

Notes:

Purchase date information refers to a Customer’s purchase of an item from the store.

Delivery date information refers to the Store’s receipt of a shipment of an item from the supplier.

Complete the following tasks based on the provided information (10/10 points):

List all the entities of the new database (3/10 points):

List the primary and foreign keys of each entity (3/10 points):

Design an ER-diagram based on the provided information using Lucidchart (4/10 points):

Please copy and paste the ER diagram to the space below:

Project 2: Database System Design Background: An department store is currently developing a new database system to store all of its information. The new system would enable the store to enter data regarding inventory levels, ordering, and so on. Currently, the data for inventory and sales tracking are stored in one file that has the following format: Item List: ItemID, ItemName, Item Description, Price, Cost, Supplier List: Supplier D, SupplierName, Supply order List: SupplyOrderID, ShippingTime, DeliveryDate, Inventory List: InventoryID, QuantityOnHand, Customer List: Customerid, CustomerlastName, Customer FirstName, Customer order List: OrderID, Purchase Date, Notes: Purchase date information refers to a Customer's purchase of an item from the store. • Delivery date information refers to the Store's receipt of a shipment of an item from the supplier. Complete the following tasks based on the provided information (10/10 points): • List all the entities of the new database (3/10 points): • List the primary and foreign keys of each entity (3/10 points): • Design an ER-diagram based on the provided information using Lucidchart (4/10 points): Please copy and paste the ER diagram to the space below:

Answer 1

List all the entities of the new database;-

Customer_detail (Customer_id,Customer_lastName,Customer_FirstName,Order_Id,Purchase_date)

Supplier(Supplier_id,Supplier_Name,Supplier_orderId, QuantityOnHnands ,Item_id, Item_Description,Price,shipping_date,delivery_date)

orderdetail(order_id,customer_id,oder_date,item_id,item_description,quantity,supplier_id)

customer_detail is the entity which has the attribute Customer_id, Customer_lastName, Customer_FirstName, Order_Id, Purchase_date.when customer ordered then order_id has been alloted to them and content the date of the order or purchase.

Supplier is the entity which has the attribute Supplier_id,Supplier_Name,Supplier_orderId, QuantityOnHnands ,Item_id, Item_Description,Price,shipping_date,delivery_date.suuplier entity content supplier details like their name ,id,which item of them they supply, item's description,price and quantity they have available and order id, order they have received from customer.

orderdetail is the entity which has the attribute order_id ,customer_id, oder_date, item_id, item_discription, quantity, supplier_id.So order id is same as the customer id and suppiler_orderid,quantity here to see the sufficient amount of that product is available at supplier or not,item details which customer requires.

List the primary and foreign keys of each entity

primary keys

customer_detail (customer _id,order _id)

supplier_detail(supplier_id,suppiler_orderid)

order_detail(customer _id,order _id,supplier_id)

foreign keys

order_detail (customer id and supplier id is a foreign key)

customer_detail (order _id is a foreign key)

supplier_detail(suppiler_orderid is a forign key)

Design an ER-diagram based on the provided information

Orders item to suppiler Customer detail Orders item Supplies item Order detail
Please draw the oval shape in each entity for the all attribute of that entity.So it represents the entity relationship diagram of the database