You will need to maintain a list of all fashion products carried. For each store, you...

Transcribed Image Text:

You will need to maintain a list of all fashion products carried. For each store, you will need to maintain the inventory of all products. For each product, you track a list price, but for now, you require the price to be the same across all stores. You dream of someday opening stores across Canada and know the price will vary across Canada, but for now, you want to focus on the minimum viable product and build the necessary scalability into the database. You do anticipate opening more stores throughout Alberta. You also have an old customer database where you track customers. Their purchases are tracked via invoices, including the quantity, list price, percentage discount, and price paid for each item. At the time of sale, the list price matches the list price for the product, but the product list price can change; whereas the invoice list price remains the same. Unfortunately, this database has been corrupted and you need to start from the beginning. Product are grouped into product lines (formal and casual), although you anticipate adding wedding clothes in the future. Products are further classified into different categories (for example shirt, shoes, belts etc.). Each product may belong to only one category. Additionally, products are associated with specific tags to help identify sales opportunities. For instance, shirts may be associated with the tags like "spring", "3 summer", "fall", "winter", "wool", "synthetice", etc. Additional Detail While not all products are available at all stores, there is a large overlap in the products. In addition to the invoice percentage discount for each item, there may be an overall discount applied to a complete invoice. For each customer, an address and list of phone numbers are tracked. Previously you only allowed customers to enter a home and mobile number, but you found many customers are giving an alternate mobile number and you have decided the new database should support as many phone numbers as customers choose to give. At least one phone number is required for easy lookup. For small quick purchases, without a discount, there is a "guest" customer that is associated with the purchase. This customer is associated with the phone number "000-000-0000". Stores are identified by a short name (e.g. "Stephen Avenue" is short for and a full name of "Calgary - Stephen Avenue", or "Mountain" for "Canmore - Mountain"). It is important to understand what products are sold at which stores. Although somewhat redundant based on the list price and percentage discount, the actual sell price and total invoice amount are both stored in the database fo easy querying later. Products are sold either by item, or size or weight. You can't be sure that you won't allow additional methods (such as length for measuring rope) in the future, so you have decided to create a table to allow for new methods to b added. Assume there is no sales tax on any of the items. ASSIGNMENT INSTRUCTIONS Create an Entity Relationship Diagram (ERD) for your fashion shop situation. Include the cardinality of relationships and make sure to define the primary (PK) keys (s), foreign (FK) key(s), composite (CK) keys and which attributes are involved in each relationship. All bridging tables should make use of a composite key. Many-to-Many relationships may not be used, and your final product should be in 3rd You will need to maintain a list of all fashion products carried. For each store, you will need to maintain the inventory of all products. For each product, you track a list price, but for now, you require the price to be the same across all stores. You dream of someday opening stores across Canada and know the price will vary across Canada, but for now, you want to focus on the minimum viable product and build the necessary scalability into the database. You do anticipate opening more stores throughout Alberta. You also have an old customer database where you track customers. Their purchases are tracked via invoices, including the quantity, list price, percentage discount, and price paid for each item. At the time of sale, the list price matches the list price for the product, but the product list price can change; whereas the invoice list price remains the same. Unfortunately, this database has been corrupted and you need to start from the beginning. Product are grouped into product lines (formal and casual), although you anticipate adding wedding clothes in the future. Products are further classified into different categories (for example shirt, shoes, belts etc.). Each product may belong to only one category. Additionally, products are associated with specific tags to help identify sales opportunities. For instance, shirts may be associated with the tags like "spring", "3 summer", "fall", "winter", "wool", "synthetice", etc. Additional Detail While not all products are available at all stores, there is a large overlap in the products. In addition to the invoice percentage discount for each item, there may be an overall discount applied to a complete invoice. For each customer, an address and list of phone numbers are tracked. Previously you only allowed customers to enter a home and mobile number, but you found many customers are giving an alternate mobile number and you have decided the new database should support as many phone numbers as customers choose to give. At least one phone number is required for easy lookup. For small quick purchases, without a discount, there is a "guest" customer that is associated with the purchase. This customer is associated with the phone number "000-000-0000". Stores are identified by a short name (e.g. "Stephen Avenue" is short for and a full name of "Calgary - Stephen Avenue", or "Mountain" for "Canmore - Mountain"). It is important to understand what products are sold at which stores. Although somewhat redundant based on the list price and percentage discount, the actual sell price and total invoice amount are both stored in the database fo easy querying later. Products are sold either by item, or size or weight. You can't be sure that you won't allow additional methods (such as length for measuring rope) in the future, so you have decided to create a table to allow for new methods to b added. Assume there is no sales tax on any of the items. ASSIGNMENT INSTRUCTIONS Create an Entity Relationship Diagram (ERD) for your fashion shop situation. Include the cardinality of relationships and make sure to define the primary (PK) keys (s), foreign (FK) key(s), composite (CK) keys and which attributes are involved in each relationship. All bridging tables should make use of a composite key. Many-to-Many relationships may not be used, and your final product should be in 3rd

Answer rating: 100% (QA)

Im unable to directly create an Entity Relationship Diagram ERD because I cant generate images However I can describe how to create an ERD for your fa... View the full answer