SQL / Relational Databases

---

An Introduction to SQL

Structured Query Language (SQL) is an open standard that has been maintained by the American National Standards Institute (ANSI) since 1986. A major revision of the SQL standard, SQL2, was released in 1992.

Also the version, SQL3 contains object-oriented components.The SQL language is essentially a programming language for relational databases and is independent of the underlying database structure.

Today, SQL is one of the most widely used cross−vendor languages. Many relational database management systems (RDBMS) support SQL language and if you know how to use it, you can create database applications in MySQL, MS SQL Server, DB2, Oracle, PostgreSQL, and other relational database management systems. SQL stores data in objects which have been created with relations among them.

 The database objects in its main structure are tables which are indemnified for exclusive objects or for properties (attributes) of them. Thus, a database is a collection of tables and functions.
A table is an array of two dimensions and each field of it (column of the table) represents an attribute of the object (entity) which the table concerns. On the other hand the horizontal structure of the table (rows) declares the data records which the table contains.

Thus, a database table is a list of records (rows) which have the same structure and the same fixed number of fields (columns). Unfortunately, until now the SQL compatibility could not be achieved by RDBMS commercial database developers. ANSI SQL92 has been developed as a standard that should be followed by those who are going to create database systems supporting SQL.

RDBMS developers in most of the cases in order to differentiate their systems are based on a different version of SQL implementation and do not implement fully ANSI SQL92–compatible SQL engines, adding their own different features and the database portability among different database systems is lost. You can visit the official site of ansi.org for further information about the ANSI SQL92 standard at: http://www.ansi.org.

---

The Flow of Information

First of all you have to understand the procedure of the flow of information between you as a user and the database when you are using SQL.

This flow of information consists of commands as well as data and is always handled by the DBMS which undertakes to manage them. All communications between the user and the database is made by the DBMS.

Users requests are sent to the DBMS and after proper processing they are turned into a readable and writeable format to the database engine. The procedure below shows the path of processing that an SQL command follows:

• SQL request DBMS processes and sends a request to the Database Engine.
• Database Engine retrieves data from database and returns the result to DBMS which returns data to user.

The tables keep the data of the database and control its structure.
Through the DBMS you must be able to submit queries to database for the values of its tables in order to see, to change, to delete or to add new data to them.

 Thus, the SQL92 standard SQL language implementation offers more than 40 commands in connection with all DBMS functions such us data access control, data definitions, data retrieval, data handling, data sharing and data integrity.

In order to cover weaknesses of SQL in connection with the flow control and conditional statements we use other programming languages like C, PHP or Pascal working in a joined very powerful programming environment as we will see in detail later.

---

The Model of a Relational Database and SQL Components

The model of a relational database was created by E. F. Codd in 1969. The relational model is based on the set theory which was introduced in mathematics by the German mathematician Georg Cantor (1845-1918) who was considered the father of set theory.

The concept of the relational model is that an unordered set of tables which are related to each other could be the content of a database. Then the tables could be called relations and could be modified by the user.
Each table is a sub-set of the relational database which is the hyper-set and represents a unique entity which contains a set of objects with common characteristics (attributes). Each entity or object of the table is a row (record) of the set of attributes (columns) of the relation (table).

Relational Data Model is based on a simple data structure which is the relationship. Thus, a relational database is presented as a collection of relationships each of which can be stored separately. Every relation is represented by a two dimensional table. Each table from the collection of database tables is a sub-set of it and is composed by an unlimited (theoretically) number of rows and from a specific number of columns which are its attributes or data items or elements or fields.

Each attribute has a specific set of values. Thus, tables are relations under which entities and their attributes as well as relationships among them are stored. As we have mentioned earlier an entity or object is represented by a row in the structure of the table. Then, the specific table keeps information for a set of entities or objects of this category as much as the rows (records) of it.
Thus, in a relational database we have entities or objects that contain data which can be modified by the user and they are grouped as sets in tables which represent relations with common characteristics. In terms of set theory, the attributes or characteristics of a table (table columns) are elements of a set that contains sub-sets (table rows).

In the following graphic representation we show the three levels of a typical database structure based on the set theory. In the first circle we present the database set of tables (root set) which contains all tables of the data model as elements (three element-tables). In the second circle we give the structure of the table as a sub-set of the database set.

In this evel the elements of the table set are the attributes (columns) of the table. In the third circle we present the structure of the sub-set of an attribute-element of a table set.
In this case each element of this set represents an entity (row) and inherits its attributes from its parent table set.

The relations among the database tables are defined during the phase of database schema design which is the data model that the database will be based on.
The phase of the database schema design takes place after the phase of data model analysis and its requirements.

