DBMS & SQL Previous Year Questions

Explanation

In searching for an element in hash table requires O(1) time for the average time, whereas for direct it holds for the worst-case time. So the correct answer is option (c).

Explanation

Clustered index determines the order in which the rows of a table are stored on disk. If a table has a clustered index. Then the rows of that table will be stored on disk in the same exact order as the clustered index. In some cases, the index is created on non-primary key columns which may not be unique for each record. Clustering index is defined as an ordered data files.

Explanation

A relation is in BCNF, if and only if, every determinant is a candidate key i.e. the left hand side of every non trivial dependency is a super key or there is a trivial dependency or both. After 3NF, there may arise a situation that two candidate keys are having one attribute in common (overlapping) and this overlapping must be cleared to satisfy BCNF. A prime attribute (an attribute that is a part of a candidate key) can on depend on a super key. So a prime attribute cannot be transitively dependent on a key in BCNF relation. So the false statement is option (d).

Explanation

This is a theorem in universal hashing: if h is chosen from a universal collection and is used to hash n keys into a table of size m, where n ≤ m, the expected number of collisions involving a particular key is less than 1. So the answer is (a).

Explanation

T(n) = 3T(n/4) + n. Here a = 3, b = 4, f(n) = n and n^(log₄3) ≈ n^0.79. Since f(n) = n grows polynomially faster than n^(log₄3), case 3 of the master theorem gives T(n) = Θ(n). So T(n) = O(n) (it is also O(n²), but (c) is the tight bound and the best choice).

Explanation

Option (b) is NOT CORRECT as the number of request for disk drive can be greatly influenced by file allocation method.

Explanation

Super block describes the state of file system, the total size of partition, the block size, pointers to a list of free blocks, the node number of root directory, magic number etc.

Explanation

R₁ ∩ R₂ ≠ φ. This makes the decomposition lossless join, as all the attributes are keys, R₁ ∩ R₂ will be a key of the decomposed relation Now, even the original relation R is in 3NF (Even BCNF) as all the attributes are prime attributes (in fact attribute is a candidate key) Hence, any decomposition will also be in 3NF and 2NF, So the answer is option (d).

Explanation

2NF says that all the non-key attributes should depend the keys, partial dependency (an attribute is defined by a part of a composite key) is not allowed 3NF says that any dependency between non-key attribute should be cleared. BCNF says that all determinants in a table is key for every non trivial (no overlapping attributes) function. In the table book, (Title Author) identifies catalog-no. Which if turn identifies publisher and year further (publisher, Title, Year) identifies price. Now here the dependents are partially dependent on the key attributes on left hand side of (R). So the table book is in 2NF. In the table collection, (Title, Author) determines catalog-no., the only non-key attribute. So it is in 3NF as well as BCNF.

Explanation

Name attribute specifies the name of an anchor and it is used to create a bookmark inside a document. Syntax : <a name = "value"> ⇒ href attribute specifies the URL of the page the link goes to, and if the href attribute is not present, the <a> tag is not a hyperlink. Syntax : <a href = "URL">

Explanation

Chomsky Normal Form : production rules are of the form A → BC or A → a (and S → ε) — grammar (iii). Greibach Normal Form : production rules are of the form A → aA₁A₂...Aₙ or A → a — grammar (iv). S-grammar : every production's right-hand side begins with a terminal, and this terminal is different for any two productions with the same left-hand side — grammar (i). LL grammar : two production rules for the same nonterminal can begin with the same terminal only if their select sets differ — grammar (ii). So A-iii, B-iv, C-i, D-ii, i.e. option (c).

Explanation

Probability of k = 1 in one trial = 1/1000000 = 10⁻⁶, so the probability of k ≠ 1 is 1 – 10⁻⁶. Probability that 1 is never printed in 10⁶ printouts = (1 – 10⁻⁶)^(10⁶) ≈ 1/e. Probability that 1 is printed at least once = 1 – (1 – 10⁻⁶)^(10⁶) ≈ 1 – 1/e = 0.632121.

Explanation

Average internal fragmentation per process is y/2. A process has x/y pages and each page-table entry needs z bytes, so the page table takes xz/y bytes. Total overhead = xz/y + y/2. Setting d/dy (xz/y + y/2) = –xz/y² + 1/2 = 0 gives y = √(2xz).

Explanation

