Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

Chapter 7 . Database Concepts and Design 121 . Second Normal Form (2NF): This form includes the 1NF definition and requires that all attributes of a specific entity depend on the entire key value. If the key value is only one value, then attributes that depend on it already depend on the entire value, as shown in Figure 7-9. 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 355 Baseball Hall of Fame 95 Braves 400 NFL Hall of Fame 94 49ers 411 Formula One 99 Mclaren 400 NFL Hall of Fame 94 49ers 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-9: Making sure all attributes depend on the key . Third Normal Form (3NF): This form includes the 2NF definition and requires that all attributes of a specific entity depend only on the key, not on other attributes. In other words, the Team is dependent only on the Team_ID, not on the Name of the person. Figure 7-10 illustrates the final tables for this example. Note that foreign-key constraints could be used to remove Table4 from the example.
Note: If you are looking for cheap and reliable webhost to host and run your web application check Vision coldfusion web hosting services

120 Part I . Getting Started Referential integrity means that any foreign key that exists within one table must also have a corresponding row and primary key within the related table. For example, if a Zip Code exists within a customer s record in the example, there must be a corresponding Zip Code record in the Locale table for referential integrity. Normalization Long the woe of many a college student learning database concepts, normalization is the practice of getting a database design into a format with no duplications or redundancy of data within the database so the database operates as efficiently as possible. This has obvious benefits of efficiency. However, it can also prevent catastrophic inconsistencies. There are five forms that a data model passes through on its way to being normalized. For our purposes, I ll deal with only the first three forms. As a data model is normalized, it is said to be in (and pass through) First Normal Form, Second Normal Form, Third Normal Form, and so on, like this: . First Normal Form (1NF). This form is achieved when there are no repeating attributes. Table 7-1 shows such a table. For many small projects this type of table is fine. While there is repeated data such as the Team and Location, having the repeats doesn t adversely affect performance on small tables and projects. However, if you had to make a change to the Team for one of the people in the table, the update could take longer. Additionally, if you delete the Steve Suehring record from the table, you would lose all of the Team and Location info, resulting in unforeseen data loss. Table 7-1 Table in First Normal Form ID Name Location Loc_ID Team Team_ID 4 Steve Suehring Bat Boy 515 Dodgers 78 5 Greg Maddux Baseball Hall of Fame 355 Braves 95 6 Jerry Rice NFL Hall of Fame 400 49ers 94 7 Mika Hakkinen Formula One 411 McLaren 99 8 Steve Young NFL Hall of Fame 400 49ers 94 3 Don Sutton Baseball Hall of Fame 355 Braves 95
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision ecommerce web hosting services

Chapter 7 . Database Concepts and Design 119 Customer Customer First Name Customer Last Name Customer Address Customer Supplemental Address Customer Zip Customer Area Code Customer Telephone Number Customer Email Address Customer Card Info Customer CC Number Customer CC Expiration Customer Name on Card Customer Email Address Card ID Locale Locale City Locale State Locale Zip Manufacturer Manufacturer Name Manufacturer Address Manufacturer Zip Manufacturer Area Code Manufacturer Telephone Number Manufacturer Contact Product Product Name Product Price Product Quantity On Hand Product Category Product Type Product ID Product Category Credit Card Type Card ID Credit Card Type Figure 7-8: Separation of the Product Category from the Product Table Constraints Constraints are limitations or controls placed on attributes within an entity. They are used to maintain order and keep the model true to the business process it is describing. The Uniqueness constraint is one that you encounter frequently. For the online store model to work, the e-mail address of each customer will need to be unique. Additional attributes within other entities also must be guaranteed to be unique, such as the Zip Code attributes in the Locale entity. The Uniqueness constraint is sometimes called a primary key within a database table though Uniqueness is only one of the attributes of a primary key. An Exclusion constraint is a logically exclusive ORrelationship. With such a constraint, one and only one entity may participate in a relationship with a root entity. For example, if you manufactured widgets and could get parts from an internal or external supplier, one and only one supplier would supply the part for a specific order. Any entity that exists and has a primary key must also ensure that the primary key exists to meet the constraint of Entity Integrity. Since e-mail address is the primary key for the customer entity, if you start a customer record for your online store, they must have an e-mail address to exist at all according to this constraint. Note
Note: If you are looking for cheap and reliable webhost to host and run your web application check Vision coldfusion web hosting services

