Relational Databases Tutorial

Welcome to the Relational Databases Tutorial offered by Simplilearn. The tutorial is a part of the SQL Database Training Course.

Let us begin by looking into the objectives of the tutorial in the next section.

Objectives

The topics covered in this Relational Databases Tutorial include -

  • What are databases

  • Role of databases in application programming

  • Tables and Relations

  • Primary and Foreign Keys and Other Constraints

In the next section, let us learn about the SQL databases.

What are Databases?

There is a number of the different kinds of databases in sql. However, in this tutorial, we will concentrate on-

  • Relational databases

  • Hierarchical databases

  • Object-relational,

  • Object-oriented databases

Hierarchical databases mostly feature in the mainframe computing area and object-oriented databases use object-oriented concepts similar to c++, .net, Java that likes to model the database.

These programming languages are not required to be used much. Thus, relational databases remain at the heart of the database technologies. In the image shown, we have the entity relationship. The diagram of a database and a relational database are as shown below.

[image]

In this tutorial, we will make use of the MySQL Workbench as a tool which is a free tool that can be downloaded from the MySQL website. You can find similar tools like graphical tools and query tools in all common database systems these days.

From the diagram, we find two main elements of a relational database; they are the tables and their relations and thus they are called relational databases. The tables, for example, a person table, are the boxes as shown in the image

[image]

The relations are the connecting lines between the tables.

[image]

Let us first look at the tables. There are six tables here and each table has a set of columns.

[image of person table]

In the person table, we have -

  • A unique ID for each person,

  • The First name,

  • The Last name,

  • Date of Birth,

  • The Manager ID,

  • An area to record notes,

  • A time stamp for when the record was created.

The Manager ID is that it is one person that another person is reporting to, which is the person on the right in the diagram.

In our SQL editor, we can have a look at that table and its content.

[image]

On top of this table are the columns whose names are listed above. There are multiple records in the table in different rows. Each row is a person with their respective details. From the table, we find that there are null areas, particularly in the notes column which is perfectly normal as we have optional elements in such a database table, like the optional information that we may or may not have.

Heading back to our diagram, we will look at the relationship aspect now. Let us take an example of the relationship between a person and contact.

[image]

For contact, the database is about the person being part of a project team, the contact details were in association with projects and the likes. From the image, we see the relationship between the person and the contact table.

The contact table allows you to record the contact details of a person which could be many, like, the person’s telephone number or multiple telephone numbers, a postal address or any mail address and so on. These are some of the various kinds of contacts that we can set up in this contact type table.

In the image shown below, we find that, at the end of the relationship line, it splits up indicating it is a one to end relationship.

[image]

In this occasion, it means that one person can have ‘n’ associated records in the contact table that allows to nicely structure your data and avoid redundancies. Imagine the scenario of a person and multiple contact details in an excel sheet, for instance, then we would obviously create a lot of the redundancies and would look like shown in the image below.

[image]

Here, we have actually pulled together the various data from the various related tables. We now find that there are multiple lines for each person depending on how many contact details they have.

For example, Paul Miller has three records here, an email, a phone, and an address. We find in the output that his personal details are redundant, that is if it was so in our data source. This is not a good practice, because, when we need to maintain this data, it will probably create problems.

Thus, in this way, having a relational database allows us to reduce redundancies. But, in the end, we need to create such fled views for many usages.

In the next sections, we will learn about tables and relationships, and querying the data we hold in our database.

In the next section, we will look at the role of sql databases in application programming.

How about investing your time in SQL database training course? Take a look at the course preview NOW!

What is the Role of SQL Databases in Application Programming?

In this section, we will talk about the role of the sql databases in application programming. Take a look at a database schema with nicely-structured and designed tables. It's a great way of storing your data efficiently, but it is not easy to look at this data and work with these data input data.

[image]

Let us have a quick look at the SQL editor again.

[image]

Here, we see one such table, a person table, and it only has the data of that person. What we require will be some kind of view, like the personal contacts as we can see here.

