Question

Tina loves tea. She is passionate about brewing and enjoying the perfect cup of tea. Her passion for tea is

so great that she has recently decided to quit her government job and pursue her dream, of owning her

very own tea shop.

Tina knoWs that she will need some way of tracking her inuentory and suppliers as well as other importan

business information, including

The database must keep track of her product inventory, including information such as product

name, product type, description, cost, seling price and supplier

She also wishes to track the following customer information name, contact intoem.ation, birthday,

and purchase histor

For suppliers, she would ike to keep their contact information inciuding the company name, sales

representative name, telephone, email, address and inotes

Once her database is operationa, Tina woulid lke to use it to keep track of sales and manage her

inventory.

Some of the reports that Tina would like to produce include:

1. A customer contact list.

2. A list of suppliers

3. An inventory report showing all inventories along with the supplier

4. A sales report showing who bought what for a given period of time.

5. Other sales reports such as total sales by product, total sales for a date range, total sales by product

type, etc.

Draw an ERD and Identify the entities (Primary and join tables)-include all attributes, relationships

(Primary & Foreign Keys), and degree of relationships (one-to-one, one-to-many, many-to-many) See

Data Modeling example (Twigs Tree Trimming Service) Example provided

Answer 1

Please find below the steps to obtain the ERD for the given scenario:

Step 1: List the required entities:

1. Customer: To handle the customer details

2. Supplier: To handle the supplier details

3. ProductInventory: To handle the products in the inventory

4. ProductCategory: The handle the different categories for the products

5. SaleOrder: To store the sale history for products and customers

Step 2: Assign attributes to the entities:

1. Customer(customer_id, name, address, contact, birthday)

2. Supplier(company_id, company_name, representative_name, address, contact, email, note)

3. InventoryProduct (product_ID, name, description, cost, selling_price, units_in stock, units_on_order)

4. ProductCategory(category_id, name, description)

5. SaleOrder(sale_id, date, bill_amount, additional_note)

Step 3: List relationship between the entities:

1. Customer will have Many SaleOrder whereas a SaleOrder will belong to one customer, thus it is a One to Many relationship.

2. InventoryProduct will belong to one ProductCategory, whereas a ProductCategory can have many InventoryProduct. It is a Many to One relationship.

3. InventoryProduct will be supplied by one Supplier, whereas a Supplier can supply many InventoryProduct. It is a Many to One relationship.

4. Sale will have an InventoryProduct, an InventoryProduct will have many Sales. It is a Many to One relationship.

Step 4: Create ERD:

ERD below shows the Entity attributes:

PK = Primary key

FK = Foreign key

Customer SaleOrder PK customer_id IT OCPK sale_id name FK1 customer_id address Date contact bill_amount birthday additional_note FK2 product_id Supplier Product Category PK company_id PK category_id Inventory Product name company_name PK product_id description name representative_name IH address FK1 category_id contact description email cost note selling_price units_in_stock quantity_on_order FK2 supplier_company_id