Home arrow MySQL arrow Page 3 - An Introduction to Database Normalization (part 2)

Querying the MySQL database - MySQL

Last week we introduced you to the basics of creating an efficientdatabase table structure. After today's concluding article of this series you'll be able to create some of your own MySQL-powered applications, or use this syntax as the basis for learning to create normalized databases on your database server of choice.

TABLE OF CONTENTS:
  1. An Introduction to Database Normalization (part 2)
  2. The Project
  3. Querying the MySQL database
  4. Conclusion
By: W.J. Gilmore
Rating: starstarstarstarstar / 9
December 05, 2000

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
As you likely already know, interaction with an SQL database takes place through queries. A query is nothing more than a request for information from a database, this request being a phrase constructed from various keywords and table / column references. In this section, I’ll demonstrate using queries how database normalization makes an administrator’s life considerably easier. To begin, assume that the news table has been filled with this information.

Using this information, assume I execute the following query:

mysql>SELECT n.title, a.name FROM news n, administrators a WHERE
->n.admin_id = a.admin_id AND a.admin_id = 2;

Resulting in:

titlename
Nuevo Sitio: www.ziobudda.netMichel


Knowing that the admin_id ‘2’ maps to ‘Michel’, we can build our query without worrying that Michel will be misspelled. Furthermore, if the administrator later decides to changes his name to just the initials ‘M.M.’, the query will not need to be changed because the criteria is based upon an admin_id and not a name. Additionally, this name change will not require the consumption of potentially magnanimous amounts of resources, as would be the case if the name were included along with each row of the news table.

Considering another example, consider the query:

mysql>SELECT n.title FROM news n, categories c WHERE 
->n.category_id = c.category_id AND c.category_id = 2;

This yields:

titlename
Nuovo Sito: www.phpitalia.comNew Sites
Nuevo Sitio: www.ziobudda.netNew Sites


Now suppose that I want to update one of the category names found in the categories table, in particular I want to change the name ‘New Sites’ to ‘Great Sites’. All that I need to do is update one row found in the categories table:
mysql>update categories set name = ‘Great Sites’ 
->where category_id = ‘2’;

Again, executing the previous SELECT query would result in:

titlename
Nuovo Sito: www.phpitalia.comGreat Sites
Nuevo Sitio: www.ziobudda.netGreat Sites


Of course, your Web application is not likely to repeat the category name, but this will certainly make a difference when using dynamically-named table headers.



 
 
>>> More MySQL Articles          >>> More By W.J. Gilmore
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: