CY-405 Database Management System - Jun 2023 Question Paper

Under each question, How to answer lists the points an answer should cover, outlined by CampusPrep from the syllabus. Check the details against your textbook.

1. 7 Marks
   aExplain the various levels of data abstraction in a database management system.
   How to answer

   • Define physical, logical/conceptual and view/external levels
   • Describe schema vs instance at each level
   • Explain data independence with diagram of three-schema architecture
2. 7 Marks
   bExplain different types of relationships amongst tables in a DBMS. Explain different types of attributes in a DBMS.
   How to answer

   • Define one-to-one, one-to-many, many-to-one, many-to-many relationships with examples
   • Define simple, composite, single-valued, multi-valued, stored, derived, key attributes
   • Give table examples for relationships and attributes
3. 7 Marks
   aDefine degree and cardinality. Based upon the given table write degree and cardinality. Patients

   

   How to answer

   • Define degree as number of attributes/columns and cardinality as number of tuples/rows
   • From Patients table count degree = 4
   • From Patients table count cardinality = 5
4. 7 Marks
   bWhat is the meaning of ACID characteristics in relational databases? Define the structure of a relational database schema.
   How to answer

   • Expand ACID: Atomicity, Consistency, Isolation, Durability with definitions
   • Explain each property with transaction example
   • Define relational database schema: relation, attributes, domains, constraints
5. 7 Marks
   aDescribe the different types of keys. Explain different operations in Relational algebra.
   How to answer

   • List key types: primary, candidate, super, foreign, alternate, composite, unique
   • Define each key with example
   • List relational algebra ops: select, project, union, set-difference, cartesian-product, rename, join
6. 7 Marks
   bConsider the student table given below. Write commands (using relational algebra operations), and outputs for the followings:

   1. i)To display all the records of student table.
   2. ii)To display all the records of CSE branch in student table.
   3. iii)To display the record of ECE branch section B students.
   4. iv)To display the records of section B CSE and IT branch.
   5. v)To display all the records in student tables whose regno>2.

   

   How to answer

   • Use student table with Reg.No., Branch, Section and 4 rows as input
   • Write selection/projection expressions for each of i)-v)
   • Show expected output tables for each query
7. 7 Marks
   aWhat does Normalization mean? Explain different normal forms with examples.
   How to answer

   • Define normalization and need to remove redundancy/anomalies
   • Explain 1NF, 2NF, 3NF, BCNF rules
   • Give example tables for each normal form conversion
8. 7 Marks
   bSuppose you are given a relation $R = (A, B, C, D, E)$ with the following functional dependencies. $\{CE\to D, D\to B, C\to A\}$.

   1. i)Find all candidate keys.
   2. ii)Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF).
   3. iii)If the relation is not in BCNF, decompose it until it becomes BCNF. At each step, identify a new relation, decompose and re-compute the keys and the normal forms they satisfy.
   How to answer

   • Compute attribute closures to find candidate keys containing CE
   • Test partial and transitive dependencies for 2NF/3NF
   • Decompose on violating FDs e.g. D->B and C->A until BCNF and re-evaluate keys
9. 7 Marks
   aLet $R = (A, B, C, D, E, F)$ be a relation scheme with the following dependencies: $C\to F$, $E\to A$, $EC\to D$, $A\to B$. Find candidate key for R?
   How to answer

   • List given FDs C->F, E->A, EC->D, A->B
   • Compute closure of EC to show it determines all attributes
   • Conclude EC is candidate key and check minimality/uniqueness
10. 7 Marks
    bThe following functional dependencies are given: $AB\to CD, AF\to D, DE\to F, C\to G, F\to E, G\to A$. Find the closure of CF, BG, AB and AF.
    How to answer

    • Apply Armstrong axioms iteratively starting from CF, BG, AB, AF
    • Expand using AB->CD, AF->D, DE->F, C->G, F->E, G->A
    • List final closures for each set
11. 7 Marks
    aWhat do you mean by Serializability? Discuss the conflict and view serializability with suitable example.
    How to answer

    • Define serializability and need for concurrent correctness
    • Explain conflict serializability using precedence graph
    • Explain view serializability with example schedules
12. 7 Marks
    bWhat do you mean by Multiple granularities? How is it implemented in transaction system?
    How to answer

    • Define granularity: database, table, page, tuple level locks
    • Explain intention locks IS, IX, SIX and protocol
    • Explain implementation via lock hierarchy in transaction manager
13. 7 Marks
    aWhat are the methods used for deadlock prevention in DBMS? What is difference between blocking and deadlock?
    How to answer

    • List prevention schemes: wait-die, wound-wait, timeout, ordering
    • Explain detection vs prevention approach
    • Differentiate blocking (waiting) vs deadlock (circular wait)
14. 7 Marks
    bWhat is Checkpoint in DBMS? Why is Checkpoint used in database? How Checkpoint works in DBMS?
    How to answer

    • Define checkpoint as point of synchronised log and buffers
    • Explain purpose: reduce recovery time, establish consistency
    • Describe fuzzy vs sharp checkpoint working steps
15. 7 Marks
    aWhat is Secondary storage? Explain types of data storage in DBMS and describe storage hierarchy.
    How to answer

    • Define secondary storage vs primary storage
    • Explain types: magnetic disk, SSD, optical, tape
    • Describe storage hierarchy pyramid with speed/cost/capacity
16. 7 Marks
    bDistinguish between:

    1. i)Primary and Secondary indexing.
    2. ii)Ordered indexing and hashing.
    How to answer

    • Define primary vs secondary index on ordering vs non-ordering field
    • Define ordered indexing vs hashing on search key
    • Tabulate differences on basis, structure, access and use