c 02000csT204052Lo3

Reg No.:

Fourth

Course Name:
Max. Marks: 100

PART A
(Answer oll questions; each question carries 3 marks) Marks

I Classif, the following cases into logical data independence and physical data independence. a
J
(a) Creating an index for a data file
(b) Changing the integrity constraint
(c) Reorganizing the file
2 What is meant by a recursive relationship type? Give an example of recursive relationship type.
J Consider the relational model constraints: domain constraint, key constraint, entity integrity
and referential integrity. Specify which of these constraints may be violated during the
following modification operations: insert, update and delete.
4 What is meant by complete set of relational algebra? Show how join operation in relational
algebra can be represented using this set.
5 what is meant by a correlated nested query? Give a suitable exampre. J
6 Explain the advantage of a multilevel index. J
7 Why Armstrong's axioms are said to sound and complete? J
8 What is meant by lossless join property? J
9 List and explain the desirable properties of a transaction. 3

l0 lllustrate the states for transaction execution.

t 3

*ART B
(Answer onefull question from each module, eoch question carries I4 marks)

Module -1
I I ,. Consider the followihg information about a university database: Professors have a ssn, a qpme, l4
an age, a rank, and a research specialty. Projectrhave a project number, a sponsor name, a
starting date, an ending date, and a budget. Graduate students have ssn, a name, an age, and a
degree program (e.g., M.S. or Ph.D.). Each project is managed by one professor (known as the
project's principal investigator). Each project is worked on by one or more professors (known
as the project's co-investigators). Professors can manage and/or work on multiple projects.
Each project is worked on by one or more graduate students (known as the project's research
assistants). When graduate students work on a project, a professor must supervise their work
on the project. Departments have a department number, a department name, and a location.
Departments have a professor (known as the chairman) who manages the department.
Professors work in one or more depaftments, and for each department that they work in, a time

Page 1 of 4
 02000csT204052Lo3

percentage is associated with their job. Graduate students have one major department in which
they are working on their degrde. Each graduate student has another, more senior graduate
student (known as a student advisor) who advises him or her on what courses to take. Design
and draw an ER diagram that captures the information about the university.
12 a) Explain the difference between database schema and database state with suitable example. 7
Specify the role of schema in a DBMS.
b) With a neat diagram explain the Three Schema Architecture of a DBMS 7

Module -2
13 a) Consider the two relations Tl and T2 shown below. Show the results of the following 8

operations.
Relation Tl Relation T2

P a R
A B c
30 Ac 25
30 Bc 26
35 Bc 28
45 Cc 23
45 Ac 26
30 Bc 25

i) Tl x11.q=r2.BT2
ii) T1 N rr. p=r2.nT2
iiD T1 U T2
iv) Tl N1rr.n=[Link].R=r2.c)T2
b) An Employee relation has attributes: Employee-ld (numeric type), Name (character type), 6

, Salary (numeric type) and Dep-No (numeric Upe).

A Department relation has attributes: Department-Number (numeric Upe), Department-
Name(character type), Dep-Manager-ld (numeric type).
Employee-ld is the primary key of Employee relation. Department-Number is the primary key
of the Department relation. Dep-No attribute of Employee relation refers to the Department-
.. Number attribute of Department relation and Dep-Manager-ld attribute of Department lelation
refers to the Employee-ld attribute of Employ€?elation.
(i) Write create table statements by specifoing necessary integrity constraints for
creating these two relations in SQL.
(ii) Write SQL statement to insert the details of an employee John with id l0l with
salary 5000 and working in department number 5.
(iii) Insert the details of a Research Department with Department Number I and it has
not been assigned any manager.
(iv) Assume that a department with employees working in it is to be deleted. Specify
the two options to manage this scenario.
14 Convert the ER schema for Bank database given below into a relational schema. Specify all l4
primary keys and foreign keys.

Page 2 of 4
 02000csT204052tO3

@56i Grs)

N;;;)

:-rst)
Module -3
15 a) Consider the following relations:
Employee (Employee-ld. Employee-Name, Salary, Department-No)
Department (Department-No, Department-Name)
Write SQL queries for the following:
(i) Retrieve the employee names and their department names
(ii) Retrieve department names and the average salary given by them
(iii) Retrieve the ids of employees getting salary greater than the average salary of their
department
(iv) For each department that has more than 4 employees, retrieve the department-No
and the number of employees getting salary more than Rs. 50000
b) What is meant by a heap file? Explain how insert, update, delete and search operations can be
performed in a heap file.
16 a) oWhat are the advantages of Views? Explain two view implementation techniques. , 7
b) Consider a disk with block size 512 byes. A block pointer is 6 bytes long, and a record pointer 7
is 7 bytes long. A file has 30,000 EMPLOYEE records of fixed-length. Each record has the
following fields: NAME (30 bytes), sSN (gbytes), DEPARTMENTCODE (9 bytes),
ADDRESS (40 bytes), pHoNE (9 bytes),BIRTHDATE (8 bytes), sEX (l byte), JOBCODE (4
bytes), SALARY (4 byes, real number). An additional bye is used as a deletion marker.
Assume tha file is not ordered by the key field SSN and we need to create a secondary index
on SSN.
(i) Find the number of levels needed, if we make it into a multilevel index.
(ii) Find the number of block accesses needed to retrieve a record from this file if we
use the multilevel index.

Page 3 of 4
 02000csT2040s2103

17 a) p={AB * c, cE - D, A -- E}.
'il:lllilldencies
Determine whether each functionar dependency below is in F* or not:
i) AB--D
ii) A--C
b) Consider the following relation:
CAR-SALE (car#, Date_sold, salesperson#, comm iss ion%o,Discount_amt)
Assume that a car may be scitd by multiple salespeople, and hence
{Ca#,Salesperson#} is the
primary key. Additional dependencies are :
Date-sold -> Discount_amt and Salesperson# --t commissiono/o
(i) Based on the given primary key and functional dependencies, is this relation in lNF.
2NF, or 3NF? Why or why not?
(ii) How would you successively normalize it completely?
l8 a) Consider the following decompositions for the relation schema R into
Rl, R2 and R3.
Determine whether the decomposition has the lossless join property
with respect to the given
F.
R={P, Q, R, S, T, U}
Rl={P, Q}, R l: {R, S, T}, R3={p,R,U}
F={P
- Q, R --+ {S, T}, {p,R} -- U}
b) Explain insert, update and delete anomaries with suitable examples.

Module -5
l9 a) Consider the schedule S of three transactions Tl,T2 and T3 given below. State whether the
schedule is serializable or not.
S: r3(Y), r3(Z), rl(X),wl(X), w3(y), w3(Z),r2(Z), rl(y), wl(y), r2(y),w2(y), r2(X),w2(X)
(Hint: Interpret the notation r3(Y) as the operation read database item y
of transaction T3.)
b) Explain the lost update problem and temporary update problem that occur
when concurrent
execution is uncontrolled.
20 ,a) Explain conservative and strict two-phase locking techniques. Why strict
2pL is deadlock free? 7
b) Differentiate among recoverable, cascading rollback and strict schedules
with suitable 7
examples.
rt **

Page 4 of4
 c 02000csT2040s2104

Reg No.: Name:

APJ ABDUL KALAM TECHNOLOGICAL UNIVERS
[Link] Degree s4 (R,s) / s4 (PT) (R,s) Examination June2023 (z0lg
N.W9t
rsi;;ffi*!
Course Code: CST 204
Course Name: Database Management Systems
Max. Marks: 100 Duration: 3 Hours

PART A
(Answer all questions; eoch question carries 3 marks) Marks

I List any SIX major advantages of using a DBMS J

a

2 What is the concept of a weak entity used in data modelling? Define the terms owner 3
entity type, Identifring relationship type.
3 Define theta join.
Given the two relations R and S:
A B C D E
2 3 J I
4 5 6 6 2
7 8 9

Find R0e.p.S.

4 Define primary key, candidate key and super key. 3

5 What is the difference between the WHERE and HAVING clause? Illustrate with an 3
example.
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 List Armstrong Axiom rules * J
9 List the ACID properties of transactions. 3
l0 What is a key-value database? List its major properties. J
PART B
(Answer onefull questionfrom each module, each qaestion corries 14 marks)

Module -1
I I Draw an ER diagram to model the application with the following assumptions. Specifu t4
key attributes of each entity type and (min, max) constraints on each relationship type.

Page 1 of 5
[---

02000csr204052104

o Each home uniquely defined by home identifier, street address, city, state, a number
of bedrooms and a fumber of bathrooms and an associated owner.
o Each owner has a Social Security Number, first name, last name, phone, and
profession.
o An owner can spouse one or more homes.
o Agents represent owners in the sale of a home. An agent can list many homes, but
only one agent can list a home.
o An agent has a unique agent number, name, phone number and an associated office.
o When an owner agrees to list a home with an agent, a commission and a selling price
are determined.
o An office has office identifier, phone number, the manager name, address and an
optional agent number.
o Many agents can work at one office.
o A buyer entity type has a Social Security Number, first name, last name, phone,
preferences for the number of bedrooms and bathrooms, and a price range.
o An agent can work with many buyers, but a buyer works with only one agent.
12 a) What is the difference between logical data independence and physical data
independence? Which one is harder to achieve? Why?
b)