Average time to service a page fault = 0.7 × 20 + 0.3 × 8 = 14 + 2.4 = 16.4 m. sec.

Explanation

Only I is correct: a self join is implemented on a single table using alias names. Outer join is not a basic relational algebra operation, and natural join and outer join are not equivalent.

Explanation

A weak entity does not have sufficient attributes to form a primary key; it is drawn as a double rectangle in an ER diagram. A weak entity set can be converted into a strong entity set simply by adding suitable attributes.

Explanation

The candidate keys are AB, CD, BC and AD, so A, B, C and D are all prime attributes and the relation is in 3NF. It is not in BCNF because in C → A and D → B the left side is not a superkey. Hence the best normal form is 3NF.

Explanation

{A}⁺ = {A, B, C, D}, which does not include E, so A alone is not a key. {AE}⁺ = {A, B, C, D, E}, and nothing can be removed from AE, so AE is the minimal key.

Explanation

Every binary relation (a relation with only 2 attributes) is always in BCNF: if both attributes form the key it is in BCNF, and if one attribute is the key the other is determined by it. So statement (a) is false.

Explanation

The Unix Kernel maintains two key data structures related to processes, the process table and the user structure. In user structure there is no place for scheduling parameter process table store the information of all running process. The process table contains process ID's, memory usage of the process, what are file descriptor used in the process.

Explanation

Traditional file system has all mentioned drawbacks. Database applications built by using tables are used to overcome them. eg : Normalization and integrity constraints reduces redundancy & inconsistency. Select * from table provide easy access to data references, joins etc removes data is action primary & foreign key along with check predicates solves integrity problem

Explanation

Every weak entity must be associated with an identifying entity i.e. the weak entity set is said to be existence dependent on the identifying set. The identifying entity set is said to own the weak entity set that it identifies. It is also called owner entity set.

Explanation

If you drop child table first, the foreign key will be dropped as well. If you attempt to drop percent table first you will get an "could not drop object "a" because it is referenced by a foreign key constraint error, Drop table can not be used to drop a table reference by a foreign key constraint. The referencing foreign key constraint or the referencing table must be dropped.

Explanation

We have weak entities for several reasons : • We want to avoid the data duplication and consequent entity. Possible inconsistencies caused by duplicating the key of strong. • Weak entities be stored physically with their strong entities • Weak entities can be detected automatically when their strong entity is detected. So (d) is incorrect statement, Hence (d) is the answer.

Explanation

CREATE ASSERTION <assertion name> CHECK <predicate> is the proper syntax. For example, an assertion demanding that there is no more than one president among the employees: CREATE ASSERTION AT_MOST_ONE_PRESIDENT CHECK ((SELECT COUNT(*) FROM EMP e WHERE e.job = 'PRESIDENT') <= 1).

Explanation

Number of pointers that can be stored in the index block – which depends on the size of the index block and the size of each address. Size of each data block – because each pointer points to a block, so the larger the block size, the more data each pointer represents.

Explanation

Just like linked list where insertion, deletion of blocks is easy. In linked allocation only pointer needs to be changed. In linked allocation each file is a linked list of disk blocks. The directory contains a pointer to the first block of the file, for example, a file of 5 blocks which starts at block 4, might continuous at block 7, then block 16, block 10, 8 finally block 27. Each block constrains a pointer to the next block & the last block contains a NIL-pointer. The value-1 may be used for NIL to differential it from block 0.

Explanation

One table for each entity : 2 R₁ is one to many (No separate table): 0 R₂ is many to many and requires one separate table: 1 Total number of tables: 3

Explanation

The average marks of male students is more than the average marks of student in the university.

Explanation

Option (a) is which it is clearly mentioned that partial dependency exists is against 2NF principle. Question seems incomplete, because there is nothing said about functional dependency.

Explanation

Since prolog is a dynamically typed language. Which is to say that programs are not checked for type errors until run time.

Explanation

Four views regarding the design of a data warehouse: Top-down view – allows selection of the relevant information necessary for the data warehouse; Data source view – exposes the information being captured, stored and managed by operational systems; Data warehouse view – consists of fact tables and dimension tables; Business query view – sees the data in the warehouse from the view of the end-user.

Explanation

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. An index is a pointer to data in a table, very similar to an index at the back of a book.

Explanation

The LIKE operator with the % wildcard matches any sequence of characters: Select * from customers where city like '%GAR%';

Explanation

The following statements are true: (1) the ability to develop more realistic models of the real world; (2) the ability to represent the world in a non-geometric way; (3) the ability to develop databases using natural language approaches. Hence the answer is (a).

Explanation

Attribute 'one' can have 10 values from 1 to 10 and attribute 'two' can have 5 values from 1 to 5. Since 'two' is unique, it cannot repeat, and every tuple must have a distinct value of 'two' from only 5 possibilities. So the relation can have at most 5 tuples.

Explanation

R.B = S.B holds for 2 records (B = 2 and B = 4), so SQ2 (inner join) returns 2 tuples. The full outer join returns the 2 matching tuples plus the unmatched tuple of R (1, 3) and the unmatched tuple of S (7, 8), so SQ1 returns 4 tuples.

Explanation

Query (3) returns the union of the two result sets, so it returns between 10 and 17 rows. If all rows are distinct the answer would be 10 + 7 = 17; if set (2) is a subset of set (1), it is 10 + 7 – 7 = 10. Among the options only 10 is possible.

Explanation

F = x'y'z + x'yz + xy'z' + xyz' = x'z(y' + y) + xz'(y' + y) = x'z + xz'

Explanation

The entity integrity constraint states that primary keys cannot be null. There must be a proper value in the primary key field. The basis of referential integrity is foreign keys. A foreign key in one table references a primary key in another table.

Explanation

The SQL null is the term used to represent a missing value. A null value in a table is a value in a field that appears to be blank. A field with a null value is a field with no value.

Explanation

Drop command remove all tuples of the relation as well as schema also. Delete command in SQL removes tuples from the relation but retain the schema of table in database.

Explanation

Attribute B has distinct values in all tuples, so {B} can be a candidate key, and {B, C} is then only a superkey (not minimal), because a candidate key is a minimal superkey. {A, D} and {C, D} have distinct values in all tuples, so both can be candidate keys. Hence only II could not be a candidate key.

Explanation

(i) Views help provide data independence, allow the schema to change without recompiling existing applications, and help with access control by letting users see only a particular subset of the data. (ii) A conceptual ER model may be used as the foundation for logical data models. (iii) Being unable to store information unless other unrelated information is stored is an insertion anomaly, not an update anomaly. (iv) SQL is not a procedural language. Only statements III and IV are false.

Explanation

Objects in an object-oriented database contain not only data but also methods for processing the data – correct. Object-oriented databases store computational instructions in the same place as the data – correct. Object-oriented databases are more adept at handling structured (analytical) data than relational databases – incorrect. Object-oriented databases store more types of data than relational databases and access that data faster – correct. So, option (c) is correct.

Explanation

With location transparency the user does not need to know where the data is stored, but still needs to know how the data is fragmented; so a SQL query needs to specify fragments. Fragmentation transparency is the highest level, where the user does not even need to know that the database is partitioned.

Explanation

SQ₁: S tuples with D < 7 have S.C = 4, 2, 3, 1. Joining with R.A: S.C = 1 matches R tuples with A = 1 (R.B = 2, 2, 3, 4) and S.C = 3 matches <3, 1, 4> (R.B = 1). Grouping by R.B gives groups 1, 2, 3, 4, so M = 4. SQ₂: S.B = 1 has distinct D {5, 3} (count 2), S.B = 2 has {7, 4} (count 2), S.B = 3 has {4} (count 1). The third group is not taken because COUNT(DISTINCT S.D) > 1 fails, so N = 2.

Explanation

Semi-join strategies are techniques for query processing in distributed database system. In which only the joining attributes are sent from one site to another and then only the required rows are returned.

Explanation

I, II and III all return exactly those tuples of R whose B value appears in S, whereas IV returns every (A, R.B) pair of R as long as S is non-empty. Example: R = {(10, 1), (20, 2)}, S = {(2, 30), (3, 40)}. (I) R ⋈ S = {(20, 2, 30)}, so π_A,B(R ⋈ S) = {(20, 2)}. (II) π_B(S) = {(2), (3)}, so R ⋈ π_B(S) = {(20, 2)}. (III) π_A(R) × π_B(S) = {(10, 2), (10, 3), (20, 2), (20, 3)}, and R ∩ that = {(20, 2)}. (IV) π_A,R.B(R × S) = {(10, 1), (20, 2)}. So I, II and III are the same query.