KTU DBMS Model Question paper Solved Answer key 2019 S4 CSE

KTU DBMS Model Question paper Solved Answer key 2019 S4 CSE

DBMS is the KTU 2019 course for S4 CSE students, also known as Database Management System. This course provides in-depth information on database concepts, site models, implementation of multilingual data processing and SQL commands. This course is compulsory for all fourth-year CSE students at KTU. database users' activities. The program provides data interpreting services, data manipulation, and query data, support for a high-level programming language, helping to ensure the integrity of the database. SQL, transactions, triggers, data mining algorithms, data storage design considerations.

Board KTU
Scheme 2019 New Scheme
Year Second Year
Semester S4  Computer Science
Subject CST 204 | Database Management System Solved Model Question Paper
Type Model Question Paper Solved
Category KTU S4 Computer Science 

To fulfil our mission, we try hard to give you qualified and updated study materials. Here is the answer key for the KTU DBMS MODEL QUESTION PAPER in the syllabus. Study well & don't forget to share with your friends.   

--------------------------------------------------------

DBMS Model Question Paper Solved | 2019 Scheme

APJ ABDUL KALAM TECHNOLOGICAL UNIVERSITY FOURTH SEMESTER B.TECH DEGREE EXAMINATION

Course Code: CST 204
Course name: Database Management System

Max Marks: 100                                                                                                           
Duration: 3 Hours

PART-A

(Answer All Questions. Each question carries 3 marks)

1 List out any three salient features of a database system.

Ans 1: 

Low Repetition and Redundancy:
DBMS reduces data duplication and repetition by creating a single database that is accessible to multiple users and minimizes split files over and over again.

Maintenance of Large Databases:
A DBMS helps maintain large databases by enforcing user-defined validation and integrity constraints, such as user-based access. 

Enhanced Security:
When handling large amounts of data, security becomes a major concern for all businesses. The database management software does not allow full access to anyone other than the site administrator. All other users are restricted, depending on their level of access.

Improved File Consistency:
By implementing a database management system, organizations can create a standardized way to use files and ensure data compatibility with other programs and applications.

Multi-User Environment Support:
Database management software incorporates and supports a multi-user environment, allowing multiple users to access and process data at once.

2 When is a multi-valued composite attribute used in ER modelling?

Ans 2:  

Some combination keys can be a multi-value attributes. For example, a student address attribute can be further divided into 'Location', 'City' and 'PIN' which are a combination of attributes. Also, a student address may have more than one value. Therefore a composite adjective can be used in such cases.

3 For the SQL query, SELECT A, B FROM R WHERE B=’apple’ AND C = ‘orange’ on the table R(A, B, C, D), where A is a key, write any two equivalent relational algebra expressions.

Ans 3:  

Method 1:





Method 2:


4 Outline the concept of theta-join.

Ans 4:  

THETA JOIN allows you to merge two tables based on the situation represented by theta. Theta joins the work of all comparison users. Indicated by the symbol θ. The common story of JOIN's performance is called Theta joining.

Notation A θ B

Theta joining can apply to any conditions in the matter of selection.


Example



5 How is the purpose of where clause is different from that of having clause?

Ans 5: 

The clause section WHERE only applies to whole groups (i.e., pre-set lines representing groups), while the clause "applies only to each row. The question can contain both the THERE paragraph and the POSSIBILITY section. In that case:
  • The clause when used first in each row of tables or items with a table value in the drawing window. Only the lines that meet the conditions in the section WHERE they are collected.
  • The HAVING clause is then applied to the lines in the result set. Groups that meet the HAVING criteria only appear in the query output. You can only use the WE-section in the re-columns in the GROUP BY or integration function.

6 What is the use of a trigger?

Ans 6: 

  • The trigger is widely used to maintain the integrity of the information on the database.
  • Triggers help the database designer ensure certain actions such as maintaining an audit file, are completed regardless of which program or user makes changes to the data.

For example:

  • Create a job evaluation tracker on a database. For example, you can track updates in the order table by reviewing the verification information in the checklist.
  • Apply the rule of business. For example, you can determine when an order exceeds a customer’s credit limit and display a message indicating that.
  • Find additional data not available within the table or on the database. For example, if an update occurs in the bulk column of the item table, you can calculate the correlation corresponding to the column_value.
  • Focus on the integrity of the index. When you delete a customer, for example, you can use a trigger to delete matching lines with the same customer number in the order table.
