SQL> SELECT ONUM,AMT,ODATE FROM ORDERS;
ONUM AMT ODATE
3001 18.69 10-MAR-90
3003 767.19 10-MAR-90
3002 1900.1 10-MAR-90
3005 5160.45 10-MAR-90
3006 1090.16 10-MAR-90
3009 1713.25 10-APR-90
3007 75.75 10-APR-90
3008 4723 10-MAR-90
3010 1309.95 10-JUN-90
3011 9891.88 10-JUN-90
10 rows selected.
Saturday, October 10, 2009
Thursday, June 4, 2009
Terminology Used in a Relational Database
A relational database can contain one or many tables. A table is the basic storage structure of an RDBMS. A table holds all the data necessary about something in the real world, such as employees, invoices, or customers.
The slide shows the contents of the EMPLOYEES table or relation. The numbers indicate the following:
1. A single row or table representing all data required for a particular employee. Each row in a table should be identified by a primary key, which allows no duplicate rows. The order of rows is insignificant; specify the row order when the data is retrieved.
2. A column or attribute containing the employee number. The employee number identifies a unique employee in the EMPLOYEES table. In this example, the employee number column is designated as the primary key. A primary key must contain a value, and the value must be unique.
3. A column that is not a key value. A column represents one kind of data in a table; in the example, the salary of all the employees. Column order is insignificant when storing data; specify the column order when the data is retrieved.
4. A column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other. A foreign key refers to a primary key or a unique key in the same table or in another table. In the example, DEPARTMENT_ID uniquely identifies a department in the DEPARTMENTS table.
5. A field may have no value in it. This is called a null value. In the EMPLOYEES table, only employees who have a role of sales representative have a value in the COMMISSION_PCT (commission) field.
6. A field can be found at the intersection of a row and a column. There can be only one value in it.
Properties of a Relational Database
In a relational database, you do not specify the access route to the tables, and you do not need to know how the data is arranged physically.
To access the database, you execute a structured query language (SQL) statement, which is the American National Standards Institute (ANSI) standard language for operating relational databases. The language contains a large set of operators for partitioning and combining relations. The database can be modified by using the SQL statements.
The slide shows the contents of the EMPLOYEES table or relation. The numbers indicate the following:
1. A single row or table representing all data required for a particular employee. Each row in a table should be identified by a primary key, which allows no duplicate rows. The order of rows is insignificant; specify the row order when the data is retrieved.
2. A column or attribute containing the employee number. The employee number identifies a unique employee in the EMPLOYEES table. In this example, the employee number column is designated as the primary key. A primary key must contain a value, and the value must be unique.
3. A column that is not a key value. A column represents one kind of data in a table; in the example, the salary of all the employees. Column order is insignificant when storing data; specify the column order when the data is retrieved.
4. A column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other. A foreign key refers to a primary key or a unique key in the same table or in another table. In the example, DEPARTMENT_ID uniquely identifies a department in the DEPARTMENTS table.
5. A field may have no value in it. This is called a null value. In the EMPLOYEES table, only employees who have a role of sales representative have a value in the COMMISSION_PCT (commission) field.
6. A field can be found at the intersection of a row and a column. There can be only one value in it.
Properties of a Relational Database
In a relational database, you do not specify the access route to the tables, and you do not need to know how the data is arranged physically.
To access the database, you execute a structured query language (SQL) statement, which is the American National Standards Institute (ANSI) standard language for operating relational databases. The language contains a large set of operators for partitioning and combining relations. The database can be modified by using the SQL statements.
Relating Multiple Tables
Each table contains data that describes exactly one entity. For example, the EMPLOYEES table contains information about employees. Categories of data are listed across the top of each table, and individual cases are listed below. Using a table format, you can readily visualize, understand, and use information. Because data about different entities is stored in different tables, you may need to combine two or more tables to answer a particular question. For example, you may want to know the location of the department where an employee works. In this scenario, you need information from the EMPLOYEES table (which contains data about employees) and the DEPARTMENTS table (which contains information about departments). With an RDBMS you can relate the data in one table to the data in another by using the foreign keys. A foreign key is a column or a set of columns that refer to a primary key in the same table or another table.
You can use the ability to relate data in one table to data in another to organize information in separate, manageable units. Employee data can be kept logically distinct from department data by storing it in a separate table.
Guidelines for Primary Keys and Foreign Keys
• You cannot use duplicate values in a primary key.
• Primary keys generally cannot be changed.
• Foreign keys are based on data values and are purely logical, not physical, pointers.
• A foreign key value must match an existing primary key value or unique key value, or else be null.
• A foreign key must reference either a primary key or unique key column.
ER Modeling
Entities
To represent an entity in a model, use the following conventions:
• Soft box with any dimensions
• Singular, unique entity name
• Entity name in uppercase
• Optional synonym names in uppercase within parentheses: ( )
Attributes
To represent an attribute in a model, use the following conventions: • Use singular names in lowercase.
• Tag mandatory attributes, or values that must be known, with an asterisk: *.
• Tag optional attributes, or values that may be known, with the letter o.
Relationships
Symbol Description
Dashed line Optional element indicating “may be”
Solid line Mandatory element indicating “must be”
Crow’s foot Degree element indicating “one or more”
Single line Degree element indicating “one and only one”
Relationships
Each direction of the relationship contains:
• A label, for example, taught by or assigned to
• An optionality, either must be or may be
• A degree, either one and only one or one or more
Entities
To represent an entity in a model, use the following conventions:
• Soft box with any dimensions
• Singular, unique entity name
• Entity name in uppercase
• Optional synonym names in uppercase within parentheses: ( )
Attributes
To represent an attribute in a model, use the following conventions: • Use singular names in lowercase.
• Tag mandatory attributes, or values that must be known, with an asterisk: *.
• Tag optional attributes, or values that may be known, with the letter o.
Relationships
Symbol Description
Dashed line Optional element indicating “may be”
Solid line Mandatory element indicating “must be”
Crow’s foot Degree element indicating “one or more”
Single line Degree element indicating “one and only one”
Relationships
Each direction of the relationship contains:
• A label, for example, taught by or assigned to
• An optionality, either must be or may be
• A degree, either one and only one or one or more
ER Modeling
In an effective system, data is divided into discrete categories or entities. An entity relationship (ER) model is an illustration of various entities in a business and the relationships between them. An ER model is derived from business specifications or narratives and built during the analysis phase of the system development life cycle. ER models separate the information required by a business from the activities performed within a business. Although businesses can change their activities, the type of information tends to remain constant. Therefore, the data structures also tend to be constant.
Benefits of ER Modeling
• Documents information for the organization in a clear, precise format
• Provides a clear picture of the scope of the information requirement
• Provides an easily understood pictorial map for the database design
• Offers an effective framework for integrating multiple applications
Key Components
• Entity: A thing of significance about which information needs to be known. Examples are departments, employees, and orders.
• Attribute: Something that describes or qualifies an entity. For example, for the employee entity, the attributes would be the employee number, name, job title, hire date, department number, and so on. Each of the attributes is either required or optional. This state is called optionality.
• Relationship: A named association between entities showing optionality and degree. Examples are employees and departments, and orders and items.
Relational Model
The principles of the relational model were first outlined by Dr. E. F. Codd in a June 1970 paper called “A Relational Model of Data for Large Shared Data Banks.” In this paper, Dr. Codd proposed the relational model for database systems.
The more popular models used at that time were hierarchical and network, or even simple flat file data structures. Relational database management systems (RDBMS) soon became very popular, especially for their ease of use and flexibility in structure. In addition, a number of innovative vendors, such as Oracle, supplemented the RDBMS with a suite of powerful application development and user products, providing a total solution.
Components of the Relational Model
• Collections of objects or relations that store the data
• A set of operators that can act on the relations to produce other relations
• Data integrity for accuracy and consistency
Definition of a Relational Database
A relational database uses relations or two-dimensional tables to store information. For example, you might want to store information about all the employees in your company. In a relational database, you create several tables to store different pieces of information about your employees, such as an employee table, a department table, and a salary table.
Data Models
Models are a cornerstone of design. Engineers build a model of a car to work out any details before putting it into production. In the same manner, system designers develop models to explore ideas and improve the understanding of the database design.
The more popular models used at that time were hierarchical and network, or even simple flat file data structures. Relational database management systems (RDBMS) soon became very popular, especially for their ease of use and flexibility in structure. In addition, a number of innovative vendors, such as Oracle, supplemented the RDBMS with a suite of powerful application development and user products, providing a total solution.
Components of the Relational Model
• Collections of objects or relations that store the data
• A set of operators that can act on the relations to produce other relations
• Data integrity for accuracy and consistency
Definition of a Relational Database
A relational database uses relations or two-dimensional tables to store information. For example, you might want to store information about all the employees in your company. In a relational database, you create several tables to store different pieces of information about your employees, such as an employee table, a department table, and a salary table.
Data Models
Models are a cornerstone of design. Engineers build a model of a car to work out any details before putting it into production. In the same manner, system designers develop models to explore ideas and improve the understanding of the database design.
Saturday, March 21, 2009
Program to calculate Factorial of number.
declare
v_num number :=#
fact number :=1;
begin
for i in 1..v_num
loop
fact:=fact*i;
end loop;
dbms_output.put_line('fact of '||v_num||' is '||fact);
end;
/
v_num number :=#
fact number :=1;
begin
for i in 1..v_num
loop
fact:=fact*i;
end loop;
dbms_output.put_line('fact of '||v_num||' is '||fact);
end;
/
Program to calculate Even or odd Number
declare
n number;
r number :=1;
begin
n:=&number;
for r in 1..n
loop
if mod(r,2)=0 then
dbms_output.put_line('Even No :' || r);
end if;
end loop;
end;
n number;
r number :=1;
begin
n:=&number;
for r in 1..n
loop
if mod(r,2)=0 then
dbms_output.put_line('Even No :' || r);
end if;
end loop;
end;
Monday, February 23, 2009
Exceptional handler In PL-SQL
1 begin
2 delete from dept_college where deptid=10;
3 exception
4 when others then
5 dbms_output.put_line('Connot delete ---foreign key violation');
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> select * from dept_college
2 /
DEPTID DEPTNAME LOCATION
--------- --------------- ----------
20 Anil Hyderabad
30 Accounting Banglore
40 Marketing Nagpur
50 Sunil Chennai
2 delete from dept_college where deptid=10;
3 exception
4 when others then
5 dbms_output.put_line('Connot delete ---foreign key violation');
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> select * from dept_college
2 /
DEPTID DEPTNAME LOCATION
--------- --------------- ----------
20 Anil Hyderabad
30 Accounting Banglore
40 Marketing Nagpur
50 Sunil Chennai
(4) Program To find salary of an employee from using employee nos.
SQL> Declare
2 ex1 exception;
3 en number;
4 ena varchar2(30);
5 esal number(7,2);
6 Begin
7 en:=&en;
8 select ename,sal into ena,esal from emp where empno=en;
9 if esal<1500>'||esal);
13 Exception
14 when ex1 then
15 update emp
16 set sal=sal+(sal*0.10)where empno=en;
17 dbms_output.put_line('Salary is updated');
18 End;
19 /
Output :
Enter value for en: 7900
old 7: en:=&en;
new 7: en:=7900;
PL/SQL procedure successfully completed.
2 ex1 exception;
3 en number;
4 ena varchar2(30);
5 esal number(7,2);
6 Begin
7 en:=&en;
8 select ename,sal into ena,esal from emp where empno=en;
9 if esal<1500>'||esal);
13 Exception
14 when ex1 then
15 update emp
16 set sal=sal+(sal*0.10)where empno=en;
17 dbms_output.put_line('Salary is updated');
18 End;
19 /
Output :
Enter value for en: 7900
old 7: en:=&en;
new 7: en:=7900;
PL/SQL procedure successfully completed.
Program to print sum of first n natural nos In SQL.
SQL> Declare
2 i number:=0;
3 n number;
4 sum1 number:=0;
5 Begin
6 n:=&n;
7 while i
8 loop
9 sum1:=sum1+i;
10 dbms_output.put_line(i);
11 i:=i+1;
12 end loop;
13 dbms_output.put_line('The sum is:'||sum1);
14 End;
15 /
Enter value for n: 10
old 6: n:=&n;
new 6: n:=10;
PL/SQL procedure successfully completed.
Sunday, February 22, 2009
Create a trigger for displaying a message “Record is inserting “before insertion of each row in the department table.
SQL> create or replace trigger addtri before insert on dept for each row
2 Begin
3 dbms_output.put_line('Record is inserting');
4 end;
5 /
Trigger created.
SQL> insert into dept values(50,'Finance','America');
1 row created.
2 Begin
3 dbms_output.put_line('Record is inserting');
4 end;
5 /
Trigger created.
SQL> insert into dept values(50,'Finance','America');
1 row created.
Write a Program package to input empno and return its name salary and designation by using out parameter. Write a function to return area of triangle.
SQL> create or replace package empch10 is procedure printef1(eno in number,n out varchar2,s out num
ber,d out varchar2);
2 function area(b number, h number)
3 return number;
4 end empch10;
5 /
Package created.
SQL> create or replace package body empch10 as procedure printef1(eno in number,n out varchar2,s out
number,d out varchar2)is
2 ena varchar2(10);
3 csal varchar(7,2);
4 desg varchar2(9);
5 begin
6 select ename,sal,job into ena,esal,desg from emp where empno:=eno;
7 n:-=ena;
8 s:=csal;
9 d:=desg;
10 end printef1;
11 function area(b number,h number)
12 return number is
13 a number;
14 begin
15 a:=1/2*b*h;
16 return a;
17 end area;
18 end empch10;
19 input truncated to 18 characters
20 /
Package Body created.
SQL> declare
2 a number;
3 e varchar2(10);
4 b number;
5 c varchar2(9);
6 Begin
7 a:=&a;
8 empch10.printcf(a,c,b,c);
9 dbms_output.put_line(e||'--->'||b||'--->'||c);
10 end;
11 /
SQL> declare
2 a number;
3 b number;
4 c number;
5 begin
6 a:=&a;
7 b:=&b;
8 c:=empch10.area(a,b);
9 dbms_output.put_line('the result is:'||c);
10 end;
Nobody should be allowed to update emp table on Sunday write a trigger for this.
SQL> create or replace trigger emptri before update on emp for each row
2 begin
3 if to_char(sysdate,'D')=1 then
4 raise_applicaton_error(-20001,'Not allowed on sunday');
5 end if;
6 end;
7 /
2 begin
3 if to_char(sysdate,'D')=1 then
4 raise_applicaton_error(-20001,'Not allowed on sunday');
5 end if;
6 end;
7 /
Write a function to return area of triangle.
SQL> create or replace function area (b number,h number)
2 Return number is
3 a number;
4 begin
5 a:=1/2*b*h;
6 return a;
7 end;
8 /
Function created.
OUTPUT
SQL> select area(5,12) from dual;
AREA(5,12)
----------
30
Write a function to return minimum salary from given department number .
SQL>
1 create or replace function minsalary(id number);
2 2 return number is;
3 3 sa number(7,2);
4 4 begin
5 5 select min(sal)into sa from emp where deptid:=di;
6 6 return sa;
7* 7* end;
SQL> /
Output :
SQL> select min(salary) from emp_college
2 /
MIN(SALARY)
-----------
4000
1 create or replace function minsalary(id number);
2 2 return number is;
3 3 sa number(7,2);
4 4 begin
5 5 select min(sal)into sa from emp where deptid:=di;
6 6 return sa;
7* 7* end;
SQL> /
Output :
SQL> select min(salary) from emp_college
2 /
MIN(SALARY)
-----------
4000
Write a program to delete a record from a department table used in parameter to accept department number errors like deleting a foreign key value
1 begin
2 delete from dept_college where deptid=10;
3 exception
4 when others then
5 dbms_output.put_line('Connot delete ---foreign key violation');
6* end;
SQL> /
Output
PL/SQL procedure successfully completed.
SQL> select * from dept_college
2 /
DEPTID DEPTNAME LOCATION
--------- --------------- ----------
20 Anil
30 Accounting Banglore
40 Marketing
50 Sunil Chennai
Subscribe to:
Posts (Atom)
What is RDBMS?
In recent years, database management systems (DBMS) have established themselves as the primary means of data storage for information systems ranging from large commercial transaction processing applications to PC-based desktop applications. At the heart of most of today's information systems is a relational database management system (RDBMS). RDBMSs have been the workhorse for data management operations for over a decade and continue to evolve and mature, providing sophisticated storage, retrieval, and distribution functions to enterprise-wide data processing and information management systems. Compared to the file systems, relational database management systems provide organizations with the capability to easily integrate and leverage the massive amounts of operational data into meaningful information systems. The evolution of high-powered database engines such as Oracle7 has fostered the development of advanced "enabling" technologies including client/server, data warehousing, and online analytical processing, all of which comprise the core of today's state-of-the-art information management systems.
Examine the components of the term relational database management system. First, a database is an integrated collection of related data. Given a specific data item, the structure of a database facilitates the access to data related to it, such as a student and all of his registered courses or an employee and his dependents. Next, a relational database is a type of database based in the relational model; non-relational databases commonly use a hierarchical, network, or object-oriented model as their basis. Finally, a relational database management system is the software that manages a relational database. These systems come in several varieties, ranging from single-user desktop systems to full-featured, global, enterprise-wide systems, such as Oracle7.
This blog discusses the basic elements of a relational database management system, the relational database, and the software systems that manage it. Also included is a discussion of nonprocedural data access. If you are a new user to relational database technology, you'll have to change your thinking somewhat when it comes to referencing data nonprocedurally.
The Relational Database Model
Most of the database management systems used by commercial applications today are based on one of three basic models: the hierarchical model, the network model, or the relational model. The following sections describe the various differences and similarities of the models.
Hierarchical and Network Models
The first commercially available database management systems were of the CODASYL type, and many of them are still in use with mainframe-based, COBOL applications. Both network and hierarchical databases are quite complex in that they rely on the use of permanent internal pointers to relate records to each other. For example, in an accounts payable application, a vendor record might contain a physical pointer in its record structure that points to purchase order records. Each purchase order record in turn contains pointers to purchase order line item records.
The process of inserting, updating, and deleting records using these types of databases requires synchronization of the pointers, a task that must be performed by the application. As you might imagine, this pointer maintenance requires a significant amount of application code (usually written in COBOL) that at times can be quite cumbersome.
Elements of the Relational Model
Relational databases rely on the actual attribute values as opposed to internal pointers to link records. Instead of using an internal pointer from the vendor record to purchase order records, you would link the purchase order record to the vendor record using a common attribute from each record, such as the vendor identification number.
Although the concepts of academic theory underlying the relational model are somewhat complex, you should be familiar with are some basic concepts and terminology. Essentially, there are three basic components of the relational model: relational data structures, constraints that govern the organization of the data structures, and operations that are performed on the data structures.
Relational Data Structures
The relational model supports a single, "logical" structure called a relation, a two-dimensional data structure commonly called a table in the "physical" database. Attributes represent the atomic data elements that are related by the relation. For example, the Customer relation might contain such attributes about a customer as the customer number, customer name, region, credit status, and so on.
Key Values and Referential Integrity
Attributes are grouped with other attributes based on their dependency on a primary key value. A primary key is an attribute or group of attributes that uniquely identifies a row in a table. A table has only one primary key, and as a rule, every table has one. Because primary key values are used as identifiers, they cannot be null. Using the conventional notation for relations, an attribute is underlined to indicate that it is the primary key of the relation. If a primary key consists of several attributes, each attribute is underlined.
You can have additional attributes in a relation with values that you define as unique to the relation. Unlike primary keys, unique keys can contain null values. In practice, unique keys are used to prevent duplication in the table rather than identify rows. Consider a relation that contains the attribute, United States Social Security Number (SSN). In some rows, this attribute may be null in since not every person has a SSN; however for a row that contains a non-null value for the SSN attribute, the value must be unique to the relation.
Linking one relation to another typically involves an attribute that is common to both relations. The common attributes are usually a primary key from one table and a foreign key from the other. Referential integrity rules dictate that foreign key values in one relation reference the primary key values in another relation. Foreign keys might also reference the primary key of the same relation. Figure illustrates two foreign key relationships.
Oracle and Client/Server
Oracle Corporation's reputation as a database company is firmly established in its full-featured, high-performance RDBMS server. With the database as the cornerstone of its product line, Oracle has evolved into more than just a database company, complementing its RDBMS server with a rich offering of well-integrated products that are designed specifically for distributed processing and client/server applications. As Oracle's database server has evolved to support large-scale enterprise systems for transaction processing and decision support, so too have its other products, to the extent that Oracle can provide a complete solution for client/server application development and deployment. This chapter presents an overview of client/server database systems and the Oracle product architectures that support their implementation.
An Overview of Client/Server Computing
The premise of client/server computing is to distribute the execution of a task among multiple processors in a network. Each processor is dedicated to a specific, focused set of subtasks that it performs best, and the end result is increased overall efficiency and effectiveness of the system as a whole. Splitting the execution of tasks between processors is done through a protocol of service requests; one processor, the client, requests a service from another processor, the server. The most prevalent implementation of client/server processing involves separating the user interface portion of an application from the data access portion.
On the client, or front end, of the typical client/server configuration is a user workstation operating with a Graphical User Interface (GUI) platform, usually Microsoft Windows, Macintosh, or Motif. At the back end of the configuration is a database server, often managed by a UNIX, Netware, Windows NT, or VMS operating system.
Client/server architecture also takes the form of a server-to-server configuration. In this arrangement, one server plays the role of a client, requesting database services from another server. Multiple database servers can look like a single logical database, providing transparent access to data that is spread around the network.
Designing an efficient client/server application is somewhat of a balancing act, the goal of which is to evenly distribute execution of tasks among processors while making optimal use of available resources. Given the increased complexity and processing power required to manage a graphical user interface (GUI) and the increased demands for throughput on database servers and networks, achieving the proper distribution of tasks is challenging. Client/server systems are inherently more difficult to develop and manage than traditional host-based application systems because of the following challenges:
The components of a client/server system are distributed across more varied types of processors. There are many more software components that manage client, network, and server functions, as well as an array of infrastructure layers, all of which must be in place and configured to be compatible with each other.
The complexity of GUI applications far outweighs that of their character-based predecessors. GUIs are capable of presenting much more information to the user and providing many additional navigation paths to elements of the interface.
Troubleshooting performance problems and errors is more difficult because of the increased number of components and layers in the system.
Databases in a Client/Server Architecture
Client/server technologies have changed the look and architecture of application systems in two ways. Not only has the supporting hardware architecture undergone substantial changes, but there have also been significant changes in the approach to designing the application logic of the system.
Prior to the advent of client/server technology, most Oracle applications ran on a single node. Typically, a character-based SQL*Forms application would access a database instance on the same machine with the application and the RDBMS competing for the same CPU and memory resources. Not only was the system responsible for supporting all the database processing, but it was also responsible for executing the application logic. In addition, the system was burdened with all the I/O processing for each terminal on the system; each keystroke and display attribute was controlled by the same processor that processed database requests and application logic.
Client/server systems change this architecture considerably by splitting all of the interface management and much of the application processing from the host system processor and distributing it to the client processor.
Combined with the advances in hardware infrastructure, the increased capabilities of RDBMS servers have also contributed to changes in the application architecture. Prior to the release of Oracle7, Oracle's RDBMS was less sophisticated in its capability to support the processing logic necessary to maintain the integrity of data in the database. For example, primary and foreign key checking and enforcement was performed by the application. As a result, the database was highly reliant on application code for enforcement of business rules and integrity, making application code bulkier and more complex. Figure 2.1 illustrates the differences between traditional host-based applications and client/server applications. Client/server database applications can take advantage of the Oracle7 server features for implementation of some of the application logic.
