An epigrammatic account of SQL
The time gone by
of SQL begins in an IBM laboratory in San
Jose , California , where
on earth SQL was urbanized in the late 1970s. The fundamental pose for
Structured Query Language and the language itself is time and again referred to
as "sequel." It was in the inauguration built-up for IBM's DB2 item
for consumption as a basic criterion of a relational database management
system, or RDBMS.. In fact, SQL creates an RDBMS achievable. SQL is a
nonprocedural language, in disparity to the procedural or third-generation
languages such as COBOL and C that had been created up to that time. The quality
that categorizes a DBMS from an RDBMS is that the RDBMS provides a set-oriented
database language. For most RDBMSs, this set-oriented database language is SQL.
Two standards association, the American National Standards Institute and the
International Standards Organization, currently prop up SQL standards to exchange.
The ANSI-92 standard is the customary for the SQL used throughout this article.
Although these standard-making bodies systematize standards for database system
designers to tag along, all database products differ from the ANSI standard to
some degree. In addition, most systems provide some proprietary extensions to
SQL that extend the language into a true procedural language. We have used
various RDBMSs to prepare the examples in this article to give you an idea of
what to expect from the common database systems.
It was an
inquiring feeling whether there is a modest background on the evolution of
databases and database conjecture would facilitate us value the workings of
SQL. Database systems stock up in sequence in every feasible business
environment. From outsized pathway databases such as airline proviso systems to
a child's baseball card collection, database systems store and hand out the
data that we depend on. Until the last few years, large database systems could
be run only on large mainframe computers. These machines have traditionally
been expensive to design, purchase, and maintain. However, today's generation
of powerful, inexpensive workstation computers enables programmers to design
software that maintains and distributes data quickly and inexpensively.
Model of Database
1.
The largest part of popular
data storage model is the relational database, which was bedded on a formative paper named "A Relational Model of Data for Large
Shared Data Banks," written by Dr. E. F. Codd in 1970. SQL steps forward to
service on the conception of the relational database introduced by Dr. Codd who had promulgated such new
exploration for creating and building object orient programming software to be
based on the 13 rules, referred to as
Codd's 12 Rules, for the relational model which are the basic milestone in
RDBMS concept.:
2.
The following rules
have been explored by Dr. Codd which are basically known as ‘Dr. Codd’s Database rules.
1. All information in a
relational database (including table and column names) is represented
explicitly as values in tables.
2. Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name.
3. The DBMS provides systematic support for the treatment of null values (unknown or inapplicable data), distinct from default values, and independent of any domain.
4. The description of the database and its contents is represented at the logical level as tables and can therefore be queried using the database language.
5. At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.
2. Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name.
3. The DBMS provides systematic support for the treatment of null values (unknown or inapplicable data), distinct from default values, and independent of any domain.
4. The description of the database and its contents is represented at the logical level as tables and can therefore be queried using the database language.
5. At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.
6. All views that are theoretically updatable can be updated
through the system.
7. The DBMS supports not only set-level retrievals but also set-level inserts, updates, and deletes.
8. Application programs and ad hoc programs are logically unaffected when physical access methods or storage structures are altered.
9. Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table structures.
10. The database language must be capable of defining integrity rules. They must be stored in the online catalog, and they cannot be bypassed.
11. Application programs and ad hoc requests are logically unaffected when data is first circulated or when it is reallocate.
12. It ought not to be potential to get around the integrity rules defined through the database language by using lower-level languages.
7. The DBMS supports not only set-level retrievals but also set-level inserts, updates, and deletes.
8. Application programs and ad hoc programs are logically unaffected when physical access methods or storage structures are altered.
9. Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table structures.
10. The database language must be capable of defining integrity rules. They must be stored in the online catalog, and they cannot be bypassed.
11. Application programs and ad hoc requests are logically unaffected when data is first circulated or when it is reallocate.
12. It ought not to be potential to get around the integrity rules defined through the database language by using lower-level languages.
A good number database has had a "parent/child"
relationship; that is, a parent node would contain file pointers to its
children. This method has several advantages and many
disadvantages. In its favor is the fact that the physical structure of data on
a disk becomes unimportant. The programmer simply stores pointers to the next
location, so data can be accessed in this manner. Also, data can be added and
deleted easily. However, different groups of information could not be easily
joined to form new information. The format of the data on the disk could not be
arbitrarily changed after the database was created. Doing so would require the
creation of a new database structure. Codd's idea for an RDBMS uses the
mathematical concepts of relational algebra to break down data into sets and
related common subsets. Because information can naturally be grouped into
distinct sets, Dr. Codd organized his database system around this concept.
Under the relational model, data is separated into sets that resemble a table
structure. This table structure consists of individual data elements called
columns or fields. A single set of a group of fields is known as a record or
row. For instance, to create a relational database consisting of employee data,
you might start with a table called EMPLOYEE that contains the following pieces of information: Name, Age, and Occupation. These three pieces of
data make up the fields in the Job holder table.
Job
holder table.
Name
|
Age
|
Occupation
|
Mehedi
|
12
|
Electrical engineer
|
Gias
|
44
|
Museum curator
|
Kaium
|
42
|
Assistant Chef
|
Abdul Karim
|
29
|
Student
|
Mohammad
|
32
|
Game programmer
|
Kamruzzaman
|
46
|
Singer
|
The six rows are the records in the EMPLOYEE table. To retrieve a
specific record from this table, for example, Dave Davidson, a user would
instruct the database management system to retrieve the records where the NAME field was equal to Dave
Davidson. If the DBMS had been instructed to retrieve all the fields in the
record, the employee's name, age, and occupation would be returned to the user.
SQL is the language that tells the database to retrieve this data. A sample SQL
statement that makes this query is
SELECT *
FROM EMPLOYEE
It is important to note that the exact syntax is not
important at this point. Due to the fact that the various data items can be
grouped according to obvious relationships, the relational database model gives
the database designer a great deal of flexibility to describe the relationships
between the data elements. Through the mathematical concepts of join and union,
relational databases can quickly retrieve pieces of data from different sets
(tables) and return them to the user or program as one "joined"
collection of data. The join feature enables the designer to store sets of
information in separate tables to reduce repetition.
Duty table.
Name
|
Duties
|
Skender
|
Cook
|
Lily Huq
|
Teacher
|
Shovon
|
Dancer
|
Idiorty
|
Superintendent
|
Designing
the Database Structure
The vital decision for a database designer, after the
hardware platform and the RDBMS have been preferred, is the structure of the
tables. Decisions made at this stage of the design can affect performance and
programming later during the development process. The process of separating
data into distinct, unique sets is called normalization.
Modern
Database Panorama
Computing technology has made a permanent change in the
ways businesses work around the world. Information that was at one time stored
in warehouses full of filing cabinets can now be accessed instantaneously at
the click of a mouse button. Orders placed by customers in foreign countries
can now be instantly processed on the floor of a manufacturing facility. Even
though 20 years ago much of this information had been transported onto
corporate mainframe databases, offices still operated in a batch-processing
environment. If a query needed to be performed, someone notified the management
information systems (MIS) department; the requested data was delivered as soon
as possible. In addition to the development of the relational database model,
two technologies led to the rapid growth of what are now called client/server
database systems. The first important technology was the personal computer.
Inexpensive, easy-to-use applications such as Lotus 1-2-3 and Word Perfect enabled employees (and
home computer users) to create documents and manage data quickly and
accurately. Users became accustomed to continually upgrading systems because
the rate of change was so rapid, even as the price of the more advanced systems
continued to fall.
The second important technology was the local area network
(LAN) and its integration into offices across the world. Although users were
accustomed to terminal connections to a corporate mainframe, now word
processing files could be stored locally within an office and accessed from any
computer attached to the network. After the Apple Macintosh introduced a
friendly graphical user interface, computers were not only inexpensive and
powerful but also easy to use. In addition, they could be accessed from remote
sites, and large amounts of data could be off-loaded to departmental data servers.
During this time of rapid change and advancement, a new
type of system appeared. Called client/server development because
processing is split between client computers and a database server, this new
breed of application was a radical change from mainframe-based application
programming. Among the many advantages of this type of architecture are
- Reduced maintenance
costs
- Reduced network load
(processing occurs on database server or client computer)
- Multiple operating
systems that can interoperate as long as they share a common network
protocol
- Improved data integrity
owing to centralized data location
In putting into
practice Client/Server figure, Bernard H. Boar classifies client/server
computing as follows:
Client/server
computing is a processing model in which a
single application is partitioned between multiple processors (front-end and
back-end) and the processors cooperate (transparent to the end user) to
complete the processing as a single unified task. Implementing Client/Server
Computing A client/server bond product ties the processors together to provide
a single system image (illusion). Shareable resources are positioned as
requestor clients that access authorized services. The architecture is
endlessly recursive; in turn, servers can become clients and request services
of other servers on the network, and so on and so on. This type of application
development requires an entirely new set of programming skills. User interface
programming is now written for graphical user interfaces, whether it be MS
Windows, IBM OS/2, Apple Macintosh, or the UNIX X-Window system. Using SQL and
a network connection, the application can interface to a database residing on a
remote server. The increased power of personal computer hardware enables
critical database information to be stored on a relatively inexpensive
standalone server. In addition, this server can be replaced later with little
or no change to the client applications.
Implementation
Level
Oracle Corporation released the first commercial RDBMS that
used SQL. Although the original versions were developed for VAX/VMS systems,
Oracle was one of the first vendors to release a DOS version of its RDBMS.
(Oracle is now available on more than 70 platforms.) In the mid-1980s Sybase
released its RDBMS, SQL Server. With client libraries for database access,
support for stored procedures (discussed on Day 14, "Dynamic Uses of
SQL"), and interoperability with various networks, SQL Server became a
successful product, particularly in client/server environments. One of the
strongest points for both of theseSQL Server powerful database systems is their
scalability across platforms. C language code (combined with SQL) written for
Oracle on a PC is virtually identical to its counterpart written for an Oracle
database running on a VAX system.
An
Overview of SQL
SQL is the de facto standard language used to manipulate
and retrieve data from these relational databases. SQL enables a programmer or
database administrator to do the following:
- Modify a database's
structure
- Change system security
settings
- Add user permissions
on databases or tables
- Query a database for
information
- Update the contents of a
database
According
to Dr. Codd, the term SQL can be confusing. The S, for Structured, and
the L, for Language, is undemanding enough, but the Q is a little
misleading. Q, of course, stands for "Query," which--if taken
literally--would restrict you to asking the database questions. But SQL does
much more than ask questions. With SQL you can also create tables, add data,
delete data, splice data together, trigger actions based on changes to the
database, and store your queries within your program or database.
Unfortunately, there is no good substitute for Query. Obviously,
Structured Add Modify Delete Join Store Trigger and Query Language (SAMDJSTQL)
is a bit cumbersome. In the interest of harmony, we will stay with SQL.
However, you now know that its function is bigger than its name. The most
commonly used statement in SQL is the SELECT statement (see Day 2, "Introduction to the Query: The
SELECT
Statement"), which retrieves data from the database and returns the data
to the user. The EMPLOYEE
table example illustrates a typical example of a SELECT statement situation. In addition to the SELECT statement, SQL provides
statements for creating new databases, tables, fields, and indexes, as well as
statements for inserting and deleting records. ANSI SQL also recommends a core
group of data manipulation functions. As you will find out, many database
systems also have tools for ensuring data integrity and enforcing security (see
Day 11, "Controlling Transactions") that enable programmers to stop
the execution of a group of commands if a certain condition occurs.
Popular
SQL Implementations
This section introduces some of the more popular
implementations of SQL, each of which has its own strengths and weaknesses.
Where some implementations of SQL have been developed for PC use and easy user
interactivity, others have been developed to accommodate very large databases
(VLDB). This sections introduces selected key features of some implementations.
In addition to serving as an SQL reference, this article also contains many
practical software development examples. SQL is useful only when it solves your
real-world problems, which occur inside your code.
Microsoft
Access
We use Microsoft Access, a PC-based DBMS, to illustrate
some of the examples in this text. Access is very easy to use. We can use GUI tools or manually enter your SQL
statements.
Oracle7
Language
In view of the above discussion, it is obvious that in
creating an object oriented programming software, the importance SQL is
inevitable. In integrating parent-child relationship, SQL is very important.
From SQL, Insert, Update and Delete Command are helpful. In a nut shell, we
exercise Oracle7, which represent the larger corporate database world, to
demonstrate command-line SQL and database management techniques. These
techniques are imperative for the reason that the days of the standalone
machine are drawing to an end, as are the days when expressive one database or
one in commission system was enough. In command-line,
simple stand+[cedilla]one SQL statements are entered into Oracle's SQL*Plus
tool. This tool then returns data to the screen for the user to see, or it
performs the apposite action on the database. Most examples are directed toward
the beginning programmer or first-time user of SQL. We begin with the simplest
of SQL statements and advance to the topics of transaction running and stored
procedure programming. The Oracle RDBMS is circulated with a full complement of
development tools. It comprises a C++ and Visual Basic language library (Oracle
Objects for OLE) that can link an application to a Personal Oracle database. It
also comes with graphical tools for database, user, and object administration,
as well as the SQL*Loader utility, which is used to introduce internally and
send abroad data to and from Oracle.
Comments
Post a Comment