7 When do you say that a relationship is not in 1NF?

Ans 7: 

1NF must have the following conditions
  • The table should not contain any multivalued attribute 
  • The column must contain content from the same domain
  • Each column must have a unique name
  • There are no column layouts
  • No duplicate lines If any conditions are not satisfied the table is not in 1NF

8 Given the FDs P→Q, P→R, QR→S, Q→T, QR→U, PR→U, write the sequence of Armstrong’s Axioms needed to arrive at a. P → T b. PR → S

Ans 8:  

a. To arrive at P→T following Armstrong’s Axioms are used Based on the Transitivity property P→Q and Q→T can be written as P→T


9 What is meant by the lost update problem?

Ans 9: 
  • Also called write - write Conflict problem
  • In the case of a missing update issue, an update made to the data item is missing as it is written over an update made with another purchase.

For Example:
10 What is meant by checkpointing?

Ans 10: 

The test site specifies the time when the DBMS was in the same condition and everything that was done was done. These test sites are monitored throughout the operation. Activity log files will be generated after processing. The log file is deleted when it arrives at the savepoint/checkpoint by keeping its update on the database. Then a new log that contains the upcoming activity log is generated, which is updated until the next test site, and the process continues.

PART-B 

(Answer any one question from each module. Each question carries 14 Marks)

11 a. Design an ER diagram for the following scenario: There is a set of teams, each team has an ID (unique identifier), name, main stadium, and to which city this team belongs. Each team has many players, and each player belongs to one team. Each player has a number (unique identifier), name, DoB, start year, and shirt number that he uses. Teams play matches, in each match, there is a host team and a guest team. The match takes place in the stadium of the host team. For each match, we need to keep track of the following: The date on which the game is played The final result of the match. The players participated in the match. For each player, how many goals he scored, whether or not he took the yellow card, and whether or not he took the red card. During the match, one player may substitute another player. We want to capture this substitution and the time at which it took place. Each match has exactly three referees. For each referee, we have an ID (unique identifier), name, DoB, years of experience. One referees the main referee and the other two are assistant referees.  (14)

Ans 11 (a): 

KTU S4 CSE Database Management System Solved Model Question Paper - Kerala Notes



OR

12 a. Interpret the following ER diagram.

Database Management System ER Diagram Problem

b. Distinguish between physical data independence and logical data independence with suitable examples.

Ans 12 (a): Given Diagram Not Clear

Ans 12 (b): 

Physical Data Independence Logical Data Independence
It is mainly concerned with how the data is stored in the system. It is mainly concerned with the structure or the changing data definition.
It is easy to retrieve. It is difficult to retrieve because the data is mainly dependent on the logical structure of the data.
As compared to logical independence it is easy to achieve physical data independence. As compared to physical independence it is not easy to achieve logical data independence.
Any change at the physical level does not require to change at the application level. The change in the logical level requires a change at the application level.
Modifications made to the internal level may or may not be necessary to improve the performance of the structure. Modifications made at a reasonable level are important whenever the logical structure of the site needs to be changed.
It is concerned with the internal schema. It is concerned with the conceptual schema.
Example: Change in compression techniques, Hashing algorithms and storage devices etc. Example: Add/Modify or Delete a new attribute.

13. EMPLOYEE(ENO, NAME, ADDRESS, DOB, AGE, GENDER, SALARY, DNUM, SUPERENO) 

DEPARTMENT(DNO, DNAME, DLOCATION, DPHONE, MGRENO) PROJECT(PNO, PNAME, PLOCATION, PCOST, CDNO) 

DNUM is a foreign key that identifies the department to which an employee belongs. MGRENO is a foreign key identifying the employee who manages the department. CDNO is a foreign key identifying the department that controls the project. SUPERENO is a foreign key identifying the supervisor of each employee. Write relational algebra expressions for the following queries:-

       (a) Names of female employees whose salary is more than 20000.
       (b) Salaries of employees from ‘Accounts’ department
       (c) Names of employees along with his/her supervisor's name
       (d) For each employee return name of the employee along with his department name and the names of projects in which he/she works
        (e) Names of employees working in all the departments      (14)

