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

Figure 3-3: Raw data has structure applied to create structured data. Tables contain fields and records. Fields apply structure to records, whereas records duplicate field structure an indefinite number of times. Records, Rows, and Tuples The terms record, row, and tuple all mean the same thing. They are terms used to describe a record in a table. Figure 3-4 shows the structure of fields applied to each record entry in a table. There is really nothing to understand other than that a table can have multiple fields, whereas that set of fields can have many records created in that table, and data can subsequently be accessed according to the field structure of the table, record by record. Books contain relatively disorganized information 1585670081 Publisher publisher_id name 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 Author author_id name Subject subject_id print_id title Publication publication_id subject_id (FK) author_id (FK) title Review review_id publication_id (FK) review_id (FK) text Publisher coauthor_id (FK) publication_id (FK) Edition ISBN publisher_id (FK) publication_id (FK) print_date pages list_price format rank ingram_units Organize information using a database model Resulting in a neatly structured set of columns and rows of data 39 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-1: Table fields express the metadata (horizontal) dimension. Records are repeated over and over again in the vertical dimension, duplicating field structures from the horizontal dimension, as exaggerated in Figure 3-2. Figure 3-2: Table records duplicate the set of fields into the tuples or data (vertical) dimension. A table is effectively a structure containing fields across it in one dimension defining the structure of records repeatedly added to that table. In other words, all records in the same tables have the same field structure applied to them. Figure 3-3 shows a picture demonstrating a pile of books on the left, passed through a table structure represented by the miniature ERD in the center, resulting in the structured data set on the right, duplicated as the table records from Figure 3-2. ISBN AUTHOR PUBLISHER TITLE GENRE PRINTED 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 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 ISBN AUTHOR PUBLISHER TITLE GENRE PRINTED 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 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 38 Chapter 3
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision servlet hosting services

Information, Data and Data Integrity Information refers to knowledge or the way in which knowledge is communicated. Values in a database are made up of data, which is essentially information. Validity is determined by the integrity of data. The integrity of data is the correct form of data. The following list leads off the definitions of basic terms and concepts: . The concept of information Information is knowledge or the communication of knowledge. Knowledge is accumulated and derived by processes including those of experience, events, or static information (such as a set of statistical values). In computer jargon, information is data that is stored in a database, processed by programs, or even transmitted over a network such as the Internet (between multiple users). . The concept of data Data is composed of unique, specifically formatted items of information. Unique data item values are stored in slots in a database, processed as individual values by coded programs, and transmitted across networks of wires, or even communicated with electromagnetic signals to and from satellites (all over the world, and beyond). . The concept of a computer program Programs are sets of precise instructions, used to manipulate and process changes to a database. . The concept of a datatype Datatypes comprise the forms data can take, such as numbers, dates, strings, and others. . The concept of data integrity The integrity of data is the validity of data. Possible compromises to data integrity include human error at data entry, network transmission errors, software bugs and virus infections, hardware malfunction, disk errors, and natural disasters. Countering compromises to data integrity is mostly a pre-emptive process, rather than a re-active process. In other words, the best solution is to attempt to prevent data integrity loss. The most significant prevention mechanisms are database backups (regularly), computer security (in all forms), and properly designed interfaces restricting how data is entered by data entry users. Solving the problem after the fact often utilizes something called a parity check (such as when transmitting over a network), which is simply a check of something, of itself. Understanding the Basics of Tables In data model theory, a table is a bucket into which data is poured. The idea of the relational database model and normalization is that data in a specific table is directly associated with all other items in that same table that would be each field as exaggerated in Figure 3-1, pictured as the horizontal dimension. 37 Database Modeling Building Blocks
Note: If you are looking for reliable webhost to maintain and run your java application check Vision java hosting services

