MySQL, Java, JSP, Servlet, SSH, Tomcat Web Hosting Blog

Benefits of Normalization Effectively minimizing redundancy is another way of describing removal of duplication. The effect of removing duplication is as follows: . Physical space needed to store data is reduced. . Data becomes better organized. . Normalization allows changes to small amounts of data (namely single records) to be made to one table at once. In other words, a single table record is updated when a specific item is added, changed, or removed from the database. You don t have to search through an entire database to change a single field value in a single record, just the table. Potential Normalization Hazards There are potential problems in taking this redundancy minimization process too far. Some detailed aspects of the positive effects of normalization mentioned previously can have negative side effects, and sometimes even backfire, depending on the application focus of the database. Performance is always a problem with too much granularity caused by over-application of normalization. Very demanding concurrency OLTP databases can be very adversely affected by too much granularity. Data warehouses often require non-technical end-user access and over-granularity tends to make table structure more technically oriented to the point of being impossible to interpret by end-users. Keep the following in mind: . Physical space is not nearly as big a concern as it used to be, because disk space is one of the cheapest cost factors to consider (unless, of course, when dealing with a truly huge data warehouse). . Too much minimization of redundancy implies too much granularity and too many tables. Too many tables can lead to extremely huge SQL join queries. The more tables in a SQL join query, the slower queries execute. Performance can be so drastically affected as to make applications completely useless. . Better organization of data with extreme amounts of redundancy minimization can actually result in more complexity, particularly if end-users are exposed to database model structure. The deeper the level of normalization, the more mathematical the model becomes, making the model techie-friendly and thus very user-unfriendly. Who is accessing the database, end-users or OLTP applications? Tables are connected to each other with relationships. Examine what a relationship is and how it can be represented. Representing Relationships in an ERD Tables can have various types of relationships between them. The different types of inter-table relationships that can be formed between different tables can be best described as displayed in Entity Relationship Diagrams (ERDs). 49 Database Modeling Building Blocks
Note: If you are looking for best quality webspace to host and run your tomcat application check Vision tomcat hosting services

. NOT NULL This is the simplest of field level constraints, making sure that a value must always be entered into a field when a record is added or changed. . Validation check Similar to a NOT NULL constraint, a validation checking type of constraint restricts values in fields when a record is added or changed in a table. A check validation constraint can be as simple as making sure a field allowing only M for Male or F for Female, will only ever contain those two possible values. Otherwise, check validation constraints can become fairly complex in some relational databases, perhaps allowing inclusion of user written functions running SQL scripting. . Keys Key constraints include primary keys, foreign keys, and unique keys. All these key types are discussed briefly later on in this chapter and further in later chapters in this book. Key constraints allow the checking and validation of values between fields in different tables. Primary and foreign keys are essentially the implementation of relationships between parent and child tables. Those relationships or relations are the source of the term relational database. Some relational databases allow constraints to be specified at both the field level or for an entire table as a whole, depending on the type of constraint. Understanding Relations for Normalization By dictionary definition, the term normalization means to make normal in terms of causing something to conform to a standard, or to introduce consistency with respect to style and content. In terms of relational database modeling, that consistency becomes a process of removing duplication in data, among other factors. Removal of duplication tends to minimize redundancy. Minimization of redundancy implies getting rid of unneeded data present in particular places, or tables. In reality, normalization usually manages to divide information into smaller, more manageable parts, preferably not too small. The most obvious redundancies can usually be removed without getting too deeply mathematical about everything. Commercially speaking, primary objectives are usually to save space and organize data for usability and manageability, without sacrificing performance. All this is often a juggling act and commonly partially ironed out by trial and error. Additionally the demands of intensely busy applications and end-user needs can tend to necessitate breaking the rules of normalization in many ways to meet performance requirements. Rules are usually broken simply by not applying every possible layer of normalization. Normal Forms beyond 3rd Normal Form are often ignored and sometimes even 3rd Normal Form itself is discounted. Normalization can be described as being one of introduction of granularity, removal of duplication, or minimizing of redundancy, or simply the introduction of tables, all of which place data into a better organized state. Normalization is an incremental process. In other words, each Normal Form layer adds to whatever Normal Forms have already been applied. For example, 2nd Normal Form can only be applied to tables in 1st Normal Form, and 3rd Normal Form only applied to tables in 2nd Normal Form, and so on. Each Normal Form is a refinement of the previous Normal Form. Similarly 3rd Normal cannot be applied to tables in 4th Normal Form because by definition tables in 4th Normal Form are cumulatively already in 3rd Normal Form. 48 Chapter 3
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision ecommerce web hosting services