'*-'/--J-*(,Ng9
(.srdr) (Fet

Consider the bank database given above and answer the following questions
i. List the strong (nonweak) entity types in the ER diagram.
" ii. Is there a weak entity type? If so, give its name, partial key, and identifying
relationship.
iii. What constraints do the partial key and the identiffing relationship of the weak entity
type speciff in this diagram?
iv. .List the names of all relationship types, and specify the (min, max) constraint on each
participation of an entity type in a relationship type.

Page 2 of 5
 02000csT204052104

v. Suppose that every customer must have at least one account but is restricted to at most
two loans at a time, and that qbank branch cannot have more than 1,000 loans. How
does this show up on the (min, max) constraints?
Module -2
13 a) Consider the UNIVERSITY database with the following relations: l0
STUDENT @!!IIq, name, degree, year, sex, deptNo, advisor)
DEPARTMENT (!9p!!{, name, hod, phone)
PROFESSOR (gp!!,, name, sex, startYear, deptNo, phone)

@gld, cname, credits, deptNo)

COURSE
ENROLLMENT@grade)
TEACHING@classRoom)
PREREQUISITE@
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.
b) What is a foreign key constraint? Why are such constraints important?
What is referential integrity?
14 a) Convert the following ER diagram into a relational schema
t

b) Consider the following relation schema with referential integrity constraints: l0

PROFESSOR name, sex, startYear, No, phone)

Page 3 of 5
 02000csT204052104

Write SQL DDL statemepts 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.
Module -3
15 a) What is an assertion? How they differ from triggers? 4
b) Consider the following relation schema and write SQL queries to find: l0
EMPLOYEE(Fname, Minit, Lname, $![, Bdate, Address, Sex, Salary SuperSSN, Dno)

@!gI,
DEPARTMENT(Dname, MgrSSN, MgrStartDate)
DEPT_LoCATIONS@
PROJECT(Pname, Pnumber. Plocation,Dnum)

WORKS_ON(ESSNJUS,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 allthe projects controlled by

department number 5.
r 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.
" 16 a) Consider a disk with block size B :512$ytes. A block pointer is P:6 bytes ldng and a g

record pointer is Pn :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 (l 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.

Page 4 of 5
 02000csT204052104

ll. Suppose that the file is ordered by the key field Sr'urr##i ',6ffi
i{.'-- | ;':
primary index on ssn. carculate The number
a
of first-led"?t'hi
number of first-level index blocks
iii. calculate the number of levels needed if we'make
it into a
b) what is a grid file? what are its advantages and disadvantages?

Module -4
17 a) 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?
b) Define minimal cover' Let the given set of
functional dependencies be: E:{B + A,D +
A,AB -+ D) . Find the minimal cover of E
18 a) Explain with example 2NF, 3NF and BCNF.
8
b) consider a relation schema R(x Y zw P (above
) table R) is decomposed into Rl( x y 6
Z) andR2(z w P). Determine whether the above Rl and R2
are Lossless or Lossy?
Module _5
19 a) what is a schedule? Define the concepts ofrecoverable,
cascade less and strict schedules,
and compare them in terms of their recoverability.

b) which of the following schedule is conflict serializable?

