The EMPLOYEE table in SQL.

A single set of a group of fields is known as a record or row. For example, 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 EMPLOYEE table.


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

What are Dr. Codd's 12 Rules for a Relational Database Model.

The most popular data storage model is the relational database, which grew from theseminal paper "A Relational Model of Data for Large Shared Data Banks," written by Dr. E. F. Codd in 1970. SQL evolved to service the concepts of the relational databasemodel. Dr. Codd defined 13 rules, oddly enough referred to as Codd's 12 Rules, for therelational model:

1. A relational DBMS must be able to manage databases entirely through its relational capabilities.
2. Information rule-- All information in a relational database (including tableand column names) is represented explicitly as values in tables.
3. Guaranteed access--Every value in a relational database is guaranteed to beaccessible by using a combination of the table name, primary key value, and column name.
4. Systematic null value support--The DBMS provides systematic support for the treatment of null values (unknown or inapplicable data), distinct from default values, and independent of any domain.
5. Active, online relational catalog--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.
6. Comprehensive data sublanguage--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.>
7. View updating rule--All views that are theoretically updatable can be updated through the system.
8. Set-level insertion, update, and deletion--The DBMS supports not only set level retrievals but also set-level inserts, updates, and deletes.
9. Physical data independence--Application programs and ad hoc programs arelogically unaffected when physical access methods or storage structures arealtered.
10. Logical data independence--Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table structures.
11. Integrity independence--The database language must be capable of defining integrity rules. They must be stored in the online catalog, and they cannot be by passed.
12. Distribution independence--Application programs and ad hoc requests are logically unaffected when data is first distributed or when it is redistributed.
13. Nonsubversion--It must not be possible to bypass the integrity rules defined through the database language by using lower-level languages.
    

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.

Introduction to SQL, A Brief History.

The history of SQL begins in an IBM laboratory in San Jose, California, where SQL was developed in the late 1970's. The initials stand for Structured Query Language, and the language itself is often referred to as "sequel." It was originally developed for IBM's DB2 product (a relational database management system, or RDBMS, that can still be bought today for various platforms and environments). In fact, SQL makes an RDBMS possible. SQL is a nonprocedural language, in contrast to the procedural or third generation languages (3GLs) such as COBOL and C that had been created up to that time.
The characteristic that differentiates 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. Set oriented means that SQL processes sets of data in groups.
Two standards organizations, the American National Standards Institute (ANSI) andthe International Standards Organization (ISO), currently promote SQL standards toindustry. The ANSI-92 standard is the standard for the SQL used throughout this book.Although these standard-making bodies prepare standards for database system designersto follow, all database products differ from the ANSI standard to some degree. Inaddition, most systems provide some proprietary extensions to SQL that extend thelanguage into a true procedural language. We have used various RDBMSs to preparethe examples in this book to give you an idea of what to expect from the commondatabase systems. (We discuss procedural SQL--known as PL/SQL--on Day 18, "PL/SQL: AnIntroduction," and Transact-SQL on Day 19, "Transact-SQL: An Introduction.")
A little background on the evolution of databases and database theory will help you understand the workings of SQL. Database systems store information in every conceivable business environment. From large tracking databases such as airline reservation systems to a child's baseball card collection, database systems store and distribute 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.