Module 1

1.​ List any three categories of database users, highlighting any one important characteristic of
each category.
2.​ What are the major differences between structured, unstructured and semi structured data.
3.​ List any three characteristics of database system
4.​ Draw neat labelled diagram of three schema architecture and briefly describe each level
5.​ Differentiate between Structured, Semi-structured, and Unstructured data. Give an example
each.
6.​ Explain the three categories of Data Models.
7.​ Explain the “Cross-Reference approach” to mapping a Binary 1:1 Relationship Type that
you employ when you map an ER Model into a Relational Schema,with the help of an
example.
—----------------------------------------------------------------------------------------------------
8.​ A company has the following scenario: There are a set of salespersons. Some of them
manage other salespersons. However, a salesperson cannot have more than one manager.
A salesperson can be an agent for many customers. A customer is managed by exactly
one salesperson. A customer can place any number of orders. An order can be placed by
exactly one customer. Each order lists one or more items. An item may be listed in many
orders. An item is assembled from different parts and parts can be common for many
items. One or more employees assemble an item from parts. A supplier can supply
different parts in certain quantities. A part may be supplied by different suppliers.
(i) Identify and list entities, suitable attributes, primary keys, and relationships to
represent the scenario.
(ii) Draw an ER diagram to model the scenario using min-max notation.

9.​ Explain three schema architecture with figure

10.​Illustrate Database architecture with a neat diagram
11.​Explain the characteristics of Database system
12.​Differentiate between two-tier and three-tier client-server database architecture with the
help of neat labelled diagrams.
13.​Draw an ER diagram based on the following information,
• Manufacturers have a name, which we may assume is unique, an address,and a phone
number
• Products have a model number and a type. Each product is made by onemanufacturer,
and different manufacturers may have different products with the same model number.
However, you may assume that no manufacturer would have two products with the same
model number
• Customers are identified by their unique social security number. They haveemail
addresses, and physical addresses. Several customers may live at thesame (physical)
address, but we assume that no two customers have the sameemail address
• An order has a unique order number, and a date. An order is placed by onecustomer. For
each order, there are one or more products ordered, and thereis a quantity for each
product on the order.
14.​Write briefly about any three types of database end users
15.​Interpret the following diagram

16.​What is the difference between logical data independence and physical data independence?
Which one is harder to achieve? Why?
17.​Explain the main four characteristics of the Database Approach that distinguish itfrom
the traditional file-processing approach
18.​ Categorize the different types of people who work in a database system environment.
19.​Design an ER diagram for a typical college library database and then map it intoa
relational database schema. List your assumptions and indicate the cardinality
mappings.

Module 2
1.​ What is entity integrity? Why is it important?
2.​ Distinguish between Super key, Candidate key, and Primary key using a real
convincing example.
3.​ Write briefly about any three relational database integrity constraints.
4.​ With the help of an example database, explain the usage of the set operations
Union, Intersection, and set difference in Relational Algebra.
—------------------------------------------------------------------------------------------------------------
5.​ 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.
6.​ Explain the left outer join, right outer join, full outer join operations with examples
7.​ 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.
8.​ List the basic data types available for defining attributes in SQL
a) 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:
i) 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
9.​ Differentiate between the following SQL statements
i) DROP and DELETE ii) ALTER and UPDATE
10.​Write SQL DDL statements based on the following database schema (Assumesuitable
domain types):
Employee (eid, name, designation, salary, comp_id)
Company (comp_id, cname, address, turnover)
i) Create the above mentioned tables assuming each company has manyemployees.
Mention the primary key, foreign key and not nullconstraints.
ii) Insert values into both the tables. Mention in which order insertions will be
carried out.
iii) 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%
11.​Illustrate any three ways of using INSERT statement in SQL.
12.​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.
13.​Convert to relational schema

14.​Consider the following relation schema with referential integrity constraints:

Write SQL DDL statements for the following:

