Web Hosting Java Blog

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 105 A PPLY YOUR K NO WLEDGE Component/Parts E. Suppliers, Equipment, Components, Parts, Component/Equipment Answers to Review Questions 1. The client/server model can be thought of as a connection between a client program or computer requesting a service or data from the server. When the client application needs certain data, it makes a call to the server. The server searches for only the specific data sought by the client and then fulfills the client s request by sending the requested data back to the client application. 2. An entity can be thought of as a table. Entities are usually represented as boxes. Attributes are the characteristics of the table. Attributes are usually represented as rows inside an entity. They can be thought of as the columns of a table. The Primary and Foreign Keys on those tables make up the relationships. They show the relationship of one table to another in a real- world situation. 3. Decomposing an attribute provides many benefits in contrast to using general-built attributes. Decomposing an attribute is done when data integrity is a key factor and also when data query performance needs to be improved. See Optimizing Attributes. 4. A Foreign Key is a column or multiple columns whose values match the Primary Key of another table. Foreign Keys help in the relational process between two entities by connecting to a Primary Key. When a Primary Key is created on a parent table, it is connected to another table by hooking onto the other table s Foreign Key. See the section Foreign Keys. 5. The four constraints that can be placed on columns are Default, Null, Duplicate, and Changes. When the data model approaches perfection, certain measures must be taken to provide the most for your needs and to keep data integrity at its peak. These restrictions or constraints maintain the correctness of the data being entered. 6. Denormalization is undertaken when the database lacks performance. A database that has been normalized requires more join queries to gather information from multiple tables. Therefore, CPU usage might overwhelmingly increase and cause applications to freeze. In situations like this, denormalization is appropriate. Answers To Exam Questions 1. B, C. Replication and partitioned views enable you to spread the load of a very large database system across several machines. The benefit of additional processing power and getting the data closer to the user could be recognized by both features, assuming they were properly partitioned and configured. For more information, see the section titled The Client/Server Model. 2. B. With few resources on the clients, you have to make the clients as small as possible. N-tier or Internet could be potential solutions, but with the lack of sufficient processing power in the form of a server-grade machine, these would not suit this scenario. For more details, see the section The Client/Server Model. 3. E. This is a good candidate for an Internet solu

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

104 Part I EXAM PREPARATION APPLY YOUR KNOWLEDGE 11. A small manufacturing company has a considerable number of data sources because no standardization has occurred across any platform. One of the database servers has SQL Server installed; the others come from a variety of vendors. For a project you are working on, you need to gather data from the SQL Server and merge it together with data from two other sources. You then need to bring the data into Excel to do some charting. How would you accomplish this? A. Export the data from the other sources into a comma-delimited file for import to SQL Server. Then export from SQL Server the data that is to be imported into Excel. B. Export the data from all three sources so that it can be imported into Excel. C. Use SQL Server to transfer all the data from all sources directly into Excel. D. Use Excel to transfer data from all three sources into a spreadsheet. 12. A small scientific laboratory needs a powerful database server to perform analysis of complex measures performed on scientists regular experiments. The lab requires exact accuracy with all calculations because the results determine the fracture points of various metals. Which data type offers the most accurate results? A. smallmoney B. money C. float D. real E. decimal 13. In a large department store, an inventory database is maintained for all products sold. Data is updated frequently by multiple computer terminals. Forty computer terminals throughout the offices and store can access the database simultaneously to perform updates. You want to minimize conflict situations and reduce the load on the server as much as possible. The client systems have very little processing power of their own. What architecture would you select for this system? A. Single-tier B. Two-tier thin client C. Two-tier thick client D. N-tier E. Internet 14. You are implementing a database for a military warehouse that needs to track the components that make up their equipment. Each piece of equipment has more than a hundred parts, each of which is made up of many smaller components. Any given aircraft has thousands of components from wheel assemblies to jet engines. A wheel assembly is made up of approximately 50 other components, each of which may come from a different supplier. Many of the separate parts are used in other components. What set of entities would be used in this structure? A. Suppliers, Equipment, Components, Parts B. Suppliers, Equipment, Components, Parts, Supplier/Parts C. Suppliers, Equipment, Components, Parts, Equipment/Parts D. Suppliers, Equipment, Components, Parts,

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

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 103 APPLY YOUR KNOWLEDGE D. Decomposable normal form E. Boyce-Codd normal form 7. You are working for an automobile dealership that tracks inventory in a SQL Server database. The database contains information on the autos in stock. A partial listing of attributes is as follows: VehicleIDNo(20 char), InvoiceNo (bigint), Make(20 char), Model(15 char), Year(smalldatetime), Colorcode(int), PurchasePrice(smallmoney), StickerPrices (smallmoney). Which of the columns would you choose as a Primary Key? A. Use a compound key with Make, Model, and Year. B. Create a surrogate identity key. C. Use the VehicleIDNo as the key. D. Use the InvoiceNo as the key. E. Use a compound key with InvoiceNo and VehicleIDNo. 8. You are working in a database that has a nchar(5) attribute used to store solely numeric data. You want to minimize the amount of disk space used for storage and need to select an alternative data type. Which of the following data types would you select? A. char(5) B. real C. smallint D. int E. bigint 9. You are creating a historical database that stores information about important dates in history. You need to be able to store dates from the beginning of the 14th century. You want to minimize the storage space used by the data. Which data type would you use? A. datetime B. smalldatetime C. bigint D. int E. char(8) 10. You are preparing a database structure for a large construction company. At any one time the company is working on five or more job sites, and each site has between 25 and 200 homes. In charge of each site is a site supervisor who organizes the subcontractors at each phase of the building process (landscaping, framing, drywalling, electrical, plumbing, and so on). Any subcontractor who is planning on working on a given site must be found in a database of approved vendors. The company would like a structure that would allow for storage of the subcontractors personal information and information about each site that includes the subcontractors assigned to the site. How would you set up this structure? A. A Site entity and a Contractor entity B. A Site entity, a Contractor entity, and a Site/Contractor entity C. A Site entity, a Process entity, and a Contractor entity D. A Site entity, a Contractor entity, and a Site/Process entity

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

