Understanding ER Diagrams and Relational Mapping in DBMS

Question:

Which of the following statements is true about the above ER diagram?

1. The minimum number of relations required for the above ER diagram is 2.
2. Attribute C will be present as a prime attribute in relation E1 for the minimal relational model of the above ER diagram.

Options:

• A: Only I
• B: Only II
• C: Both I and II
• D: None of the above

Correct Answer:

Option C: Both I and II

Solution:

1. Minimum Number of Relations:
– Entities E1 and E2 are merged into a single relation due to their connectivity via R1.
– E3 remains a separate relation due to its additional attributes and relationships.
– This results in 2 relations: E1 ∪ E2 and E3.
– Therefore, Statement I is true.

2. Attribute C as Prime:
– C, as the primary key of E2, is added to E1 during the merging process.
– Thus, C becomes a prime attribute in the resulting relation.
– Therefore, Statement II is true.

Conclusion: Both statements are valid. The correct answer is Option C: Both I and II.

Clustered File Organization and Disk I/O in DBMS

Question:

Consider a clustered file organization given below, with each studio clustered with the movies made by that studio.
Suppose that ten records, either studio records or movie records, will fit on one block.
Also assume that the number of movies per studio is uniformly distributed between 1 and m.

As a function of m, the average number of disk I/O’s needed to retrieve a studio and all its movies is:

• A: m
• B: m/2
• C: m + 1
• D: m/10

Correct Answer:

Option D: m/10

Solution:

1. Understanding the Scenario:
– Each block can hold up to 10 entries (either studio or movie records).
– The number of movies per studio is uniformly distributed between 1 and m.

2. Total Records Per Studio:
– For a studio with m movies, the total records = m + 1 (1 for the studio and m for the movies).

3. Block Access Calculation:
– If m = 10, total records = 11. These records require 2 blocks (as each block holds 10 records).
– If m = 0, total records = 1. This requires 1 block.
– The function must scale linearly with m, given the uniform distribution and block capacity.

4. Average Disk I/O:
– On average, the number of blocks required is m/10, as the block access scales linearly with the number of records and the block capacity.

Conclusion: The average number of disk I/O’s needed to retrieve a studio and all its movies is m/10. Hence, the correct answer is Option D.

Total Number of Blocks Needed for B-Tree Index in DBMS

Question:

Suppose that blocks can hold either ten records or 99 keys and 100 pointers. Also assume that the average B-tree node is 70% full, i.e., it will have 69 keys and 70 pointers. We can use B-trees as part of several different structures.

What is the total number of blocks needed for a 1,000,000 record file if the data file is a sequential file, sorted on the search key, with 10 records per block and the B-tree is a dense index?

Options:

• A: 15,034
• B: 100,000
• C: 114,494
• D: None of the above

Correct Answer:

Option C: 114,494

Solution:

1. Data Blocks:
– Each block holds 10 records.
– Total records = 1,000,000.
– Number of data blocks = 1,000,000 ÷ 10 = 100,000.

2. Bottom-Level Index Blocks:
– Each B-tree node holds 70 pointers (70% full).
– Number of index blocks = 100,000 ÷ 70 = 1,429.

3. Upper-Level Index Blocks:
– Second-level blocks = 1,429 ÷ 70 ≈ 21.
– Third-level blocks = 21 ÷ 70 ≈ 1.
– Root block = 1.

4. Total Blocks:
– Total blocks = Data blocks + Index blocks (all levels) + Root block.
– Total blocks = 100,000 + 1,429 + 21 + 1 = 114,494.

Conclusion: The total number of blocks needed is 114,494. Hence, the correct answer is Option C.

Understanding Conflict Serializability, Recoverability, and Precedence Graphs in DBMS

Question:

Select the correct answer for the matching of given lists:

List I:

1. Schedule 1:

   R2(C), R3(B), W1(A), W2(A), R2(B), R3(C), W3(B), R1(B), W1(B)

2. Schedule 2:

   R2(A), W1(A), W2(A), R3(A), R3(B), C1, C3, C2

3. Schedule 3:

   R2(A), W3(A), C3, W1(A), C1, W2(B), C2, R4(A), R4(B), C4

List II:

1. a. Non-Recoverable Schedule
2. b. Conflict Serializable and Recoverable
3. c. Non-Conflict Serializable Schedule

Options:

• A: 1 → a, 2 → c, 3 → b
• B: 1 → c, 2 → a, 3 → b
• C: 1 → c, 2 → b, 3 → a

Correct Answer:

Option B: 1 → c, 2 → a, 3 → b

Solution:

1. Schedule 1:

• The precedence graph of the schedule contains a cycle.
• Since there is a cycle, the schedule is not conflict serializable.
• Matching: Schedule 1 matches with (c): Non-Conflict Serializable Schedule.

2. Schedule 2:

• Recoverability Check: T3 reads A after T2 writes A, but T3 commits before T2.
• This violates recoverability, making it a Non-Recoverable Schedule.
• Matching: Schedule 2 matches with (a): Non-Recoverable Schedule.

3. Schedule 3:

• The precedence graph of this schedule does not contain any cycles, confirming conflict serializability.
• Additionally, there are no dirty reads, making it both conflict serializable and recoverable.
• Matching: Schedule 3 matches with (b): Conflict Serializable and Recoverable.

Explanation with Precedence Graphs:

1. Schedule 1:

• The precedence graph for Schedule 1 contains a cycle between T1, T2, and T3.
• This makes it non-conflict serializable.

2. Schedule 2:

• T3 reads data written by T2 but commits before T2. This results in a non-recoverable schedule.

3. Schedule 3:

• The precedence graph does not have cycles, making it conflict serializable.
• No dirty reads or uncommitted dependencies ensure recoverability.

Understanding SQL Query with NOT EXISTS Clause in DBMS

Question:

Consider the following relational schema:

• Suppliers: (sid integer, sname string, address string)
• Parts: (pid integer, pname string, color string)
• Catalog: (sid integer, pid integer, cost real)

Which of the following is correct about the below-given SQL query?

SELECT C.sid
FROM Catalog C
WHERE NOT EXISTS (SELECT P.pid
                  FROM Parts P
                  WHERE (P.color = 'red' OR P.color = 'green')
                  AND (NOT EXISTS (SELECT C1.sid
                                   FROM Catalog C1
                                   WHERE C1.sid = C.sid AND 
                                         C1.pid = P.pid)));

Options:

• A: Result the sids of suppliers who supply some red or green part.
• B: Result the sids of suppliers who do not supply any red or green part.
• C: Result the sids of suppliers who supply every red or green part.
• D: Result the sids of suppliers who do not supply some red or green part.

Correct Answer:

Option C: Result the sids of suppliers who supply every red or green part.

Solution:

1. NOT EXISTS Clause:
– The NOT EXISTS clause returns true only if no tuples are returned by the subquery.

2. Query Analysis:
– The inner subquery checks for parts with colors ‘red’ or ‘green’.
– It verifies if there is no record in the Catalog table where the supplier (C.sid) supplies the specific part (P.pid).

3. Output:
– The outer query ensures that only those suppliers (C.sid) who supply every red or green part are included in the result.

Conclusion: The query correctly identifies suppliers who supply every red or green part, making Option C the correct answer.

 

 

 

DBMS SQL Query on NOT LIKE Clause – Practice Question

Question:

Consider the following instance of relation Customer:

Customer_id	Last_Name	First_Name	Favorite_website
4000	Jackson	Joe	abc.com
5000	Smith	Jane	big.com
6000	Ferguson	Samantha	NULL
7000	Reynolds	Allen	xyz.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	check.com

Query:

SELECT First_Name 
FROM Customer 
WHERE First_Name NOT LIKE '[J, F, P, R]%';

Which of the following is the correct result of the above-given query?

• A:
  • Joe
  • Jane
  • Paige
• B:
  • Jackson
  • Ferguson
  • Reynolds
  • Johnson
• C:
  • Samantha
  • Allen
  • Derek
• D: None of the above

Correct Answer:

Option C: Samantha, Allen, Derek

Solution:

The query uses the NOT LIKE clause to exclude rows where the First_Name starts with the letters J, F, P, R.

– From the given data, the following names are excluded:

• Joe (starts with J)
• Jane (starts with J)
• Paige (starts with P)
• Reynolds (starts with R)
• Ferguson (starts with F)
• Jackson (starts with J)

The remaining names are:

• Samantha
• Allen
• Derek

Conclusion: The correct result is the list of names that do not start with the excluded letters: Samantha, Allen, and Derek. Therefore, the correct answer is Option C.

Understanding Referential Integrity and Referential Actions in DBMS

Question:

Which of the following statements are NOT TRUE?

1. Referential actions are generally implemented as implied triggers.
2. Referential Integrity Constraints ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

Options:

• A: Only I
• B: Only II
• C: Both I and II
• D: Neither I nor II

Correct Answer:

Option D: Neither I nor II

Solution:

1. Referential Actions:
– Referential actions are typically implemented as implied triggers. These triggers often have system-generated names and are hidden from users.
– As such, they are subject to the same limitations as user-defined triggers, including their order of execution relative to other triggers.
– Hence, Statement I is true.

2. Referential Integrity Constraints:
– Referential integrity ensures that a value appearing in one relation for a specified set of attributes must also appear for a certain set of attributes in another relation.
– This guarantees consistency and logical connections between related tables in a database.
– Therefore, Statement II is also true.

Conclusion:
Both statements are valid. Therefore, the correct answer is Option D: Neither I nor II, as neither of the statements is false.