Ans 13 (a):  

πName (σGender=’female’ AND Salary> ‘20000’ (Employee))

Ans 13 (b):
   
πName,Salary (Employee ⋈Dnum=Dno AND Dname='Accounts' Department)

Ans 13 (c): 
   
πEmplyee.Name,Emp1.Name (Employee ⋈Employee.SuperEno=Emp1.Eno1 AND Dname=' Accounts^' ) ρ_Emp1 (Employee))

Ans 13 (d):   Requires additional info like an employee works for one project or more than one project, a project is worked by one employee or more than one employee

Ans 13 (e):   Whether we need to assume eno not be the primary key


OR

14 a.Write SQL DDL statements for the following (Assume suitable domain types):

          i. Create the tables STUDENT(ROLLNO, NAME, CLASS, SEM, ADVISER), FACULTY(FID,
               NAME, SALARY, DEPT). Assume that ADVISER is a foreign key referring FACUTY table.
         ii. Delete department with name ‘CS’ and all employees of the department.
        iii. Increment salary of every faculty by 10%.     (10)

b.Illustrate foreign key constraints with a typical example. (4)

Ans 14 (a): (i) 

Create tables STUDENT(
 ROLLNO int primary key,
 NAME text not null,
 CLASS varchar(10) ,
 SEM int ,
 ADVISER text ,
 Foreign key (Adviser) references faculty( name ));

Ans 14 (a): (ii) 

Ans 14 (a): (iii) 

Ans 14(b):  

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Persons Table

PersonID Last Name First Name Age
1. Hansen Ola 30
2. Svendson Tove 23
3. Pettersen Kari 20


Orders Table
                                                                           
OrderID OrderNumber PersonID
1. 77895 3
2. 44678 3
3. 22456 2
4, 24562 1

Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.

15. For the relation schema below, give an expression in SQL for each of the queries that follow:

employee(employee-name, street, city) 
works(employee-name, company-name, salary) 
company(company-name, city) 
manages(employee-name, manager-name)

a) Find the names, street addresses, and cities of residence for all employees who work for the Company ‘RIL Inc.' and earn more than $10,000.

b) Find the names of all employees who live in the same cities as the companies for which they work.

c) Find the names of all employees who do not work for ‘KYS Inc.’. Assume that all people work for exactly one company.

d) Find the names of all employees who earn more than every employee of ‘SB Corporation'. Assume that all people work for at most one company.

e) List out the number of employees company-wise in the decreasing order of a number of employees. (14)

Ans 15 (a): 

select employee.employee-name, employee.street, employee.city from
employee, works
where employee.employee-name=works.employee-name
and company-name = ' RIL Inc.' and salary > 10000)

Ans 15 (b):

select e.employee-name
from employee e, works w, company c
where e.employee-name = w.employee-name and e.city = c.city
and w.company-name = c.company-name

Ans 15 (c):

select employee-name
from works
where company-name <> ‘KYS Inc.'

Ans 15 (d):

select employee-name
from works
where salary > all (select salary
from works
where company-name = 'SB Corporation')

Ans 15 (e):  

SELECT employee-name, COUNT(*)
FROM works
GROUP BY company-name;
ORDER BY company-name DESC


OR

16 a. Consider an EMPLOYEE file with 10000 records where each record is of size 80 bytes. The file is sorted on employee number (15 bytes long), which is the primary key. Assuming un-spanned organization and block size of 512 bytes compute the number of block accesses needed for selecting records based on employee number if,

       i. No index is used
      ii. Single level primary index is used
     iii. Multi-level primary index is used

Assume a block pointer size of 6 bytes. (9)

b. Illustrate correlated and non-correlated nested queries with real examples. (5)

Ans 16 (a):   

(i) No index is used

Block size=512 bytes
Record Size= 80 bytes
Block Factor = Block Size/Record Size= 512/80 = 6
Hence 6 records per block
Total number of Blocks = 10000/6=1667 blocks
To find a record in the block we can use binary search=O(log2 Bi)
i.e log2 1667= 11

ii. Single level primary index is used

The primary key is 15 bytes and the block pointer size is 6 bytes
The indexing size will be 15+6=21
Block size=512 bytes
Indexing=512/21=24
Total blocks formed in indexing =1667/24=70
To find a record in the block =O(log2 Bi)
i.e log2 (70+1) = 6
(+1 is for the data access part)