102 Part I EXAM PREPARATION APPLY YOUR KNOWLEDGE 3. In a large office with hundreds of users, you have several servers that are centrally located in a secured room that only IT staff can access. One server is used as a security server and also acts as a DHCP server. A second dual processor server is running SQL Server and another machine runs an e-mail system with IIS operational. The office does not permit any other user access to the Internet nor does it expose any information to the Internet through a web site. You must select an application architecture suitable to this configuration. No other software is available on the servers. What application architecture would be best suited? A. Single-tier B. Two-tier thin client C. Two-tier thick client D. N-tier E. Internet 4. You are creating a database for a large government office. The Primary Key has already been established but you need to supply another column that has to have different values for each record. What data types could you use without creating additional constraints? Choose all that apply. A. timestamp B. bigint C. uniqueidentifier D. nvarchar E. sql_variant 5. You are creating a database for a large government office. The Primary Key has already been established, but you need to supply another column that has to have different values for each record. What implementation techniques are available other than data type selection? Choose all that apply. A. Identity B. Foreign Key C. Unique index D. Unique constraint E. Rule 6. You are putting together the logical design for a database. Tables to be included in the database are Employees, Customers, Supplies, Products, and Sales. The table used to store customer data has the following attributes: CustomerID (Primary Key), CustomerName, StreetAddress, City, State, ZipCode, BalanceOwing, SalesmanID, SalesmanName. Which of the following rules of normalization are not being maintained? Select all that apply. A. First normal form B. Second normal form C. Third normal form

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

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 101 A PPLY YOUR K NO WLEDGE 4. To disallow changes on an attribute, use the NO CHANGES property. Because changes are not going to be made on the date an employee was hired, place this value beside the Hire_Date attribute. 5. The UNIQUE characteristic specifies that duplicate values not be permitted on any given column. In the Employee entity, the Phone, SSN, and Address attributes cannot have duplicate values; that is, no employee can have the same phone number as any other employee. 6. The final entity should look similar to the one in Figure 2.15. If you have not already copied out the entity, then copy it out. Review Questions 1. How does data processing occur in the client/server model? 2. What are entities, attributes, and relationships? 3. Why would you want to decompose an attribute? 4. How are Foreign Keys related to Primary Keys? 5. Name four constraints that can be placed on columns. Why would you want to place constraints? 6. When would you consider denormalizing a database? Exam Questions 1. You have prepared the logical design for a very large database system that will act as the back end for an Internet application, as well as being accessed from the corporate WAN. You need to support a large number of concurrent users who will be accessing the database at various bandwidth speeds. Which SQL Server technologies could assist in allowing the users access while providing good performance? Choose all that apply. A. Analysis services B. Replication C. Partitioned views D. English query E. Meta data services 2. You are designing a database that will be used for a small office. The client machines have minimal hard drive space and very little RAM. Other than the database server, there are no server-grade machines available. You have chosen a SQL Server in a client/server architecture as the best implementation for this system. Which application architecture is best suited for this system? A. Single-tier B. Two-tier thin client C. Two-tier thick client D. N-tier E. Internet

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

