Web Hosting Java Blog

OBJECTIVES The software to be used is not necessarily relevant to the database design, but because the software, SQL Server 2000 Enterprise Edition, is predetermined in this case, the features and limitations of the product as they relate to the database structure have been brought into this chapter. A complete breakdown of all SQL Server data types is covered here as a lead-in to Chapter 3, which covers SQL Server specifics in much more detail. Define entities. Considerations include entity composition. Specify entity, attributes, and relationships in a logical model. . This objective deals with creating and identifying entities and attributes in a data model. The coverage of this objective teaches you how an entity behaves during the three prominent phases of database normalization. Define entities. Considering normalization and denormalization. Specify degree of normalization. . For this objective, you will have to fully normalize a database structure and then decide whether denormalization of a database is appropriate in a given situation. Design entity keys. Considerations include FOREIGN KEY constraints, PRIMARY KEY constraints, and UNIQUE constraints. Specify attributes that uniquely identify records. Specify attributes that reference other entities. . Here you will learn how to undergo the process of creating FOREIGN KEY and PRIMARY KEY constraints and deciding on ways to uniquely identify records. You have to decide on which attribute or attributes will be required to uniquely identify records. This objective requires you to know how CHAPTER 2 Database Design for SQL Server 2000

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

OBJECTIVES The software to be used is not necessarily relevant to the database design, but because the software, SQL Server 2000 Enterprise Edition, is predetermined in this case, the features and limitations of the product as they relate to the database structure have been brought into this chapter. A complete breakdown of all SQL Server data types is covered here as a lead-in to Chapter 3, which covers SQL Server specifics in much more detail. Define entities. Considerations include entity composition. Specify entity, attributes, and relationships in a logical model. . This objective deals with creating and identifying entities and attributes in a data model. The coverage of this objective teaches you how an entity behaves during the three prominent phases of database normalization. Define entities. Considering normalization and denormalization. Specify degree of normalization. . For this objective, you will have to fully normalize a database structure and then decide whether denormalization of a database is appropriate in a given situation. Design entity keys. Considerations include FOREIGN KEY constraints, PRIMARY KEY constraints, and UNIQUE constraints. Specify attributes that uniquely identify records. Specify attributes that reference other entities. . Here you will learn how to undergo the process of creating FOREIGN KEY and PRIMARY KEY constraints and deciding on ways to uniquely identify records. You have to decide on which attribute or attributes will be required to uniquely identify records. This objective requires you to know how CHAPTER 2 Database Design for SQL Server 2000

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

