Question Bank......

Chapter 1

[2 marks question]

1. List the disadvantages of file processing system over DBMS.
2. List the advantages of DBMS over file processing system
3. Define data abstraction and its levels.
4. Enlist various types of data models.
5. List the functions of database administrator.
6. Define data independence and its types.
7. What are component of DBMS.
8. State the characteristics of DBMS[data integrity, data independence ,data abstraction,security,backup and recovery]
9. Explain any one responsibility of database manager.

[4 marks question]

1. Compare Network model and Hierarchical data model.
2. Compare Network model and relational data model.
3. Describe network model by suitable example.
4. Explain types of database users.
5. Explain the client server architecture.
6. Explain overall structure of a DBMS.
7. What is Data abstraction? Explain the levels of data abstraction.
8. What is DBMS? Explain any 2 functions of DBMS.
9. Define Query processor and explain diff. component of query processor.

Chapter 2

[2 marks question]

1. Explain the term database schema
2. Define the terms attribute,domain,tuple,relations
3. Define term primary key, foreign key and candidate key.
4. What is meant by database authorization? give one example
5. Six properties of relational data model

[4 marks question]

1. Explain the term primary key and candidate key with example
2. What is IC ? Explain any one in detail
3. What is revoke and grant privileges?

Chapter 3

[2 marks question]

1. What is statement is used in pl/sql to display the output?
2. Explain the use of truncate statement. Give example
3. Explain the use of create table statement. Give example
4. Explain the use of insert into statement. Give example
5. Explain the use of desc/describe statement. Give example
6. Explain the use of rename statement. Give example
7. Explain the use of alter table statement. Give example
8. Explain the use of select clause with an example
9. Explain the use of from clause with an example
10. Explain the use of where clause with an example
11. Explain the use of group by clause with an example
12. Explain the use of order by clause with an example
13. Explain the use commit and rollback with an example

[4 marks question]

1. Explain sql data type with an example
2. What are aggregate functions? explain with an example
3. Explain string function with an example
4. Explain set operator with an example
5. what is cursor explain different types cursor with an example
6. what is trigger explain with an example
7. explain pl/sql block structure with an example
8. Define the following
1. Weak entity
2. Strong entity
3. Partial key
4. Owner Entity

click on follwing link to dowload doc file
Question bank Doc file

PL/SQL Program

Write a program to display 10 reverse number using for loop

SQL> declare
i number;
begin
for i in reverse 1..10
loop
dbms_output.put_line(i);
end loop;
end;
.
SQL> /
10
9
8
7
6
5
4
3
2
1

PL/SQL procedure successfully completed.

Write a program to display 1 to 10 number using for loop

SQL>declare
i number;
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;

SQL> /
1
2
3
4
5
6
7
8
9
10

Functional Dependency

Functional dependency

[1]Functional dependency describes the relationship between
attributes in a relation.

[2]For example, if A and B are attributes of relation R, and B is
functionally dependent on A ( denoted A B), if each value of
A is associated with exactly one value of B. ( A and B may each
consist of one or more attributes.)




The above answer is for 2marks....for 4m giv any real time example along vth above answer

Transaction Notes

View the presentation online, or click on the link at the bottom to download the presentation and watch on your PC.

Transaction Notes

OR
Click on DOWNLOAD

TRANSACTION


****Best of luck ****

TRIGGER

What is trigger ? Explain with an example

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

The Syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF ]
[INSERT [OR] | UPDATE [OR] | DELETE}]
ON table_name
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- Sql statements
END;

For Example: The price of a product changes constantly. It is important to maintain the history of the prices of the products.

We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

1) Create the 'product' table and 'product_price_history' table

CREATE TABLE product_price_history
(
product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2)
);

CREATE TABLE product
(
product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2)
);

2) Create the price_history_trigger and execute it.

CREATE or REPLACE TRIGGER price_history_trigger
BEFORE UPDATE OF unit_price
ON product
FOR EACH ROW
BEGIN
INSERT INTO product_price_history
VALUES
(:old.product_id,
:old.product_name,
:old.supplier_name,
:old.unit_price);
END;

/

3) Lets update the price of a product.

UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100

Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.

4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.

CURSOR

What is cursor ? explain with an example

[1] A cursor is a temporary work area created in the system memory when a SQL statement is executed.
[2] This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time.

There are two types of cursors in PL/SQL:
[1]Implicit cursors:
[2]Explicit cursors:

[1]Implicit cursors:These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

[2]Explicit cursors:They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time,

For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:

DECLARE var_rows number(5);

BEGIN

UPDATE employee

SET salary = salary + 1000;

IF SQL%NOTFOUND THEN

dbms_output.put_line('None of the salaries where updated');