iii. Multi-level primary index is used

Number of second-level index = number of Single level index/Bfr=70/6=12
Number of the third levels index= 12/6=2
Number of the fourth levels index=2/6=1
The number of levels in the Multi-level primary index is 4

Ans 16 (b):

Illustrate correlated and non-correlated nested queries with real examples

Subqueries can be categorized into two types:  
  • A noncorrelated (simple) subquery obtains its results independently of its containing the (outer) statement.
  • A correlated subquery requires values from its outer query in order to execute.

Correlated Subqueries

The correlated sub-query usually gets values ​​from its external query before it starts. When the subquery returns, it relays its results to an external query.

In the following example, the subquery needs values from the addresses. state column in the outer query:


=> SELECT name, street, city, state FROM addresses
     WHERE EXISTS (SELECT * FROM states WHERE states.state = addresses.state);

  • The query extracts and evaluates each address. state value in the outer subquery records.
  • Then the query—using the EXISTS predicate—checks the addresses in the inner (correlated) subquery.
  • Because it uses the EXISTS predicate, the query stops processing when it finds the first match.

Noncorrelated Subqueries

A noncorrelated sub-query is signed without an external query. The subquery starts first and then transmits its results to an external query
For example:

=> SELECT name, street, city, state FROM addresses WHERE state IN (SELECT state FROM states);
  1. Executes the subquery SELECT state FROM states (in bold).
  2. Passes the subquery results to the outer query.
17 a. Illstrate3NF and BCNF with suitable real examples. (6)

b. Given a relation R(A1,A2,A3,A4,A5) with functional dependencies A1→A2A4 and A4→A5, check if the decomposition R1(A1,A2,A3), R2(A1,A4), R3(A2,A4,A5) is lossless. (8)

Ans 17 (a): 

3NF

A relation is in the third normal form if there is no transitive dependency for non-prime attributes as well as it is in the second normal form.

Two Conditions for 3NF

        It should be in 2NF

        No transitive Dependency

       What is Transitive dependency?

       If a Non-prime attribute  Non-prime attribute, then it is called a Transitive dependency

Example:

        R(A,B,C,D)

       FD:- (AB, BC, CD)

       Check whether the above relation is in 3NF

Solution

        Find the attribute closure of all the attributes

       ABCD+={A,B,C,D}

       ABCD+ is a superkey

        Find out the candidate key

       Discard the element by checking the FD

       ABCD

       Check A is a superkey

       A+={A, B, C, D}  --- A is a candidate key because there is no proper subset for identifying a superkey

        Check out more candidate keys are possible

       Check whether the prime attribute A is present on the right-hand side of the FD

       In this problem, no prime attribute is there

       Hence no more candidate key

        Now what we have identified.

       A is the only candidate key

       Prime Attribute is A

        Now check all the FD whether satisfies the following conditions

       It should be in 2NF

       No transitive Dependency

       AB, BC, CD


Hence the given relation is not in 3NF and it is in 2NF

BCNF

        A relation is in BCNF iff

       It is in 3NF

       For each non-trivial FD XY, X must be Super key

Example:

        R(A,B,C)

       FD:- (AB, BC, CA)

       Check whether the above relation is in BCNF

Solution


        Find the attribute closure of all the attributes

       ABC+={A,B,C}

       ABC+ is a super key

        Find out the candidate key

       Discard the element by checking the FD

       ABC

       Check AC is a super key

       AC+={A,B,C} ---is a super key

       AC

       A+={A, B, C} --- is a super key  (A is a candidate key because there is no proper subset for identifying a super key )

        Check out more candidate keys are possible

       Check whether the prime attribute A is present on the right-hand side of the FD

       Yes prime attribute is there in the RHS of the FD:- (AB, BC, CA)

       C, B

       Hence the candidate keys are A, B, C

       Hence prime attributes are A, B, C

        Check for BCNF Conditions

       It is in 3NF

       For each non-trivial FD XY, X must be super key 


Hence the given relation is BCNF

Ans 17 (b): 

Based on property 1:

R1 U R2 U R3 = R

R1(A1,A2,A3) U R2(A1,A4) U R3(A2,A4,A5) = R(A1,A2,A3,A4,A5)