i. Create table STUDENT, DEPARTMENT, PROFESSOR including primary and
foreign key integrity constraints.
ii. Add an address attribute in the table STUDENT
iii. Write an SQL statement to delete the “CS” department. Given the referential
integrity constraints, explain what happens when this statement is executed.
15.​Consider the following schema and frame Relational Algebra queries for the following
problems:
Suppliers (SID: integer, SName: string, Address: string) Parts (PID: integer,
PName: string, Color: string) Catalog (SID: integer, PID: integer, Cost: real)
The key fields are underlined and the domain of each field is given after the field
name.
(i) Find the names of suppliers who supply red parts
(ii) Find the SIDs of suppliers who supply some red part or are at the address
‘221 Packer Ave’
(iii) Find the SIDs of suppliers who supply some red part and some green part
(iv) Find the SIDs of suppliers who supply every red part
16.​Differentiate between DELETE and DROP commands in SQL. Illustrate their usage.
17.​a) Consider a company database having the following schema and frame Relational
Algebra queries for the following problems. Primary keys are underlined.
EMPLOYEE (SSN, Name, SupervisorSSN, Dnum)
DEPARTMENT (Dnumber, Dname, MgrSSN)
PROJECT (Pnumber, Plocation, ControlDeptNum)
EMPLOYEE (Dnum) References DEPARTMENT (Dnumber) and
PROJECT (ControlDeptNum) References DEPARTMENT (Dnumber)
(i) Find the names of all employees who are supervised by the supervisor of the
employee named ‘Smith’
(ii) List the numbers of the projects (Pnumbers) controlled by Smith’s
department
18.​Illustrate the usage of the SQL commands – ALTER, INSERT, DELETE and UPDATE

Module 3
1.​ Compare DDL and DML with the help of an example
2.​ Illustrate the concept of trigger in SQL with an example
3.​ Compare DDL and DML with the help of an example
4.​ Differentiate between theta join and natural join operations.
5.​ Give any three uses of a trigger
6.​ What is the difference between the WHERE and HAVING clause? Illustrate with an
example.
7.​ “Views simplify the specification of certain queries”. Justify this statement 3
8.​ Differentiate between Assertions and Triggers.
—---------------------------------------------------------------------------------------------------
9.​ Illustrate structure of B-Tree and B+ Tree and explain how they are different?
10.​What are the different types of single-level ordered indices?
11.​Differentiate between static hashing and dynamic hashing.
12.​Write short notes on Nested queries
13.​For the relation schema below, give an expression in SQL for each of the queries
that follows:
employee (ID, person_name, street, city)
works (ID, company_name, salary)
company ( company_name, city)
manages (ID, manager_id)
i) Find the employees whose name starts with ‘C’
ii) Find the name of managers of each company
iii) Find the ID, name, and city of residence of employees who works for “First
Bank Corporation” and earns more than Rs50000
iv) Find the name of companies whose employees earn a higher salary, on average,
than the average salary at “First Bank Corporation”
14.​Differentiate correlated and non-correlated nested queries with suitable examples
15.​What is multi-level indexing? How does it improve the efficiency of searching an
index file?
16.​ Insert the following keys, in the order given, into a B -tree of order 3:
{10, 50, 20, 5, 22, 25}
17.​Consider the following relation schema and write SQL queries to find:
EMPLOYEE(Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary SuperSSN,
Dno)
DEPARTMENT(Dname, Dnumber, MgrSSN, MgrStartDate)
DEPT_LOCATIONS(Dnumber, Dlocaions)
PROJECT(Pname, Pnumber, Plocation,Dnum)
WORKS_ON(ESSN, Pno,Hours)
i. Retrieve the name and address of all employees who work for the 'Research'
department.
ii. For each employee, retrieve the employee's name, and the name of his or her
immediate supervisor.
iii. Retrieve the name of each employee who works on all the projects controlled by
department number 5.
iv. Make a list of all project numbers for projects that involve an employee whose
last name is 'Smith' as a worker or as a manager of the department that controls
the project.
v. Retrieve the SSN of all employees who work on project number 1, 2, or 3.
18.​Consider a disk with block size B =512 bytes. A block pointer is P=6 bytes long and a
record pointer is PR =7 bytes long. A file has r=30,000 EMPLOYEE records of fixed
length. Each record has the following fields: Name (30 bytes),Ssn (9 bytes),
Department_code (9 bytes), Address (40 bytes), Phone (10 bytes), Birth_date (8
bytes), Sex (1 byte), Job_code (4 bytes), and Salary (4 bytes, real number). An
additional byte is used as a deletion marker.
i. Calculate the record size R in bytes.
ii. Suppose that the file is ordered by the key field Ssn and we want to construct a
primary index on Ssn. Calculate The number of first-level index entries and the
number of first-level index blocks
iii. Calculate the number of levels needed if we make it into a multilevel index.
19.​What is a grid file? What are its advantages and disadvantages?
20.​Consider the following Database with two tables:
Table: Employees
EmployeeID INT PRIMARY KEY
FirstName VARCHAR(50)
LastName VARCHAR(50)
JobTitle VARCHAR(100)
Salary DECIMAL(10,2)
HireDate DATE
DepartmentID INT
Table: Departments
DepartmentID INT PRIMARY KEY
DepartmentName VARCHAR(100)
ManagerID INT
Foreign Key: Employees.DepartmentID references Departments.DepartmentID
Frame SQL queries for the following problems:
(i) Calculate the average salary per department.
(ii) List the employees with the highest salary in each department:
(iii) Find departments with more than 25 employees.
(iv) Get the employee names starting with 'S' in alphabetical order

