A grocery chain needs a database designed in order to keep track of inventory and sales; the scenario to model is as follows: A customer goes to one of the stores to make a purchase, which contains one or more products. The employee ID of the cashier will be stored for the purchase; if the customer is a member of the loyalty program, his membership ID will be associated with the purchase as well (although this is not required). Each product will have a description and current price (applied to all stores); stock inventory count for these products will be tracked for each store. The purchase amount will be the total of all product prices multiplied by their quantity. After the customer pays, the purchase should be marked as paid, and the stock inventory count for all items in the purchase should be reduced for that store accordingly.

a. Read through the above scenario and determine which entities are involved (identify all of the things and decide whether they are entity classes or attributes). Create a simple ERD to show only the entities and the relationships between them (see the first lecture example Name the relationships (e.g. employs", schedules", etc) but do not show the relationship cardinalities nor any of the attributes. It is expected and perfectly OK to have at least one many-to-many and/or ternary relationship here, because these will be changed in the next step b. Expand the ERD from the previous question by adding the minimum and maximum relationship cardinalities to the model; eliminate any ternary and/or many-to-many relationships from the previous ERD by creating an intersection entity between them. Show optional/mandatory cardinalities and use crows-feet notation to display the many" sides. Include cardinality for all relationships between two entities c. Taking each pair of related entities at a time, write one sentence describing the relationship cardinalities (minimum and maximum) in each direction. For example, this relationship MPLOYEE STOVE ...may be stated An employee builds zero to many stoves/A stove is built by exactly one employee. d. Turn four of your entities into relations. Select an attribute(s) to represent the primary key; display this first, underlined. Include other attributes you would expect to find for this entity. Lastly, include any foreign keys which reference other entities; display these in italics Example from the FiredUp database STOVE (SeialNumber, Type, Version, DateOfManufacture, Color, FK_EmpID) e. What assumptions did you make when you were creating the ERD? Turn these assumptions into questions that you would ask the client in order to continue the design process. Include at least five questions. Be sure to address any ambiguities in the scenario that might affect your design. Example: Can two or more cashiers both be listed on a single purchase? This would create an M:N relationship that would require an intersection entity to resolve f. Assume the types of the columns in the SKU_DATA_4 table in Q7 are Colum Name SKU SKUDescription Buyer Data Type NUMERIC(6, 0 CHAR(128) Size Bytes per row 128 Bytes per row 04 Bytes per roW 64 Assume the store has 500,000 products. Ignoring any additional overhead, how large would the SKU_DATA 4 table be in Bytes? Show how you calculated this number a. Read through the above scenario and determine which entities are involved (identify all of the things and decide whether they are entity classes or attributes). Create a simple ERD to show only the entities and the relationships between them (see the first lecture example Name the relationships (e.g. employs", schedules", etc) but do not show the relationship cardinalities nor any of the attributes. It is expected and perfectly OK to have at least one many-to-many and/or ternary relationship here, because these will be changed in the next step b. Expand the ERD from the previous question by adding the minimum and maximum relationship cardinalities to the model; eliminate any ternary and/or many-to-many relationships from the previous ERD by creating an intersection entity between them. Show optional/mandatory cardinalities and use crows-feet notation to display the many" sides. Include cardinality for all relationships between two entities c. Taking each pair of related entities at a time, write one sentence describing the relationship cardinalities (minimum and maximum) in each direction. For example, this relationship MPLOYEE STOVE ...may be stated An employee builds zero to many stoves/A stove is built by exactly one employee. d. Turn four of your entities into relations. Select an attribute(s) to represent the primary key; display this first, underlined. Include other attributes you would expect to find for this entity. Lastly, include any foreign keys which reference other entities; display these in italics Example from the FiredUp database STOVE (SeialNumber, Type, Version, DateOfManufacture, Color, FK_EmpID) e. What assumptions did you make when you were creating the ERD? Turn these assumptions into questions that you would ask the client in order to continue the design process. Include at least five questions. Be sure to address any ambiguities in the scenario that might affect your design. Example: Can two or more cashiers both be listed on a single purchase? This would create an M:N relationship that would require an intersection entity to resolve f. Assume the types of the columns in the SKU_DATA_4 table in Q7 are Colum Name SKU SKUDescription Buyer Data Type NUMERIC(6, 0 CHAR(128) Size Bytes per row 128 Bytes per row 04 Bytes per roW 64 Assume the store has 500,000 products. Ignoring any additional overhead, how large would the SKU_DATA 4 table be in Bytes? Show how you calculated this number