Table of Contents
This user's guide aims to provide a basic resource for understanding and operating MRALD. Deep technicality will be avoided when discussing MRALD, so the software may be presented clearly to a user who has no prior experience with the software or databases. Basic database information is provided in Appendix A for those who are interested.
This is the copyright work of The MITRE Corporation. No other use other than that granted to the U. S. Government, or to those acting on behalf of the U. S. Government is authorized without the express written permission of The MITRE Corporation. For further information, please contact The MITRE Corporation, Technology Transfer Office, 7515 Colshire Drive., McLean, VA 22102, (703) 883-6000.
(c) 2004 The MITRE Corporation
MRALD gives the user the power to search a database without requiring prior knowledge or expertise with databases. MRALD takes an HTML form as input and parses (seperates and processes) the input fields into SQL instructions for a database query. MRALD encodes its instructions in a set of tags, which have a flexible structure in case future instructions need to be added for a more sophisticated SQL query. MRALD is an automated enterprise data delivery system. It runs on almost any platform and will access any database with a reasonably complete JDBC driver. MRALD allows for any database to be accessed easily via a web browser. Each project can have the same main page (like it's own Portal, where each user will see a personalized version of this main page). It is domain independent and is ready to deliver data, filtered based on user needs/selections, right afterinstallation. For installation, see MRALD Installation Guide.
Figure 2.1. The User Interface
The MRALD homepage is depicted in figure 2.1. The left section of the interface, labeled “news”, provides general news and updates concerning MRALD. The news is set by either the deployer or the system administrator, with entry dates clearly labeled.
The interface (fig. 2.1) provides three methods for submitting a query by the use of a form:
Pre-built Forms: Forms for general use should be placed here. The deployers of the system should pre build forms that will accomodate most of the users' needs and place them here. They can be listed by simple links, or they can be clumped and put in drop downs like the one in the Special Forms section from the default installation. |
Personal Forms: Users that need extra to retrieve data that the deployers didn't anticipate can build their own forms using the form builder and subsequently have their forms listed here. To use the form builder, the user must have at least some understanding of the data in the underlying database. In addition, the user needs a good understanding of which tables this data can be obtained from. If the user does not have this understanding, then a developer, or someone familiar with the database structure should be contacted to create a personalized form. More information is given in Create Your Own Form. |
Special Forms: This section provides a location for non-standard forms. For example, the DirectQuery and DirectQueryUpload forms allow a user to directly type in a query. Other candidates for this area are forms that provide customized output, or lead to password protected areas of the deployment. |
Table of Contents
The user may use a prexisting form if it has been built by either the deployer or a user who has used the Create Your Own Form. If no forms have been built, no prexisting forms will appear. The prexisting forms are built for general use, and can be found in the “forms” box under “data sets” (fig. 2.1). Prexisting forms are also known as generic forms. As an example, suppose the user possesses databases about medical patients and we wish to explore the biographical data concerning the patients such as race, gender, birthdate, nation of residence, etc.
All example data has been fabricated. Any similarity, likeness, or implication drawn from the content or data is completely unintentional. Any questions, comments, or concerns, please email us at <mrald-dev-list@mitre.org>.
The user must know which general form to use in advance, and again this information could be found through contacting the the deployer or other users knowledgable about the database. If the user knows patient information such as race, gender, and nation of origin is stored in the database, but is not sure which form to use, the user may find out by doing the following:
In figure 3.1, the user selects the form entitled “Medical History Items” guessing that this is the form he wants.
Figure 3.1.
Once the form has been chosen, the user will see a screen resembling figure 3.2.
Figure 3.2. Medical History Items Form
The green arrow in the top left of the form allows the user to navigate one page backwards (just as a normal browser).
The drop down menu in the top right of the form will allow the user to visit the MITRE homepage if the user chooses “MITRE” or will instead send the user to the user's MRALD homepage if the user chooses “HOME”.
The section entitled “Output Data Selections” (fig 3.2) shows what fields of the data MRALD can output. Each field can either be selected or ignored for output by using the provided checkboxes.
If the user does not find the particuliar data he or she wishes to search under this section, then the user must return to the MRALD homepage and choose a different generic form.
Here we see that the Medical History Items form has the following characteristics to describe it: “Name”, “Aliases”, and “Description”. Since the user's originial goal was to find out biographical information about the patients, the user knows that this is the wrong form to use. Instead, let the user return to the MRALD homepage and choose the form entitled “Subject” (fig. 3.3) which brings up the Subject form (fig. 3.4).
Figure 3.3.
Figure 3.4. Subject Form
From the new “Output Data Items” section (fig. 3.4) the user can see that the correct form was chosen given her initial desire to search personal information about the patients.
This section may or may not exist depending upon how the form was originally built.
Figure 3.5.
If the user wishes to compute statistics on the data then the user should skip the following section concerning Output Data Items and proceed directly to Normal Filters.
The Sort by option under the section Output Data Items allows the user to sort the order of returned entries from the query in the output. There are three levels of precedence, where the levels correspond to the the drop down menus from left to right. Each drop down menu allows the user to choose any field from the table to use. If the “Descending” checkbox is selected next to a field the field will be outputted in descending order, instead of the usual ascending order.
Suppose the user wishes to categorize his output data as: race, gender, and birthdate in their respective order. The user would specify these values resulting in figure 3.6.
Figure 3.6.
Under the next section in the form, “Filter Selections”, the user may search for records that must match specific criteria. For now, ignore the section entitled “Other Filters” and look to where the user is specified for a “Field”, “Operator”, and a “Value” to create a Normal Filter. In figure 3.7, the section is boxed in red. Normal Filters are for general use in filtering data for the user. The user specifies which operators will be applied to which fields to create an appropriate filter for the user's query. The operators are applied to the field and the result is compared to the input specified by the user in the “Value” box. If the result from the operation matches the input in “Value” then the record has passed through the filter and that field will be returned. If there are multiple filters then each record must pass through all filters (In effect a boolean “AND” is placed between filter clauses in the query). The operators are:
= : Standard boolean equals, i.e. tests if the current record's field “equals” the input in Value - if so then the current record will pass the filter.. |
!= : Standard boolean not equals, i.e. tests if the current record's field is “not equal” to the input in Value then the current record will pass the filter.. |
< : Standard boolean less than, i.e. if the current record's field is “less than” the input in Value then the current record will pass the filter. |
> : Standard boolean greater than, i.e. if the current record's field is “greater than” the input in Value then the current record will pass the filter. |
<= : Standard boolean less than or equals to, i.e. if the current record's field is “less than or equal to” the input in Value then the current record will pass the filter |
>= : Standard boolean greater than or equals to, i.e. if the current record's field is “greater than or equal to” the input in Value then the current record will pass the filter |
Contains : Tests if the current record's field “contains” the substring dictated by the input in Value. The user should note the filter is case sensitive. |
Starts With : Tests if the current record's field “starts with” the substring dictated by the input in Value . The user should note the filter is case sensitive. |
IS NULL : Tests if the current record's field exists. |
IS NOT NULL : Tests if the current record's field does not exist. |
Let the user choose to find medical patients that are caucasion and female (note caucasion is encoded as a “1”). The result is shown in figure 3.7
Figure 3.7.
In some cases the user may see a pre-built Normal Filter created when the form was orignally built. In a pre-built Normal Filter the “Field” input has already been chosen as well as the “Operator” input. The user is given default options for the “Value” input (where these default options were chosen by the form builder); however, the user may enter his own choices for the “Value” input. It is the form builder's job to clearly label the relevant “Field” and “Operator” above the filter. Shown below is a pre-built filter for the race “Field”. Note the user may choose any number of race options to pass through the filter via checkboxes. In addition, the user may use the textboxes on the far right to enter his own choices for the “Value” input.
At the top of the section entitled “Filter Selections”, OR Filters may be available to the user. An OR Filter uses radio buttons to let the user choose a Value.
It is very similiar to a Normal Filter. The difference is that an OR Filter allows for the comparison of two fields simultaneously. If one field or both fields match the comparison by their specified operators to a specified value then the record will pass. Thus an OR Filter is like applying a standard boolean OR operator to two Normal Filters, only one value will be tested for both fields. The relevant fields to the OR Filter should be clearly labeled above the available values. It is the form builder's duty to ensure that future users will be able to ascern which fields are being used.
In addition to letting the user choose a default value specified when the form was built, an OR Filter allows the user to choose the value “none” (on the far left) or a custom value that must be entered into the provided text box(on the far right).
For this example, the user can see an OR Filter has been built to let the user choose a value for primary_language OR second_language (boxed in red) in figure 3.8. Let the user choose english (let it be encoded as the value 3). Since 3 is not available, the user must enter a custom input by selecting the rightmost radio button and entering the value. Thus, if English is the patient's Primary Language or if it's the patient's Secondary Language, the patient will pass through the filter. See figure 3.8
Figure 3.8.
Range Filters must be built using the form builder and will not appear on a form by default. If they are built, they will appear at the top of the section entitled “Filter Selections”. See Chapter 4 on Form Building for information on building a Range Filter.
Here the user can see that a Range Filter has been built for the birthdate field. Suppose the user wishes to select patients who were born after January 1, 1975 and before January 1, 1985. The user would enter these respective dates as the minimum and maximum so that any dates in between would be filtered through (see fig. 3.9).
Date format is database dependent for Range Filters. Formats should be specified clearly by a label when the form is built.
Figure 3.9.
In addition to filtering, MRALD gives the user the power of applying statistical functions to numeric fields in the data.
Statistical functions are to be used seperately from a query. Instead of returning matching records to criterion specified by the user, MRALD will perform statistics on specified fields and return the results. If the user wishes to use statistical functions, no Output Data Items should be selected. Note that if the Time Selection section exists at the beginning of the form it must be completed. Filters may be used in conjuction with statistics so the stats are only performed on certain records. The output from using MRALD's statistical function will return statistics for user defined groups only (more on “Group By” feature below).
If the user wishes to use statistical functions, in the “Function” drop down menu under the section entitled “Statistical Functions” the user will find nine functions available for use. They are:
Count(*) : Returns the total number of records for all tables in the database. Null records are included. |
Count : Returns the total number of records that have a Non-Null value for the chosen field. Repeated records are counted twice. |
Count Distinct : Returns the total number of distinct records that have a Non-NULL value for the chosen field. |
Max : Returns the maximum field value for all the matching records in the data set (only valid on numeric fields). |
Average : Returns the average field value for all the matching records in the data set (only valid on numeric fields). |
Standard Deviation : Returns the standard deviation for all the matching records in the data set (only valid on numeric fields). |
Sum : Returns the sum for all matching records in the data set (only valid on numeric fields). |
Variance : Returns the variance for all the matching records in the data set (only valid on numeric fields). |
After selecting the proper Function, the user must specify which field to apply the function to in the box labeled “Field”. Numeric fields will appear by default, however non-numeric fields may appear as well depending upon how the form was built (see Form Building for more information).
The “Group By” feature under the “Statistical Functions” section lets the user compute statistics on particuliar groups within the set of all records. The user may group the data in up to three levels. Each level further subdivides the grouping to narrow in on more specific groups. For instance, grouping people based on race would provide statistics on each race. Grouping people based on race and then based on gender (2 levels) will provide statistics on each race for each gender.
To wrap up Statistical Functions, let the user find the Average educational level for caucasion females, Grouped By gender and then by handedness. Figure 3.10 shows the complete form for use of Statistics. Figure 3.11 shows the results.
Figure 3.10.
Figure 3.11.
Under the section entitled “Output Size and Format Selections” the user may or may not limit his or her output size by selecting one of three radio buttons under “Output size limit”. If no limit is necessary, select “None”, if the limit is to be defined by the number of matching records, select “Lines” and enter the maximum number of matching records (note the default is 500). Finally, if the limit is to be defined by the output file size, select the rightmost option and specify the limit in Megabytes (floats are allowed e.g. 0.01 gives roughly 10 Kilobytes worth of data).
The user may see redundant matches to his or her query by selecting “Include duplicate rows in returned data”.
MRALD allows three options for the destination of the query. They are:
Browser: The results of the query will be displayed in the user's browser window. |
File: The results of the query will be printed to a text file. The file may be saved on the user's local disk or directly opened. |
Excel: The results of the query will be opened by Microsoft Excel. If the user does not have Microsoft Excel installed on their local machine or their file associations are not set up properly, the output will be downloaded as an html table. |
The three destination options are labeled as columns under the section entitled Output Size and Format Selections. Once the user has decided which destination to use, the user must decide in what format the output will be displayed. Formats are chosen by selecting the proper radio button under whichever destination has been chosen. Possible formats are:
CSV: Comma Seperated Values i.e. the output will be printed out to either a browser or a file, with each successive matching record seperated by a line and each field for that record comma delimited (by default, a comma is used). If the user wishes to use an alternate character delimiter, the user may enter it into the provided text box. |
Text: The output will be opened in a browser or downloaded into a file, with each successive matching record seperated by a line and each field for that record tab delimited. The result will read like a table without borders. |
Table: Output will be printed to an HTML table in the browser window with each successive matching record seperated by a line and each field represented by a column. |
XML: Output will be printed in XML in the browser window. |
To finish the example query into the database of medical patients, let the user choose the browser window as the Destination and an HTML table as the Format. Also, let the user restrict his query to only 10 matching records. The user should then click “Retrieve Data” at the bottom of the form and will see the final result in the user's browser window. Figure 3.12 shows the matching records from the query.
Figure 3.12.
Table of Contents
In order for the user to create their own form, the user must be familiar with using forms. More information about using prebuilt forms can be found in Chapter 3. First, click “Build a new form” under “personal forms” on the MRALD homepage (fig. 4.1). The user will then see a screen resembling figure 4.2.
Figure 4.1.
Figure 4.2. Step 1 Interface
Select A Page Title: The page title is the phrase that will appear in multiple places in the MRALD system after the form has been created. Although it is only a line in the xml file, the page title will appear both as the title and header of the created form as well as the text for the link to the form from the main page. In addition, if you desire to “bookmark” or add the page to your “favorites”, the page title will also appear as the name of the bookmark. For this example, let the user name the form “NewForm” (fig. 4.2).
Select Related Table Search Depth: The joins that MRALD will follow to determine the set to use for the new form. If the structure of the table relationship is such that a shallow depth produces a relatively large number of tables, the succeeding step in the form builder may take longer than expected. For this example, let the user concern his query with only the selected tables, so the table search depth will be 0 (fig. 4.2).
Select Tables To Be Used: Each row of this table represents a specific table , or view, in the database. The checked tables are the set the new form will be built from. For this example, let the user choose to search the tables “people” and “images” (fig. 4.2). The center column is the system name for the table and the far right column represents the system comments for the table. These comments are filled in by the database administrator when the table are created. No comments will be displayed as “Null”. For Oracle databases, these comments are not shown by default. See the Deployer's Guide for guidance.
All example data has been fabricated. Any similarity, likeness, or implication drawn from the content or data is completely unintentional. Any questions, comments, or concerns, please email us at <mrald-dev-list@mitre.org>.
Once appropriate tables have been selected, click “Proceed” to continue. If the user wishes to clear all selected tables, click “Reset Form” to do so (see fig. 4.3)
Figure 4.3.
Figure 4.4.
Configure Links: If only one table was selected in Step 1 then skip the section entitled Link Configuration. Otherwise, the links that appear in the rows of the table are the links that currently exist in the data model (fig. 4.4). The “Primary” and “Foreign” columns are the primary keys and foreign keys between the tables. For this example, the primary key is “peopleid” and is uniquely defined in the table for people. In the image table, each image uses a “peopleid” as a foreign key to map the image to a person.
In some cases, the user may choose to ignore certain links because of the irrelevance to the desired results. By choosing “Ignore” as opposed to “Full”, the link will not be considered and the form and resulting queries will disregard the relationship. In addition, the form provides drop-down menus for artificially creating links in the customized form. This allows the user to add a relationship constraint that may not be include in the data model. For more information about form structure, see the glossary term form.
This only affects the created form. It does not change of the data model or any other forms created on these tables.
Figure 4.5.
Choose General Options: Each row pertains to a specific field in a given table (fig. 4.5). The checkboxes underneath the column titles act as a "Select/Deselect All" function, which means that all the columns will be toggle to the value of the top checkbox. Each of the column titles mean the following:
Ignore: If the user has no need at all for the current field, he can choose to ignore it and the rest of the values or fields you set for this field will be ignored. This provides the user a quick way to disregard a field without having to check or uncheck every single box. Note that ignored fields will not be present in the resulting file, so further editing will not e possible. If the user unchecks every box, there is the possibility of using the editor to put the field back on the form. Let the user ignore the “latticgroupid” in the people table (see fig. 4.5). |
Table This is simply the table in which the current field exists. In this example there are two tables, images and people. |
Column Name This is the system name for the current field. |
Column Label This is an available label for the current field as it will appear on the created form. This can be used to translate a cryptic system name into something all users can understand. It will not effect the query or results, only the way the name is displayed on the form. In addition, the label will also appear as the column header on the returned results. By default, the column label will simply be the system name. |
Output If you will ever have need for the values of this field, leave this column checked. This column controls whether or not the field appears in the available output selection on the created form. If this column is not checked, there will be no way to select the field as output. For this example, output all fields except for “latticegroupid”, because it has been Ignored. |
Default Selection In order to assist some of our users as a speed up the time from start to finish, MRALD provides the ability to define the pre-selected output fields. This means that whenever the created form is viewed or visited, this collection of fields will already be included in the output. It's useful if the user wants to build a form that retrieves the same set of data each time (Of course, this is as customizable as the rest of the form. These fields can also be unchecked if not desired). For this example, let all fields have a Default Selection. |
Filter In order to reduce the clutter and confusion, MRALD gives the user an opportunity to select the fields that will show up in the available filter fields. With some forms, the field list can be quite large. This column allows the user to set which fields will appear under the filter drop-down menu. For this example, let all fields be available to filter operations. |
Stats This column allows the user to choose which fields will be include in the created form's statistical filter. Again, the user has the ability to choose which fields will appear in the drop-down menu. A standard set of statistical functions is provided. This list can be modified by contacting the system administrator for the user's deployment. By default, only numeric fields are included, however the user may want to include an field he feels a statistical function such as Count (which counts the number of matching records) which may be relevant. For this example, let all fields be available to statistical operations, as long as the field makes sense in a statistical operation. Fields such as “filename”, “email”, and “password” are not applicable, so they are not checked. |
Group By This column allows the user to choose which fields will be included in the created form's “Group By” section, which allows for grouping the returned results. This is usually used in conjunction with statistics, so that statistics may be performed on certain groups in the data. For normal output, using the Sort option under the section entitled Output Data Items is faster. Again, the user has the ability to choose which fields will appear in the drop-down menu. For this example, every field is checked, and will be accessible to grouping in the form. |
Order This column determines the order of the fields that will appear available in the output from left to right. This column also allows for decimal numbers, i.e. 1.02 is after 1 but before 2. If a field is ignored or not included in the output, the associated order number will be ignored, so there is no need to make the numbers continuous. The list will be sorted from smallest to largest so missing numbers will not affect the performance or results. For this example, the order will stand as is. |
Sort This column allows the user to choose which fields will be included in the created form's sorting feature. This feature allows the user to sort the results of the query by a desired field. Again, the user has the ability to choose which fields will appear in the drop-down menu. For more information about sorting, see Sort By in Chapter 3. For this example, every field is checked, and will be available to sorting in the form |
Format For numbers and dates, this column allows the user to specify how they would like the format. This will only affect the output of the number, not how it is stored in the system or how it is filtered or sorted in the query. It is especially useful for outputting dates. For this example, let the format be by default. |
Comments These are the comments entered for the field when the data model was entered into the system. Contact the sysadmin for your system if you need to add or change the comments. Any fields will no commments will appear as null. For this example, there are no comments for any of the fields. |
For this example, see figure 4.5 for the final result.
Figure 4.6.
A filter (fig. 4.6) allows the user to make an easy adjustment to the query that will respond with specific information. By creating a filter with the form builder, when the form is built the filter will always be displayed by default without the user having to create his own every time. Suppose the user wishes to create a filter for airport data, filtering only certain airports through. Entering the title as 'Departure Airports' and using the corresponding field, the “=” operator, and the values, “LAX”, “JFK”, “IAD” (different airports), yields the following output on the created form:
Include a DESCRIPTIVE title, since the field will not be displayed
Consider entries for each of the fields you feel your users will want to do frequent filtering on. This type of filter makes the form much easier to use, as it provides a convenient way for the users to filter their data, and gives examples of the type of data stored. The result of this type of filter is a query clause like
AND (dep_airport='LAX' or dep_airport='DFW' or dep_airport='IAD' or ...)In other words, one field can have any one of many values.
Figure 4.7.
An “OR” filter (fig. 4.7) allows the user to make an easy adjustment to the query that will respond with specific information. This option allows the user to specify that two fields are equal to (or other operator relationship to ) a single value. The principle difference between this filter and the standard Filter is the standard filter allows for a single field to equal two or more values whereas the “OR” filter allows two fields to equal one value. For example, entering the title as “Departure and Arrival Airports” and using the corresponding fields, the '=' operators, and the values, “LAX, JFK, IAD”, yields the following output on the created form, which allows the form user to restrict the query results to those records where the arrival or departure airport is one of the values listed, or as entered into the text input box. An example:
Include a DESCRIPTIVE title, since the field will not be displayed
The result of this type of filter is a query clause like
AND (dep_airport='LAX' or arr_airport='LAX')
Figure 4.8.
A Range Filter (fig. 4.8) allows the user to designate a source field by which a range can be used. This feature can be used on anything from longitude and latitude degrees to counts or number fields. The chosen field will be used but the desired field label will be displayed on the created form with the words “Range of” preceding whatever the user chose as text for the label.
If the user selects a field that can have multiple formats (such as a date), it is imperative that the user specifies the format clearly in the label
Figure 4.9.
A Time Filter (fig. 4.9) allows the user to add the availability of a time constraint on a date and time field. A Time Filter will appear under the section “Time Selection” in the built form. All aspects of the Time Filter will be configurable, including the enter Start and End Date. The "Time Based on Event" field will be populated by all the Time/Date fields not ignored when created the form. MRALD will find all date based fields and provide them in the drop down box. Leaving these text boxes blank will prevent a date filter from appearing on the resulting form, even if date-based fields are available. As an example:
Once all filters have been set, click “Build Form” to create your permanent form for use. The form will appear at the user's MRALD homepage under the section entitled “personal forms” (see fig. 4.10)
Figure 4.10.
Figure 4.11.
From the main menu, your form will appear under “personal forms”. Click on the link to use it (fig. 4.11).
Download Form: If the user chooses to download his or her form to a directory, click on the icon and the user will be prompted to specify a directory for the file transfer
Edit Form: The user may edit his or her form by repeating Step 2 with the previous entries still existent.
Fields that were previously marked as Ignore, when the form was last built, will not be available.
Share Form: The user may share his or her form with other users connected to the current computer
Specify the appropriate users you wish to share the form with
The Direct Query feature of MRALD is for users who are knowledgeable about the underlying data model, such as the deployers or the system administrators, and are fluent in SQL.
Figure 5.1.
From the main menu, choose “Direct Query” under “special forms” and Click “Go”. (fig. 5.1).
Figure 5.2.
Enter an SQL Query in the text box labeled “Query Text” (fig. 5.2).
Figure 5.3.
Select Output Size and Format Selections (fig. 5.3)
Finally, click “Retrieve Data” (fig. 5.4) and the output will be printed to the destination in the specified format.
Figure 5.4.
From the main menu, choose “Direct Query File Upload” under the section entitled “special forms” and Click “Go” (fig. 5.4).
Figure 5.5.
In the textbox labeled “Query File” browse the local disk to find the classpath for the file containing the query (see fig. 5.5). Click “Retrieve Data” to start the query.
Format is to be one query per line, with the ending semicolon optional.
Each query will appear next to a checkbox, select those queries which should be executed and Click “Retrieve Data” to return the results as desired. Again the user may choose the Output Size and Format Selections (see fig. 5.6).
Figure 5.6.
Q: How can I contact the deployer? A: The deployer may be contacted by email at < mrald-dev-list@listsrv.mitre.org>.
Common Error List
ERROR: Attribute table.attribute must be GROUPed or used in an aggregate function : An individual field cannot be included in the output unless it is used in the “Group By” clause during statistical operations. In other words, no “Output Data Items” should be checked unless they are used in the “Group By” section under Statistics. If this did not help you or did not cause this errorname or you have any questions please contact us using the link below.
ERROR: pg_atoi: error in “Value”: can't parse “Value”.: The Value input under Filter Selections has been given an invalid value. MRALD cannot parse the value because it does not match valid entries for the specified field.
ERROR: Function function(character) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts : The attempted conversion of a character string to a number failed during statistical operations because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in statistical functions such as Average, Standard Deviation, Sum, and Variance requiring arithmetic operations. Note only numeric fields may be added to or subtracted from dates. If this did not help you or did not cause this errorname or you have any questions please contact us using the link below.
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list : This error name occurs when a query is built that sorts on a field that is not selected for output and filtered as “Distinct”. In order to retrieve your data, use your browser's back button and either make sure you have selected for output all the fields you are sorting on, or check the box in the output selections that reads “Include duplicate rows in returned data”. Note this will cause you to get duplicate data if you have mulitple, identical rows. If this did not cause this errorname or you have any questions please contact us using the link below.
ERROR: Invalid date format: Entered Date. Please reenter date. The expected formats are month/day/year. If you entered a time, the format should be either hour:minute, or hour:minute:second.: This error name occurs when the user has entered an invalid date under the Time Selection section. In order to retrieve the data, the user should use the browser's back button and reenter the date in the specified format.
ERROR: Bad date external representation 'Entered Date': This error name occurs when the user has entered an invalid date in a Range Filter. In order to retrive the data, the user must use the browser's back button and reenter the date in the appropriate format (which should be specified in the filter label).
ERROR: Processing of multipart/form-data request failed. Stream ended unexpectedly: This error name occurs when the user has entered an invalid file name in the Direct Query File Upload section of MRALD. The user must ensure that the file name is correct and the file follows the specified format for multiple queries (one query per line, with ending semi-colon optional).
ERROR: You may only perform queries through this form: This error name occurs when the user has entered an invalid SQL query in the Direct Query section of MRALD. In order to retrive the data, the user must use the browser's back button and reenter a valid SQL query.
MRALD has no funding of it's own. It's creation and advancement have been accomplished during the ongoing business of other projects within MITRE. Any new features you might want added will have to be paid for by your project. In addition, if you use MRALD on your project, we'd appreciate a bit of staffing to help with ongoing enhancement, training, documentation, and maintenance. Please contact us at <mrald-dev-list@listsrv.mitre.org>
This section aims to provide the user with a basic understanding of databases and the framework for MRALD. In other words, what is a database, and how does MRALD search that database?
In its most general form, a database is simply a collection of related information stored in a structured format. The same can be said for a table, however a table is a single store of related information while a database can consist of one or more related tables. A database consisting of multiple related tables is known as a relational database. To make the structure more concrete, consider a table where student records are stored. Each record consists of an individual student's information, such as name, id, and home address. Another table may store information for teachers such as name, class, and students. Finally, a third table could contain records of schools in a region, characterized by faculty, level of education, and mascot. A relational database would relate the three tables, and could answer such questions as “How many students named John have a teacher named Betty and a school mascot of a Zebra”. As you can see, a query into a database can provide very accurate interpretations of very large chunks of data.
A relational database relates seperate tables by using primary and foreign keys. A primary key is a unique identifier for each record in a table. An example for the table representing student records is the student's id (let's assume it's unique e.g. a Social Security Number). Now we can map a student's id in the student table (using the primary key) to a student's id in the teacher table (the foreign key). Thus we know who each student's teacher is (assume each student has only one teacher). Now we can obtain a teacher's id and use it as a primary key to map to the teacher's id in the school table. Finally we have our answer, the student has been indirectly mapped using primary keys and foreign keys to his or her school mascot (see fig. A.1).
Figure A.1.
One of three main ways to submit a query into the database. For advanced users with a good understanding of SQL and the underlying data model, a Direct Query will take an SQL statement entered by the user and execute it on the database (see Chapter 6).
Similiar to a Direct Query but instead of entering the query in the browser the query is written into a text file stored on the local disk of the user. Multiple simultaneous queries are allowed. Format for the file is one query per line, with ending semicolon optional.
A Field is a column of a table. It is a single unit of data stored as part of a database record. The value describes the data in a particuliar way. As an example, in a table of people, a potential field could be a birthdate. Each entry of the table, which would be a particuliar person, would have a value of this field.
A filter restricts the results of a query to a database
A foreign key is a field in a relational table that matches the primary key column of another table. It is used to relate the data in a Relational Database Management System.
A form lets the user designate how he or she wishess to search the database. It contains a title, designates the tables, joins, filters, and fields to be used in the query. An MRALD form represents a set of database tables and a unique way of joining them together to retrieve data around a certain concept. Trying to force a single form to do more than this will cause unpredictable or incorrect results (it is possible to build an MRALD form that will build multiple queries and therefore bypass these issues, however these forms must be hand built).
A special min-app that any user can use to build their own forms. These forms will then be saved as personalized forms (see chapter 3). The administrator can take these forms directly and place them in the personal forms section of the MRALD main page for everyone to access if appropriate.
Java DataBase Connectivity Driver: JDBC is a Java Applicaton Program Interface (API) that enables Java programs to execute SQL statements. This allows Java programs to interact with any SQL-compliant databases. Since nearly all relational database management systems (DBMS's) support SQL, and because Java itself runs on most platforms, JDBC makes it possible to write a single database application that can run on different platforms and interact with different DBMS's.
Join is used in SQL to combine the data contained in two relational database tables based upon a common field.
The primary key of a relational table uniquely identifies each record in the table. It can be any field of that particular data set that is guaranteed to be unique for every entry in the table. All foreign keys must map to a primary key, but the all primary keys do not necessarily have to map to a foreign key.
Queries are the primary mechanism for retrieving information from a relational database and consist of a description of the data desired. Many database management system use the Structured Query Language (SQL) standard query format.
A record is a row in a table. It represents an individual object in that table, and that object holds characteristic fields to describe it. An example is Dr. Jon Smith in a table of doctors.
Structured Query Language - an industry standard language used for manipulation of data in a relational database
In a relational database, a table is a predefined format of rows and columns to define an entity. The rows in a table represent an individual record while the columns in a table represent all representive fields of the entity to describe a particular record.
Focusing on a subset of data in a larger database. For instance suppose the database is composed of medical information, a view into the database would be examining a subset, such as biographical information about the patients.