If you have all the details in this chapter committed to memory and well understood, you can skip it if you are really strapped for time. However, I do recommend you read this chapter in its entirety. There are concepts, ideas, and object aspects of data modeling described in this chapter that you will not find in most books of this nature. I have taken the liberty of expanding on the basic structure of the relational database model and adding little bits and pieces here and there such as materialized views and auto counter sequences. For example, even though materialized views are not a part of the normalization process used to create a relational database model, materialized views can have a most profound effect on the behavior of data warehouses, particularly in the area of performance. The process of normalization and the application of normal forms are covered in detail in Chapter 4. All you have to know at this stage is that normalization is the method or formula used to divide data up into separate tables according to a bunch of rules. So, when the term normalization is mentioned, simply assume it implies that new tables are being created or more streamlined versions of existing tables are being devised. Views are included in this chapter. A view is not the same thing as a materialized view, which are relegated to a final section covering specialized objects. Materialized views and auto counters are included. As a performance tuner, I don t believe in using views for anything other than security purposes, and I still wouldn t recommend even that practice. Views are often used by developers to prototype or speed up development. The result is usually drastically poor performance in production. I prefer not to promote the use of views in general. In addition, views can be used to get around or skirt poor database model design. Like I said, I prefer not to suggest use of views too strongly, by reason of past experience. This chapter describes all the pieces that compose the relational database model. All of the constituents of the relational database model help to create an organized logical structure for managing data in a database. The organized logical structure is a relational database model. In this chapter, you learn about the following: . Information, data, and data integrity . Tables . Fields, columns, and attributes . Rows, records, and tuples . Datatypes . Validation and NULL values . Relations, relationships, and some normalization . Entity Relationship Diagrams (ERDs) . Primary and foreign keys . Referential integrity . Indexes . Specialized objects (views and materialized views) So, arm in arm and onward we shall go! Let s begin with a conceptual perspective. 36 Chapter 3
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision make web site services

3 Database Modeling Building Blocks Well begun is half done. (Aristotle) Begin at the beginning by starting with the simple pieces. This chapter introduces the building blocks of the relational database model by discussing and explaining all the various parts and pieces making up a relational database model. For example, a table is probably the most important piece in the puzzle of the relational database model, where fields or fields in tables are perhaps of less significance but still essential to the semantics of the model as a whole. So far, this book has covered the historical evolution of database models, how different applications and end-user needs affect database type, the basics of the art of database design, plus various human factors influencing design. Before describing how a data model is built, you must know what all the pieces are. You need a basic knowledge of all the parts and pieces constituting the relational database model. This chapter describes all those parts and pieces needed for future chapters, which will cover the process of creating and refining relational database models. In a book such as this, the objective is to teach an understanding of the relational database model by whatever means necessary. Previous chapters have used approaches such as a history of database models to describe the benefits and reasons for the evolution of the relational database model, as it exists today. Various aspects of this book have already gone into some detail to describe some basic and fundamental concepts, with deliberate succinctness. At this point, this book could jump straight into the workings of the normalization process using normal forms. Normalization is used to granularize and organize data for use in a database. It is also assumed, having purchased this book, that the concept of a table in a relational database model is completely unfamiliar to you. So, I shall have to err on the side of caution and proceed to devote this entire chapter to describing all the various parts and pieces of what makes up a relational database model. You have to begin with the elements that make up the relational database model before proceeding to learn how to create relational database models.
Note: In case you are looking for affordable and reliable webhost to host and run your business application check Vision php5 hosting services