and numbers. Storage issues can become problematic. Relational databases use many different types of underlying disk storage techniques to make the management of records in tables more efficient. A typical record in a table may occupy at most 2 KB (sometimes known as a page or block), and often much less. Even the smallest of graphic objects used in Web site applications easily exceeds the size of a record and each record in a table could have a unique graphic object. Therefore, storing a graphic object with each record in the underlying operating system block structure completely ruins any kind of specialized storage structure performance tuned for simple table record strings and numbers storage. Binary objects were created to physically separate binary values from traditional table record values. The obvious extension to this concept was creation of binary objects to store anything in binary format, reducing storage, even items such as large strings, sound files, video, XML documents . . . the list goes on. . Reference pointers In the C programming language, a reference pointer is a variable containing an address on disk or in memory of whatever the programmer wants to point at. A pointer provides the advantage of not having to specify too much in advance with respect to how many bytes the pointer value occupies. Some relational databases allow the use of pointers where a pointer points to an object or file stored externally to the database, pointing from a field within a table, to the object stored outside the database. Only the address of the externally stored object is stored in the table field. This minimizes structural storage effects on relational tables as often is the result of storing binary objects in table records. Pointers are generally used for pointing to static binary objects. A static object does not change very often. . Collection arrays Some relational databases allow creation of what an object database would call a collection. A collection is a set of values repeated structurally (values are not necessarily the same) where the array is contained within another object, and can only be referenced from that object. In the case of a relational database, the containment factor is the collection being a field in the table. Collection arrays can have storage structures defined in alternative locations to table fields as for binary objects, but do not have to be as such. Collection arrays, much like program arrays, can be either fixed length or dynamic. A dynamic array is a variable-length array, and is actually a pointer. When using a fixed-length array, the programmer must specify the length of the array before using it. . User-defined types Some relational databases allow programmable or even on-the-fly creation of user-defined types. A user-defined type allows the creation of new types. Creation of a new type means that user-defined datatypes can be created by programmers, even using other user-defined types. It follows that fields can be created in tables where those fields have user-defined datatypes. Specialized Datatypes Specialized datatypes take into account datatypes that are intended for contained complex data objects. These specialized datatypes allow types with contained inherent structure (such as XML documents, spatial data, and multimedia objects). Constraints and Validation Relational databases allow constraints, which restrict values that are allowed to be stored in table fields. Some relational databases allow the minimum of constraints necessary to define a database as being a relational database. Some relational databases allow other constraints in addition to the basics. In general, constraints are used to restrict values in tables, make validation checks on one or more fields in a table, or even check values between fields in different tables. Following are some examples of constraints: 47 Database Modeling Building Blocks
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision ecommerce web hosting services

