Question Bank with answers Module 2

PART B
Consider the following relations for a database that keeps track of business trips of
salespersons in a sales office: SALESPERSON(Ssn, Name, StartYear, DeptNo)
TRIP(Ssn, FromCity, ToCity, DepartureDate, ReturnDate, TripId)
EXPENSE(TripId, AccountNo, Amount)
i) A trip can be charged to one or more accounts. Specify the foreign keys for this
schema, stating any assumptions you make.
ii) Write relation algebra expression to get the details of salespersons who have
travelled between Mumbai and Delhi and the travel expense is greater that Rs. 50000.
iii) Write relation algebra expression to get the details of salesperson who had incurred
the greatest travel expenses among all travels made.
iv) List the salespersons who have not been on any trips.
v) Find the total number of trips made by each salesperson.

Answer

The foreign keys in this schema are designed to connect related data across the tables:
● In the TRIP table, Ssn is a foreign key that references Ssn in the SALESPERSON table.
This creates a link between a trip and the salesperson who made it.
● In the EXPENSE table, TripId is a foreign key that references TripId in the TRIP table.
This links each expense to a specific trip.

● A salesperson can have zero or more trips.

● A trip can have one or more expenses associated with it.
● Each trip is uniquely identified by a TripId.
ii) To get the details of salespersons who have traveled between Mumbai and Delhi with
expenses greater than Rs. 50000, you can use the following relational algebra expression:
π_Ssn, Name, StartYear, DeptNo(σ_FromCity='Mumbai' ∧ ToCity='Delhi' ∧ Amount
> 50000(SALESPERSON ⨝ TRIP ⨝ EXPENSE))

iii) To find the salesperson who has incurred the greatest travel expenses among all travels
made, we first need to sum the expenses by salesperson and then find the maximum. This can
be complex in pure relational algebra without extensions for aggregation, but conceptually,
the steps involve:
 iv) To list the salespersons who have not been on any trips, you can use a set difference
operation:
AGG_COUNT(TRIP; Ssn) -> COUNT_TRIPS

Q. Consider the following schema,

Suppliers (sid , sname, address)
Parts (pid, pname, color)
Catalog (sid, pid, cost)
The primary key fields are underlined. Write relational algebra expressions for the following
queries: b) Find the name of parts supplied by supplier with sid=105
ii) Find the names of suppliers supplying some green part for less than Rs 1000
iii) Find the IDs of suppliers who supply some red or green part
iv) Find the names of suppliers who supply some red part

ANSWER:-

The basic operations:-

selection (σ), projection (π), join (⨝), union (∪).
part names (pname) supplied by the supplier with sid=105.
This requires a join between the Suppliers and Catalog tables to identify which parts are
supplied by sid=105, followed by another join with the Parts table to get the names of those
parts.
π_pname(σ_sid=105(Catalog) ⨝_Catalog.pid=Parts.pid Parts)

Consider the relational database shown below:

employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)
Define the integrity constraints in the given relation. State the assumptions used.

1.
Entity Integrity Constraint: This ensures that the primary key attribute in each relation is
not NULL.
For the employee relation: person-name should be unique and not NULL.
For the works relation: Both person-name and company-name together should be
unique and not NULL.
For the company relation: company-name should be unique and not NULL.
For the manages relation: Both person-name and manager-name together should be
unique and not NULL.
Referential Integrity Constraint: This ensures that foreign key values in a relation reference
an existing tuple in another relation.
In the works relation, person-name should exist in the employee relation, and
company-name should exist in the company relation.
In the manages relation, both person-name and manager-name should exist in the
employee relation.
Attribute Domain Constraint: This ensures that each attribute value falls within a
specified domain.
Assuming that all attributes (person-name, street, city, company-name, salary,
manager-name) are strings, this constraint ensures that no attribute value exceeds a certain
length and follows a specific format. For instance, salary should be numerical.
Explain the SELECT and PROJECT operations in relational algebra with examples.
SELECT Operation:
● The SELECT operation is used to retrieve tuples from a relation that satisfy a
specified condition.
● It filters rows based on a given predicate.
● The resulting relation contains only the tuples that meet the specified criteria.
Syntax: σ<condition>(relation)
Example: Consider a relation named employee with attributes (employee_id, name, age,
department). Let's say we want to retrieve the names of employees who belong to the "Sales"
department.
If employee relation looks like this:
EMPLOYEE ID NAME AGE DEPARTMENT
1. ALICE 30 SALES
2. BOB 35 MARKETING
3. CHARLIE 28 SALES
4. DAVID 40 HR
The SELECT operation to retrieve the names of employees in the "Sales" department
σ(department = 'Sales')(employee)