For each serial izableschedule
! determine the equivalent serial schedule.
(:). r1. (p; 13 (X); wI (x;; 12(9, w3(x)
q'! ({); 13 (x); -s1x1; wr1k7; ,iQi)
20
(c).r3 (X); rz (X); ws14; rrlxj; il6)
what is the two-phase locking (2PL; protocol?
How does it guarantee serializability?
How strict 2PL differs from basic 2pL? _.; '
b) Explain the need for multimodal database.
List the important characteristics of
ArangoDB.

***

Page 5 of 5
 C 0200CST204122303

Res NO. : Name:

APJ ABDUL KALAM TECHNOLOGICAL UNIVERSIT
[Link] Degree S4 (R,S) (FTAVP/PT) Exam April 2025 (2019 Sche

r Course Code: CST 204

Course Name: Database Management Systems

Max. Marks: 100 Duration: 3 Hours

PART A

(Answer all questions; each question carries 3 marks)

Compare weak entity and strong entity with examples.

What are the major differences between structured, unstructured and semi-structured 3

data?

3 Compare SELECT and PROJECT operations on relational database with examples. 3

4 Differentiate between EQUI-JOIN, THETA JOIN and NATURAL JOIN 3

5 What is an assertion? How they differ from triggers? 3

6 key, candidate key and super key. 3

7 Define functional dependency and explain with example. 3

8 What are Armstrong's axioms? 3

9 ignificance of Log-Based Recovery? 3

10 ist and explain the desirable properties of a transaction 3

PART 8

(Answer one full question firom each module, each question carries 14 marks)

Module -1

With the help ofa neat diagram explain the three schema architecture ofDBMS 7

What is an attribute? Explain different types of attributes in the data base 7

management system.

12 a) Draw the ER model ofa company by considering the following constraints 10

Page 1 of 4
 0200CST204122303

• In a company, an employee works on many projects which are controlled

by one department.

• One employee supervises many employees.

• An employee has one or moredependents.

• One employee manages one department.

b) List different database users and explain each with examples.

Module -2

13 a) Write the rules for converting ER diagram to relational model and convert the 10

following ER diagram to relational model.

b) Differentiate between the given below SQL statements with examples

(i) DROP and DELETE

(ii) ALTER and UPDATE

14 a) An Employee relation has attributes: Employee-Id (numeric type), Name 8

(character type), Salary (numeric type) and Dep-No (numeric type).

A Department relation has attributes: Department-Number (numeric type),

Department-Name (character type), Dep-Manager-Id (num:ric type).

Page 2 of 4
 0200CST204122303

Employee-Id is the primary key of Employee relation. Department-Number is the

primary key of the Department relation. Dep-No attribute of Employee relation

refers to the Department-Number attribute of Department relation and Dep-

Manager-Id attribute of Department relation refers to the Employee-Id attribute of

Employee relation.

(i) Write create table statements by specifying necessary integrity constraints for

creating these two relations in SQL.

(ii) Write SQL statement to insert the details of an employee John with id 101 with

salary 5000 and working in department number 5.

(iii) Insert the details of a Research Department with Department Number I and it

has not been assigned any manager.

(iv) Assume that a department with employees working in it is to be deleted.

Specify the two options to manage this scenario.

b) What is meant by constrains in relational database? Explain each with example. 6

Module -3

15 a) Illustrate structure of B-Tree and 8+ Tree. Differentiate between internal nodes 6

and leave nodes of both trees.

b) For the relation schema below, By assuming necessary key and referential 8

integrity constraints, 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

Page 3 of 4
 0200CST204122303

(iii) Find the ID, name, and city of residence of employees who works for "First

Bank Corporation" and earns more than Rs. 50000

(iv) Find the name of companies whose employees earn a higher salary, on

average, than the average salary at "First Bank Corporation"

16 a) Define views in SQL. Write simple SQL queries to create a view, update a view 8

and drop a view.

b) What is a grid file? What are its advantages and disadvantages?

Module -4

17 a) What is normalization? Explain about lNF, 2NF and 3NF with definition and 7

relevant examples.

b) What are different anomalies in designing a database? Explain each with examples. 7

18 a) Consider the relation R = {A, 8, C, D, E, F, G, H} and the set of functional 8

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.

b) What is the dependency preservation property for decomposition? Why is it 6

important?

Module -5

19 a) Differentiate serial and concurrent schedules. Elaborate conflict serializability 10

with suitable example.

b) Explain briefly the ACID properties ofa transaction.

20 a) What are dirty-read and lost-update problems? Explain with the help of examples. 7

b) What is the two-phase locking (2PL) protocol? How does it guarantee 7

serializability? How strict 2PL differs from basic 2PL?

***

Page 4 of 4
 c 0200csT204122302

RegNo.: Name:

