Koneru Lakshmaiah Educafion Foundation

(Category -1, Deemed to be University estd. u's. 3of the UGC Act, 1956)
Accredited by NAAC as 'A+*>Approved by AlCTE ISO 21001:2018 Certifled
Campus: Green Fields, Vaddeswaram - 522 302, Guntur Distrlct, Andhra Pradosh, INDIA
Phone No, +91 8645 - 350 200; www.klef.ac.in; www.klef.edu. in; www.kluniversily.in
Admin Oft: 29.36-38, Museum Road, Governorpet, Vjayawada - 520 002. Ph: +91 -866-3500122, 2576129

ACADEMICYEAR 2023-24 - EVEN SEMESTER

22AD2102R - DATABASE MANAGEMENT SYSTEMS

SEM -IN EXAM-1 [QUESTION PAPER SET 1]

TIME: 90 MINUTES MIAX MARKS: 50 M
Q.NO QUESTION DESCRIPTION CO COl-BTL
SECTION-A [ANSWER ALL QUESTIONS])
1 Answer allQuestions (6X 2 = 12 M]
AA Compare and Contrast the File System with DBMS? CO1 BTL-2
CO1 BTL-2
Classify the DBMS according to Environment?
CO1 BTL-2
1,¬ Illustrate about Entity?
1.D Compare and Contrast between Nested and Correlated query? CO2 BTL-2
-1.£ Explain the DML, TCL Commands in SQL with Syntax? C02 BTL-2
Outline the significance of the ORDER BY clause in SQL? C02 BTL-2
1.F
SECTION-B [ANSWER ALLQUESTIONS]
2 Answer allQuestions [4 X4 = 16M)
Explain the difference between external, logical and physical level schemas.
BTL-2
How are these different schema layers related to the concepts of logical and CO1
physical data independence?
Construct the ER Diagram for the following case study? Create a database for
acompany that runs training courses. For this, we must store data about the
trainees and the instructors. For each course participant (about 5,000).
identified by a code, we want to store her soclal secürity number, surname,
age, sex, place of birth, employer's name, address and telephone number,
previous employers (and periods employed), the courses attended (there are
about 200 courses) and the final assessment for each course. We need also
to represent the seminars that each participant is attending at present and,
for each day,the places and times the classes are held. Each course has a code
and atitle and any course can be given any number of times. Each time a CO1 BTL-2
2.B
particular course is given, we will call it an 'edition' of the course. For each
edition, we represent the start date, the end date, and the number of
participants. If a trainee is self employed, we need to know her area of
expertise, and, if appropriate, her title. For somebody who works for a
company, we store the level and position held. For each instructor (about
300), we will show the surname, age, place of birth, the edition of the course
taught, those taught in the past and the courses that the tutor is qualified to
teach. All the instructors' telephone numbers are also stored. An instructor
can be permanently employed by the training company or freelance.
Make use of the following Tables to build the Queries?
1. Develop an SQL query to list the names of employees and their
(2.C) corresponding department names using inner join CO2 BTL-3
2. Develop an SQL query to display all departments and the employees in
each department, including departments without employees using Left Outer
Join.

Page 1 of 4
 Employees
EmplopyeelD FirstName LastName DepartmentlD
1
Alice Johnson 101
2 Bob Smith 102
carol Williams 101

Departments
DepartmentlD DepartmentName
101 HR
102 IT
101
Marketing
Slove the following SQL Queries using below tables (a)
ldentify the
sailors who have reserved a red or a green Boat (b) ldentify the names
of
names
sailors who have reserved at least two boats (c) ldentify the sids of
of
with age over 20 sailors
who have not reserved a red boat.
Sailor
Reserves
sid Sname rating age sid bid day
22 Dustin 7 45.0 22 101 10-10-98
29 Brutas 1 33.0 22 102 10-10-98
Lubber 55.5 22 103 10-8-98
32 Andy 25.5 22 104 10-7-98
58 Rusty 10 35.0 31 102 11-10-98
64 Horatio 35.0 31 103 11-6-98
2.D 71 Zorba 10 16.0 31 104 11-12-98 CO2 BTL-3
74 Horatio 9 35.0 64 101 9-5-98
85 Art 3 25.5 64 102 9-8-98
95 Bob 63.5 74 103 9-8-98