[image]

For example, the users provide with their contact details. As shown in the image, we have different people on the team. Considering the case of Paul Miller; there are three different ways of contacting him. It needs quite a complex query to get this data together and we will learn at a later stage how to do that. But this proves a point that it is actually not very easy to work for an end user.

Thus, what we can do is, have frontend applications that will make use of this database to visualize and build proper interfaces for a user to display and input data. Consider a simple example here taken from PHP, which is one of the most common application development languages in the web application development, aside from .net and Java.

[image]

From the image shown above, we find that we can plot out the database table on a webpage screen using PHP. To do this, look at the PHP script shown below.

[image]

This PHP script gives us an idea of these application modules that implement this. Irrespective of whether you use PHP, Java, or .net, the concept is always the same. We need to establish a connection to the database server into the database we are interested in. Typically, that needs some kind of user and password and because it's a local SQL server, we will use the default user and password.

In a protective environment, we want to add security to that setup and not use the default user and password that would be relatively easy to hack in.

Thus, in the first instance, that is -

“ $connection=mysql_connect($db_server,$db_user,$db_pw); ”

we have one PHP comment that does the database connection using the servers, a user, and the password. And once that connection is established, it uses the database name to connect to the individual database in the database server.

Next, we construct our query, which is very simple.

First, “ $sql= “select * from person; ”which means we want to plot out the whole table. This is not something you will do and practice very often. While we typically, filter our data., here,  we just want to see the concept of the application development.

Next, we can execute mysql_query (“$recset=mysql_query($sql, $connection);”) against the database connection that we have set up before. This will return a recordset. The record set includes that list of records that our query returns. So what we've just seen on the SQL query tool and also the web screen is the list of our records as they are in our database.

If this has not worked fine, something could have gone wrong like the database connection could have had a problem or the query could have an error in it and so on, and once it has gone wrong, we can simply output this data.

It is nothing but a normal HTML table which we construct step by step, by adding the headers and run a loop over our recordset is to fetch the individual records. For this, we have used the object style; we can also use the array style, whichever feels more suitable. In the section shown below -

[image]

We are plotting the individual content in each column of the record. The name of each of these elements in our recordset object is simply the name of the column. Finally, we do a bit of cleaning up; we destroy the recordsets and close the connection and that completes it.

And the result of the running that script is the output shown in the image below.

[image]

A simple and easier version of doing this in real terms is to add all kinds of designs to it to make it look nice and neat. We will add forms to this to modify the content to enter new persons and so on. There are no limits to your creativity, in this regard. It is quite simple to integrate such a database into our web application scripts.

Once the database is created, it takes only a few lines of code to actually build the wrapper around. Thus, the important part is to design the database and to understand how to query the database so that we get the right data on the screen; because the last thing we would want is pushing all kind of data over the network and then dissect them in our PHP script when actually the database could do that for us.

This is the whole purpose of SQL querying and so obviously we will spend a lot of lessons to learn about all the different options of secretary SQL query.

In the next section, we will learn about tables and relations in sql.

Tables and Relations

In this section, we will take a closer look at the database tables in sql database relationships.

[image]

Here we have a database schema or the database diagram or the entity relationship diagram and the boxes are the tables, and the lines between them are the relationships. This database is all about persons that are involved in projects, their role in the project and the contact details like; telephone numbers, email addresses, and so on.

Let us now have a look at one of the tables.

[image]

The person table has a couple of entities like -

  • Columns,

  • A unique id for the identification of a person,

  • The first and the last name,

  • Date of birth,

  • Manager id INT,

  • Reporting to a field,

  • Notes about that person, and

  • A time stamp when that person was created in the database.

Each of these columns has a clear and a unique name within the table, which is important as that is how we can access the data in that particular column of a particular record.

Next to it, we can find that each column has a data type assigned. Starting with the id, we have an integer. The primary keys or the unique ids in the record should always be numbers because it is the easiest for the database to handle to sort and to search.

