Scribd Logo
Upload

Welcome to Scribd!

  • 19 views

    DBSA AK-Regular Mid-Sem Question Papers

    Uploaded by

    pvaid2208
    This document contains instructions and questions for a mid-semester database systems test. It provides details on the course, date, duration, number of questions and pages. The 8 questions assess different database concepts like data models, schema normalization, SQL commands and queries involving various relations. Students are asked to define terms, draw diagrams, normalize schemas, compare operations and write relational algebra and SQL expressions.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd

    DBSA AK-Regular Mid-Sem Question Papers

    Uploaded by

    pvaid2208
    19 views3 pages
    This document contains instructions and questions for a mid-semester database systems test. It provides details on the course, date, duration, number of questions and pages. The 8 questions assess different database concepts like data models, schema normalization, SQL commands and queries involving various relations. Students are asked to define terms, draw diagrams, normalize schemas, compare operations and write relational algebra and SQL expressions.

    Original Description:

    Exam paper for dbms

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains instructions and questions for a mid-semester database systems test. It provides details on the course, date, duration, number of questions and pages. The 8 questions assess different database concepts like data models, schema normalization, SQL commands and queries involving various relations. Students are asked to define terms, draw diagrams, normalize schemas, compare operations and write relational algebra and SQL expressions.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    19 views3 pages

    DBSA AK-Regular Mid-Sem Question Papers

    Uploaded by

    pvaid2208
    This document contains instructions and questions for a mid-semester database systems test. It provides details on the course, date, duration, number of questions and pages. The 8 questions assess different database concepts like data models, schema normalization, SQL commands and queries involving various relations. Students are asked to define terms, draw diagrams, normalize schemas, compare operations and write relational algebra and SQL expressions.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 3

    Birla Institute of Technology & Science, Pilani

    Work Integrated Learning Programmes Division


    First Semester 2022-2023

    Mid-Semester Test
    (EC-2 Regular)

    Course No. : SESAPZC337


    Course Title : Database Systems and Applications
    Nature of Exam : Closed Book
    Weightage : 30% No. of Pages = 2
    Duration : 2 Hours No. of Questions = 8
    Date of Exam : 08-10-2022_AN
    Note to Students:
    1. Please follow all the Instructions to Candidates given on the cover page of the answer book.
    2. All parts of a question should be answered consecutively. Each answer should start from a fresh page.
    3. Assumptions made if any, should be stated clearly at the beginning of your answer.

    Q.1. What are the different types of users supported by DBMS? Illustrate with suitable examples.
    [3 Marks]
    Answer: Any 3 users with description
    i) Naïve/Parametric User
    ii) Casual
    iii) Sophisticated
    iv) Stand alone

    Q.2. Explain the 3-schema architecture of DBMS. [3 Marks]


    Answer:
    Block diagram of 3-schema architecture = 1
    Explanation - 2

    Q.3. Define and explain the following concepts of ER model with suitable example
    a) Entity type
    b) Relationship type
    c) Attribute [3 Marks]
    Answer: Definition and notation – 1x3

    Q.4. Define 1NF, 2NF and 3NF and apply the same to normalize the following schema: [5 Marks]

    Answer:
    1NF, 2NF, 3NF definition – 3 marks
    1NF – Default all the attributes are simple and single valued – ½ mark
    2NF – FD2 and FD3 violates with FD1, decomposition – 1 Mark
    3NF – No Transitive FD – ½ Mark

    Q.5. Compare and contrast the following SQL operations:


    a) ALTER and UPDATE
    b) DELETE, DROP and TRUNCATE [2 Marks]
    Answer: 1 marks for each sub-question
    Q.6. For the following Schema:
    EMP(fname, Lname, SSN, Bdate, address, gender, salary, superSSN, Dno)
    DEPT(Dname, Dnumber, MgrSSN, mgrstartdate)
    DEPT_LOC(Dnumber, Dloc)
    PROJECT(Pname, Pnumber, Ploc, Dnum)
    WORKS_ON(ESSN, Pno, Hours)
    DEPENDENT(ESSN, Dependent_name, Gender, bdate, relationship)
    Give the relational Algebra expression for the following:
    i) Retrieve the name of the manager of each department.
    ii) For each project, retrieve the Pnumber, Pname, and number of employees who worked on
    that project.
    iii) Retrieve the names of employees who work on all the project controlled by department
    05.
    iv) Retrieve the name of employees who have no dependents
    v) Retrieve number of Male and Female employee working in the company. [5 Marks]

    Q.7. Convert the following ER diagram into its relational data model schema diagram

    [4 Marks]

    Q.8. For the following Schema:


    EMP(fname, Lname, SSN, Bdate, address, gender, salary, superSSN, Dno)
    DEPT(Dname, Dnumber, MgrSSN, mgrstartdate)
    DEPT_LOC(Dnumber, Dloc)
    PROJECT(Pname, Pnumber, Ploc, Dnum)
    WORKS_ON(ESSN, Pno, Hours)
    DEPENDENT(ESSN, Dependent_name, Gender, bdate, relationship)
    Give the SQL queries for the following:
    i) Retrieve the name and address of all the employees wo work for ‘Sports’ department.
    ii) Retrieve Dnumber, number of employee and average salary of each department.
    iii) List the project number, controlling department number and department manager’s last
    name, address and birthdate.
    iv) Retrieve the name of the employee with 2 or more dependents.
    v) Retrieve female employees from dno=20 and earning more than 50000. [5 Marks]

    ***********

    You might also like