APJ ABDUL KALAI\{ TECHNOLOGICAL UN 9,1. LN.

\i',[Link]
Fourth Semester [Link] Degree (S, FE) Examination January 2024 (

Course Code: CST 204

Course Name: Database Management Systems
Max. Marks: 100 Duration: 3 Hours

PART A
(Answer all questions; each question carries 3 marhs) Marks

Differentiate between Structured. Semi-structured. and Unstructured data. Give 3

an example each.

Explain the three categories of Data Models. J

Explain the "Cross-Reference approach" to mapping a Binary l:l Relationship J

Type that you employ when you map an ER Model into a Relational Schema,
with the help of an example.
4 With the help of an example database, explain the usage of the set operations
Union, Intersection, and set difference in Relational Algebra.
a
5 "Views simplifu the specification of certain queries". Justifr this statement J

6 Di fferenti ate between Assertion s and Tri ggers. J

7 Define the term "Functional Dependency". Give an example. J

8 Give an algorithm to find the Minimal Cover for a set of Functional 3

Dependencies.
.9 Give six types of failures in a transaction processing system that necessitate
Recovery procedures.
l0r Explain the importance of transaction loggi4g and checkpointing for efficient
transaction processing.
PART B
(Answer [Link] fall q uestion fro
Module -1
u a) Explain the main four characteristics of the Database Approach that distinguish it
from the traditional file-processing approach
b) Categorize the different types of people who work in a database system
environment.

Page 1 of 4
 0200csr204122302

12 Design an ER diagram for a typical college library database and then map it into 14

a relational database scheira. List your assumptions and indicate the cardinality
mappings.
Module -2
13 a) Consider the following schema and frame Relational Algebra queries for the II

following problems:
- Suppliers (SlDjnlegef, SName: string, Address: string) Parts (PlDjnlg$r
PName: string, Color: string) catalog (slo: intgggl, PID: inteeer, 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

(iD 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
b) Differentiate between DELETE and DROP commands in SQL. Illustrate their 3

usage.

14 a) Consider a company database having the following schema and frame Relational l0
Algebra queries for the following problems. Primary keys are underlined.
EMPLOYEE GSN, Name, SupervisorSSN, Dnum)
DEPARTMENT (Dnumber, Dname, MgTSSN)
t PROJECT (pnumUer, 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
I
employee named'Smith' i
(ii) List the numbers of the projects (Pnumbers) controlled by Smith's
department
-a
b) Illustrate the usage of the SQL commands - ALTER, INSERT, DELETE and 4
UPDATE
Module -3
15 Consider the following Database with two tables: 14

Page 2 of 4
 0200csT204122302

Table: Employees r
EmployeelD INT PRIMARY KEY

FirstName VARCHAR(50)

LastName VARCHAR(5O)

' JobTitle VARCHAR(I00)

Salary DECIMAL(10,2)

. HireDate DATE

DepartmentlD INT

Table: Departments

DepartmentlD INT PRIMARY KEY

DepartmentName VARC HAR( I 00)

ManagerlD INT

Foneign Key: [Link] references [Link]

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:
I
(iiD Find departments with more than 25 employees.
(iv) Get the employee names starting with 'S' in alphabetical order
16 a) With the help of an example explain Single-level indexing and multi-level 8
indexing. Also, compare and contrast single-level indexing with multi-level
.
indexing '4
b) Explain a situation where a multi-level index would be significantly less effective 6

. than a single-level in{ex, and vice versa *. 3

. Module 4
17 a) Given the following FDs for the relation Employees (EmployeelD, 6
DepartmentlD, ManagerlD, Salary):
EmployeelD DepartmentlD
-
DepartmentlD + ManagerlD

Page 3 of4
 O2fircsT204122302

ManagerlD * Salary
'Identifi any redundant FIls in the set and explain why they are redundant.

b) Consideithe following FDs for the relation Books (BookID, Title, Author,
Publisher):
BookID + Title, Author
Author r Publisher

{Title, Publisher} BookID

-
Find a minimal cover for this set of FDs. Explain how you arrived at your
answer.

l8 . a) Give an algorithm each for checking Lossless Join and Dependency Preserving
hoperties
b) 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
19 a) What is a serial schedule? Why are serial schedules unacceptable in practice? 4

b) What is a conflict serializable schedule? Give an algorithm to check whether a t0

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); rl(X); wl(X); rl(Y); wl(Y)
(ii) rl(X); r2(X);wl(X); rl(Y); w2(X); wl(Y)
20 a) Explain the working of Binary Locks and Shared/Exclusive Locks
t
5

b) Explain Two-Phase locking protocol andtny three variants of it 9

* *tr

Page 4 of4
l-_

C 0200csT204052401 l(€
Reg No.: Name:

APJ ABDUL KALAM TECHNOLOGICAL UNIV

[Link] Degree 54 (R,S) / 54 (PT) (R,S) / 54 (WP) (R) Examination May

Course Code: CST204

Course Name: Database Management Systems
Max. Marks: 100 Duration: 3 Hours

PART A
(Answer all questions; each question carries 3 marks) Marks
I List out any three database users and their functionalities. 3
2 Distinguish between schema and instance. 3
3 Explain the intersection operation in relational algebra with an example. 3
4 Differentiate between Super key, Candidate key and Primary key. 3
5 Outline concept of assertion in SQL with an example. 3
6 Explain any three differences between Hash indexes and B* tree indexes. 3

7 State the Armstrong's axioms of FD. 3

8 State insertion anomaly with suitable example. 3

9 Explain the different properties of a transaction. 3

10 Summarize the purpose of using a lock compatibility matrix. 3

PART B
(Answer onefull questionfrom each module, each question canies 14 marks)

n -1
Module
r
ll a) Explain the seven drawbacks of file pto..rdg system, compared to database 7

approach.

b) Represent a "Library Management Software" using an ER diagram. 7

t2 a) Explain the concept of three schema architecture with the help of a neat labelled 7

diagram.
b) Represent an "Online Shopping Portal" using an ER diagram.

. Module -2
13 a) Explain the left outer join, right outer join and full outer join operations with

Page 1 of 3
a-

0200csT2040s240L

suitable examples.
b) Consider the following schema.

Suppliers( g!{, sname, addrels)

Parts(p[g[, prulme, color)

Catalog(sid. pid, cost)

The primary key fields are underlined; Foreign keys have the same name as

primary keys. Assume integer domain for the attributes sid, pid, cost and string 8
domain for the attributes sname, address, pname, color. Write relationel
algebra expressions for the given questions. (Use * symbol for natural join and

. K symbol forjoin)
(i) Find the names of suppliers who supply some red part.
(ii| Find the sids of suppliers who supply some red or green part.
(iii) Find the sids of suppliers who supply every red part or supply every

green part.

(ivl Find the pids of parts supplied by at least two different suppliers.
14 a) What is constraint? Discuss about domain constraint, entity integrity and 7
referential integrity constraint with suitable example.
b) Illustrate with an example the diflerent steps involved in synthesizing an ER 7
diagram into a relational schema.

Module -3
ll a) With the help of an example explain Single-level indexing and multi-level 8
indexing. Explain the difference (any four) between singleJevel indexing and
. multi-level indexing.
' b) Describe any three aggregate functions in SQL with example. 6
16 " a) Explain a situation where a multi-levelindex would be significantly lesd 6
effective than a single-level index, and vice versa.
b) Consider the following relations: 8
Employee (gmplgyee-Id, Employee-Name, Salary, Department-No)
Department (DgpartmentNo, Department-Name)

The primary key fields are underlined. Foreign keys have the same name as

primary keys. Write SQ[.: queries for the following:

(i) Retrieve the employee names and their department names.
(ii) Retrieve ddpartment names and the average salary given by them.

. Page 2of3
 0200csT204052401

(iii) Retrieve the ids of employees getting salary greater than the average
salary of their department.
(iv) For each department tfiat has more than 4 employees, retrieve the
department-No and the number of employees getting salary more
than Rs. 50000.
Module -4
17 a) Write an algorithm to find the closure of an attribute. 6
-
b) Let R: (A,B,C,D,E), Rl : (A,D), R2 : (A,B), R3 : (B,E), R4 : (C,D,E), and 8

R5 : (A,E). Let the FDs be: A-C, B--+C, C-+D, A-D, DE-C, CE+A.
Check whether the decomposition of R into {Rl, R2, R3, R4, R5} is a lossless
decomposition or not.

.. 18 a) P and Q are two set of FDs for a relational schema R(A, B, C, D). 6
p: {A+B, B+C, C---+D} and Q: {A--+BC, C-D}. Check whether P covers
Q and Q covers P? Also check whether P and Q are equivalent?
b) Define lNF,2NF,3NF and BCNF with suitable examples. 8

Module -5
19 a) Describe the different states of a transaction with the help of a neat sketch. 7

b) What is defened database modification? How it is different from immediate 7

database modification? Explain the recovery steps in deferred database

modification with an example.
.20 a) Determine if the following schedule is recoverable. Is the schedule cascadeless? 4
r Justifr your answer.
l(X),r2(Z),rl(Z), r3(X), r3(Y), wl(X), cl, w3(Y), c3, r2(Y), w2(Z),w2(Y),c2
(Note: ri(Xywi(X) means transaction Ti issues read/write on item X;
' , ci means transaction Ti commits.) r
b) What is REDIS? What are the Features (any fffir) of REDIS? 6

c) Check whether the following schedule is conflict serializable or not and find an 4