Figure 3-9: Dates with timestamps and dates without timestamps. Complex Datatypes Complex datatypes encompass object datatypes. Available object datatypes vary for different relational databases. Some relational databases provide more object-relational attributes and functionality than others. Complex datatypes include any datatypes breaching the object-relational database divide including items such as binary objects, reference pointers, collection arrays and even the capacity to create user defined types. Following are some complex datatypes: . Binary objects Purely binary objects were created in relational databases to help separate binary type data from regular relational database table record structures. A large object such as a graphic is so very much larger than the length of an average table record containing all strings SQL> select isbn, print_date AS Printed, 2 to_char(print_date, DD/MM/YYYY HH24:MI:SS ) AS TimeStamp 3 from edition where print_date is not null; ISBN PRINTED TIMESTAMP 893402095 345308999 345336275 5557076654 5553673224 246118318 345334787 449208133 345323440 345333926 425130215 31-MAY-79 28-FEB-83 31-JUL-86 31-JAN-51 31-JAN-20 28-APR-83 31-DEC-85 31-MAY-85 31-JUL-96 30-NOV-90 30-NOV-91 31/05/1979 00:12:01 28/02/1983 04:55:03 31/07/1986 03:44:33 31/01/1951 09:41:00 31/01/2020 22:15:20 28/04/1983 10:17:10 31/12/1985 08:13:45 31/05/1985 00:01:12 31/07/1996 03:00:30 30/11/1990 21:04:40 30/11/1991 16:43:53 Database specific format Timestamp format 46 Chapter 3
Note: If you are looking for cheap webhost to host and run your apache application check Vision apache web hosting services

. Floating points A floating-point number is just as the name implies, where the decimal point floats freely anywhere within the number. In other words, the decimal point can appear anywhere in the number. Floating-point values can have any number of digits both before and after the decimal point, even none on either side. Values such as 1000, 1000.12345, and 0.8843343223 are valid floating-point numbers. Floating point values are likely to be less efficient in storage and retrieval than fixed-length decimals and integers because they are less predictable in terms of record length and otherwise. Figure 3-7 shows a 32-byte length floating-point datatype and a relatively unlimited length floating point datatype. For most databases, any INSERT commands into the float fields exceeding length requirements (such as 32 bytes for SFLT) will not produce errors because values added will likely be truncated and converted to exponential (scientific) notation when too large or too small, as shown in Figure 3-8. Figure 3-8: Adding values to floating-point datatype fields. . Dates and times Dates can be stored as simple dates or dates including timestamp information. In actuality, simple dates are often stored as a Julian date or some other similar numbering system. A Julian date is a time in seconds from a specified start date (such as January 1, 1960). When simple date values are set or retrieved in the database, they are subjected to a default formatting process spitting out to, for example, a dd/mm/yyyy format excluding seconds (depending on default database formatting settings, of course). A timestamp datatype displays both date and time information regardless of any default date formatting executing in the database (sometimes stored as a special timestamp datatype). Figure 3-9 shows the difference between dates with timestamps and dates without timestamps. insert into numbers(sflt) values(5.2234); SQL> select sflt from numbers; SFLT 5 60000 4.0000E+20 .000005 insert into numbers(sflt) values(55444); insert into numbers(sflt) values(449998234590782340895); insert into numbers(sflt) values(0.0000049998234590782340895); 45 Database Modeling Building Blocks
Note: If you are looking for cheap webhost to host and run your apache application check Vision apache web hosting services

