Database Management System

Mar 15 • Engineering Sample Papers • 2278 Views • 1 Comment on Database Management System

k4

Database Management System

Full Marks:70                                                                Time:3 Hours

Answer Question No.1 which is compulsory and any five from rest.

Q.1 Answer the following questions:

(a) What is Database & DBMS ?

Ans:-Database is nothing but a organized collection of interrelated data and set of programs to access those data.

DBMS is nothing but a software package which is used to store, access, retrieve  and manipulate a large volume of data  of database on user’s request.

(b)   Define Primary key and Candidate key.

Ans :

The primary key uniquely identifies each record in a table.

Candidate key is an attribute or set of attributes that uniquely     identifies a record in a table.

Example:-Student(Roll, Name, Address, DOB)

Here {Roll}, {Name, Address} and {Name, DOB} are candidate keys.

But{Roll} is the primary key

All primary keys are candidate keys but all candidate keys are not primary keys.

(c) What do you mean by Data dictionary ?

Ans:-It is a special file  that contains the result of DDL statements.

It contains metadata means data about data.

Suppose you create a table in DBMS then the name of table, columns, name, constraiting information, datatypes are store in specified tables.Such tables are called data dictionary.

 

(d)  Define Deadlock.

Ans : A process request the resources, the  resources are not available at that time. So, the process enter into the waiting state i.e the requesting resources are held by another waiting process . So both are in waiting state. This situation is called deadlock.

For example p1 and p2 are two processes, r1 and r2 are two resources, p1 request the resources r1, r1 held by processp2, p2 request the resource r2, r2 is held by process p1, then both are entered into waiting state. So, this situation is called deadlock.

(e)  Define concurrency.

Ans: When multiple transactions of a database executed at same time is known as concurrency.  These concurrent transactions may perform various database updates simultaneously.

(f)  What is the difference between REDO and UNDO ?

Ans: REDO:- It sets the value of all data items updated by transaction Ti to the new values.

UNDO:-It restores the value of all items updated by transaction Ti to the old values.

(g)  Define Join and types of join.

Ans: It is a binary operation that takes 2 relations and produces a new relation. The join are basically divided into 2 types such as:

  • Inner join
  • Outer join

Again outer join is devided into 3 types such as:

  • Left outer join
  • Right outer join
  • Full outer join

 

(h)  Differentiate Relational algebra and Relational calculus.

Ans:

Relational Algebra

 

Relational calculus

It  is a procedural query language.It  is a non-procedural query language.
It consists of operations that take 1 or 2 relations as input and produce a new relations as output.It has 2 form such as:Tuple relational calculus:- It is based on tuple variable(row wise).Domain relational calculus:- It is based on domain variable(column wise).

 

(i)   Define Normalization. Is it necessary for RDBMS ?

Ans: It is a process of simplifying the relationship among data element in a record.

Normalization is necessary for RDBMS because normalization reduces redundancy. The redundancy can cause problems with storage , retrieval and updation of data in database and the redundancy can generates inconsistencies. So, the  normalization need foe RDBMS.

(j)  Explain with example the following SQL commands.

(a)  create

(b) insert

(c) alter

(d) truncate

(e) rename

(f) destroy

Ans:  (a) Create command is used to create a table.

        Create table student(name varchar2(30),roll number(10),           mark number(3),address  varchar2(40));

(b) insert command is used to insert data into the table.

   Insert into student(name,roll,mark,address) values (‘karishma’, 100,550,’Odisha’);

         (c) alter command is used to change the table data/definition.

Alter table student add(fname varchar2(30),DOB date);

(d) truncate command is used to remove all contents from table.

   Truncate  table student;

(e) rename command is used to rename various objects.

    Rename student to temp;

(f) destroy command is is used to destroy.

      Destroy table temp;

Answer any five question .[5*10=50]                                                                                         

No.2.  Define Transaction. Explain different states of a  transaction .

Ans: Collection of operations that form a single logical unit of work are called transactions. A transaction is a unit of program execution that access and possibly update various data item.

Example:- Booking a airline ticket , transfer money fron one account to another.

State of transactions:-

A transaction must be in one the following states:

(1) Active:- The transaction stay in this state while it is executed.

(2) Partially Commited:-It occurs after the final statement has been executed.

(3) Failed:- It occurs after the discovery that normal execution can no longer proceed.

(4) Aborted:- It occurs after the transaction has been rolled back.

(5) Committed:- It occurs after successful execution of transaction.

 

 

 

 

