[Type your answer. Hand-written answer is not acceptable.]

1. (20 pts) Given the following BANKING database, formulate a Relational Algebra expression for each of the following questions. SELECT should be performed before any JOIN operation. Notation: use the symbol Sfor SELECT, P for PROJECT, J for INNER JOIN, * for NATURAL JOIN, LJ for LEFT JOIN, RJ for RIGHT JOIN, R for RENAME, and F for FUNCTION. Type your answer; hand-writing is not accepted.

branch (branch_name, branch_city, assets), PK = branch_name

customer (customer_name, customer_street, customer_city), PK = customer_name loan (loan_number, branch_name, amount), PK = loan_number

borrower (customer_name, loan_number), PK = (customer_name, loan_number) account (account_number, branch_name, balance), PK = account_number depositor (customer_name, account_number), PK = customer_name

1. (10 pts) Find the names of all borrowers who have a loan in branch “Cupertino” and whose loan values are higher than $40,000.

1. (10 pts) Among customers with loans, list each of the customer’s name, the number of loans borrowed by the customer, and the average loan amount of the customer.
2. (40 pts) Map the following ER diagram into a relational database schema.

1. Use any software to draw the schema diagram. For each table, specify the primary key (underline) and, if any, foreign keys (directed arcs, to which column in which table). Anyadditional columns (including surrogate key) are notallowed. Hand-drawing is not accepted.

1. Specify the schema diagram in text (for online exam).

3. (40 pts) Consider the following relation:

CAR_SALE(Car#, Date_sold, Salesperson#, Commission%, Discount_amt)

Assume that a car may be sold by multiple salespeople, and hence (Car#, Salesperson#) is the primary key. Additional functional dependencies (FDs) are

Date_sold ? Discount_amt

Salesperson# ? Commission%

Car# ? Date_sold

1. (15 pts, 5 pts each) Is this relation compliant with primary key based 1NF, 2NF, or 3NF? Justify your answer.

1NF: Yes/No, justify:

2NF: Yes/No, justify with all applicable FDs:

3NF: Yes/No, justify with all applicable FDs:

1. (25 pts) Apply primary key based normalization successively(e.g., n NF then n+1 NF then …) until you reach 3NF or you cannot decompose the relations further. For eachnormalization step, list all the resulting relations, underline the primary key of each relation, and state the reasons behind each decomposition (e.g., remove … problem). Type your answer or use software to draw a diagram; hand-drawing is not accepted.

Submit the following single file

• CMPE138_HW4_YourName (.pdf, .doc, or .docx)

The ISA and/or instructor leave feedback to your homework as comments and/or annotated comment. To access annotated comment, click “view feedback” button. For details, see the following URL:

assignment submission guidelines