Docstoc

DBMS Practical File - TechyMantra

Document Sample
DBMS Practical File - TechyMantra Powered By Docstoc
					                                                                         DBMS LAB FILE



Introduction to DBMS

WHAT IS A DATABASE?

A collection of related pieces of data: representing/capturing the information about
a real-world enterprise or part of an enterprise. Collected and maintained to serve
specific data management needs of the enterprise. Activities of the enterprise are
supported by the database and continually update the database.

An example:

University database:

    Data about students, faculty, courses, research-laboratories, course
     registration/enrollment etc.
    Reflects the state of affairs of the academic aspects of the university.

Purpose: to keep an accurate track of the academic activities of the university.



DATABASE MANAGEMENT SYSTEM (DBMS)
A general purpose software system enabling creation of large disk-resident
databases, posing of data retrieval queries in a standard manner, retrieval of query
results efficiently, concurrent use of the system by a large number of users in a
consistent manner, guaranteed availability of data irrespective of system failures.




                                                                                   1
                                                                       DBMS LAB FILE


THERE ARE TWO APPROACHES TO DATA STORAGE:

 FILE-BASED
 DATABASE.

FILE-BASED APPROACH

File-based approaches to data storage are based on relatively simple data
structures, such as the indexed sequential access method (isam), and are usually
implemented for a single application. Files are generally created on an as needed
basis to service the data needs of an application. The files are associated with an
application. The same data may be repeated on many files and stored under
different names. For example, an accounting application may refer to customer
name while a purchasing application may refer to buyer name. The physical
storage characteristics of the same data may be different for different
applications. For example, one application may allow 20 characters for name
while another application allows 25 characters for the same name. Different
business units are responsible for different data.

DATABASE APPROACH

Database approaches to data storage support the sharing of data across
multiple applications with multiple users. Databases are structured in a way
that is meaningful to an organization. For example, if an organization
maintains information on suppliers and the geographic areas they service,
there would be a link in the database between the suppliers and geographic
areas. Databases reduce data redundancy.
         A database management system (dbms) is the software that handles
all database accesses. A dbms presents a logical view of the data to the
users. How this data is stored and retrieved is hidden from the users. A dbms
ensures that the data is consistent across the database and controls who can
access what data.



                                                                               2
                                                                        DBMS LAB FILE



Database characteristics

1. Self-describing nature of the database system: A DBMS catalog stores the
description of the database. The description is called metadata. This allows the
DBMS software to work with different databases.

2. Insulation between program and data: It is called program-data independence. It
allows changing the data storage structure and operations without changing the
DBMS access program.

3. Data Abstraction: A data model is used to store hide storage details and present
the user with a conceptual view of the database.

4. Support the multiple views of the data: Each user may see a different view of the
database, which describes only the data of interest to that user.

5. Sharing of data and multiuser transaction processing: It says that it allows a set
of concurrent users to retrieve and to update the Database. Concurrency with the
DBMS guarantees that each transaction is correctly executed or completely
aborted.




                                                                                3
                                                                        DBMS LAB FILE



Introduction to SQL
SQL, which is an initialize for Structured Query Language, is a language to request
data from a database, to add, update, or remove data within a database, or to
manipulate the metadata of the database.

SQL is generally pronounced as the three letters in the name, e.g. ess-cue-ell, or in
some people's usage, as the word sequel.

SQL is a declarative language in which the expected result or operation is given
without the specific details about how to accomplish the task. The steps required to
execute SQL statements are handled transparently by the SQL database.
Sometimes SQL is characterized as non-procedural because procedural languages
generally require the details of the operations to be specified, such as opening and
closing tables, loading and searching indexes, or flushing buffers and writing data
to file systems. Therefore, SQL is considered to be designed at a higher conceptual
level of operation than procedural languages because the lower level logical and
physical operations aren't specified and are determined by the SQL engine or
server process that executes it.




                                                                                4
                                                                           DBMS LAB FILE



   Characteristics of SQL

  i.   SQL enables end user and system persons to deal with a number of database
       management systems where it is available.

 ii.   Applications written in SQL can be easily ported across systems. Such porting
       could be required when the underlying DBMS needs to upgraded because of
       change in transaction volumes or when a system developed in one environment
       is to be used on another.

iii.   SQL as a language is independent of the way it is implented internally. A query
       returns the same result regardless of whether optimizing has been done with
       indexes or not. This is because SQL specifies what is required and not how it is
       to be done.

iv.    The language while being simple and easy to learn can cope with complex
       situations.

 v.    The results to be expected are well defined in SQL.




                                                                                   5
                                                                     DBMS LAB FILE



Introduction to Oracle
             ORACLE is a fourth generation relational database management
system. In general, a database management system (DBMS) must be able to
reliably manage a large amount of data in a multi-user environment so that many
users can concurrently access the same data. All this must be accomplished while
delivering high performance to the users of the database. A DBMS must also be
secure from unauthorized access and provide efficient solutions for failure
recovery. The ORACLE Server provides efficient and effective solutions for the
major database features.

                    ORACLE consists of many tools that allow you to create an
application with ease and flexibility. You must determine how to implement your
requirements using the features available in ORACLE, along with its tools. The
features and tools that you choose to use to implement your application can
significantly affect the performance of your application.

                    Several of the more useful features available to ORACLE
application developers are integrity constraints, stored procedures and packages,
database triggers, cost-based optimizer, shared SQL, locking and sequences.

   




                                                                             6
                                                                        DBMS LAB FILE



Features of oracle
Overview of Scalability and Performance Features

Oracle includes several software mechanisms to fulfill the following important
requirements of an information management system:

      Data concurrency of a multiuser system must be maximized.
      Data must be read and modified in a consistent fashion. The data a user is
       viewing or changing is not changed (by other users) until the user is finished
       with the data.
      High performance is required for maximum productivity from the many
       users of the database system.



Concurrency

A primary concern of a multiuser database management system is how to control
concurrency, which is the simultaneous access of the same data by many users.
Without adequate concurrency controls, data could be updated or changed
improperly, compromising data integrity.

One way to manage data concurrency is to make each user wait for a turn. The goal
of a database management system is to reduce that wait so it is either nonexistent
or negligible to each user. All data manipulation language statements should
precede with as little interference as possible and destructive interactions between
concurrent transactions must be prevented. Destructive interaction is any
interaction that incorrectly updates data or incorrectly alters underlying data
structures. Neither performance nor data integrity can be sacrificed.

Oracle resolves such issues by using various types of locks and a multiversion
consistency model. These features are based on the concept of a transaction. It is
the application designer's responsibility to ensure that transactions fully exploit
these concurrency and consistency features.

                                                                                7
                                                                         DBMS LAB FILE


Read Consistency

Read consistency, as supported by Oracle, does the following:

    Guarantees that the set of data seen by a statement is consistent with respect to
     a single point in time and does not change during statement execution
     (statement-level read consistency)
    Ensures that readers of database data do not wait for writers or other readers
     of the same data
    Ensures that writers of database data do not wait for readers of the same data
    Ensures that writers only wait for other writers if they attempt to update
     identical rows in concurrent transactions and packages, database triggers,
     cost-based optimizer, shared.

The simplest way to think of Oracle's implementation of read consistency is to
imagine each user operating a private copy of the database, hence the multiversion
consistency model

Locking Mechanisms

Oracle also uses locks to control concurrent access to data. When updating
information, the data server holds that information with a lock until the update is
submitted or committed. Until that happens, no one else can make changes to the
locked information. This ensures the data integrity of the system.

Oracle provides unique non-escalating row-level locking. Unlike other data servers
that ÒescalateÓ locks to cover entire groups of rows or even the entire table,
Oracle always locks only the row of information being updated. Because Oracle
includes the locking information with the actual rows themselves, Oracle can lock
an unlimited number of rows so users can work concurrently without unnecessary
delays.




                                                                                 8
                                                                       DBMS LAB FILE


Automatic Locking

Oracle locking is performed automatically and requires no user action. Implicit
locking occurs for SQL statements as necessary, depending on the action
requested. Oracle's lock manager automatically locks table data at the row level.
By locking table data at the row level, contention for the same data is minimized.

Oracle's lock manager maintains several different types of row locks, depending on
what type of operation established the lock. The two general types of locks are
exclusive locks and share locks. Only one exclusive lock can be placed on a
resource (such as a row or a table); however, many share locks can be placed on a
single resource. Both exclusive and share locks always allow queries on the locked
resource but prohibit other activity on the resource (such as updates and deletes).

Manual Locking

Under some circumstances, a user might want to override default locking. Oracle
allows manual override of automatic locking features at both the row level (by first
querying for the rows that will be updated in a subsequent statement) and the table
level.

Quiesce Database

Database administrators occasionally need isolation from concurrent non-database
administrator actions, that is, isolation from concurrent non-database administrator
transactions, queries, or PL/SQL statements. One way to provide such isolation is
to shut down the database and reopen it in restricted mode. You could also put the
system into quiesced state without disrupting users. In quiesced state, the database
administrator can safely perform certain actions whose executions require isolation
from concurrent non-DBA users.




                                                                               9
                                                                       DBMS LAB FILE


Real Application Clusters

