- 2 Considering The Following Schema Of A Bank Database Branch Branch Name Branch City Assets Customer Customer Nam 1 (177.41 KiB) Viewed 13 times
2. Considering the following schema of a bank database, 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) (a) Identify the candidate keys and the primary key of each relation. (b) Draw a schema diagram for the database. Make sure to underline the primary keys. (c) Considering the schema ve, write relational expressions for the following sentences i. ii. iii. Find all customers from 'Kent' city. Find the names of all borrowers with a loan amount greater than $5000 Find the names of all depositors who have an account with a balance greater than $9,000 at the "Kent" branch. (d) What is Cartesian-product? Explain its importance in the relational data model. [15 points] [15 points] [15 points] 3. List superkeys and candidate keys of the following relation schemas of hospital database, Doctors (DoctorID, email, Name, Mailing Address, Salary) Patient (PatientID, SSN, name, address) VisitingRecords (PatientID, DoctorID, dateOfVisit) 4. Implement 2(c) in mysql and add the screen shot here. Use your own data for the tables. [10 points] [15 points] [15 points]