equivalent serial schedule if possible.

rl(X),r2(Z),rl(Z),r3(X),r3(Y),wl(X),w3(Y),r2(Y),w2(Z),w2(Y)
(Note: ri(Xywi(X) means tansaction Ti issues read/write on item X)
- *:r*

Page 3 of 3
 c 02000csT20,4052101

RegNo.: t;
!.

:;

Course Code: CST204

Course Name: DATABASE MANAGEMENT SYSTEMS
Max. Marks: 100 Duration: 3 Hours

PART A
(Answer all questions; each question carries 3 morks) Marks

I List anythree characteristics of database system 3

2 Draw neat labelled diagram of three schema architecture and briefly describe 3
each level
Write briefly about any three relational database integrity constraints.
a
J 3

4 Differentiate between theta join and naturaljoin operations. 3

5 Give any three uses of a trigger 3

6 A file has r :20000 STUDENT records of fixed length. Each record has the 3
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 8:512
bytes,
a) Calculate the record size R in bytes.
b) Calculate the blocking factor bfr and the number of file blocks b assuming an
I unspanned organization.
c)
Calculate the average time it takes to find a record by doing a linear search
7 Define [Link] normal form. How does it differ from 3NF? 3

Suppose, a relational schema R (P,Q, R, S) and set of functional dependencies F 3

: and G are as follow: F : { P - Q, Q - & R --+ S } G : { P' QR, R --- S }.'
Check the equivalency of functional dependencies F and G.
9 Write briefly on log based recovery a
J

l0 Explain briefly the characteristics of Column family database. a

J

PART B
(Answer onefull questionfrom each module, eoch question carries 14 marks)

Module -1
11 a) Differentiate between two-tier and three-tier client-server database architecture
with the help of qeat labelled diagrams.

Page 1 of4
 02flx1csT204052101

b) Draw an ER diagram based on the following information,

o Manufacturers haveb name, which we may assume is unique, an address,
and a phone number

o Products have a model number and a type. Each product is made by one
manufacturer, 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
o Customers are identified by their unique social security number. They have
email addresses, and physical addresses. Several customers may live at the
same (physical) address, but we assume that no two customers have the same

email address
o An order has a unique order number, and a date. An order is placed by one
customer. For each order, there are one or more products ordered, and there
is a quantity for each product on the order.

12 a) Write briefly about any three types of database end users

b) Interpret the following ER diagram

Page 2 of4
 0200(rcsT204052101

Module -2
13 a) Consider the following scltma, 8

Suppliers (gid, sname, address)

Parts (pltl. pname, color)

Catalog Gid, 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
b) Differentiate between the following SQL statements 6

b) EROP and DELETE

iD ALTER and UPDATE
14 a) Write SQL DDL statements based on the following database schema (Assume 8

suitable domain types):

Employee (ekl, name, designation, salary, comp_id)
Company (ggrnpjA cname, address, turnover)
b) Create the above mentioned tables assuming each company has many
employees. Mention the primary k"y, foreign key and not null
constraints.
t ii) Insert values into both the tables. Mention in which order insertions will be

carried out.
ii) Modifu tfre i"aUfe Employee to include a new column "years-of-exp"
iv) Increment the salary of employees whose salary is less than Rs25000 by 5%.
*
b) Illustrate any three ways of using lNSERf'statement in SQL. 6

Module -3
15 a) For the relation schema below, give an expression in SQL for each of the queries 8

that follows:
employee (ID person_name, street, city)

works (ID. company_name, salary)

company ( company_name, city)
manages (ID manager_id)

Page 3 of4
 02fitocsT204052101

b) Find the empioyees whose name starts with 'C'

ii) Find the name of maragers 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 salbry at "First Bank Corporation"
' b) Differentiate correlated and non-correlated nested queries with suitable 6

examples
16 a) What is multi-level indexing? How does it improve the efficiency of searching 8

an index file?

b) Insert the following keys, in the order given, into a B -tree of order 3: 6

{ lo, 50, 20,5,22,25}

Module -4
17 a) Consider a relation R(A, B, C, D, E) with FDs 8

AB+C, AC-)B, BC+A,D+E.

[Link] all the keys of relation R. Also decompose the relation into

collections of relations that are in BCNF.

b) database.
Write briefly on the different types of anomalies in designing a 6

18 a) Consider a relation schema R (A,B,C,D) with the following functional 6

