IQAC

DEPARTMENT

PART B (MODULE III) 1* 2* 3* 4*

15 Marks Questions Marks Course Knowledge Level Theory(T)/ Difficulty Lev

Each question can have maximum four sub division Outcome Problem(P)/ (S/A/D/T)
Design(D)
(Prepare maximum Questions possible, covering all areas of the modules assigned )
1 (a) ()
 IQAC

2 ()

3 ()

4
Suppose that we have an ordered file with 400,000 records stored on a disk with

block size 4,096 bytes. File records are of fixed size and are unspanned,with record

length 200 bytes. How many blocks are needed for the file? Approximately, how

many block accesses are required for a binary search in this file? On an average, how

many block accesses are required for a linear search, if the file is nonordered

Based on question 15.a, give an example to illustrate that indexing can improve the

search time.

5
Consider a file with 450000 records . Each record size is 125 bytes and block is 1000

bytes. The primary key of the file is 10 bytes and record pointer size is 6 bytes.

1). Calculate number of index block required in case of primary indexing

 IQAC

2) Calculate number of index blocks required in case of multilevel indexing

6
Suppose that we have an ordered file with r=30000 records stored on a disk with block

size B =1024 bytes. File records are of fixed length and are un-spanned with record length

R =100 bytes. Assume that the file is ordered on the attribute V of length 9 bytes and the

block pointer length P= 6 bytes. Compute the number of block access for the file
i. Binary search (no index)

ii. Search a record using Primary index

Discuss the major issues associated with primary indexing

7
There are 12000 records in a data file. Each record in the file is of 75 bytes.

Compute the number of block accesses if (i) Single level secondary index is

available on a field of size 15 bytes. (ii) Multilevel index is available on the same
field.

Assume that the block size is 394 bytes, that un-spanned organization is used and

that block and record pointers are 5 and 7 bytes, respectively

8
Define the following: (i) physical record (ii) logical record (iii) blocking factor
9 ()
With the help of an example, illustrate the use of SQL TRIGGER
10 ()
List the basic data types available for defining attributes in SQL
11 ()
With suitable example, list aggregate functions in sql
12 ()
What is the importance of views in sql? Explain with suitable example
 IQAC

13 ()
Differentiate DDL and DML with suitable example.
14 ()
Demonstrate the working of GROUP BY clause in SQL

15 ()
Consider the following schema and write SQL queries to find:

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)

PREREQ(preCourseId, courseId)

i. Get the employee Id, name and phone number of professors in the CS dept (deptNo= 3)

who have joined after 1999.

ii. Get the rollNo, name of students in the CSE dept (deptNo= 3) along with their advisor’s

name and phone number.

iii. Get the rollNo, name of students who have a lady professor as their advisor.
iv. Get the roll number and name of students whose gender is same as their advisor’s.

16 ()
How view is different from a table in SQL? Give the syntax of view declaration and
 IQAC

illustrate the use with an example

17 ()
Consider the schema given below.

person (driver-id, name, address)

car (reg-no, model, year, driver-id)

accident (report-number, date, location)

participated (driver-id, reg-no, report-number, damage-amount)

Write SQL queries for the following

a. Find the name of driver, who is drives the car with reg-no='AABB2000'. Find the total

number of people who were involved in car accidents in 01-01-1989.

c. Find the number of accidents in which the cars belonging to “John Smith” were
involved.

d. Update the damage amount for the car with reg-no “AABB2000”in the accident with

report number “AR2197” to $3000

18
Consider the following relations for bank database (Primary keys are underlined):

Customer (customer-name, customer-street, customer-city)

Branch (branch-name, branch-city, assets)

Account (account-number, branch-name, balance)

Depositor (customer-name, account-number)

Loan (loan-number, branch-name, amount)

Answer the following in SQL:

 IQAC

i) Create tables with primary keys and foreign keys

ii) Create an assertion for the sum of all loan amounts for each branch must

be less than the sum of all account balances at the branch

19
In the following tables ADVISOR and TAUGHTBYare foreign keyd referring to the

table PROFESSOR. ROLLNO and COURSEID in ENROLLMENT refer to tables with

primary keys of the same name.

STUDENT(ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR)

COURSE(COURSEID, CNAME, TAUGHTBY, CREDITS)

