Question

Problem: Read the following tables and answer the following questions:

Customers

Customer ID	Name	Address	Phone	Email
9087	John Doe	204 University Ave.	987-098-9087	[email protected]
2098	Bill Lawrence	123 Jones St	717-387-3387	Business
2398	Laura Smith	0900 West Blvd.	901-234-4567	Information

Transactions

Customer ID	Account Number	Date of Last Transaction
9087	375	01/31/98
2098	123	03/09/97
2398	375	09/21/97
2098	375	12/31/97
2398	123	02/01/98

Accounts

Account Number	Balance	Account Type
375	234.45	Checking
123	056.90	Savings

1. Who owns the account with account number 123?

2. Can you insert a row with 3456 Customer ID, 456 as Account Number, and 09/20/97 as Data of Last Transaction into table Transactions? Why or why not?

3. List how many records in Transactiontable corresponds to each record in Customerstable, and how many records inTransactionstable corresponds to each record in Accountstable;

4. What is the mapping cardinality between customers and accounts?

5. Which column(s) is a primary key in each table?

6. Which columns are foreign keys?

Answer 1

1):- Account number 123 is a joint account owned by Bill Lawrence (Customer ID 2098) and Laura Smith (Customer ID 2398).

2):- No, you cannot enter such a row in Transactions table, because there is no customer with ID 3456 in Customers table and no account with number 456 in Accounts table.

3):-

Customerstable record # of corresponding records in

(customer ID) Transactions table. 9087. 1 2098. 2. 2398. 2.

Accountstable record. # of corresponding records (ACC.NO) in Transactions table. 375. 3. 123. 2

4):- There is a many-to-many mapping between customers and accounts. Cardinality on the Customersside is (1, m) and that on the Accountsside is (1, n). Relation table Transactions exhibits this [m: n] mapping between the two entities Customer and Account.

5):- Customers– Customer ID

Accounts– Account Number

Transactions– composite key (Customer ID, Account Number)

6):- Transactions– Customer ID (Customers), Account Number (Accounts).