118 Part I . Getting Started Yet another iteration of the data model is needed because I must create a relationship between the credit-card type and the credit card. Recall that the type of card could be determined by looking at the digits of the card itself. However, I d like to do some error checking, so having the customer choose the card type is useful. To relate card type to card number, I could simply add a field to the Customer Card Info table. However, doing so would nullify any efficiency gained from separating those entity types. Therefore I will create a Card ID attribute type that is simply an integer. For example, I will give Visa credit cards an ID of 1, MasterCard an ID of 2, and so on. Figure 7-7 shows the next draft of the data model. Customer Customer First Name Customer Last Name Customer Address Customer Supplemental Address Customer Zip Customer Area Code Customer Telephone Number Customer Email Address Customer Card Info Customer CC Number Customer CC Expiration Customer Name on Card Customer Email Address Card ID Locale Locale City Locale State Locale Zip Credit Card Type Card ID Product Credit Card Type Manufacturer Product Name Manufacturer Name Product Price Manufacturer Address Product Quantity On Hand Manufacturer Zip Product Category Manufacturer Area Code Manufacturer Telephone Number Manufacturer Contact Figure 7-7: Addition of the Card ID entity type enables joining of the Customer Card Info and Card Type tables. An examination of the Product entity type reveals that the Product Category attribute really doesn t belong with that entity type. Therefore I create a new entity type for Product Types. I can now add a Product ID attribute to relate that entity type back to the Product entity type. The result is shown in Figure 7-8.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision best web hosting services

Chapter 7 . Database Concepts and Design 117 Customer Customer First Name Customer Last Name Customer Address Customer Supplemental Address Customer Zip Customer Area Code Customer Telephone Number Customer Email Address Customer Card Info Customer CC Number Customer CC Expiration Customer Name on Card Credit Card Type Credit Card Type Locale Locale City Locale State Locale Zip Product Manufacturer Product Name Manufacturer Name Product Price Manufacturer Address Product Quantity On Hand Manufacturer Zip Product Category Manufacturer Area Code Manufacturer Telephone Number Manufacturer Contact Figure 7-5: Placing City, State, and Zip in another entity type makes the model more efficient. Many online stores choose E-mail Address as the unique attribute in a customer s information. I believe this is a good idea, so I implement my customers e-mail addresses to relate their credit card information to them, as shown in Figure 7-6. Customer Customer First Name Customer Last Name Customer Address Customer Supplemental Address Customer Zip Customer Area Code Customer Telephone Number Customer Email Address Customer Card Info Customer CC Number Customer CC Expiration Customer Name on Card Customer Email Address Locale Locale City Locale State Locale Zip Credit Card Type Credit Card Type Product Manufacturer Manufacturer Name Manufacturer Address Manufacturer Zip Manufacturer Area Code Manufacturer Telephone Number Manufacturer Contact Product Name Product Price Product Quantity On Hand Product Category Figure 7-6: Relating customers to their credit cards is rather important.
Note: In case you are looking for affordable webhost to host and run your web application check Vision cheap hosting services

116 Part I . Getting Started Customer Customer First Name Customer Last Name Customer Address Customer Supplemental Address Customer City Customer State Customer Zip Customer Area Code Customer Telephone Number Customer Email Address Customer Card Info Customer CC Number Customer CC Expiration Customer Name on Card Credit Card Type Credit Card Type Manufacturer Manufacturer Name Manufacturer Address Manufacturer City Manufacturer State Manufacturer Zip Manufacturer Area Code Manufacturer Telephone Number Manufacturer Contact Product Product Name Product Price Product Quantity On Hand Product Category Figure 7-4: Results from breakdown of many-to-many relationship. One major area for improvement in this data model is in the area of the City, State and Zip storage. Since Zip codes define and are always tied to one city and state, there is no reason to store the City and State in both the Customer entity type and the Manufacturer entity type. It would be much more efficient to make another entity type to house the City and State and relate that entity type to both the Customer and Manufacturer entity types. This draft of the ERD is shown in Figure 7-5. As you can see from the new draft of the data model shown in Figure 7-5, the Zip attribute type is duplicated in both the Customer/Manufacturer entity types and in the Locale entity type as well. This is so I can join or match the information together. For example, I have a customer with a Zip code of 54481. I can then look up the city and state for the zip code of 54481 in the Locale table. Without the zip code being stored in the Customer table, I would have no way to know where the customer lives or where to ship their goodies! The Zip Code attribute within the Customer entity is known as a foreign key. A foreign key enables you to relate an attribute in one entity to the attribute of another entity. In this case, the Zip Code attribute in the Customer entity type is related to the Zip Code attribute of the Locale entity type. Based on information about foreign keys, the astute observer may have already noticed the flaw in the Customer to Credit Card relationship. There is no way to tie customers to their credit card information. Either I charge random cards or don t charge at all. A recipe for disaster no matter how you look at it. I must choose a way to relate a customer with their credit card info. Customer name is out because I hope to have more than one John Smith shop at my store. I must find an attribute type that will always be unique within the Customer entity type.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision best web hosting services

