Figure 4-33 (page 194) shows an EER diagram for a simplified credit card environ

Question

Figure 4-33 (page 194) shows an EER diagram for a simplified credit card environment. There are two types of card accounts: debit cards and credit cards. Credit card accounts accumulate charges with merchants. Each charge is identified by the date and time of the charge as well as the primary keys of merchant and credit card. a. Develop a relational schema. b. Show the functional dependencies. c. Develop a set of 3NF relations using an enterprise key

CUSTOMER Customer ID Cust Name Cust Address ttybs CARD ACCOUNT Account ID Exp Date Card Type MERCHANT Merch ID Merch Addr Card Type- C" DEBIT CARD Bank No CREDIT CARD Cur Bal CHARGES Charge Date Charge Time Amount

Explanation / Answer

For the above EER diagram for a simplified credit card environment

a) The relational schemas would be :-

For all entity sets

1. Customer (Customer_id, Cust_name, cust_address)

2.Card Account (Account_id,Exp_date, Card_type)

3. Debit_Card (Account_id, Bank_no)       [--> Account_id being a generalization relationship of Card Account]

4. Credit_Card ( Account_id, Cur_Bal)      [--> Account_id being a generalization relationship of Card Account]

5. Merchant (Merch_id, Merch_Addr)

6. Charges(Account_id, Merch_id,Charge_date,Charge_Time, Amount )

b) Functional Dependencies:-

The functional dependencies will be as follows:-

1. Cust_name and cust_address are functionally dependent on Customer_id

Customer_id --> Cust_name , cust_address

2. Exp_date, Card_type, Bank_no, Cur_Bal are functionally dependent on Account_id

Account_id --> Exp_date, Card_type, Bank_no, Cur_Bal

3. Merch_Addr is functionally dependent on Merch_id

Merch_id --> Merch_Addr

4. Charge_date,Charge_Time and Amount are functionally dependent on both Account_id, Merch_id

Account_id, Merch_id --> Charge_date,Charge_Time, Amount

c) Set of 3NF Relation

A relation is said to be in 3NF when every non-prime attribute of table must be dependent on primary key, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. Well the relation schema shown above is in 3NF as there is no transitivity in any of the relations and also all our relation schemas are in Second Normal form which says that there must not be any partial dependency of any column on primary key.

So the final relation schemas are :-

1. Customer (Customer_id, Cust_name, cust_address)

2.Card Account (Account_id,Exp_date, Card_type)

3. Debit_Card (Account_id, Bank_no)       [--> Account_id being a generalization relationship of Card Account]

4. Credit_Card ( Account_id, Cur_Bal)      [--> Account_id being a generalization relationship of Card Account]

5. Merchant (Merch_id, Merch_Addr)

6. Charges(Account_id, Merch_id,Charge_date,Charge_Time, Amount )

Where the primary keys for each relation is being indicated with Bold and Underline.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.