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

Note:

• In the diagrams, to indicate primary and foreign keys notational conventions are used.

o The attributes noted with underlined name are primary key values.

o The attributes noted with italics letters are foreign key values.

Explanation:

• The relational schema consists of six relations, they are, CUSTOMER, CARD ACCOUNT, DEBIT CARD, CREDIT CARD, CHARGES and MERCHANT.

• In the relation CUSTOMER,

o The attributes are CustomerID, CustName, and CustAddress.

o Here, CustomerID is the primary key.

• In the relation CARD ACCOUNT,

o The attributes are AccountID, ExpDate, CardType and CustomerID.

o Here, AccountID is the primary key.

o CustomerID is the foreign key which is used to refer CUSTOMER relation.

• In the relation DEBIT CARD,

o The attributes are DAccountID and BankNo.

o DAccountID is the foreign key attribute which is used to refer CARD ACCOUNT relation.

• In the relation CREDIT CARD,

o The attributes are CAccountID and CurrBal.

o CAccountID is the foreign key attribute which is used to refer CARD ACCOUNT relation.

• In the relation CHARGES,

o The attributes are MerchID, CAccountID, ChargeDate, ChargeTime, and Amount.

o The charges of the card items are identified by the date and time of charge, which acts as the primary key for the relation. ChargeDate and ChargeTime are primary key attributes.

o MerchID and CAccountID are the foreign key attributes which is used to refer CARD ACCOUNT and MERCHANT relations respectively.

• In the relation MERCHANT,

o The attributes are MerchID and MerchAddr.

o MerchID is the primary key attribute for the relation.

The diagram to describe the functional dependency in the EER (Enhanced Entity Relationship) diagram is as given below:

Explanation:

• Functional dependency is a constraint between two attributes or a set of attributes.

• In a relation, one attribute is functionally dependent on another attribute to get one value.

• In the relation CUSTOMER,

o The attributes are CustName, and CustAddress are functionally dependent on CustomerID.

• In the relation CARD ACCOUNT,

o The attributes are ExpDate, CardType and CustomerID are functionally dependent on AccountID.

• In the relation DEBIT CARD,

o The attribute DAccountID is functionally dependent on BankNo.

• In the relation CREDIT CARD,

o The attribute CAccountID is functionally dependent on CurrBal.

• In the relation CHARGES,

o The attributes are MerchID, CAccountID, ChargeDate, ChargeTime, are functionally dependent on Amount.

• In the relation MERCHANT,

o The attribute MerchID is functionally dependent on MerchAddr.

(c) Using an Enterprise key develop a set of 3NF relations

The set of third normal form (3NF) relations using an enterprise key is as given below:

Enterprise key:

• To strength the standards of a primary key in the database, experts recommend new key known as enterprise key.

• Enterprise key is type of primary key, primary is unique in a table, whereas enterprise key is unique across the database.

Note:

• The highlighted attributes are foreign key attributes, and the underlined attributes are primary key attributes.

Explanation:

OBJECT (OID, Object type)

• In the above line, OBJECT is the relation; the attributes inside the relation are OID and Object Type.

• Here, OID acts as a primary key as well as enterprise key, this key is unique across the database.