Relational Model

The table (row and column) structure is easy for users to understand. The relational database model, as implemented by “Codd’s 12 rules,” has gained wide acceptance by vendors.

SQL Standards

SQL is an open standard and is not “owned” by any company. The standard is established by the American National Standards Institute (ANSI) under the joint leadership of the International Organization for Standardization (ISO) and the International Engineering Consortium (IEC).

Like English, Easy to Learn

Compared to some other programming languages, SQL is easy to learn. An SQL statement reads like a sentence.

Nonprocedural

If you have never done programming before, this point is of little consequence: It means that when you do a query, all of the data results are returned to you at once, instead of giving you one row (or record) at a time.

Interactive or Embedded

With interactive SQL, you issue SQL commands directly to the graphical- or command-line tool of DBMS, which displays the results as soon as they are produced. If you are writing an application, you can embed the SQL in the host programming language such as C++, COBOL, Java, Perl, PHP, or Python. You can use SQL in a PHP CGI script to query a MySQL database.

Multiple Views of Data

The person that created the database can give different views of the database to different users. For example, an employee from the payroll department might be restricted to only payroll data. Also, data from multiple tables can be combined into one table presentation.

Dynamic Data Definition

With SQL, the structure of the database can be changed dynamically while users are accessing the database. This means that SQL can create a database, control security, retrieve data, change data, and share data with many users simultaneously.

Client/Server Database

A central computer called the server is accessed from a local area network (LAN) or a wide area network (WAN). PCs, called clients, are used to access the server. These client/server networks are generally not accessible to the general public.

Web-Based Database

Customers are able to use PCs with internet browsers to access a web site available to the general public. They may need to log in (if required) to access a database. SQL is executed by the web application and returns data to the web server where it is returned to the internet browser for the customer’s use.

In a many-to-many relationship, each row in Table A can have zero, one, or many matching entries in Table B and Table B can have zero, one, or many matching entries in Table A. There are two ways to create a many-to-many relationship:

> Two tables can be joined together. See JOIN.
> You can create a third table called a junction table whose primary key is a composite of the keys from Table A and Table B. This creates a unique key for the junction table and changes these keys to a one-to-many relationship.

In a one-to-many relationship, each row in Table A can have zero, one, or many matching entries in Table B. However, Table B has only one matching entry in Table A. A one-to-many relationship exists when the primary key of Table A matches a foreign key in Table B.

Relational DataBase Management Systems (RDBMS) perform more reliably than other systems because they function with an association established between common column values of two or more tables. There are three kinds of relationships:

> One-to-One
> One-to-Many
> Many-to-Many

In a one-to-one relationship, each row in Table A can have no more than one matching entry in Table B. If you are going to have a one-to-one relationship, then you probably need to include the columns from both tables in just one table. A possible exception to combining multiple one-to-one tables into one table would be if the second table’s data required higher security that would be applied only to the second table.

 

Foreign keys are used to associate data between tables. For instance, a foreign key of REP in the CUSTOMER_TBL points to the primary key EMPL_ID in the EMPLOYEE_TBL.

> A foreign key is one or more columns in a table whose values reference values in another table.
> A foreign key in the child table establishes a relationship to the primary key in the parent table.
> The matching foreign key and primary key can have different names.
> If this relationship is enforced, then this is called referential integrity.
> If the relationship is not enforced and there is a foreign key in a child table that does not match
the primary key of the parent table, then this is called an orphan row.
> Unlike primary keys, foreign keys can be null.
> Foreign keys are not always unique in their table.
> A foreign key can reference the primary key of the same table. For instance, the MANAGER of
an employee can reference the EMPLOYEE ID of the manager.

Tables and columns have unique names, but rows are unnamed. Rows can not be identified by their position within the table, and the relational model looks at a table as an unordered set. Therefore, each table is required to have one or more columns as a unique primary key. It is traditional to place the primary key columns in the left-hand positions.

> A table must have one and only one primary key.
> A primary key with only one column is called a simple key.
> A primary key with two or more columns is called a composite key.
> No two rows in a table can have the same primary key.
> A primary key only includes the columns necessary for uniqueness.
> A primary key can not have an empty (null) value in any column.
> If referential integrity is enforced, a primary key can not be deleted unless all foreign keys are also deleted.

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.