ELSIF SQL%FOUND THEN

var_rows := SQL%ROWCOUNT;

dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

END IF;

END;

In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.

INTRODUCTION TO DATABASE SYSTEM

INTRODUCTION TO DATABASE SYSTEM

Click on Download button
DOWNLOAD

PL/SQL Variables

These are placeholders that store the values that can change through the PL/SQL Block.

The General Syntax to declare a variable is:

Variable_name datatype [NOT NULL: = value];
• variable_name is the name of the variable.
• datatype is a valid PL/SQL datatype.
• NOT NULL is an optional specification on the variable.
• value or DEFAULT valueis also an optional specification, where you can initialize a variable.
• Each variable declaration is a separate statement and must be terminated by a semicolon.

For example, if you want to store the current salary of an employee, you can use a variable.
DECLARE
salary number (6);

* “salary” is a variable of datatype number and of length 6.

When a variable is specified as NOT NULL, you must initialize the variable when it is declared.

For example: The below example declares two variables, one of which is a not null.
DECLARE
salary number(4);
dept varchar2(10) NOT NULL := “HR Dept”;
The value of a variable can change in the execution or exception section of the PL/SQL Block. We can assign values to variables in the two ways given below.

1) We can directly assign values to variables.
The General Syntax is:
variable_name:= value;

2) We can assign values to variables directly from the database columns by using a SELECT... INTO statement. The General Syntax is:

SELECT column_name

INTO variable_name

FROM table_name

[WHERE condition];

Example: The below program will get the salary of an employee with id '1116' and display it on the screen.
DECLARE
var_salary number(6);
var_emp_id number(6) = 1116;
BEGIN
SELECT salary
INTO var_salary
FROM employee
WHERE emp_id = var_emp_id;
dbms_output.put_line(var_salary);
dbms_output.put_line('The employee '
|| var_emp_id || ' has salary ' || var_salary);
END;
/

PL/SQL

What is PL/SQL?
PL/SQL stands for Procedural Language extension of SQL.

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
A Simple PL/SQL Block:
Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

A PL/SQL Block consists of three sections:
• The Declaration section (optional).
• The Execution section (mandatory).
• The Exception (or Error) Handling section (optional).

Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE.
This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section.
Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END.
This is a mandatory section and is the section where the program logic is written to perform any task.
The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION.
This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully.
If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

This is how a sample PL/SQL Block looks.

Oracle Buys Sun

Oracle Corporation (NASDAQ: ORCL) and Sun Microsystems (NASDAQ: JAVA) announced today they have entered into a definitive agreement under which Oracle will acquire Sun common stock for $9.50 per share in cash. The transaction is valued at approximately $7.4 billion, or $5.6 billion net of Sun’s cash and debt. “We expect this acquisition to be accretive to Oracle’s earnings by at least 15 cents on a non-GAAP basis in the first full year after closing. We estimate that the acquired business will contribute over $1.5 billion to Oracle’s non-GAAP operating profit in the first year, increasing to over $2 billion in the second year. This would make the Sun acquisition more profitable in per share contribution in the first year than we had planned for the acquisitions of BEA, PeopleSoft and Siebel combined,” said Oracle President Safra Catz.

“The acquisition of Sun transforms the IT industry, combining best-in-class enterprise software and mission-critical computing systems,” said Oracle CEO Larry Ellison. “Oracle will be the only company that can engineer an integrated system – applications to disk – where all the pieces fit and work together so customers do not have to do it themselves. Our customers benefit as their systems integration costs go down while system performance, reliability and security go up.”

There are substantial long-term strategic customer advantages to Oracle owning two key Sun software assets: Java and Solaris. Java is one of the computer industry’s best-known brands and most widely deployed technologies, and it is the most important software Oracle has ever acquired. Oracle Fusion Middleware, Oracle’s fastest growing business, is built on top of Sun’s Java language and software. Oracle can now ensure continued innovation and investment in Java technology for the benefit of customers and the Java community.

The Sun Solaris operating system is the leading platform for the Oracle database, Oracle’s largest business, and has been for a long time. With the acquisition of Sun, Oracle can optimize the Oracle database for some of the unique, high-end features of Solaris. Oracle is as committed as ever to Linux and other open platforms and will continue to support and enhance our strong industry partnerships.

“Oracle and Sun have been industry pioneers and close partners for more than 20 years,” said Sun Chairman Scott McNealy. “This combination is a natural evolution of our relationship and will be an industry-defining event.”

