KayeILSQL-01-Database Concepts: A Relational Approach

Contents

The European Parliament (EP) is the parliamentary institution of the European Union (EU).

The European Parliament is elected by the citizens of the European Union to represent their interests.

Its origins go back to the 1950s and the founding Treaties. Since 1979 its members have been directly elected by the citizens of the EU.

Elections are held every five years, and every EU citizen is entitled to vote, and to stand as a candidate, wherever they live in the EU. Parliament thus expresses the democratic will of the European Union's nearly 500 million citizens and it represents their interests in discussions with the other EU institutions.

The European Parliament model and data used in this SQL text, are based on the elections which were held in June 2004. The European Parliament of 2004 elections had 785 members from all 27 EU countries.

The sources for the data, which are all freely obtainable from the European Union website, and from several leaflets of the EU institutions, are listed in the References section.

These sources are used to create the fictitious Business Narrative of the "Data Modelling and Normalization" Chapter.

We called this Business Narrative; "the Story of the European Union" which explains about the EU and EP, for purposes of Data Modeling.

The Story of the European Union is the last Chapter of Kaye is Learning SQL.

A report which is one of the application programs in a Business Information System, helps users take their business decisions.

Sometimes, these decisions have to be made immediately – they can be daily decisions, hourly decisions or even minutely decisions.

Sometimes decisions have to be made whose results will affect a business in the long run.

These reports are also known as the "Business Intelligence" of a company.

Whether they will affect this very minute, or a ten years’ span, the users need the "information" in these reports..

Information that is necessary to take the right decisions – the decisions that are vital for a business to operate and to be successful.

Information itself uses "data" stored in the Database and managed by the Database Management System Software .

• Data are the raw materials.
• They are the atoms.

And when the data are processed – that is when they are manipulated, collected and organized, the result is information.

Below are some of the basic terms used, to describe the building blocks of a database.

Example

If you were a statistician in EUROSTAT-the statistical office of the European Commission, you would like to have all the information about the European Union member countries.

A COUNTRY is an Entity Type or an Entity Class.

However, each COUNTRY in the European Union is an Entity Instance .

For example, Belgium and Poland are Entity Instances of the Entity Type COUNTRY.

There is only one Entity Type/Class of COUNTRY, whereas there are twenty-seven Instances of this entity.

Another Example

If you were working in the DG(Directorate-General) Personnel of the European Parliament , you would like to have all the information about the Members of the European Parliament(MEPs).

A MEMBER OF THE PARLIAMENT is an Entity Type or an Entity Class, and each Member of the Parliament is an Entity Instance.

There is only one Entity Type/Class of MEMBER OF THE PARLIAMENT, whereas there are 785 Instances of this entity.

Example

Some of the likely "attributes" for a country are;

• country name
• capital
• population
• area
• gdp
• and so on...

Another Example

Some of the likely "attributes" for an MEP are;

• last name
• first name
• political group
• country of citizenship
• salary information
• start date
• and so on...

Reationships are interactions between Entities.

They are described by "verbs".

Example

A Member of the European Parliament(MEP) belongs to a Political Group.

Therefore, we can say that the Relationship between the Entity MEP and the Entity POLITICAL GROUP is "belongs to".

Now that we know briefly about the concepts of "Entity" and "Relationship", we can say that;

a Database is made up of a Collection of Entities and the Relationships between these Entities.

Example

European Parliament's database may be made up of a Collection of the Entities "COUNTRY", "POLITICAL GROUP", "MEMBER OF THE PARLIAMENT", "DELEGATION", "ROLE" and the Relationships between these Entities.

When we design a database, we need to create Entities each of which is a collection of entity instances. For example Entity POLITICAL GROUP and the Entity MEP (Members of the European Parliament).

When we design a database, we also have to establish the "relationships" between these "entities".

Depending on the type of interaction, the relationships are classified into three categories:

A one-to-one relationship is written as 1:1 in short form.

Entities X and Y have a 1:1 relationship if the following conditions hold:

• An entity instance in Entity X has only one matching instance in Entity Y
and vice versa - in other words,
• An entity instance in Entity Y has only one matching instance in Entity X as well.

Let's say in our database, there is an Entity called COUNTRY, and an Entity called CAPITAL.

The RELATIONSHIP between these two entities is:

We say that, the entities COUNTRY and CAPITAL have a 1:1 relationship.

A one-to-many relationship is written as 1:M in short form.

Entities X and Y have a 1:M relationship if the following conditions hold:

• An entity instance in Entity X has many matching instances in Entity Y,
but
• An entity instance in Entity Y has only one matching instance in Entity X.

Let's say in our database, there is an Entity called POLITICAL GROUP, and an Entity called MEMBER OF THE PARLIAMENT.

The RELATIONSHIP between these two entities is:

We say that, the entities POLITICAL GROUP and MEMBER OF THE PARLIAMENT have a 1:M relationship.

A many-to-many relationship is written as M:M or M:N in short form.

Entities X and Y have a M:M relationship if the following conditions hold:

• An entity instance in Entity X has many matching instances in Entity Y,
and
• An entity instance in Entity Y has many matching instances in Entity X.

Let's say in our database, there is also an entity called ROLE.

We already know that there is an Entity called MEMBER OF THE PARLIAMENT in our database.

The RELATIONSHIP between these two entities is:

We say that the entities MEMBER OF THE PARLIAMENT and ROLE have a M:M relationship.

To determine the Type of Relationship between the Entity X and the Entity Y, we can ask the following two questions:

Depending on the answers to these two questions, we can determine the type of relationship, between the two entities.

How this is achieved, is illustrated below:

A table is a matrix of rows and columns in which;

• each row represents an entity instance
and
• each column represents an attribute

We refer to a Relation as an Entity at the Data Modeling and Database Design stage.

When the physical database is created, Entities become Tables.

We will use the following concepts interchangeably for the rest of this chapter:

Relation-Entity-Table

We refer to a Relation as an Entity at the Data Modeling and Database Design stage.

When the physical database is created, Entities become Tables.

Entity Instance-Row

A table is a matrix of rows and columns in which;

• each row represents an entity instance
and
• each column represents an attribute

Attribute-Column

A table is a matrix of rows and columns in which;

• each row represents an entity instance
and
• each column represents an attribute

We will use the following Relations-Entities-Tables of a Sample European Research Institution to learn new terminology:

• PROJECTS
• PROGRAM_PROJECTS
• PROGRAMS_LAST_YEAR
• PROGRAMS_THIS_YEAR
• STAFF
• DEPARTMENTS

PROJECTS has;

• 5 Attributes/Columns
• 4 Entity Instances/Rows

PROGRAM_PROJECTS has;

• 3 Attributes/Columns
• 10 Entity Instances/Rows

PROGRAMS_LAST_YEAR has;

• 3 Attributes/Columns
• 3 Entity Instances/Rows

PROGRAMS_THIS_YEAR has;

• 3 Attributes/Columns
• 4 Entity Instances/Rows

STAFF has;

• 4 Attributes/Columns
• 5 Entity Instances/Rows

DEPARTMENTS has;

• 2 Attributes/Columns
• 3 Entity Instances/Rows

In relational terminology, a Row in a Table or an Instance of an Entity is referred to as a Tuple.

• It is assumed that there is no pre-defined order to rows of a table.
• Also, no two rows of a table have the exact same set of values.

The number of columns in a table is called the Degree of the relation.

For instance, if a table has four columns, then the table is of degree 4.

Just like the order of rows is not important in a Relational Database Model, the order of the columns is not important either.

The set of all possible values that a column may have is called the Domain of that column.

An Example for Domain

The domain of the "dg_name" column of the "DEPARTMENTS" table is the set of all possible Directorate-Generals in this Research Institution.

This domain can be;

• DG Communications
• DG Innovation and Research
• DG Legal Service
• DG Finance
• DG Human Resources
• DG Technical Services
• ... and so on.

Another Example for Domain

Domain of the "country" column of the PROGRAMS_THIS_YEAR table, is the set of all countries that take part in the European Research Project, namely, all the EU Member and Candidate countries;

In our tables, the columns;

• dg_id
• staff_id
• program_no
• project_no
• number_of_projects

all have numeric values, but their use is different. Therefore they do not have the same domain.

A Key is a minimal set of columns used to uniquely identify each row in a table.

If a single column can be used to identify each row, there is no need to use two columns as a key.

An Example for a Single Column Key

In PROGRAMS_LAST_YEAR and PROGRAMS_THIS_YEAR, "program_no" column uniquely identifies each row.

In these tables, the column "program_no" is a Single Column Key.

Another Example for a Single Column Key

In PROJECTS, "project_no" column uniquely identifies each row.

In PROJECTS, the column "project_no" is a Single Column Key.

Example for a Key with More Than One Column

In PROGRAM_PROJECTS, none of the columns can uniquely identify a row.

In this table, a combination of columns must be used to uniquely identify a row.

This combination is "program_no" and "project_no" cloumns.

In the PROGRAM_PROJECTS table, "program_no" and "project_no" columns together can be used as a key, to uniquely identify each row.

UNIQUE IDENTIFIER

A KEY Column, or a Combination of Columns that act as a KEY , is sometimes called a UNIQUE IDENTIFIER.

When a Single Column is used as a Unique Identifier , then this column is known as the Primary Key of the table.

For example, "project_no" column is the Primary Key of the PROJECTS table.

When a Combination of Columns is used as a Unique Identifier, then these columns are known as;

• The Composite Primary Key
or
• The Composite Key.

Sometimes a more human approach is used to identify or retrieve a row from a table.

This is because, it is not possible to remember primary key values such as;

• staff identification number
• department identification number
• project number
• ... and so on.

In such cases,

• staff name
• department name
• project type
• project name
• ... and so on,

Such a key is known as a Secondary Key.

If none of the columns in a table is a candidate for the primary key, then database designers can use an extra column as the primary key.

In some cases, this can be preferred to having a composite primary key.

Such a key, which is a Unique Identifier for the row it belongs to, is known as a Surrogate Key.

• A new column such as "country_identification_number" which will act as the Surrogate Key, can be added in a table to identify a country.
• A new column such as "program_project_identification_no" which will act as the Surrogate Key, can be added in a table to identify a project within a program.
• A new column such as "sponsor_number" which will act as the Surrogate Key, can be added in a table to identify a sponsor.

• Surrogate means "substitute".
• Values of surrogate keys serve only as surrogates or substitutes for the entities they stand for.
• They carry no additional information or meaning whatsoever.

One way of generating column values for the Surrogate Key Columns in Oracle is, to use the Sequence object.

In a relational database, tables are related to each other through a common column.

A column in a table that references a column in another table is known as a Foreign Key.

For example, the "project_no" column is a Foreign Key column in the PROGRAM_PROJECTS table, that References the "project_no" column in the PROJECTS table.

Although we will learn about the concepts of integrity later in this SQL Course in detail, here is an early introduction.

In a database managed by a Relational Database Management System, it is very important that the data in the underlying tables are kept consistent.

If consistency is compromised, then data become unusable.

This fact led to the following two Integrity Rules:

1. Entity Integrity
2. Referential Integrity

Integrity Rule #1 Entity Integrity

• Column values of a Primary Key MAY NOT be Null.
• The Primary Key provides the means of uniquely identifying a row, or an entity.
• Column values of a Primary Key MUST be Unique.

Integrity Rule #1 Entity Integrity

NULL

• A Null Value means a value that is;
  • not known
  • not entered
  • not defined
  • not applicable
• A zero or a space is NOT considered to be a Null Value.

Integrity Rule #1 Entity Integrity

If the Primary Key value in a row is Null, then we do not have enough information about the row to uniquely identify it.

Integrity Rule #1 Entity Integrity

• A Relational Database Management System software strictly follows the "Entity Integrity" rule,
• and, DOES NOT allow the users and/or application programs to enter a row WITHOUT a Unique and Non-Null value in the Primary Key column.

Integrity Rule #1 Entity Integrity

