PART SQL Essentials II IIII …. In This Part Chapter 8 Command Line Interface (CLI) Chapter 9 SQL According to MySQL Chapter 10 Databases and Data ….
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision ecommerce web hosting services
130 Part I . Getting Started Note Since databases are practically living things, changes often are required in the life cycle. Using DDL statements like alter table, you can keep the database useful as business rules and processes change. Summary Data is really anything you can imagine and quantify. Though it s not just information in a computer or within a database, Relational Database Management Systems (of which MySQL is an example) make large amounts of data efficiently retrievable and usable. . A database is a group of organized information, not necessarily on a computer. . The database life cycle includes analysis, logical design, physical design, and implementation of the database. . Entities, Attributes, and Relationships are the objects that make up a database design. . An Entity-Relationship Diagram (ERD) models the layout of the entities, attributes, and relationships. The ERD can be transformed easily into normalized form and then into the physical layout of the database. . Normalization is the process of reducing redundancy and waste in a database while improving performance and possibly removing errors. . Indexing helps a database to quickly retrieve information. . The actual implementation of the database onto the server is the first step in the final phase of the database life cycle. Maintaining and administering the database server is an ongoing process throughout the lifetime of the database. …
Note: In case you are looking for affordable webhost to host and run your web application check Vision http web server services
Chapter 7 . Database Concepts and Design 129 Implementation The final step in the database life cycle is implementation, which includes: . Creation of the physical database and tables on the server . Ongoing administration and maintenance of the database Physically creating the tables for a normalized database design on the database server is simple. The actual writing of the SQL for table creation was done during the Logical Design. Now it is a matter of executing that code on the server. To create the ecommerce example database, I use the mysqladmin tool, as shown in Figure 7-16. I m highlighting this command inside a whole figure to show that it is normal for the command to return no message when it succeeds. If you have an error, mysqladmin will let you know. Cross- Reference Syntax for mysqladmin is contained in Chapter 8. Figure 7-16: The mysqladmin command returns no message upon successful completion of a database creation. Once the database is created, you can create the tables within the database based upon the SQL you wrote during the logical design phase. You re ready to enter the DDL to create the tables into the MySQL CLI to create the tables. Congratulations, the database is complete! However, the database life cycle isn t over. You now have to maintain the database server by . Making sure queries are operating as efficiently as possible . Checking table health and repairing tables when needed . Actively applying bug fixes and security audits.
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision ecommerce web hosting services
128 Part I . Getting Started Figure 7-14: The primary key index on the locale table assists MySQL in performing this query. To confirm the existence of the primary key index on the locale table, the show index command can be used (see Figure 7-15). Figure 7-15: The show index command gives useful information about indexes. MySQL allows you to create indexes in a number of ways (even while creating the table itself), via the alter tablecommand or the create index command. Cross- Reference Indexing and other optimizations for MySQL are discussed in Chapter 12.
Note: If you are looking for cheap and reliable webhost to host and run your web application check Vision coldfusion web hosting services
Chapter 7 . Database Concepts and Design 127 For a more complicated query (shown in Figure 7-14), MySQL believes that it could use a primary key index in the locale table to boost efficiency. Since the Zipcolumn is already a primary key, the query can run efficiently. Figure 7-12: Notepad can be used as a source for creating long DDL and DML commands. Figure 7-13: Indexing usually doesn t assist with simple queries.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision web and email hosting services
126 Part I . Getting Started Tip On the CD-ROM I write table definitions in a text editor such as viin Linux or Notepad in Windows, as shown in Figures 7-11 and 7-12. By using a text editor to create long DDL and DML commands, you can easily copy and paste them into the MySQL CLI or typing them in through the command line. This can be a lifesaver if you run into problems with the database server (or if your syntax or spelling is wrong for a particular command). The example database tables are on the CD-ROM so you don t have to type them (though it is good practice). Figure 7-11: Using vi to write DDL and DML can be a great timesaver. For the indexing example, I can add a record into the locale and customertables. insert into locale values ( 54481 , Stevens Point , WI ); insert into customer values ( suehring.ngermen.com , Steve , Suehring , 834 Main St. ,NULL, 54481 , 715 , 5551212 ); Indexing will usually not help a simple SELECT statement such as select * from locale. To confirm this, you can try the EXPLAIN command, as shown in Figure 7-13. In Figure 7-13, you can see information on the select statement and extra information about how MySQL ran the query. For determining possible indices, the important place to look is the possible_keys column; it tells you what columns could be created as an index to improve the performance of the query. For the example, MySQL did not need any indices to efficiently handle the query (as evidenced by the NULLin the primary_keyscolumn).
Note: In case you are looking for affordable and reliable webhost to host and run your business application check Vision ftp web hosting services
Chapter 7 . Database Concepts and Design 125 The Physical Design phase is the third step in the database life cycle. Now is the time to decide how many servers you are going to use for your database application, where they will be physically located, and so on. The final step after Physical Design planning is to take the SQL statements and create the database. Since you ve already designed the SQL for the normalized tables, it is now a matter of simply executing the code to make the database and tables. Cross-There are a number of ways to import data into MySQL, many of which are cov- Reference ered in another chapter. Indexing Much like an index entry in a book, an index entry in a database allows the server to quickly locate information. As you create the physical design of your database, you should choose indices that accelerate queries and thus accelerate the operation of your database server and applications. With MySQL, an index can be created either during the initial DDL to create the table or during later operation. An example of creating the index with the initial DDL is as follows: CREATE TABLE example ( id int NOT NULL, name varchar(50), address varchar(50), zip varchar(10), INDEX (id, name) ); To specify an index after the initial table has been created, the syntax is generally intuitive: CREATE INDEX index_name on table_name ( column1, column2, … ); MySQL has commands that can help you determine what indices to create. The DESCRIBE command allows you to describe a SELECT statement as well, to see possible keys or indices to create. Another name for the DESCRIBE command in this context is the EXPLAIN command. Some database operators may be familiar with one or the other of these commands; their functions overlap somewhat. For the example, I ve created a test database called ecommercethat uses the same example tables as the rest of the chapter.
Note: If you are looking for reliable webhost to maintain and run your java application check Vision java hosting services
124 Part I . Getting Started ); CREATE TABLE cardtype ( card_id int NOT NULL PRIMARY KEY, card_type varchar(20) ); CREATE TABLE locale ( zip varchar(10) NOT NULL PRIMARY KEY, city varchar(50), state char(2) ); CREATE TABLE manufacturer ( id int NOT NULL PRIMARY KEY, name varchar(50), address varchar(50), zip varchar(10), area_code char(3), telephone_number char(7), contact_name varchar(50) ); CREATE TABLE product ( id int NOT NULL PRIMARY KEY, name varchar(50), price decimal(9,2), quantity integer, manu_id int ); CREATE TABLE producttype ( id int NOT NULL PRIMARY KEY, category varchar(50) ); The varchar column type is chosen because it uses space more efficiently than the char type. The charcolumn type pads the column with extra spaces to fill the length of the column; the varchartype adds only one extra byte to store the length of the column. For example, if char the column type for the city column, the database would always use 50 bytes, regardless of whether the city was named Ames or Stevens Point. The char type is fine for area-code and telephone-number columns (which are always the same length for U.S. phone numbers). The decimal column type is chosen for the product table s price column because the precision can be specified before and after the decimal point. In the example, I use the (9,2) definition that calls for up to 9 digits before the decimal and 2 digits after the decimal. Therefore I can list prices of up to 999,999,999.99 for products in the database. Cross- Reference For a summary of MySQL s column types, see Appendix A. Physical Design and Implementation Once you have a normalized design, turning that design into the actual database and tables on the server is easy.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision web and email hosting services
Chapter 7 . Database Concepts and Design 123 Cross-Refer to Appendix A for a complete listing of column types supported by MySQL. Reference Recalling Figure 7-10 s candidate tables, I recommend using integer type for the ID columns in all tables. Further, I would recommend column types of varcharfor the name columns. For constraints, the ID column in all tables should be required and unique. This makes them primary keys. Note To create a primary key in MySQL, you must specify the column as NOT NULL and PRIMARY KEY. The SQL to create the example tables in MySQL is as follows: CREATE TABLE person ( person_id int NOT NULL PRIMARY KEY, person varchar(50) ); CREATE TABLE location ( loc_id int NOT NULL PRIMARY KEY, location varchar(50) ); CREATE TABLE team ( team_id int NOT NULL PRIMARY KEY, team varchar(50) ); CREATE TABLE table4 ( tperson_id int NOT NULL PRIMARY KEY, tloc_id int, tteam_id int ); Going back to my ongoing example of an e-commerce Web site, the candidate tables for the structure are created by the following code: CREATE TABLE customer ( e-mail_address varchar(75) NOT NULL PRIMARY KEY, first_name varchar(50), last_name varchar(50), address1 varchar(50), address2 varchar(50), customer_zip varchar(10), area_code char(3), telephone_number char(7) ); CREATE TABLE cardinfo ( card_id int NOT NULL PRIMARY KEY, ccnum varchar(16), ccexp date, name_on_card varchar(100), e-mail_address varchar(75)
Note: If you are looking for high quality webhost to host and run your jsp application check Vision jsp web hosting services
122 Part I . Getting Started Person Person ID Person Name 3 Don Sutton 4 Steve Suehring 5 Greg Maddux 6 Jerry Rice 7 Mika Hakkinen 8 Steve Young Location Team Loc_ID Location Team ID Team Name 355 Baseball Hall of Fame 95 Braves 515 Bat Boy 78 Dodgers 400 NFL Hall of Fame 94 49ers 411 FormulaOne 99 Mclaren Table4 Person ID Loc_ID Team ID 3 355 95 4 515 78 5 355 95 6 400 94 7 411 99 8 400 94 Figure 7-10: Final example tables, normalized Data Definition Language and Data Markup Language Data Definition Language (DDL) and Data Markup Language (DML) are common names for the tools and syntax used to create and work with a database. . DDL defines how the data is addressed. DDL examples include CREATETABLE, CREATE INDEX, and such privilege options as GRANT and REVOKE. Note DDL statements do not work with data directly. . DML statements actually work with real data. Examples of such statements are INSERT and DELETE. Producing the SQL The last step of Logical Design is creation of the SQL for the database. As this point, you must make some important decisions involving column types and constraints for the candidate tables. There are a number of column types supported by MySQL.
Note: In case you are looking for affordable and reliable webhost to host and run your business application check Vision ftp web hosting services