Real Application Clusters (RAC) comprises several Oracle instances running on
multiple clustered computers, which communicate with each other by means of a
so-called interconnect. RAC uses cluster software to access a shared database that
resides on shared disk. RAC combines the processing power of these multiple
interconnected computers to provide system redundancy, near linear scalability,
and high availability. RAC also offers significant advantages for both OLTP and
data warehouse systems and all systems and applications can efficiently exploit
clustered environments.

Portability

Oracle provides unique portability across all major platforms and ensures that your
applications run without modification after changing platforms. This is because the
Oracle code base is identical across platforms, so you have identical feature
functionality across all platforms, for complete application transparency. Because
of this portability, you can easily upgrade to a more powerful server as your
requirements change.

Overview of Manageability Features

People who administer the operation of an Oracle database system, known as
database administrators (DBAs), are responsible for creating Oracle databases,
ensuring their smooth operation, and monitoring their use. In addition to the many
alerts and advisors Oracle provides, Oracle also offers the following features:

Self-Managing Database

Oracle Database provides a high degree of self-management - automating routine
DBA tasks and reducing complexity of space, memory, and resource
administration. Oracle self-managing database features include the following:
automatic undo management, dynamic memory management, Oracle-managed
files, and mean time to recover, free space management, multiple block sizes, and
Recovery Manager (RMAN).


                                                                               10
                                                                  DBMS LAB FILE



SQL commands
SQL consisting of DDL, DML, DCL, TCL commands.

DDL

Data definition language (DDL) statements are used to define the database
structure or schema.

DDL commands: create, alter, drop, rename, truncate.

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for
the records are removed

RENAME - rename an object



DML

Data manipulation language (DML) statements are used for managing data within
schema objects

Dml commands: insert ,update, delete, select

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table.

SELECT - retrieve data from the a database




                                                                          11
                                                                         DBMS LAB FILE


DCL

Data control language (DCL) statements is used to create roles, permissions, and
referential integrity as well it is used to control access to database by securing it.

DCL commands: grant, revoke

GRANT - gives user's access privileges to database

REVOKE - withdraw access privileges given with the grant command



TCL

Transaction control (TCL) statements are used to manage the changes made by
DML statements. It allows statements to be grouped together into logical
transactions.



TCL commands: commit, rollback, save point

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last commit.

.




                                                                                 12
DBMS LAB FILE




        13
                                                      DBMS LAB FILE


Queries to create a table and insert values into it



Command for creating a table:




To insert values into table:




To view all contents of a table:




                                                              14
                                                                            DBMS LAB FILE


Queries to create a table and insert values into it


Command for creating a table:
“create table table_name(column_name data_type(size));”

e.g.:-

“create table emoloyee(name varchar2(10),id number(10),address   varchar2(10),contact_num
number(10));



To insert values into table:
“insert into table_name values();”

e.g.:-

“insert into employee values(‘Ria’,12,’ranjit ave’,654123);”



To view all contents of a table:
“select * from table_name;”

e.g:-

“select * from employees;”




                                                                                    15
                                           DBMS LAB FILE


Queries to select values from dual table


A)




B)




C)




D)




                                                   16
                                                                               DBMS LAB FILE


Queries to select values from dual table
Dual table



Queries:-

A) “select ascii(‘a’) small_a,ascii(‘A’) big_a from dual;”

This command is used to show the ascii values of characters from dual table.




B) “select sysdate from dual;”

sysdate command is used to show the current date of system.




C) “select length (‘computer’) from dual;”

length command is used to find the length of a given string.




D) “select 2*2 from dual;”

This query display result on execution.




                                                                                       17
     DBMS LAB FILE




E)




F)




G)




             18
                                                                                  DBMS LAB FILE




E) “select add_months (sysdate,11) months from dual;”

add_months command add or subtract a number of months to/from a date value.




F) “select last_day (sysdate) from dual;

Last_day command returns the date of the last day of the month containing the date parameter.




G) “select concat (‘computer’,’science’) from dual

concat command combines the two words which are separated by white space.




                                                                                          19
                                        DBMS LAB FILE


Queries to like, in, between commands




Use of LIKE operator




Use of IN operator




Use of BETWEEN operator




                                                20
                                                                                   DBMS LAB FILE


Queries to like, in, between commands


Use of LIKE operator
The % sign in the pattern match zero, one or more characters. It cannot match a NULL.

e.g:-

“select name from employee where name like (‘s%’);”




Use of IN operator
The IN operator compares the value of a column or expression with a list of values within a set.

e.g:-

“select name from employee where id in (67,4,3,7);”




Use of BETWEEN operator
The BETWEEN operator is used to test whether the value is within the range or not. It works
with numeric, string and data values.

e.g:-

“select name from employee where id between 1 and 3;




                                                                                            21
                                                          DBMS LAB FILE


Queries to perform the lpad, ltrim and replace commands



To do padding in left side of letters




For trimming from left side of letters




To replace a character with another character




                                                                  22
                                                                                   DBMS LAB FILE


Queries to perform the lpad, ltrim and replace commands


To do padding in left side of letters
Query:-

“select lpad(name,14,*) lname from employee;”

In this query lpad means to do padding from left side of each attribute of column name with ‘*’
and 14 means the limit up to which the padding has to be done.



For trimming from left side of letters
Query:-

“select ltrim(name,’r’)lname from employee;”

In this query the letter “s” will be trimmed from every attribute of column name of table
employee.



To replace a character with another character
Query:-

“select replace(course,’b’,’m’)from student;

This query will replace every occurrence of ‘b’ with ‘m’ in column course of table student.




                                                                                              23
                                                       DBMS LAB FILE


Queries to perform the union, intersection, minus commands




To apply set operator UNION




To apply set operator INTERSECT




                                                               24
                                                                                  DBMS LAB FILE


Queries to perform the union, intersection, minus commands


To apply set operator UNION
Query:-

“select * from employee union select * from employee1;”

This query will show all the contents of columns of table’s employee and employee1, but it will
show the repeated values only once.




To apply set operator INTERSECT
Query:-

“select * from employee intersect select * from employee1;”

This query will show the repeating contents of columns of tables employee and employee1.




                                                                                           25
                              DBMS LAB FILE


To apply set operator MINUS




                                      26
                                                                                  DBMS LAB FILE




To apply set operator MINUS
Query:-

“select * from employee minus select * from employee1;”

This query will show the contents of columns of tables employee which are present in table
employee1 but not in table employee.




                                                                                             27
                                                  DBMS LAB FILE


Query to add a new column in the existing table




To update an existing value in a table




To rename a table




                                                          28
                                                                                   DBMS LAB FILE


Query to add a new column in the existing table
Query:-

“alter table student add(age number(2));”

This query will add a column to an existing table student using alter keyword. This column will
contain null values.




To update an existing value in a table
Query:

“update student set age=20 where id =1;”

This query will update the age of student with id=1. This command can also insert values into the
new added column.




To rename a table
Query:-

“rename kultar to student;”

This query will update the name of table student to student_data.




                                                                                           29
                                                         DBMS LAB FILE


Queries to delete, truncate, commit, rollback commands



To delete a table




To restore a deleted table




To save the table on physical storage permanently




To delete a table permanently using truncate command




To delete a particular row




                                                                 30
                                                                         DBMS LAB FILE


Queries to delete, truncate, commit, rollback commands



To delete a table
Query:-

“delete student;”

This query will delete the table student.



To restore a deleted table
Query:-

“rollback;”

This query will restore the deleted table using delete command.



To save the table on physical storage permanently
Query:-

“commit;”

This query will save the data permanently on physical storage (H.D.D).



To delete a table permanently using truncate command
Query:-

“truncate table employee;”

This query will delete the table permanently from database.



To delete a particular row
Query:-“delete from student where id=3;”


                                                                                 31
                                             DBMS LAB FILE


To apply primary key constraint on a table




To apply not null constraint on a table




To apply unique constraint on a table




                                                     32
                                                                                     DBMS LAB FILE


To apply primary key constraint on a table


Query:-

“create table student(name varchar2(10),rollno number(10) primary key);


This query will create a table “student” with primary key “rollno”.
If we insert the same value of rollno for two different students then error message will display.




To apply not null constraint on a table


Query:-

“create table ritu(name varchar2(10) not null,rollno number(10));


This query will create a table “ritu” in which null values cannot be inserted into “name” column
as NOT NULL constraint is applies on “name” column.




To apply unique constraint on a table


Query:-

“create table ria(id number(10) unique,name varchar2(10));


This query will create a table “ria” in which the column “id” will have unique constraint applied
on it i.e. repeated values cannot be inserted into column “id” because of unique constraint.




                                                                                              33
                                             DBMS LAB FILE


To apply foreign key constraint on a table




                                                     34
                                                                              DBMS LAB FILE


To apply foreign key constraint on a table


Query:-

“create table mannu(name varchar2(10),rollno number(10),city varchar2(10),foreign key(name)
references sarab(name));”


This query will create a table “mannu” in which the column “name” will refer to column “name
(primary key)” of table “sarab”.




                                                                                       35
                                        DBMS LAB FILE


To apply check constraint on a column




                                                36
                                                                             DBMS LAB FILE


To apply check constraint on a column


Query:-

“create table mannu(name varchar2(10) check(name=upper(name)),rollno number(10));”


This query will create a table “mannu” in which the names in the column “name” can only be
inserted in uppercase because of check constraint.




                                                                                     37

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:31
posted:3/27/2013
language:Unknown
pages:37