Then we have the first name and the last name, which is a variable character; which means that it is a field that can vary in lengths and the database will just optimize the storage for it. In our explanation, we have used the default of two hundred and fifty-five characters, which is kind of an old-fashioned default but it does the trick for a lot of things.

It’s worthwhile noting here that there's no much point being much more distinct because of the whole idea of VarChar is that a database uses as much space as you actually put as content in there.

Thus, if your first name is only five characters long, it will only use five characters of the memory for it.

This is why we prefer to use this kind of default lengths for it. That way we don't get into trouble as a lot of the databases are very finely designed on how many characters you have for a first name or last name. Then, if you come around in the word you will quickly find out that some names that are varying. In many European countries, you get very long last names that are often combined.

For example, in some countries, you get very lengthy first names. So we really don't want to delimit that to ten or twenty characters, because we will easily run out of space.

We also have the text column, which is a large area and can take much more characters in VarChar. And finally, for the created, we have a time stamp. If you look around on the schema, there are few other bits featuring in particular.

[image at 4:20 of the project]

Here, we have a double, which is for floating point numbers. Then we have a project budget column. Therefore, each column in such a database table has a name, has a data type, and size for the data type. And finally, we have the column expressed by the solid or by the hollow diamond the ability for that particular field to be null or not null. This basically means, do you have to have a value in it or is it an optional column.

Finally, we have the keys. Here, all we need to know is that a primary key, which means that the c column makes sure that every single person can be identified uniquely.

Now, let us take a look at the relationships.

[image at 5.36 of relationships - connector]

If you look at these lines, you can find that all of them have a split at one end, which points to them being a one to end relationship. This means that one person, for example, can have n contact details; or one person can have multiple assignments to a project. One project can have multiple persons assigned and one role can have multiple persons.

Like, If you have a development team, it is likely that you have more than one developer on that team. This way, we can express relationships and structure the data into the different tables and that's the core principle of the relational databases. Whenever you have multitudes of something, you will outsource this into an external table and create such a one to end relationship.

Learning that is a skill in its own right and database design is an issue and a subject in its own right, which we will not learn in detail now. However, it is important to understand these relationships. There is also a one-to-one relationship.

They were used in the historical systems when we had significantly fewer front-end applications and we would actually have end users clearing a database directly and all you could do is restrict access on a per table basis. A typical example in those times was to separate personal details into the different departments.

Therefore, we would have a table for personal details that everyone could see and we would have a table for personal details for the same set of persons. For example, only the HR department would be allowed to see the table containing the personal details for the same set of persons, which also includes the salary details.

In modern database systems, we wouldn’t require this kind of a structure anymore because typically we would organize these through dedicated front-end applications that show the right data to the right audience that is supposed to see or you can set access limitations on a per column basis rather than only on a per table basis.

Thus, it is good to know that there is one to one relationship is found in the legacy system, but they wouldn't be designed in the new database systems nowadays. The other important relationship type is the end to end relationship. The end to end relationship is a theoretical construct that expresses that one entity, not only has n related entities on the other side, but the same goes the opposite way around.

It is a theoretically, important construct. But it can actually be solved in an easy way in databases by a combination of two one to end relationships with an intermediate table in the middle. Consider the below image for example.

[image - person, contact type, and contact table]

Here, we have the person table, the contact type table, and the contact table. In the end, we are trying to express that one person can have multiple contacts and also multiple contact types and equally, each multiple contact type has multiple persons assigned to it.

Similar to a person having an email address, a physical address, and telephone number and so on. To each contact type, for example, an email address lays off multiple people who have an email address. This is kind of a typical situation where wanting to end the relationship alone wouldn't help. Thus, we need the end to end which we will split up by putting these middle tables in.

Thus, in the end, this middle table does nothing else but pairing up the M and N partners.

[image]

Therefore, from the table shown, that is highlighted at this moment to key tables, which basically is a combination of the primary keys of both tables. Thus, we have a possible combination between a contact type and a person. On this occasion, we can add the exact content for that contact type where ever we like.

