Access Mid term

The condition, ____, entered in the Criteria row of a long text field in a query window would retrieve all records where the long text field had any mention of education.

*education*

What are the 3 phases of a query?

1. Parsing: The DBMS parses the SQL query and chooses the most efficient access/execution plan. 2. Execution: The DBMS executes the SQL query using the chosen execution plan. 3. Fetching: The DBMS fetches the data and sends the result set back to the client.

Define Data Sparsity?

A column distribution of values or the number of different values a column can have. So fewer values = lower sparsity. If you only have Male and Female as choices there is low sparsity because each value is relatively common! By contract a DOB would have high sparsity, since the occurrence of that value would be rare.

Parameter

A criteria value in a query that can be changed is called.

Expression

A formula that performs a calculation.

Outer join

A join that is typically used to display records from both tables, regardless of whether there are matching records.

Totals query

A query that calculates subtotals across groups of records.

Action query

A query that enables you to create a new table or change data in an existing table.

Parameter query

A query that prompts you for criteria before running it.

Crosstab query

A query that uses an aggregate function for data that is grouped by two types of information and then displays the data in a compact, spreadsheet-like format.

What is an SQL cache?

A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions. Also called procedure cache.

Define DBMS performance tuning?

Activities to ensure that clients' requests are addressed as quickly as possible while making optimum use of existing resources.

What makes up SQL Performance Tuning?

Activities to help generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end.

Append query

An action query that adds new records to an existing table by adding data from another Access database or from a table in the same database.

Make table query

An action query that creates a new table by extracting data from one or more tables.

Update query

An action query that is used to add, change, or delete data in fields of one or more existing records.

Delete query

An action query that removes records from an existing table in the same database.

Compare Automatic Query Optimization to Manual Query Optimization?

Automatic query optimization is a method by which a DBMS finds the most efficient access path for the execution of a query. Manual query optimization requires that the optimization be selected and scheduled by the end user or programmer.

What are the 5 typical bottlenecks for DBMS processing?

CPU, RAM, Hard Disk, Network, App code

What are the fundamental physical structures a db lives on in the computers that house it?

Data Files:A named physical storage space that stores a database's data. It can reside in a different directory on a hard disk or on one or more hard disks. All data in a database is stored in data files. A typical enterprise database is normally composed of several data files. A data file can contain rows from one or more tables.

Static data

Data that does not change.

What are the predefined increments a data file can expand as called?

Extents

What kind of hash index is good for evaluating equality conditions by?

Hash

Total row

In Access queries, you can use aggregate functions in the Datasheet view by adding a.

When is first row optimization typically employed?

In transaction systems. You can engross yourself with the first few lines while the rest loads.

Why are indexes crucial to speeding up data access?

Indexes are crucial in speeding up data access because they facilitate searching, sorting, and using aggregate functions and even join operations.

Arithmetic operators

Mathematical Symbols such as +,-,*,and / that are used to build expressions are known as.

Does the SQL cache store SQL as written by user?

NO! The SQL cache does not store the SQL written by the end user. Rather, the SQL cache stores a "processed" version of the SQL that is ready for execution by the DBMS.

Aggregate function

Performs a calculation on a column of data to return a single value.

Unmatched records

Records in one table that have no matching records in a related table.

Compare Static Query Optimization to Dynamic Query Optimization?

Static Query Optimization is a query optimization mode in which the access path to a database is predetermined at compilation time. Dynamic Query Optimization is a process of determining the SQL access strategy at run time, using the most up-to-date information about the database.

What is the fastest Access Plan?

Table Access (row ID).

What is the slowest Access Plan?

Table Scan (full).

What are Data Files grouped into?

Table Spaces AKA File Group Examples: System Table Space (where data dictionary lives), User Table Space (user created tables), Index Table Space, Temporary Table Space (sorting ,grouping).

What performs SQL parsing?

The Query Optimizer: A DBMS process that analyzes SQL queries and finds the most efficient way to access the data. The query optimizer generates the access or execution plan for the query. This is the most time consuming part of the query processing!

What is the Listener?

The listener process listens for clients' requests and handles the processing of the SQL requests to other DBMS processes. Once a request is received, the listener passes the request to the appropriate user process.

What is the Optimizer?

