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.
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.
CREDIT CARD is a weak entity type, so it needs to include the primary key of the entity type it depends on as part of its own primary key - hence custID in CREDITCARD.
coupon (ORDER) is multivalued. This is handled with a separate relation, as shown with COUPON.
tax and subtotal are derived. Since they can be computed from other data in the database, no values need to be stored so they are not included in the relational schema.
Binary 1:N relationships are often best handled using the foreign key approach. The primary key of the "1" entity type is added to the relation for the "N" entity type as a foreign key, but is not included in that primary key. NOT NULL applies to the foreign key if the "N" entity type has total participation in the relationship.
HAS CREDIT CARD is an identifying relationship and was already handled as part of handling the weak entity type CREDIT CARD.
PLACES is handled by adding custID to ORDER. It is not part of ORDER's primary key (that captures the "1" cardinality) but should be NOT NULL (that captures the total participation of ORDER).
PAYS FOR is handled by adding the entire primary key for CREDITCARD - custID, number, and expiration - to ORDER. These are not part of ORDER's primary key (that captures the "1" cardinality) but should be NOT NULL (that captures the total participation of ORDER). Note that this means ORDER has two separate customer ID attributes - one for the customer who places the order and one for the customer who owns the credit card used to pay for the order.
Binary M:N relationships are often best handled with the cross reference approach, meaning a separate relation corresponding to the relationship with attributes consisting of the primary keys of the two entity types involved plus any attributes of the relationship type itself. This was done for SUPPLIES and CONTAINS.
Omitting an attribute.
Include extra attributes in CREDITCARD's key or not including custID in its key.
Making coupon an attribute of ORDER (which only allows a single coupon per order).
Including derived attributes tax and subtotal in ORDER.
Having only a single custID attribute in ORDER to cover both PLACES and PAYS FOR.
Getting the foreign key approach for binary 1:N relationships backwards e.g. adding ordernum to CUSTOMER and CREDITCARD. This makes it impossible to get the participation and cardinality constraints fully correct.
Including custID in ORDER's key. It needs to not be part of the PK to enforce the "1" cardinality.
Using the foreign key approach for binary M:N relationships. This leads to repeated information.
Not addressing cardinality and participation constraints for relationships, especially M:N relationships. (It may not be possible to express the participation constraints in the diagram.)
Not indicating the foreign keys.