Property 1 satisfies

Based on property 2 common attributes are there in R1, R2 and R3

Based on property 3:

R1(A1,A2,A3), R2(A1,A4)

Common attribute for R1 and R2 is A1. Find the Attribute closure for A1

A1+ = {A1,A2,A4,A5}

A1 is candidate key for R2

R1 R2=A1,A2,A3,A4

Now check with R3 (A2,A4,A5)

Here A2 and A4 are the common attribute and therefore find attribute closure for A2A4

A2A4+ = A2,A4,A5

A2A4 is the candidate key for R3 and

Hence it is Lossless

OR

18 a. Consider the un-normalized relation R(A, B, C, D, E, F, G) with the FDs A→B, AC→G, AD→EF, EF→G, CDE→AB. Trace the normalization process to reach 3NF relations.

b. Illustrate Lossless Join Decomposition and Dependency Preserving Decomposition with typical examples. (7)

Ans 18 (a): 

ABCDEFG+={A,B,C,D,E,F,G}  is a super key

ACD+={A,C,D,B,G,E,F} is a super key

Proper subset of ACD+ = {AC,CD,AD,A,C,D}

  • AC+ is not a super key
  • CD+ is not a super key
  • AD+ is not a superkey
  • A+ is not a super key
  • C+ is not a super key
  • D+ is not a super key

Hence ACD is a candidate key

Now prime attributes are A,C,D

CDE+={C,D,E,A,B,E,F,G} is a superkey

Proper subset of ACD+ = {AC,CD,AD,A,C,D}

  •         CD+ is not a super key
  •         DE+ is not a super key
  •         CE+ is not a super key
  •         C+ is not a super key
  •         D+ is not a super key
  •         E+ is not a super key

Hence CDE is a candidate key

Now prime attributes are A,C,D,E

Now check for transitive dependency

A→B --> No transitive dependency

AC→G --> No transitive dependency

AD→EF --> No transitive dependency

EF→G --> No transitive dependency

CDE→AB --> No transitive dependency

Hence relation R(A, B, C, D, E, F, G) with the FDs A→B , AC→G, AD→EF, EF→G, CDE→AB is in 3NF

Ans 18 (b): 

Lossless Join Decomposition

If we decompose a relation R into relations R1 and R2,

·        Decomposition is lossy if R1 R2 R

·        Decomposition is lossless if R1   R2 = R

To check for lossless join decomposition using FD set, following conditions must hold:


1.      Union of Attributes of R1 and R2 must be equal to attribute of R. Each attribute of R must be either in R1 or in R2.

 Att(R1) U Att(R2) = Att(R)

2.      Intersection of Attributes of R1 and R2 must not be NULL.

 Att(R1) ∩ Att(R2) ≠ Φ

3.      Common attribute must be a key for at least one relation (R1 or R2)

 Att(R1) ∩ Att(R2) -> Att(R1) or Att(R1) ∩ Att(R2) -> Att(R2)

For Example, A relation R (A, B, C, D) with FD set{A->BC} is decomposed into R1(ABC) and R2(AD) which is a lossless join decomposition as:

1.      First condition holds true as Att(R1) U Att(R2) = (ABC) U (AD) = (ABCD) = Att(R).

2.      Second condition holds true as Att(R1) ∩ Att(R2) = (ABC) ∩ (AD) ≠ Φ

3.      Third condition holds true as Att(R1) ∩ Att(R2) = A is a key of R1(ABC) because A->BC is given.

 

Dependency Preserving Decomposition

If we decompose a relation R into relations R1 and R2, All dependencies of R either must be a part of R1 or R2 or must be derivable from combination of FD’s of R1 and R2.

For Example, A relation R (A, B, C, D) with FD set{A->BC} is decomposed into R1(ABC) and R2(AD) which is dependency preserving because FD A->BC is a part of R1(ABC).

Example

Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D which is decomposed into R1(AB) and R2(CD)

This decomposition is dependency preserving decomposition because

  • A->B can be ensured in R1(AB)
  • C->D can be ensured in R2(CD)

19 a. Discuss the four ACID properties and their importance. (7)

b. Determine if the following schedule is conflict serializable. Is the schedule recoverable? Is the schedule cascade-less? Justify your answers.