Chapter 1 INTRODUCTION TO SQL SERVER 2000 51 A PPLY YOUR K NO WLEDGE 3. A. You should always avoid installing SQL Server on a domain controller. Although the configuration is possible, there are serious security implications in having an application server on the same machine as a network s security context. For more information, see SQL Server 2000 as a Resource. 4. E. To use indexed views you must have either the Developer or Enterprise Editions. Because this server is for a production environment, not a test setup, the only choice is the Enterprise Edition. For more information see the section SQL Server Versions. 5. C. The Personal Edition is probably most appropriate for this type of application because it is suited to mobile users that require SQL Server for data storage. However, if the application is using replication as a means of moving data from the laptops to the servers, it should be noted that the Personal Edition allows only for the subscriber options to be set. It cannot act as a publisher. For more information see SQL Server Versions. 6. C. XML, now supported through a number of new features, provides a mechanism where the data can be transmitted from one application to the other while maintaining the data structure and other formatting provided by XML schemas and style sheets. For more information, refer to Features of SQL Server 2000. 7. B. To perform analysis of this type, you would use the Profiler to gather detailed server-wide information. The Query Analyzer is more appropriate to analyze and improve upon the performance of singular queries and the Analysis Manager is used to configure data warehousing. For more information see the section Features of SQL Server 2000. Suggested Readings and Resources Holzner, Steven. Inside XML. New Riders Publishing, 2001. SQL Server 2000 blogs Online Getting Started with SQL Server blogs Online What s New in Microsoft SQL Server 2000 SQL Server Architecture Overview XML and Internet Support Overview MSDN Online Internet Reference: (http://msdn.microsoft.com) XML Online Developer Center: (/xml/default.asp) Developer Resources for SQL Server: (/sqlserver) Technet Online Internet Reference: IT Resources for SQL Server (http://www. microsoft.com/technet/sql/default.asp) MS Press Online Internet Reference: Learning and Training Resources for IT Professionals / SQL Server 2000 (http://mspress.microsoft.com/ it/feature/100500.htm)

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

50 Part I EXAM PREPARATION A PPLY YOUR K NO WLEDGE 7. You are working on a database implementation in a production environment. You would like to perform analysis on the server hosting the database. You need to get detailed information on the types of queries being performed and the locking effects of all operations. Which tool should you use? A. Query Analyzer B. SQL Profiler C. blogs Online D. Analysis Manager E. Enterprise Manager Answers to Review Questions 1. Data can be read from a SQL Server database using a variety of techniques. The most common would be a front-end application designed with a Windows or Internet interface. Visual Basic, FrontPage, or Visual Interdev could be used to design such a front-end application. You can also query data using the OSQL command-line tool or use the Query Analyzer. 2. It probably goes without saying that each version has a specific focus. The CE version, of course, is for use on handheld computers that use Windows CE version 2.11 or later. The Developer Edition installs on Windows 2000 Professional and Windows NT Workstation 4.0, as well as on Microsoft Windows NT 4.0 servers and Windows 2000 servers. Personal Edition runs on non-server operating systems including Windows 2000 Professional, Windows NT Workstation 4.0, Windows Millennium Edition, and Windows 98, as well as servers. 3. The answer here is likely to be very close to 100%. SQL Server blogs Online is an immense and complete SQL Server resource. Just about any question you have or database technology you would like to use or learn can be found within the application. 4. The Master database represents a given SQL Server installation. The Master database should be considered as important or more important than the most mission-critical database on the server. The Master database contains information for all databases and other objects stored on the server. 5. The Model database acts as a template for each newly created database. Every time you create a database, a copy of the objects and settings stored in the Model is used as a basis for the newly created database. Answers to Exam Questions 1. B. To install SQL Server you need a minimum of a Pentium 166, 64MB of RAM (though more is recommended) and Internet Explorer 4 or above. See the section Installation Requirements for more information. 2. A, D. OLE-DB and ODBC are industry- standard technologies. They supply the standards for drivers that are supplied to allow data to be read from an underlying data source. ODBC (open database connectivity) is a mature interface supported by almost all database engines. OLEDB is a set of driver APIs that has growing usage and allows for access to data in a generic form, as well. See the section Executing a Program for more information.

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

Chapter 1 INTRODUCTION TO SQL SERVER 2000 49 APPLY YOUR KNOWLEDGE C. ISO D. ODBC E. Analysis Services F. SQL Profiler 3. You are preparing a computer that is going to serve as a Windows 2000 domain controller. You would also like to install SQL Server for a database that will be used in your office. What is the preferred installation to use for these operations? A. Use one machine as a domain controller and a second as the database server. B. Install SQL Server first and then promote the machine to a domain controller. C. Promote the Windows 2000 machine to be a domain controller and then install SQL Server. D. A Windows 2000 domain controller cannot have SQL Server installed. 4. You are implementing a database for a chain of variety stores that share a central warehouse. Each of the stores tracks sales information and places orders from the central warehouse once a week. The server will use replication, indexed views, and analysis services for a variety of procedures. What version of SQL Server should be installed? A. SQL Server 2000 CE B. SQL Server 2000 Standard C. SQL Server 2000 Personal D. SQL Server 2000 Developer E. SQL Server 2000 Enterprise 5. You are a SQL Server database administrator preparing to install a series of laptops that will be used by your company s traveling sales force. The laptops are already loaded with Windows 98 and need to have SQL Server installed so that the salespeople can record orders through a custom Visual Basic application. When the salespeople return to the office, they execute another process to upload the information to a primary production server. What version of SQL Server do the laptops require? A. SQL Server 2000 CE B. SQL Server 2000 Standard C. SQL Server 2000 Personal D. SQL Server 2000 Developer E. SQL Server 2000 Enterprise 6. A large shipping company uses a dual processor SQL Server to track load information for a fleet of transport vehicles that handle shipments throughout North America. The data being collected is shared with other vendors. This company requires a technology that will provide a data structure and formatting rules, and that will be easily transferable between applications. Which technology is best suited for this structure? A. HTML B. IIS C. XML D. Replication E. Triggers

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

48 Part I EXAM PREPARATION APPLY YOUR KNO WLEDGE 2. Expand the SQL Server Group, Server, Databases, and Northwind database and select the Diagrams container. 3. Right-click and select New Database Diagram. Select Next to bypass the introductory screen. 4. Select the Add Related Tables Automatically check box and leave the number of levels at the default of 1. 5. Select the Customers table and select Add. Select Next and Finish to draw the diagram. Database diagrams are useful when you want to provide documentation about a database system. The diagrams show relationships and have a number of other useful features you can use to augment a diagram. 1.4 Using blogs Online In Exercise 1.4, you can explore blogs Online and get used to using it as a learning tool. Estimated Time: 5 minutes. 1. Open blogs Online by choosing Start, Programs, Microsoft SQL Server. 2. Select the Contents tab and expand the Getting Started blog. 3. Select the Getting Started blog and read through the table to review the hyperlinks available. 4. Select the Index tab and enter the word SELECT. 5. Double-click Examples from the list of subheadings. 6. Select the Favorites tab and click Add to place SELECT Examples into the listing of Favorites. You are sure to add and remove topics from your Favorites list on a regular basis as you study the product. Review Questions 1. How is data queried from SQL Server? 2. Which versions of SQL Server have operating system requirements? What are these requirements? 3. What percentage of answers to potential exam questions can be obtained from within blogs Online? 4. How is the Master database used? 5. What is the purpose of the Model database? Exam Questions 1. You are the chief database administrator for a large manufacturing company. You need to install Microsoft SQL Server Enterprise for test purposes and have moderate storage capabilities. Which of the following systems suit your needs without requiring you to perform any alterations? A. Pentium 133, 256MB RAM, IE 5 B. Pentium 200, 64MB RAM, IE 4 C. Pentium 400, 128MB RAM, IE 3 D. Pentium 400, 32MB RAM, IE 5 2. You are the database administrator for a small private educational institution. You would like to use SQL Server as a gateway to a variety of data sources that have been used for a number of different applications. As a primary goal you would like to get a copy of all data stored on the SQL Server. What technologies could be used to solve this problem? Select all that apply. A. OLE-DB B. ANSI

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

Chapter 1 INTRODUCTION TO SQL SERVER 2000 47 APPLY YOUR KNOWLEDGE Exercises 1.1 Using the Enterprise Manager In Exercise 1.1, you explore the Enterprise Manager interface to become comfortable with its layout. Estimated Time: 5 minutes. 1. Open the Enterprise Manager and view the structure of the interface by choosing Start, Programs, Microsoft SQL Server. 2. Expand the SQL Server Group and Computer name by depressing the plus sign to the left of the name. 3. Expand databases and you should see the listing of system databases: Master, Model, Msdb, Tempdb, and the sample databases Northwind and Pubs. You may also see other user databases that have been previously created and the system s Distribution database if replication has been enabled. 4. Expand the Northwind database and view the variety of database object storage containers. 5. Click on tables to show the user and system tables in the right view pane. 6. Click on the Type column to sort the tables according to type. (You may have to click twice so that the user tables are all located at the top of the list). Notice how easy it is to adjust the objects and place them into a desired sequence by name, owner, type, or creation date. 1.2 Using the Query Analyzer In Exercise 1.2 you will run a couple of basic queries from the Query Analyzer and explore its interface. Estimated Time: 5 minutes. 1. Open the Query Analyzer by choosing Start, Programs, Microsoft SQL Server. 2. Select OK to connect to the local server and supply login credentials if necessary. 3. On the left side of the screen is the object browser that enables the user to quickly look up names and syntax while creating and editing scripts. 4. Select the Northwind database from the Database drop-down list box in the toolbar. 5. Enter the following T-SQL query: SELECT * FROM Customers. 6. Run the query by pressing F5. (Alternatively, you can also select the Execute Query button from the toolbar.) You can save the query if you want. It is also possible to save the query results or run the results straight to an output file, which you can do by selecting Results to File in the Execute Mode drop-down list box on the toolbar, prior to running the query. 1.3 Creating a Database Diagram In Exercise 1.3, you will draw a database diagram from the sample database, Northwind. Estimated Time: 5 minutes. 1. Open the Enterprise Manager and view the structure of the interface by choosing Start, Programs, Microsoft SQL Server.

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

46 Part I EXAM PREPARATION CHAPTER SUMMARY HTML IIS index INSERT Internet host name OSQL Master database Microsoft Management Console (MMC) Model database MSDB database NetBIOS computer name ODBC OLE-DB online analytical processing (OLAP) online transaction processing (OLTP) partitioned views Profiler Query Analyzer relational database replication roles SELECT server network utility Service Manager stored procedures Structured Query Language (SQL) table TCP/IP tempdb Transact-SQL trigger UPDATE user-defined functions view XML

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

Chapter 1 INTRODUCTION TO SQL SERVER 2000 45 CASE STUDY: ACME GENERATORS uses ODBC and OLE-DB functionality to gain access to virtually any data stored anywhere. SQL Server makes an excellent back-end database server for an Internet or intranet site. SQL Server and IIS integrate well to make data accessible over the Internet in a number of formats, including XML, HTML, and ADO. With SQL Server replication, data can be transferred quickly and flexibly from one server to another. Options are available to maintain updateable copies at all locations if desired. tasks. What are some of the features available in SQL Server that are suited to this deployment? ANALYSIS Microsoft SQL Server 2000 would be an excellent software choice in this situation. It handles well in a distributed environment so that all offices can gain access to the data they need. SQL Server has many tools for interoperating in a heterogeneous environment that has multiple vendors and database products. SQL Server continued CHAPTER SUMMARY This chapter was aimed at giving you an introduction to SQL Server. The tools and programs talked about in this chapter span the rest of this blog and are examined at much greater depth. Use this chapter as a springboard to all the topics that you need to understand to be properly prepared for the 70-229 exam. This chapter has only outlined the basics of what is a large and diverse area of expertise. KEY TERMS ADO Analysis Server BCP blogs Online Cascading Referential Integrity Client Network utility collation constraints Data warehouse DELETE English Query Federated Database Server

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

44 Part I EXAM PREPARATION SQL Server 2000 Enterprise Evaluation Edition TIPEXAM The Enterprise Evaluation Edition is a full-featured version available The Edition for the Exam This by a free download from the web. It is intended only for use in eval exam is based on the functional uating the features of SQL Server and stops running 120 days after ity provided by the SQL Server it is initialized. Enterprise Edition. In preparing for the exam, you should have this edition available for installation and testing. ESSENCE OF THE CASE Here are the essential points in this case: . Distributed environment . Heterogeneous data sources . Ability to integrate with IIS . Data replication CASE STUDY: ACME GENERATORS SCENARIO ACME Generators manufactures small electronic motors for use in a variety of mechanical devices. It is a medium-sized company of 150 employees with a manufacturing plant in Idaho and sales offices in Michigan, California, and Brazil. They require a database server to store all company data, provide access to data in sources across multiple platforms, and provide for front- end applications that will later be developed in Visual Basic and web applications developed for use on an IIS Server. The software chosen must be able to handle data replication to and from the sales offices while maintaining ongoing updates from a fully automated manufacturing floor. Procedures will be updated to include information coming in from monitoring equipment that reports at various points during the manufacturing process. The company executives have been looking into Microsoft SQL Server 2000 and would like to get information on how suitable it would be for the

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