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

Question paper 1

1. Section-A

   1. (a) What are the unique feature of RDBMS? Describe

   Answer:

   It is a database management system based on the relational model of Tata course top most database in Widespread use today are based on this model.

   Stores any kind of data:- It should not restricted to employee name salary and address any kind of data that exist in real would can be stored in their database system.

   Support any kind of data:- ( accuracy completeness isolation It make sure is that every DBMS that is real purpose of data should not be lost while performing transaction like deleteinsert and update

   It represent Complex relationship B/W data:- Data stored in a database is connected with each other and a relationship is made byB and w data.

   Backup and recovery:- These are many chances of failure of while database. At that time is no one will be able to get the database back and for sure company will be in a big loss.

   So these are the unique feature of relational database management system.

2. (b) Draw a comprehension among various file organisation technique known to you.

   Answer:

   comparison of file organisation techniques

   	Sequentionl
   type of assess	Batch	Online	Bache or online
   Data organisation	Frequently or by key-value.	No particular order	Frequently and by index
   Flexibility in handling enquiries	low flexibility	High flexibility	Very high flexibility
   Availability of up to date data han	not available	available	available
   Speed of retrieve	Slow	Very fast	Fast
   Activity	High	Low	High
   Volatility	LOw	High	High
   Example	Payroll processing and billing operation	airline reservation and banking transaction	customer ordering and billing

    

3. 2. (a) What kind of operations are generally performed on files? Explain with example.

   Answer:

   Operation performance On file:- Operation all files are usually grouped into ritav operation and update operation. Operation on file

   1. Read Operation:-Meant to read the information which is stored into the files.

   2. Write Operation:- For inserting some new concept into a file.

   3. Rename or change the name of the file.

   4. Copy the file from one location to another.

   5. Short aur arrange the content of file.

   6. More aur cut the file from one place to another.

   7. Deleted files on stop

   8. Execute means to run. It means file to display output.

   So operation on the file are usually grouped into to read trater will operation and update operation. The former do not change any data in the file but only locate data in a full certain record so that their file value can be examined and proceed.

4. (b)  What do you mean by primary key and foreign key in RDBMS? What is there significances? Describe with relevant example.

   Answer:

   Primary Key	Foreign Key
   A primary key uniquely identify a record in the database table for stop	A foreign key deferred to the field in a table which is primary key of another table.
   A table can contain only one primary key.	A table can contain more than one foreign key.
   no two rows can carry duplicate value for a primary key attribute.	A foreign key can contain duplicate value.
   A primary key does not allow null values.	A foreign key can contain null values.
   A primary key consists can be e emplicity e define on the temporary table.	A foreign key consistent cannot be in force on the local or global temporary tables.
   A primary key constraint cannot be deployed from the the parent table with referred to the foreign key in the child table.	A foreign key value can be dropped from the child table even if it is referred to the primary key of parent table for stop

6. 3. (a) What is a transaction? How does RDBMS ensure that transactions are executed properly? Describe.

   Answer:

   Transaction:-It is a logical unit of processing in a a DBMS which enters one or more data base assess operation. During the transaction the database is inconsistent. Only once the database is committed the state is a change from one consistent state to another.

   different states of transaction exclusion.

   1. Active State:- A transaction enters into an active state when it execution process begins. during this is straight up a read or write operations can be performed

   2. Partially:-A transaction goes into the partially committed state

   3. Committed State:-When transaction is a computer to state it has already completed the alkylation successful bus stop more over all of its change are recorded to database permanently.

   4. Failed State:-A transaction consider field when any one of the check fail or if a transaction is is aborted when it is in the active state.

   5. Terminated State:-It is a state of transaction teacher transmitted State Man certain transaction will are living in the system can't be Restarted.

   All type of database access operation while are held the beginning and end transaction statement are considered as a single logical transaction during the transaction the database is in inconsistent only once the database is committed the state is changed from one consistent state to another.

7. (b) What is serializability? How do you test serializability of schedules? Discuss with an example.

   Answer:

   Serial ability:- It is Concurrency team when the the concurrent transaction is is equal to one that execute the transaction serial e. Bus schedule is a list of transaction serious schedule define each transaction is executed consistently without any e interferences from other transaction.

   Serialization group is used to key the serializability of schedule.

   Assume are scared dual for contact a graph known as proceed graph this group has pair. When we consists a set of verticals and e consists of a set edge.

   The set ofvertex is used to contain all the transaction participating in the sky dual. The set of Edge is used to contain all edge and for which one of the three condition hold

   1. Create a Node Ti-Tj if Ti executes write (Q) before Tj executes Read (Q).

   2. Create a Node Ti-Tj if Ti executes read (Q) before Tj executes Write(Q).

   3. Create a Node Ti-Tj if Ti executes write (Q) before Tj executes Write (Q).

   Precedence Graph office schedule f

   if are produce a graph contain a a exit Ti and Ti then all the instruction of Ti are executed before the first instruction of TJ is is executed.

   if are process graph for schedule contain cycle then as is non serializable if the the processor graph has no cycle then as it is known as serialisation.

8. 4. (a) What do you mean by deadlock? What are the steps for deadlock recovery? Brief out.

   Answer:

   Deadlock:- A system is a deadlock and if there is a set of transactions such that every transaction in the sector is waiting for another transaction in the set.

   Following are various way of deadlock recovery.

   1. Deadlock Recovery Through Pre-emption:- The ability to take a resource always from process have another process use it and then give it back without the process and nothing. It is highly dependent on the nature of the resources.

   Deadlock recovery through preemption too difficult or sometime impossible.

   2. Deadlock recovery through Rollback:- In this case of deadlock recovery through rollback whenever a deadlock is a detected It is easy to see which resources are needed.

   To do the recovery of deadlock a process that Honours a needed resources is rolled back to a point in time before it acquired some other resources just by starting one of these Earlier Check Point.

   3. Deadlock recovery through Killing Process:- This Method of deadlock recovery through killing process is the simplest way of deadlock recovery.

   Sometime it is the best to kill a process that can be returned from the beginning with no ill effects.

   Above all the ways of deadlock recovery.

9. (b)  What are the various locking techniques for concurrency control? Briefly compare them.

   Answer:

   Locking techniques for concurrency control:- Looking Is an operation which secures permission and record for permission to write a data item.

   Various concurrency control techniques are

   1. Two-phase locking protocol:- Locking is an operation which secure permission to read for permission to write a data items.

   Two phase locking is a processor used to gain ownership of shared resources with creating the possibility of deadlock.

   A transaction in the two phase locking protocol can consume one of the two phases

   1. Growing:- A transaction can only require all the locks it in need is called the lock point.

   2. Shrinking Phase:- In this phase a transaction can only release locked but cannot acquire any.

   2. Time Stamp ordering protocol:-A time stamp is a tag that can be attached to any transaction or any data items which denotes a specific time on which the transaction or the data item had been used.

   3. Multisession Concurrency control:-It easy used to increase the Concurrency.

   4. Validation Concurrency control:-This approach is based on an assumption that the majority of the database operation do not conflict the optimistic approach required nether locking timestamp technique.

   Majority

11. Section-B

    5. (a) What do you mean by transaction failure? write main causes of failure? Describe

    Answer:

    Transaction Failure:-The transaction failure occur when it fails to execute or when it reach a point from where it can't go any further stop if a few transaction or process if I had then this is called transaction failure.

    A transaction may fail due to one or more following reason

    1. Transaction error:-This error happens when user about the transaction or logic programming error or some operational like division by zero etc.

    2. Computer Failure:-A hardware software or network error may cause a computer to fail or hang during An outgoing transaction.

    3. Concurrency control enter cement:- if are transaction cause deadlock or virtual serializability Then conferencing control-m will focus on transaction to about.

    4. Disk failure:-Some dost block may lose their data because of read or write main function or because of a disk read write head create.

    So the failure are caused because computer for transaction error internal cause Aachen currency control enforcement.

12. (b) What is meant by different database modification? Explain its process with an example.

    Answer:

    Deferred Database modification :- Deferred Database modification Techniques occur if transaction does not modify the data base unit it has committed.

    • assume that transaction execute serially.

    • transaction start by waiting record to log.

    • a right X operation results in log record being written where we is the new value for action note old value is not needed for this scheme.

    • The right is not performed on access at this time but is director.

    • vanity priority comments t commit is returned to the log.

    • finally the log record are read and used to actual acute drop previously Deferred writes.

    So in this method all the logs are created and stored in the stable storage and the database is updated when a transaction commits.

13. 6. (a) Explain the client server architecture of Oracle.

    Answer:

    Client-Server Architecture:- In the Oracle database system environment that database application and the database are separated into two parts a front end or client position and the backend or Marwar position. hence that term client server architecture. The client run the database application that access database information and insert with a user through the keyboard screen and pointing device such as a mouse. the server run the Oracle Software and handle the function required for concurrent shared data access to an Oracle database.

    Although the client application and Oracle can be run on the same computer gen8 efficiently can offered by I achieved when the client position and server position are run by different computer connected through a network.

    Client/server Architecture Enna DA order database system environment the database application and the database is separated into two parts a front end and client position and backend or server position hence the term client server architecture.

14. (b) What are the various DCL statements? describe their usage

    Answer:

    DCL Statement:- A data control unique us send text similar to a computer programming language used to control access to data storage in database. In particular it is a component of structured query language.

    Example of DCL commands

    • GRAM to allow specified user to performspecified task.

    • REVOKE it is a remove the user accessibility to database objects.

    The operation for the which village main generated to or provoked from a user or role applied to both the data define language and DML.

    In the Oracle database TCL command issue an implicated commit. His you cannot rollback the command.

    In Pastgre SQL DCL is a transactional and can be rolled back.

    SQ lite Don’t have any TCL command as it does not have usernames on locators.

16. 7. (a) What is the pattern matching? How do you write SQL statement for pattern matching? elaborate with an example

    Answer:

    Pattern Matching:- Pattern Matching pattern matching is a mechanism for checking a value against a pattern for stop a successful match can also a value into its consistent parts. It is a more powerful vision of the switch statement in a Java and it can likewise be used in place of a A Seria a of if else statement.

    The SQL like operator:- The like operator is used in a where clause Tu To for a specified Pattern in a column.

    There part 2 wild cards Offered in use in conjunction with the like operator.

    • % the4 percent sign represent Zero one aur multiple characters.

    • The _represent a single character

    Example

    • WHERE customer name like find any value that state

    • WHERE customer name like a find any value with that end with a.

    • WHERE contact name like a find any value that start with a and end with a full stop

17. (b) What are before type Trigger? What are the application where you can apply before type Trigger? Discuss.

    Answer:

    Before type triggers:- BEFORE triggers run the trigger action before the triggering statement

    This type of Trigger is commonly used in the following situations.

    • When the trigger action determines whether that triggering statement should be allowed to complete. Using a before Trigger for this purpose you can eliminate unnecessary processing of the trailing statement and it evaluate rollback in case where an exception is raised in the trigger action.

    • To Drive specific column value before completing a trigger insert or update statement.

    • BEFORE statement Trigger before The triggering statement that trigger action is Run.

    • BEFORE row trigger:- Before modifying each row affected by the triggering statement and before checking appropriate integrator consistent the trigger action is run if the trigger restriction was not vaulted. you can create as many Trigger of the proceeding define type as you need for each type of DML statement.

18. 8. (a) Oracle office server data related function. Discuss some of them with proper syntax.

    Answer:

    Date related function:-The most commonly used Oracle data function provided by SQL plus is SYSDATE

    Oracle date function IT returns the current date and time in the default Oracle data format.

    1. Add-month(Din)

    The odd month Oracle date function give you are same day and number of month Always. the and can be positive or negative.

    2. Oracle Data function-last-day(d)

    The last day Oracle date function return the last day of the month of the dated. If you want to find the first day of the next month simply add one to the last day results.

    SELECT

    SYS DATE

    LAST-DAY(SYSDATE)EOM

    LAST-DAY(SYSDATE)+1 EOM

    FROM Duel

    SYSDATE EOM FOM

    24-JAN-20 31-March-20 01-Nov-15

    3. Oracle date function –next-day

    The next day Oracle date function return the date of the day of week after dated day of week can be the full name or or alternative.

    These are some date related function in Oracle.

19. (b) What is a trigger which statement are used for creating and dropping Trigger? Discuss with the proper syntax.

    Answer:

    Trigger:- It it consists of an event (an INSCRT, DELETE, or UPDATE statement issued against an isolated table ) and an action. Trigger are used to to preserve Data integrity by checking on or Changing. Data in a consistent manner.

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

    • A Stored PL/SQL block associated with a table in a schema for the database.

    • An stored PL/SQL block aur call to speed implemented in plsql.

    Oracle database automatically Are Trigger when specified condition occur.

    2. DROP TRIGGER Statement:- The drop Trigger statement drop a database from the database.

    • The trigger must be in your own schema or must have that drop any Trigger system privilege. how to drop a trigger on database in another users scheme you must also have administrator database Trigger system privilege.

    • The Core SQL of the table statement

    • TRUNCAIE TABLE table-name parameter is the name of the table to delete andrecreate.

    • The table-name Parameter is the name of the Table to delete and re-create.

21. Section-C

    9. Attempt the following(a) What are advantages of RDBMS

    Answer:

    RDBMS Advantages:-

    • Minimum data Requndrncy

    • Data Consistency:- less likelihood of incorrect orincomplete data. Data is organised in a logical.

    • Integrated data data is organised in a logical relational does making it easy to relate data items.

    • Data sharing allow user from different department to share data.

    • Data accessibility allow a user to access or a relative in flexible manner.

    • uniforms clarity privacy and integrity control administrator can establish control for ascending update and protecting the data.

22. (b) Difference between locking and time Stamping protocols.

    Answer:

    1. Time stamp-Based protocols:-Each transaction is in used and timestamp when it enters the system if an old transaction it has timestamp as a new transaction t is assigned the timestamp of such that.

    The protocol manage contract Execution Sach date timestamp determines the the serializability order.

    2. Lock-based protocols:-

    A lock is a mechanism to control connected access data item.

    Data item can be locked in in two modes

    1. Exclusive (x) mode. Data item can be both real as well as written X locks request using lock instructions.

    2. Shared (S) Mode. data item can only be read as lock is requested using lock as instructions.

    Lock request are made to currency control manage transaction can proceed only after request is generated.

23. (c) Mention the properties of a transaction.

    Answer:

    • Atomicity:-The entrance transaction take place at one or does not happen to all.

    • Consistency:-The database must consist before and after the transactions.

    • Isolation:-Multiple transactions occur independently without Not interface.

    • Durability:- The change of successful transaction occur even if the system failure occurs.

    These aur card a CID the properties of and transaction.

24. (d) What are the database checkpoint? Why are they used.

    Answer:

    Database Checkpoint:- A database checkpoint is a test operation that verify data from the database by comparing that data with the baseline copy stored in your project full stop that may be needed during the test run the database checkpoint use the connection settings stored by the corresponding database table element to connected to the data Bean and actual data from it will stop then it check the retrieve data against the baseline and Data stored in the database table element of your project and report the result.

    For example:- When your test an application that modify database and want to verify that the appropriate table are update correctly.

    Database Checkpoint:- A database checkpoint is a test operation that verify data from the database by comparing that data with the baseline copy stored in your project full stop that may be needed during the test run the database checkpoint use the connection settings stored by the corresponding database table element to connected to the data Bean and actual data from it will stop then it check the retrieve data against the baseline and Data stored in the database table element of your project and report the result.

    For example:- When your test an application that modify database and want to verify that the appropriate table are update correctly.

26. (e) How do you use order by in a SQL select statement?

    Answer:

    Order in SQL select statement

    • the ordered by statement in SQL is used to sort the in either ascending or descending according to one or more columns.

    • By default ordered by sorts and data in ascending order.

    • We can use the keyword De to sort the data in descending order and the keyboard a SSC to sort in ascending order.

    Syntax:-

    SELECT column column

    FROM table name

    ORDER bi column and column

    ASC/DESC

    • Sort according to one column.

    • Sort according to multiple columns

    • Sort according to simple columns

27. (f) Difference between outer join and inner join.

    Answer:

    Outer join:- It is the join with written matching row from the both table as well as any unmatched row from one or both the table.

    Inner join:- It is a very popular join which is used to match the row from two table and return a result which contain only such record that are present in both the table based on the joining conditions.

    Inner join finds and returns matching data from tables while an outer join file and return matching data and some dissimilar data from table.