Assignment 2 (ch. 2)

Joe Lundgren Database Systems Dr. Ankur Agrawal Fall 2017 Assignment 2 (ch. 2) 2.9 Consider the bank database of Figure 2.15. a. What are the appropriate primary keys? Primary keys are underlined. branch(branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) loan (loan_number, branch_name, amount) borrower (customer_name, loan_number) account (account_number, branch_name, balance) depositor (customer_name, account_number) b. Given your choice of primary keys, identify appropriate foreign keys. Schema Foreign Key/s Depositor customer_name references customer account_number references account Borrower customer_name references customer loan_number references loan Loan branch_name references branch Account branch_name references branch 2.10 Consider the advisor relation shown in Figure 2.8, with s_id as the primary key of advisor . Suppose a student can have more than one advisor. Then, would s_id still be a primary key of the advisor relation? If not, what should the primary key of advisor be? Assuming s_id held the value of only one advisor, making it the primary key would not uniquely identify a tuple. Therefore, the primary key should be s_id and i_id in the case of two advisors. 2.12 Consider the relational database of Figure 2.14. Give an expression in the relational algebra to express each of the following queries: a. Find the names of all employees who work for “ First Bank Corporation ” . Π (person_name) ( σ company_name = “ First Bank Corporation ” (works)) b. Find the names and cities of residence of all employees who work for “ First Bank Corporation ” . Π (person_name.city) (employee ⋈ ( σ company_name = “ First Bank Corporation ” (works)) c. Find the names, street address, and cities of residence of all employees who work for “ First Bank Corporation ” and earn more than $10,000.