CST204 QP

C 02000CST204052101 Pages: 4
Reg No.:_______________ Name:__________________________

APJ ABDUL KALAM TECHNOLOGICAL UNIVERSITY
Fourth Semester B.Tech Degree Examination June 2022 (2019 scheme)
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
1 List any three characteristics of database system 3

2 Draw neat labelled diagram of three schema architecture and briefly describe 3
each level
3 Write briefly about any three relational database integrity constraints. 3
4 Differentiate between theta join and natural join 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 B=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
unspanned organization.
c)
Calculate the average time it takes to find a record by doing a linear search
7 Define Boyce-Codd normal form. How does it differ from 3NF? 3
8 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, R → S } G : { P → QR, R → S }.
Check the equivalency of functional dependencies F and G.
9 Write briefly on log based recovery 3
10 Explain briefly the characteristics of Column family database. 3
PART B
(Answer one full question from each module, each question carries 14 marks)
Module -1
11 a) Differentiate between two-tier and three-tier client-server database architecture 7
with the help of neat labelled diagrams.
Page 1 of 4
02000CST204052101
b) Draw an ER diagram based on the following information, 7

• 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 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
• 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
• 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 6
b) Interpret the following ER diagram 8
Page 2 of 4
02000CST204052101
Module -2
13 a) Consider the following schema, 8
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:
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) DROP and DELETE
ii) ALTER and UPDATE
14 a) Write SQL DDL statements based on the following database schema (Assume 8
suitable domain types):
Employee (eid, name, designation, salary, comp_id)
Company (comp_id, cname, address, turnover)
b) Create the above mentioned tables assuming each company has many
employees. Mention the primary key, foreign key and not null
constraints.
ii) Insert values into both the tables. Mention in which order insertions will be
carried out.
ii) 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%
b) Illustrate any three ways of using INSERT 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 of 4
02000CST204052101
b) 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”
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
{10, 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.
Determine all the keys of relation R. Also decompose the relation into
collections of relations that are in BCNF.
b) Write briefly on the different types of anomalies in designing a database. 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 of R into R1 ( A , B ) , R2 ( B , C ) and R3 ( B , D ) is lossless or
lossy. Write the complete steps.
b) What is dependency preservation property for decomposition? Why is it 8
important?
Module -5
19 a) Explain briefly the ACID properties of a transaction. 8
b) Check whether the given schedules are conflict serializable or not 6
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)
20 a) What 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?
******
Page 4 of 4

CST204 QP

Uploaded by

Copyright:

Available Formats

CST204 QP

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

CST204 QP

Uploaded by

Copyright:

Available Formats

C 02000CST204052101 Pages: 4

Reg No.:_ Name:____________

Course Code: CST204

1 List any three characteristics of database system 3

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

b) Find the employees whose name starts with ‘C’

i) S1 : R1(X) , R2(X) , R1(Y) , R2(Y) , R3(Y) , W1(X) , W2(Y)

You might also like