Consider the following relational database

Question 1

Question 2

Consider the following relational schema:

Emp (eid, did, sal, hobby)

Dept (did, dname, floor, phone)

Finance (did, budget, sales, expenses)

Consider the following query:

SELECT d.dname, f.budget

FROM emp e, dept d, finance f

WHERE e.did = d.did

AND d.did = f.did AND d.floor = 10

AND e.sal >= 10,000 AND e.hobby = ‘swimming’

Draw an initial relational algebra tree using Cartesian product, selection, and projection. Identify a relational algebra tree that optimizes the SQL query using a heuristic approach.

Question 3

Answer to question 1

step 1

step 2

Answer to question 2

In this question first, do the natural join with tables Dept and Finance check the conditions 1. d.did=f.did 2.d.floor=10.

then join with the EMP table and check the conditions 1.e.hobby=swimming 2.e.sal>=10,000 then check e.did=d.did

if equals then only selects the and f.budget.

Answer to question 3

Create Author Table:

CREATE TABLE `author` (  

  `author_id` INT NOT NULL,

  `author_name` VARCHAR(45) NOT NULL,

  `country` VARCHAR(45) NOT NULL,

  PRIMARY KEY (`author_id`));

Create author table screenshot:

Create Books Table: 

CREATE TABLE `books` (

  `book_no` INT NOT NULL,

  `book_title` VARCHAR(45) NOT NULL,

  `isbn` INT(13) NOT NULL,

  `author_id` INT NULL,

  PRIMARY KEY (`book_no`),

  FOREIGN KEY(`author_id`) References `author`(`author_id`));

Create Books Table screenshot

Leave a Comment