100 Part I EXAM PREPARATION APPLYYOURKNOWLEDGEAPPLY YOUR KNOWLEDGE Traine. TraineeCours. Course Traine. I. Cours. I. Firs. Nam. Cours. I. . F. Traine. I. . F. Name Las. Nam. Location Stree. Addres. Cost Cit. Available Stat. Zi. Cod. Gende. Dat. Registere. Dat. o. Birt. SS. Nam. o. Cours. Paymen. Du. FIGURE 2.14 An example of a many-to-many relationship. Include in each entity the attributes defined as rows inside the box. 2. Recall that a many-to-many relationship can only be implemented with the help of an associative entity as an intermediate. Create a new entity between Trainee and Course and name it TraineeCourse. 3. The new associative entity needs to link both the Course and Trainee entities. To do this, the associative entity must contain two Foreign Keys: TraineeID and CourseID. 4. After you have placed these two attributes onto the associative entity, draw two lines from the associative entity to both other entities, placing a large dot at the ends of the lines pointing to the associative entity. The final model should look similar to the one in Figure 2.14. 2.3 Deciding on Primary Keys and Attribute Characteristics This exercise demonstrates the use of characteristics and Primary Keys, as well how to identify them. Estimated Time: 15 minutes 1. The entity to which you will be adding column constraints is the Employee entity. If you have not already copied out the Employee entity, do so now. 2. Now you will need to decide upon a Primary Key Employe. Employe. I. -IDENTIT. Firs. Nam. -NO. NUL. Las. Nam. ContractTyp. -NO. NUL. -NUL. Salar. -NO. NUL. Divisio. -NO. NUL. UNIQU. SS. -NO. NUL. N. CHANGE. Hire_Dat. -NO. NUL. UNIQU. UNIQU. Phon. Addres. -NUL. -NUL. FIGURE 2.15 An example of choosing attribute characteristics. for this entity. You could use the employee s first name as a Primary Key, but doing so allows for duplicate values. Creating a Primary Key with the IDENTITY property enforces uniqueness on any attribute. Create a new attribute named EmployeeID that will be used as the Primary Key and mark it as IDENTITY. 3. You can decide which values are required and which values do not need to be entered; use NULL and NOT NULL to do this. Place NOT NULL for the FirstName, LastName, Hire_Date, SSN, Division, and Salary attributes. Place NULL beside the ContractType, Address, and Phone attributes.

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

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 99 APPLYYOURKNOWLEDGEAPPLY YOUR KNOWLEDGE implementation of the database will be a major exam focus. KEY TERMS client/server Relational Database Management System (RDBMS) entities attributes relationships entity decomposition Primary Key one-to-one relationship one-to-many relationship many-to-many relationship Foreign Key normalization denormalization Exercises Employe. Course Employe. I. Cours. I. Firs. Nam. Employe. I. Las. Nam. Nam. Stree. Addres. Locatio. Cit. Cos. Stat. Availabl. Zi. Cod. Gende. Dat. Registere. Dat. o. Birt. SS. Nam. o. Cours. Paymen. Du. 2.1 Creating a One-to-Many Relationship The purpose of this exercise is to show you how to create a basic one-to-many relationship between the Employee entity and the Course entity. Employees teach courses at Lloyd s Hospital. A single employee or teacher may teach many courses, such as blood composition and blood circulation, therefore creating a oneto- many relationship. Estimated Time: 5 minutes. 1. Draw out the two entities, Employee and Course, as boxes, including the attributes defined in each as rows inside the box. Define a Primary Key element for each entity. 2. Creating a Foreign Key is required on the child table (Course) so that it can be related to the parent table (Employee). Create a Foreign Key named EmployeeID on the Course table that references the EmployeeID Primary Key in the Employee table. 3. Draw a line from the Employee entity to the Course entity, making a large dot at the end of the line pointing to Course. This indicates a oneto- many relationship, as shown in Figure 2.13. 2.2 Creating a Many-to-Many Relationship This exercise demonstrates how to create a many-tomany relationship between the Trainee entity and the Course entity. Trainees enroll in courses taught at Lloyd s. A single trainee can enroll in many courses, and at the same time many trainees can enroll to a single course, thus developing a many-to-many relationship. Estimated Time: 5 minutes. 1. Draw out the entities participating in this exercise; that is, the Trainee and Course entity. FIGURE 2.13 An example of a one-to-many relationship.

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