EMPLOYEE ID NAME AGE DEPARTMENT

1 ALICE 30 SALES
2 CHARLIE 28 SALES

PROJECT Operation:
● The PROJECT operation is used to select a subset of attributes from a relation.
● It returns a relation with only the specified attributes, eliminating duplicates.
Syntax: π<attribute list>(relation)
Example: Continuing with the employee relation example, let's say we want to retrieve only
the names and ages of employees.
The PROJECT operation to retrieve names and ages would be:
π(name, age)(employee)

NAME AGE
ALICE 30
BOB 35
CHARLIE 28
DAVID 40

Study the tables given below and write relational algebra expressions for the queries that
follow. STUDENT(ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR)
COURSE(COURSEID, CNAME, CREDITS) PROFESSOR(PROFID,PNAME, PHONE)
ENROLLMENT(ROLLNO, COURSEID, GRADE) Primary keys are underlined. ADVISOR
is a foreign key referring to PROFESSOR table. ROLLNO and COURSEID in
ENROLLMENT are also foreign keys referring to the primary keys with the same name.
(i) Names of female students.
(ii) Names of male students along with adviser name.
(iii) Roll Number and name of students who have not enrolled for any course.
iv) For each course, the number of students enrolled in the course.
 STUDENT(ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR)

COURSE(COURSEID, CNAME, CREDITS)

PROFESSOR(PROFID, PNAME, PHONE)

ENROLLMENT(ROLLNO, COURSEID, GRADE)

primary keys as underlined and foreign keys with an asterisk (*).

(i) Names of female students: π_NAME(σ_GENDER='F'(STUDENT))

(ii) Names of male students along with adviser name:

π_NAME, ADVISOR_NAME((σ_GENDER='M'(STUDENT)) ⨝ PROFESSOR)
(iv) Roll Number and name of students who have not enrolled for any course:
π_ROLLNO, NAME((STUDENT) - (ENROLLMENT ⨝ STUDENT))
(v) For each course, the number of students enrolled in the course:
π_COURSEID, COUNT(ENROLLMENT) (ENROLLMENT ⨝ COURSE)

Consider the following schema,

Suppliers (sid , sname, address)
Parts (pid, pname, color)
Catalog (sid, pid, cost)
The primary key fields are underlined. Write relational algebra expressions for the following
queries:
b) Find the name of parts supplied by supplier with sid=105
ii) Find the names of suppliers supplying some green part for less than Rs 1000
iii) Find the IDs of suppliers who supply some red or green part
iv) Find the names of suppliers who supply some red part

● Suppliers (sid, sname, address)

● Parts (pid, pname, color)
● Catalog (sid, pid, cost)
● Primary keys are underlined.

b) Find the name of parts supplied by the supplier with sid=105:

π_pname((σ_sid=105(Catalog)) ⨝ Parts)

ii) Find the names of suppliers supplying some green part for less than Rs 1000:

π_sname((σ_color='green' ∧ cost < 1000 (Catalog)) ⨝ Suppliers)

iii) Find the IDs of suppliers who supply some red or green part:

π_sid((σ_color='red' ∨ color='green' (Parts)) ⨝ Catalog)

iv) Find the names of suppliers who supply some red part:

π_sname((σ_color='red' (Parts)) ⨝ Catalog ⨝ Suppliers)

Consider the UNIVERSITY database with the following relations:

 STUDENT (rollNo, name, degree, year, sex, deptNo, advisor)

DEPARTMENT (deptId, name, hod, phone)

PROFESSOR (empId, name, sex, startYear, deptNo, phone)

COURSE (courseId, cname, credits, deptNo)

ENROLLMENT (rollNo, courseId, sem, year, grade)

