Web Hosting Java Blog

80 Part I EXAM PREPARATION TIPEXAM Normalization on the Exam The exam will require that you know what to denormalize, as stated in the Microsoft exam sub-objective: Specify degree of normalization. Normalization is not always the best design for a given database. Normalization creates numerous, small, interrelated tables. Processing the data in these tables can incur a great deal of extra work and other overhead to combine the related data. The extra processing reduces the performance of the database. In these situations, denormalizing the database slightly to simplify complex processes can improve performance. transaction processing) may not have redundant updates and may be more understandable and efficient for queries if the design is not fully normalized. In data warehousing the results of calculations are often stored with the data, so that type of processing does not have to occur when the data is read. Many reporting systems also denormalize data to produce results specific to the application. Sometimes you ll encounter situations where a fully normalized data model just won t perform in the situation you place it in. In situations like this, you have to denormalize your database. A normalized database needs more join queries to gather information from multiple entities (because entities are divided into smaller entities when undergoing the process of normalization). Therefore, CPU usage might overwhelmingly increase, and cause an application to slow or freeze. In situations like this, denormalization is appropriate. Normalization and denormalization processes begin to put a high- performance database system together in a logical fashion. Any seasoned database person knows, however, that performance isn t the only concern. It is necessary in any database system to minimize the administrative management needed to keep a database functional and accurate. The aim here is integrity. MAINTAINING DATA INTEGRITY . Design attribute domain integrity. Considerations include CHECK constraints, data types, and nullability. Specify scale and precision of allowable values for each attribute. Allow or prohibit NULL for each attribute. Specify allowable values for each attribute. Whether you have implemented or are in the process of implementing a data model, you will need to keep data integrity in mind as a key factor in verifying the correctness and uniqueness of data. Data integrity itself means preserving the correctness and verifying the consistency of data. When incorrect or inconsistent values and

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

80 Part I EXAM PREPARATION TIPEXAM Normalization on the Exam The exam will require that you know what to denormalize, as stated in the Microsoft exam sub-objective: Specify degree of normalization. Normalization is not always the best design for a given database. Normalization creates numerous, small, interrelated tables. Processing the data in these tables can incur a great deal of extra work and other overhead to combine the related data. The extra processing reduces the performance of the database. In these situations, denormalizing the database slightly to simplify complex processes can improve performance. transaction processing) may not have redundant updates and may be more understandable and efficient for queries if the design is not fully normalized. In data warehousing the results of calculations are often stored with the data, so that type of processing does not have to occur when the data is read. Many reporting systems also denormalize data to produce results specific to the application. Sometimes you ll encounter situations where a fully normalized data model just won t perform in the situation you place it in. In situations like this, you have to denormalize your database. A normalized database needs more join queries to gather information from multiple entities (because entities are divided into smaller entities when undergoing the process of normalization). Therefore, CPU usage might overwhelmingly increase, and cause an application to slow or freeze. In situations like this, denormalization is appropriate. Normalization and denormalization processes begin to put a high- performance database system together in a logical fashion. Any seasoned database person knows, however, that performance isn t the only concern. It is necessary in any database system to minimize the administrative management needed to keep a database functional and accurate. The aim here is integrity. MAINTAINING DATA INTEGRITY . Design attribute domain integrity. Considerations include CHECK constraints, data types, and nullability. Specify scale and precision of allowable values for each attribute. Allow or prohibit NULL for each attribute. Specify allowable values for each attribute. Whether you have implemented or are in the process of implementing a data model, you will need to keep data integrity in mind as a key factor in verifying the correctness and uniqueness of data. Data integrity itself means preserving the correctness and verifying the consistency of data. When incorrect or inconsistent values and

