ICAS5192A Configure an internet gateway

Unit contents

An internet gateway is a device that connects internal private networks to the outside world via the Internet. It translates and converts messages from one protocol to another. The Internet gateway is also there to protect the internal private network from harm. It is at the battle front, protecting important data and information from attack, be it by email, viruses or worms, and hackers. An internet gateway can also provide proxy services, which is a means of reducing network costs by caching internet pages. Without internet gateways, you would not be able to send emails, look at Web Pages or use any web services.

This unit (ICAS5192A) will give you the knowledge and skills to implement and manage security on an operational system. You will learn how to do the following:

• confirm client requirements and network equipment
  
• review security issues relating to Internet connectivity
  
• install and configure a gateway
  
• configure and test node to use gateway.

Unit topics

The topics for this unit are as follows:

1. Confirm client requirements and network equipment

2. Review security issues

3. Install and configure gateway products and equipment

4. Configure and test node

In this topic you will learn how to assign nodes to a specific gateway, determine the connection type and configure with reference to network architecture and ensure node software and/or hardware is configured.

1. Confirm client requirements and network equipment

In this topic you will learn how to confirm and validate client requirements, determine the scope of Internet services with reference to the client requirements, and finally, identify and verify the gateway equipment specification and product availability.

Activity 1.1 Confirming client’s requirements

A friend wants you to make a recommendation on what can be done to allow easy access to the Internet from both of the family’s home computers. Read up on Microsoft’s Home and Small Office Network Topologies at http://search.technet.microsoft.com/search/default.aspx?siteId=1&tab=0&query=network+topologies and determine the appropriate options for your friend. Set out the considerations you make for the various requirements that your friend may have.

onsider under what circumstances you would recommend the following solutions:

• residential gateway
  
• using a host computer with ICS (Internet connection sharing)
  
• using a host computer with another Internet sharing program
  
• individual dial-up connections for each computer.

A: Some of the requirements to consider include

• operating systems used
  
• connection method to the Internet (broadband, dial-up, wireless broadband)
  
• common times of use
  
• location of computers to each other
  
• phone and network connections.

This can be best represented in a table.

Table 3: Considerations and recommendations

