Institute of Business & Information Technology
Faculty of Business, Economics & Administrative Sciences
University of the Punjab
Quaid-e-Azam Campus, Lahore
Sheet No.
Assignment 03
Relational Algebra, SQL, & Functional Dependencies
Spring Term 2022
Code: IT-261 Degree: BBIT
Title: Relational Database Management System Batch: F20 MB & AB
Instructor: Mr. Saleem Raza Date: May 17, 2022
Due Date: Tuesday May 24, 2022, before 1130a.m. Marks: 95
Instructions:
1. Do not forget to pray before starting to attempt the paper. Trust me it helps.
Remember! SOMEONE is always with you (Be Relaxed), and HE is also watching you (Be Honest)
2. Print this Assignment on A4/Letter Size otherwise Assignment will NOT be Accepted/Evaluated.
3. Questions are SELF EXPLANATORY. Understanding the Question Paper is part of Solution.
4. Nothing Beyond the Finish Line will be Evaluated. Back Side of Pages is Beyond Finish Line.
5. For Calculations etc. Use the back side of the pages.
6. Solve your Assignment using Black/Blue Pen ONLY.
7. Error in Question will be advantageous to Student.
8. Read the Questions carefully before attempting.
9. Copied/Shared Work will be awarded ZERO.
10. Attempt All Questions in a Precise Fashion.
Good Luck
Roll No.: Name:
Class: MA AA MB AB Signature:
Q 1. Q 2. Q 3. Q 4. Q 5. Q 6. Q 7. Q 8. Q 9. Q 10. Total
15 10 10 30 10 20 95
Invigilator’s Signature Examiner’s Signature
Roll No.: A03-F20-BBIT MB & AB
Name: Relational Database Management System
Question No 01. 15
Compute the Candidate Keys and Primary Key for the relations with the given set of FDs.
R(A, B, C, D, E) with F={AB→C, CD→E, C→AD, D→B}
R(A, B, C, D, E) with F={AB→C, C→A, C→BD, D→E}
R(A, B, C, D, E, G, J) with F={A→B, AB→E, BG→E, CD→J, E→C}
May 2022 Page 2 of 8
Roll No.: A03-F20-BBIT MB & AB
Name: Relational Database Management System
Question No 02. 10
Compute the Minimal Cover of Following? Clearly mention the Inference Rule used. 10
R(A, B, C, D, E)
𝐹 = {𝐴𝐵 → 𝐶𝐸, 𝐷 → 𝐴𝐶, 𝐴𝐶 → 𝐵, 𝐶 → 𝐸}
May 2022 Page 3 of 8
Roll No.: A03-F20-BBIT MB & AB
Name: Relational Database Management System
Question No 03. 10
Consider relation 𝑅(𝐴, 𝐵, 𝐶, 𝐷, 𝐸, 𝐹, 𝐺, 𝐻) with 𝐹𝐷 = {𝐴 → 𝐵, 𝐴𝐵𝐶𝐷 → 𝐸, 𝐸𝐹 → 𝐺𝐻, 𝐴𝐶𝐷𝐹 → 𝐸𝐺}. Find the
Minimal Cover for R.
May 2022 Page 4 of 8
Roll No.: A03-F20-BBIT MB & AB
Name: Relational Database Management System
Question No 04. 30
Following is the Relational Schema of Course Registration Systems of an institute
Degree(DCode, Title, DCH, Sem)
Students(RegNo, Name, Gender, DoB, City, DCode)
Courses(CCode, CTitle, CH, Sem, DCode)
Faculty(FCode, Name, Designation, eMail, City)
Term(TCode, Description, MidWeek, FinalWeek)
CRS(RegNo, CCode, TCode, OM, Grade)
Teach(FCode, CCode, TCode, Section, FB, NoS) // FB-> Feedback, NoS-? No. of Students
Write the Relational Algebra and SQL Statements for followings:
i. List the Courses which are not offered in term ‘S15’ but were offered in ‘S14’ 5
ii. List the Courses which are not taught by any Faculty in ‘S15’ 5
iii. List Faculty who has taught ‘BF-357’ and ‘EC-123’ but have NEVER taught ‘CS-137’ 5
May 2022 Page 5 of 8
Roll No.: A03-F20-BBIT MB & AB
Name: Relational Database Management System
iv. List the Course which are being taught by ‘Lecturers’ during term ‘S17’ which were taught by ‘Professor’
during term ‘S15’. 5
v. List the Students Name with Course Titles of students who are enrolled only in those courses during
term ‘S17’ in which students have secured ‘A’ in term ‘S15’. 5
vi. List the Students who are enrolled in courses offered in even semesters of the ‘BBIT’ 5
May 2022 Page 6 of 8
Roll No.: A03-F20-BBIT MB & AB
Name: Relational Database Management System
Question No 05. 10
Consider the following database instance
Teachers IS-QUALIFIED SECTION
TCNIC TName TCNIC CCOde Qualify SCode CCode
4210 Imran 4240 CS104 8/96 2912 CS201
4220 Shoaib 4220 CS201 8/95 2913 CS201
4230 Qadeer 4220 CS402 8/95 2914 CS204
4240 Sara 4210 CS201 8/96 2915 CS402
4210 CS204 8/96 2916 CS303
4230 CS204 8/95
STUDENT COURSE IS-REGISTERED
RegNo StdName CCode CTitle PreReq RegNo SCode Term
214 Alia CS104 C Programming NULL 214 2914 SP09
107 Tahreem CS201 Data Structures CS104 107 2914 SP09
324 Isbah CS204 Database Systems CS201 107 2915 SP09
542 Izaan CS402 Compiler Design CS201 324 2913 SP09
410 Fatima CS303 Software Engineering CS204 214 2916 F09
542 2914 F09
Write the following queries in SQL Statement for following.
i. Retrieve the names of all students, which were not enrolled in any courses during semester ‘SP09’ 5
Sample Output of query for the given state database:
StdName
Izaan
Fatima
ii. Retrieve the Course Title, Pre-Req Course, and Qualified Faculty Name for all courses. 5
Sample Output of query for the above given state database:
Course Title Pre Requisite Course Qualified Faculty Name
C Programming NULL Sara
Data Structures C Programming Shoaib
Data Structures C Programming Imran
Database Systems Data Structures Imran
Database Systems Data Structures Qadeer
Compiler Construction Data Structures Shoaib
Software Engineering Database Systems NULL
May 2022 Page 7 of 8
Roll No.: A03-F20-BBIT MB & AB
Name: Relational Database Management System
Question No 06. 20
Consider the relation schemas as follows.
Works(pName, cTitle, salary);
Lives(pName, Street, pCity);
Company(cTitle, cCity);
managers(pName, mName); where mName refers to Works(pName).
a. Find the names of the persons who live and work in the same city. 4
b. Find the persons whose salaries are more than the salary of everybody working with company ’SBC’. 4
c. Find the names of the companies which is located-in every city where company ’SBC’ is located-in. 4
d. List names of person along with company who lives in different city than the city of their company 4
e. List names of persons, Company City and Salary, who are 2 managerial levels below the person ‘Ali’ 4
Finish Line
Nothing beyond this line will be evaluated
May 2022 Page 8 of 8