CSCI A114 / INFO I111 Lab Notes Fourteen First semester 2000-2001

PROJECT NINE:

ADMINISTERING A DATABASE SYSTEM

You found many activities that the individual in charge of administering the database must perform. These include:

1. analyzing tables for possible duplication
2. analyzing performance to see where improvements could be made, and
3. producing complete system documentation

Security is another issue for the administrator, who should consider the use both passwords and encryption to protect the database from unauthorized use.

Bavant wants more users to have remote access to the database, so administration would include the creation of both data access pages and replicas.

You also learned how important the language called SQL has become in a database environment and determined that the administrator should be familiar with the language and how it can be used.

In this lab you will work with two databases:

• Bavant Marine Services

• Holton Clinic

Then, open Bavant Marine Services database (BMS14) on your desktop.

1. Converting a Database to an Earlier Version.

1. Click Tools, Database Utilities, Convert Database, To Prior Access Database Version.
2. Save as "Bavant Access 97 Version", on the Desktop.

2. Using the Table Analyzer.

1. Click Tools, Analyze, Table, Next.
2. Click Next again. Click Marina, then Next again.
3. Make sure "Yes, let the wizard decide button" is selected, then click Next.
4. Because the type of duplication identified by the analyzer does not pose a problem click Cancel.

3. Using the Performance Analyzer.

1. Click Tools, Analyze, Performance.
2. Click Tables, Select All. Click Queries, Select All, OK, Close.

4. Using the Documenter.

1. Click Tools, Analyze, Documenter, Tables, the Marina check box, OK.
2. Close window by clicking its Close button.

1. Click the Tables object, right-click Marina, Design View. Maximize the window.
2. With the Marina Number field selected, click the Input Mask property and then type >LL99 as the value.
3. Close window, save changes.

1. Click Relationships. (If the Relationships window displays a table named Category_1, right-click the line joining Category_1 and Open Workorders and click Delete on the shortcut menu. Click Yes to permanently delete the relationship from the database. Right-click the Category_1 table and then click Hide Table on the shortcut menu.) Right-click the line joining the Marina and Open Workorders tables and then point to Edit Relationship on the shortcut menu.
2. Click Edit Relationship, then Cascade Delete Related Records check box, then OK.
3. Right-click line joining Category and Open Workorders tables, Edit Relationship.
4. Check Cascade Update Related check box, then click OK.
5. Close the Relationships window, save changes.

7. Updating a Table with Cascade Options.

1. Open Category table, change category number on the first record to 11.
2. Close Category table, open Open Workorders table. Check modification. Then close it.
3. Open Marina table and click record selector for marina EL25, then press DELETE, Yes.
4. Close Marina table, open Open Workorders table. The record for El25 has been deleted. Close table.

8. Setting Startup Options.

1. Click Tools, then Startup, Display Form/Page box arrow, choose Technician Master Form.
2. Click OK, then close and open the database. Close the form.

Then click the Open button arrow. Select Open Exclusive.

9. Setting a Password.

1. Click Tools, Security, Set Database Password.
2. Type your password twice, then click OK. Close. Start again, type password.

1. Close database, click Tools, Security, Encrypt/Decrypt.
2. Select Holton Clinic, type password, click OK, type HoltonEncrypted as the file name.

11. Removing a Password.

1. Open Holton Clinic in exclusive mode, enter password when requested.
2. Click Tools, Security, Unset Database Password. Close, open, verify.

12. Creating a Grouped Data Access Page.

1. Open BMS14, when the Technician Master Form appears, close it.
2. Click Pages, maximize window, double-click Create data access page by using wizard.
3. Click Tables/Queries box arrow, then Open Workorders. Add the following fields:
   • Marina Number
   • Location
   • Category Number
   • Total Hours (est)
   • Hours Spent
   • Next Service Date
4. Click Next twice. Click the first sort box, select Location. Then click Next.
5. Type Open Workorders as the title for the page, then click Finish.
6. If Field List box displays, close it. Type title: Open Workorders by Marina.
7. Close, save as "Open Workorders by Marina".

13. Previewing the Data Access Page.

