Tables

You will see the Relational Database as a collection of one or more tables.

A table:
> Is a two dimensional grid of rows and columns.
> Holds data as a value at each row and column intersection.
> Has at least one column and zero or more rows. (The table is empty if it has no rows.)
> Has a unique name within the Relational Database.

An RDBMS uses two types of tables:
> User tables contain user-defined and maintained data.
> System tables contain data about the tables defined by the users and is called the system catalog. This includes table definitions, security, and performance statistics.

Columns

> Each column reflects a specific property or attribute of the table’s entity.
> Each column has a unique name within its table to signify this property or attribute. However, this same name may also be used in other tables.
> Each column entry contains a single data value.
> The order of the columns from left to right is unimportant.
> Each column has a constraint on its values called the domain. This domain limits the column values to characters and numeric values (names, dates, times, etc.) with other specifications, such as length and null values. A null value means that there is no value, which is not the same as a blank value.

Rows

> Each row reflects a fact about a table’s entity. If this is an employee table, then each row is for a different employee.
> Each column in the row contains a value or a null.
> No two rows of a table can be identical, because each row in a table is uniquely identified by its primary key
> The order of the rows, top to bottom, is unimportant.

E.F. Codd conceived the relational database in 1970. He worked at IBM and published “Codd’s 12 Rules.” A small start-up company released the first RDBMS and called it ORACLE. Then IBM published their version as SEQUEL-XRM and it later became DB2. A Relational DataBase Management System (RDBMS) is a database divided into logical units called tables, where the data in the two-dimensional tables are related to one another within the database. These tables are largely independent, which gives relational databases their flexibility. You can add, change, or delete data in one table without affecting other tables, provided that the changed table is not a parent of any other table. (Parent-child tables are discussed later.)

SQL operators are used to select rows or columns of data from the table, producing a new, temporary result table. This language differs considerably from previous DBMS languages, because it returns one or many rows (or records) with each query. We will show you later how to handle the individual rows. Next we will see how easy SQL is to use!

NETWORK databases were created first. They have very little redundancy but have structural complexity.

HIERARCHICAL databases have a parent/child hierarchy that is faster than network databases, but it suffers from structural inflexibility, redundancy problems, and broken pointers. As it is an older DBMS, it is called a legacy database.

RELATIONAL databases are almost exclusively the database of choice today. Many businesses have converted to Relational DataBase Management Systems (RDBMS) for performance and ease of maintenance.

Today, businesses use DBMS programs run on mainframes, mini-computers, and personal computers. A DBMS system that can go from a large computer to a small computer is called scalable.Management System (DBMS). A DBMS contains a definition of how the data is defined, along with the actual data. A data structure is built to hold variable data. These databases work with client/server and web technologies. The DBMS is a set of programs used as a tool to define, administer, and process databases and their associated applications. It is also the job of the DBMS to handle multiple users who are accessing or updating the data, check security, ensure data integrity, and perform database backup and recovery operations.

DBMS programs run on mainframes, mini-computers, and personal computers. A DBMS system that can go from a large computer to a small computer is called scalable.

Flat Files

Every business has data that must be organized in a meaningful manner for retrieval and maintenance. These data files started years ago on mainframe computers as flat files.

A flat file was the simpelest structure possible and was a collection of one data record after another in a specified format. The person who created the file chose the length of each field and its position in the record. Anyone using these flat files had to know the length of each field and the order of the fields in the record. The files were read and processed from beginning to end like reading a complete book.

Indexed Files

Then indexed files were invented. The index was maintained in sequence and each index pointed to a record of related data. It was like looking up a person’s address and telephone number in a telephone book.

SQL 001 What is a Relational Database, DBMS, or RDBMS?

A database is a collection of data with common record formats making a file with many different records. Other files with different record formats make up a system. For example, you have a company with employees and you must run a payroll system to pay these employees. The employee file contains the Employee ID, Name, Address, Rate/Hour, etc. If you append the Pay Date, Hours Worked, Gross Pay, etc., then you can produce payroll checks and pay your employees.

For the next pay period, you would be able to duplicate each record on your employee file.

This method will work fine for a small company, but you can see that there is a duplication of much of the data, such as Employee ID, Name, Address, Rate/Hour, etc. This is called Data Redundancy.

If you have an employee file and a payroll file, then you must read two files to produce payroll checks, but most of the data redundancy has been eliminated — requiring much less data-storage space. Because there is less data to read, the processing may go faster.

Database systems manage the data by adding, updating, and deleting data, as well as sorting, doing calculations, and generating reports.

Have you seen our online lab demo? Try it today at http://ComputerBasedTrainingInc.com.

