Scribd Logo
Upload

Welcome to Scribd!

  • 48 views

    CST204 e

    Uploaded by

    Sikha Sasidharan

    Copyright:

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

    CST204 e

    Uploaded by

    Sikha Sasidharan
    48 views4 pages

    Original Title

    CST204-E

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    48 views4 pages

    CST204 e

    Uploaded by

    Sikha Sasidharan

    Copyright:

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

    C 0200CST204122302 Pages: 4

    Reg No.:_______________ Name:__________________________


    APJ ABDUL KALAM TECHNOLOGICAL UNIVERSITY
    Fourth Semester B.Tech Degree (S, FE) Examination January 2024 (2019 Scheme)

    Course Code: CST 204


    Course Name: Database Management Systems
    Max. Marks: 100 Duration: 3 Hours

    PART A
    (Answer all questions; each question carries 3 marks) Marks

    1 Differentiate between Structured, Semi-structured, and Unstructured data. Give 3


    an example each.
    2 Explain the three categories of Data Models. 3
    3 Explain the “Cross-Reference approach” to mapping a Binary 1:1 Relationship 3
    Type that you employ when you map an ER Model into a Relational Schema,
    with the help of an example.
    4 With the help of an example database, explain the usage of the set operations 3
    Union, Intersection, and set difference in Relational Algebra.
    5 “Views simplify the specification of certain queries”. Justify this statement 3
    6 Differentiate between Assertions and Triggers. 3
    7 Define the term “Functional Dependency”. Give an example. 3
    8 Give an algorithm to find the Minimal Cover for a set of Functional 3
    Dependencies.
    9 Give six types of failures in a transaction processing system that necessitate 3
    Recovery procedures.
    10 Explain the importance of transaction logging and checkpointing for efficient 3
    transaction processing.
    PART B
    (Answer one full question from each module, each question carries 14 marks)

    Module -1
    11 a) Explain the main four characteristics of the Database Approach that distinguish it 7
    from the traditional file-processing approach
    b) Categorize the different types of people who work in a database system 7
    environment.

    Page 1 of 4
    0200CST204122302

    12 Design an ER diagram for a typical college library database and then map it into 14
    a relational database schema. List your assumptions and indicate the cardinality
    mappings.
    Module -2
    13 a) Consider the following schema and frame Relational Algebra queries for the 11
    following problems:
    Suppliers (SID: integer, SName: string, Address: string) Parts (PID: integer,
    PName: string, Color: string) Catalog (SID: integer, PID: integer, Cost: real)
    The key fields are underlined and the domain of each field is given after the field
    name.
    (i) Find the names of suppliers who supply red parts
    (ii) Find the SIDs of suppliers who supply some red part or are at the address
    ‘221 Packer Ave’
    (iii) Find the SIDs of suppliers who supply some red part and some green part
    (iv) Find the SIDs of suppliers who supply every red part
    b) Differentiate between DELETE and DROP commands in SQL. Illustrate their 3
    usage.
    14 a) Consider a company database having the following schema and frame Relational 10
    Algebra queries for the following problems. Primary keys are underlined.
    EMPLOYEE (SSN, Name, SupervisorSSN, Dnum)
    DEPARTMENT (Dnumber, Dname, MgrSSN)
    PROJECT (Pnumber, Plocation, ControlDeptNum)
    EMPLOYEE (Dnum) References DEPARTMENT (Dnumber) and
    PROJECT (ControlDeptNum) References DEPARTMENT (Dnumber)
    (i) Find the names of all employees who are supervised by the supervisor of the
    employee named ‘Smith’
    (ii) List the numbers of the projects (Pnumbers) controlled by Smith’s
    department
    b) Illustrate the usage of the SQL commands – ALTER, INSERT, DELETE and 4
    UPDATE
    Module -3
    15 Consider the following Database with two tables: 14

    Page 2 of 4
    0200CST204122302

    Table: Employees

    EmployeeID INT PRIMARY KEY

    FirstName VARCHAR(50)

    LastName VARCHAR(50)

    JobTitle VARCHAR(100)

    Salary DECIMAL(10,2)

    HireDate DATE

    DepartmentID INT

    Table: Departments

    DepartmentID INT PRIMARY KEY

    DepartmentName VARCHAR(100)

    ManagerID INT

    Foreign Key: Employees.DepartmentID references Departments.DepartmentID

    Frame SQL queries for the following problems:


    (i) Calculate the average salary per department.
    (ii) List the employees with the highest salary in each department:
    (iii) Find departments with more than 25 employees.
    (iv) Get the employee names starting with 'S' in alphabetical order
    16 a) With the help of an example explain Single-level indexing and multi-level 8
    indexing. Also, compare and contrast single-level indexing with multi-level
    indexing
    b) Explain a situation where a multi-level index would be significantly less effective 6
    than a single-level index, and vice versa
    Module -4
    17 a) Given the following FDs for the relation Employees (EmployeeID, 6
    DepartmentID, ManagerID, Salary):
    EmployeeID → DepartmentID
    DepartmentID → ManagerID

    Page 3 of 4
    0200CST204122302

    ManagerID → Salary
    Identify any redundant FDs in the set and explain why they are redundant.
    b) Consider the following FDs for the relation Books (BookID, Title, Author, 8
    Publisher):
    BookID → Title, Author
    Author → Publisher
    {Title, Publisher} → BookID
    Find a minimal cover for this set of FDs. Explain how you arrived at your
    answer.
    18 a) Give an algorithm each for checking Lossless Join and Dependency Preserving 6
    Properties
    b) Suppose that we decompose the schema R = (A, B, C, D, E) into 8
    (A, B, C)
    (A, D, E).
    Show that this decomposition is a lossless decomposition if the following set F
    of functional dependencies holds:
    F = {A → BC, CD → E, B → D, E → A}
    Module -5
    19 a) What is a serial schedule? Why are serial schedules unacceptable in practice? 4
    b) What is a conflict serializable schedule? Give an algorithm to check whether a 10
    schedule is conflict serializable or not. Check whether the following schedules
    are conflict serializable or not and find an equivalent serial schedule if possible.
    (i) r2(X); w2(X); r1(X); w1(X); r1(Y); w1(Y)
    (ii) r1(X); r2(X); w1(X); r1(Y); w2(X); w1(Y)
    20 a) Explain the working of Binary Locks and Shared/Exclusive Locks 5
    b) Explain Two-Phase locking protocol and any three variants of it 9
    ***

    Page 4 of 4

    You might also like