“This is a fantastic day for Sun’s customers, developers, partners and employees across the globe, joining forces with the global leader in enterprise software to drive innovation and value across every aspect of the technology marketplace,” said Jonathan Schwartz, Sun’s CEO, “From the Java platform touching nearly every business system on earth, powering billions of consumers on mobile handsets and consumer electronics, to the convergence of storage, networking and computing driven by the Solaris operating system and Sun’s SPARC and x64 systems. Together with Oracle, we’ll drive the innovation pipeline to create compelling value to our customer base and the marketplace.”

“Sun is a pioneer in enterprise computing, and this combination recognizes the innovation and customer success the company has achieved. Our largest customers have been asking us to step up to a broader role to reduce complexity, risk and cost by delivering a highly optimized stack based on standards,” said Oracle President Charles Phillips. “This transaction will preserve and enhance investments made by our customers, while we continue to work with our partners to provide customers with choice.”

The Board of Directors of Sun Microsystems has unanimously approved the transaction. It is anticipated to close this summer, subject to Sun stockholder approval, certain regulatory approvals and customary closing conditions.

There will be a conference call today to discuss the transaction at 5:30 a.m. Pacific time. Investors can listen to the conference call by dialing (719) 234-7870, passcode 923645. A replay will be available for 24 hours after the call ends at (719) 884-8882, passcode: 923645. A live audio webcast of the call will be made available at www.oracle.com/investor and a replay will be available for seven days after the call ends.

Oracle is the #1 Relational Database

Gartner 2008 Worldwide RDBMS Market Share Reports 48.9% Share for Oracle (*)

Gartner has published their market share numbers by operating system for 2008 based on total software revenues. According to Gartner, Oracle

* Continues to be #1 overall with 48.9 per cent share
* Continues to hold more market share than its six closest competitors combined
* Continues to be #1 on Linux with 75.8 per cent share

Transaction

1. ACID Properties

To preserve the integrity of data, the database system must ensure the following properties of the transactions:

Atomicity : Either all operations of the transaction are properly reflected in the database or none are.

Consistency : Execution of a transaction in isolation preserves the consistency of the database.

Isolation : Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executing transactions. That is, for every pair of transactions Ti and Tj , it appears to Ti that either Tj finished execution before Ti started or Tj started execution after Ti finished (even though they may be executing concurrently -- their operations are interleaved)

Durability : After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.

These properties are often referred to as the ACID properties.

Suppose X is a data item in database. The following are two operations to access item X:

--read(X) : transfer the data item X from the database to a local buffer belonging to the transaction that executed the read operation

--write(X) : transfer the data item X from the local buffer of the transaction that executed the write back to the database

Example: (Fund transfer) Transaction to transfer $50 from account A to account B:

read(A)
A := A - 50
write(A)
read(A)
B := B + 50
write(B)

consistency: the sum of balances of account A and account B is unchanged by the execution of the transaction. Note that the database is temporarily inconsistent after step 3 but before step 6.

atomicity : two updates are performed at steps 3 and 6. If the transaction fails after step 3 and before step 6, the system should ensure that the updates are not reflected in the database, else an inconsistency will result.

durability : once the transaction has completed (i.e., the transfer of the $50 has taken place), the updates to the database by the transaction must persist despite failures

isolation : if between steps 3 and 6, another transaction is allowed to access the partially updated database, it will see an inconsistent database (the sum of balances of account A and account B will be less than it should be). The database system should ensure that this inconsistent database state be not seen by another transaction.

This can be trivially achieved by executing transactions serially, that is, one after the other. However, running multiple transactions concurrently increase the throughput.

2. Transaction State

A transaction must be in one of the following states (using the transaction model in book):
Active : the initial state; the transaction stays in this state while it is executing
Partially committed : after the final statement has been executed
Failed : normal execution can no longer proceed
Aborted: after the transaction has been rolled back and the database restored to its state prior to the start of the transaction.

Two options after it has been aborted:

-- restart the transaction, only if no internal logical error

-- kill the transaction

Committed, after successful completion

3. Concurrent Executions

Multiple transactions are allowed to run concurrently in the system. Advantages are:

increased processor and disk utilization, leading to better transaction throughput: one transaction can be using the CPU while another is reading from or writing to the disk

reduced average response time for transactions : short transactions need not wait behind long ones

concurrency control schemes -- mechanisms to control the interaction among the concurrent transactions in order to prevent them from destroying the consistency of the database

4. Schedules

· A schedule of a set of transactions is an ordering of the instructions of the transactions such that

¨ it consists of all instructions of each transaction of this set

¨ it preserves the order in which the instructions appear in each individual transaction

Example: (a) Let T1 transfer $50 from account A to account B, and T2 transfer 10% of the balance from account A to account B. The following schedule (schedule I) is a serial schedule, in which T1 is followed by T2 .




(b) Let T1 and T2 be the transactions defined previously. The following schedule (schedule II) is not a serial schedule, but it is equivalent to schedule I