Chapter 7 . Database Concepts and Design 115 Customer Customer First Name Customer Last Name Customer Address Customer Supplemental Address Customer City Customer State Customer Zip Customer Area Code Customer Telephone Number Customer Email Address Credit Card Credit Card Type Credit Card Number Credit Card Expiration Credit Card Name on Card Manufacturer Product Manufacturer Name Product Name Manufacturer Address Product Price Manufacturer City Product Quantity On Hand Manufacturer State Product Category Manufacturer Zip Manufacturer Area Code Manufacturer Telephone Number Manufacturer Contact Figure 7-3: Customers can use more than one card, and more than one customer can use the card. However, a many-to-many relationship should be broken down into two separate one-to-many relationships. Along with breaking the many-to-many relationship apart, I must search for extra attributes that may be disguising themselves as relationships. The result of this analysis is shown in Figure 7-4.
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision mysql5 web hosting services

114 Part I . Getting Started Customer Customer First Name Customer Last Name Customer Address Customer Supplemental Address Customer City Customer State Customer Zip Customer Area Code Customer Telephone Number Customer Email Address Customer Customer First Name Customer Last Name Customer Address Customer Supplemental Address Customer City Customer State Customer Zip Customer Area Code Customer Telephone Number Customer Email Address Credit Card Credit Card Type Credit Card Number Credit Card Expiration Credit Card Name on Card Manufacturer Product Manufacturer Name Product Name Manufacturer Address Product Price Manufacturer City Manufacturer State Manufacturer Zip Manufacturer Area Code Product Quantity On Hand Product Category Manufacturer Telephone Number Manufacturer Contact Figure 7-2: First Draft of an ERD for the online store
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision servlet hosting services

Chapter 7 . Database Concepts and Design 113 Tip The O shape near the Product entity type indicates that the existence of a product is not required for a Manufacturer to exist in the database. This O is missing from the Manufacturer side of the relationship because a manufacturer is required if there is a product in the database. Manufacturer Product Manufacturer Name Manufacturer Address Manufacturer City Manufacturer State Manufacturer Zip Product Name Product Price Quantity On Hand Figure 7-1: Relationships among some example entity types Entity-relationship diagrams With a list of entity and attribute types for a data model in place and a basic understanding of how to relate them to each other you create a diagram that formally shows the relationships of each entity type. The diagram simply groups together like information. This will help to normalize the data and understand the business processes, and possibly find additional entities, attributes, and relationships that must be included in the data model. Square or rectangular boxes on the diagram represent entities. Relationships can be represented by number of line formats. In looking again at the list of attribute types in the example, it is evident that certain customer-related information is stored in the database. My initial draft of an Entity- Relationship Diagram (ERD) for the example database is shown in Figure 7-2. Analysis of diagrams As I touched on earlier, the manufacturer and product relationships are (mostly) fine. One manufacturer can have many products, but a given product can have only one manufacturer. Examining customer and credit-card relationships in the draft of the ERD in Figure 7-2, you can see that each customer can have more than one credit card stored in the database. However, each credit card can only be associated with one customer. This is not what I would like I want the entire family to create accounts and use Dad s credit card to buy. To alleviate this problem, I must make both sides of the relationship many-to-many. The second draft of the ERD, in Figure 7-3, shows the new many-to-many relationship.
Note: If you are looking for cheap webhost to host and run your apache application check Vision apache web hosting services

112 Part I . Getting Started . Manufacturer Contact . Manufacturer Area Code . Manufacturer Telephone Number . Product Category Now that I have a list of entity types and attribute types for the data model, I can start to analyze how they relate. Relationships In my example, it s obvious that some attribute types relate to each other. For example, each First and Last Name pair can have one or more Addresses, Supplemental Addresses, Cities, States, and Zips. As should be evident, First Name/Last Name (which I will refer to as Customer Name for brevity s sake), Address, Supplemental Address, City, State, Zip (which I will refer to simply as Address), and so on are all items relating to the Customer entity type. It quickly becomes clear that I could go down a slippery slope because one Customer Name could have more than one Address and one Address could have more than one Customer Name. However, what may appear clear is not necessarily as clear as you may think. While it is true that a Customer Name could have more than one Address, a Customer Record is made up of one and only one Address. What appeared to be a problem in the design is actually a false relationship. Tip By adding other entity types, you can add support for more than one address to allow the customer to ship to different addresses. Each relationship has a degree of cardinality. In other words, there can be one and only one Manufacturer of a certain product, but that Manufacturer can have many products. Determining the relationships is an art that takes practice. Thinking through the business process thoroughly will help to work out the true relationships for your data model. Figure 7-1 shows an example of relationships in an Entity-Relationship Diagram. The example illustrates the relationship between manufacturer and products. . Each manufacturer can have one or more products, represented by the crow s-foot line near the Product box. . Each product can and must have one and only one manufacturer, as represented by the single line near the manufacturer box.
Note: If you are looking for high quality webhost to host and run your jsp application check Vision jsp web hosting services