For high quality website hosting services please check tomcat web hosting website.

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 79 As stated previously, a database design s initial goal is to achieve the value and correctness of the third normal form to remove all data redundancy. The third normal form ensures elimination of data redundancy by eliminating interdependencies between non-key attributes. Identifying a 3NF violation involves looking at an attribute and then asking whether this attribute depends on the availability of any other non-key attribute. For example, examine the entity in Figure 2.9. The Trainee entity is violating one of the restrictions of 3NF because it has three interdependent non-key attributes, which are EnrolledCourseID, EnrolledCourse, and DateEnrolled. These are referred to as interdependent attributes because they depend on the attribute EnrolledCourseID. To implement and resolve this 3NF violation, add the enrollment date to the Course table and eliminate the extra unnecessary columns. This eliminates the redundant data in an entity (see Figure 2.10). Traine. TraineeCours. Course Traine. I. Cours. I. Firs. Nam. Cours. I. . F. Traine. I. . F. Name Las. Nam. Location Stree. Addres. Cost Cit. Availabl. Stat. Cours. Dat. Zi. Cod. Gende. Dat. Registere. Dat. o. Birt. SS. Nam. o. Cours. Paymen. Du. Normalizing a design provides for efficient use of storage, but in many situations will affect performance. Planned redundancy or denormalization are often brought into the design to provide for better performance or to clarify data. When to Denormalize Denormalization is the planned placement of redundant data to minimize table joins, reduce network traffic, eliminate repeated calculations, and provide for application-specific procedures. A database that is used primarily for decision support (as opposed to update-intensive Traine. Traine. I. Nam. Addres. Gende. Dat. Registere. Dat. o. Birt. SS. Nam. o. Cours. Paymen. Du. Enrolle. Cours. I. Enrolle. Cours. Dat. Enrolle. FIGURE 2.9 The Trainee entity violating a restriction of 3NF. FIGURE 2.10 The Trainee entity is no longer violating 3NF.

For reliable and cheap web hosting services please check tomcat web hosting website.

78 Part I EXAM PREPARATION After eliminating duplicates and adding additional entities, you can then proceed with the second normal form and ensure that attributes are assigned to the most appropriate entity. Second Normal Form The first requirement of the second normal form is that it meets the requirements of the first normal form. The second requirement is that all non-key attributes are dependant on the entity key. To pass 2NF, every attribute in an entity must depend on the whole key, not just a part of it. A violation in 2NF occurs when these requirements are not met. Warehous. I. . F. Regio. I. . F. Squar. Footag. Corporat. I. . F. FIGURE 2.8 The RegionWarehouse entity violating a restriction of 2NF. To know whether an entity is violating the restrictions of 2NF, see whether the attribute is dependant on only part of the Primary Key. 2NF applies in only those entities that have a compound Primary Key and a data element is dependent on only a portion of that key. In the example case there is no violation of 2NF, so Figure 2.8 uses a separate simple example to illustrate this point. In Figure 2.8, the partial dependence is true for the Square Footage attribute in that it is dependant on only the Warehouse ID and not the Region ID. To resolve a 2NF violation, create a new entity for the partially- dependant attributes and then place these attributes inside it. Then create a key to make a relationship to the original entity. The 2NF ensures model flexibility and tries to ensure the prohibition of redundant data. Meeting the second normal form brings the design closer to an efficient model. A final set of adjustments to consider is the third normal form. Third Normal Form Third normal form shares a common objective like the other normal forms, in that its goal is to eliminate redundant data. However, it is more similar to the second normal form because it accomplishes this by cutting down interdependencies on non-key attributes. The third normal form ensures that you get the most consistency with the entity layout after you normalize the database. 3NF states that all non-Primary Key attributes must depend on the key. You cannot have any indirect or transitive dependencies, as described in the following paragraphs.

For high quality jboss hosting services please check jboss web hosting website.

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 77 Employee not further divisible. Employe. I. An attribute, which is not repeating, is known as an atomic value, Nam. Address which is a value that is represented only one time. You learned about Gende. Dat. o. Birt. the decomposition of attributes in the earlier discussion about hav SSN ing Street, City, State, and Zip fields rather than a general Address Contrac. Typ. Salary field. This is precisely what the 1NF does. Positio. MachineName1 Successfully passing the 1NF eventually increases data access and MachineName. performance. If there is ever a violation of the first normal form, MachineName. it can easily be resolved. To know whether your database has violated the restrictions of 1NF, look at the attributes in an entity FIGURE 2.6 The Employee entity violating a restriction and then see whether the attribute repeats or is akin to any of the of 1NF. other attributes. If so, then you know that you are violating the first normal form. To resolve this type of situation, find all the attributes that seem to be repeating. For example, take a look at a variation of the Employee entity where a repeated array of values holds information for an employee s multiple machine capabilities, as seen in Figure 2.6. To resolve this type of situation, find all the attributes that seem to be repeating. Create a new entity with a name similar to the repeating attribute and place all repeatedly occurring attributes into it. For this example, if a machine entity didn t already exist it would be created. After creating a new entity and shifting repeated attributes into it, you have to create a key (a key that uniquely identifies each row in the new entity) and a related key in the Employee entity. In the example it is better treated as a many-to-many relationship. Many employees can run a machine and an employee can run many machines. This relationship would look similar to the illustration in Figure 2.7. Employe. EmployeeMachiner. Machinery Employe. I. Machiner. I. Employe. I. Firs. Nam. Machiner. I. Name Las. Nam. Location Stree. Addres. Purpose Cit. Stat. Zi. Cod. Gende. Dat. o. Birt. SS. Contrac. Typ. Salar. FIGURE 2.7 Positio. Employee/machinery relationship.

For high quality website hosting services please check cheap web hosting website.

76 Part I EXAM PREPARATION planned denormalization is the process of simplifying data and data design to achieve maximum performance and simplicity. This denormalization process involves the planned addition of redundant data. Although both normalization and denormalization are valid, necessary processes, the two achieve opposite goals. They don t by themselves achieve maximum performance and simplicity, though they do strive for a perfect balance between performance (denormalization) and simplicity (normalization). Normalization means no duplicate data. In 1970, Dr. E. F. Codd designed three regulations a relational database adheres to, known as normal forms, and today known as the first, second, and third normal forms. (Normal forms do exceed three, but the first three are the only ones widely used.) The goal of the initial database design is to simplify the database into the third normal form. Before starting to normalize a database, look at the following guidelines: All columns should be decomposed (broken down to their most basic form). Many-to-many relationships should be converted to pairs of one-to-many relationships. This is not part of normalization, but part of the physical implementation. Primary and Foreign Keys should be created and identified. Normalizing a database is seemingly good, but can hamper performance. In many cases a designer has to consider denormalizing a database, which is discussed a little later. The following sections discuss the three normal forms of a database and how to implement them. First Normal Form The first normal form, or 1NF, defines the foundation for the relational database system. Attributes should be atomic, which means that they cannot (or should not) be further broken down, based on the business needs for the use of the attribute. The first normal form defines that all attributes be atomic, which is to say they cannot be decomposed and must be non-repeating. In relational database terms, 1NF states that all attributes must be defined in their singular- most form; which means that attributes must be decomposed and

For reliable and cheap web hosting services please check cheap web hosting website.

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 75 REVIEW BREAK design that will improve efficiency of the data store. Data Modeling Overview We have completed our look at the basic elements of data modeling using the Entity Relationship approach. A general listing of attributes for each entity and the relationships between these entities is an important springboard for progressing through the database design to the eventual completed system. Entities can correspond with each other in several different manners. If you look at a typical sales problem, it is easy to see some of these relationships. A salesman will have many customers, a customer will have many invoices, and an invoice will have many purchased products. The most common relationship is one-to-many, but the entire database system is sure to reveal a few many-to-many relationships as well. In the sales case, for example, many salesmen sell many products. Although you now have a general listing of entities, relationships, and attributes, the logical design is not yet complete. Next you need to fine-tune the model with a process called normalization. UNDERSTANDING DATABASE NORMALIZATION . Define entities. Considering normalization and denormalization. Specify degree of normalization. Normalization in general refers to how you implement and store data. (Normalization is a design process intended to eliminate duplicate data, it is not how you implement.) In a normalized entity, the redundant data is removed and the entity is simplified to its most basic form. This usually leads to a more involved entity structure with more entities. In the same way, database normalization and

For high quality website hosting services please check cheap web hosting website.

74 Part I EXAM PREPARATION FIGURE 2.4 A many-to-many relationship. FIGURE 2.5 Sample data after a many-to-many resolution. Traine. TraineeCours. Course Traine. I. Traine. I. . F. Cours. I. Firs. Nam. Cours. I. . F. Name Las. Nam. Location Stree. Addres. Cost Cit. Availabl. Stat. Zi. Cod. Gende. Dat. Registere. Dat. o. Birt. SS. Nam. o. Cours. Paymen. Du. resolving many-to-many relationships is shown in Figure 2.4. To help you better understand many-to-many relationships, the Course, Trainee, and Description entities along with sample data are shown in Figure 2.5. One other many-to-many relationship also exists in the scenario. There is likely to be a many-to-many relationship between Employee and Machine, which is explored in the next section. Setting up the relationships finalizes a draft of the ER model. This draft will undergo modifications as the database approaches a physical design. The next topic to discuss is potential alteration to the TraineesTraineeIDFirstNameLastNameZipCodeCityStateAddressDuePayment 1101JohnJones74832BurfordCA278MillStreet200.0000 2207SueSmith73638DelhiCA837PhelanStreet300.0000 3473BillBrown73833EmbroCA62DundasStreet400.0000 4673JoeSamson73345DurhamCA9304FerdaleAvenue200.0000 5983SallyHillery78390WilsonCA9CenterDrive100.0000 CoursesCourseIDNumberNameCostAvailable 11A2378BedBaths1001 21B6392Sterilization1001 31C7296IntroductoryMeds1001 41D1739AdvancedMeds1001 52A1736CardioTherapy1001 62B8363ICUCare1001 72C8923EmergencyCare1001 82D7436PostOperativeCare1001 93A7322PostNatal1001 103B4280DoctorAssisting1001 113C8370DietaryCare1500 123D1298MedicalTraining1500 134A7922IVTherapyI2000 144B2492IVTherapyII2000 154C0982IVTherapyIII2500 164D3487IVTherapyIV2500 TraineeIDCourseID 11013A 21013B 32072D 42073A 52073B 64732A 74732B 84732C 94732D 106732C 116732D 129831A

For reliable and cheap web hosting services please check cheap web hosting website.

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 73 An EMPLOYEE helps a PATIENT with his/her expertise and can prescribe MEDICINE if needed. EMPLOYEES teach COURSES to TRAINEES studying at Lloyd s Academic. Employee Course Treats Trainee Patient TRAINEES are those A PATIENT is one who needs participating in the COURSES treatment from a doctor provided by Lloyd s Hospital (EMPLOYEE). Used by doctors to better help patients Medicine Machinery MEDICINE is used to aid in treating their PATIENTS. MACHINERY helps EMPLOYEES PATIENTS and is prescribed by EMPLOYEES. going to look like. This is shown in Figure 2.3. So how are relationships actually implemented? They are implemented as parent and child entities. In all cases in the ER model, a key attribute from a child parent entity is attached to a related key value in a parent. All cardinality of relationships are implemented in this way. This means that whether you have a one-to-one, one-to-many, or many-to-many relationship, you always have the key of the child related to a parent. As noted previously in the chapter, many-to-many relationships exist when many instances of one entity are related to many instances of another entity. The reason why many-to-many relationships are implemented a bit differently is that the ER relational model does not support many-to-many relationships. Nonetheless, establish many-to-many relationships by creating two one-to-many relationships and connecting them to a new entity. This new entity is known as an associate entity or join entity. Resolving many-to-many relationships involves creating two one-tomany relationships from each of the original entities onto the associative entity. Take the many-to-many relationship between the Course entity and Trainee entity, for example. A many-to-many relationship needs to be resolved by creating an associative entity, TraineeCourse, and then linking a one-to-many relationship from the Course and Trainee entities to TraineeCourse. This process of FIGURE 2.3 ER model overview.

For high quality website hosting services please check java web hosting website.

72 Part I EXAM PREPARATION determining entities and relationships and I advise beginners to use this. I once came across a saying, One trick to discovering relationships between entities is to look closely at the entity definitions. I ve particularly found that many beginners and even professionals use this method and find it extremely effective. Look back at the definitions you wrote for each entity in Step by Step 2.2: Patient. Stores information about the individual patients registered in Lloyd s Hospital to get treatment provided from doctors (EMPLOYEES). Employee. Tracks information, such as salary and contract, about the doctors and workers employed at Lloyd s Hospital who teach COURSES and treat PATIENTS. Medicine. Keeps a statistical list of the medicine available, which EMPLOYEES are allowed to prescribe for their ailing PATIENTS. Machinery. Holds miscellaneous details of the sophisticated machinery and utilities available at Lloyd s, only used by specialized EMPLOYEES. Course. Keeps information concerning the courses available at Lloyd s Hospital, taught to TRAINEES by EMPLOYEES. Trainee: Keeps track of the students learning the different COURSES available from Lloyd s EMPLOYEES. From the preceding descriptions, you can construct a blueprint of an ER diagram after you summarize the information. A PATIENT is one who needs treatment from a doctor (EMPLOYEE). An EMPLOYEE helps a PATIENT with his/her expertise and can prescribe MEDICINE if needed. MEDICINE is used to aid PATIENTS and is prescribed by EMPLOYEES. MACHINERY helps EMPLOYEES in treating their PATIENTS. EMPLOYEES teach COURSES to TRAINEES. TRAINEES are those participating in the COURSES provided by Lloyd s Hospital. Now you can construct a general overview of what your model is

For high quality jboss hosting services please check jboss web hosting website.