r1(X), r2(Z), r1(Z), r3(X), r3(Y ), w1(X), c1, w3(Y), c3, r2(Y), w2(Z), w2(Y), c2

(Note: ri(X)/wi(X) means transaction Ti issues read/write on item X; ci means transaction Ti commits.)(7)

Ans 19 (a):

        A transaction is a single logical unit of work which accesses and possibly modifies the contents of a database.

       Transactions access data using read and write operations. 

        In order to maintain consistency in a database, before and after the transaction, certain properties are followed. These are called ACID properties

KTU S4 CSE Database Management System Solved Model Question Paper - Kerala Notes

Atomicity

        By this, we mean that either the entire transaction takes place at once or doesn’t happen at all.

        There is no midway i.e. transactions do not occur partially.

        Each transaction is considered as one unit and either runs to completion or is not executed at all.

        It involves the following two operations. 
Abort: When a job is terminated, changes made to database are not visible. 
Commit: If a transaction commits, changes made are visible. 
Atomicity is also known as the ‘All or nothing rule’. 

Atomicity Example

        Consider the following transaction T consisting of T1 and T2: Transfer of 100 from account X to account Y.

        If the transaction fails after completion of T1 but before completion of T2.( say, after write(X) but before write(Y)), then amount has been deducted from X but not added to Y.

        This results in an inconsistent database state.

        Therefore, the transaction must be executed in entirety in order to ensure correctness of database state. 

KTU S4 CSE Database Management System Solved Model Question Paper - Kerala Notes

Consistency

        This means that integrity constraints must be maintained so that the database is consistent before and after the transaction.

        It refers to the correctness of a database. 

        Referring to the same example, 

       The total amount before and after the transaction must be maintained. 

       Total before T occurs = 500 + 200 = 700

       Total after T occurs = 400 + 300 = 700

       Therefore, database is consistent.

       Inconsistency occurs in case T1 completes but T2 fails.

As a result T is incomplete.

KTU S4 CSE Database Management System Solved Model Question Paper - Kerala Notes

Isolation

        This property ensures that multiple transactions can occur concurrently without leading to the inconsistency of database state.

        Transactions occur independently without interference.

        Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed.

        This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order. 

Isolation Example

        Let X= 500, Y = 500. 
Consider two transactions T and T”. 

        Suppose T has been executed till Read (Y) and then T’’ starts.

        As a result , interleaving of operations takes place due to which T’’ reads correct value of X but incorrect value of Y and sum computed by 

       T’’: (X+Y = 50, 000+500=50, 500) 

        is thus not consistent with the sum at end of transaction: 

       T: (X+Y = 50, 000 + 450 = 50, 450)

        This results in database inconsistency, due to a loss of 50 units. Hence, transactions must take place in isolation and changes should be visible only after they have been made to the main memory. 

KTU S4 CSE Database Management System Solved Model Question Paper - Kerala NotesKTU S4 CSE Database Management System Solved Model Question Paper - Kerala Notes

Durability

        This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs.

        These updates now become permanent and are stored in non-volatile memory.

        The effects of the transaction, thus, are never lost. 


Ans 19(b):

Transaction 1 Transaction 2 Transaction 3
 r1(x)    
   r2(Z)  
 r1(Z)    
    R3(x)  
     R3(y)
w1(X)   
 C1    W3(y)

  C3 
  r2(Y) 
   w2(Z)   
   w2(Y)  
  C2  

KTU S4 CSE Database Management System Solved Model Question Paper - Kerala NotesKTU S4 CSE Database Management System Solved Model Question Paper - Kerala Notes

Since there is no cycle of transaction in the above schedule, it is conflict serializable.

Because all of the transactions T1, T2, and T3 commit after the transactions that wrote the data, this schedule is recoverable.

It is cascade-less. T1,T2,T3 read the data after all of the previous transactions that wrote the data had committed

OR

20 a. Discuss the main characteristics of Key-value DB and Graph DB. (7)

b. Illustrate two-phase locking with a schedule containing three transactions. Argue that 2PL ensures serializability. Also argue that 2Pl can lead to deadlock.

Ans 20 (a): 

Key-value DB