Note that in both schedules I and II, the sum of the balances of A and B is preserved.


(c) The following concurrent schedule (schedule III) does not preserve the value of the sum of the balances of A and B.


5. Serializability

· Basic assumption: each transaction preserves database consistency

· Thus, serial execution of a set of transactions preserves database consistency

· A (possible concurrent) schedule is serializable if it is equivalent to a serial schedule. Different forms of schedule equivalence give rise to the notions of

(i) conflict serializability

(ii) view serializability

· To focus on the main ideas, in our discussions, we ignore operations other than read and write instructions, and assume that transactions may perform arbitrary computations on data in local buffers in between reads and writes. That is, the simplified schedules consist of only read and write instructions.



Conflict Serializability

· Instructions Ii and Ij of transactions Ti and Tj respectively conflict if and only if there exists some item Q accessed by both Instructions Ii and Ij and at least one of these instructions wrote Q.

· (i) Ii = read(Q) and Ij = read(Q) : Ii and Ij don't conflict

(ii) Ii = read(Q) and Ij = write(Q) : Ii and Ij conflict

(iii) Ii = write(Q) and Ij = read(Q) : Ii and Ij conflict

(iv) Ii = write(Q) and Ij = write(Q) : Ii and Ij conflict

· Intuitively, a conflict between Ii and Ij forces a temporal order between them. If Ii and Ij are consecutive in a schedule and they do not conflict, their results would remain the same even if they had been interchanged in the schedule

· If a schedule S can be transformed into a schedule S' by a series of swaps of non-conflicting instructions, we say that S and S' are conflict equivalent.

· We say that a schedule S is conflict serializable if it is conflict equivalent to a serial schedule.

· Example of a schedule that is not conflict serializable:

T3 T4

read(Q)

write(Q)

write(Q)















We are unable to swap instructions in the above schedule to obtain either the serial schedule
or the serial schedule .

· Schedule III below can be transformed into Schedule I , a serial schedule where T1 is followed by T2 , by a series of swaps of non-conflicting instructions. Therefore, schedule III is conflict serializable.


ead(B)

View Serializability

· Let S and S' be two schedules with the same set of transactions. S and S' are view equivalent if the following three conditions are met:

(a) For each data item Q, if transaction Ti reads the initial value of Q in schedule S, then transaction Ti must in schedule S' also read the initial value of Q.

(b) For each data item Q, if transaction Tj executes read(Q) in schedule S, and that value was produced by transaction Ti (if any), then transaction Tj must in schedule S' also read the value of Q that was produced by transaction Ti .

(c) For each data item Q, the transaction (if any) that performs the final write(Q) operation in schedule S must perform the final write(Q) operation in schedule S'.

· A schedule S is view serializable if it is view equivalent to a serial schedule.

· Every conflict serializable schedule is also view serializable.

· Schedule IV below is a schedule which is view serializable but not conflict serializable.

It is view equivalent to the serial schedule T3, T4, T6 .

T3 T4 T6

read(Q)

write(Q)

write(Q)

write(Q)

Schedule IV



















· In schedule IV, T4 and T6 each performs a write(Q) without having performed a read(Q) operation. Such a write operation is called a blind writes.

It can be shown that blind writes appear in every view-serializable schedule that

is not conflict serializable.

Transaction Concept

Transaction Concept

A transaction is a set of operations that collectively perform a single logical function.

E.g., transfer of funds from a checking account to a savings account

a single logical function from the customer's standpoint

to achieve this function, two operations are involved: (1) decrease the checking account by an X amount; and (2) increases the savings account by the same X amount

A transaction must see a consistent database.

In the funds transfer example, suppose the checking account balance before the execution of the transaction is $1000.00 and the amount to be transferred is $300.00. Then, the checking account balance should remain $1000.00 before it is changed to $700.00. It should not be that before the transaction reads the balance, the balance is $1000.00 and after reading the balance but before changing it to $700.00, it becomes $1200.00 (probably updated by another transaction).

When the transaction is committed, the database must also be consistent. Note that the database may not be consistent during the execution of the transaction.

· Two main issues to deal with:

-- failures of various kinds, such as hardware failures and system crashes

-- concurrent execution of multiple transactions

Database Schema

Database Schema

The description of a database. Includes
descriptions of the database structure and the constraints that should hold on the database.

DBMS Defines schemas at three levels :

- Internal schema at the internal level to describe data
storage structures and access paths. Typically uses a physical data model.

- Conceptual schema at the conceptual level to describe the structure and constraints for the whole database. Uses a
conceptual or an implementation data model.

- External schemas at the external level to describe the
various user views. Usually uses the same data model as the conceptual level.

Database Instance: The actual data stored in a database at a particular moment in time. also called database state