[Type your answer. Hand-written answer is not acceptable.]
(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 S for
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),
(10 pts) Find the names of all borrowers who have a
loan in branch “Cupertino” and whose loan values are higher than $40,000.
(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.
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). Any additional columns (including surrogate key)
are not allowed. Hand-drawing is not accepted.
b. Specify the schema diagram in text (for online
3. (40 pts) Consider the following relation:
Salesperson#, Commission%, Discount_amt)
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
a. (15 pts, 5 pts each) Is this relation compliant
with primary key based 1NF, 2NF, or 3NF? Justify your answer.
1NF: Yes/No, justify:
justify with all applicable FDs:
3NF: Yes/No, justify with all
b. (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 each normalization 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
.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