Home arrow MySQL arrow Page 2 - An Introduction to Database Normalization

Preliminary Definitions - MySQL

A database can be great fun, right? Yes, of course!There are though, a couple things that can ruin allthat hard work and effort you put into your efficient little database. Today we discuss how to keep that beloved bin of data from going bad on you: databasenormalization.

TABLE OF CONTENTS:
  1. An Introduction to Database Normalization
  2. Preliminary Definitions
  3. So Why Normalize?
  4. The Three Normal Forms
  5. What's Next
By: W.J. Gilmore
Rating: starstarstarstarstar / 234
November 27, 2000

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
In this section I introduce several definitions that are common jargon in the world of database administration and normalization.

entity: The word ‘entity’ as it relates to databases can simply be defined as the general name for the information that is to be stored within a single table. For example, if I were interested in storing information about the school’s students, then ‘student’ would be the entity. The student entity would likely be composed of several pieces of information, for example: student identification number, name, and email address. These pieces of information are better known as attributes.

primary key: A primary key uniquely identifies a row of data found within a table. Referring to the school system, the student identification number would be the primary key for the student table since an ID would uniquely identify each student.
Note that a primary key might not necessarily correspond to one specific attribute. In fact, it could be the result of a combination of several components of the entity. For example, while a location could not be a primary key for a class, since there might be several classes held there throughout the day, the combined time and location would make a satisfactory primary key, since no two classes could be held at the same time in the same location. When multiple attributes are used to derive a primary key, this key is known as a concatenated primary key.

relationship: Understanding of the various relationships both between the data items forming the various entities and between the entities themselves forms the crux of database normalization. There are three types of data relationships that you should be aware of:

  • one-to-one (1:1) - A one-to-one relationship signifies that each instance of a given entity relates to exactly one instance of another entity. For example, each student would have exactly one grade record, and each grade record would be specific to one student.

  • one-to-many (1:M) - A one-to-many relationship signifies that each instance of a given entity relates to one or more instances of another entity. For example, one professor entity could be found teaching several classes, and each class could in turn be mapped to one professor.

  • many-to-many (M:N) - A many-to-many relationship signifies that many instances of a given entity relate to many instances of another entity. To illustrate, a schedule could be comprised of many classes, and a class could be found within many schedules.

    foreign key: A foreign key forms the basis of a 1:M relationship between two tables. The foreign key can be found within the M table, and maps to the primary key found in the 1 table. To illustrate, the primary key in the professor table (probably a unique identification number) would be introduced as the foreign key within the classes entity, since it would be necessary to map a particular professor to several classes.

    Entity-relationship diagram (ERD): An ERD is essentially a graphical representation of the database structure. These diagrams, regardless of whether they are built using the latest design software or scrawled on a napkin with a crayon, are immensely useful towards attaining a better understanding of the dynamics of the various database relationships. Click here to examine a sample ERD diagram which illustrates the relational structure that might be found in our school system database.



     
     
    >>> 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: