MCS 043 Free Assignment April 2011

Question 2

Solution

           

(a)   Select Supplier.su_id, supplier.Name.su (Supply of per quantity of supply) from supplier

On supplier.su_id inner join supply of par su_id group by su_id

(b)   Begin Transaction Trans

Go

Use Adventure work 2008 R

GO

SELECT + Money

From Adventurework 2008 R

Human Resource.Account

Where Acount No. = 123456

Commit Transaction @ Account No

Go

= Begin Trasaction Tran 2

Go

Use Adventure Work 2008 R2

Go

Insert into Adventure work 2008 R2

VALUES (10000, ACCOUNT No. )

Commit Transaction @ Account No.

(c)    Create view supplier

As

Select * FROM Supplier

Where su_id = @ su_id

= create view system manager

As

Select * from supplier su_id, supplier.su_name

Supplier.su_address, supplier.su_status

Supply of part.su_id, supply of part. Pa_id, supply of part.

Date of supply, supply of part.quanitity of

Supply, supply of part.su_price from

Supplier on supplier.su_id inner join

Supply of part.su_id

(d)   Transaction processing is designed to maintain a computer system (typically a database or some modern filesystems) in a known, consistent state, by ensuring that any operations carried out on the system that are interdependent are either all completed successfully or all canceled successfully.

For example, consider a typical banking transaction that involves moving $700 from a customer's savings account to a customer's checking account. This transaction is a single operation in the eyes of the bank, but it involves at least two separate operations in computer terms: debiting the savings account by $700, and crediting the checking account by $700. If the debit operation succeeds but the credit does not (or vice versa), the books of the bank will not balance at the end of the day. There must therefore be a way to ensure that either both operations succeed or both fail, so that there is never any inconsistency in the bank's database as a whole. Transaction processing is designed to provide this.

Deadlock Detection

Deadlock can occur whenever two or more processes are competing for limited resources and the processes are allowed to acquire and hold a resource (obtain a lock) thus preventing others from using the resource while the process waits for other resources.

Two common places where deadlocks may occur are with processes in an operating system (distributed or centralized) and with transactions in a database.  The concepts discussed here are applicable to any system that allocates resources to processes.

Locking protocols such as the popular Two Phase Locking (see concurrency control) give rise to deadlock as follows: process A gets a lock on data item X while process B gets a lock on data item Y.  Process A then tries to get a lock on Y.  As Y is already locked, process A enters a blocked state.  Process B now decides to get a lock on X, but is blocked.  Both processes are now blocked, and, by the rules of Two Phase Locking, neither will relinquish their locks. 

This is a deadlock: process A is waiting for a resource held by process B and process B is waiting for a resource held by process A.  No progress will take place without outside intervention.  Several processes can be involved in a deadlock when there exists a cycle of processes waiting for each other. Process A waits for B which waits for C which waits for A.

Four conditions must hold for deadlock to occur:

1.    Exclusive use – when a process accesses a resource, it is granted exclusive use of that resource.

1.  2.    Hold and wait – a process is allowed to hold onto some resources while it is waiting for other resources.

2.  3.    No preemption – a process cannot preempt or take away the resources held by another process.

3.  4.    Cyclical wait – there is a circular chain of waiting processes, each waiting for a resource held by the next process in the chain.

QUESTION-3

(a)    Select from Guide Table Group by Guide_Id

      (b)

<University>

<Teacher>

<id> 101 </id>

<Name> Rajat Sharma </name>

<Qualification> B.Tech </Qualification>

<Work Experience> 3 Years </work experience>

<published materials> Data Structure </published materials>

</Teacher>

</University>

=Begin Trasaction Trans 3

Go

Use Adsventrue Work 208 R2

Go

Update Adventure work 2008 R2 Set

 Interest = @ interest where

Account No. = @ Account No.

Commit Transaction @ Acocount, @ Interest

GO

= Begin Trasaction Trans 4

Go

Use adventure Work 208 R2

Go

Select * from Adventure work 2008 Rs

Commit Transaction

Go.