Boats
bid bname color
101 Interlake Blue
102 Interlake Red
103 Clipper Green
104 Marine Red

SECTION -C[ANSWER 03 OR Q4]

3
Answer all Questions [5M + 6M = 11M]
3.A Demonstrate how the strong entity is different from normal entity? CO1 BTL-2
Construct the ER Diagram for the following case study? Create a database for
acompany that runs training courses. For this, we must store data about the
trainees and the instructors. For each course participant (about 5,000),
3.B identified by a code, we want to store her socialsecurity number, surnarne,
CO1 BTL-2
age, sex, place of birth, employer's name, address and telephone number,
previouS employe rs (and periods employed), the courses attended (there are
about 200 courses) and the final assessment for each course. We need also
to represent the seminars that each participant is attending at present and,

Page 2 of 4
 for each day,the places and times the classes are
held.Each course has a code
and a title and any course can be given any number of
times. Each time a
particular course is given, we will call it an 'edition' of the course. For each
edition, we represent the start date, the end date, and the number of
participants. If a trainee is self employed, we need to know her area of
expertise, and, if appropriate, her title. For somebody who works for a
company, we store the level and position held. For each instructor (about
300), we will show the surname, age, place of birth, the edition of the course
taught, those taught in the past and the courses that the tutor is qualified to
teach. All the instructors' telephone numbers are also stored. An instructor
can be permanently employed by the training company or freelance
OR
Answer all Questions (6M +5M =11 M]
Extend the following E-R diagram into relations Schema.
TOW

day
Number

To light Bookings
alrcraft Hlights

CO1 BTL-2
4.A To Cust Seal
Customers

Custl0

name
phone Address

CO1 BTL-2
4.B Explain Data Independence and its types in detail?
SECTION -D [ANSWER Q5 OR Q6)
5 Answer all Questions [6M + 5M = 11 M)
Make use of the Following Worker Table i) Develop the PostgreSQL query to
fetch "FlIRST NAME" from Worker table using the alias name as ii) Develop
the PostgreSQL query to fetch "FIRST_NAME" from Worker table in upper
case
WORK FIRST NA LAST NAM JOINING_D
ME E SALARY ATE DEPARTMENT
ER_ID
Monika Arora 100000 20-02-2014 HR
1

2 Niharika Verma 80000 06-11-2014 Admin

3 Vishal Singhal 300000 20-02-2014 HR CO2 BTL-3

5.A
4 Amitabh singh 500000 20-02-2014 Admin

5 Vivek Bhati 500000 06-11-2014 Admin

6 Vipul Diwan 200000 06-11-2014 Account

7 Satish Kumar 75000 20-01-2014 Account

Geetika Chauhan 90000 11-04-2014 Admin

Page 3 of 4
 Make use of the Following Tables to sove the Queries using joins?
A) ldentify an SQL query to display all departments and the employees in each
department, including employees who are not assigned to any department
using Right Outer Join
8) ldentify SQL query to display al! employees and their corresponding
department names, including employees without departments and
departments without employees using FullOuter Join
Employees
EmplopyeelD FirstName LastName DepartmentiD
1 Alice Johnson 101
5.8 CO2 BTL-3
2 Bob Smith 102
3 carol Williams 101

Departments
DepartmentlD DepartmentName
101 HR
102 IT

101 Marketing

OR
6 Answer all Questions [6M + SM = 11 M)
For the following relation schema:
Employee (employee-name, street, city)
Works (employee-name, company-name,salary)
Company (company-name, city)
6.A
Manages (employee-narne, manager-name) CO2 BTL-3
Give an expression in SQL for each of the following queries:
a) Find the names, street address, and cities of residence for all employees
who work for 'First Bank Coporation' and earn more than $10,000.
b) Find the names of all employees in the database who live in the same cities
as the companies for which they work.
For the Following Database Schema
emp (eno, ename, bdate, title, salary, dno)
proj (pno, pname, budget, dno)
dept (dno, dname, mgreno)
6.B workson (eno, pno, resp, hours) C02 BTL-3
1)Write an SQL query that returns the project number and name for projects
with a budget greater than $100,000.
2) Write an SQL query that returns all works on records where hours worked
is less than 10 and the responsibility is 'Manager'.

*** END OF QUESTION PAPER ***

Page 4 of 4