21.​a) With the help of an example explain Single-level indexing and multi-level indexing.
Also, compare and contrast single-level indexing with multi-level indexing
22.​Explain a situation where a multi-level index would be significantly less effective than
a single-level index, and vice versa
Module 4
1.​ Illustrate different anomalies in designing a database
2.​ Explain different types of normalization. What is it needed?
3.​ A file has r =20000 STUDENT records of fixed length. Each record has the
following fields: NAME (30 bytes), SSN (9 bytes), ADDRESS (40 bytes),
PHONE(9 bytes), BIRTHDATE (8 bytes), GENDER (1 byte), DEPTID (4
bytes), CLASSCODE (4 bytes), and PROGID (3 bytes). An additional byte is
used as a deletion marker. The file is stored on the disk with block size B=512
bytes,
1)​ Calculate the record size R in bytes.
2)​ Calculate the blocking factor bfr and the number of file blocks b assuming an
3)​ unspanned organization.
4)​ Calculate the average time it takes to find a record by doing a linear search
4.​ Define Boyce-Codd normal form. How does it differ from 3NF? 3
5.​ Suppose, a relational schema R (P,Q, R, S) and set of functional dependencies F and G
are as follow: F : { P → Q, Q → R, R → S } G : { P → QR, R → S }.Check the
equivalency of functional dependencies F and G.
6.​ Explain the difference between Hash indexes and B+-tree indexes.
7.​ Define the term functional dependency. Why are some functional dependencies called
trivial?
8.​ Define the term “Functional Dependency”. Give an example.
9.​ Give an algorithm to find the Minimal Cover for a set of Functional Dependencies.
—------------------------------------------------------------------------------------------------
10.​i) What are Armstrong’s axioms?
ii) Write an algorithm to compute the attribute closure of a set of attributes (X) under a set of
functional dependencies (F).
iii) Explain three uses of attribute closure algorithm.
11.​Explain the difference between BCNF and 3NF with an example
12.​a) Consider the relation R = {A, B, C, D, E, F, G, H} and the set of functional
dependencies F = {A→DE, B→F, AB→C, C→GH, G→H}. What is the key for R? Decompose
R into 2NF and then 3NF relations.
13.​What is the lossless join property of decomposition? Why is it important?
14.​a) Consider a relation R(A, B, C, D, E) with FDs AB → C, AC → B, BC → A, D →
E.Determine all the keys of relation R. Also decompose the relation into collections of relations
that are in BCNF.
15.​Write briefly on the different types of anomalies in designing a database.
16.​Consider a relation schema R (A,B,C,D) with the following functional dependencies A → B, B
→ C, C → D, D → B. Determine whether the decomposition of R into R1 ( A , B ) , R2 ( B , C
) and R3 ( B , D ) is lossless orlossy. Write the complete steps.
17.​What is dependency preservation property for decomposition? Why is it important?
18.​Consider a relation R with five attributes (A,B,C,D,E) . You are given the
following dependencies: A → B, BC → E, and ED → A.
i. List all keys for R.
ii. Is R in 3NF?
iii. Is R in BCNF?
19.​Define minimal cover. Let the given set of functional dependencies be: E:{B →
A,D →A, AB → D} . Find the minimal cover of E
20.​Explain with example 2NF, 3NF and BCNF. 8
21.​Consider a relation schema R(X Y Z W P ) (above table R) is decomposed into
R1( X YZ ) and R2( Z W P). Determine whether the above R1 and R2 are
Lossless or Lossy?
22.​Given the following FDs for the relation Employees
(EmployeeID,DepartmentID, ManagerID, Salary):
EmployeeID → DepartmentID
DepartmentID → ManagerID
ManagerID → Salary
Identify any redundant FDs in the set and explain why they are redundant.
23.​Consider the following FDs for the relation Books (BookID, Title, Author,
Publisher):
BookID → Title, Author
Author → Publisher
{Title, Publisher} → BookID
Find a minimal cover for this set of FDs. Explain how you arrived at your
Answer.
24.​Give an algorithm each for checking Lossless Join and Dependency Preserving
Properties
25.​ Suppose that we decompose the schema R = (A, B, C, D, E) into (A, B, C)
(A, D, E).
Show that this decomposition is a lossless decomposition if the following set F
of functional dependencies holds:
F = {A → BC, CD → E, B → D, E → A}
Module 5