The benefit of converting something like a spreadsheet into a database model is that the spreadsheet is likely to be a lot less complex than a mainframe-based legacy network database or a paper-based system. The reason why is likely to be because the company keeps losing copies of the spreadsheet or spreadsheets. Loss can occur from bad hardware, but most likely they occur because of human error caused by accidental deletions or copying. People lose things all the time. Spreadsheet or any single-user based system, unless its something built in a legacy piece of software like dBase, is unlikely to present too much history; however, complexity is certainly a possibility. Sorting Out a Messed-up Database Sorting out a messed-up database implies that there is a relational database in existence, but that the database model is a complete mess. Expect to find invalid data, orphaned records, and other such wonderful problems. Once again, establish what is needed first before starting to go through it willy-nilly. After you establish what the records are supposed to look like, you might even find that there are only a few minor structural errors or relationship errors that can be easily repaired. Even though a task like this can seem daunting, it really only has two very distinct steps. First, establish and build the correct structure. If the company has decided that the existing structure is problematic, the company probably has plenty of ideas on how to fix it. The company probably also knows who can give you all the correct information. Second, copy data across to new tables, if necessary. Summary In this chapter, you learned about: . Business rules are partially built into a relational database model design . Talk to users to get the information you need . Abstraction requires management perspective . Assess the culture of in-house technical people to understand their perspective . Higher-level personnel in small companies are more accessible than those in large companies . Different types of people in different roles can give differing perspectives . Talk to the right people to get the right information in a specific topic area . Unfavorable scenarios are difficult conversion situations, but not necessarily as daunting as they seem The next chapter begins the discussion of the technical details of the relational database model itself by introducing all the various terms and concepts, the building blocks of the relational database model. 34 Chapter 2
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision make web site services

Converting Legacy Databases Converting legacy databases can often be the most difficult of tasks. Sometimes the databases are partially inaccessible or difficult to access at best. Sometimes the databases may even be in existence using network or even hierarchical database modeling techniques. These database model structures can be extremely large and complex and, therefore, very difficult to decipher. As with a paper system, find someone who knows all about the database to help you; otherwise, dig into the database yourself, allow plenty of time for analysis, and verify structure. In the worst case, analyze applications as well as the database model to confirm the operational functionality of the database and that it actually does what it should do. It could very well be the case that a new database model is required because the legacy database and software was incorrectly built in the first place, or that requirements have drastically changed since its inception. If things have changed, you should find someone or something that makes these differences very obvious, either inside or outside of the database and applications, or both. It s better to find an expert in the company first. Once again, you will get much farther, much faster, and with much more ease, by talking to people and asking questions. Homogenous Integration of Heterogeneous Databases In scientific terms, a heterogeneous system is a system consisting of dissimilar parts. And, obviously, a homogenous system is the complete opposite consisting of similar parts, or parts being of a uniform structure throughout. In terms of database models, some of the much more sophisticated (largely very expensive) database engines allow for the creation of homogenous integration of heterogeneous databases. In other words, it is possible to transparently link multiple types of databases, using different database engines, perhaps even including legacy databases, and really any type of database that can be catered for with whichever database is being used to perform the integration. The idea is to retrieve data from the controlling database, the one establishing the homogeneous, seamless, transparent interface, and manage data in any number of underlying databases. These maps or overlying homogeneous structures usually require what are called gateways, which are essentially database links from one database to another. The link is passed through a specialized driver, which allows the controlling database to talk to another database. Typically, these gateways are restricted to access only the commonly used databases. Any connections or gateways to older network or hierarchical databases could be implemented by manual coding. That is complicated and probably not worth the development effort. The fact is this all computer software, including databases and their incorporated database models, have a fixed life cycle. That life cycle is the cycle of usefulness within acceptable limits of cost effectiveness. There comes a point where older legacy software is either too expensive to maintain or can be easily replaced. At that point, there is simply no reason to retain and not rewrite older software. Other than that, older legacy software and databases (depending on how old they are) can present enormous problems for management in finding people to maintain those older systems. Converting from Spreadsheets Spreadsheets are always fun at first because they look like flat files. When looking into spreadsheet files, however, you can find all sorts of complexities with formulas on multiple levels and even multiple related sheets. Paper-based and legacy-system spreadsheets require analysis, requirements specifications of what should exist, and preferably the person who built the spreadsheet. 33 Database Modeling in the Workplace
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision mysql5 web hosting services

