rewrite

· Coupon_ID: identification number for coupons. No two separate coupons can have the same id

· Coupon_value: value in dollars

· Service_ID: identification number for various services. No two separate services can have the same id

· Service_name: self-explanatory

· Promotion_ID: identification number for promotions. No two separate promotions can have the same id

· Promotion_type: for example “to all customers”

· Promotion_details: self-explanatory

· Customer_ID: identification number for customers. No two separate customers can have the same id

· Customer_name: self-explanatory

· Customer_address: For example 31st Wall St, New York

· Customer_phone: For example 1-555-4898

· Transaction_ID: identification number for transactions. No two separate transactions can have the same id

· Transaction _value: the accumulated value from both products purchased and services for a specific customer

· Transaction_date: the date when the transaction took place

· Product_ID: identification number for products like Bar Code. No two separate products can have the same id

· Product_name: self-explanatory

· Product_value: in dollars

3. Using your answer to Question 2, designate which attribute or attributes form the identifier for each entity type. Explain why you chose each identifier

As shown in the above diagram (indicator for primary key – PK), I have designate an attribute called entity_ID for every entity. No two separate instances of the same entity can have the same ID. I could have chosen a more complex primary key for each entity. For example, for entity Product I could have used both its name and its value. To avoid ambiguities I chose this simple and still efficient primary key for every enity.

4. Using your answer to Question 3, draw the relationships between entity types needed by the system. Remember, a relationship is needed only if the system wants data about associated entity instances. Give a meaningful name to each relationship. Specify cardinalities for each relationship and explain how you decided on each minimum and maximum cardinality at each end of each relationship. State any assumptions you made if the Petrie’s Electronics cases you have read so far and the answers to questions in these cases do not provide the evidence to justify the cardinalities you choose. Redraw your final E-R diagram in Microsoft Visio

The above diagram shows relationships and their names. Each cardinality is explained below:

· Coupon_is_received: Customer may earn zero to many coupons according to their purchases. Loyal customers will receive many coupons. Each coupon is unique and it is associated to only a specific customer

· Promotion_is_earned: Customers may earn zero to many promotions according to their purchases. Customers purchasing old movies may receive more promotions, for example. Each promotion is unique and it is associated to only a specific customer

· Customer_makes_transaction: A customer may make one to many transactions. One must make at least a transaction to be thought as customer. Each transaction is unique and it is associated to only a specific customer

· Transaction_involves_service and Transaction_involves_product: A transaction may involve zero to many products and zero to many products. On the other hand, a specific service or a specific product is associated to a unique transaction

CouponCustomerServiceTransactionPromotionProductCoupon_IDPKCoupon_valueCustomer_IDPKCustomer_nameCustomer_addressCustomer_phoneService_IDPKService_nameTransacton_IDPKTransaction_typeTransaction_valueTransaction_datePromotion_IDPKPromotion_typePromoion_detailsProduct_IDPKProduct_nameProduct_value

Coupon Customer Service Transaction Promotion Product Coupon_ID int FK PK Coupon_value int FK PK Customer_ID int FK PK Customer_name int FK PK Customer_address int FK PK Customer_phone int FK PK Service_ID int FK PK Service_name int FK PK M1 M2 M3 M4 Transacton_ID int FK PK Transaction_type int FK PK Transaction_value Transaction_date int FK PK Promotion_ID int FK PK Promotion_type int FK PK Promoion_details int FK PK Product_ID int FK PK Product_name int FK PK Product_value int FK PK M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4