|
ERODE SENGUNTHAR ENGINEERING COLLEGE
(An Autonomous Institution , Affiliated to Anna University)
PERUNDURAI, ERODE -638 057
|
|
II Year-B.E Computer Science and Engineering [A&B]
|
Model Examination
|
Credit: 3
|
19IT302 DATABASE MANAGEMENT SYSTEM
|
R-2019
|
Date : 03.06.2022
|
SET-II
|
Maximum marks: 100
|
Time: 10.00 a.m. to 1.00 p.m.
|
ANSWER ALL THE QUESTIONS
|
Duration: 3 Hours
|
Q.No
|
Part- A ( 8 x 1 = 8 Marks)
|
Bloom’s Level
|
COs
|
1.
|
Which of the following is known as a set of entities of the same type that share same properties or attributes?
|
[RE]
|
CO1
|
Relation set
|
b.Tuples
|
c.Entity set
|
d. Entity Relation model
|
2.
|
______________ is a set of one or more attributes taken collectively to uniquely identify a record.
|
[UN]
|
CO1
|
a. Primary Key
|
b. Primary Key
|
c.Super key
|
d.Candidate key
|
3.
|
What term is used to refer to a specific record in your music database; for instance; information stored about a specific album?
|
[RE]
|
CO2
|
a.Relation
|
b.Instance
|
c.Row
|
d.Column
|
4.
|
What does the following code snippet do?
DELETE FROM STUDENTS WHERE AGE = 16;
ROLLBACK;
|
[AP]
|
CO2
|
a.Perform undo operation on delete
|
b.Delete the rows from the table where age=16
|
c.Delete Entire Table
|
d.None of the above
|
5.
|
Functional Dependencies are the types of constraints that are based on______
|
[UN]
|
CO3
|
a. Key
|
b.Superset Key
|
c.Revised key
|
d.None
|
6.
|
For select operation the ________ appear in the subscript and the ___________ argument appears in the paranthesis after the sigma.
|
[UN]
|
CO3
|
a.Predicates, relation
|
b.Relation, Predicates
|
c.Operation, Predicates
|
d.Relation, Operation
|
7.
|
The scheme that controls the interaction between executing transactions is called as _____
|
[RE]
|
CO4
|
a.Concurrency control scheme
|
b.Multiprogramming scheme
|
c.Serialization scheme
|
d.Schedule scheme
|
8.
|
Which of the following locks the item from change but not from read?
|
[RE]
|
CO4
|
a.Implicit lock
|
b.Explicit lock
|
c.Exclusive lock
|
d.Shared lock
|
9
|
______________ partitions data and parity among all N+1 disks, instead of storing data in N-disks and parity in one disk.
|
[UN]
|
CO5
|
a.Block interleaved parity
|
b.Block interleaved distributed parity
|
c.Bit parity
|
d.Bit interleaved parity
|
10
|
The order of log records in the stable storage ____________ as the order in which they were written to the log buffer.
|
[UN]
|
CO5
|
a.Must be exactly the same
|
b.Can be different
|
c.Is opposite
|
d.Can be partially same
|
Q.No
|
Part- B ( 7 x 2 = 14 Marks)
|
Blooms
Level
|
COs
|
11
|
What are the four categories of Data models?
|
[RE]
|
CO1
|
12
|
The storage manager implements several data structures as part of the physical system implementation. What are they?
|
[UN]
|
CO1
|
13
|
List the various types of attributes.
|
[RE]
|
CO2
|
14
|
What is meant by query processing and query optimization?
|
[RE]
|
CO2
|
15
|
Brief about Boyce-Codd normal form
|
[RE]
|
CO3
|
16
|
Write short notes on features of Good relational designs.
|
[UN]
|
CO3
|
17
|
Define Transaction. How transactions access the data?
|
[UN]
|
CO4
|
18
|
Brief about Lock Manager.
|
[RE]
|
CO4
|
19
|
What are uses of indices? Define sparse and dense indices.
|
[UN]
|
CO5
|
20
|
How to improve the speed of access to blocks?
|
[RE]
|
CO5
|
Q.No
|
Part- C ( 2 x 14 = 28 Marks)
|
Bloom’s Level
|
COs
|
21.
|
(a) i.What are the functions of Database Administrator and describe the views of data? (7)
|
[UN]
|
CO1
|
ii. Enumerate purposes of Database management systems. (7)
|
[UN]
|
CO1
|
OR
|
(b) Explain the architecture of DBMS with neat sketch. (14)
|
[UN]
|
CO1
|
22.
|
(a) A university registrar’s office maintains data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram forthe registrar’s office. Document all assumptions that you make about the mapping constraints. (14)
|
[AP]
|
CO2
|
OR
|
b)i) Write the equivalence rules of the query optimization (7)
|
[UN]
|
CO2
|
|
ii)Consider the following relational schema for a company database application:EMPLOYEE(ENO, NAME, GENDER, DOB, DOJ, DESIGNATION, BASIC,DEPT_NO, PAN, SENO)
Implement a Check Constraint for GENDERPAN – Permanent account Number SENO –Supervisor Employee Number
DEPARTMENT (DEPT_NO, NAME, MENO) MENO - ManagerEmployee Number
PROJECT (PROJ_NO, NAME, DEPT_NO)WORKSFOR (ENO, PROJ_NO,DATE_WORKED, HOURS)
The primary keys are underlined. Identify the foreign keys and draw schema diagram and Create the above mentioned tables and populate the values. (7)
|
[AP]
|
CO2
|
23
|
Describe Relational algebra and relational calculus (14)
|
[UN]
|
CO3
|
OR
|
|
|
i)Normalize the following schema with given constraints to 4NF.
books(accessionno,isbn,title,author,publisher)
users(userid,name,deptid,deptname)
accessionno →isbn
isbn →title
isbn →publisher
isbn →→ author
userid →name
userid →deptid
deptid →deptname (7)
|
[AP]
|
CO3
|
ii) Write the Functional -Dependency Theory (7)
|
[UN]
|
CO3
|
24
|
a)i) Consider the timestamp-ordering protocol, and two transactions, one that writes two data items p and q, and another that reads the same two data items. Give a schedule whereby the timestamp test for a write operation fails and causes the first transaction to be restarted, in turn causing a cascading abort of the other transaction. Show how this could result in starvation of both transactions. (7)
|
[AP]
|
CO4
|
ii) Describe the usage of log record in recovery and atomicity. (7)
|
[UN]
|
CO4
|
OR
|
|
|
b)Consider the following two transactions:
T1: read(A);
read(B);
if A = 0 then B := B + 1;
write(B).
T2:read(B);
read(A);
if B = 0 then A := A + 1;
write(A).
Let the consistency requirement be A = 0 ∨ B = 0, with A = B = 0
the initial values.
i. Show that every serial execution involving these two transactions
preserves the consistency of the database.
ii. Show a concurrent execution of T1 and T2 that produces a nonserializable schedule.
iii. Is there a concurrent execution of T1 and T2 that produces a serializable schedule? (14)
|
[AP]
|
CO4
|
25
|
(a) Explain Different RAID levels with suitable example. (14)
|
[UN]
|
CO5
|
OR
|
b) Write the structure of the B+ tree, insertion, updation and deletion operation with an example (14)
|
[UN]
|
CO5
|
The following course outcome are assessed:-
|
COURSE OUTCOMES
|
MARKS
|
CO1
|
Describe the fundamental elements of relational database management systems
|
34
|
CO2
|
Explain the basic concepts of relational data model, entity-relationship model, relational database design, relational algebra and SQL.
|
34
|
CO3
|
Design ER-models to represent simple database application scenarios
|
34
|
CO4
|
Convert the ER-model to relational tables, populate relational database and formulate SQL queries
|
34
|
CO5
|
Familiar with basic database storage structures and access techniques
|
34
|
|
Bloom’s level
|
Keyword
|
Marks
|
|
Prepared by
Dr.M.P.Thiruvenkatasuresh, P/CSE
|
Remembering
|
RE
|
16
|
Approved by
Dr.G.Sivakumar
HOD/CSE
|
Understanding
|
UN
|
104
|
Applying
|
AP
|
50
|
Scrutinized by
Dr.G.Sivakumar
HOD/CSE
|
Analyzing
|
AN
|
-
|
Evaluating
|
EV
|
-
|
Creating
|
CR
|
-
|
TOTAL
|
170
(Including Choices)
|
Share with your friends: |