Dealing with Unfavorable Scenarios Quite often, the best environment for which to design is a completely non-computerized environment. Common in many database model design projects are what can only be called unfavorable and sometimes even ugly scenarios. Sometimes those ugly scenarios have to do with people. Those are best avoided; however, there are some commonly encountered situations where the people involved are only too willing to assist you in helping themselves out of a difficult database model problem. With any existing database model and any type of conversion, be it from a legacy database on a mainframe, a paper-based system, or even a spreadsheet program, get as much information as possible on what the new database model is supposed to achieve. Work backward from that point. Computerizing a Pile of Papers This can, in some ways, be the easiest problem to solve, and also, in some ways, the most difficult. The fact is nothing exists in the way of a computerized database and quite often the paper pile and its attached trail can show you (in great detail) exactly how the database model should look. Paper-based systems are sometimes very accurate and very detailed. They must also take all possibilities into account, and have been designed for all possible eventualities over many years. Aproblematic situation with a paper-based system is that it has often not been meticulously designed, and has more or less grown into what it is because of necessity. The obvious result is a complete nightmare of different types of paper with a lot of duplicated information, conflicting information, and a whole plethora of potential problems. In this extreme situation, you can use the basic format of paper documents to build basic table structures and possibly establish the operational aspects of the business. Sometimes it is possible to find one person in an organization who knows how it all works. Sometimes it s up to you to figure it out. In an extreme situation of total confusion, it is best to approach a database model design cautiously and get some kind of verification from people who understand operational functioning of the business. These people likely enjoy applying their knowledge to the creation of a database model. And better still, they also understand your database model design much better when it comes to final handover to the client. Further still, they might even point out other factors such as field additions and special cases that as an outsider you would never be able to see. The easiest way through a paper system is to collect as much printed material as you can and then start categorizing it. This task might be easier if you can find someone in the organization who can help you through this categorization process. Just be sure that the person actually does know about both the paper system and the operational function of the business. Someone who doesn t know zip could well confuse the heck out of you. Also, exchanging them later on for someone who is in the know could ruffle feathers. Choose wisely if you have a choice. Ask the manager or executive who hired you to pick the person. Ensure that it is the executive who wanted you in the company. You don t want to get involved in any petty politicking. Computerizing a pile of papers always looks like a daunting task. It isn t! In fact, it is very possible that by the time a company decides to computerize a paper system, the company will be more than willing to assist you in this most daunting of tasks because they might very well be trying to get rid of all those pesky pieces of paper they are constantly having to fill in and correct. 32 Chapter 2
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

Getting the right information is really an extension of talking to the right people, in that the correct detail comes from the mouths of the people who know how a company functions operationally. This is not a derogatory statement. The database designer must figure out what should and should not be listened to, and taken into account. Some company employees have a birds-eye view; others have a detailed picture of specific aspects of how a company makes its living on a daily basis. Another way to look at it is this. The more people you talk to in a greater number of sections and levels of a company, the more likely you (as a database model designer) will get a better perspective, perhaps even to the point of being able to dissimilate between correct and incorrect information and advise. The danger of talking to far too many people, taking account of some and not implementing suggestions and or requests of others, could certainly ruffle some feathers (not to mention completely confusing the database model designer). The situation might even be a delicate balance between producing a good structure and not upsetting anyone. Strike a balance. The database model must be correct, but it also has to be acceptable to the users. If there really are special scenarios, maintain them as special cases and not abstractions. Abstraction will save a database model from complexity, but could also make it unusable. Finally, if you are an outside consultant, bear in mind that what you think is correct and what you think is incorrect may not be the reality of the situation. You could be completely wrong. Try not to ruffle any feathers while analyzing for and building your database model design because you want people to use your database. There is no point in building a database design if it is not accepted because people simply don t like you. Strive to be liked, as well as achieving technical aims, and your efforts are unlikely to be wasted. Many technical people think that being liked is unimportant. They are wrong! It s not what you know, it s who you know. It s also not what you say, but how you say it! Never assume that you know more than in-house employees, but also realize that you do have some experience; otherwise, you would not be there. A combination of your skills and the knowledge of others can help you build a good database model design. Balance all perspectives and opinions to find the best solution. That solution should also be agreeable to as many of the participants as possible (including yourself). You as the database designer are important as well because ultimately you designed it and you are responsible for it. When you know you are correct, insist gently (but firmly), but also acquiesce when someone else might be correct. Consider all possibilities and don t let your own ego get in the way of your own success. Ensuring that a database model design comes into enthusiastic use is just as important as making sure the design is correct. Above all, listen! Listen, learn, and examine every piece of information you are given. If a piece of information is not given, ask for it. If a snippet of information is essential and one person does not or cannot provide it, ask another. Achieve your objectives, but be careful not to tread on people s toes. The material discussed here can be found in a sales handbook. It is all basic common sense, and, yes, it is largely selling selling yourself, your skills, your experience, and your ideas. When building a database model for a company, be it a brand new concept or a rebuild of something that is already there, you are potentially introducing change. Some people inside the company may react badly to potential change. Be aware of that, work around it, and work with it. 31 Database Modeling in the Workplace
Note: If you are looking for high quality webhost to host and run your jsp application check Vision christian web host services

