A large retailer of outdoor goods and wares in town is interested in developing a new database to store information about the products that they offer on their website, the social media data obtained from Twitter about their products, and the sales of products.

Products are described using a unique product ID, description, standard price and year of make. Each product is associated with one or more SKUs these stock keeping units represent a unique combination of size and color, and are identified with a SKU#. The retailer wants to store information about the level of inventory for each SKU as well. For example, say products A and B have two SKUs each (A001, A002, B001, B002 so each SKU# by itself is unique)

A sales order is uniquely identified with a transaction ID, the ID of the user that makes the purchase, the date of the order, the order amount (total), number of items purchased, and the type of the order (online, in store, phone etc). A sales order can contain multiple SKUs and each SKU may appear on multiple sales orders. The retailer wants to keep track of each SKU purchased (not just product) in a sales order including the quantity purchased of that SKU, and the item sale price (which may differ from the standard price).

For each user that buys from this retailer, store the following information user ID, name, address, lifetime $ amount spent so far, and lifetime number of items purchased from the retailer.

Customers may also make returns of specific items in their sales order. For each item returned, the retailer wants to generate a unique return ID, and track the specific SKU# returned, date returned and quantity returned. For example, if a user bought two jackets of the same SKU (brand-model-color size), when there is a return, we want to track how many of the SKU items were returned (1 or 2 in this case). If the user returns both jackets but on separate occasions, there will be two entries in the return entity. Note: so return cannot be tracked simply by user or sales order alone because neither of these two entities has any information about the unique items purchased. So returns needs to be related to some other entity.

Finally, the retailer also wants to track the sentiment (one of three values-positive, negative or neutral) in the Twitter mentions for each product. The Twitter data is retrieved as described above using the TweetAnalytics tool, and parsed in a form that contains the following data: tweet ID, author ID, date, tweet text, product IDs mentioned in the tweet and the sentiment about a particular product ID. One tweet may mention one, or more product IDs, and a single tweet may contain multiple different sentiments about the mentioned products. For example, I hate product B, but love product A. One product can have many tweets about it.

TASK

Please draw a 3NF physical ER data diagram for the above described business need using VISIO or LucidChart or another diagramming tool

Clearly identify the primary keys of each entity.

Use the Crows foot notation (not any other)

Indicate cardinality AND modality on all relationships.

Resolve any many to many cardinalities that you may find.

For intersection/associative entity use a composite key NOT a surrogate key

Include appropriate foreign keys in the tables.

Indicate data type for the attributes and their size (if appropriate)

Ensure that your model is in 3NF