PROFESSOR(PROFID,PNAME, PHONE)

ENROLLMENT(ROLLNO, COURSEID, GRADE)

Write SQL expressions for the following queries:

(i) Names of courses taught by ‘Prof. Raju’.

(ii) Names of students who have not enrolled for any course taught by ‘Prof.

Ganapathy’.

(iii) For each course, name of the course and number of students enrolled for the

course.
20
Consider the following relations:

FACULTY(FNO, NAME, GENDER, AGE, SALARY, DNUM)

DEPARTMENT(DNO, DNAME, DPHONE)

COURSE(CNO, CNAME, CREDITS, ODNO)

TEACHING(FNO, CNO, SEMESTER)

 IQAC

DNUM is a foreign key that identifies the department to which a faculty

belongs. ODNO is a foreign key identifying the department that offers a

course.

Write SQL expressions for the following queries:

a) Course numbers and names of 3-credit courses offered by ‘CS’ department

b) Names of faculty members teaching maximum3 courses

c) Names of departments along with number of courses offered by each of them, in

the increasing order of number of courses; exclude departments which do not offer

any course

21
Consider two tables STUDENT(
ENROLLMENT(ROLLNO,COURSENAME

ENROLLMENT is a foreign key referring to

every time a STUDENT tuple is deleted, all the ENROLLMENT tuples referring to the

deleted STUDENT tuple are also deleted. Write SQL statements to

specify this foreign key requirement

Consider a scenario where movie can have manyartists. Assuming suitable attributes be
represented using relations with foreign keys. (A relational schema showing primary and

foreign keys is sufficient. Minimal number of attributes is required)

 IQAC

22 ()
Compare primary indexing, secondary indexing and clustered indexing with suitable

diagram.

23 ()
Define the structure of B+ tree
24 ()
What is multi-level index? When do you prefer multilevel index over single level index?
25 ()
What is a corelated subquery in SQL? Give example
26 ()
Write SQL DDL commands to construct the 'Catalog' table in the following relations
Suppliers(sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

Include the primary key and referential integrity constraints in the table.
27 ()
How is DML different from DDL? Write a sample statement in DML and one in DDL
28
Consider the query SELECT NAME, AGE FROM STUDENT WHERE GENDER =

‘Male’ on the table STUDENT(ROLLNO, NAME, AGE, GENDER, ADDRESS). Give a

relational algebra expression corresponding to the query. Is result produced by the

query and your expression always the same? Why?

29 ()
Illustrate use of assertions with an example
30 ()
Given two tables STUDENT(

ENROLLMENT(ROLLNO,COURSENAME

refers to STUDENT, what does the following SQL statement return?

 IQAC

SELECT COURSENAME FROM ENROLLMENT WHERE ROLLNO = ALL

(SELECT ROLLNO FROM STUDENT)

31 ()
Define super key and minimal super key and illustrate using good examples
32 ()
Consider the following relations:

FACULTY(FNO, NAME, GENDER, AGE, SALARY, DNUM)

DEPARTMENT(DNO, DNAME, DPHONE)

COURSE(CNO, CNAME, CREDITS, ODNO)

TEACHING(FNO, CNO, SEMESTER)

DNUM is a foreign key that identifies the department to which a faculty belongs.

ODNO is a foreign key identifying the department that offers a course.Write SQL

expressions for the following queries:

(a) Names and department names of faculty members. (b) Names of faculty members

not offering any course. (c) Names of departments offering more than three courses, in

alphabetic order.

33 ()
Can you explain the concept of extendible hashing, and how it can be used to efficiently

access data in a large database?

34 ()
What is the structure of B-trees and B+-trees, and how do these data structures optimize

database performance?
35 ()
Can you explain the concept of extendible hashing, and how it can be used to efficiently

access data in a large database?

 IQAC

36 ()
How do you perform indexing on multiple keys in SQL, and what are the advantages and

limitations of using grid files for this purpose?

37 ()
How do aggregation and grouping functions work in SQL, and what are some common use

cases for these functions?

38 ()
In what situations would you need to use a nested SQL query, and how do you write a

non-correlated query?
39 ()
Can you provide an example of a correlated SQL query, and explain why it is useful in

certain contexts?
40 ()
How do aggregation and grouping functions work in SQL, and what are some common use

cases for these functions?

()