Access Database

Application Database Access

Figure 12.4 depicts the system and database access logic. The database access tier sits between the business logic code and the database. It is written in Java using JDBC and performs I/O and related persistence operations of the original application.

Sharing an Access database

The method of sharing will depend on factors such as the availability of SQL server space and supporting technical resources.

Database Access

The native database system that comes with IMS is based on a hierarchical model, which preceded the development of relational database systems. The higher performance of the hierarchical model is one of the reasons IMS-based applications are still in production. Today's IMS applications can also use DB2, in addition to or in place of the IMS DB database. The database access runs using the application's thread. A data propagation utility is available that moves data updates from IMS DB to DB2, or vice versa, automatically. Java library support allows IMS DB to invoke stored procedures hosted in DB2. Other tools allow data to be moved between IMS and non-IBM relational databases.

MS Access Database Data Store

If you use a Microsoft Access database as your XenApp data store the default password must be changed. During the installation of the first XenApp server in a new farm that is utilizing an Access database as the data store, a database is created with the default username of “citrix” and the default password of “citrix.” The credentials should be changed by using the dsmaint config command. Additionally, NTFS permissions should be set to prevent unauthorized user access and modification to the database.

Access

The Microsoft database product Access is widely available in the Microsoft Office Suite. Access stores data in tables, similar to spreadsheets; each row is a record, each column is a field. One may want to think ahead and change the default column heading in, say a COUNTER Journal 1 table, to include the year, such as 2011_YTD_Total. In this way, where each year’s COUNTER report is stored in the table, and more than one year is queried, it is easy to see what the total is for each year.

Good database design decrees that data is stored only once. This not only saves memory and storage space, but also reduces maintenance and update issues. The idea is for each item in each entity to have a unique field, a key, either a number self-assigned by the database or another unique identifier. ISSNs, ISBNs, record numbers and the like make good keys. Titles do not make good keys because there are differing alphabetization practices (for example, using “The” in a journal title first, or putting it last after a comma) and different systems handle diacritics and foreign languages in various ways.

A well-designed database stores data in its simplest form, having only one kind of information in a field. For example, if an ILS stores multiple ISSNs in the 022 field, these should be separated. Avoid calculations and other manipulations, also. For some system reports that may mean cleaning up the data to separate the ISSN from the journal title or the place of publication from the publisher, depending on the data need. Data clean-up is essential to having a clean, robust database. In fact, probably many clean-up projects will be found while moving data into the database. Inconsistent coding, changing practices and mistakes – all will be revealed. Every clean-up project, while burdensome, leads to better data and best practices. It is worth carrying them out and raises staff awareness for diligence and discipline as they perform their duties. As a librarian’s technical skills increase, clean-up becomes less daunting. It is hoped that staff will embrace new skills, also, and join in the commitment to data quality.

Understanding Access terminology will help with the design of the database. Every table of data will need to have a key assigned, even if it is an auto-number assigned by the database. For queries, tables are joined on common elements like ISSNs or ILS record numbers. This refers to the relationships between entities. The nature of the joins can be adjusted.

Views in Access

Access has several views, as shown in Figure 9.4.

Figure 9.4. Views available in Microsoft Access, Home tab

© Microsoft Corporation. All rights reserved. Used with permission from Microsoft Corporation.

1.

Design View shows the list of fields in the table and their properties (Figure 9.5).

Figure 9.5. Access Design View

© Microsoft Corporation. All rights reserved. Used with permission from Microsoft Corporation.

2.

SQL View shows the SQL database code, as shown in Figure 9.6.

Figure 9.6. Access SQL View for a query from two tables

© Microsoft Corporation. All rights reserved. Used with permission from Microsoft Corporation.

3.

PivotChart View shows a designable chart. Desired fields are dragged and dropped onto the chart. Using the same fictional table in all examples, two discipline areas were chosen, art and biology, and their costs. Figure 9.7 is unfinished but one can see the power of this tool.

Figure 9.7. Access Pivot Chart View comparing costs for Art and Biology titles

© Microsoft Corporation. All rights reserved. Used with permission from Microsoft Corporation.

4.

PivotTable View, using the same data, shows the table view, automatically summing the amount paid in each discipline (Figure 9.8). Again, one can choose which fields to display, even the values in the fields.

Figure 9.8. Access Pivot Table View summarizing the amount paid for various print disciplines