Of course, every situation is different. Some may require a greater investment in infrastructure in order to provide the services required. Also, there is no reason to prevent a residential gateway from being used with a dial-up connection as long as the device is able to support a serial port for a modem or ISDN terminal adapter such as various mainstream routers and the Open Networks (http://www.opennw.com/index.php) OPEN524R router. These devices use the serial port as a backup WAN connection in place of a failed broadband link, but can be used without broadband at all for ISDN dial-up connections.

Activity 1.2 Examining high-end enterprise appliances

To gain an insight into the variety of devices available for larger business and enterprise situations, have a look at the following demonstration from Cisco about their ASA (adaptive security appliance) product range at http://www.cisco.com/cdc_content_elements/flash/asa/flash.html(Cisco ASA demo)

This demo requires Macromedia Software Flash to be installed and will take approximately seven minutes for the Introduction section to download on a dial-up connection. It will take longer if other downloads are also being processed. If the demo is unavailable you might try http://www.cisco.com/go/asa for more information.

A: From the demonstration, you can see that products such as Cisco ASA range have a multipurpose capability that allows them to be distributed as a solution to many different needs in an organisation. A key feature for enterprise use is the central control of remote devices and automatic product updates.

Similar products are available from McAfee and Symantec, to name a few. Virtually all network infrastructure manufacturers will have a range of products to perform gateway functions of some level. Some examples are http://www.mcafee.com/au/products/mcafee/antivirus/internet_gateway/ws_appliances_3000.htm (McAfee – Webshield 3000 Series Appliances)

http://www.mcafee.com/us/products/tools/demos/ws_appliance/ws_appliance.asp (Macromedia Flash demo)

http://www.symantec.com/enterprise/products/allproducts.jsp (Symantec – Gateway Security 5400 Series. Click on the Symantec Gateway Security 5400 Series link.)

Activity 1.3 Validating client requirements

This scenario applies to Activity 3 and Activity 4. Read the scenario and answer the questions that follow.

Compstat is an SME that provides market research to over 100 clients Australia-wide. Compstat’s head office is located in Perth and has three remote offices located in Sydney, Melbourne and Brisbane. Currently, remote sites are connected to the head office via ISDN links. They are looking to upgrade their network to utilise new applications that have improved data-gathering
methods. Currently, market research participants fill in a paper-based form that is then transferred into electronic format by data entry personnel. Compstat wants to change this paper-based system to a computer-based system that utilises web technologies. This will allow the
collection and storage of research data in one step instead of many, saving time and money.

Compstat wants to be able to provide a computer kiosk system where the participant completes the questionnaire online in a remote area like a shopping centre. They want to use wireless broadband technologies to connect the kiosk computers to the Compstat web servers anywhere and anytime wireless broadband access is available. This environment will need to be safe and secure.

Q: Are the client’s requirements valid? Can they be fulfilled? Refer to the following document: Client Requirements - Sample Validating Client Requirements (23 KB 2821_reading1.xls)

A: Yes, the client’s requirements are valid. They can be filled using a range of multiple mobile technologies.

Activity 1.4 Scope of Internet services required

Q: To practise determining the scope of Internet services required, refer back to the scenario in Activity 3 and fill in the document Client Requirements - Sample Scope of Internet Services

(1.21 MB 2821_reading2.xls)

A: The level of detail in this tool is still incomplete? As I learn about other existing and new technologies, I still need to modify the tool in order to effectively record a client’s requirements for an Internet gateway.

Activity 1.5 Identify suitable components

Make a comparison of the specifications of the following products and identify what Internet gateway services they are suitable for.

Download the product specification sheets, datasheets and/or user guides or manuals for these products:

Home and small business components

TP-Link – TL-460 multifunction router http://www.tp-link.com/. Click on the Cable/DSL Routers image then click on the TL-460 image.

MSI – Residential Gateway http://www.msicomputer.com.au/. Search for RG54GS and select the appropriate result link.

Billion – BiPAC 5200 ADSL2+ Modem/Router http://www.billion.com/product/adsl.htm. Click on the BiPAC 5200 image.

Enterprise components

Cisco – ASA http://www.cisco.com/go/asa. Scroll down to related documents and click Datasheets. Click on the ASA Platform and Module datasheet link, then download the PDF or read the web page.

Symantec – Gateway Security 5400 Series http://www.symantec.com/enterprise/products/allproducts.jsp Click on the Symantec Gateway Security 5400 Series link.

A: Comparing these devices, I see that the specifications concerning what can be done from an Internet gateway or router point of view is very similar across the board from home and small business up to enterprise level. However, the data speeds and the few additional processing functions of the enterprise appliances set them apart. The additional capacity of some enterprise appliances to actively detect worms and viruses and other threats makes these devices come at a price and may not be justifiable to a home or small business client.

2. Review security issues

In this topic you will learn how to assess security features of Internet gateways with reference to architecture and the security plan and review security measures with the Internet service provider with reference to firewalls and other measures. You will also learn how to brief users on the security plan with reference to Internet use and hazard possibilities.

Activity 2.1 Assess Internet security for home or organisation

Examine the security features of an Internet connection you have access to by researching and answering the following questions:

• What do you use to share Internet access at your home or business?
• Is there a network administrator or ‘computer person’ that you can ask some information from at work?
  
• What services are provided from your side of the Internet link?
  
• Are there open ports for special programs?
  

You might also find the following sites helpful in making your decision:
http://www.cert.org/tech_tips/home_networks.html (CERT – Home Network Security)
http://www.webcamsoft.com/en/faq/firewall.html (Configure for DMZ servers)
http://www.haxial.com/faq/routerconfig (Port forwarding examples)
http://www.cisco.com/en/US/products/sw/secursw/ps2120/products_configuration_guide_chapter09186a00801162eb.html (Configuring PIX firewall)
http://www.portforward.com/help/porttrigger.htm (Explanation of ports, NAT and port forwarding)
http://www.portforward.com/help.htm (Basic help and definitions)
http://www.irchelp.org/irchelp/security/fwfaq.html (Firewall FAQ)

A: Were you able to determine the aspects of your Internet security provision at home or work? There are many answers to the creation of Internet security. Perhaps you have one or parts of several of the following solutions:

• MS Windows system on a dial-up connection with a software firewall
  
• Internet connection sharing (ICS) through a dial-up connection with firewalls on every system
  
• broadband connection with a router with NAT enabled
  
• broadband modem connected to one system with a software firewall and ICS running
  
• broadband connection with NAT router and firewall device routed through a server providing DNS and anti-virus checking of the network traffic.

Activity 2.2 Access ISP security information

Check for information about the security arrangements provided by your ISP. Look for FAQs, information pages, connection details and similar pages in order to find out what security measures are in place at the ISP premises that could potentially affect you or your client.

• What does your ISP do for you?
• Do they provide virus scanning of emails?
  
• Are any ports blocked at their premises such as port 25 or others? Do they explain why they have done this?
  
• Do they provide static IP addresses?
  

A: Were you able to find the information? Some ISPs don’t advertise the fact that they block anything. You can determine if your ISP blocks port 25 by running the Telnet program and trying to connect to another ISP’s email server using port 25. For example in Windows you would do the following:

• click on Start -Run then type cmd into the command area and click OK. (or command on Windows 95, 98 or ME)
  
• in the command window type telnet mail.dodo.com.au 25 and press Enter.
  
• An unsuccessful connection will time out and show something like the following:

Telnet output shows that the mail.dodo.com.au mail server is not reachable using port 25 from this computer.

A successful connection will show something like the following:Telnet output shows a connection has been established with the mail.bigpond.com.au mail server on port 25

The images above show that access is possible to the mail server mail.bigpond.com.au but not to the mail server at mail.dodo.com.au.

Bigpond definitely blocks port 25, but you have to search for the information. Try the following to get the information: http://www.bigpond.com/ Type block ports into Search Bigpond and read the article on ‘Why does Bigpond manage the use of port 25

Activity 2. 3 Notifying users of Internet security measures

What is the best way to get the information across? You will provide different formats for the security measures depending on your method of deployment of the information. Have a look at the following sites and see the range of information you may need to be providing:

Search Google for technology acceptable-use policy within Australia:

• Security policies – guides and examples http://www.infosyssec.org/infosyssec/security/secpol1.htm (Information security portal for information system security professionals.) Note that many links do not work so keep trying different ones.
• Site security policy development: http://www.windowsecurity.com/whitepaper/info/policy/AusCERT.html (Window Security site)
• Computer and information security policy: http://www.windowsecurity.com/whitepaper/info/policy/hk_polic.html (Window Security site)
  

For the different methods listed in the Reading notes, describe how you may get this information across.

These methods were

• induction packages for employees
  
• seminars
  
• emails
  
• log-on notices
  
• messages of the day
  
• default home page.

Q: Write your answers below:

A: There could be various answers here. Some will be more effective than others depending on the audience as well as the content. Here are a few ideas:

Table: Methods of delivery and information formats

3. Install and configure gateway products and equipment

In this topic you will learn how install and configure gateway products as required by technical guidelines, plan and execute tests, and analyse error reports and make changes to the gateway.

Activity 3.1 Terminology used to set configuration of devices

Q: The following link is for a manufacturer of a proprietary Internet phone system. Their software requires routers or firewalls to be configured to allow the service to be accessed from the Internet on their client’s computers. The feature that allows this is often called port forwarding.

• Click on the link provided below and scroll down to the bottom of the page where you will find links for a variety of routers and firewalls.
• Click on each of these links in turn (use the Back button in between) and assess the differences in terminology and the logical grouping of services in the various menu systems used in these routers and firewalls.
• Specifically, identify the port forwarding references and create a table with the alternative naming, description and grouping for each of the router and firewall products and devices listed.

A: The pages for the different routers and firewalls show various options for port forwarding to be configured, such as those shown in the next table.

Table: Devices and terminology


Activity 3.2 Exploring Linux gateways

Q: Research some of the Linux gateway solutions shown in the Reading notes. Click on each of the links and investigate the features and licensing for the various products offered. Produce a table with a basic summary of your findings.

• http://www.simonzone.com (SimonZone Guarddog)
• http://www.coyotelinux.com (Vortech Consulting Coyote Linux)
• http://www.clarkconnect.com (Point Clark Networks’ ClarkConnect)
• http://www.coyotelinux.com (Vortech Consulting Wolverine Linux)

A: Each of the products has differing requirements in both the knowledge needed to install them and the ongoing support given. Generally, if a payment and annual fee is required, then support will be more dependable. (You get what you pay for.) The free products are not necessarily inferior to the commercial offerings—often they only differ in the support offered.

GuardDog requires a working Linux installation before it can be used, so a working knowledge of Linux is required in order to install and configure the Internet gateway.

The other solutions are installed on dedicated systems. Coyote Linux is the least resource hungry and may be installed on an otherwise-disused computer system with two network cards.

Both Clark Connect and Wolverine Linux will benefit from a more powerful system, depending on the final level of performance required by the Internet gateway. So a system with a small hard drive and memory may be suitable without purchasing new equipment specifically for the Internet gateway to be set up on.

Activity 3.3 Enterprise appliances

Q: Research some of the enterprise appliances available from the following manufacturers. Find information on the firewall and VPN throughput and the maximum number of connections.

• Cisco Systems: http://www.cisco.com – search for “Adaptive Security Appliances Models Comparison” and follow the resulting links to locate detailed specifications on an ASA product.
  

Table: Cisco Adaptive Security Appliance – ASA 5510 specifications

• Symantec Systems: http://www.symantec.com – search for "Symantec Security Appliances Comparison Chart" and follow the resulting links to locate detailed specifications on an appliance product and get the actual comparison chart from the resources list at the bottom of the page.

Table: Symantec Gateway Security – SGS 5420 specifications

Activity 3.4 Plan and execute tests

Q: Download and open the Test Plan – Sample Workbook and try the test links while your Internet connection is open. Test Plan - Sample Workbook (19 KB Test Plan_Sample Workbook.xls)

• Practise filling in the workbook as you perform the tests.
• Do all the tests work?
• What other tests would be helpful in this test tool?

A: Practise filling in the workbook by

• saving the sample test plan with a new file name
• changing the date heading to reflect the date when you performed the tests
• filling in either Pass or Fail in the results column under the date you just entered.

Most connections to the Internet should allow all of these tests to succeed.

Additional tests that would extend the usefulness of the test tool include

• trial downloading of various file types – ZIP, EXE, COM
• trial using of different communications programs – MSN Messenger, ICQ, SSH, Telnet, BitTorrent.

4. Configure and test node

In this topic you will learn how to assign nodes to a specific gateway, determine the connection type and configure with reference to network architecture and ensure node software and/or hardware is configured.

Activity 4.1 Determine the IP configuration method

In order to determine how the IP configuration is obtained on a Microsoft Windows XP system we first have to log in as an unrestricted or administrative level user.

Once you have logged in

• go to Start -Control Panel
• from the control panel list, open the Network Connections option. This will open a window with a Dial-up section and/or a LAN or High-Speed Internet section.

Note: If control panel displays in Category View, you will have an additional step of opening the Internet and Network Connections option before opening the Network Connections option.

Part 1 – Dynamic IP settings

Most dial-up connections are configured as dynamically-allocated IP addresses, so if you have a Dial-up section with a connection present

• right-click on a connection and select Properties from the pop-up menu
• select the Networking tab from the dialog then open the Internet Protocol (TCP/IP) by selecting it from the list and clicking on the Properties button.

In most cases this Properties dialog will show that the options Obtain an IP address automatically and Obtain DNS server address automatically are selected.

Important: Leave these settings as they are by clicking the Cancel buttons until the Network Connections list is displayed again!

A: In Part 1 you should have moved through and displayed the TCP/IP Properties dialog for a Dial-up connection and obtained a dialog similar to the following:

Part 2 – Static IP settings

The IP address configuration can be statically (or manually) allocated.

• If you have a connection in the LAN or High-Speed Internet section, then right-click on a connection and select Properties from the pop-up menu.
• Select the Networking tab from the dialog then open the Internet Protocol (TCP/IP) by selecting it from the list and clicking on the Properties button.

In many cases, this Properties dialog will show that the options Obtain an IP address automatically and Obtain DNS server address automatically are selected.

Change the selected options to the following:

• Use the following IP address and use the following DNS server addresses. Notice that the IP address fields become available to take the static IP address information including the IP address, Sub-network mask, default gateway address and the Preferred DNS server address.

Important: Leave these settings as they are by clicking the Cancel buttons until the Network Connections list is displayed again!

A: In Part 2 you should have moved through and displayed the TCP/IP Properties dialog for a LAN or High Speed Internet connection. By selecting the options Use the following IP address and Use the following DNS server addresses, you should have obtained a dialog similar to the following:

Part 3 - Current values

In order to determine the current values being used by the system, a command line tool is available.

Open a command prompt window by doing the following:

• Start, Run, type cmd in the Open field and click on the OK button. This brings up a black command prompt window.
• at the flashing prompt, type ipconfig /all and the current values will all be displayed.

A: In Part 3, the IP settings should be displayed in the command prompt window similar to the following:

Activity 4.2 Configuring Internet Explorer to use a proxy server

Internet Explorer is integrated into the Windows operating system to the degree that you do not need to open Internet Explorer to set parameters. To set the proxy server settings for Internet Explorer on a Microsoft Windows XP system you should

• log in as an Unrestricted or Administrative level user
• go to Start then Control Panel
• from the Control Panel list, open Internet Options and select the Connections tab.

Note: If Control Panel displays in Category View, you will have an additional step of opening the Internet and Network Connections option before opening Internet Options.

This will open a dialog with a Dial-up and Virtual Private Network settings section and a Local Area Network (LAN) settings section. For this activity you can choose an available Dial-up setting and click on the Settings button or click on the LAN Settings button. The difference between the two dialogs is in the Dial-up including fields for the User name and Password for the connection.

To activate the use of a proxy server

• click on the check box under Proxy server beside the instruction Use a proxy server for this connection
• this activates the fields that allow you to enter the IP Address and the Port number for the HTTP proxy server
• you can also activate to bypass the proxy server for local addresses by clicking on the Advanced button. You can configure different server addresses and ports for the different protocols displayed.

Important: Leave these settings as they are by clicking the Cancel buttons until the Control Panel is displayed again.

A: There are a number of different ways to open the proxy settings dialogs. Each connection can be configured with a different set of parameters. Most DHCP servers cannot be used to supply this information to a DHCP client. You should have obtained a dialog for the proxy settings similar to the following:

Activity 4.3 Testing completed node capabilities

The testing tool that you created in order to test the operation of the gateway can be used in the testing of each node as well. Download and open

• Test Plan - Sample Workbook (20 KB 2824_reading01.xls)

Practice filling in the workbook as you perform the tests.

• Do all the tests work?
• What other tests would be helpful in this test tool?

A: Practice filling in the workbook by

• saving the Sample Test Plan with a new file name
• changing the date heading to reflect the date on which you perform the tests
• fill in either Pass or Fail in the results column under the date you just entered.

Additional tests that would extend the usefulness of the test tool include

• trial downloading various file types – ZIP, EXE, COM
• trial using different communications programs – MSN Messenger, ICQ, SSH, Telnet, BitTorrent.

ICAB4170B Build a database

Amend a database application to meet client requirements

Overview

After implementation, it's often the case that you or your client finds that an application requires modification. Even a well-designed application may at some time need to be revised. This unit will help you to understand how to amend a database application as required to meet you client's requirements.


Amending an application

If you designed your application well, according to your client's requirements, why would you need to change it?

Modifications may be required for any of the following reasons:

• to remove errors or limitations
• to account for new or changed data
• to add new features
• to improve usability and productivity
• to streamline the application
• to facilitate the interaction of the application with other programs.

Types of changes

Some changes will be 'cosmetic' and easy to achieve, such as redesigning a form. Other changes, such as expanding the scope of the application to include new tables and reporting functions, may seem simple (especially to a client) but can actually require significant work. Some changes may even require the complete redesign of the application. Implementing changes at different stages of the development process will have varying consequences. In general, it's more difficult to make modifications once relationships have been established between the tables and data has been entered.

Making amendments to an application mirrors the development cycle discussed in an earlier topic, with the added provision that modifications must integrate seamlessly with the existing application. The application amendment cycle can be broken down into the following stages:

• consultation and analysis
• design
• implementation
• testing
• delivery.

Consultation and analysis

Initially you'll need to meet with the client or obtain a brief and any supporting materials, detailing the changes required. You then need to consider how you can provide a solution to the client's needs in the context of the existing application. This involves determining the feasibility and potential impacts of the proposed modification. You should ensure that:

• The modification is technically possible.
• The scope of the modification has been determined. For example, in a multi-user application that stores data on a server, changes to the data structures on the server (back-end) will affect the entire application, whereas changes to the front-end (the users' forms and reports) are more trivial.
• Existing data is preserved. If existing data and relationships are affected by the modification, what measures can you take to ensure that data integrity is maintained?
• The modification won't conflict with other parts of the application.
• The application flexibility is retained. Will the modification limit the potential for expansion of the database at a later time?

Application performance will not be adversely affected by the modification.
Make sure you keep the client informed about the cost and delivery time for any proposed modifications. These are often the most important factors in your client's decision to proceed with the changes.

After completing the analysis and design phases, you can implement and test the modification. Remember to always document any changes you make.

The revised application

You should then be ready to deliver the revised application to the client. Again, this should be carried out in sequence:

• backup the previous version of application-this will allow you to roll back to the original version if you encounter problems.
• make a general backup of systems that may be affected-in case your revised.
• application has unforeseen effects on the system.
• install the amended application on the client's computer(s).
• provide training for users in the operation of the amended application, if required.

Making changes to the Tru Blue agency application

In previous activities you completed the required design for the Tru Blue Agency, and implemented a working database application (Agency.mdb). Now, the agency has decided to incorporate into its database additional information about the screening costs for its advertisements. Your supervisor has met with the agency and has given you details of the changes required. You will need to make the specified changes to your design and implement the modifications.

Supervisor report on Tru Blue Agency application amendments

The agency's Accounting Department regularly receives a text file called ScreeningRatesTable.txt from the cable TV station. The file contains details on the advertisement screening costs charged by the cable TV station, and is used by the agency Accounting Department to prepare client invoices.
Download the file and store it on your local computer with your other files for this subject.

The text file is supplied to the agency in a comma-delimited format. The screen shot below shows the data format.

You can see from the sample file above that as the daily screening frequency increases, there is a corresponding decrease in the price of each screening. For example, an advertisement that's screened once per day costs $245.00 per screening, whereas an advertisement that's shown ten times per day is discounted to $150 per screening.

The agency General Manager now wants to be able to use this information in the agency database. In particular, he has asked that the current 'Brand Manager Summary' report be amended to include the total screening costs for each advertisement.

The Accounting Department is responsible for receiving and maintaining the data about screening costs. As the department acts as the central control for this information, it's been decided to link the text file they receive to the existing Tru Blue database. This will ensure that the screening cost data used in the database is always up to date, and means that no extra data entry is required when the file is updated in the Accounting office.

The required amendments

To meet the client's requirements, the following amendments are needed:

• link the text file named ScreeningRatesTable.txt to the Tru Blue Agency database, Agency.mdb.
• create a relationship between the linked ScreeningRatesTable and the tblAds table
• modify the qryBrandManagerSummary query to include a calculation of the total screening costs for each advertisement
• modify the rptBrandManagerSummary to use the new query.

Implementing the amendments-what you have to do

Now we'll look at the steps you'll need to take to carry out each of these tasks. To begin with:

• Make a back-up copy of your Agency.mdb database. Name the backup file AgencyBackup.mdb.
• Download the text file named ScreeningRatesTable.txt that contains the advertisement screening cost data.
• Open your original database Agency.mdb.

Linking the screening rates file to the database

The next step is to link the ScreeningRatesTable.txt file to the database.The following steps briefly describe how to link the file. Linking is covered in another topic. If you are unfamiliar with this, refer to the MS Access in-built help.

1. In the open database, choose File > Get External Data > Link Tables.
2. Complete the first Link dialogue box as follows:
3. Continue with the Link Text Wizard as follows:
4. Open the linked table in the database and check that the data is correctly displayed, as shown below:

• use the Look in box to navigate to the folder in which you've stored the text file
• from the Files of Type box, choose the Text Files format from the drop-down list
• select the file ScreeningRatesTable.txt and click the Link button. Access will launch the Link Text Wizard.
  
• at the first wizard screen, select the Delimited option, then click Next
• at the second wizard screen select the Comma radio button and the First Row Contains Field Names tickbox, then click Next
• at the third screen check that fields Screening Frequency and Rate are shown, then click Next
• the final wizard screen will display the name of the linked file. Click Finish. Access will link the file to the database and display the file name in the database window.
  
  

Relationships and queries

1. Now set the relationship between tblAds table and the linked ScreeningRatesTable, using the common field Screening_Frequency. Save the relationship. Note that you cannot set referential integrity for a linked table.

2. Check your version of the qryBrandManagerSummary query against the feedback in the queries suptopic. If your version is different, correct it now.

3. Make a copy of the qryBrandManagerSummary query, and name it qryNewBrandManagerSummary.

4. The Brand Managers at the agency want to know the total screening costs for each advertisement. In the queries subtopic we added a calculated field called Total_Screenings. To calculate the total screening cost for each ad we need to multiply the total screenings by the Rate from the ScreeningRatesTable. Modify the qryNewBrandManagerSummary query by:

• adding the ScreeningRatesTable table using the show tables button
• a new calculated field that multiplies Total_Screenings by the Rate. Name the field Total_Screening_Cost
• save the modified query.

Modifying the report

1. Make a copy of the rptBrandManagerSummary, and name it rptNewBrandManagerSummary.

2. Modify the the rptNewBrandManagerSummary report:

• in design view, using the Properties window, change the Record Source property of the report to qryNewBrandManagerSummary. This tells Access to use your new query.

3. Run the report and check that your calculated fields are functioning correctly, and that all fields and labels are clearly displayed. You may need to adjust the width and position of your columns.

4. Add the new report to the database switchboard.

5. Print a diagram of the relationships, showing the new linked table, for future reference.

• If you have not already done so, you will need to change the printer page orientation to landscape so that you can fit in the extra column easily. Select File > Page Setup to set the printer page orientation.
• Add a new column to the qryNewBrandManagerSummary report that shows the Total_Screening_Cost calculated field. Make it the rightmost column.
• Save the modified report.

Summary

In this topic is shown about even a well-designed application may require modification. This may be required to:

• remove errors or limitations
• account for new or changed data
• add new features
• improve usability and productivity
• streamline the application
• facilitate the interaction of the application with other programs.

ICAB4136B Use structured query language to create database structures

Create and use advanced database queries

Overview

The management of information is an increasingly important feature of workplaces today. As an IT professional working in this environment, you'll need to know how to provide the most flexible, efficient solutions to a variety of information management tasks. Advanced database queries allow you to prepare and present data in a variety of useful forms. In this topic you'll learn how manipulate data using advanced database queries.

Case study

The database of BookshopA2k.mdb was developed for a small business, the Busy Bee Bookshop, to record information about its products, sales, customers, and staff. It has been operation at the store for several months. During this time the owner of the shop, Mandy Simons, has come to realise that the database does not meet all the data management needs of the store. The main problem areas Mandy has identified include:

• The integration of data provided in non-database files by the store's book suppliers
• The transfer of information between the database and other software packages used in the store
• The need to make data that's stored outside the database available for particular database functions
• The retrieval of detailed sales, customer and book information for reports, mail-outs and catalogues.

Bookshop database layout

The Busy Bee Bookshop database consists of 6 tables:

The database also contains a number of queries and reports that are used by staff at the shop to produce sales, customer, and stock reports.

Creating advanced queries

In this case, queries are mostly used to display the static results of relatively simple questions like:

• How many books were sold in January 2002?
• Which customers living in the postcode 3032 have bought a book on gardening in the past six months?

however, queries can do a lot more than this. They can perform actions that include calculations and the modification, addition or deletion of records from a table. You can even make an entirely new table from a query or display query results in a spreadsheet-like column and row format. In this section we'll look at a range of used query types and how they can be used.

Appending data sets

The append query, one of several action queries available in Access, allows you to select a set of records from a table (or from the output of a query) and add them to an existing table.
In the following example we'll be appending records to an empty table but keep in mind that records can be added to a table that already contains data. You might want to work through this exercise as practice. If you do, you'll be creating a backup copy of a table.

Creating an Append query

1. The first step is to open the relevant database. In our demonstration here it's the bookshop database.
2. Create a copy of the tblBooks table, choosing the Structure Only option. Be sure to choose the option to copy the table structure only, not the data, and name the new table OriginaltblBooks. (If you are not sure how to copy a table, consult the Access online Help.) Note that the tblBooks table has 100 entries.
3. Create a new query based on the tblBooks table, adding all fields to the query design grid as shown below.

4. Select Query / Append Query from the application menu bar.

5. The Append dialogue box will appear. Select the table Original tblBooks table from the dropdown list and click the OK button.

6. The query design grid has changed showing the originating field names in the Field row and the receiving field names in the Append To row. Because we are making an exact copy of the table (the structure of the tables is identical) you'll see that Access has supplied the correct field names in both rows.

7. If Access can't find a matching field name in both tables it will leave the Append To area blank. You can then select the field by clicking on the relevant field area in the Append To row and selecting a field name from the drop down list a shown below.

8. Click on the Run Query button to append the data from one table to the other. You will see a message box warning that you are about to append the data. Take note of the warning message!

9. Save the query as qryAppendOriginal. You'll notice that the Append query symbol (shown below) precedes the query name in the database window, indicating that it is an Append query.

Deleting data sets

A delete query is called an action query because it performs an action (delete) on the selected records. Note that the delete query acts upon records - you place fields onto the query design grid to specify the criteria for record deletion, but you do not actually delete the fields.

You need to be very careful when deleting records in a relational database! Records in one table may be linked to many other tables, so it's important that you understand how the relationships have been set up between tables before making any irreversible changes. You also need to know if the Cascade Delete Related Records setting has been selected for the relationships. If this setting has been activated any related records will be deleted at the same time. To check if the Cascade Delete Related Records option has been set:

• open the relationships window (select Tools / Relationships from the menu bar)
• right-click on each relationship link to display the Edit Relationships window.
  The example below shows how you can delete all records from a table. You might like to work through this example as practice.

Creating a Delete Query

1. The first step is to open the relevant database.
2. Select the Queries tab from the objects bar in the database window.
3. Choose Create Query in Design View from the database window, and add the table. In this example we're working with the OriginaltblBooks table.
4. Select Query / Delete Query from the menu bar.

5. If you look closely at the query design grid you'll see it now displays a Delete row

6. You can delete ALL records by adding the entire table to the first field cell in the design grid. To do this, double-click the asterisk at the top of the OriginaltblBooks table. All fields in the table will added as shown below.

7. Alternatively, you can delete selected records by setting criteria for a particular field. In the screen shot below a single field was used but you can set criteria in multiple fields.

8. Click on the Run Query button. You will see a message box similar to that shown below, warning you are about to delete the data. Take note of the warning message!

9. Save the query. You'll notice that the icon shown below precedes the query name in the database window to indicate it's a delete query.

ICAB4060A Identify physical database requirements

1: IBM Software Information

IBM eServer hardware
The IBM eServer hardware information offers links to technical documentation to help you optimize and customize hardware for your IBM eServer iSeries(TM) servers, OpenPower(TM) servers, pSeries(R) servers, xSeries(R) servers, and zSeries(R) servers.

Virtualization Engine
IBM Virtualization Engine(TM) is a set of technologies and systems services that allow system administrators to access and manage resources across a heterogeneous environment. Its systems services simplify IT resource management by virtualizing data, applications, servers, and network resources.

Virtualization Engine console
The IBM Virtualization Engine console is the focal point for managing your virtualized business environment. With the Virtualization Engine console, you can manage solutions rather than specific IBM products by removing operating system boundaries and maximizing the sharing of resources.

Director Multiplatform
IBM Director Multiplatform provides a suite of tools and utilities that automate many of the processes that are required to manage systems proactively such as event logs and action plans, file transfer, hardware and software inventory, and resource monitors and thresholds. For some IBM systems based on Intel(TM) microprocessors, Director Multiplatform also provides capacity planning, asset tracking, preventive maintenance, diagnostic monitoring, and troubleshooting.

Enterprise Workload Manager
IBM Enterprise Workload Manager (EWLM) enables you to define business-oriented performance goals for an entire domain of servers. Then, EWLM provides an end-to-end view of the actual performance relative to those goals. This data is available in the monitor views of the EWLM Control Center. Use these views to determine if a performance problem exists in your EWLM domain.

New and changed information:

• Expanded support now includes IBM z/OS(R) V1R6 as a managed server

Systems provisioning
The systems provisioning topic contains information about planning for systems provisioning and installing systems provisioning and prerequisite products. It also contains a scenario that describes server provisioning in a BladeCenter(TM).

New and changed information:

• IBM Tivoli(R) Provisioning Manager Fix Pack V2.1.0.1, which adds support for the following:

1. SUSE LINUX Enterprise Server 8 and Red Hat Enterprise Linux(R) 3.0 (for AS 32-bit xSeries Update 3)
2. AIX(R) V5.3 64-bit on POWER5(TM) (for iSeries and pSeries servers)

IBM Dynamic Infrastructure
Dynamic Infrastructure is an IBM on demand solution for a heterogeneous environment that can enable you to run SAP environments more efficiently by dynamic provisioning of SAP systems across IBM eServer platforms.

Grid computing
IBM offers a development product based on the Globus Toolkit Version 3.0 (GT3) called the IBM Grid Toolbox V3 for Multiplatforms. Use IBM Grid Toolbox to build a GT3-compliant grid and to develop, deploy, and manage services on your grid. The goal of grid computing is to create a powerful self-managing virtual computer out of a collection of connected systems sharing various combinations of resources in a multiplatform environment. These systems and the connections between them are the grid.

Security
IBM eServers provide maximum protection in a consistent manner across open, heterogeneous, e-business environments. eServer security systems manage resources and users so that access to programs and data is protected, and any intrusion, in the enterprise or in an individual server, can be quickly detected and corrected.

• IBM eServer Security Planner asks basic questions about your business environment and security goals. Based on your answers, the planner provides you with a list of security policy recommendations that you can use to begin protecting your operating system resources immediately.
• Enterprise Identity Mapping (EIM) is an IBM infrastructure that allows administrators and application developers to solve the problem of managing multiple user registries across their enterprise. This infrastructure provides a common set of APIs that can be used across platforms to develop applications that look up the relationships between user identities and a single EIM identifier that represents a user in the enterprise.

Common Information Model
The Common Information Model (CIM) provides a model for describing and accessing data across an enterprise. CIM consists of both a specification and a schema. The specification defines the details for integration with other management models, while the schema provides the actual model descriptions. CIM is a standard that is part of the Web Based Enterprise Management (WBEM) initiative. CIM and WBEM are developed by the Distributed Management Task Force (DMTF), which is a consortium of major hardware and software vendors, including IBM.

IBM Data Discovery and Query Builder
The IBM Data Discovery and Query Builder software is a Web-based tool that allows you to build queries quickly and easily and to run the queries against your data. Using Data Discovery and Query Builder does not require knowledge of complex data query languages. This section contains links to a variety of guides for installing, configuring, and using Data Discovery and Query Builder and its tools.

2: SQL Server Database Requirements

Problem

In some organization, that have been noticed like database requirements are never included as a portion of the system requirements. The requirements always focus on the interface and we derive the database design from the interface as well as fill in some of the gaps. For some developers that process seems to yield a decent product, but not always. I think if we requested database requirements from both the business and technical management our overall offerings would be much better and we would have less patch\fix cases. For the requirements in our environment I have a couple of ideas in mind, but I am hoping you can give me a broader view of the situation with respect to overall SQL Server database requirements.

Solution

It is unfortunate that SQL Server database requirements are not included in your requirements document. Depending on the organization and internally how things work with new projects, it would be a good idea to outline a baseline set of requirements with the caveat that every project is different so new or more requirements may be needed. If you are the SQL Server DBA responsible for the systems moving forward it would be wise to speak to your management to find out how you can have input at the beginning of process to ease your tasks at the end of the process. In some organizations that is easier said than done, but it is worth outlining some of the potential issues either historically or theoretically and discussing them with your management in a professional manner.

As mentioned above, requirements differ from project to project. As an example, the requirements for building an OLTP system versus a disaster recovery solution vary widely, so consider taking these steps in an effort to build requirements for your SQL Server database project at hand:

• Build a baseline set of requirements for all projects that can help get the requirements questions formulated
• Think about what is needed for the system when it is released to production from a business, technology, database and user perspective then build requirements around those areas
• Educate yourself on the project\technology so you are aware of requirements needed specific to the project

In terms of requirements for an SQL Server database (OLTP) project consider the following items as a baseline set of requirements:

• Make vs. Buy - One of the first requirements that should be outlined is the make versus buy decision. Depending on your organization this could be a non decision because either you have internal developers, you have a contract with a development organization or your organization purchases everything off the shelf. It is just a key step in many circumstances that is sometimes overlooked, but depending on the project can sway the direction of the requirements one way or another.

1. Keep in mind with the recent news related to SQL Server Data Services this offering could change the equation related to some SQL Server infrastructure decisions.
2. Hosting your SQL Server externally can also be a key decision in this phase of the project and can change budgets and resource needs.

• Project Budget - Depending on the project and your roll in the organization, finances may or may not be discussed. The reality is that everything has a cost, it just depends on if a hard dollar figure is used versus out how many internal resources are used to complete a project for a specific duration.

1. Even if overall project costs are not defined, it is imperative to find out if additional hardware and licensing is needed and the overall budget for that portion of the project if you are going to be responsible for making a decision on the hardware platform.

• Project Team - Knowing your team members and what they can bring to the table is important when determining deadlines and setting general expectations. Having a gap in any one skill set should be identified early in the process and alternatives should be determined. One or many people should be responsible for:
• Project management
• Development - Front end, middle tier, back end
• Database design and development
• Infrastructure setup and configuration
• Testing
• Documentation
• Support - Infrastructure, application, database, etc.
• Sign-off - User, business, technical, etc.
• Deadlines - Agreeing on the deadlines ahead of time can offer a great deal of value for both the user and technical team as well. The users will know when they can expect phases of the project completed and the technical team should be able to plan for the project deadlines while balancing the remainder of their projects or daily tasks.
  
• Business, Technical, User Value - Understanding what the business, users or technical team is trying to solve can be the biggest help in truly resolving the issue. Be sure the problem is clearly understood then consider offering a few different general approaches to resolve the problem followed by the design for the final solution.
  
• User Requirements - The more you know about the users the better, here are some items to keep in mind:
• Number of users
• Working hours
• Location
• Bandwidth between the users and infrastructure
• Hardware specifications (desktop, notebook, etc.)
• Technical expertise
• Expected training on the application
• Historical successful or unsuccessful applications (Having some history behind what has or has not worked in the past may give you some good insight for the current application)
• How the problem is resolved today via people, process and technology
• Application type
  - Web, desktop, mobile, etc.
  - OLTP vs. Reporting vs. Analytical
  
• SQL Server Technical Requirements
  - SQL Server Name\Instance Name
  - SQL Server Services Needed (Relational engine, SQL Server Agent, Full Text Search, Analysis Services, Reporting Services, Integration Services, etc.)
  - Database (Name, Size, Anticipated growth rate or capacity planning, Database configurations, Storage configurations.)
  - Data (Data elements: Tables, Columns, Data types, Accept null, Defaults, Language support [Character set and sort order]; Data access (Determine how the data will be accessed for index selection); Source (Uploads or downloads from an existing system,
  Data entry by customers, Data entry by internal users)
  - Reporting (Infrastructure: Separate SQL Server hardware, instance, license, separate data model, etc.); (Report type: Real time, dashboard, analytical, trending, detailed, etc.) ;
  (Users: Number, location, operating hours) ; (Data: Detailed, rolled up, geographical, departmental, process related, etc.)
  - Operating hours (24X7 or Monday to Friday 9:00 AM to 5:00 PM, etc.)
  - Performance requirements (Transactions per second, Number of sustained users,
  Response time)
  - Security (Security Model, Database auditing - Third party, triggers, etc.)
  - Automated Processes (Frequency: Hourly, Daily, weekly, monthly) ; (Duration - Every hour for 1 minutes or every day for 3 hours, etc.) ; (Source - Data source could be a partner, service provide or another internal application) ; (Destination - Data source could be a partner, service provide or another internal application) ; (Technology - SSIS, web service, custom application, etc. )
  - Documentation (Data model, Database dictionary)
  - Maintenance Schedule (Frequency, Duration)
  - Backups Schedule (Type - Backup, differential, transaction log, third party solution, etc.) ; (Potential data loss) ; (Recovery time)
  - High Availability (Determine the types of failures that should be prevented:
  Hardware, software, administrative error) ; (Amount of acceptable downtime)
  (Native vs. third party solution)
  - Disaster Recovery (Determine the types of failures should be recoverable :Hardware, software, administrative, natural disaster) ; (Amount of acceptable downtime) ;
  (Native vs. third party solution)
  
• Testing Requirements (Who is responsible for testing: Traditional tester, User, Technical testing) ; (Building test plans) ; (Sign - off on testing)
  
• Application Pilot Requirements (Number of users, Duration, Use cases, Infrastructure,
  Success, failure or enhancement reporting )
  
• Production Support Requirements (Production support team, Escalation procedures,
  Enhancement requests)

http://www.mssqltips.com

http://publib.boulder.ibm.com

PSPPM402B Manage simple projects

1: Manage project progress

The project is underway! Your team has been selected and you have identified the resources you need. Now you just have to manage it all! This is a process of watching what is happening and taking steps to keep the project on track.

Outcomes for this Learning Pack

After completing this Learning Pack you will be able to:

• Continually monitor all aspects of a project
• Review the progress of a project using milestones, deliverables, objectives and specifications to assess performance
• Apply methods for controlling project activities to maintain progress
• Consult and negotiate with team and stakeholders throughout the project
• Assess and implement proposed changes in projects which may involve changes in work plans, agreements and expectations
• Use agreed communication, documentation and reporting mechanisms

Activity 1: Monitoring a project

Q: Where might you find basic information that can be used to monitor and control a project, and how might that information be applied to a system?


A: Systems for monitoring and controlling the project are established early and can be found in such things as the project plan, communications plan and contracts (for outsourced work and materials). Information in the project plan applied to monitoring and control would include the network diagram and the schedule that is derived from it, which can decide points at which outcomes should be checked or reports made. The communication plan may describe an itinerary for meetings and other ways that reporting is to occur, as well as what templates and forms are needed for reports to gather project data. Contracts will also have described deliverables for the project and a range of deadlines for work.


Activity 2: Task and team management


Q: Good delegation is done in three basic steps. Which of the items below is not one of them, and why not?


A: Draw their attention to any mistakes, offer criticism and suggest corrective actions because drawing attention to mistakes may have its place, but does not indicate the sort of trust needed to delegate well. Project work itself is essentially a delegated responsibility, so trust is very important. A focus on mistakes also risks creating a culture of blame, rather than having a focus on learning. Team members should be secure in the knowledge that if they draw attention to problems they will not be criticised.

Activity 3: Change control procedures


Scenario:

You’re in charge of a simple project to develop an asset database for your organisation. You were allocated one staff member to work on the project for two days per week over six weeks. Three weeks into the project, you are informed that this staff member has been allocated as an emergency replacement in a support team going with the General Manager, Peter Doyle, to a South Pacific conference. The team will be away for approximately one week.


A: There are several alternatives possible. You could request:

• a new team member for the project, or
• a delay in the delivery date, or
• a reduced number of features in the final product.

Perhaps you thought of a further alternative. The particular request you make will depend on further information such as the possible impact of a delay in the project. For example, the database may have been timed to coincide with the procurement of a large number of assets.


Activity 4: Preparing project reports

Who should receive a status report? How long should a status report be? what information should be included in a status report?

1. The main audience is the client and those working directly on the project. Other stakeholders are the secondary audience.


2. A status report should be now more than one page, and should include charts and headings to make reading easier.


3. Information should include progress against milestones, budget information, changes, quality guidelines and issues (both technical and project issues)

Activity 5 Self checking

Q1: What are the four most important aspects to focus on when monitoring and controlling a project?

• cost
• time (the schedule)
• performance levels (quality)
• changes (controlling, directing, correcting, etc).

Q2: List at least five ways of effectively gathering project information.

A2: There are many ways of effectively gathering information. The answers are :

• Plan to monitor progress of all tasks in the project
• Collect regular feedback (both formally and informally) from individual team members on their work
• Get team members to provide you with written reports
• Observe progress first hand
• Hold regular meetings to discuss progress
• Communicate regularly using tools such as email
• Document the processes the team is using to record progress
• Use planned indicators.
• Use a variety of tools to analyse data collected and provide graphical reports

Q3: Metrics are?

A3: Metrics are sample measurements of values, such as staff levels, percentage of tests that have passed, estimated versus actual duration between major milestones, or the number of tasks planned and completed. The word ‘metric’ simply refers to the particular measurement being used, and any useful value in a project can be used.

Q4: What is the impact of schedule slippage on a project?

A4: The schedule closely interacts with deliverables, costs, task relationships, time, workloads, and scope and therefore any slippage can affect any or all of them. For example, a four-day delay on a task on the critical path can have a major impact because a milestone further along the path may not be changeable.

Q5: What are the five general ways or methods of controlling a project?

A5: Control tools and methods can include:

• rescheduling where required
• adapting resources where necessary
• delegating tasks
• changing priorities
• changing objectives where necessary

Q6: Internal changes in project development are often those that arise due to:

A6: misinterpretation of requirements, errors of principle or fact, estimation errors, invalid logic and technical issues that could not have been foreseen in planning are all causes for internal changes that arise in project development.

Q7: Control of change involves three key steps—what are they?

• Request for change
• Evaluation of the change request
• Decision and acceptance.

Q8: Progress reports are used for:

A8: Progress reports are used for collecting project data, updating the critical path, changing the schedule and informing stakeholders of project status.

Q9: What are the three main kinds of regular project reports?

A9: The three main types of regular project reports are:

• Status reports
• Progress reports
• Forecasts

Q10: A report that summarises information gathered in periodic progress reports from team members is called:


A10: A status report


Q11: Using templates that have already been created, tried and tested for project management forms and checklists, is useful because:


A11: Using templates and checklists already created, tried and tested for project management is useful because most of the fact-finding and reporting needed in project work has remarkable similarities from one project to another; they can help avoid overlooking obvious items when designing a form yourself and it good to start by standing on the shoulders of those who have gone before you (even if they are your own shoulders! Remember to keep templates and forms you create and use for later projects).



2: Plan a simple IT project

Planning for a simple IT project occurs in the first two phases of the project life cycle. The first or ‘initiate’ phase involves activities to define the scope of the project. Once project scope is defined, the ‘plan’ phase includes developing a detailed task list, estimating task times and costs, arranging a sequence for tasks and bringing together that information in a schedule.


The two phases end in the creation of a project plan, as the means to move from planning to execution, and which is used to control and measure performance during the life of the project.


Outcomes for this unit is you will be able to:

• outline activities that occur in the ‘initiate’ and ‘plan’ phase of a project
• prepare a scope statement for a simple project
• break down a project into discrete tasks
• outline a variety of methods for estimating task duration
• distinguish between duration and elapsed time
• outline methods for estimating costs
• arrange tasks into an appropriate sequence (schedule)
• prepare a project plan for a simple project.

Activity 1: Management skills for scoping


You may recall that a good manager needs to have skills in planning, organising, controlling, leading, and communicating. In preparing a scoping document the most significant skills are organising, communicating and planning.


In preparing your scope statement you carry out the following activities:

• Arrange meetings with sponsors and stakeholders
• Determine project requirements to meet project objectives
• Define project goals and objectives
• Determine constraints
• Determine assumptions
• Prepare initial estimates for budget, people, time frames
• Prepare task and deliverables list

Decide which general management skills are being used for these activities.



Activity 2: Estimate task time

In the reading, you were given a formula for calculating task completion time, based on the best time (B), the worst time (W) and the most likely time (L). Here’s the formula again:


Use this formula to do the following activity and complete the table.

Q: You have three tasks that need a completion time to be estimated. They are not well known activities so you want to calculate a time that will take into account variations.


Use the weighted average formula to fill in the last column values

A: The weighted averages completion times are as follows:

Table of weighted averages—completion times

Activity 3: The main planning tasks

Q: Describe the five main tasks that you have to complete in the planning phase of the project life cycle and the two general management skills you might need.

A: Did you remember the following?

• develop detailed task list
• estimate all task times and all costs
• arrange best sequence of all tasks
• develop workable schedule and identify critical milestones
• write detailed project plan and obtain approval from stakeholders.

The general management skills of planning and communication would be needed—to meet with many people and elicit information to correctly estimate work durations, costs and resources, etc.