. Numbers Numeric datatypes are often the most numerous field datatypes in many database tables. The following different numeric datatype formats are common: . Integers An integer is a whole number such that no decimal digits are included. Some databases allow more detailed specification using small integers and long integers, as well and standard-sized integer datatypes. Figure 3-7 shows three whole number integer datatypes in the fields SINT, INT, and LONGINT. SINT represents a small integer, INT an integer, and LONGINT a long integer datatype. Figure 3-7: Integer, decimal, and floating-point numeric datatypes. . Fixed-length decimals Afixed-length decimal is a number, including a decimal point, where the digits on the right side of the decimal are restricted to a specified number of digits. For example, a DECIMAL(5,2) datatype will allow the values 4.1 and 4.12, but not 4.123. However, the value 4.123 might be automatically truncated or rounded to 4.12, depending on the database engine. More specifically, depending on the database engine the value to the left of the decimal, the number 5 in DECIMAL(5,2), can be interpreted in different ways. In some databases the value 5 limits the number of digits to the left of the decimal point; in other databases, 5 limits the total length of the number, and that total length may include or exclude the decimal point. Thus, in some databases, DECIMAL(5,2) would allow 12345.67 and some databases 12345.67 would not be allowed because the entire number contains too many digits. So, the value 5 can specify the length of the entire number, or only the digits to the left of the decimal point. In Figure 3-7 assume that DECIMAL(5,2) implies 2 decimals with total digits of 5 at most, excluding the decimal point. So in Figure 3-7 the fields DECIMALS5_2 and DECIMALS3_0 allow fixed length decimal values. All INSERT commands adding values to the two DECIMALS5_2 and DECIMALS3_0 fields will fail because decimal length, or overall field length specifications for the datatypes have not been adhered to. create table numbers( SQL> desc numbers Name Type SINT SMALLINT INT INTEGER LONGINT LONG FLT FLOAT(126) SFLT FLOAT(2) WHOLE INTEGER DECIMALS5_2 DECIMAL(5,2) DECIMALS3_0 DECIMAL(3,0) sint smallint ,int integer ,longint long ,flt float ,sflt float(2) ,whole number ,decimals5_2 number(5,2) ,decimals3_0 number(3)); insert into numbers(decimals5_2) values(1234567); insert into numbers(decimals5_2) values(1234.567); insert into numbers(decimals3_0) values(1234.567); 44 Chapter 3
Note: In case you are looking for affordable and reliable webhost to host and run your business application check Vision ftp web hosting services

Figure 3-6: Fixed-length strings and variable-length strings. . Variable-length strings A variable-length string allows storage into a datatype as the actual length of the string, as long as a maximum limit is not exceeded. The length of the string is variable because when storing a string of length less than the width specified by the datatype, the string is not padded (as is the case for fixed-length strings). Only the actual string value is stored. Storing the string XXX into a variable length string datatype of ten characters in length stores the three characters only, and not three characters padded by seven spaces. Different databases use different naming conventions for variable-length string datatypes. VARCHAR(n) or TEXT(n) are common naming formats for variable-length strings. Figure 3-6 shows variable-length strings on country names (COUNTRY), returning only the names of the countries and no padding out to maximum length as for fixed-length strings. DM returned as DM COUNTRY is variable length returning only the value in the column SQL> select country|| , ||fxcode|| , ||currency Gambia, GMD, Dalasi Ghana, GHC, Cedis Guinea, GNF, Francs Guatemala, GTQ, Quetzales Germany, DM , Deutsche Marks Guyana, GYD, Dollars COUNTRY|| , ||FXCODE|| , ||CURRENCY 2 from country 3 where fxcode is not null 4 and country like G% ; FXCODE is a fixed length, 3 character string 43 Database Modeling Building Blocks
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

Whereas fields apply structure to records, datatypes apply structure and restrictions to fields and values in those fields. Datatypes There are many different types of datatypes, which vary often more in name than anything else with respect to different database engines. This section describes all different variations of datatypes, but without targeting any specific vendor database engine. Datatypes can be divided into three separate sections: . Simple datatypes These are datatypes applying a pattern or value limitation on a single value such as a number. . Complex datatypes These include any datatypes bridging the gap between object and relational databases, including items such as binary objects and collection arrays. Specifics on complex datatypes are not strictly necessary for this book as they are more object-oriented than relational in nature. . Specialized datatypes These are present in more advanced relational databases catering to inherently structured data such as XML documents, spatial data, multimedia objects and even dynamically definable datatypes. Simple Datatypes Simple datatypes include basic validation and formatting requirements placed on to individual values. This includes the following: . Strings A string is a sequence of one or more characters. Strings can be fixed-length strings or variable-length strings: . Fixed-length strings Afixed-length string will always store the specified length declared for the datatype. The value is padded with spaces even when the actual string value is less than the length of the datatype length. For example, the value NY in a CHAR(3) variable would be stored as NY plus a space character. Fixed-length strings are generally only used for short length strings because a variable-length string (discussed next) requires storage of both value and length. Fixed-length strings are also more efficient for ensuring fixed record lengths of key values. Figure 3-6 shows an FXCODE field representing a foreign exchange currency code, always returning three characters even when the currency code is less than three characters in length. Acase in point is the defunct currency code DM (Deutsche Marks, German currency), returning DM plus a space character, yielding a total of three characters. 42 Chapter 3
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision make web site services

