c 02000csT2040s2104

Reg No.: Name:

APJ ABDUL KALAM TECHNOLOGICAL UNIVERS
B.Tech 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