Integrity Rule #2 Referential Integrity

• A Foreign Key value MAY be a Null Value,
OR
• or it must exist as a value of the Primary Key in the Referenced Table.

For example, the "project_no" column is a Foreign Key column in the PROGRAM_PROJECTS table, that References the "project_no" column which is the Primary Key of the PROJECTS table.

Integrity Rule #2 Referential Integrity

• Oracle RDBMS does not allow you to input a value into the Foreign Key column, if it does not exist as a Primary Key value in another table.
• It allows you to leave the Foreign Key column value as a Null.

Integrity Rule #2 Referential Integrity

E.F. Codd suggested Two Theoretical Relational Languages to use with the Relational Model:

• Relational Algebra - a Procedural Language
• Relational Calculus - a Nonprocedural Language

In the database systems available today, nonprocedural Structured Query Language SQL is used as a data-manipulation language.

The two theoretical languages which will be explained below, have provided the basis for SQL.

Relational algebra is a procedural language.

It is the manipulative part of Relational Model as suggested by E.F. Codd.

"Relational Algebra is basically a Set of Operators that take Relations as their operands and return a Relation as their result."

• The user accomplishes the desired results, by using a Set of Operations in a sequence.
• It uses Operations on Relations to produce new Resulting Relations.
• These Resulting Relations are then used for subsequent Operations.

The nine Operations used by Relational Algebra are as follows:

The UNION of two tables results in retrieval of all rows that are in one or both tables.

The duplicate rows are eliminated from the resulting table -- the resulting table does not contain two rows with identical data values.

Union Compatiblility

Two tables that satisfy the requirements above are said to be Union Compatible.

• In Mathematical Set Theory, a UNION can be performed on any two sets.
• In Relational Algebra, a UNION can only be performed on UNION COMPATIBLE TABLES.

If we want to see all the programs that took place last year and programs taking place this year, we can perform a UNION on the PROGRAMS_LAST_YEAR and PROGRAMS_THIS_YEAR tables.

If we call the resulting table TABLE_A, the operation can be denoted by:

• The INTERSECTION of two tables produces a table with rows that are in BOTH tables.
• Two tables MUST be UNION COMPATIBLE to perform an INTERSECTION on them.

If we want to find out the programs that took place BOTH LAST YEAR and CONTINUING THIS YEAR, we can perform an INTERSECTION operation on the PROGRAMS_LAST_YEAR and PROGRAMS_THIS_YEAR tables.

If we call the resulting table of this INTERSECTION OPERATION, TABLE_B, then the operation can be denoted by:

The resulting table TABLE_B shows us those programs which started last year or before and continuing this year.

• The DIFFERENCE of two tables produces a table with rows that are present in the FIRST table but NOT in the SECOND table.
• Two tables MUST be UNION COMPATIBLE to perform a DIFFERENCE on them.

If we want to find out the programs which took place LAST YEAR, BUT DO NOT CONTINUE THIS YEAR, we can perform a DIFFERENCE operation on the PROGRAMS_LAST_YEAR and PROGRAMS_THIS_YEAR tables.

If we call the resulting table of this DIFFERENCE OPERATION, TABLE_C, then the operation can be denoted by:

The resulting table TABLE_C shows us those programs which took place last year and do not continue this year.

Just as in Mathematics, in Relational Algebra also, A - B may not be equal to B - A.

If we want to find out those programs which DID NOT EXIST LAST YEAR, BUT STARTED THIS YEAR, we can perform a DIFFERENCE operation on the PROGRAMS_THIS_YEAR and PROGRAMS_LAST_YEAR tables.

If we call the resulting table of this DIFFERENCE OPERATION, TABLE_D, then the operation can be denoted by:

The resulting table TABLE_D shows us those programs which started this year.

• The PRODUCT of two tables is a combination of all rows in both tables.
• It is also known as a CARTESIAN PRODUCT.
• It can cause huge results with big tables.

• If the First Table has X number of Rows, and the Second Table has Y number of Rows, then the Resulting Table has X TIMES Y number of Rows.
• If the First Table has M number of Columns, and the Second Table has N number of Columns, then the Resulting Table has M PLUS N number of columns.