Figure 3-5: The vertical structure of a table showing fields, constraints and datatypes. Examine Figure 3-5 again. The left column shows the name of fields in the table. This particular table is used to contain separate editions of the same book. Separate editions of a single book can each be published by different publishers. Thus, the PUBLISHER_ID field is included in the EDITION table. Note various other fields such as the PUBLICATION_ID field - uniquely identifying a unique book name, which is stored in the PUBLICATION table. The PUBLICATION table represents each book uniquely regardless of the existence of multiple editions of the same title or not. The International Standard Book Number (ISBN) uniquely identifies a book on an international basis, and is distinct for each edition of a book. Also note the datatypes shown in Figure 3-5. Datatypes are shown as INTEGER, DATE, or VARCHAR(32). These three field types restrict values to be of certain content and format. INTEGER only allows whole numbers, all characters consisting of digits between 0 and 9, with no decimal point character. DATE only allows date entries where specific formatting may apply. Most databases will have a default format for date values. If the default format is set to dd/mm/yyyy, an attempt to set a date value to 12/31/2004 will cause an error because the day and month values are reversed. Effectively, datatypes can constrain values in fields in a similar way to that of the previously specified NOT NULL constraint does. Similar to constraints, datatypes can restrict values, so datatypes are also a form of field value constraining functionality. Name ISBN PUBLISHER_ID PUBLISCATION_ID PRINT_DATE PAGES LIST_PRICE FORMAT RANK INGRAM_UNITS Null? NOT NULL NOT NULL NOT NULL Type INTEGER INTEGER INTEGER DATE INTEGER INTEGER VARCHAR (32) INTEGER INTERGER Column names NULL constraint Datatype 41 Database Modeling Building Blocks
Note: In case you are looking for affordable webhost to host and run your web application check Vision http web server services

Figure 3 -4: Records repeat table field structure. So far, this chapter has examined tables, plus the fields and records within those tables. The next step is to examine relationships between tables. Fields, Columns and Attributes The terms field, column, and attribute all mean the same thing. They are all terms used to describe a field in a table. A field applies structure and definition to a chunk of data within each repeated record. Data is not actually repeated on every record, but the structure of fields is applied to each record. So, data on each record can be different, both for the record as a whole, and for each field value. Note the use of the term can be rather than is, implying that there can be duplication across both fields and records, depending on requirements and constraints. A constraint constrains (restricts) a value. For example, in Figure 3-5 the second box showing NOT NULL for the first three fields specifies that the ISBN, PUBLISHER_ID, and PUBLICATION_ID fields can never contain NULL values in any record. Rows repeat the column structure of the table 1585670081 345333926 345336275 345438353 553293362 553298398 553293389 553293370 893402095 345323440 345334787 345308999 5553673224 5557076654 246118318 449208133 425130215 James Blish Larry Niven Isaac Azimov James Blish Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Larry Niven Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Isaac Azimov Larry Niven Kurt Vonnegut Overlook Press Ballantine Books Ballantine Books Ballantine Books Bantam Books Spectra Spectra Spectra L P Books Del Rey Books Del Rey Books Del Rey Books Books on Tape Books on Tape HarperCollins Publishing Fawcett Books Berkley Publishing Cities in Flight Ringworld Foundation A Case of Conscience Second Foundation Prelude to Foundation Foundation s Edge Foundation and Empire Foundation Footfall Foundation Foundation Foundation Foundation Foundation Lucifer s Hammer Hocus Pocus Science Fiction ISBN AUTHOR PUBLISHER TITLE GENRE PRINTED Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Science Fiction Modern American 30-Nov-90 31-Jul-86 31-May-79 31-Jul-96 31-Dec-85 28-Feb-83 31-Jan-20 31-Jan-51 28-Apr-83 31-May-85 30-Nov-91 40 Chapter 3
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision ecommerce web hosting services