TEACHING (empId, courseId, sem, year, classRoom)

PREREQUISITE(preReqCourse, courseID) Write relational algebra expressions for the

following queries:

i. For each department, find its name and the name, sex and phone number of the head
of the department.

ii. Find courses offered by each department.

iii. Find those students who have registered for all courses offered in the department of
Computer Science.

iv. Obtain the department Ids for departments with no lady professor.

v. Obtain the rollNo of girl students who have obtained at least one S grade

● STUDENT (rollNo, name, degree, year, sex, deptNo, advisor)

● DEPARTMENT (deptId, name, hod, phone)
● PROFESSOR (empId, name, sex, startYear, deptNo, phone)
● COURSE (courseId, cname, credits, deptNo)
● ENROLLMENT (rollNo, courseId, sem, year, grade)
● TEACHING (empId, courseId, sem, year, classRoom)
● PREREQUISITE (preReqCourse, courseId)

i. For each department, find its name and the name, sex, and phone number of the head of the
department:

π_name, hod, sex, phone((DEPARTMENT ⨝ PROFESSOR) ÷ {deptId})

ii. Find courses offered by each department:

π_deptId, cname((COURSE ⨝ DEPARTMENT) ÷ {deptId})

iii. Find those students who have registered for all courses offered in the department of
Computer Science:

π_rollNo(ENROLLMENT ÷ {rollNo} - σ_deptNo='Computer Science'(COURSE) ⨝

ENROLLMENT)

iv. Obtain the department Ids for departments with no lady professor:

π_deptId(σ_sex ≠ 'female'(PROFESSOR) ⨝ DEPARTMENT)

 vi. Obtain the rollNo of girl students who have obtained at least one S grade:

π_rollNo(σ_sex='female'(STUDENT) ⨝ σ_grade='S'(ENROLLMENT))

π denotes the PROJECT operation.

σ denotes the SELECT operation.
⨝ denotes the JOIN operation.
÷ denotes the SET DIFFERENCE operation.

The relational schema for a library describing members, books and issue information is given
below. Foreign keys have the same name as primary keys.
BOOKS(ACC-NO,ISBN, TITLE, EDITION, YEAR)
MEMBERS(MEMBERID, MEMBERNAME, MEMBERTYPE)
ISSUEDTO(ACC-NO,MEMBERID, DATE OF ISSUE)
Write relational algebra expressions for the following queries:-
i) Accession Number(s) and Name(s) of third edition books published in 2018.
ii) Names and date of issue of books taken by a member with name ‘PRIYA’.
iii) Names of books not taken by any member.
iv)List the names and types of members who have borrowed books.
v) Find the names of members who have not borrowed any books.
BOOKS(ACC-NO, ISBN, TITLE, EDITION, YEAR)
MEMBERS(MEMBERID, MEMBERNAME, MEMBERTYPE)
ISSUEDTO(ACC-NO, MEMBERID, DATE OF ISSUE)
i) Accession Number(s) and Name(s) of third edition books published in 2018:

π_ACC-NO, TITLE(σ_EDITION=3 ∧ YEAR=2018(BOOKS))

ii) Names and date of issue of books taken by a member with name 'PRIYA':

π_TITLE, DATE_OF_ISSUE((σ_MEMBERNAME='PRIYA'(MEMBERS) ⨝ ISSUEDTO) ⨝

BOOKS)

iii) Names of books not taken by any member:

π_TITLE(BOOKS - (ISSUEDTO ⨝ BOOKS))

iv) List the names and types of members who have borrowed books:

π_MEMBERNAME, MEMBERTYPE(MEMBERS ⨝ ISSUEDTO)

vi) Find the names of members who have not borrowed any books:

π_MEMBERNAME(MEMBERS - (MEMBERS ⨝ ISSUEDTO))

Write SQL DDL statements based on the following database schema (Assume suitable
domain types): Room (room_number, type, price, availability) Guest (guest_id, name,
contact, nationality) Reservation (reservation_id, guest_id, room_number, check_in_date,
check_out_date). i) Create table Room, Guest, Reservation including primary and foreign key
integrity constraints. ii) Insert values into both the tables. Mention in which order insertions
will be carried out. iii)Add a new column "special_requests" to the Guest table. iv) Increase
the room prices by 15% for rooms of type "Suite."