The optimizer process analyzes SQL queries and finds the most efficient way to access the data.

How does the processing of DDL statements differ from that of DML statements?

The processing of SQL DDL statements (such as CREATE TABLE) is different from the processing required by DML statements. The difference is that a DDL statement actually updates the data dictionary tables or system catalog, while a DML statement (SELECT, INSERT, UPDATE, or DELETE) mostly manipulates end-user data.

What is an access plan?

The result of the SQL Optimizer's parsing. It contains a series of steps the DBMS is going to use to execute the query in the most efficient way. Access plans are DBMS-specific and translate the client's SQL query into the series of complex I/O operations required to read the data from the physical data files and generate the result set

What is the Scheduler?

The scheduler process organizes the concurrent execution of SQL requests.

Inner join

The type of join in which only the records where the common field exists in both related tables are displayed in the query results.

How does a user interact with the DBMS?

Through one or more user processes assigned to that user.

Find Duplicates Query Wizard

Used to perform a query that locates duplicate records in a table.

Join

What is used to help a query return only the records from each table you want to see, based on how those tables are related to other tables in the query.

Crosstab query

What type of query has to include at least one row heading, one column heading, and one summary field.

Source table

When creating an append query, the table from which records are being extracted is called the.

Destination table

When creating an append query, the table to which you are appending the fields is called the.

Where are Bitmap indexes often used?

When there is low sparsity!

Where are B-tree indexes often used?

Where there is high sparsity.

Find Unmatched

Which Query Wizard is used to locate records in one table that do not have related records in a related table.

[hourly wage]+[.5]

Which expression would be used to update the current hourly wage by $.50

Sum

Which function should be used to add a column of numbers and return a single value.

calculated field

a field that obtains its data by using a formula to perform a calculation or computation

To add spreadsheets to a table, you would use the ___________ field type.

attachment

To use the Input Mask Wizard, select the Input Mask property in the field's property sheet and then select the ____ button.

build

What are the reserved memory areas where recently accessed data blocks are stored called?

data or buffer cache. system catalog and index data can also be found here.

When us last row retrieval minimization often used?

embedded SQL and stored procs.

Any row and column spacing changes made to a datasheet are saved automatically when you close the table.

false

Once the tab order for fields is determined, it cannot be changed.

false

Rows in a datasheet may be different sizes.

false

When you add a title to a form using the Title button, Access places the title in the Detail section.

false

When you enter data in a long text field, Access automatically expands the row and column to display all the data in the field.

false

To create a simple form with a datasheet, select the table that is the "one" part of a one-to-many relationship in the Navigation Pane, click CREATE on the ribbon, and then click the ____ button on the CREATE tab.

form

You would use a __________ field type to add the address of a company's web page in a format that would allow you to easily access it.

hyperlink

A(n) ______ specifies how data is to be entered and how it will appear.

input mask

The ____ data type can store up to a gigabyte of text.

long text

To insert data into an Attachment field, use the ____ command on the Attachment field's shortcut menu.

manage attachments

Changing the value of the Tab Order property for a control to ___________ will bypass the control when the TAB key is pressed.

no

What is one of the most common bottleneck events caused by?

one of the most typical bottlenecks is caused by transactions competing for the same data rows.

To change the Tab Stop property for a control, select the control, click the ____ button on the FORM DESIGN TOOLS DESIGN tab, and change the value of the Tab Stop property from Yes to No.

property sheet

To change the special effect of a label, select the label, click the ____ button on the FORM DESIGN TOOLS DESIGN tab, and then click the Special Effect property arrow.

property sheet

To resize a row so that more data can appear, drag the lower edge of the _____ to the desired size.​

record selector

A form that is contained within another form is called a(n) ____.

subform

A form that contains a subform includes navigation buttons for both the form and subform.

true

In Datasheet view, an Attachment field appears as a paper clip rather than the field name.

true

The three possible size modes are : Clip, Stretch, and Zoom.​

true

When a form includes a subform, the subform is a separate object in the database.

true

When data, such as a picture, is inserted into an OLE field, Access does not immediately display the picture.

true

When entering data in a field that has an input mask, Access will insert the appropriate special characters in the proper positions.

true

The __________ picture size mode is the best option for photographs​.

zoom