|
[ Pobierz całość w formacie PDF ]
.However, Jani Smith has only onetelephone number, so we leave the columns Telephone2 and Telephone3 empty.Unfortunately, our friend Bill Simpson is one of those characters with a hometelephone, a business telephone, a cell phone, a pager, and a phone just for mes-sages.Since our table handles only three telephone numbers, we need to add twomore columns just for Bill.Most people we add into the table won t have morethan three telephone numbers, so the vast majority of Telephone4 andTelephone5 columns will be empty.Of course, just when we limit the table to fivetelephone numbers, Bill will get a summer cabin with a telephone in it as well.Wecannot continue to add columns just to accommodate Bill s communicationneeds, especially when all of the added telephone columns will generally beempty.To solve this problem of multiple columns in the database, we apply rules asso-ciated with the First Normal Form.The First Normal Form is the first in aseries of optimizations that should be applied to a database to produce a highlyefficient system.The rules in First Normal Form are: % %Columns with similar content must be eliminated. % %A table must be created for each group of associated data. % %Each data record must be identifiable by means of a primary key. What Is a Database? 31It isn t necessary to apply all of these rules to achieve First Normal Form, but theyshould be attempted nevertheless.For our database, the first and third rules canbe applied.Rule two isn t valid for our data because all of the pieces of data areassociated with each other.Rule number 1 is the one that will make the mostdifference in the database.Here s our data after we ve applied rules 1 and 3:ID Name City State Telephone101 John Doe Chicago IL 217-333-3333102 John Doe Chicago IL 800-333-3333103 Jani Smith Atlanta GA 403-222-2223We won t include Bill in the example to keep it small.Notice how John Doe sinformation is being duplicated so we can handle additional telephone num-bers.If John Doe gets another telephone number, we just add a new record tothe table with duplicate name, city, and state values.The third rule doesn treally help with our telephone number problem, but in order for our table to bein First Normal Form, it needs to be applied.Second Normal FormOf course, all of this data duplication simply cannot be a good thing because itis clearly wasting space in the database.We can get some help with the dupli-cated data using Second Normal Form and its associated rules: % %If the contents of columns repeat, the table needs to be divided intomultiple tables. % %Multiple tables from rule 1 need to be linked by foreign keys or theirderivative.Since we have repeating data in the sample table, we apply rules 1 and 2 tocreate a second table just for the city, state, and telephone information.Forexample, the following table might be called the name table:ID Name101 John Doe102 Jani SmithThe telephone table would look like this:ID telephone_id city state telephone201 101 Chicago IL 217-333-3333202 101 Chicago IL 800-333-3333203 102 Atlanta GA 403-222-2223 32 Working with MySQL SQLWe now have two tables for all of our sample data.The first table, called name,holds just the name of our contact as well as an ID for each name in the table.There won t be any duplicate names in this table.The second table, called tele-phone, holds all of the contact information for each name in the name table.Of particular important in the telephone table is the use of the telephone_id col-umn.This column is considered a foreign key and links the name table to thetelephone table.The ID column in the name table is copied to each of the tele-phone table rows as appropriate.If we need to find each of the telephone num-bers for John Doe, we look up the ID in the row associated with John Doe.ThisID is used as a reference value in the name_id columns of each row in the tele-phone table.Those rows that have the same ID value are returned.The tele-phone number value can be pulled from each row and displayed.Third Normal FormThe last normal form we consider is called Third Formal Form and it is thegoal for most database designers.There is a single rule in this form: % %Columns that are not directly related to the primary key must be elimi-nated (that is, transplanted into a table of their own).In the table called telephone we created earlier, we have to examine the use ofthe telephone_id column and the data within the table itself.The Third NormalForm rule tells us that the city and state columns shouldn t be part of the tele-phone table because that data doesn t relate to the primary key of the table.This calls for a new table to hold the city and state information.For example,we might create a table called address to hold this information:ID address_id city state301 101 Chicago IL302 102 Atlanta GAWe ve provided a brief introduction to database design and the use of NormalForms to achieve a good design.There is, of course, much more to considerwhen designing databases, and we recommend you consult a good databasetheory book for additional information.Introducing MySQL SQLThe majority of this chapter concentrates on the specifics of the MySQL data-base and its representation of SQL.In this section, we examine the basics you Introducing MySQL SQL 33need to build databases and tables, populate the databases with data, andretrieve the data.Overview of MySQLMySQL is a DBMS designed as open source software.It is a relationship DBMSbecause it supports the idea of building multiple tables and linking those tablesusing columns within the tables.The application is considered open sourcebecause you can download the binaries of the system or the source code.The MySQL system is entry-level SQL92 compliant, and the developers areconstantly striving to expand their support of SQL92 as well as SQL99 whilemaintaining speed and efficiency.Some of the featured highlights include thefollowing:Speed and efficiency MySQL is written in C/C++ using the latest compil-ers on the various support platforms.The code is multithreaded and takesadvantage of kernel threads for extreme efficiency on systems with multipleCPUs.All of the code is highly optimized and makes us of B-trees, in-memory hash tables, and class libraries.Column types These include signed/unsigned integers 1, 2, 3, 4, and 8bytes long; FLOAT; DOUBLE; CHAR; VARCHAR; TEXT; BLOB; DATE;TIME; DATETIME; TIMESTAMP; YEAR; SET; and ENUM types.We demon-strate many of these column types throughout the book in code examples.A full-featured command set All of the standard SQL commands, suchas SELECT, INSET, DELETE, as well as JOINs, are supported.Supportincludes the SHOW command for obtaining information about the system.Aliases on table and columns are supported per SQL92.Functions A wide range of functions are available, including AVG(),SUM(), MAX(), and many others [ Pobierz całość w formacie PDF ] |
Archiwum
|