Database
Content
database
A database is a collection of data. That may sound overly simplistic but it pretty much sums up what any database is. A database could be as simple as a text file with a list of names. Or it could be as complex as a large, relational database management system, complete with in-built tools to help you maintain the data. Before we get into dedicated database management systems, let's start with the basics - let's look at a simple text file example. Text File Imagine we have a text file called "Individual.txt", and that the contents look like this:
We could use this information to do things such as send an email to everyone on our list. We could do this because, due to the way we designed the list, we know that each row contains a different individual, and the information on that row is related to that individual. Also, the items in each row are separated by commas. Therefore, we know that the email address
next to "Homer" is his email address. We could also call each row a record. Therefore, we currently have 4 records in our database. With a small list like this, a text file may serve our purposes perfectly. Spreadsheet Another option would be to store it in a spreadsheet using spreadsheet software (for example, Microsoft Excel). That way, we could do some extra things with our list (such as format it, or sort by first name/surname etc). A spreadsheet program like Excel makes these tasks relatively easy to do. Also, programs like Excel organize the data into rows and columns, making your data easier to comprehend. Something like this:
Database Software A better option would be to store the data in a database table using specialized database software, such as Microsoft Access. Something like this:
So What's the Difference? You may be wondering what the difference is between the last two examples (Excel vs Access). After all, both examples have the data organized into rows and columns. There are many differences between spreadsheet software and database software. The rest of this tutorial will show you why database software is a much better option for creating databases.
Database Management Systems
A Database Management System (DBMS), is a software program that enables the creation and management of databases. Generally, these databases will be more complex than the text file/spreadsheet example in the previous lesson. In fact, most of today's database systems are referred to as a Relational Database Management System (RDBMS), because of their ability to store related data across multiple tables. Some of the more popular relational database management systems include:
• • • • •
Microsoft Access Filemaker Microsoft SQL Server MySQL Oracle
Throughout this tutorial, you will become familiar with some of the key concepts of database management systems. These include:
• • • • •
Database creation Tables Adding data to your database Querying a database Relational database design
What Does a Database Management System Look Like?
Different database management systems look different, but generally, there are a number of common features that you'll usually see across most of them. Microsoft Access
This is the main screen you'll see when opening up Access to view an existing database. The outer part is the database management system and it's menu, the middle part is the actual database. In this example, the database is called "dateSite" and has 20 tables. If you were to open a different database, the name of the database would be different and you would see different tables, but the available options would be the same (i.e. Tables, Queries, Forms, Reports, Macros, Modules, Open, Design, New). Some of these options are common across all database management systems. All database systems allow you to create tables, build queries, design a new database, and open an existing database.
Microsoft SQL Server
Microsoft SQL Server is a more robust database management system than Access. While Access is better suited to home and small office use, SQL Server is more suited to enterprise applications such as corporate CRMs and websites etc. The above screen is what you see when you open SQL Server through Enterprise Manager. Enterprise Manager is a built-in tool for managing SQL Server and its databases. In this example, there are 6 databases. Each
database is represented down the left pane, and also in the main pane (with a "database" icon). Which Database System to Use? If you are using a database for home or small office use, Microsoft Access or Filemaker should be fine. If you need to create a database driven website, then you're better off using a more robust system such as SQL Server, Oracle, or MySQL. The examples in this tutorial use Microsoft Access. If you don't have Microsoft Access, you should still be able to follow the examples. The tasks we perform are the same tasks you would need to perform regardless of which database management system you use. The key goal with this tutorial is to provide you with an overview of what is involved in creating and maintaining a database.
Creating a Database
With database management systems, many tasks can be done either via programatically or a user interface. Creating databases is no exception. Option 1: Programatically Many database administrators (DBAs) use Structured Query Language (SQL) to perform many of their database tasks. To enter SQL, you need to open an interface that allows you to enter your code. For example, if you use SQL Server, you would normally use Query Analyzer.
The following example is the basic code for creating a new database. Parameters can be added to this example if your requirements are more specific. CREATE DATABASE MyDatabase Note: This example assumes you know how to use your database system to run scripts like this. If you don't you, will probably find it easier to use the user interface method (below). Option 2: User Interface Most database systems make it very easy to create a database via a user interface. Generally, it's just a matter of selecting an option from a menu, then providing a name for your database. The following examples demonstrate how to create a database in Microsoft Access. 1. From the "File" menu, click on "New Database":
2. Choose "Blank Database". (MS Access also gives you the ability to choose from a template, but we'll just use a blank database here):
3. Choose a location to save the database:
Your New Database Once you've completed the above tasks, you should see a blank database, like this:
We know this database is blank because it doesn't have any tables. If it did, you would see these tables in the middle pane of the table tab. Now that we have our blank database, we can start adding some tables
About Database Tables
Database tables will most likely be the area you'll become most familiar with after working with databases for a while. Now, before we go ahead and start adding tables to our new database, let's have a look at what a database table actually is. What is a Table?
In database terms, a table is responsible for storing data in the database. Database tables consist of rows and columns. In the following example, the second row is highlighted in black:
In the next example, the second column is highlighted in black. This column has been given a name of "FirstName":
A row contains each record in the table, and the column is responsible for defining the type of data that goes into each cell. Therefore, if we need to add a new person to our table, we would create a new row with the person's details. OK, now lets go ahead and create a table.
Creating Database Tables
With database management systems, you need to create your tables before you can enter data. Just as you can create a database programatically, you can create your tables programatically too. Option 1: Programatically
The following is an example of creating a new table. Note that we are specifying the name of the table, the name of each column, and the data type of each column. More parameters can be added to this example if your requirements are more specific. CREATE TABLE Individual (IndividualId int, FirstName Varchar(255), LastName Varchar(255), DateCreated dateTime ) Option 2: User Interface Database management systems usually have a "Design View" for creating tables. Design view enables you to create the names of each column, specify the type of data that can go into each column, as well as specifying any other restrictions you'd like to enforce. Restricting the data type for each column is very important and helps maintain data integrity. For example, it can prevent us from accidentally entering an email address into a field for storing the current date. More parameters can be added against each column if you require them. For example, you could specify a default value to be used (in case the field has been left blank by the user). When you create a table via the user interface (or design view), depending on which database system you use, you should see something like this:
Once you've created your table in "design view", you can switch to "datasheet view" to see the resulting table. You should see something like this:
OK, so this is a blank table - it doesn't have any data yet. What we have is a table that contains the columns required before we can enter any data. So, now that we have a blank table, let's look at how to add data.
Adding Data to a Database
There are a number of ways you can enter data into a database table. The method you choose will largely depend on your context. You will need to choose from the following methods:
• • •
Direct entry Form Structured Query Language (SQL)
•
Website or other application
Here's an explanation of those methods. Direct entry You can type directly into the table while it's in Data Sheet view. Initially, this may seem like the quickest and easiest method, but it's not suitable if you have lots of data, and/or if non-technical users need to enter data.
Form If you use a desktop database program (such as MS Access), you can set up a form, so that nontechnical users can enter data into the form. Once they submit the form, the data is automatically inserted into our table. The form could insert data into multiple tables too - saving you from having to open up each table to manually insert the data.
Most enterprise database systems don't have the ability for setting up a form. This is probably because they're designed for larger scale applications with hundreds, thousands, or even millions of users. In this environment, a form would be created through other means (for example, using HTML). Applications such as Access provide a form wizard, which steps you through the process to building a form. Structured Query Language (SQL) You can use a programming language called SQL to insert the data (we could also have used SQL to create the database and tables if we'd wanted to). One advantage of this is that you can save your SQL script for re-use. This could be handy if you need to insert the data into multiple databases. It's also useful to create scripts that insert "lookup" data - this is generally a base set of data that never changes (such as Countries, Cities, etc). If you ever need to rebuild your database, you can simply run your ready made script against it (which saves you from manually re-entering the data). Website or other application You could build a program that uses the database to store and retrieve data. The person entering the data doesn't need to have direct access to the database. They don't even need to have database software. By using SQL, your database could be part of a larger application - such as a website. This is probably the most common method of adding data to a database. If you've ever registered with a website, your details would have been inserted into a database using this method.
Querying a Database
Queries are one of the things that make databases so powerful. A "query" refers to the action of retrieving data from your database. Usually, you will be selective with how much data you want returned. If you have a lot of data in your database, you probably don't want to see everything. More likely, you'll only want to see data that fits a certain criteria. For example, you might only want to see how many individuals in your database live in a given city. Or you might only want to see which individuals have registered with your database within a given time period. As with many other tasks, you can query a database either programatically or via a user interface. Option 1: Programatically The way to retrieve data from your database with SQL is to use the "SELECT" statement. Using the SELECT statement, you can retrieve all records... SELECT * FROM Individual ...or just some of the records: SELECT * FROM Individual WHERE FirstName = 'Homer' The 2nd query only returns records where the value in the "FirstName" column equals "Homer". Therefore, if only one individual in our database had the name "Homer", that person's record would be shown. Something like this:
SQL is a powerful language and the above statement is very simple. You can use SQL to choose which columns you want to display, you could add further criteria, and you can even query multiple tables at the same time. If you're interested in learning more about SQL, be sure to check out our SQL tutorial after you've finished this one! Option 2: User Interface You might find the user interface easier to generate your queries, especially if they are complex. Database management systems usually offer a "design view" for your queries. Design view enables you to pick and choose which columns you want to display and what criteria you'd like to use to filter the data. Here's an example of design view in Microsoft Access:
When using design view, the database system actually uses SQL (behind the scenes) to generate the
Relational Database Design
Most popular database management systems are relational systems, and are usually referred to as Relational Database Management Systems (RDBMS). What this means is that their databases can contain multiple tables, some (or all) of which are related to each other. For example, consider the following screenshot:
In this example, the database has 20 tables. Each table serves a specific purpose. This enables us to organize our data much better. It can also help us with the integrity of our data. Using the example above, the Individual table can hold data that is strictly about the individual. The City table can hold a list of all cities. If we want to know which city each individual lives, we could store a "pointer" in the Individual table to that city in the City table.
The above example demonstrates the relationship between the Individual table and the City table. The individuals in the "Individual" table live in cities that are defined in the "City" table. Therefore, we can cross-reference each "Individual" record with a "City" record. How Does This Work? Firstly, in the City table, each record has a unique identifier. A unique identifier is a value that is unique to each record. This identifier can be as simple as an incrementing number. So, in our City table, the first record has a number of 1, the second record has a number of 2, and so on. Secondly, when entering each individual into the Individual table, instead of writing out the full city name in that table, we only need to add the city's unique identifier. In this case, the unique identifier is a number, so we enter this number into the "CityId" column of the "Individual" table. The following screenshots demonstrate this:
So, by looking at both tables, we can determine that Homer lives in Sydney, Barney lives in Cairns, and both Ozzy and Fred live in Osaka. At this stage, nobody in our database lives in Queenstown or Dunedin. Primary Keys and Foreign Keys Primary keys and foreign keys are terms that you will become very familiar with when designing databases. These terms describe what role each of the columns play in their relationship with each other.
The column that contains the unique identifier is referred to as the Primary Key. So, in our City table, the primary key is the CityId column. A foreign key is the column in the other table that points to the primary key. Therefore, the CityId column in the Individual table is a foreign key to the CityId column in the City table.
Database Driven Website
A database driven website is a website that has most of its webpage content in a database. Therefore, the website content isn't actually sitting in files on the server, it is sitting in tables and columns in a database. A website with its content stored on the file system is often referred to as a static website, whereas a database driven website is often referred to as a dynamic. Content Management Systems A website with dynamic content usually has a CMS (Content Management System) to assist the content providers in updating the website. A CMS is usually provided in the form of an administration area where content providers need to log in before they can add content. Once logged in, they can create, update and delete articles. They may be able to upload files such as Word documents, PDF files etc. They might be able to upload images too. All of this content can be stored in the database. Some may be stored on the file system too though. For example, although documents and images can be
stored in the database, there are sometimes reasons to store them on the file system. Performance is often a key reason. Database size is another. Discussion Forums and Blogs Discussion forums and blogs have become a popular feature for many websites. Most, if not all, forums and blogs are database driven. Users can register their details, then add content. When the user clicks the "Submit" button, their details/content is inserted into the database. Then when someone decides to view this content, it is read from the database using SQL (Structured Query Language). Combination of Static and Dynamic Some websites have a combination of static content and dynamic content. There could be any number of reasons for this. Often, smaller websites will be static. There's little need to configure a database just to store a handful of webpages - much easier and cheaper to keep them as files on the server. Even websites like this might contain some added functionality such as a discussion forum, or a blog. In this case, the discussion forum or blog will need its content stored in a database. Benefits of a Database Driven Website Database driven websites can provide much more functionality than a static site can. Extended functionality could include:
•
Enabling many (potentially non-technical) users to provide content for the website. Users can publish articles on the website without needing to FTP them to a web server.
•
Shopping cart
•
You can provide advanced search functionality that enables users to filter the results based on a given field. They can then sort those results by a field - say "Price" or "Date".
• •
Customized homepage You can allow your users to perform tasks such as registering for a newsletter, post questions to your forums, provide comments on a blog, update their profile, etc.
•
Integration with corporate applications such as CRM systems, HR systems etc
•
Much more
Creating a Database Driven Website The most common tasks for database driven websites is inserting, updating, and deleting data. Some of these are the same tasks that you learned in this tutorial, however when using a database driven website, you need to use a different method to do these tasks. You need to use a programming language called SQL (Structured Query Language) to insert, update, and delete your data. Don't worry, this is not as scary as it may sound. SQL is a very easy language to learn and, once you start using it, you will be thankful you took the time to learn it. In fact, you've already learned some basic SQL statements in previous lessons. To create a database driven website, you need the following skills:
•
You need to be able to build a static website HTML, and preferrably CSS and JavaScript
•
You need to be able to write basic code using a server side scripting language such as PHP, ColdFusion etc
• •
You need to know how to write basic SQL You need to be able to design/build a database. If you need to learn about databases but missed the start of this tutorial, here it is :)
If you're familiar with PHP or ColdFusion, check out how to connect to and query a database in each of these technologies.
• •
Querying a database using ColdFusion Querying a database using PHP
Database Summary
So, you've made it to the last page of this database tutorial... Well done! You should now have a general understanding about databases and how they're used. This tutorial was intended for beginners trying to gain an understanding of databases. Databases are not like most other files and require a little bit of thought in order for you to understand the concept. If you have your own database management system installed, you should have a better understanding of where to start and what the various options mean. What Next? Most of the examples in this tutorial used Microsoft Access. If you'd like to learn how to use Microsoft Access, try the Microsoft Access Tutorial. Also, you'll have noticed SQL coming up throughout this tutorial. SQL is a very powerful language, but is also very easy to learn. You can achieve a lot even by learning just a little SQL. If you're interested in learning more about
SQL, check out the SQL tutorial. Once you complete this tutorial, you will be able to do things such as:
• • • • • • • •
Select only the columns you want from a query Query multiple tables Create databases programatically Create tables programatically Query multiple tables Use built-in functions Create an index And more...
Sounds good? Learn SQL now!
A database is a collection of data. That may sound overly simplistic but it pretty much sums up what any database is. A database could be as simple as a text file with a list of names. Or it could be as complex as a large, relational database management system, complete with in-built tools to help you maintain the data. Before we get into dedicated database management systems, let's start with the basics - let's look at a simple text file example. Text File Imagine we have a text file called "Individual.txt", and that the contents look like this:
We could use this information to do things such as send an email to everyone on our list. We could do this because, due to the way we designed the list, we know that each row contains a different individual, and the information on that row is related to that individual. Also, the items in each row are separated by commas. Therefore, we know that the email address
next to "Homer" is his email address. We could also call each row a record. Therefore, we currently have 4 records in our database. With a small list like this, a text file may serve our purposes perfectly. Spreadsheet Another option would be to store it in a spreadsheet using spreadsheet software (for example, Microsoft Excel). That way, we could do some extra things with our list (such as format it, or sort by first name/surname etc). A spreadsheet program like Excel makes these tasks relatively easy to do. Also, programs like Excel organize the data into rows and columns, making your data easier to comprehend. Something like this:
Database Software A better option would be to store the data in a database table using specialized database software, such as Microsoft Access. Something like this:
So What's the Difference? You may be wondering what the difference is between the last two examples (Excel vs Access). After all, both examples have the data organized into rows and columns. There are many differences between spreadsheet software and database software. The rest of this tutorial will show you why database software is a much better option for creating databases.
Database Management Systems
A Database Management System (DBMS), is a software program that enables the creation and management of databases. Generally, these databases will be more complex than the text file/spreadsheet example in the previous lesson. In fact, most of today's database systems are referred to as a Relational Database Management System (RDBMS), because of their ability to store related data across multiple tables. Some of the more popular relational database management systems include:
• • • • •
Microsoft Access Filemaker Microsoft SQL Server MySQL Oracle
Throughout this tutorial, you will become familiar with some of the key concepts of database management systems. These include:
• • • • •
Database creation Tables Adding data to your database Querying a database Relational database design
What Does a Database Management System Look Like?
Different database management systems look different, but generally, there are a number of common features that you'll usually see across most of them. Microsoft Access
This is the main screen you'll see when opening up Access to view an existing database. The outer part is the database management system and it's menu, the middle part is the actual database. In this example, the database is called "dateSite" and has 20 tables. If you were to open a different database, the name of the database would be different and you would see different tables, but the available options would be the same (i.e. Tables, Queries, Forms, Reports, Macros, Modules, Open, Design, New). Some of these options are common across all database management systems. All database systems allow you to create tables, build queries, design a new database, and open an existing database.
Microsoft SQL Server
Microsoft SQL Server is a more robust database management system than Access. While Access is better suited to home and small office use, SQL Server is more suited to enterprise applications such as corporate CRMs and websites etc. The above screen is what you see when you open SQL Server through Enterprise Manager. Enterprise Manager is a built-in tool for managing SQL Server and its databases. In this example, there are 6 databases. Each
database is represented down the left pane, and also in the main pane (with a "database" icon). Which Database System to Use? If you are using a database for home or small office use, Microsoft Access or Filemaker should be fine. If you need to create a database driven website, then you're better off using a more robust system such as SQL Server, Oracle, or MySQL. The examples in this tutorial use Microsoft Access. If you don't have Microsoft Access, you should still be able to follow the examples. The tasks we perform are the same tasks you would need to perform regardless of which database management system you use. The key goal with this tutorial is to provide you with an overview of what is involved in creating and maintaining a database.
Creating a Database
With database management systems, many tasks can be done either via programatically or a user interface. Creating databases is no exception. Option 1: Programatically Many database administrators (DBAs) use Structured Query Language (SQL) to perform many of their database tasks. To enter SQL, you need to open an interface that allows you to enter your code. For example, if you use SQL Server, you would normally use Query Analyzer.
The following example is the basic code for creating a new database. Parameters can be added to this example if your requirements are more specific. CREATE DATABASE MyDatabase Note: This example assumes you know how to use your database system to run scripts like this. If you don't you, will probably find it easier to use the user interface method (below). Option 2: User Interface Most database systems make it very easy to create a database via a user interface. Generally, it's just a matter of selecting an option from a menu, then providing a name for your database. The following examples demonstrate how to create a database in Microsoft Access. 1. From the "File" menu, click on "New Database":
2. Choose "Blank Database". (MS Access also gives you the ability to choose from a template, but we'll just use a blank database here):
3. Choose a location to save the database:
Your New Database Once you've completed the above tasks, you should see a blank database, like this:
We know this database is blank because it doesn't have any tables. If it did, you would see these tables in the middle pane of the table tab. Now that we have our blank database, we can start adding some tables
About Database Tables
Database tables will most likely be the area you'll become most familiar with after working with databases for a while. Now, before we go ahead and start adding tables to our new database, let's have a look at what a database table actually is. What is a Table?
In database terms, a table is responsible for storing data in the database. Database tables consist of rows and columns. In the following example, the second row is highlighted in black:
In the next example, the second column is highlighted in black. This column has been given a name of "FirstName":
A row contains each record in the table, and the column is responsible for defining the type of data that goes into each cell. Therefore, if we need to add a new person to our table, we would create a new row with the person's details. OK, now lets go ahead and create a table.
Creating Database Tables
With database management systems, you need to create your tables before you can enter data. Just as you can create a database programatically, you can create your tables programatically too. Option 1: Programatically
The following is an example of creating a new table. Note that we are specifying the name of the table, the name of each column, and the data type of each column. More parameters can be added to this example if your requirements are more specific. CREATE TABLE Individual (IndividualId int, FirstName Varchar(255), LastName Varchar(255), DateCreated dateTime ) Option 2: User Interface Database management systems usually have a "Design View" for creating tables. Design view enables you to create the names of each column, specify the type of data that can go into each column, as well as specifying any other restrictions you'd like to enforce. Restricting the data type for each column is very important and helps maintain data integrity. For example, it can prevent us from accidentally entering an email address into a field for storing the current date. More parameters can be added against each column if you require them. For example, you could specify a default value to be used (in case the field has been left blank by the user). When you create a table via the user interface (or design view), depending on which database system you use, you should see something like this:
Once you've created your table in "design view", you can switch to "datasheet view" to see the resulting table. You should see something like this:
OK, so this is a blank table - it doesn't have any data yet. What we have is a table that contains the columns required before we can enter any data. So, now that we have a blank table, let's look at how to add data.
Adding Data to a Database
There are a number of ways you can enter data into a database table. The method you choose will largely depend on your context. You will need to choose from the following methods:
• • •
Direct entry Form Structured Query Language (SQL)
•
Website or other application
Here's an explanation of those methods. Direct entry You can type directly into the table while it's in Data Sheet view. Initially, this may seem like the quickest and easiest method, but it's not suitable if you have lots of data, and/or if non-technical users need to enter data.
Form If you use a desktop database program (such as MS Access), you can set up a form, so that nontechnical users can enter data into the form. Once they submit the form, the data is automatically inserted into our table. The form could insert data into multiple tables too - saving you from having to open up each table to manually insert the data.
Most enterprise database systems don't have the ability for setting up a form. This is probably because they're designed for larger scale applications with hundreds, thousands, or even millions of users. In this environment, a form would be created through other means (for example, using HTML). Applications such as Access provide a form wizard, which steps you through the process to building a form. Structured Query Language (SQL) You can use a programming language called SQL to insert the data (we could also have used SQL to create the database and tables if we'd wanted to). One advantage of this is that you can save your SQL script for re-use. This could be handy if you need to insert the data into multiple databases. It's also useful to create scripts that insert "lookup" data - this is generally a base set of data that never changes (such as Countries, Cities, etc). If you ever need to rebuild your database, you can simply run your ready made script against it (which saves you from manually re-entering the data). Website or other application You could build a program that uses the database to store and retrieve data. The person entering the data doesn't need to have direct access to the database. They don't even need to have database software. By using SQL, your database could be part of a larger application - such as a website. This is probably the most common method of adding data to a database. If you've ever registered with a website, your details would have been inserted into a database using this method.
Querying a Database
Queries are one of the things that make databases so powerful. A "query" refers to the action of retrieving data from your database. Usually, you will be selective with how much data you want returned. If you have a lot of data in your database, you probably don't want to see everything. More likely, you'll only want to see data that fits a certain criteria. For example, you might only want to see how many individuals in your database live in a given city. Or you might only want to see which individuals have registered with your database within a given time period. As with many other tasks, you can query a database either programatically or via a user interface. Option 1: Programatically The way to retrieve data from your database with SQL is to use the "SELECT" statement. Using the SELECT statement, you can retrieve all records... SELECT * FROM Individual ...or just some of the records: SELECT * FROM Individual WHERE FirstName = 'Homer' The 2nd query only returns records where the value in the "FirstName" column equals "Homer". Therefore, if only one individual in our database had the name "Homer", that person's record would be shown. Something like this:
SQL is a powerful language and the above statement is very simple. You can use SQL to choose which columns you want to display, you could add further criteria, and you can even query multiple tables at the same time. If you're interested in learning more about SQL, be sure to check out our SQL tutorial after you've finished this one! Option 2: User Interface You might find the user interface easier to generate your queries, especially if they are complex. Database management systems usually offer a "design view" for your queries. Design view enables you to pick and choose which columns you want to display and what criteria you'd like to use to filter the data. Here's an example of design view in Microsoft Access:
When using design view, the database system actually uses SQL (behind the scenes) to generate the
Relational Database Design
Most popular database management systems are relational systems, and are usually referred to as Relational Database Management Systems (RDBMS). What this means is that their databases can contain multiple tables, some (or all) of which are related to each other. For example, consider the following screenshot:
In this example, the database has 20 tables. Each table serves a specific purpose. This enables us to organize our data much better. It can also help us with the integrity of our data. Using the example above, the Individual table can hold data that is strictly about the individual. The City table can hold a list of all cities. If we want to know which city each individual lives, we could store a "pointer" in the Individual table to that city in the City table.
The above example demonstrates the relationship between the Individual table and the City table. The individuals in the "Individual" table live in cities that are defined in the "City" table. Therefore, we can cross-reference each "Individual" record with a "City" record. How Does This Work? Firstly, in the City table, each record has a unique identifier. A unique identifier is a value that is unique to each record. This identifier can be as simple as an incrementing number. So, in our City table, the first record has a number of 1, the second record has a number of 2, and so on. Secondly, when entering each individual into the Individual table, instead of writing out the full city name in that table, we only need to add the city's unique identifier. In this case, the unique identifier is a number, so we enter this number into the "CityId" column of the "Individual" table. The following screenshots demonstrate this:
So, by looking at both tables, we can determine that Homer lives in Sydney, Barney lives in Cairns, and both Ozzy and Fred live in Osaka. At this stage, nobody in our database lives in Queenstown or Dunedin. Primary Keys and Foreign Keys Primary keys and foreign keys are terms that you will become very familiar with when designing databases. These terms describe what role each of the columns play in their relationship with each other.
The column that contains the unique identifier is referred to as the Primary Key. So, in our City table, the primary key is the CityId column. A foreign key is the column in the other table that points to the primary key. Therefore, the CityId column in the Individual table is a foreign key to the CityId column in the City table.
Database Driven Website
A database driven website is a website that has most of its webpage content in a database. Therefore, the website content isn't actually sitting in files on the server, it is sitting in tables and columns in a database. A website with its content stored on the file system is often referred to as a static website, whereas a database driven website is often referred to as a dynamic. Content Management Systems A website with dynamic content usually has a CMS (Content Management System) to assist the content providers in updating the website. A CMS is usually provided in the form of an administration area where content providers need to log in before they can add content. Once logged in, they can create, update and delete articles. They may be able to upload files such as Word documents, PDF files etc. They might be able to upload images too. All of this content can be stored in the database. Some may be stored on the file system too though. For example, although documents and images can be
stored in the database, there are sometimes reasons to store them on the file system. Performance is often a key reason. Database size is another. Discussion Forums and Blogs Discussion forums and blogs have become a popular feature for many websites. Most, if not all, forums and blogs are database driven. Users can register their details, then add content. When the user clicks the "Submit" button, their details/content is inserted into the database. Then when someone decides to view this content, it is read from the database using SQL (Structured Query Language). Combination of Static and Dynamic Some websites have a combination of static content and dynamic content. There could be any number of reasons for this. Often, smaller websites will be static. There's little need to configure a database just to store a handful of webpages - much easier and cheaper to keep them as files on the server. Even websites like this might contain some added functionality such as a discussion forum, or a blog. In this case, the discussion forum or blog will need its content stored in a database. Benefits of a Database Driven Website Database driven websites can provide much more functionality than a static site can. Extended functionality could include:
•
Enabling many (potentially non-technical) users to provide content for the website. Users can publish articles on the website without needing to FTP them to a web server.
•
Shopping cart
•
You can provide advanced search functionality that enables users to filter the results based on a given field. They can then sort those results by a field - say "Price" or "Date".
• •
Customized homepage You can allow your users to perform tasks such as registering for a newsletter, post questions to your forums, provide comments on a blog, update their profile, etc.
•
Integration with corporate applications such as CRM systems, HR systems etc
•
Much more
Creating a Database Driven Website The most common tasks for database driven websites is inserting, updating, and deleting data. Some of these are the same tasks that you learned in this tutorial, however when using a database driven website, you need to use a different method to do these tasks. You need to use a programming language called SQL (Structured Query Language) to insert, update, and delete your data. Don't worry, this is not as scary as it may sound. SQL is a very easy language to learn and, once you start using it, you will be thankful you took the time to learn it. In fact, you've already learned some basic SQL statements in previous lessons. To create a database driven website, you need the following skills:
•
You need to be able to build a static website HTML, and preferrably CSS and JavaScript
•
You need to be able to write basic code using a server side scripting language such as PHP, ColdFusion etc
• •
You need to know how to write basic SQL You need to be able to design/build a database. If you need to learn about databases but missed the start of this tutorial, here it is :)
If you're familiar with PHP or ColdFusion, check out how to connect to and query a database in each of these technologies.
• •
Querying a database using ColdFusion Querying a database using PHP
Database Summary
So, you've made it to the last page of this database tutorial... Well done! You should now have a general understanding about databases and how they're used. This tutorial was intended for beginners trying to gain an understanding of databases. Databases are not like most other files and require a little bit of thought in order for you to understand the concept. If you have your own database management system installed, you should have a better understanding of where to start and what the various options mean. What Next? Most of the examples in this tutorial used Microsoft Access. If you'd like to learn how to use Microsoft Access, try the Microsoft Access Tutorial. Also, you'll have noticed SQL coming up throughout this tutorial. SQL is a very powerful language, but is also very easy to learn. You can achieve a lot even by learning just a little SQL. If you're interested in learning more about
SQL, check out the SQL tutorial. Once you complete this tutorial, you will be able to do things such as:
• • • • • • • •
Select only the columns you want from a query Query multiple tables Create databases programatically Create tables programatically Query multiple tables Use built-in functions Create an index And more...
Sounds good? Learn SQL now!
