Problem:
Consider a real-world organization such as Walmart orKroger. (You should feel free toengage in additional research to gather relevant information to supplement this case description.)
The organization generally operatesmultiple linesof business. (For instance,Walmart has different lines of storesincluding Supercenter, Neighborhood Market,Express, DiscountStore, and Sams Club.)Each line of business hasmany retailstores in differentregions of the United States. Each store has several employees in different roles such as associate, supervisor, and manager. Each employee is assignedto a specific department within the store, whichgenerally changes every other week or so. Each line of businessmay have different departments such asgrocery,home goods, toys, electronics, and pharmacy within its stores.The departments may differ between thelines of business. Each department carries differentlines of productssuch as apparel, snacks, phones, cookware,and jewelry.The lines of productsmay be sold by different units of measure such as pieces or pounds.The products may be subjectdifferent levelsof sales tax includingno sales tax. The sales taxes are imposedby the localgovernments in the regions in which the stores are operated. The storescater to individual consumers as well ascorporate customers. The organization necessarily maintainsinformation about corporatecustomers
since they may be billed periodically or later for their purchases. Information about individual consumers may or may not be gathered depending on whetherthey opt to sign up for loyalty programs. Customersmay complete manysalestransactions at any of the storesand each sales transaction may containany number of items. Theorganization periodically replenishes the inventory at its many stores from the inventory it holds in its manywarehouses. The decision to replenish is based on predefined reorder levels.The organization manages many trucks to ship the replenishment inventory to its many stores.The trucks are generally affiliated with specificwarehouses and take specificroutes and cater to specific stores. When inventory at the warehouses run low, theorganization generates purchase orders to different suppliers. Each purchase order may be used to order multipletypes of products.Each supplier generally supplies a predefined set of productsfor the organization, which are sentto the warehouses that generatedthe purchase orders.
Answer the following questionsbased on the case description above as well as any additional information you mayhave gathered.Be sure to state your assumptions. Provide justification for any solutionchoices and includeany necessary description of your solution stepsand your final solution. (In other words, an E-R diagramalone is notsufficient for the deliverable; you should demonstrate your knowledgeof data modeling.)
Required:
Question 1) Identify the entities.Be sure to considerstrong and weak entitiesas well as supertypes and subtypes.
Question 2) Identify the attributes for each entity.Be sure to consider multivalued and derived attributes. Be sure to identify primary and foreign keys.
Question 3) Identify the relationships between entities, including multiplicity constraints. Be sure to considerbinary,ternary, and recursive relationships.
Question 4) Develop a single E-R model that combinesall entities, attributes, and relationships you identified in yourresponses above.Be sure to developthe E-R diagram in Visio.
Question 5) Describe the advantages and limitations of your E-R model.Reflect on the extent to which your E-R model is consistent with business requirements.
Provide thorough explanation of every question given in the problem.