For simplicity we take two tables with one column each and perform the PRODUCT Operation on them.

TABLE_1 has 1 Column and 3 Rows.

TABLE_2 has 1 Column and 2 Rows.

What will the Result of the PRODUCT Operation be?

If we call the Resulting Table of this PRODUCT Operation, TABLE_G, then the operation can be denoted by:

The Resulting Table of the PRODUCT Operation on TABLE_1 and TABLE_2, which is TABLE_G, has 6 Rows and 2 Columns.

Another Example on the PRODUCT Operation

Now, let us take the STAFF and the DEPARTMENTS tables, and perform the PRODUCT operation on these two tables.

STAFF table has 5 rows and 4 columns:

DEPARTMENTS table has 3 rows and 2 columns:

Cartesian Product of these two tables, will produce a Result Table with 5x3=15 Rows, and 4+2=6 Columns as seen below:

• ASSIGNMENT Operation creates a new table from existing tables.
• We have been using ASSIGNMENT throughout all the other operations above.
• ASSIGNMENT Operation is denoted by an = sign.
• ASSIGNMENT Operation gives us an ability to name new tables that are based on other tables.

We have already used the ASSIGNMENT (=) Operator in the previous examples as seen below:

• JOIN is one of the most important operations because of its ability to get related data from more than one table.
• JOIN is based on COMMON SET OF VALUES of two columns.
• To be able to perform a JOIN Operation on two tables, the JOIN columns;
  • DO NOT HAVE TO have the same name,
  • but they HAVE TO have the SAME DOMAIN.

If we are interested in STAFF information, but we also want to see the Department Name which is NOT IN THE STAFF TABLE, then a JOIN Operation can be carried out using the STAFF and the DEPARTMENTS tables.

The dg_id column is the common column having the same domain in both tables, and it will be used for the JOIN Operation.

This expression is read as:

In Relational Algebra, the logic behind the JOIN Operation can be depicted as follows:

Step 1

A PRODUCT Operation on the STAFF and DEPARTMENTS tables is performed first.

The Resulting Table T1 will have 15 Rows and 6 Columns.

Step 2

A SELECTION Operation on the Resulting Table T1 of Step 1 is performed next, selecting only those rows where dg_id = dg_id in T1.

Those rows in T1 where dg_id = dg_id are highlighted below.

The Resulting Table T2 of this SELECTION Operation on table T1, T2 = SEL(T1: dg_id=dg_id) can be seen below:

Step 3

A PROJECTION Operation is performed next on Table T2, eliminating the DUPLICATE dg_id Columns in T2.

Table T3 is the Result Table of the JOIN Operation.

The Resulting Table TABLE_H

The Resulting Table TABLE_H of this JOIN Operation on STAFF and DEPARTMENTS tables, which is explained in detail in Step 1, Step 2, and Step 3 above can now be seen below.

DIVISION Operation in Relational Algebra is similar to the DIVISION Operation in Mathematics.

• PROGRAM_PROJECTS table has all the Programs, and the Projects that are in the Programs.
• PROJECTS table has all the available Projects.

• Using the DIVISION Operation between the PROGRAM_PROJECTS table and the PROJECTS table, we create a Result Table.

TABLE_I = PROGRAM_PROJECTS DIVISION PROJECTS

• The Relationship between this new Result Table TABLE_I and the PROGRAM_PROJECTS and the PROJECTS tables is that; the Result Table TABLE_I holds only those Projects that are in each and every Program.

• Project 100 takes place in each and every Program.
• Project 200 takes place in Programs 10 and 20.
• Project 300 takes place in Programs 20 and 30.
• Project 400 takes place only in Program 30.

Project no 100, whose project type is 'ATOMS' and described as 'Nano Technology', takes place in each and every Program of this Research Institution.

Hence, the Result Table TABLE_I of this DIVISION Operation will only hold one row with project_no=100.

Step 1

Use the SELECTION Operation on the PROGRAMS_LAST_YEAR table to find the program where the country is Belgium.