Syntax Element      In PL/I
prefix              EXEC SQL
terminator          ;
target              label constant or
                    PL/I label variable

Variable Definition Syntax

PL/I variable definition =
    { DCL | DECLARE }
    { host identifier | (host identifier.,..) }
    type spec [ character representation...] ;

type spec =
    { { CHAR | CHARACTER } [VARYING] (length)
    [CHARACTER SET [IS] character set name] }
  | { BIT [VARYING] (length) }
  | { type fixed decimal ( precision [ , scale ] ) }
  | { type fixed binary [ ( precision ) ] }
  | { type fixed binary ( precision ) }

type fixed decimal =
    { DEC | DECIMAL } FIXED | FIXED { DEC | DECIMAL }

type fixed binary =
    { BIN | BINARY } FIXED | FIXED { BIN | BINARY }

type float binary =
    { BIN | BINARY } FLOAT | FLOAT { BIN | BINARY }

SQL Type                 PL/I Type
BIT(length)              BIT(length)
BIT VARYING(length)      BIT VARYING(length)
CHAR(length)             CHAR(length)
DECIMAL                  FIXED DECIMAL
  (precision, scale)       (precision, scale)
FLOAT(precision)         FLOAT BINARY(precision)
INTEGER                  FIXED BINARY
SMALLINT                 FIXED BINARY
VARCHAR(length)          CHAR VARCHAR(length)
SQLCODE                  FIXED BINARY (PP)
                         where PP = the
                         implementation defined
                         precision for SQLCODE.
SQLSTATE                 CHAR (5)
indicator                FIXED BINARY

Syntax Element      In PHP
prefix              $sql = "
terminator          ";
target              unsigned integer (must
                    correspond to a valid label)

Variable Definition Syntax

PHP variable definition =
host identifier... : type spec;

type spec =
    BIGINT | BINARY | BIT | CHAR | DATETIME | DECIMAL
  | FLOAT | INT | NUMERIC | REAL | SMALLDATETIME
  | SMALLINT | TIMESTAMP | TINYINT | VARCHAR

SQL Type            PHP Type
BITINT              String
BINARY              Stream
BIT                 Integer
CHAR                String
DATETIME            Datetime
DECIMAL             String
FLOAT               Float
INT                 Integer
NUMERIC             String
REAL                Float
SMALLDATETIME       Datetime
SMALLINT            Integer
TIMESTAMP           String
TINYINT             Integer
VARCHAR             Stream
SQLCODE             INTEGER
SQLSTATE            CHAR(5)
indicator           INTEGER

Syntax Element      In Pascal
prefix              EXEC SQL
terminator          ;
target              unsigned integer (must
                    correspond to a valid label)

Variable Definition Syntax

Pascal variable definition =
    host identifier... : type spec;

In Pascal, brackets ([ and ]) are part of the syntax of
variable declarations. They are also one of the
conventions used to create a syntax diagram. In the
following diagram, the expressions left bracket and right
bracket mean that brackets are literally used in the
Pascal statement. Brackets shown as ([ and ]) enclose an
optional portion of the statement.

 type spec =
    { PACKED ARRAY left bracket 1..length right bracket
    OF CHAR [ CHARACTER SET [IS] character set name] }
    | { PACKED ARRAY left bracket 1..length right bracket OF BIT }
    | BIT
    | INTEGER
    | REAL
    | { CHAR [CHARACTER SET [IS] character set name] }

SQL Type            Pascal Type
BIT(n)              BIT(n)
BIT VARYING(n)      BIT(n) VAR
CHAR(n)             CHAR
CHAR(length)        CHAR(n)
DECIMAL(p,s)        DEC(p,s)
DOUBLE PRECISION    BIN FLOAT(53)
INTEGER FIXED       BIN(31)
NUMERIC(p,s)        FIXED
REAL                BIN FLOAT(21)
SMALLINT            FIXED BIN(15)
VARCHAR(n)          CHAR(n) VAR
SQLCODE             INTEGER
SQLSTATE            CHAR(5)
indicator           INTEGER

Syntax Element      In MUMPS
prefix              &SQL(
terminator          )
target              statement label

Variable Definition Syntax

MUMPS variable definition =
    { numeric variable | character variable };

MUMPS character variable =
    VARCHAR { host identifier (length) }...

MUMPS numeric variable =
    { DEC [ ( precision [ , scale ] ) ] }
  | INT
  | REAL }
    host identifier...

SQL Type            MUMPS Type
CHAR                VARCHAR
DECIMAL             DEC
INTEGER             INT
REAL                REAL
VARCHAR             VARCHAR
SQLCODE             INTEGER
SQLSTATE            VARCHAR (5)
indicator           INT