Question

Chanteq Boutique is a local dress manufacturer. It sells its products to major distributors who distribute through their own stores in major cities and shopping malls. The primary product line is a casual dress for women. Chanteq Boutique is in the process of designing a database system. The requirements needed are as follows: Chanteq Boutique manufactures several products. Product id is a unique identifier. Product names also need to be stored. Each product has been assigned to a different stock-keeping unit (SKU). Each SKU belongs to a specific product. SKU number is a unique identifier of SKU. SKU description is also recorded. Chanteq Boutique has created several unique colors for its dress. Each color is uniquely identified by a color id. A color description will describe the color options (e.g. red, blue, navy). Each SKU has only one color. Some colors have been assigned to SKU or may never be used. Each retailer will have a contract with Chanteq Boutique based on the products it will distribute. A retailer will distribute one or more products, and a particular product will be distributed by more than one retailer. Each retailer has a unique id, name and phone number. Retailers have stores, which are the places where they sell the skirt and other products. Each retailer has one or more stores. To identify a store in the Chanteq Boutique database for shipping purposes, all customers have agreed to provide their store id and customer id as confirmation. They also need to write their address. Based on the given information: a) Construct an Entity Relationship Diagram (ERD) complete with all the required tables, attributes, relationships as well as the cardinalities using the Crow's Foot notation that reflects all of the given business rules. (16 marks) b) Briefly explain TWO (2) examples of reports that can be generated from ERD in (a). (4 marks)

Answer 1

The Examples of reports that can be generated from ERD in (a) are:

• Report 1: Top 10 best-selling products by retailer
• Report 2: Sales trends by color

In terms of Report 1, This report would show the top 10 best-selling products for each retailer. This information could be used by Chanteq Boutique to determine which products are most popular with customers and to focus their marketing efforts on those products.

In terms of Report 2 This report would show the sales trends for each color of dress. This information could be used by Chanteq Boutique to determine which colors are most popular with customers and to plan their production accordingly.