dependencies A + B, B -- C, C
- D, D + B. Determine whether the
decomposition ofR into Rl ( A, B ), R2 ( B, C ) and R3 ( B, D ) is lossless or
I
lossy. Write the complete steps.
b) What is dependency preservation property for decomposition? Why is it 8
"-
important?
Module -5
o '
D a) Explain briefly the ACID propertieiofftransaction. 8

b) Check whether the given schedules are conflict serializable or not 6

i) Sl : RICX), Rz(X), Rr(Y), Rz(Y) , k(Y), Wr(X) , Wz(Y)

iD 52 : Rr(X), Rz(X), Rz(Y), Wz(Y), Rr(Y), Wr(X)
-What
20" a) is two phase locking protocol? How does it guarantee serializability? 8

b) What are the main characteristics of NOSQL systems in the areas related to data 6
models and query languages?
* **:f *:f

Page 4 of4
c 02000csr204052L02

RegNo.:
APJ ABDTJL KALAM TECHNOLOGICAL TINIVE
Fourth Semester [Link] Degree Examination July 2021 (2019 Sc

Course Code: CST204

Course Name: DATABASE MANAGBMENT SYSTEMS
Max. Marks: 100 Duration: 3 Hours

PART A
(Answer all questions; each qaestion carries 3 marks) Marks

l <, List any three categories of database users, highlighting any one important 3

characteristic of each category.

What are the major difference between structured, unstructured and semi 3

structured data.
What is entity integrity? Why is it importantT 3

Distinguish between Super key, Candidate key, and Primary key using a real 3

convincing example.
a
5 Illustrate the concept of trigger in SQL with an example J

6 Compare DDL and DML with the help of an example 3

I Illustrate different anomalies in designing a database 3

a
J
8 How can we conclude two FDs are equivalent?
a
9 lllustrate two phase locking -)
I
l0 How conversions of locks are achieved in concurrency control? J

PART B
(Answer onefull questionfrom esch module, each question carries 14 marks)
Module -1
1 I a) A company has the following scenario: There are a set of salespersons. Some of 7

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

Page 1 of 3
 02000csT204052102

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.
b) Explain three schema architecture withfigure 7

12 a) Illustrate Database architecture with a ne-at diagram 7

,,, O, Explain the characteristics of Database system 7

Module -2
' .- 13 a) Study the tables given below and write relational algebra expressions for the 8
c.
queries that follow.

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

COURSE(COURSETD, CNAME, CREDTTS )
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 rbferring 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.
t b) Explain the left outer join, right outer join, full outer join operations with 6
' examples
rt

,. 14 a) Consider the following relations for a database that keeps track of business trips 9

I of salespersons in a sales office:

SALESPERSON(Ssn, Name, StartYear, DeptNo)

TRIP(Ssn, FromCity, Tocity, DepartureDate, RetumDate, Tripld)
EXPENSE(TripId, AccountNo, Amount) s

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.

Page 2 of 3
 02000csT2040s2rc2

iii) Write relation algebra expression to get the details of salesperson who had
incurred the greatest travel expenses among all travels made.
b) List the basic data types available for defining attributes in SQL? 5

Module -3
l5 a) Illustrate structure of B-Tree and B+ Tree and explain how they are different? 5
b) What are the different typ€s of siigle-level ordered indices? Explain. 9
L6 a) Differentiate between static hashing and dynamic hashing. g
. b),, Write short notes on Nested queries 5

Module -4
17-
\ a) i) What are Armstrong's axioms? l0
ii) Write an algorithm to compute the attribute closure of a set of attributes (X)
'i under a set offunctional dependencies (F).

iii) Explain three uses of attribute closure algorithm.

b) Explain the difference between BCNF and 3NF with an example 4
. 18 a) Consider the relation R = {A, B, C, D, E, F, G, H} and the set of functional g

dependencies F: {,{+DE, B-'F, AB--C, C--*GH, G-rH}.What is the key

for R? Decompose R into 2NF and then 3NF relations.
b) What is the lossless join property of decomposition? Why is it important? 5

, Module -5
19 a) Explain rhe concepts behind the following: _ l0
i) Log-Based Recovery
t ii) Deferred Database Modification.
' b) Why recovery is needed in transaction processing?
, 4
'. 20 a) Differentiate serial and concurrent schedules. Elaborate conflict seiializability l0
I with suitable example.
b) What are the desirable properties of transactions? Explain 4
*****

Page 3 of 3