Solved question paper for RDBMSO May-2017 (BCA 4th)

Question paper 1

1. Section A   

   1. Discuss in detail the basic file operations.

   Answer:

   Basic file operation:- When An application stores information with file it is a common to include some simple file management task within the software. This will describe method of copying moving etc operation of files.

   1. Copying a file:-When using file claim there are two overload of the copy method for stop the first accept two string parameter contains the path and Name for the copy.

   E.g. File copy(*(:\\temp\\Text.txt:*(:\\temp\\copy.text*) :

   this is the example copy a filename test.tst that reside in the folder full stop the copy of the file has a new name but is created in the same folder in this example.

   2. Moving a file:-File can optionally changed their filename at the same time using the more method of the file class Full stop files can be moved from one location to another.

   E.g. File move(*(:\\temp\\Text.txt:*(:\\temp\\move .text*) : this method accept string argument for the file to move and the new location and name.

   3. Renaming a file:-Neither The file class 9 the file into class provide a method for renaming a file. However the move and move to Method can be used for this purpose.

   E.g. File move(*(:\\temp\\Text.txt:*(:\\temp\\rename .text*) :

   So The same pass is used for both parameter with only the filename part being changed.

   4. Deleting a file:- The first class include the static delete method to allow a file to be permanently erase .

   This method does not send the file to the recycle bin and given no option for cancelling the operation So should be used with Case.

   E.g. File delete(*(:\\temp\\Text.txt:*:

   The file class method accept a single parameter contain the name of the file to be deleted.

2. 2. Define and distinguish between sequential and indexed file allocation technique

   Answer:

   Sequential and index file allocation:-

   Sequential file organization:-

   • It record and store and access in a particular order sort using a key field.

   • Retrieval requires a searching sequently through the entry file record by record to the end.

   • Because the record in a file are sorted in a particular order datafile searching method like the binary search technique can be used to reduce the time used for searching a file.

   Advantages

   • The sorting make it easy to assessor record.

   • The binary job technique can be used to reduce record search time by as much as half the time taken.

   Disadvantage

   • The sorting does not remove the need to assess other record as the search look for particular records.

   • Sequential record can't support modern technique colleges that require fast assessed to stored record.

   • The requirement that all record be of the same size in same time difficult to in force.

   Indexed file allocation :- A special block is known as a index block that contain the pointer to all the block occupied by a file each file has it on index block the the entry in the induction block contain the desk address of the the file block.

   Advantages

   • This sport Direct Access to block occupied by the file and therefore provide assessed to the file block.

   • It overcome the problem of external tag mentions.

   Disadvantage

   • The pointer overhead for indexer allocation is generator then linked allocation.

   • For every small file save file that expand only e23 block. The inducted allocation would keep word attire block for the point which is is inefficient in term of memory utilisation.

3. 3. Define concurrency. How concurrency is controlled using lock based protocols? Explain.

   Answer:

   concurrency:- It is the ability of a database to allow a user to effect a multiple transaction this is one of the main property that front a database from other forms of data storage like spreadsheet other user can read the file but may not edit a data.

   Lock based protocol:- In this type of protocol any transaction cannot read or write data until it acquire the appropriate Lock on it. There are 2 types of locks

   1. Shared lock

   • It is also known as a read-only lock for stop in a shared lock the data item can only read by the transaction.

   • It can be shared between the transactions because when the transaction field unlock then it can't update the data on the data items.

   2. Exclusive lock:- In the Exclusive lock the data item can be both read as well as written by the transaction.

   This lock is exclusive and in this lock multiple transaction do not modify the same data simultaneously.

   There are 4 type of lock protocols

   1. Simplistic lock protocol:- It hello all the transaction to get the lock on the data before insert or delete the update on it a full stop it will unlock the data item after completing the transaction.

   2. Pre-calming lock protocol:- It evaluate the transaction to list all the data item on which they needed lock.

   3. Two phase locking:- It Device the transaction into 3 part exclusion transaction accor all locks and third transaction cannot demand any new lock.

   4. Strict Two phase locking:- The First phase of a strip is a similar to Tu To PL. in this first phase after acquiring the lock that an infection continues the exclude normally.

   Only difference lpl and track lpl is that strict lpl does not release log after using it.

4. 4. Define deadlock. How deadlocks are disconnected and recovery is performed? Explain.

   Answer:

   Deadlock :- In a database a deadlock is an unwanted situation in which two or more transaction are waiting identify For one another to give up locks.

   Dead detection:- Dead detection can be described as a wait for graph which consists of a pair G

   • V is a set of vertex all the transaction is the system.

   • E is a set of edge is element is an ordered pair and TJ

   • If tntj is in E then there is directed edge from Ti to The.

   • Implying Ti-Tj that waiting for to release a data item.

   • When ti request a data item held by TJ thenTi The is in the wait for graph.

   • This edge is a remove only when t j is no longer holding a data item needed by t j.

   • The system is in a deadlock state if a only if the wait for graph has a cycle.

   • The system invoke a deadlock detection algorithm to lock the

   Deadlock Recovery:-

   • When deadlock is deleted

   • Some transaction will have to rolled back made a victim to break deadlock.

   Select the transaction as victim that will ensure minimum cost

   Rollback:- determine how far to rollback transaction

   Total rollback about the transaction and then restart it

   Partial rollback:- more effectively to rollback transaction only as far as necessary to break deadlock.

   Secretion happens if same transaction is always chosen as victim

   The system may include the number of roll back in cost faster to avoid starvation.

6. Section B

   5. What can be reason of transaction failure? Discuss the method of performing recovery.

   Answer:

   Reasons of transaction failure

   1. Transaction error:- This error happens when a user about the transaction or logical performing error for some operation like a division by zero etc.

   2. Computer failure:-A hardware and software on network error may cause a computer to fail or hand during the outgoing transactions.

   3. Concurrency control enforcement:- If a transaction cause deadlock or violet serializability then concurrency control system will force the transaction to about.

   4. Disk Failure:- Some task 2 block may lose their data because of read or write mail function or because of a disk read or write head crash.

   So failure are caused by Kaur computer failure transaction error external causes for concurrency control enforcement.

   There are two type of recovery method which facilitate a database management system in recovery recovering the the automatically of a transaction

   1. Log:- Based recovery or manual recovery; log could be sequence of Record which maintain the records of action performing by dealing it is necessary by dealing it's necessary that the log area you need to return before the particular modification and hold on a stable storage media that is file safe.

   2. Recovery with concurrent transaction:- when over one transaction is being executed in parallel the logs are are inter.

   At the time of recovery it's become exhausting for the recovery system to go back all logs and DTU B wing recovering. To raise this example the latest package user the idea of checkpoint automated recovery is of three type.

7. 6. Explain the features of different languages used in RDBMS.

   Answer:

   Features of different language used in RDBMS

   • Relationship between two table or file can be e specified at the time of the table creation.

   • It support client server architecture.

   • It supports distributed database.

   • Multiple level of security; log in at OS level command level and object level.

   • Support a simple Data Structure namely tables for relation.

   • Limit reduancy or replication of data.

   • Betta integrity as data Independence is so do not have to be aware of underline object.

   • Awful logical database in dependency data can be viewed in ways by different users.

   • Expandability is relative Li easy to active by adding new views of the data as they are required.

   • Support off queries using SQL or other appropriate languages.

   • Battery backup and recovery produces.

   • Provide multiple interfaces.

   • Solve many problems created by other data models.

   • The ability to handle efficiency simple data type.

   • Multiple user can access which is not possible in DBMS.

   So these are some unique features of language used in RDBMS.

8. 7. How data constant are applied in Oracle? Explain giving example.

   Answer:

   Data constraints:- Oracle Param it data contains to be attached to table column level by SQL Syntax that check data for integrity earlier stage. If the data constant is attached to specific cell in a table reference to the context of another cell in the table then user will have to use table level constant.

   To define an integrity constraints:- A rule that restricts the value in a database first of Oracle database lets you check 6 type of consistent and let you declare them in two ways for stop

   You can define syntactically in two ways,

   • As parts of the definition of an individual column or attribute for stop this is called inline specification.

   • As part of the table definition for stop this is called out of line specification.

   NOT NULL contains must be declared inline. And other contains can be declared either in line or out of line.

   Constraints clause can appear in in the the forming following statement

   • CREATE TABLE:-( see CREATE TABLE )

   • ALTER TABLE:- (see ALTER TABLE)

   • CREATE VIEW:- (see CREATE VIEW)

   • ALTER VIEW (see ALTER VIEW)

9. 8. Write the methods of creating a blind and dropping trigger in Oracle.

   Answer:

   CREATE TRIGGER:- Use the create a trigger statement to create and enable a database Trigger which is is

   • a stored PL SQL block associated with a table a schema for the database

   • an anonymous PL SQL block or a call to theproceed implemented in PL SQL or java

   • when a trigger is create the database enable it automatically you can subsequently disable and enable a trigger with the disable and enable clause of a alter Trigger or alter table statement.

   The ALTER TRIGGER or ALTER TABLE statement.

   DROP TRIGGER:-Use the Drop Trigger statement to the remove a database Trigger from the database.

   • the trigger must be in your own schema for you must have the Drop any e Trigger system privilege. To drop a trigger on database in another user's schema you must also have the administrator disable Trigger system.

   • the statement drop the salary check trigger in a schema.

   For enable Trigger Oracle automatically perform the below tasks

   • Oracle runs the trigger of the east type in plane and find sequence when more than one Trigger is tied by single statement.

   • it performs integrity constraint

   • It performs integrity constraint checking.

   • it provides read consistent view for queries and construct

   • Oracle used to face commit if a trigger update remove table in a distributed database.

11. Section C

    9. All questions are compulsory
    (a) distinguish between DBMS and RDBMS

    Answer:

    DBMS	RDBMS
    In DBMS relationship b and w2 table or file1 are maintained programatically.	In RDBMS relationship b&w to table or file can be specified at the time of the table creation.
    DBMS does not support client server architecture.	RDBMS support client server architecture
    DBMS does not support distributed	RDBMS please Support and vote me.
    No security	Multiple level of security
    	Log in at OS level common level and object level.

12. (b) define Thomas write rule.

    Answer:

    Thomas write rule:- Modified version of the timestamp ordering Protocol in which obsolete write operation may be ignore under certain circumstances.

    • When Ti attempt to write data item q if TS then it is attempting to write an obsolete value of hence rather than rolling back as the timestamp ordering protocol would have done These write operation can be ignore.

    • otherwise the protocol is the same as the timestamp ordering protocol for stock

    • Thomas write rule allowed greater potential Kong currency unlike previous protocol. It allows some view serializable schedules that are not conflict serializable.

13. (c) what are checkpoints?

    Answer:

    Checkpoint:- Problem is there recover word used as a discuss easier

    1. searching the entry log is a time consuming.

    2. we might unnecessarily Redo transaction which have already.

    3. Output there update to database.

    streamline recover produced by periodically perform checkpoint

    output all logs recover currently in main memory on to stable storage.

    write a log record checkpoint onto stable storage.

14. (d) write the producer of grouping table data in Oracle.

    Answer:

    Grouping distinguish row from regular grouped row group by execution such as rollup add your Set of all value is represented by null using the grouping function you can distinguish anal represent the set of all the values in row from a null in a regular row.

    The Expo in the grouping function must match one of the expression in the group by clause. The function return a value of 1 if the value of expert In the row is the null representing the set of all type valuable otherwise it return 0. The data type of the value returned by grouping function is Oracle number referred to the select group by clause for a discussion of the terms.

16. (e) distinguishing between database and form triggers.

    Answer:

    Database trigger:- Fired when a DML operation is performed the

    • De manipulate data stored in Oracle table.

    • they can cause other database trigger to file.

    • they can be fixed from any season executing. the trigger dbl statement

    From Trigger:- Fired is the response to any E event of that take place while working with other forms of.

    • De manipulate data in Oracle table as well as variable inform.

    • They cannot cause other form trigger to fire but can fire other database Trigger.

    • they can be shipped only form the the form that define the trigger.