i) Create table Room, Guest, Reservation including primary and foreign key integrity
constraints:

CREATE TABLE Room ( room_number INT PRIMARY KEY, type VARCHAR(50), price
DECIMAL(10, 2), availability BOOLEAN ); -- Create Guest table CREATE TABLE Guest (
guest_id INT PRIMARY KEY,name VARCHAR(100), contact VARCHAR(20), nationality
VARCHAR(50) ); -- Create Reservation table CREATE TABLE Reservation ( reservation_id
INT PRIMARY KEY, guest_id INT, room_number INT, check_in_date DATE,
check_out_date DATE, FOREIGN KEY (guest_id) REFERENCES Guest(guest_id),
FOREIGN KEY (room_number) REFERENCES Room(room_number) );

ii) Insert values into both tables. Insertions should be carried out in the following order:
Guest, Room, Reservation.

INSERT INTO Guest (guest_id, name, contact, nationality) VALUES (1, 'John Doe',
'1234567890', 'USA'), (2, 'Jane Smith', '9876543210', 'UK'), (3, 'Alice Brown', '4567890123',
'Canada');

INSERT INTO Room (room_number, type, price, availability) VALUES (101, 'Standard',
100.00, TRUE), (102, 'Suite', 150.00, TRUE), (103, 'Standard', 110.00, FALSE); -- Insert
values into Reservation table INSERT INTO Reservation (reservation_id, guest_id,
room_number, check_in_date, check_out_date) VALUES (1, 1, 101, '2024-03-17',
'2024-03-20'), (2, 2, 102, '2024-04-01', '2024-04-05'), (3, 3, 103, '2024-05-10', '2024-05-15');

iii) Add a new column "special_requests" to the Guest table:

ALTER TABLE Guest ADD COLUMN special_requests VARCHAR(255);

iv) Increase the room prices by 15% for rooms of type "Suite":

UPDATE Room SET price = price * 1.15 WHERE type = 'Suite';

Write SQL DDL statements based on the following database schema (Assume suitable
domain types): Employee (eid, name, designation, salary, comp_id) Company (comp_id,
cname, address, turnover) b) Create the above mentioned tables assuming each company has
many employees. Mention the primary key, foreign key and not null constraints. ii) Insert
values into both the tables. Mention in which order insertions will be carried out. ii) Modify
the table Employee to include a new column “years_of_exp”. iv) Increment the salary of
employees whose salary is less than Rs25000 by 5%

CREATE TABLE Company ( comp_id INT PRIMARY KEY, cname VARCHAR(100) NOT
NULL, address VARCHAR(255), turnover DECIMAL(15, 2) ); -- Create Employee table
CREATE TABLE Employee ( eid INT PRIMARY KEY, name VARCHAR(100) NOT
NULL, designation VARCHAR(50), salary DECIMAL(10, 2) NOT NULL, comp_id INT,
years_of_exp INT, FOREIGN KEY (comp_id) REFERENCES Company(comp_id) );

ii) Insert values into both tables. Insertions should be carried out in the following order:
Company, Employee.
-- Insert values into Company table INSERT INTO Company (comp_id, cname, address,
turnover) VALUES (1, 'ABC Corp', '123 Main St', 1000000.00), (2, 'XYZ Inc', '456 Elm St',
2000000.00), (3, 'LMN Ltd', '789 Oak St', 1500000.00); -- Insert values into Employee table
INSERT INTO Employee (eid, name, designation, salary, comp_id, years_of_exp) VALUES
(101, 'John Doe', 'Manager', 30000.00, 1, 5), (102, 'Jane Smith', 'Developer', 25000.00, 2, 3),
(103, 'Alice Brown', 'Analyst', 28000.00, 3, 4);
iii) Modify the table Employee to include a new column "years_of_exp":
ALTER TABLE Employee ADD COLUMN years_of_exp INT;
iv) Increment the salary of employees whose salary is less than Rs25000 by 5%:
UPDATE Employee SET salary = salary * 1.05 WHERE salary < 25000.00;