Q1. Assume that you are setting up a data base for a credit card company and after initial analysis you have come up with the following entity sets, each of which will be a relation in the final database: Customer(C-id, C-Name, C-address,) Primary key C-id
CreditCardAccount(A/C-Number, Exp-Date, Max-Limit, Unpaid-Balance) Primary key A/C-Number
Merchant(M-Id, M-Address, Type-of-business, BBB-Rating).Primary key M-Id
The following facts are to be noted:
1) A customer can have multiple credit card accounts, but a credit card account can only belong to one customer.
2) Whenever a customer charges a purchase, information needs to be maintained so that the data base user can find out the date of the purchase, the amount of purchase, and all available information about the merchant where the purchase was made.
a) Introduce any relationship sets or additional entity sets that you think are necessary to record the above facts and draw the complete E-R (entity-relationship) diagram
b) Provide the relationship schema to go with the above E-R Diagram
Q2.
Let the following Relational schemas be given
R (A, B, C)
S (D, E, F)
r and s are relations defined on R and S, respectively
Provide the equivalent SQL query for each of the following Relational Algebra expressions.
Q3. While examining a Bank Database, one of the relations you find is: Lending_Table (CustomerName, LoanNumber, Amount, BranchName, BranchCity, Assets) Primary Key is CustomerName, LoanNumber
You find out that the following additional dependencies hold: BranchName -> BranchCity, Assets LoanNumber -> Amount, BranchName
This Relation, Lending_Table in obviously not in BCNF. Systematically convert it to BCNF, showing your work .