Unit 6: Database Design - Practice Quiz

1 What does the functional dependency mean?

2 When is an attribute fully functionally dependent on a composite key?

3 Which set of dependencies demonstrates a transitive dependency?

4 What is the main purpose of database normalization?

5 Which requirement must a table satisfy to be in First Normal Form (1NF)?

6 A table is in Second Normal Form (2NF) when it is in 1NF and has no:

7 A table is in Third Normal Form (3NF) when it is in 2NF and has no:

8 What is a database transaction?

9 What does a read operation do in a transaction?

10 What does a write operation do in a transaction?

11 Which state indicates that a transaction is currently executing its operations?

12 Which transaction state indicates that all changes have been saved successfully?

13 Which ACID property ensures that all transaction operations occur or none occur?

14 Which ACID property ensures that committed changes survive a system failure?

15 Which section of a PL/SQL block contains executable statements?

16 Which section of a PL/SQL block is used to handle runtime errors?

17 Which PL/SQL operator is used to assign a value to a variable?

18 Which PL/SQL statement selects between actions based on a condition?

19 Which keywords begin the definition of a stored PL/SQL procedure?

20 What must a PL/SQL function provide to its caller?

21 For relation , the functional dependencies are , , and . What is the closure ?

22 Given , which functional dependency is implied by ?

23 In Enrollment(StudentID, CourseID, Grade), the key is (StudentID, CourseID). If neither StudentID nor CourseID alone determines Grade, which statement is correct?

24 Relation has composite key and dependencies and . What does this show about ?

25 In Employee(EmpID, DeptID, DeptName), suppose EmpID determines DeptID and DeptID determines DeptName. Which dependency is transitive?

26 A department name is repeated in every employee row. Changing the department name requires modifying many rows. Which database design problem is illustrated?

27 A Student table stores the value Java, Python, SQL in one Skills column. Which change best brings the design into First Normal Form?

28 OrderLine(OrderID, ProductID, OrderDate, ProductName, Quantity) has key (OrderID, ProductID). Also, OrderID determines OrderDate, and ProductID determines ProductName. Which decomposition reaches Second Normal Form?

29 Employee(EmpID, DeptID, DeptName) has key EmpID, with EmpID determining DeptID and DeptID determining DeptName. Which decomposition removes the Third Normal Form violation?

30 A bank transfer debits one account and credits another. How should these operations be organized?

31 Two transactions execute this schedule on X, initially 100: T1: read(X), T2: read(X), T1: write(X=110), T2: write(X=120). What is the result?

32 A transaction has executed its final statement, but the database has not yet guaranteed that its changes are permanently stored. What is its state?

33 A system crashes after debiting an account but before crediting the destination account. Which ACID property requires the debit to be rolled back?

34 Transaction T1 updates a salary but has not committed. Transaction T2 must not see that temporary value. Which ACID property enforces this behavior?

35 Consider a PL/SQL block whose SELECT INTO statement returns no rows and whose exception section contains WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Missing');. What occurs?

36 What value is assigned by the PL/SQL statement v_result := 10 + NULL;?

37 Given v_score := 75, what is assigned to v_grade by IF v_score >= 80 THEN v_grade := 'A'; ELSIF v_score >= 60 THEN v_grade := 'B'; ELSE v_grade := 'C'; END IF;?

38 A PL/SQL loop sets v_sum := 0 and runs FOR i IN 1..4 LOOP CONTINUE WHEN MOD(i, 2) = 0; v_sum := v_sum + i; END LOOP;. What is the final value of v_sum?

39 A procedure is declared as adjust_salary(p_id IN NUMBER, p_raise IN NUMBER, p_new_salary OUT NUMBER). Which call correctly supplies the OUT argument?

40 A function is defined as FUNCTION annual_salary(p_monthly NUMBER) RETURN NUMBER IS BEGIN RETURN p_monthly * 12; END;. What does annual_salary(5000) return?

41 For relation , let . Which set contains all and only the candidate keys of ?

42 A relation has candidate key and exactly these relevant dependencies: , , and . Which dependency is fully functional on the composite key?

43 In , suppose and , with . How should the implied dependency be classified for normalization purposes?

44 Let have . Consider the decomposition , , and . Which assessment is correct?

45 An ORDER_DATA table stores ProductIDs as a comma-separated list in each order row. Which redesign establishes 1NF without imposing an arbitrary maximum number of products?

46 For , let . The candidate keys are and . Assuming atomic attributes, what is the highest normal form satisfied?

47 In with , the candidate keys are and . Why does satisfy the formal 3NF condition even though is not a superkey?

48 Consider the schedule r1(X), w1(X), r2(Y), w2(Y), r1(Y), w1(Y), r2(X), w2(X). What does its precedence graph show?

49 Initially, . Both transactions read before either writes. Then writes its computed value , and writes its computed value . What anomaly and final value result?

50 A transaction executes its final statement, but a failure occurs before its commit record becomes durable. Recovery then completes its rollback. Which state sequence best describes this path?

51 A write-ahead logging system forces all required log records, including the commit record, to stable storage before acknowledging a commit, but writes modified data pages later. Which conclusion is correct?

52 Assuming SERVEROUTPUT is enabled, what does this PL/SQL block print?

DECLARE
  v NUMBER := 10;
BEGIN
  DECLARE
    v NUMBER := 0;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(10 / v);
  EXCEPTION
    WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE(v);
  END;
  DBMS_OUTPUT.PUT_LINE(v);
END;

53 What is printed by this PL/SQL fragment?

DECLARE
  a NUMBER := 5;
  b NUMBER := NULL;
BEGIN
  IF a <> 5 OR b = 3 THEN
    DBMS_OUTPUT.PUT_LINE('A');
  ELSIF b IS NULL AND a BETWEEN 1 AND 5 THEN
    DBMS_OUTPUT.PUT_LINE('B');
  ELSE
    DBMS_OUTPUT.PUT_LINE('C');
  END IF;
END;

54 What value is printed by this PL/SQL block?

DECLARE
  i NUMBER := 1;
  s NUMBER := 0;
BEGIN
  LOOP
    i := i + 1;
    CONTINUE WHEN MOD(i, 2) = 0;
    s := s + i;
    EXIT WHEN i >= 5;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(s);
END;

55 Without NOCOPY, what does this block print?

DECLARE
  v NUMBER := 10;
  PROCEDURE bump(p IN OUT NUMBER) IS
  BEGIN
    p := p + 1;
    RAISE_APPLICATION_ERROR(-20001, 'stop');
  END;
BEGIN
  BEGIN
    bump(v);
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
  DBMS_OUTPUT.PUT_LINE(v);
END;

56 A stored function invoked as SELECT adjust_salary(10) FROM dual executes an UPDATE statement and is neither autonomous nor otherwise exempted. What is the expected result?

57 Given , which is a minimal equivalent cover?

58 Using the minimal cover for , which relation set is produced by the standard dependency-preserving 3NF synthesis algorithm before optional removal of subsumed relations?

59 Classify the schedule w1(X), r2(X), c1, c2, where r2(X) reads the value written by .

60 What occurs when f(0) is called for the following PL/SQL function?

CREATE OR REPLACE FUNCTION f(p NUMBER)
RETURN NUMBER IS
BEGIN
  IF p > 0 THEN
    RETURN p;
  END IF;
END;