© Microsoft Corporation. All rights reserved. Used with permission from Microsoft Corporation.

5.

Datasheet View shows the “spreadsheet” (Figure 9.9).

Figure 9.9. Access Datasheet View showing electronic journal information

© Microsoft Corporation. All rights reserved. Used with permission from Microsoft Corporation.

Everyone has their own method of working, but many people spend most of their time in Design View while they adjust their work, switching to Datasheet View to check their work and results. Take your time to set up carefully, with a thought for all uses for the data. It is easier not to use a field than to add a field later (Microsoft, 2011b).

Queries are used in Access to review data, extract specific data, delete data or update or change data in the database. Queries can be used to view data, combine data from different tables, make calculations and/or summarize data (Microsoft, 2011e).

Reports in Access allow formatting of query results. Access forms are used to display, edit or enter data into a database. Access forms are for end-users of the database, also for displaying, editing or entering data, making access to data easier and more attractive. Forms allow binding the field to the values in the database, called a control source value. In Access, events are related to designer-defined macros as they are used on forms. For example, an event can move the end-user’s mouse from one input box to the next once a choice is made on a form input box.

Databases and access

Databases such as these are often best accessed through their purpose-built API, which will provide functions for selecting and filtering their contents, or through a custom web-scraping script which programmatically accesses pages.

The data that must be compiled can be thought of as a table with three fields: the compound, the target, and an observed measure of activity. Each of these is discussed in turn in the following sections. While these are the fields used directly in modeling, practitioners are encouraged to retain all available fields, even if just in raw form, during collection and curation if possible: the importance of this additional data may only be realized at a later stage in the project.

Data Access APIs

Application Database Access

The VAX flat file database implementation is replaced by a modern relational database system. The transformed EOSS business logic accesses this database through a Data Access API which uses JDBC and SQL to interface with the database. During normal operation, the application logic might open an indexed file using a certain channel and perform I/O operations on it.

To open a new file, it calls an open_File() API function with the channel number, file name, file access mode, associated record buffer, etc. The data access API takes this information and creates a “File” object and associates it with a database table with the same name. Depending on the specified open mode and the existence of the database table, it can be created, appended, or truncated at this point. Other file behaviors are also replicated by the file object. Upon a successful “open,” the API uses a Java map language construct to associate the channel number to the file object so it can be referred to later by the channel number.

To write a record to a file, the application prepares the record buffer and invokes the file object .put() method to transfer data from the record buffer to a row in the database table. The opposite occurs when reading from a file using the .get() method. Other file operations such as delete and insert are handled similarly by other API methods using JDBC and SQL. Both sequential and indexed data files are stored in the database and treated in a similar manner with the exception that all operations are not valid for all file types. Figure 10.7 diagrams the application database access.

Figure 10.7. Application database access.

SQL and Oracle

Along with e-mail, databases are one of the most crucial network-based services an organization will come to rely on. Beyond the standard server attributes of name, IP address, and so on, you should include a number of additional pieces of information when diagramming and documenting SQL Server, the most obvious of which are instances and databases. As illustrated in Figure 5.5, a single physical server may have multiple instances, or copies of the SQL Server database engine, running. Each instance is unrelated the another instance, allowing two copies of a database called APPDATA to exist, one in the DEVELOPMENT instance and one in the TESTING instance.

Figure 5.5. One SQL Server with Two Instances of the Engine

Each instance of the SQL Server engine maintains discrete listings of user accounts granted permission to log on to the server to access databases, and permission listings for each database as to which users have been granted which roles, and consequently which permissions. It is also possible to specify which mechanisms are open to clients to access the engine—TCP/IP access to port 1433, and named pipes access. As such, it is useful to document which mechanisms are in use.

A variety of third-party tools and utilities are available to assist with the complete documentation of a SQL server, covering everything from database schemas to stored procedures to permissions. The key considerations for this chapter are “where are the SQL servers?” and “who may access them?” Visio 2007 Professional and Visio 2007 for Enterprise Architects include the ability to connect to a preexisting SQL server and extract configuration information about a database to create a model of it, but this information does not include security information such as logins or permissions.

As with the SQL servers on the network, Oracle servers offer up a vital resource to an organization in the form of databases, so documentation of where these servers are and who may access them is very useful to have.

Resource Access Reports

These reports identify various system, application, and database resource access patterns across the organization and can be used for both activity audit, trending, and incident detection.