SJSU CMPE 138/180B HW4 SPRING 2020

[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 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), PK = customer_name


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

 

b.      (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.

 

a. 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 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

 

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:

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

 

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

 

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

 

       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:

 

https://guides.instructure.com/m/4212/l/352349-how-do-i-view-annotation-feedback-comments-from-my-instructor-directly-in-my-assignment-submission


Answer Preview

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

1NF: Yes/No, justify:

-Yes

Justification:

By default, all relations are in 1NF because a relation cannot contain multi-valued attributes. In 1NF, every tuple in the relation contains exactly one value for each attribute.Therefore, it is correct to say that the relation CAR_SALE is in 1NF.

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

-No

Justification:

These are the conditions for 2NF:

  • The relation should first be in 1st normal form (1NF).
  • There exists no partial functional dependency on attributes

This is only a preview of the solution. Please use the purchase button to see the entire solution