For example, for the first person, the contact type, the telephone, and the way the field would hold the telephone number; and for the second contact type, an email address to see where the field would keep the email address as a text. That way we can resolve the limitations of the on to end relationship.

This is perhaps a very common scenario. Once you begin creating a database, you very often find that once you think about the one to end relationship, this is an end to end relationship. Then, we can continue this concept further and it is done here in that project person table. The M to N can actually be something more than two tables, which makes it an M to N to P to O to Q.

To however far we desire to go. These M to N relationships normally use at least three or four tables and occasionally much bigger. Yet, the concept is the same. The middle table in these occasions is just a linking table, which gives you the combinations of person, project, and role. Thus, we basically express here that a particular person is part of the project, and in that project has a role.

For example, Peter Miller is a part of the web design project and is a project manager in that project. This kind of a structure gives an extremely powerful way of structuring the database and structuring the data. As you can probably imagine, this M to N relationship construct allows you to record as many combinations and associations as you want in your database.

There is no limit whatsoever except for the database size limit and these are well out of reach nowadays. So whatever kind of teams you have in whatever composition, you could actually keep the data of such a team in this reasonably simple database structure.

So here we have seen in some more detail the whole concept, of tables and relationships between them and a few typical relationship types and that is the backbone of every database system which then we used to carry the data to get the information together that we actually want to know on this data.

In the next section, we will look at the primary and foreign keys of the SQL database.

You too can join the high-earners club. Enroll our SQL database training course and earn more today.

Primary and Foreign Keys

In this section, we will talk about the sql database constraints which includes the primary keys, foreign keys, unique keys, and a couple of other constraints that you can set up in a relational database. We want to look at what role they play, why they are important and how to work with them.

As mentioned earlier, we have the relations that constitute a major element of the relational database and that's why they are called relational databases as they have tables that are linked by relations, like an end to end relationship as shown in the image below.

[image at 0.44]

By default, the SQL and a lot of the older database systems do not have any real enforced technical structure for it. Thus, the trick to creating a relationship is by having primary keys and foreign keys.

[image at 1.22]

Depending on which side of the relationship we are, we talk about either the primary key or the foreign key. First of all, the primary key always sits on one side of the relationship and therefore each person has a primary key, which is the unique id. This ensures that every record in that table can be identified uniquely.

It is a good practice to use numeric values for that because, the database can handle it that much easier than searching, sorting, and indexing your data. It is also the easiest way to get a unique value because when you look at such a person table, you can imagine it's virtually impossible to get any other unique value.

The combination of the first name and the last name is not unique. The number of people with the name John Smiths is way beyond remotely unique. Therefore, it is quite obvious that it won’t work. Even combining the date of birth doesn't work. You may find two people with the name John Smiths that have the same birthday.

Thus, the attempts made to create the primary keys from the data you already have in your database is not very successful and that's why we introduce the artificial primary key like the id, which in many occasions is the database which manages to automatically do it for you. In this way, they are just counting up and you can be sure that you have the unique key that also points to the primary keys.

The primary keys really don't have any business meaning they are just a number. There is no business sense to it and they are not meant to be something with a business sense and therefore they wouldn’t normally be shown to the user in an application. They are however important for our relationships. This is where the other side comes in the foreign key.

On the other side, in your side of the relationship, where you reference the person, like in this case, to record a particular contact for that person, you use exactly that id value. Thus, by having the same value in the pid, which is a reference to the id on the person side, you basically link the two records by saying the id and the person table, and the pid and the contact table is the same which means that we are talking about the same person.

From the end to end relationships that are highlighted in green as shown in the image below.

[image at 4.32]

We then have the ctid, which refers to the contact type to the table as shown in the image below.

[image at 4.37]

Thus, in the end, all you get is the id which ensures that you know which records in the different tables are linked with each other. In older database systems, you won’t necessarily have enforcement for them. In more powerful and more modern database systems, you get a concept called referential integrity, which ensures that you can’t do anything in the tables that violate such a relationship.