Thus, the model of the relational database must be based on a database where all data visible to the user is organized strictly as tables of data values and all database operations work on those tables.

During the next sections we are going to discuss the more essential components of relational databases.

---

Tables and Keys

As already mentioned, tables and the relationships among them are the elements or components of the set that declare a relational database. In order to create collection of values that inform us about entities with specific characteristics (attributes) we create tables.

Tables with columns and rows

Each column represents a piece of information (one field) that we want to keep for the specific entity which the table describes. As we mentioned earlier every column-field represents an attribute of the object-entity.
Each column is declared so as to be able to store a unique data type. Each column accepts only one predefined type of data, INT for integer, or VARCHAR for a variable number of characters up to a defined limit.

• Domain is the set of values that a column can contain
• A unique name is assigned per each column in a table
• There is no limit for the number of columns per table, but the norm is 255

Each row defines a single data-record in the table
• Rows are in no particular order
• There are no constraints on the number of rows (data-records) in a table

Look at the following example which gives us a table based on a simple payroll list ordered by employee identification numbers.
Table: "employees_payroll"

This table keeps information about the employees payroll of a small firm and consists from four rows and four columns. Thus, we can say its dimensions are (4x4).
Every column has a certain data type and every row is divided into four fields (columns).
Thus, SQL gives us the possibility to submit queries to collect all values with a common data type and definition.

Additionally, we can select one or more columns of a table in order to get all data-values they content. The intersection between rows and columns in a table in connection with the access through primary and foreign keys is the great power of SQL.

Datatypes

Every column in a table must have a datatype. During the data analysis phase the database developer chooses the best datatype for each attribute that is going to be defined as a characteristic for each table.
For example attributes that are going to be used as people names such as names of students or employees must be defined as character strings with a defined length. Thus, the suitable datatype for names would be varchar(50).
With this definition we put the maximum size of characters for this specific variable or field (attribute) to 50. Remember that only the actual length of text characters from the defined variable varchar is stored in the database and not the maximum length of its declaration.

Keys, Primary and Foreign Keys

A key is a column that can be used to identify a row in a table. If we use a set of columns to identify a row in a table then this set is called a super key. For example, look at the "employees_payroll" table.
We could use the id_number and the last_name together to identify any row in the table. We could also use the set of all the columns ( id_number, first_name, last_name, Salary). These are both super keys.

(i) Primary Keys
A primary key is a column of the table which must contain unique data values. Every table should have a primary key. In our employees payroll table the primary key is the column id_mumber which identifies all records (rows) of the table for unique employees with unique values.

Look at the following entity "employees_payroll"

The id _number enables first _name, last _name, Salary to be found.
The id _number is the Identifier or Primary Key of entity "employees_payroll"
The primary key attribute is underlined. It cannot take a NULL value
Primary Keys are very important and have significant impact on performance of database. Additionally keys are needed to guarantee the data integrity.
Especially:

• You cannot refer to rows by location (first, last, third, etc.) as rows have no specific order
• Every table must have at least one column declared as primary key (you can declare more than one)
• In a column marked as a primary key every row has a unique value as content

(ii) Foreign Keys

The foreign key of a table is used to create a connection between two tables. The value of a foreign key that a table keeps is always the primary key of the table with which it has established a relationship.
If we suppose that we have a database with two tables, one for employees payroll with the name "employees_payroll" and a second one with the name "employees_overtime" for employees who worked overtime during the month.
Then the relationship between the two tables is created by the foreign key in the table of "employees_overtime".
The entity of "employees_overtime" will be:

The id_overtime is the Identifier or Primary Key of entity "employees_overtime"
The primary key attribute is underlined .The primary key cannot take a NULL value
The employee_id is the Foreign Key of entity "employees_overtime" which must be identical with the Primary Key of entity "employees_overtime"
The foreign key attribute is underlined with a broken line
The id_overtime enables hours, overtime_date, employee_id to be found.

Entities and Types of Table Relationships

SQL is a relational database language which supports databases that consist of a set of relations. A relation between two or more tables of data is defined as a connection among them. Note that the terms table and relation mean the same thing. In our approach to this matter we will use the more common term table.

Entities, Objects, Classes and Inheritance

Before we start to design a data model we have already decided for which entities we will store information in our database and the relationships among them. Entity or object is a real thing that exists in the real world.

An example of entities or objects are employees, overtime work hours, cars, books etc. These entities or objects are composed of attributes that they give them specific behavior that characterize them.
An Entity is something of significance to the system about which information is to be held. Objects have attributes (variables) and encapsulate into them values which only through specific methods (functions) can we change. Thus, we can have relational databases that consist of objects. In an object orientation we can correspond the table with the class and the entities inside the table with objects. A class is a named collection of object instances. Each instance must have a unique object identifier (OID).
Inheritance is implemented in object-oriented systems and means that a class which is in a higher level of the hierarchy structure can inherit its attributes or its methods (functions) to the lower class.
The lower class inherits all information from the upper class.
Our next step is to implement additional features to this class.
Look at the following example to understand the inheritance process better:
Let us assume that we have a table containing information about students. We define a class (table) that stores all information about a student that is common for students, such as the first name, second name, age and school.
Additionally we define a class (table) for a specific type of student that is used to store additional information, such as student examination results.
The second class for student examination results inherits all information from the parent class which holds information about the students.

Schemas, Relationships and Notations

The database schema simply shows the structure of a database with tables.
Also it means the design of the database or the form of the database without any data in it. To describe the database schema of the previous example with the two tables we use the mathematical type of multi-variable function.

employees_payroll(id_number,first_name, last_name, Salary)
employees_overtime(id_overtime,hours,overtime_date,employee_id)

The expressions above describe a database schema with two entities-tables which are defined by a set of attributes respectively.
Each attribute name in parenthesis defines a column in the specific table and operates as independent variable of the function which is the entity-table.
To clarify how entities or objects are stored to the specific entity-table you can think in terms of set theory in which the entity-table defines a specific set (or category) of elements (entities or objects) with the same attributes (characteristics). Thus, the data of each row of the entity-table gives you a unique entity or object from the same set (category).
A relationship is an association between two entities which is important to the system. In the real world entities have relationships with other entities.Thus, relationships define access from one table-entity occurrence to another and under a specific type of connection.
 These relationship types are the following

• One-to-One
• One-to-Many (or many-to-one depending on the  direction you are looking at it from)
• Many-to-Many.

A one-to-one relationship connects exactly two table-entities. If each employee in an organization had a laptop, this would be a one-to-one relationship. If every employee in an organization works for more than one department, this would be many-to-many relationship.

When you are beginning with a database design, you must define the relationships of the entities of the system you are modeling. Do not forget that two systems are not going to be exactly the same. In the following table we present the main and most popular data modeling notations through which we can define the relationships between entities.

Finkelstein in 1989 introduced a simple and easy symbolic way to read the relationships between the table data models.

This notation way is well suited for high-level logical data modeling.
But it does not support the identification of an entity attributes.
Barker in 1990 presented his own way of notation that today is one of the most popular. In this book we are going to use this way of tables associations and relationships. The way of Barker offers a very useful notation for entities which are embedded into other entities as sub-types into super-types which is the same with the set theory symbolism as well as a subset is included in another set as an element.
This approach to subtypes of relationships (entities) is a great advantage as long as it supports the inheritance through hierarchy of several levels deep.
The UML data modeling notation is not accepted by the IT industry as a notation standard yet.

But considering the popularity of the UML, and the efforts of the Object Management Group (OMG) the recognition will come very soon.
In our tables relationships examples that follow we will use underline to define an attribute as primary key and a broken underline with a star to define attributes as foreign keys.

---

Relationships

The way of Barker offers a very useful notation for entities which are embedded into other entities as sub-types into super-types which is the same with the set theory symbolism as well as a subset is included in another set as an element. This approach to subtypes of relationships (entities) is a great advantage as long as it supports the inheritance through hierarchy of several levels deep.

One-to-One Relationships

In this kind of relationship, a key will appear only once in a related table.
An example of a one-to-one relationship is if each employee is assigned at one department within a company.
The one-to-one relationship of employees to departments in the data model is shown below.

One-to-Many Relationships

In a one-to-many relationship, keys from one table will appear multiple times in a related table.
An example is the connection between monthly employees overtime work and the master table of employees of a company.
Indeed, more than one records (rows) with the same employee key can exist in the employess_payroll table as well as the same employee can have overtime work more than one days per month.
The one-to-many relationship of employees to employees overtime work in the data model is shown in the figure that follows.

Many-to-Many Relationships

In a many-to-many relationship, the key value of one table can appear many times in a related table. It is absolutely certain that you will face many-to-many relationships during the design of a data model and the creation of its database.

Thus, you will have to know how to manage this situation since it causes problems until your database is normalized.
In order to solve this problem simply break the many-to-many relationship to one-to-many relationships creating a new table.

So the new table is structured by the repeated keys of the many-to-many tables.
Using the example of employees who work in more than one department in a company we have the following structure association of employees and departments entities.

Look at the following entities association figure.