Skip to content
CampusPrep
IT-405 · Data Base Management System/Unsolved PYQ Paper

IT-405 Data Base 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
    aDescribe the three levels of schema architecture. Why do we need mappings between different schema levels?
    How to answer
    • Define external, conceptual and internal levels
    • Describe mappings: external-conceptual and conceptual-internal
    • Explain purpose of mappings for data independence
    • Diagram of three-schema architecture
  2. 7 Marks
    bExplain how the data is organized in a network DBMS? How it is different from Hierarchical DBMS.
    How to answer
    • Define network model with records and sets
    • Explain many-to-many via owner-member set types
    • Contrast with hierarchical tree one-to-many structure
    • Give example diagram for each
  3. 7 Marks
    aWhat is the impact of weak entity set in database design? Discuss with an example.
    How to answer
    • Define weak entity set and partial key
    • Explain dependency on identifying owner entity
    • Discuss impact on keys, participation and design
    • Give example e.g. Dependent of Employee
  4. 7 Marks
    bDevelop an Entity Relationship diagram for Indian movies. It should include where it was shot, details of the actors, directors, producers, the movie language and so on.
    How to answer
    • Identify entities: Movie, Actor, Director, Producer, Location, Language
    • Define relationships and cardinalities
    • Add attributes for each entity
    • Present neat ER diagram
  5. 7 Marks
    aConsider the following two tables. Department and Employee tables as given. Discuss the foreign key and referential integrity constraints on above tables.
    Table Department with columns dno, dname and rows: 101 CSE, 102 DS, 103 IT, 104 CCE.
    How to answer
    • Define foreign key and referential integrity
    • Identify Employee.dno referencing Department.dno
    • Discuss NULL and missing 103, 104 cases
    • State constraint violations and enforcement
  6. 7 Marks
    bConsider the following two tables as following. Show the output for the following Relational Algebra Operators:
    1. i)$\Pi_A(R1 * R2)$ where $*$ denotes natural join
    2. ii)$\Pi_B(R1 \text{ left outer join } R2)$ where denotes left outer join
    3. iii)$\Pi_C(R1 \text{ left outer join } R2)$ where denotes left outer join
    Table R1 with columns A,B,C and rows 1 2 3; 4 5 6; 7 8 9; 10 11 12.
    How to answer
    • Compute natural join R1 * R2 on common attribute C
    • Compute left outer join and project required attributes
    • List output tuples for i, ii, iii
    • Explain use of NULL for non-matching rows
  7. 7 Marks
    aGive an example of two relations that would give a meaningful result of carrying out a full outer join. Formulate the full outer join using SQL.
    How to answer
    • Give two relations with overlapping and non-overlapping keys
    • Show FULL OUTER JOIN result with NULLs
    • Formulate SQL using FULL OUTER JOIN ... ON
    • Explain meaningfulness of result
  8. 7 Marks
    bAssume the following employee and department table and write the SQL queries- Emp ($\underline{EID}$, Ename, DOJ, Salary, Dname) Dept($\underline{DID}$, Dname)
    1. i)List the employees having salary between 10,000 and 20,000
    2. ii)Update the salaries of all employees in marketing department and hike it by 10%
    3. iii)Get the gross salaries of all employees
    4. iv)Display the department that has no employee
    5. v)Find the number of employees in the marketing department
    6. vi)Find the name of employee having maximum salary
    7. vii)Find the second maximum salary in the employee table
    How to answer
    • Use SELECT with BETWEEN for salary range
    • Use UPDATE with 10% hike and aggregate functions
    • Use NOT IN / LEFT JOIN for department with no employee
    • Use MAX and subquery for max and second max salary
  9. 7 Marks
    aIdentify the Highest Normal Form of the Relation R (A, B, C, D, E, F, G, H) and Functional Dependency set F = {$AB \rightarrow CD, D \rightarrow EG, F \rightarrow H, C \rightarrow EF, H \rightarrow A, G \rightarrow B, A \rightarrow B$}.
    How to answer
    • Compute attribute closures to find candidate keys
    • Check 2NF, 3NF and BCNF violations from FDs
    • Identify highest form satisfied
    • Justify with dependency analysis
  10. 7 Marks
    bConsider the following employee table. Suppose that employees assigned to multiple projects and can have multiple jobs. Normalize the table to the possible extent.
    Table with columns Eno, Pno, Skill and rows: E1 101,102 Coding,Testing,Maintenance; E2 110,111,115 Analysis,Design,Testing.
    How to answer
    • Identify multivalued non-atomic Skill and Pno values
    • Convert to 1NF by unnesting repeating groups
    • Remove partial/transitive dependencies to higher forms
    • Present normalized tables
  11. 7 Marks
    aIdentify the given schedule is conflict serializable or not.
    Schedule table with columns T1,T2,T3 and rows in order: T2 R(A); T1 W(A); T3 W(A); T2 W(A); T3 R(B); T1 R(B); T2 W(B).
    How to answer
    • List conflicting operations on same data item
    • Draw precedence graph for T1,T2,T3
    • Check for cycle to decide conflict serializability
    • State equivalent serial order if exists
  12. 7 Marks
    bConsider the following two transactions T1 and T2 as following T1 : Transfer of Rs. 1,000 from Account A to Account B T2 : Transfer of 10% amount from Account A to Account B The Initial value of Account A is 2000 and Account B is 3000. Create any one concurrent schedule with T1 and T2 and calculate the final values of A and B.
    How to answer
    • Define T1 transfer 1000 and T2 transfer 10%
    • Interleave operations to form concurrent schedule
    • Compute A=2000 and B=3000 updates step-wise
    • Give final values showing non-serializable effect
  13. 7 Marks
    aDiscuss with an example when concurrent execution of two transactions lead to conflict?
    How to answer
    • Define conflict: RW, WR, WW on same item
    • Give example of two transactions with conflicting ops
    • Show non-serializable result / inconsistency
    • Explain conflict vs non-conflict operations
  14. 7 Marks
    bIllustrate the concept of two-phase locking protocol and its variants with an example.
    How to answer
    • Define growing and shrinking phases
    • Explain Strict, Conservative and Rigorous 2PL
    • Illustrate locking-unlocking with example
    • State guarantees for serializability and deadlock handling
  15. 14 Marks
    Write short note on (any two):
    1. i)Oracle Application Express (APEX)
    2. ii)Database Anomalies
    3. iii)Distributed Databases
    4. iv)Views
    How to answer
    • Define chosen two topics with key features
    • Explain architecture / causes / uses with examples
    • Mention advantages and applications
    • Give concise structured short notes
Go to where you left off?

Quick Add to Notes

Save questions, your own notes and screenshots into notes filed by unit. It takes a free account.

Create free account

Have an account? Log in