Databases
This video is excerpt from Lynda.com.
Watch
It is strongly encouraged that you watch the entire course Progamming Foundations: Databases on Lynda.com.
What is data
Before we can talk about databases, we first must talk about data. What is data? Data can be any facts or information related to any object or topic. Data can come in many different forms depending on what the data is describing. If the data is describing a person, it may consist of information like name, age, height and weight. While the data for a web site may consist of text, links, images and videos.
Now, data by itself is raw and random. Data really only becomes useful when it is collected, structured and organized. There are many ways and tools to organize data, and a database is only one of those ways.
For example, data for a web site can be organized using HTML, XML, JSON, or CSV, just to name a few. With all these ways to manage data, why even bother with databases? To answer that question, we must first look at what is a database?
What is a database
A database is an organized collection of data. the that is strucsimply a file or collection of files that hold information.
Why use a Database
A database is merely a tools for organizing data. But just because you have data does not mean you need to use a database, even working with PHP.
There are many ways to store data that would make it accessible to PHP. For example, you can store database in a spreadsheet saved as a CSV (Comma Separated Values) file or you can save data in a web friendly data format like XML or JSON.
However, using a database gives certain advantages that we won't get with these other methods including:
- Better scalability
- Easier data management
- Better accuracy
- Better security
- Better data integrity
It is these advantages that make databases so popular and widely used. But, it is not the database itself that makes this all possible, the DBMS that puts these rules place and helps manage the database. Of course, now the next question is, what is a DBMS?
What is a DBMS
A database management system (DBMS) is a program that knows how to open database files and manage the contents of the tables. A DBMS can manage multiple databases.
There a many, many different DBMSs out on the market today, but fortunately, they can be grouped together in broad categories or types.
What are the types of DBMSs
Relational
Relational Database Management Systems (RDBMS) are the most popular type of database management systems today. Some types of RDBMSs include: MySQL, Oracle, SQL Server, and SQLite. This type of system requires predefined schemas and enforces strict rules to follow the table structure. This focus on structure is what makes RDBMSs reliable with high levels of data integrity. A database of Relational Database Management Systems is made up of tables. These tables have a predefined structure and share a formal relationship with one another.
All RDBMSs use a version of the Structured Query Language (SQL).
NoSQL
While Relational Database Management Systems are the most popular system today, a group of type of database systems, commonly known as NoSQL database systems, are gaining in popularity, especially with the rise of Social Media. Some types of NoSQL database management systems include: MongoDB, CouchDB, and Redis.
Data doesn't always fit neatly in a predefined mold. With NoSQL data can be stored in many different ways: it can be column-oriented, document-oriented, graph-based or organized as a KeyValue store. This allows for data to be organized without a structured schema.
What are the elements of RDBMs
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 department can have many products
- Each product must belong to one department
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 department can have many products). Where as on the other side a row a table can only belong to one row of another table (each product must belong to one department). 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 most 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.