of a company as possible. If a separate set of tables is created for every special-case scenario, you will have too many tables and, therefore, no reduction in complexity (probably what the company was aiming at by computerizing). An extremely complex database model is not the objective. Application developers and end-users need simplicity. Without adequate simplicity, your database model design might be impossible to use, particularly in the case of end-users and a data warehouse. The job of a database modeler is simplicity through abstraction. Abstraction is this sense of the word is the amalgamation of different aspects of company operations into a succinct set of tables and relationships joining those tables together. One simple rule is the more people you talk to about how to build their database model, the more likely you get a general overall picture (also the more likely you can get completely confused). The higher up the management scale you get, the more of a global picture you get. The problem with the management level is that they can leave out crucial details. Therefore, you must talk to the regular employees in specific job functions as well. Some managers might be able to point you at the right employees with whom you should talk. Overall, the number of people you must talk to depends on how complex the required database model should be. With simple database models, you can sometimes get away with using the elements of a paper-based system alone to build a database model. In more technical companies that include computer personnel skills (such as programmers, systems and database administrators, and so on), these people can possibly provide you with the most valuable of input; however, technical people can also be obstructive. Technical people, even in-house technical people, often do not have a clear perspective in terms of what end-users might need. In fact, more often than not, in-house technical people are less aware of end-user needs within a company than consulting people. Outside consulting help can often give a fresh perspective. Getting the Right Information When it comes to getting the right information, the question that should perhaps first be asked is, What is the right information? Do you know the correct information? As an outsider, the answer is probably not. As an insider, the answer is perhaps. There is a distinct advantage in using outside people to do essentially a task that not only treads on people s toes, but also has the threat of introducing change. No one likes change. An in-house employee cannot reach across departments, asking 101 people 101 questions, from all over the company, without having to negotiate large tangled knots of political red tape. Politics within companies is not always the case; however, environments that are difficult to deal with are common. Sometimes a consultant can either drive the process, or help get around the obstacles. Another (perhaps more quirky) fact is that the more a consultant costs, the more significant the consultant is to the company in terms of the relative cost perspective. Obviously, the higher the cost, the higher the approval-rated signature is for the consultant s bill (thus, the higher the level of management the consultant deals with and, therefore, the more likely that consultant will be effective). Obviously, all of this depends on company size, composition, political issues…the list is endless. On the contrary, in many situations, in-house people are the best option. Don t get a completely negative opinion of company politics. Political maneuvering has its purpose. The larger companies get, the more complex people issues and the human factor can become. Simple chain of command can often be misconstrued as politicking. In other words, politics is not always as unproductive as you might think. Every facet of a situation has its purpose. If a situation is wholly negative, perhaps you should attempt to use it to your own advantage in getting the job done. 30 Chapter 2
Note: If you are looking for best quality webspace to host and run your tomcat application check Vision virtual web hosting services