1.​ Illustrate two phase locking

2.​ How conversions of locks are achieved in concurrency control?
3.​ Write briefly on log based recovery
4.​ Explain briefly the characteristics of Column family database.
5.​ What is a key-value database? List its major properties.
6.​ Give six types of failures in a transaction processing system that necessitate recovery
procedures.
7.​ Explain the importance of transaction logging and checkpointing for efficient
transaction processing.
—--------------------------------------------------------------------------------------------
8.​ Explain the concepts behind the following: -

i) Log-Based Recovery
ii) Deferred Database Modification.
9.​ Why is recovery needed in transaction processing?
10.​Differentiate serial and concurrent schedules. Elaborate conflict serializability with
suitable example.
11.​What are the desirable properties of transactions? Explain
12.​Explain briefly the ACID properties of a transaction.
13.​Check whether the given schedules are conflict serializable or not
i) S1 : R1(X) , R2(X) , R1(Y) , R2(Y) , R3(Y) , W1(X) , W2(Y)
ii) S2 : R1(X) , R2(X) , R2(Y) , W2(Y) , R1(Y) , W1(X)
14.​What is two phase locking protocol? How does it guarantee serializability? 8
15.​What are the main characteristics of NOSQL systems in the areas related to data
models and query languages?
16.​What is a schedule? Define the concepts of recoverable, cascade less and strict
schedules,and compare them in terms of their recoverability.
17.​ Which of the following schedule is conflict serializable? For each serializable schedule
determine the equivalent serial schedule.
(a) r1 (X); r3 (X); w1(X); r2(X); w3(X)
(b) r1 (X); r3 (X); w3(X); w1(X); r2(X)
(c) r3 (X); r2 (X); w3(X); r1(X); w1(X)
18.​What is the two-phase locking (2PL) protocol? How does it guarantee serializability?
How strict 2PL differs from basic 2PL?
19.​Explain the need for multimodal database. List the important characteristics of
ArangoDB.
20.​What is a serial schedule? Why are serial schedules unacceptable in practice? 4
21.​What is a conflict serializable schedule? Give an algorithm to check whether a
schedule is conflict serializable or not. Check whether the following schedules
are conflict serializable or not and find an equivalent serial schedule if possible.
(i) r2(X); w2(X); r1(X); w1(X); r1(Y); w1(Y)
(ii) r1(X); r2(X); w1(X); r1(Y); w2(X); w1(Y)
22.​Explain the working of Binary Locks and Shared/Exclusive Locks
23.​Explain Two-Phase locking protocol and any three variants of it