[image at 5.17]

For example, in the contact table shown, we cannot add a record as the person id doesn't exist on the person table. On the other hand, we cannot delete a person from that person table that still has the referenced records on this site because, if we do that, we would often leave records in that contact table.

Consider an example illustrating how it would look like in the end.

[image at 5.53]

Let us first look at the person table here. Here we see the persons with their primary key.

[image at 6.00]

In this image shown above -

  • We have put together a theory to combine the three tables - person, contact, and contact type. (We will look at the explanation of this theory in further lessons).

  • Considering the id column, we have, Susan Baker with the id in the person table as six; and the pid which comes from the contact table is six.

  • We also have the ctid which is one and this comes from the contact type table which is also one.

In each of these lines, you can see the links. Normally, we wouldn't output these ids to the user because it is irrelevant to the user and thus, we usually hide these columns. In order to demonstrate how the primary and foreign key structure works, it is quite handy to see these equals.   

That is all about the primary and the foreign keys in sql. There are a few other constraints, one of which is the unique key of sql.

Unique Key

The unique key is similar to the primary key. The only difference is that you can always have only one primary key in a table, whereas, you can have multiple unique keys in a table; this means, that the values in such a column needs to be unique.

This is quite tricky and we don’t tend to use it very often because, on various occasions, things are not as unique as we imagine. Like, if we were having a database table that holds the lock in details, we would require to have such unique criteria.

For example, let’s say we have the lock in name or the lock in email address that needs to be unique so that a particular lock in name and a particular lock in email address clearly links to one account in our system. Thus we have scenarios where this makes sense, but, there are

other scenarios where people tend to think that the unique key effectively does the limiting in an unsuitable way.

Another constraint mentioning here is not null.

[image at 8.40]

Here, at some of the points, like the diamond, if it is hollow, it can be null and if it is not hollow, it can't be null.

[image at 8.50]

This means that the fields are either mandatory or optional.

We can get all kinds of extended constraints in database tables, like, we can constrain the list of valid values in it, or arrange the valid values.

Next, we will look at the primary keys of sql.

Primary Keys

A primary key can always be the only one on the table. The primary keys can be composite; this means that, from the image shown below, we observe that both the pid and the ctid have the key that shows that it is a composite primary key.

[image at 9.50]

Thus, unlike the other tables, where the value in one column id, for example, requires to be unique. Here we have a combination of the pid and the ctid which must be unique. This means that we can only have one entry per person of a particular contact type that we have defined. Therefore, for example, we can have only one telephone number or one mobile number or one email address or one physical address per person.

This could be for certain purposes to Richard and then we need to open that database design. From the image shown below

[image at 10.47]

We can continue this further in the project-person table and have a triple composite primary key, as the table describes a person being assigned to a project in a particular role, and that seems to make sense.

Therefore, a person can be assigned to a particular project and a particular role and that together needs to be unique. You don't want to duplicate such records on the table. If you didn't have a composite primary key, you could enter ‘me’ twice on the project website as a project manager.

This would seem irrelevant in lists that you create later. Hence, the primary key, here the composite primary key ensures that you don't end up in such situations.

Thus we conclude our overview of the primary key and foreign keys. Understanding this concept becomes important once we start writing queries. Therefore you know which data you will pair up with each other to create or use your relationships, to pull your data together in a sensible way.

Summary

Let us summarize what we have learned in this Relational Databases Tutorial. We learned about-

  • What are Databases

  • Role of databases in application programming

  • Tables and Relations

  • Primary and Foreign Keys and Other Constraints   

Conclusion

With this, we come to an end to the Relational Databases Tutorial.

  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

Request more information

For individuals
For business
Name*
Email*
Phone Number*
Your Message (Optional)
We are looking into your query.
Our consultants will get in touch with you soon.

A Simplilearn representative will get back to you in one business day.

First Name*
Last Name*
Email*
Phone Number*
Company*
Job Title*