DBMS Test - 4 - PDF Flipbook
DBMS Test - 4
288 Views
26 Downloads
PDF 5,971,027 Bytes
GATE
CSE
Database
ManagementSystem
Test-04Solutions
DATABASE MANAGEMENT SYSTEM
1. Which one of the following statements about normal forms is
FALSE?
a) BCNF is stricter than 3NF
b) Lossless, dependency preserving decomposition into 3NF is
always possible
c) Lossless, dependency preserving decomposition into BCNF
is always possible
d) Any relation with two attributes is in BCNF.
Answer: (c)
Solution:
Achieving lossless and dependency preserving BCNF
decomposition is a rate case. Getting Two of them in a
decomposition is a rare case.
2. Match the following:
List-I List-II
A. DDL 1. LOCK TABLE
B. DML 2. COMMIT
C. TCL 3. natural difference
D. BINARY operation 4. REVOKE operation
Codes:
ABC D
a) 2 1 3 4
b) 1 2 4 3
c) 3 2 1 4
1
d) 4 1 2 3
Answer: (d)
3. Consider the table Student (stu id' name' course' marks) which
one of the following two queries is correct to find the highest
marks student in course 5?
Q1. Select S. stuid
From student S
Where not exist
(select * from student e where e course =’5’
And e marks ≥ S marks)
Q2: select S.stu.id
From student S
Where S. marks >any
(Select district marks from student S where S. course =5)
a) Q1
b) Q2
c) Both Q1 and Q2
d) Neither Q1 nor Q2
Answer: (d)
Solution:
Both the queries are given not correct since, first query will not
retrieve any tupple since ≥ condition is used second query gives
stuid of students whose marks are greater than any who is
student taking course 5.
2
Q1. Select S. stuid
From student S
Where not exist
(select * from student e where e course =’5’
and e marks > S marks)
Q2: select S.stuid
From student S
Where S. marks ≥ all (select distic marks
From student S
Where S. course = ‘5’)
4. The capability maturity model (err) def lines 5 levels:
List-I List-II
A. Leve l 1 1. managed
B. Level 2 2. Defined
C. Level 3 3. Repeatable
D. Level 4 4. Initial
E. Level 5 5. Optimized
Correct matching is:
Codes:
AB CDE
a) 1 2 3 4 5
b) 4 3 2 1 5
c) 5 1 3 2 4
d) 5 2 1 3 4
Answer: (b)
3
Solution:
Capability maturity model is a model is a methodology used to
develop and refine an organization’ software development
process CMM 5 process maturity levels based on certain key
process Area(KAP)
1. Initial(chaotic, adhoc, individual heroics) is starting point for
use of new or undocumented repeat process
2. Repeatable is process is at least documented sufficiently may
be attempt
3. Defeated is the process is defeated/ confirmed as a standard
business process
4. Managed is the process is quantitatively manages in
accordance with agreed upon metrics
5. Optimization is process management includes deliberate
process optimization/ improvement
5. Which command classes text file, which has been created using
"SETALTERNATIVE' 'Command"?
a) SET ALIERNATE OFF
b) Close DATABASE
c) CLOSE ALTERNATE
d) CLEAR ALL
Answer: (a)
Solution:
Syntax:
Set alternative on/off
4
Set alternative
6. Which of the following commands would return process id of
sleep command?
a) Sleep 1 and echo $?
b) Sleep I and echo $#
c) Sleep I and echo $x
d) Sleep .1 and echo $!
Answer: (d)
Solution:
Sleep 1 and echo$! Will return the process_id of sleep command
7. A relational database contains two tables student and
department in which student table has columns roll_no, name
and deptid and department table has columns deptid and
dept_name. The following insert statements were executed
successfully to populate the empty tables:
Insert into department values (1, ‘Mathematics’)
Insert into department values (2, ‘physics’)
Insert into student values (1, ‘Navin’, 1)
Insert into student values (2, ‘mukesh’, 2)
Insert into student values (3, ‘Gita’, 1)
How many rows and columns will be retrieved by the following
SQL statement?
Select * from student, department
a) 0 row and 4 columns
b) 3 rows and4 columns
5
c) 3 rows and 5 columns
d) 6 rows and 5 columns
Answer: (b)
Solution:
It is natural join, you get the below rows and columns
Roll_No name Dept Id Dept_name
1 Naveen 1 Mathematics
2 Mukesh 2 Physics
3 Gita 2 Physics
8. The maximum number of super keys for the relation schema R (
E, F, G, H ) with E as key is_____.
Answer: (8)
Solution:
If E is the candidate. key, the number of super keys are 8 i.e. E,
EF, EG, EH, EFH, EFG, EGH, EFGH.
9. Data warehousing refers to
a) storing data offline at a separate site
b) backing up data regularly
c) is related to data mining
d) uses tape as opposed to disk
Answer: (c)
Solution:
A data ware house is a system used for reporting and data
analysis
6
Considered core component of busied intelligence Data mining is
also
used to turn information into Actionable knowledge
The relationship between data mining and data warehousing is
that data property warehoused is easier to mine
10. Consider the relations R (A, B) and S (B, C) and the following
four relational algebra queries over B and S:
I. A, B(R⋈S)
II. R⋈ A, B(S)
III. R ∩( A(R)* B) (S))
IV. A, B, C (R *S) where R: B refers to the column B in table R.
Once can determine that:
a) I, III and IV are the same quality
b) II, III and IV are the same quality
c) I, II and IV are the same quality
d) I, II and III are the same quality.
Answer: (d)
Solution:
Take an example:
i)
7
ii)
iii) A(R)× B(S)
iv) R×S =
Therefore (i), (ii), (iii), are equal
11. Given the following statements:
S1: A foreign key declaration can always be replaced by an
equivalent check assertion in SQL.
S2: Given the table R(a, b, c) where a and b together form the
primary key, the following is a valid table definition.
CREATE TABLE S(
a. INTEGER,
b. INTEGER,
e. INTEGER,
8
PRIMARY KEY(d),
FOREIGN KEY (a) reference R)
a) Which one of the following statements is CORRECT? S1 is
TRUE and S2 is FALSE.
b) Both S1 and S2 are TRUE.
c) S1 is FALSE and S2 is TRUE.
d) Both S1 and S2 are FALSE.
Answer: (d)
Solution:
S1: A foreign key declaration can't be replaced with a check
constraint.
S2: Foreign key always refers the primary key of the referenced
relation. In table 'S' only 'a' is Foreign key that refers primary
key (ab) in R which is incorrect.
12. Consider the relation account (customer, balance) where
customer is a primary key and there are no null values. We
would like to rank customers according to decreasing balance.
The customer with the largest balance gets rank 1. Ties are not
broken but ranks are skipped: if exactly two customers have the
largest balance they each get rank 1 and rank 2 is not assigned.
9
Consider these statements about Query 1 and Query 2.
1. Query I will produce the same row set as Query 2 for some
but not all databases
2. Both Query I and Query 2 are correct implementations of the
specification
3. Query 1 is a correct implementation of the specification but
Query 2 is not
4. Neither Query 1 nor Query 2 is a correct implementation of
the specification
5. Assigning rank with a pure relational query takes less time
than scanning in decreasing balance order and assigning
ranks using ODBC
Which two of the above statements are CORRECT?
a) 2 & 5
b) 1 & 3
c) 1 & 4
d) 3 & 5
Answer: (c)
Solution:
If there are no duplicates of balance, then they produce same
row set else not (See table above). Both are not producing
desired output.
10
13. Which of the following tuple relational calculus expression(s)
is/are equivalent to
I. ¬∃t∈r(P(t))
II. ∃t∉ r(P(t))
III. ¬∃t ∈r(¬P(t))
IV. ∃t∉r(¬P(t))
a) I only
b) II only
c) III only
d) III and IV only
Answer: (d)
11
Solution:
Output of III, IV and output of expression in question are all
equivalent.
14. in DBMS, deferred update mean
a) All the updates are done first but the entries are made in the
log file later
b) All the log files entries are made first but the actual updates
are done later
c) Every update is done first followed by a writing on the log
file
d) Changes in the views are deferent till a equality ask for a
view
Answer: (b)
Solution:
Deferent update as called No UNDER/REDO is a technique
used to recover/support transition failures, when a transition
runs, any updates or alternations made to the database are not
done immediately. They are recorded in the log file Data
changes recorded in the log file are applied to the database on
commit
15. One approach to handling fuzzy logic data might be to design
computer using ternary (base 3) logic so that data could be
stored as "true", "false", and "unknown." If each ternary logic
element is called a flit, how many flits are required to represent
at least 256 different values?
12
a) 4
b) 5
c) 6
d) 7
Answer: (c)
Solution:
Logic used: Ternary
Number of values to be represent = 256
It Take felt= 5, 35 = 243, which is sufficient to hold 256, So,
answer will be, since 36 = 729, which is sufficient to hold 256
values
16. Match the following:
List-I
i. Correctness
ii. Accuracy
iii. Robustness
iv. Completeness
List-II
1. The extent to which a software tolerates the unexpected
problems
2. The extent to which a software meets it specifications
3. The extent to which a software has specified functions
4. Meeting specifications with precision
13
Codes:
ABCD
a) 2 4 1 3
b) 1 2 3 4
c) 2 1 4 3
d) 4 2 1 3
Answer: (a)
Solution:
Software quality attributes:
(i) Correctness is the extent to which a software meets its
specifications
(ii) Accuracy meets specifications with precision
(iii) Robustness is the extent to which a software tolerates the
unexpected programs
(iv) Completeness is the extent to which a software has
specified functions
17. A super key for an entity consists of:
a) one attribute only
b) at least two attributes
c) at most two attributes
d) one or more attributes
Answer: (d)
14
Solution:
A super key is a combination of Columns that uniquely
identifies any row within a relation database management
system (RDBMS) table.
A candidate key is closely related concept where the super key
is reduced to the minimal set of column required to uniquely
identify each row.
18. Consider the schema R (A, B, C, D) and the functional
dependencies A→B and C→D. If the decomposition is made as
R1 (A, B) and R2(C, D), then which of the following is TRUE?
a) Preserves dependency but cannot perform lossless join
b) Preserves dependency and performs lossless join
c) Does not perform dependency and cannot perform lossless
join
d) Does not preserve dependency but perform lossless join
Answer: (b)
Solution:
R(A, B, C, D) with functional dependency A→ B and C →D,
Decomposition into R1(A, B) with functional dependency A→B
and R2(C, D) functional dependency C→D since dependency is
Preserved but decomposition related are preserved but
decomposition relation are not since R1 intersecting R2 = phi
15
19. The relation schemas r? and Reform a Lossless join
decomposition of R if and only if:
I. R1 ∩ R2 (R1- R2)
II. R1 R2
III. R1∩ R2 (R2- R1)
IV. R2 R1∩ R2
a) (i)and (ii)happens
b) (i) and (iv) happens
c) (i) and (iii) happens
d) (ii) and (iii) happens
Answer: (c)
Solution:
Let F be a set of functional dependency on R1, and R1 and R2
from ad decomposition of R. the decomposition is a lossless join
decomposition of R if at least one of the following functional
dependencies are in F+;
1) R1∩ R2→ R1
2) R2∩ R2→ R2
20. Which level is called as "defined" in capability maturity model?
a) level0
b) level3
c) level4
d) level 1
Answers: (b)
16
Solution:
(CMM) is a methodology used to develop are refine an
organization software development process, the model describes
a five level evolutionary path of increasingly and systematically
more nature processes there are:
Level 1: initial level
Level 2: Repeatable level
Level 3: Defined level\Leve; 4: Managed level
Level 5: Optimizing level
21. Integrity constraints ensure that changes made to the database by
authorized users do not result into loss of data consistency.
Which of the following statement(s) is (are) rue w.r.t. the
examples of integrity constraints?
I. An instructor ld. No. cannot be null, provided instructor id.
No. being primary key.
II. No two citizens have same Aadhar-ld.
III. Budget of a company must be zero.
a) (i), (ii) and (iii) are true.
b) (i) false, (ii) and (iii) are true.
c) (i) and (ii) are true; (iii) false.
d) (i), (ii) and (iii) are false.
Answer: (c)
Solution:
According to integrity constraints no two records can have same
values and primary key value can or be null(o=)) so, here
17
instructor id, which is primary key cannot be null, since adhar r
id is also unique for two person so two citizens have same
Aaadhar-id but budget of company cannot be null, since
company exist so budget value must be non-null
22. Software Quality Assurance (SEA) encompasses:
a) verification
b) varication
c) both verification and validation
d) none of the above
Answers: (c)
Solution:
Software quality assurance(SAQ) concern of every software
engineer to reduce const and improve product time –to-market
A software quality assurance plan is not merely another name
for a test plan though test plans are included in an SAQ plan
SAQ activates are plat formed on every software project SAQ
encompasses the entire software development process
23. Data Warehouse provides
a) Transaction Responsiveness
b) Storage, Functionality Responsiveness to queries
c) Demand and Supply Responsiveness
d) None of the above
Answer: (b)
18
Solution:
The increasing processing power and sophistication of analytical
tools and techniques have results in the development of what are
known as data ware house these data ware houses provide
storage functionally and responsiveness to queries behind the
capabilities of transitions oriented database
24. What does the following SQL query output?
Select sch-name, count(*),
FROM school C, Enrolment E,
Exam Result R
Where E, school_id = C. school_id
AND
E.enrollno = R. examname
AND
E.erollno = R.enrollno
AND
E.erollno = R.enrollno
AND
R.marks = 100
AND
S.school_id IN (SELECT school-id
FROM student
GROUP BY school-id
HAVING COUNT (*)> 200
GROUP BY SCHOOL –id)
19
a) for each school with more than 200 students appearing in
exams, the name of the school and the number of 100s
b) for each school with more than 200 students in it, the name of
the school and the number of 100s scored by its students
c) for each school with more than 200 students in it, the name of
the school and the number of its students scoring 100 in at
least one exam
d) Nothing; the query has a syntax error.
Answer: (d)
Solution:
If SELECT clause consists aggregate and non-aggregate
columns All non-aggregate columns in the SELECT list must
appear in Group by clause. But in this query Group by consists
school id instead of such name.
25. Suppose a database schedule S involves transactions T1, ..., Tn.
Construct the precedence graph of S with vertices representing
the transactions and edges representing the conflicts. If S is
serializable, which one of the following orderings of the vertices
of the precedence graph is guaranteed to yield a serial schedule?
a) Topological order
b) Depth-first order
c) Breadth-first order
d) Ascending order of transaction indices
Answer: (a)
20
Solution:
If a schedule is serializable, the topological order of a graph
(precedence graph) yields a serial schedule.
26. An Assertion is a predicate expressing a condition we wish
database to always satisfy. The correct syntax for Assertion is:
a) CREATE ASSERTION 'ASSERTION Name' CHECK
'Predicate'
b) CREATE ASSERTION 'ASSERTION Name'
c) CREATE ASSERTION CHECK predicate
d) SELECT ASSERTION
Answer: (a)
Solution:
An assertion in SQL takes from syntax create assertion
check
27. Match the following:
List-I List-II
A. OLAP 1. Regression
B. OLTP 2. Data Warehouse
C. Decision Tree 3. RDBMS
D. Neural Network 4. Classification
Codes:
ABCD
a) 2 3 1 4
b) 2 3 4 1
c) 3 2 1 4
21
d) 3 2 4 1
Answer: (b)
Solution:
In OLTP database there detailed current data, in OLTP database
schema used to store transactional data (usually 3NF) OLAP
(Online Analytical Processing) is characterized by relatively
low volume of transition
Decision tree is used for classification of data in data min ing
and AI
Neural networks are used for the purpose of unsupervised
learning, regression of classification
28.Which of the following is false?
a) Every binary relation is never be in BCNF.
b) Every BCNF relation is in 3NF.
c) 1 NE 2 NF, g NF and BCNF are based on functional
dependencies.
d) Multivalued Dependency (MVD) is a special case of Join
Dependency (JD).
Answer: (a)
29.ln multiuser database if two users wish to update the same
record at the same time, they are prevented from doing so by
a) Jamming
b) Password
c) Documentation
d) Record lock
22
Answer: (d)
Solution:
Record locking is the technique of evening simultaneous access
to data in a database to prevent inconsistent results. If a collision
is occurring, both of the senders will send a jamming signal over
the Ethernet.
30. Consider a schema R (A, B, C, D) and functional
Dependencies AB and CD Then the
Decomposition R1(A, B) and R2(C, D) is
a) Dependency preserving but not lossless join
b) Dependency preserving and lossless join
c) Lossless Join but not dependency preserving
d) Lossless Join
Answer: (a)
Solution:
Quantization, in image processing system, is a lossy
compression technique achieved by compression a range of
values to signal quantum value. When the number of discrete
symbols in a given stream is reduced the stream becomes more
compressible.
23
Data Loading...