Talk:Relational database/rewrite

Tables

In a relational database all data are stored in tables. A visual representation of a table might be:

+------------+-----------+-----+
| First_Name | Last_Name | Age |
+============+===========+=====+
| John       | Smith     | 36  |
+------------+-----------+-----+
| Mary       | Brown     | 39  |
+------------+-----------+-----+

In this table there are three columns, labeled "First_Name", "Last_Name", and "Age", and two rows, which contain entries for each column. It is important to note that the order of rows and columns in a table is not significant, even though they are displayed in a particular order here.

In the relational model a table is called a relation, a row is called a tuple, and a column is called an attribute.

Primary keys

Suppose we wanted to add another row to this table, for another person named John Smith who also happened to be 36 years old. This would result in a duplicate row, which is usually not allowed. To avoid this, we would introduce a primary key, "Person_Id", and then we could add the new row:

+-----------+------------+-----------+-----+
| Person_Id | First_Name | Last_Name | Age |
+===========+============+===========+=====+
| 1         | John       | Smith     | 36  |
+-----------+------------+-----------+-----+
| 2         | Mary       | Brown     | 39  |
+-----------+------------+-----------+-----+
| 3         | John       | Smith     | 36  |
+-----------+------------+-----------+-----+

A primary key is necessarily unique, it must be different for every row in the table. Most tables are created with primary keys.

Foreign keys

Suppose we had another table:

+--------+--------------------------+
| Org_Id | Name                     |
+========+==========================+
| 1      | Sales                    |
+--------+--------------------------+
| 2      | Research and Development |
+--------+--------------------------+

Now if we want to add the information of which organization each person works for into the previous table, we don't have to repeat the long name for each person, we can simply use the primary key from this table:

+-----------+------------+-----------+-----+--------------+
| Person_Id | First_Name | Last_Name | Age | Organization |
+===========+============+===========+=====+--------------+
| 1         | John       | Smith     | 36  | 1            |
+-----------+------------+-----------+-----+--------------+
| 2         | Mary       | Brown     | 39  | 1            |
+-----------+------------+-----------+-----+--------------+
| 3         | John       | Smith     | 36  | 2            |
+-----------+------------+-----------+-----+--------------+

We are using the primary key of the organization table as a foreign key in the person table.

Referential integrity

Suppose for some reason the first row of our organiziation table got deleted:

+--------+--------------------------+
| Org_Id | Name                     |
+========+==========================+
| 2      | Research and Development |
+--------+--------------------------+

Then some values of the foreign key in the person table would not refer to any rows in the organization table. This is an example of failing referential integrity. Another example of failing referential integrity would be storing the same data, such as a person's name, in two different places with different spellings.

Normalization

We can help preserve referential integrity by not duplicating information in different places, where they can get out of sync. Structuring our tables to achieve this is called normalization. Normalization has several stages, but for most business applications 3rd normal form, or 3NF, is sufficient.

Joins

To combine information from two tables, you use a join. In our example, we might want to see a list of persons along with the name of the organization they work for, without having to look up the organization id by hand. To do this we would use the following SQL statement:

SELECT First_Name, Last_Name, Name
FROM Persons, Organizations
WHERE Persons.Organization = Organizations.Org_Id

Which would yield:

+------------+-----------+--------------------------+
| First_Name | Last_Name | Name                     |
+===========+============+==========================+
| John       | Smith     | Sales                    |
+-----------+------------+--------------------------+
| Mary       | Brown     | Sales                    |
+-----------+------------+--------------------------+
| John       | Smith     | Research and Development |
+-----------+------------+--------------------------+

This is an inner join which is the most common type.

Indexes

Indexes do not change the logical structure of the database, they are purely a performance optimization. An index works like the index to a book; instead of having to read the whole book (or table) to find a specific page (or row), you can look up a particular key word on the page (or value in the row) and go to the page (or row) directly.

Indexes are defined on one or more columns, usually the primary key.

Views

Views are like tables that are not actually stored in the database, but are defined using a query on some underlying tables. A view on a single table may display only some of the columns or rows of that table. A view on multiple tables will perform some kind of join to yield table=like output.

SQL

Strictly speaking SQL, or Structured Query Language, is not part of the relational model, but it has become a standard part of all relational databases.