In Key-value DB data is recorded (added, updated, and deleted) and retrieved based on its key / retrieve key. A key-value website links a value to a key, which is used to track an item. In its simplest form, a key value store is similar to a dictionary / list / object item as it exists in most system paradigms, but is stored in a sustainable manner and managed by the Database Management System.

As the name suggests, this type of NoSQL database implements a hash table to store unique keys along with the pointers to the corresponding data values.

The values can be of scalar data types such as integers or complex structures such as JSON, lists, BLOB, and so on.

A value can be stored as an integer, a string, JSON, or an array—with a key used to reference that value.

It typically offers excellent performance and can be optimized to fit an organization’s needs.

Key-value stores have no query language but they do provide a way to add and remove key-value pairs.

Values cannot be queried or searched upon. Only the key can be queried.

Graph DB

A graphical database uses nodes, relationships between nodes and key values ​​structures instead of tables to represent information. This model is usually very fast in integrated data sets and uses a schema-less and low-top model ready to capture ad hoc and fast-changing data.

Graph database is designed for the purpose of maintaining and navigating relationships. Relationships are first-class citizens on a graph site, and most of the value of graph information is derived from these relationships. Graphical data uses nodes to store data entities, and margins to maintain relationships between businesses.


Ans 20 (b): 

A transaction is said to follow the Two-Phase Locking protocol if Locking and Unlocking can be done in two phases. 

  1.       .   Growing Phase: New locks on data items may be acquired but none can be released.
  2.       .  Shrinking Phase: Existing locks may be released but no new locks can be acquired.

Example

KTU S4 CSE Database Management System Solved Model Question Paper - Kerala Notes

Transaction T1:

  •        The growing Phase is from steps 1-3.
  •         The shrinking Phase is from steps 5-7.
  •         Lock Point at 3

Transaction T2:

  •         The growing Phase is from steps 2-6.
  •         The shrinking Phase is from steps 8-9.
    •         Lock Point at 6

LOCK POINT is the Point at which the growing phase ends, i.e., when a transaction takes the final lock it needs to carry on its work

drawbacks of 2-PL

  • Cascading Rollback is possible under 2-PL.
  • Deadlocks are possible.
KTU S4 CSE Database Management System Solved Model Question Paper - Kerala NotesKTU S4 CSE Database Management System Solved Model Question Paper - Kerala Notes

On analyzing the schedule, Dirty Read in T2 and T3 in lines 8 and 12 respectively, when T1 failed we have to roll back others also. Hence, Cascading Rollbacks are possible in 2-PL

Deadlock in 2-PL 

Consider this example. We have two transactions T1 and T2

Schedule:   Lock-X1(A)   Lock-X2(B)  Lock-X1(B)  Lock-X2(A)

 Drawing the precedence graph, the loop is detected. So Deadlock is also possible in 2-PL. 

--------------------------------------------------------

You May Like :


We hope that the question given in the KTU S4 CST 204  Database Management System Solved Model based on the 2019 program will help you in your future tests. If you like this share it with your friends.
"Share KeralaNotes.Com with your friends"

Here is the model question paper for Database Management System (DBMS) course. This question paper is designed for S4 CSE students of KTU. This question paper is prepared by ACB Teachers. Model question paper for S4 CSE Syllabus DBMS is here. This paper consists of three topics- Relational model, SQL language and transaction concept. Database concepts are now being taught under the category of Information Technology in the S4 CSE branch. This course is designed to cover all the areas of database management system. This course has 5 modules and other study material. In this we will discuss about all important aspects of dbase systems such as concept, data structure, language elements, design and implementation and so on as well as the implementation aspect such as data dictionary, enforcement of integrity constraints and so on. We will introduce you to different types of dbase systems such as Relational, Object orientation dbms and network distributed Dbms and their basic concepts and implementation aspects.

We hope the given KTU S4 CST 204 Database Management System Solved Model Question based on the 2019 scheme will help you in your upcoming Examinations.

If you have any queries regarding the KTU S4 Computer Science (CSE) Study Materials, drop a comment below and we will get back to you at the earliest.

Keralanotes.com      Keralanotes.com      Keralanotes.com      Keralanotes.com      Keralanotes.com      

#buttons=(Accept !) #days=(30)

Our website uses cookies to enhance your experience. know more
Accept !
To Top

Join Our Whatsapp and Telegram Groups now...