98 Part I EXAM PREPARATION CHAPTER SUMMARY operations to print reports. Normalization can also be a detriment to server performance. In these instances, planned denormalization can provide for easier application development and better performance. To complete the logical modeling, you define the data integrity rules. As you move toward the finalization of the logical model and into the physical design, you need to make other decisions based on data types and application development. The process is never really finalized. As the database goes through its life cycle, changes will be needed to improve performance and/or meet the changing needs of the business. The next chapter elaborates on the integrity and design specifics for SQL Server. The physical foundations of database normalization, dependencies, and redundancy. After a moment of thought, you assume that the design did not even go through the process of database normalization, for if it did, all the deficiency and lack of flexibility would have been eliminated. You decide to normalize the database until you find its best fit: the third normal form (3NF). You resolve all dependencies and interdependencies and eliminate attribute redundancy. Not only do Jones and his users see a great improvement in the overall data retrieval and modification, but they also notice an immense improvement in the hard disk space the database consumes, which is due to the effectiveness of the removal of repeated data. This chapter covered a lot on data modeling and database design concepts. You should have a firm grasp that the hub of data modeling circles is attributes and relationships. Nonetheless, other concepts were also covered. Entities become tables in the physical design. Attributes become the columns of the tables. Relationships are used in the same manner in the physical database as they are in the logical model. Normalizing the ER model helps remove redundant data. This normalization, however, can cause many small entities that need complex

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

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 97 ESSENCE OF THE CASE: . Current data design is devoid of fast access. . Jones & Sons handles more clients each day and the processing is getting difficult. . They need a re-creation of the data model. CASE STUDY: JONES & SONS source through the use of OLE-DB and ODBC drivers, which are available for most common databases. SQL Server can move data to and from these sources using Data Transformation Services (DTS), replication, and linked server operations. SQL Server can act as a gateway to any number of data sources and either handle a copy of the data itself or pass the processing to the third-party source. The capability of SQL Server to act with almost any third-party source means that existing applications can continue to function in the environment undisturbed while SQL Server applications can also make use of the same data. SCENARIO Starting as a small local bead trading company, Jones & Sons now exports and imports beads nationwide with headquarters in Detroit. Jones knew that the company would expand sometime in the distant future and so didn t worry too much about the inventory storage design he would use. His slothfulness backfired on him. There are many client interactions each day, and the current system, a poor inventory storage design created at an extremely low cost by one of the junior developers in town, just can t handle the volume. Jones and his staff have decided to implement a new design based on the original groundwork already existing. They hire you to implement a new database design that will expedite access and reduce data redundancy. ANALYSIS You request a copy of the original database design to see the flaws placed in it by the previous developer. You spot numerous defects affecting the design, some of which defy the

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

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 97 ESSENCE OF THE CASE: . Current data design is devoid of fast access. . Jones & Sons handles more clients each day and the processing is getting difficult. . They need a re-creation of the data model. CASE STUDY: JONES & SONS source through the use of OLE-DB and ODBC drivers, which are available for most common databases. SQL Server can move data to and from these sources using Data Transformation Services (DTS), replication, and linked server operations. SQL Server can act as a gateway to any number of data sources and either handle a copy of the data itself or pass the processing to the third-party source. The capability of SQL Server to act with almost any third-party source means that existing applications can continue to function in the environment undisturbed while SQL Server applications can also make use of the same data. SCENARIO Starting as a small local bead trading company, Jones & Sons now exports and imports beads nationwide with headquarters in Detroit. Jones knew that the company would expand sometime in the distant future and so didn t worry too much about the inventory storage design he would use. His slothfulness backfired on him. There are many client interactions each day, and the current system, a poor inventory storage design created at an extremely low cost by one of the junior developers in town, just can t handle the volume. Jones and his staff have decided to implement a new design based on the original groundwork already existing. They hire you to implement a new database design that will expedite access and reduce data redundancy. ANALYSIS You request a copy of the original database design to see the flaws placed in it by the previous developer. You spot numerous defects affecting the design, some of which defy the

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