Map the ER schema below into a relational schema. (Click for a larger version.) For any case where you have a choice of ways to map a particular construct, choose the mapping that you think is best for this situation and explain your decision. Also be sure to specify all primary key and referential integrity (foreign key) constraints.

Solution:

    CUSTOMER(custID,firstname,lastname,phoneAreacode,phoneNumber)
    SUPPLIER(name,contactName,phoneCountryCode,phoneAreacode,phoneNumber,
             addrStreet,addrCity,addrState,addrZip,addrCountry)
    ITEM(itemNum,itemName,qtyInStock)
    
    CREDITCARD(custID,number,expiration,type,billingStreet,
               billingCity,billingState,billingZip)
    COUPON(ordernum,coupon)

    ORDER(ordernum,date,shipStreet,shipCity,shipState,shipZip,
          shipAreacode,shipPhone, custID, ccCustID,ccNumber,ccExpiration)

    CONTAINS(ordernum,itemNum,quantity,unitPrice)
    SUPPLIES(supplier,itemNum,unitCost)
    

The total participation of ORDER in CONTAINS and ITEM in SUPPLIES are not captured in this schema - there's no way to require that a particular entity be referenced from another relation.

Discussion:

Common problems: