Home arrow MySQL arrow Page 3 - Introductory Database Theory: Systems And Design
MYSQL

Introductory Database Theory: Systems And Design


Before you can begin to design a database you must understand the underlying concepts and theories of why databases are used and how they are created. In this article, Steve gives us a great explanation of what a database is, the relational database model, metadata and indexes, the database management system and structured query language.

Author Info:
By: Steve Adcock
Rating: 4 stars4 stars4 stars4 stars4 stars / 38
January 19, 2002
TABLE OF CONTENTS:
  1. · Introductory Database Theory: Systems And Design
  2. · What is a database?
  3. · The relational model
  4. · Metadata and indexes
  5. · The DBMS and SQL
  6. · Conclusion

print this article
SEARCH DEVARTICLES

Introductory Database Theory: Systems And Design - The relational model
(Page 3 of 6 )

As databases advanced, so did common standards. The process of updating information in a database filled with repeating data eats terrible amounts of time. Consider the following example. We have a database of congressional laws, represented by Law 1, Law 2, etc. Along with the law's name, we will list the congressman who initially introduced the law, their phone number and their e-mail address. Here's what the table might look like:

Our sample congressmen database

Personally, I'd fire John Smith for his obvious lack of work, but that's beyond the point. Here, we have a basic table. You can see that two congressmen (Sam Jenkens and Adam Schroeder) initiated more than one law. This is a very simplistic table, and initially you may not see anything wrong with it.

Let's say a congressman moves houses and has his phone number changed. Instead of changing one record, three or four must be changed. In a corporate environment with thousands of records, this would take far too much time to complete, and the system's resources would take a major hit because of it. What if one especially eager congressman initiated five hundred new laws? Five hundred rows must be updated. This is just not practical.

The relational database model gives us an alternative by creating a link between two different tables. We can snatch the congressmen information and put it into a separate table and relate it to the laws table. The table shown above would work much better if it were designed like this:

An example of a relational database model

An example of a relational database model

This enables us to relate the two tables to each other by a primary key; in this case it's the Con_ID field of the first table. A primary key is simply a field that is used to set a relational yet unique reference to any record in a table. Since the second table also has a field that matches the primary key, we refer to it as the foreign Key.

Notice that the congressmen in the first table relate to which laws they initiated in the second table via the primary key, Con_ID? Now, we could setup an application that would link the two tables together and give us the same results we would have received from the first non-relational example model. What this also enables us to do is update each congressmen's phone numbers in one place and one place only, requiring much less system processing. On a much larger corporate scale, you can see how this type of database would save processing resources.

This is a very basic look at relational databases. For the purposes of this article, you should just remember that relational databases enable more than one table to be related (or connected) to each other for far superior organization and effectiveness when querying the database.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials