Database Management System
Exam Duration: 45 Mins Total Questions : 30
The process where the same entity member of more than one subclass of the specialization is known as
- (a)
disjoint
- (b)
overlapping
- (c)
mapping
- (d)
snapshot
The process where the same entity member of more than one subclass of the specialization is known as overlapping.
Given the functional dependencies
\(X\rightarrow W;\quad X\rightarrow Y;\quad Y\rightarrow Z\quad and\quad Z\rightarrow PQ\)
Which of the following does not hold good?
- (a)
\(X\rightarrow Z\)
- (b)
\(W\rightarrow Z\)
- (c)
\(X\rightarrow WX\)
- (d)
None of these
\(So,\quad W\rightarrow Z\quad does\quad not\quad hold\quad good.\)
What are the potential problems when a DBMS executes multiple transcations concurrently?
1.The lost update problem
2.The dirty read problem
3.The unrepeatable read problem
4.The phantom problem
- (a)
3 and 4
- (b)
1, 2 and 4
- (c)
2 and 3
- (d)
All of these
The five aggregation operators in SQL are
- (a)
SUM, AVG, IN, DISTINCT, COUNT
- (b)
SUM, AVG, MIN, MAX, COUNT
- (c)
SUM, AVG, MIN, MAX, DISTINCT
- (d)
SUM, AVG, IN, ALL, ANY
Five aggregation operators in SQL
SUM, AVG, MIN, MAX, COUNT
These operators are used by applying them to a scalar valued expression, typically a column name in a SELECT clause.
Which of the following schemes are used for ensuring atomicity?
- (a)
Log with deferred modification
- (b)
Log with intermediate modification
- (c)
Show paging
- (d)
All of the above
Following ensures atomicity:
- Log with deferred and immediate modification
- Shadow paging
Consider the set of relations given below and the SQL query that follows:
Student:(Roll_number,Name,date_of_birth)
Courses:(Course_number,Course_name,Instructor)
Grades:(Roll-number,Course_number,Grade)
Select DISTINCT Name from students.Roll_number = Grades.Roll number AND course.Instructor=Korth AND Course.Courese_number = Grade.
Course_number AND Grades.Grade = A
Which of the following sets is computed by the above query?
- (a)
Names of students who have got an A grade in all courses taught by Korth
- (b)
Names of students who have got A grade in all course
- (c)
Names of students who have got an A grade in at least of the course taught by Korth
- (d)
None of the above
When each of 120 students enrolls for each of the 8, the maximum number of tuples will be \(\\ \\ 120\times 8=960\\ \) .When all the 120 students enroll for the same coyurse then there will bw minimum tuples.The minimum numbles of tuples = \(120\times 1=120\)tuples.
In SQL, which command is used to change a table's storage characteristics?
- (a)
CHANGE TABLE
- (b)
MODIFY TABLE
- (c)
ALTER TABLE
- (d)
None of these
To change table storage structure, we use DDL command. We use for this ALTER command of DDL.
The relational algebra expression equivalent to the following expression:
\(\{ t/t\epsilon r\cap (t[A]=10\cup t[B]=20)\} \)
- (a)
\(\delta _{ (A-10\cup B=20) }(r)\)
- (b)
\(\sigma _{ (A=10) }(r)\cup \sigma _{ (B=20) }(r)\)
- (c)
\(\sigma _{ (A=10) }(r)\cap \sigma _{ (B=20) }(r)\)
- (d)
\(\sigma _{ (A=10) }(r)-\sigma _{ (B=20) }\)
The experssion
\(\{ t/t\epsilon r\cap (t[A]=10\cup t[B]=20)\} \)
can be represented as relational algebra as
\(\sigma _{ (A=10) }(r)\cup \sigma _{ (B=20) }(r)\)
It says that select those rows from relation r, where A = 10 union and B = 20
Which of the following is not a low high level operator?
- (a)
Join
- (b)
Project
- (c)
Update
- (d)
Select
Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?
- (a)
{M1,M2,M3,P1}
- (b)
{M1,P1,N1,N2}
- (c)
{M1,P1,N1}
- (d)
{M1,P1 }e
M1 | M2 | M3 | P1 |
and
P1 | P2 | N1 | N2 |
A functional dependency x\(\rightarrow \)y is trivial, if
- (a)
\(X\supseteq Y\)
- (b)
\(Y\supseteq X\)
- (c)
\(X\supset Y\)
- (d)
\(Y\supset X\)
A functional dependency X\(\rightarrow \)Y is trivial if \(X\supseteq Y\).
Consider the relation Student (name, sex, marks),where the primary key is shown underlined pertaining to students in a class that has at least one boy and one girl.What does the following relational algebra expression produce?(Note: \(\rho \) is the rename operator)
\(\Pi _{ name }(r_{ sex=female }(Student)-{ \pi }_{ name }(Student)\)
\(\quad \bowtie \) pn,x,m (student)
sex=female
\(\wedge x=male\\ \wedge marks\le m\)
- (a)
names of girl students with the higher marks
- (b)
names of girl students with more marks than some boy student
- (c)
names of girl students with marks not less than some boy student
- (d)
names of girl students with more marks than all the boy students
The sub-expression \(\\ \\ { \pi }_{ name }\\ \)(student \(\quad \bowtie \) Pn,X,m(student)
(sex=female)
\(\wedge x=male\\ \wedge marks\le m\)
Given , the name of female students who have less marks than other boys.
The expression \(\\ \\ { \pi }_{ name }\\ \)(rsex-Female(Student)) gives the name of all the female student from the above result.
Which of the following statements is false?
- (a)
Any relation can be decomposed into a number of relations that are in third normal form, such decomposition is lossess and preserves the depencies
- (b)
Any relation can be decomposed losslessly intop relations in the BCMF and decomposition into BCNF always preserve dependency
- (c)
The decomposition approach using the BCNF may produce inter relational join constraints
- (d)
A decomposition of relation, into BCNF is not unique
A relation Empdtl is defined with attribute empcode(unique) name, street, city, state and pincode. For any pincode.In normalization terms, Empdtil is a relation in
- (a)
1NF only
- (b)
2NF and hence also in 1NF
- (c)
3NF and hence also in 2NF and 1NF
- (d)
BCNF and hence also in 3NF,2NF and 1NF
From the given conditions FD's are 1NF
Pincode->city state 1 NF
City street->Pincode 3 NF 1NF
Consider a relation R with five attributes V,W,X,Y, and Z.The following functional dependencies hold: VY->W, WX->Z, and ZY->V. Which of the following is a candidates key for R?
- (a)
VXZ
R(V,W,X,Y,Z)
VY->W
WX->Z
ZY->V
V+={V}
VY+={V,Y,W}
VYX+={V,Y,X,W,Z}
Consider a relation schema R = (N, R, C, T, Z) on which the following functional dependencies hold \(\{ N\rightarrow RCT,\quad RCT\rightarrow Z,\quad Z\rightarrow CT\} \) then what is/are the candidate keys of R1?
- (a)
N
- (b)
R
- (c)
Z, N
- (d)
C, T
R1= (N, R, C, T, Z)
FD = \(\{ N\rightarrow RCT,\quad RCT\rightarrow Z,\quad Z\rightarrow CT\} \)
N+ = {N R C T Z}
So, N is candidate key.
Let R={A, B, C, D} and
F={
A\(\rightarrow \)B
A\(\rightarrow \)C
BC\(\rightarrow \)D
}
then A\(\rightarrow \)D is in F+
- (a)
A\(\rightarrow \)D is in F+ is always true
- (b)
A\(\rightarrow \)D is in F+ but for that D\(\rightarrow \)A must be satisfied
- (c)
A\(\rightarrow \)D is not in F+
- (d)
None of the above
A\(\rightarrow \)B
A\(\rightarrow \)C
BC\(\rightarrow \)D
F+=A\(\rightarrow \)BCD
So, A\(\rightarrow \)D will be in F+.
Consider the following relation scheme pertaining to the information about a student maintained by an university:
STDINF (Name, Course, Phone no.Major, Prof, Ggrade) then this scheme is decomposed into the relation schemes
STUDENT (Name, Phone no., Major, Grade)
Course (Course, prof)
Then select correct statement if, it is a bad decomposition
- (a)
due to loss of information
- (b)
due to redundancy and update anomaly
- (c)
The above decomposition is not at all bad
- (d)
Both a and b
Loss of information; because we lose the fact that a student has a given grade in a particular course.
Redundancy and update anomaly because the data for the attributes Phone_no. and Major are repeated.
A lack of normalization can lead to which one of the following problems?
- (a)
Insertion problems
- (b)
Deadlock
- (c)
Lost up dates
- (d)
Deferred updates
A lock of normalization can lead to deferred updates.
Consider the relation R (A, B, C, D, E, F, G) satisfies the following functional dependencies
\(A\rightarrow B,BC\rightarrow DE,AEF\rightarrow G\) Which of the following statements is true?
- (a)
BC\(\rightarrow \)DC
- (b)
ACF\(\rightarrow \)DG
- (c)
A\(\rightarrow \)DG
- (d)
B\(\rightarrow \)DG
\(A\rightarrow B,BC\rightarrow DE,AEF\rightarrow G\)
Candidate key ACF
BC\(\rightarrow \)DG is not possible as ACF is candidate key.
ACF\(\rightarrow \)DG is possible as ACF is candidate key.
In SQL, relations can contain null values and comparisons with null values are treated as unknown. Suppose all comparisons with a null values are treated as false. Which of the following pairs are not equivalent?
- (a)
X = 5 not (not(X = 5)
- (b)
X = 5 X > 4 and X < 6, where X is an interger
- (c)
X \(\neq \)5 not (x = 5)
- (d)
None of the above
X = 5 and not (not(n = 5)
X = 5 and X > 4 and X < 6, X is interger
X \(\neq \)5 and not (x = 5) all are equivalent
Information about a collection of students is given by the relation studinfo ( \(\underline { studid, } \)name,sex). The relation enroll (\(\underline { studid,courseld } \)) gives which student has enrolled for (or taken) what course(s). Assume that every course is taken by atleast one male and at least one female student. What does the following relational algebra expression represent?
\({ \prod }_{ courseid }(({ \prod }_{ studid }(\sigma _{ sex="female" }(studinfo))\\ \quad \quad \quad \quad \quad \quad \quad \quad \quad \quad \quad \times { \pi }_{ courseld }(enroll))-enroll)\)
- (a)
Courses in which all the female students are enrolled
- (b)
Courses in which a proper subset of female students are enrolled
- (c)
Courses in which only male students are enrolled
- (d)
None of the above
Given,
studinfo (\(\underline { studid,name,sex } \))
enroll (\(\underline { studid,courseid } \))
\({ \prod }_{ courseid }(({ \prod }_{ studid }(\sigma _{ sex="female" }(studinfo))\\ \quad \quad \quad \quad \quad \quad \quad \quad \quad \quad \quad \times { \pi }_{ courseld }(enroll))-enroll)\)
To find The statement represented by the relational algebra expression
Solution (\((σsex="female"\)(studinfo)) tells that students are females
\(∏courseid\) (enroll)) gives the id of the couse in which to enroll
\(∏studid\)tells regarding a proper subset of females that are entrolled
Finnaly,
\(∏courseid\) tells Course in which a proper subset of female students are enrolled.
Isolation is managed by
- (a)
transaction management component
- (b)
recovery management component
- (c)
concurrency control component
- (d)
None of the above
Isolation is managed by concurrency control component
Consider the relation employee (\(\underline { name } \), sex, supervisorName) with name as the key.supervisorName gives the name of the supervisor of the employee under consideration. What does the Following Tuple Relational Calculus query produce?
{e.name|employee (e) \(\bigwedge \)}
\((\forall x)\quad [employee(x)\vee \quad x.supervisorName\\ \quad \quad \quad \quad \quad \quad \quad \quad \quad \quad\neq x.sex="male"]\} \)
- (a)
Names of employees with a male supervisor
- (b)
Names of employees with no immediate male
- (c)
Names of employees with no immediate female subordinate
- (d)
Names of employees with a female supervisor
Given employee ((\(\underline { name } \), sex, supervisorName)
{e.name|employee (e) \(\bigwedge \)}
\((∀x)[employee(x)∨x.supervisorName\)\(≠x.sex="male"]}\)
To find The resulant of tuple relational calculus
Solution We are given with below relational calculus
{e.name|employee (e) \(\bigwedge \)}
\((∀x)[employee(x)∨x.supervisorName\)\(≠x.sex="male"]}\)
Which of the following is wrong?
- (a)
\(\Pi _{ L1\cup L2 }(E_{ 1 }\bowtie _{ 0 }E_{ 2 })=(\Pi _{ L1 }(E_{ 1 }))\bowtie _{ \theta }[\Pi _{ L1 }(E_{ 2 }))]\)
- (b)
\(\sigma _{ P }(E_{ 1 }-E_{ 2 })=\sigma _{ P }(E_{ 1 })-E_{ 2 }\)
- (c)
\(\sigma _{ \theta 1\cap \theta 2 }(E)=\sigma _{ \theta 1 }(\sigma _{ \theta 1 }(\sigma _{ \theta 2 }(E]\)
- (d)
\(E_{ 1 }\bowtie \theta E_{ 2 }=E_{ 2 }\bowtie \theta _{ 1 }E_{ 1 }\)
\(E_{ 1 }\bowtie \theta E_{ 2 }=E_{ 2 }\bowtie \theta _{ 1 }E_{ 1 }\)is not equivalent because \(\theta \)join is not commutative. Hence, the entries in \(E_{ 1 }\bowtie \theta _{ 1 }E\) and \(E_{ 2 }\bowtie \theta _{ 1 }E_{ 1 }\) may not be same
With respect to the following schedule, which of the following option is true?
T1 | T2 |
Lock X(B) | |
Read (B) | |
B=B-50 | |
Write (B) | |
lock-S(A) | |
Read(A) | |
Lock-S(B) | |
Lock-X(A) |
- (a)
deadlock can occur
- (b)
deadlock cannot occur
- (c)
cannot say
- (d)
None of the above
\({ T }_{ 2 }\overset { waiting\quad for }{ \rightarrow } { T }_{ 1 }\quad \)
because T1 holds exclusive lock on B and T2 wants to held a shared lock on it.
\({ T }_{ 1 }\overset { waiting\quad for }{ \rightarrow } { T }_{ 2 }\)
T2 holds shared lock on A and T1 wants to hold in exclusive lock on it.
In vigorous two phase locking protocol a transaction
- (a)
release exclusive locks only at the end of transaction
- (b)
release all locks only at the beginning of transaction
- (c)
acquired all locks at the beginning of transaction
- (d)
acquired all exclusive locks at the beginning of transaction
Which of the following is true?
- (a)
A file can have atmost one primary index or atmost one clustering index
- (b)
A file can have at least one primary index or atleast one clustering index
- (c)
A file can have both primary index and clustering index
- (d)
None of the above
Let r be a relation instance with schema R=(A, B, C, D). We define r1 =\({ \Pi }_{ A,B,C }(r)\) and r2 =\({ \Pi }_{ AD }(r)\). Let S = r1 * r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is true?
- (a)
\(s\subset r\)
- (b)
\(r\cup s=r\)
- (c)
\(r\subset s\)
- (d)
\(r*s=s\)
Given, r be a relation instance
R = (A, B, C, D)
\({ \Pi }_{ A,B,C }(R)\) and \({ \Pi }_{ AD }(r)\)
s = r1 * r2
* denotes natural join
decomposition of r into r1 and r2 is lossy
To find: True condition
Solution:
R = (A, B, C, D)
\(r_{ 1 }={ \Pi }_{ A,B,C }(R)\) and \(r_{ 2 }={ \Pi }_{ AD }(r)\)
s = r1 * r2
The tuples in s are more than that of r. Also, s consists of all the tuples of r along with other tuples. Therefore the most appropriate relation is \(r\subset s\).
The employee information in a company is stored in the relation
Employee (name,sex, salary, depthName)
Consider the following SQL query
Select depthName
From Employee
Where sex = 'M'
Group by deptName
Having avg (salary)>
(select avg (salary) from Employee)
It returns the name of the department in which
- (a)
the average salary is more than the average salary in the company
- (b)
the average salary of all male employees is more than the average salary of all male employees in the company
- (c)
the average salary of male employees is more than the average salary of employees in the same department
- (d)
the average salary of male employees is more than the average salary in the company.
Given, Employee(name, sex, salary, depthname)
SQL query
Select deptName
From Employee
Where sex= 'M'
Group by deptName
Having avg (salary) >
(select avg (salary) from Employee)
To find Feature of the department whose name is returned
Solution SQL query will compute as follows:
1. Selects the name from the department
2. Selects the male employee
3. Computes that
The average salary of male employees is more than the average salary in the company