1. Click Pages, right-click Open Workorders by Marina, Web Page Preview.
2. Click the GroupOfOpen Workorders-Marina Number button.
3. Click Next button to see next order of AD57. Close browser, close Access.

14.1 Creating the Replica.

1. Drag Holton Clinic to My Briefcase.
2. Answer Yes when asked if you want to continue, then No when asked about a backup.
3. When the message asking if you want to be able to make design changes in the original copy or the Briefcase copy displays, be sure that Original Copy is selected, then click OK.
4. Close any open window.

1. Open Holton Clinic from The Briefcase. Right click Patient, then click Open.
2. Add a New Record:

   ML21, Lewis, Michelle, 472 Berkshire, Perry, MI, 47626, $0.00, $0.00, 22

3. Click first name of patient number JM25, type Martin as the new name. Close.

15. Synchronizing the Design Master and the Replica.

1. Close Holton Clinic replica. Open Holton Clinic on the desktop in Open Exclusive.
2. Click Tools, Replication, Synchronize Now, then Directly with Replica, then OK.
3. When a message displays indicating that Access must close the database in order to perform the synchronization, click the Yes button.

16. To Create a New SQL Query.

1. Close Holton Clinic, open BMS14, close form.
2. Click Queries, New, Design View, OK. Close Show Table dialog box.
3. Click View, SQL.

1. Type

   SELECT [MARINA Number], [Warranty], [Non-Warranty]
   FROM [Marina];

2. Click Run.

18. Preparing to Enter a New Sql Command.

1. Click View, SQL.

19. Including All Files.

1. Type

   SELECT * 
   FROM [Marina];

2. Click Run.

20. Using a Criterion Involving a Numeric Field.

1. Delete previous command.
2. Type

   SELECT [Marina Number], [Name]
   FROM [Marina]
   WHERE [Non-Warranty] = 0;

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Marina Number]
   FROM [Marina]
   WHERE [City] = 'Burton';

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Name]
   FROM [Marina]
   WHERE [City] = 'Burton' AND [Non-Warranty] = 0;
   

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Name]
   FROM [Marina]
   WHERE NOT [City] = 'Burton';
   

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Marina Number], [Name], [Warranty]+[Non-Warranty] AS [Total Amount]
   FROM [Marina]
   WHERE [Non-Warranty] > 0;
   

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Marina Number], [Name], [Warranty], [Non-Warranty], [Tech Number]
   FROM [Marina]
   ORDER BY [Tech Number], [Warranty];

3. Click Run.

1. Delete previous command.
2. Type

   SELECT COUNT(*)
   FROM [Marina]
   WHERE [Tech Number] = '23';

3. Click Run.

1. Delete previous command.
2. Type

   SELECT COUNT(*), SUM([Warranty])
   FROM [Marina];

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Marina Number], SUM([Total Hours (est)]), SUM([Hours Spent])
   FROM [Open Workorders]
   GROUP BY [Marina Number]
   ORDER BY [Marina Number];

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Marina Number], SUM([Total Hours (est)]), SUM([Hours Spent])
   FROM [Open Workorders]
   GROUP BY [Marina Number]
   HAVING SUM([Total Hours (est)]) > 6
   ORDER BY [Marina Number];

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Open Workorders].[Marina Number], [Name], [Location], 
          [Category Number], [Total Hours (est)], [Hours Spent]
   FROM [Open Workorders], [Marina]
   WHERE [Open Workorders].[Marina Number] = [Marina].[Marina Number]

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Open Workorders].[Marina Number], [Name], [Location], 
          [Category Number], [Total Hours (est)], [Hours Spent]
   FROM [Open Workorders], [Marina]
   WHERE [Open Workorders].[Marina Number] = [Marina].[Marina Number]
         AND [Hours Spent] > 0;

3. Click Run.

1. Delete previous command.
2. Type

   SELECT [Open Workorders].[Marina Number], [Name], [Location], 
          [Category].[Category Number], [Category Description], 
          [Total Hours (est)], [Hours Spent]
   FROM   [Open Workorders], [Marina], [Category]
   WHERE  [Open Workorders].[Marina Number] = [Marina].[Marina Number]
       
     AND  [Open Workorders].[Category Number] = [Category].[Category Number]
     AND  [Hours Spent] > 0; 

3. Click Run.