i. Q4Convert the following ER-Model of Banks to relational database schema. Identify and mark all the...

Transcribed Image Text:

i. Q4Convert the following ER-Model of Banks to relational database schema. Identify and mark all the primary keys and foreign keys. (8) ii. iii. iv. V. Name Code Address Bank Loan id Loan tyne Amount ER Diagram of a Bank Has Offer Loan Branch id Availed by Custid Branch N Q5: Consider the following Database schema: lives(person-name, street, city) works(person-name, company-name, salary) located-in(company-name, city) manages(person-name, manager-name)) Name N Maintain> N Name M Account Customer. Address Hold by Phone 000 Address Account No Acc Type Balance) For the above schema (the primary key for each relation is denoted by the underlined attribute), provide relational algebra expressions and SQL for the following queries: (10) Find all tuples in works of all persons who work for the City Bank company (which is a specific company in the database). Find the average salary given by each company. Find the name and city of all persons who work for City Bank and earn more than 50,000. Find names of all persons who live in the same city as the company they work for. Find names of all persons who do not work for City Bank. 2 to 2 -25 x 100 = 200 7 i. Q4Convert the following ER-Model of Banks to relational database schema. Identify and mark all the primary keys and foreign keys. (8) ii. iii. iv. V. Name Code Address Bank Loan id Loan tyne Amount ER Diagram of a Bank Has Offer Loan Branch id Availed by Custid Branch N Q5: Consider the following Database schema: lives(person-name, street, city) works(person-name, company-name, salary) located-in(company-name, city) manages(person-name, manager-name)) Name N Maintain> N Name M Account Customer. Address Hold by Phone 000 Address Account No Acc Type Balance) For the above schema (the primary key for each relation is denoted by the underlined attribute), provide relational algebra expressions and SQL for the following queries: (10) Find all tuples in works of all persons who work for the City Bank company (which is a specific company in the database). Find the average salary given by each company. Find the name and city of all persons who work for City Bank and earn more than 50,000. Find names of all persons who live in the same city as the company they work for. Find names of all persons who do not work for City Bank. 2 to 2 -25 x 100 = 200 7

Answer rating: 100% (QA)

Solutions Step 1 Q4 Convert the following ER model of Banks to relational database schema Identify and mark all the primary keys and foreign keys These steps are used to convert the ER diagram of bank... View the full answer