Relational Database Management Systems
All Relational Database Management Systems and the databases they manage have the same common elements. These elements include Tables, Keys, Relationships, and SQL.
Tables
Imagine a webpage that has a series of HTML tables. One table has a list of product categories - things like Plumbing, Electrical, Hardware, and Sporting Goods. In the table the first column has a number for each category and the second column has the names of the categories. The second table has a list of Products that would be available in those categories. The first column has a unique number for each product. The second column has the name of the Product. The third column has a price for the product and the fourth column has the number for the department where the products belong.
| id | department |
|---|---|
| 1 | Electrical |
| 2 | Hardware |
| 3 | Plumbing |
| 4 | Sporting Goods |
| id | product | price | department id |
|---|---|---|---|
| 1 | 14 gauge copper wire - 100ft spool | 25.00 | 1 |
| 2 | Electrical tape - 3-pack | 8.50 | 1 |
| 3 | 16oz Hammer | 25.99 | 2 |
| 4 | 2" PVC elbow joing | 3.49 | 3 |
| 5 | Football | 32.99 | 4 |
| 6 | Basketball | 34.99 | 4 |
Keys
In RDBMSs, keys are used for identify specific rows in a table and to connect that row with other rows in other tables.
In our example above the department id in the first column of the first table is the columns containing the table's primary key. The primary key is a unique identifier for each row of a table and is contains a value that is unique throughout the entire table.
The fourth column of the second table is know as the foreign key. A foreign key, shares the value with primary key of a row in another table. Together the foreign key and the primary key create a relationship.
Relationship
The most important element of relational databases is that the tables of a given database have a defined relationship. These defined relationship are not meant to be arbitrary connections, but ones that occur naturally in the data.
Returning to our example above, the relationship between the two tables can be described like this:
- A genre can have many movies
- Each movie must belong to one genre
This is a nature connection or relationship of the data and is defined in the tables by using keys. In the section above, we describe how the first column of the first table and fourth column of the second table share the same values, that is because the primary key and the foreign key connect the two tables together.
Relationship Types
The relationship between the two tables as describe above is what is known as a one-to-many relationship. This is that type of relationship where on one side a piece of data from one row of a table can belong to multiple rows of another table (a genre can have many movies). Where as on the other side a row a table can only belong to one row of another table (each movie must belong to one genre). The one-to-many relationship is the most common relationship found in relational databases, but there are others.
There is the many-to-many relationship. Imagine if the rules of the example above was changed like so that a product could belong to many departments. This would create a many-to-many relationship between the two tables: a department can have many products and a production can belong to many departments. Now, some RDBMSs do not allow direct many-to-many relationship, and therefor a linking table will need to be created.
| product id | department id |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
| 6 | 4 |
The purpose of a linking table is to create an indirect many-to-many relationship by creating two one-to-many relationships. This technique is a common practice of creating linking tables is common when working with relational databases.
The final relationship type is the one-to-one relationship. The relationship is extremely rare, and in most case unnecessary, as most one-to-one relationships should be combined to create a single table.
Structured Query Language
The Structured Query Language (SQL) is the common scripting language for all relational databases. SQL is simple language with surprisingly few commands, and use to add, remove, read, or update data in a database.
We will learn more about SQL later in this course.