No.3. Explain  the ACID properties associated with database transactions.

 Ans:-To maintain data integrity and consistency when concurrent execution of transaction take place each transaction must satisfy the ACID property.

A stands for Atomicity

C stands for  Concistency

I  stands for Isolation

D stands for Durability

(i)Atomicity:- A transaction must complete its execution as a whole or not at all. In other word either all the operations of the transaction are reflected properly in the database or none of the operations are reflected.

(ii)Consistency:- If a database is in consistent state before the start of transaction, then the database must also be consistent on the successful completion of transaction.

(iii)Isolation:- When multiple transactions are executed on the database concurrently, each transaction must appear as if it has started its operation on the completion of the previous transaction.

(iv)Durability:- Once the execution of the transaction completed successfully all the updates that if carried out on the database persist even if there is a system failure after thr transaction completes execution.

No.4. Explain different CODD rules.

Ans:- 

Codd’s twelve rules are a set of thirteen rules (numbered zero to twelve) proposed by Edgar F.CODD a pioneer of the relational model for databases, designed to define what is required from a database management system in order for it to be considered relational, i.e., a relational database management system (RDBMS).

Rule (0): The system must qualify as relational, as a database, and as a management system.

For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database.

Rule 1: The information rule:

All information in a relational database is represented in only one way, namely as a value in a table.

Rule 2: The guaranteed access rule:

All data must be accessible. This rule is essentially a restatement of the fundamental requirement for primary keys.

Rule 3: Systematic treatment of null values

Rule 4: Active online catalog based on the relational model:

The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the database’s structure (catalog) using the same query language that they use to access the database’s data.

Rule 5: The comprehensive data sublanguage rule:

The system must support at least one relational language that

  1. Has a linear syntax
  2. Can be used both interactively and within application programs,
  3. Supports data definition operations , data manipulation operations , security and integrity constraints, and transaction management operations (begin, commit, and rollback).

Rule 6: The view updating rule:

All views that are theoretically updatable must be updatable by the system.

Rule 7: High-level insert, update, and delete:

This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.

Rule 8: Physical data independence:

Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.

Rule 9: Logical data independence:

Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.

Rule 10: Integrity independence:

Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.

Rule 11: Distribution independence:

The distribution of portions of the database to various locations should be invisible to users of the database. Existing applications should continue to operate successfully :

  1. when a distributed version of the DBMS is first introduced; and
  2. when existing distributed data are redistributed around the system.

Rule 12: The nonsubversion rule:

If the system provides a low-level  interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.

 

 

 

  No.5. Describe different database language.

Ans:- A database system provides 3 languages such as:

(i)    DDL

(ii)   DML

(iii)   DCL

(i)  DDL(Data Definition Language) :- It is used to define , change and drop the structure of a     table. It is also used to remove all rows from a table . It uses create command for defining a database, alter command to change a database, drop command to drop the structure of a table and it uses truncate to remove all rows from the database table.

(ii) DML(Data Manipulation Language) :- It is used to enter, modify, delete and retrieve data from a table . It uses insert command for inserting data into the database, update command to modify data, delete command to delete data and select command to retrieve data from database.

(iii) DCL(Data Control Language):- It is used to control access to the data in a database. It is also used to define the end of a transaction. It uses grant, revoke, commit, rollback for controlling the database.

 

No.6. Exclusive Lock Vs. Shared Lock

  Exclusive Lock:

  • It is also called write lock.
  • When a transaction Ti obtained an exclusive mode lock on time ‘A’ then Ti  can read and write the data item ‘A’.
  • It represented by symbol ‘X’.
  • Example: 

Lock – X (A)

Read (A)

A = A-1000

Write (A)

Unlock (A)

Lock – X (B)

Read (B)

B = B+1000

Write (B)

Unlock (B)

 Shared Lock:

  • It is also called read lock.
  • When a transaction Ti obtained an shared mode lock on time ‘A’ then Ti  can read and write the data item ‘A’.
  • It represented by symbol ‘S’.

Lock –  (A)

Read (A)

Unlock (A)

Lock – S (B)

Read (B)

B = B+1000

Write (B)

Unlock (B)

Display(A + B)

 

 

Tell us Your Queries, Suggestions and Feedback

Your email address will not be published.

One Response to Database Management System

  1. GATE Syllabus of Computer Science and Information Technology 2014 says:

    […] normal forms,integrity constraints), File structures ( indexing,sequential files, B +and B trees), Query languages (SQL ),Transactions and concurrency […]

« »