The program that took place last year in Belgium is program_no=20.

Result Table TABLE_A of this SELECTION Operation is seen below.

Step 2

Use the JOIN Operation on the STAFF table and the Result Table TABLE_A from Step 1 to find the staff who is responsible of the chosen program.

We can see above the row in the STAFF table where program_no=20.

This gives us the Resulting Table TABLE_B.

Step 3

Now that we have the staff responsible for the program in Belgium last year, as seen in TABLE_B of Step 2, we can use the PROJECTION Operation on TABLE_B to find his/her name.

The answer is HEYNEN.

This resumes the Solution of Problem 1.

Step 1

Use the JOIN Operation on PROGRAMS_LAST_YEAR and STAFF to build up the Result Table TABLE_1.

TABLE_1= JOIN(PROGRAMS_LAST_YEAR,STAFF: program_no=program_no)

Step 2

Use the SELECTION Operation on TABLE_1 to build up the Result Table TABLE_2.

TABLE_2=SEL(TABLE_1: country='Belgium')

Step 3

Use the PROJECTION Operation on TABLE_2 to build up the Result Table TABLE_3.

TABLE_3=TABLE_2(name)

This resumes the alternative solution for Problem 1.

• Relational Algebra is a Procedural Language in the sense that the User is required to use a Series of Operations to obtain a required Result.
• Solutions can be difficult to develop.
• Relational Algebra CANNOT Group related information together.
• It CANNOT Perform Calculations on Numeric Values.
• It CANNOT Sort Rows in any particular order.
• Printing information with formatting is out of the question.

• The strength of Relational Algebra as a Programming Language is that its implementation forms the basis of fourth-generation query languages.
• These languages are supported by many other tools, which provide users with full application-development capabilities.

Relational Calculus is a non-procedural language.

The programmer specifies the data requirement, and the system generates the operations needed to produce a table with the required data.

Relational Calculus uses the general syntax:

• The list of columns is on the left of the colon(:)
• The expressions and conditions are on the right of the colon(:)

In Relational Calculus, the User/Programmer writes down the expressions necessary to solve this problem as follows:

(r.program_no) : 
r in PROGRAM_PROJECTS 
AND r.project_no = 400
    

In this expression, r is known as a row variable.

The expression is read as;

Each row in PROGRAM_PROJECTS is examined using the condition to the right of the column.

The Resulting Table T contains the program_no column where its value is 30.

This solution is in Relational Calculus.

In Relational Calculus, the system generated the required operations and produced the Resulting Table T with the required data, using the expressions given by the User/Programmer.

In Relational Calculus, the User/Programmer writes down the expressions necessary to solve this problem as follows:

        
(r.staff_id, r.name) : 
r in STAFF 
AND s in DEPARTMENTS
AND r.dg_id= s.dg_id 
AND s.dg_name = 'DG Innovation and Research' 
   
    

The Resulting Table T is as follows:

This solution is in Relational Calculus.

In Relational Calculus, the system generated the required operations and produced the Resulting Table T, using the expressions given by the User/Programmer.

In Relational Calculus, the User/Programmer writes down the expressions necessary to solve this problem as follows:

(e.name, d.dg_name, p.location) :  
e in STAFF			
AND d in DEPARTMENTS
AND p in PROGRAMS_THIS_YEAR
AND e.dg_id = d.dg_id
AND e.program_no = p.program_no
AND p.program_no = 10

The Resulting Table T is seen below:

This solution is in Relational Calculus.

In Relational Calculus, the system generated the required operations and produced the Resulting Table T with the required data, using the expressions given by the User/Programmer.

• ISBN 92-79-03653-X , How the European Union works: Booklet by EU Publications Office
• ISBN 978-92-824-2203-8, Information handbook of the Council of the European Union, DG Press, General Secretariat of the Council
• European Union

  https://europa.eu/european-union/index_en

• European Council

  https://www.consilium.europa.eu/en/

• European Parliament

  https://www.europarl.europa.eu/portal/en

• The Official Directory of the European Union (Who is Who)

  https://op.europa.eu/en/web/who-is-who