Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/52

Click to flip

52 Cards in this Set

  • Front
  • Back
What is a database?
A collection of data structured and stored with the goal of minimizing redundancies and facilitating manipulation and retrieval
Relational database is structured as a collection of tables
Columns are called fields – you’ve seen this before
Rows are called records
Uses and Benefits of Relational Databases
Data – pieces of information
Database Management System (DBMS) provides organizations with the means to store, maintain and gain access to their data.
Planning a Database
First: Is this the correct tool?
What are the required results?(output)
What information is available? (input)
Sketch the structure - including the data types
Steps to create a new database
Step 1: Create the database file.
Step 2: Create tables.
Step 3: Define the relationships between the tables.
Step 4: Create input forms (maybe).
Step 5: Populate the database.
Step 6: Create Reports.
Step 7: Query the database.
Creating a Table
From the table option, you select new
In design view you can
add/delete/change fields
add descriptions
modify properties
assign keys
FIELD NAME: Identifies data that is entered into the field
Rule 1: It should be descriptive of the data and can be up to 64 characters in length, including letters, numbers, and spaces.
Rule 2: It includes letters, numbers and some special characters
Rule 3: Do not use spaces.
Properties of the Field
It is a characteristic or attribute of an object that determines how the object looks and behaves.
Each field has its own set of properties that determine how the data in the field is stored and displayed.

Examples of properties:

Field Size, Format, Input Mask, Caption, Default Value, Validation Rule, Validation Text, Required, Allow Zero Length, Indexed
Data Types
DATA TYPE: Determines the type of data that can be entered and the operations that can be performed on that data
Access recognizes the following data types:
Number (can be used in calculations)
Text (alphanumeric characters up to 255 per field)
Memo (alphanumeric value of unlimited length)
Date/Time
AutoNumber (numeric value, automatically increases)
Yes/No
OLE Object
Currency Hyperlink
PRIMARY KEY
It is a field (or combination of fields) that makes each record in a table unique. Every table must have a primary key and each record must have a unique value as its primary key.
Relationships
PRIMARY KEY: It is a field (or combination of fields) that makes each record in a table unique. Every table must have a primary key and each record must have a unique value as its primary key
Tables and their relationships to each other are stored
Tables are related in a one-to-many (or one-to-one) relationship
A foreign key is required to establish the relationship – one table’s foreign key is another table’s primary key.
Referential Integrity
The tables in a database must be consistent with one another. For example, you can not delete a record in the Customer Table if it has related records in the Orders table.
Database limitations
Access Limitations
Database limitations
2 gigabytes in size
Maximum of 32,768 objects
64 character maximum in an object name
14 character maximum for passwords
Support up to 255 concurrent users
Table limitations

Access Limitations
Table limitations
have up to 64 characters in each field name
have up to 255 fields
be one of 2048 concurrent open tables
be up to 1 gigabyte
have 32 indexes
be sorted by up to 255 characters in one or more fields
Relationships
Access is a relational database
Tables and their relationships to each other are stored
Tables are related in a one-to-many (or one-to-one) relationship
A foreign key is required to establish the relationship
One to Many
Relationships
Table 1 can have many of table 2, table 2 can only have 1 of table 1
Ex. A father can have many biological children, but each child can have only one biological father.
Many to Many
Relationships
Table 1 can have many of table 2, and table 2 can have many of table 1
Ex. Each class can have many students, and each student can take many classes
One to Many Example
One to Many Example
Customer can have many orders, but each order can only belong to one customer
Building Forms
A form provides an easy way to enter, change, and display the data stored in a table.
Methods for Building Forms
Methods
Autoform wizards – 5, each with a different form layout. Allows use of one table only
Form Wizard – user specifies one or more tables and display format
Design View – user has control over fields, formats and calculations
Forms
Parts of a form or report
Header – top section that stays the same for all pages of the form / report
Might include the form / report title
Body – middle section that changes based on the data derived from the database
For example, if referencing a customer table, the body section would automatically change for each customer as one moved through the table
Footer - bottom section that stays the same for all pages of the form / report
Might include page numbers, date, etc.
Controls
Forms
Objects on a form or report that accept and display data
Types of Controls
Forms
Bound control
Has a data source
Unbound control
Has NO data source
Calculated control
Data source is an expression (e.g., addition of two fields)
What is a query?

Queries
“Let’s you see the data you want in the sequence you want it”
Query components
Dynaset – window that shows the results of a query

Selection Criteria – specifies how you want the data to be filtered

Sort – allows you to sort in ascending or descending sequence

Show checkbox – allows you to show or not show the field in the query results
Form
Provides an interface for interacting with data stored in tables
Used by individuals that are tasked with data entry / editing / deleting
Report
Provides a graphical view of data stored in tables
Often used by managers to provide a simple explanation of complex data
Building Forms
A form provides an easy way to enter, change, and display the data stored in a table.
Methods
Autoform wizards – 5, each with a different form layout. Allows use of one table only
Form Wizard – user specifies one or more tables and display format
Design View – user has control over fields, formats and calculations
Report Header
appears once at the beginning of a report
Report Footer
appears once at the end of a report
Page Header
Page Header appears at the top of every page in a report and can be used to display date and page numbers
Page Footer
Page Footer appears at the bottom of every page in a report and may contain date and page numbers
Group Header

*** Group Header and group footer are used only when the records in a report are sorted (grouped) according to a common value in a specific field.
Group Header – appears at the beginning of a group of records to identify the group
Group Footer

*** Group Header and group footer are used only when the records in a report are sorted (grouped) according to a common value in a specific field.
Group Footer - appears after the last record of a group and contains summary information about the group
Detail Section
Detail Section – Appears in the main body of a report and is printed once for every record in the table
What is a Query ?
A query lets us see the data we want in the sequence that we want it.

It lets us select specify records from table (or from several tables) and show some or all of the fields for the selected record.

It also lets us perform calculations to display data that is not explicitly stored in the underlying table. For example: customer’s age.
Queries are more powerful than filters because:
Can retrieve data from multiple tables
Can add calculations to the results
Can select records for display in a form or report
Most common type of query
Features:
Display selected rows of data
Display selected columns of data
Sort query results
Calculate within records (i.e., calculating gross pay for each employee record)
Group records and create subtotals (i.e., subtotaling expenses for each department)
Calculate totals such as sums, counts, and averages
Simple Queries
Add the tables you want (Query-> add table)
Double click on the fields you want to include in your query
Can show fields or not, sort them or not, you can hide several fields and sort by as many fields as you want
Can also use the criteria row to select only certain records
advanced Queries
Using the “and”, “or” and “not” conditions
Using relational operators ( <, >, =, <>)
Using “Is Null” – try with student address
Using “Between…And…”
Adding calculated fields
Using the expression builder
Calculated field
Calculated field – holds a mathematical expression whose results will be displayed in the field

Calculated fields can be entered directly into the design grid, or created using the Expression Builder. To access the builder, right click on any field in the query grid and choose “build”…
Entering Expressions directly
Expressions can be typed directly into a cell, rather than using the expression builder
Remember to separate the Column label from the expression using a colon :
Tables are referenced by name, with table name and field name separated by a !
SubTotal:Table1!Price * Table2!Quantity
Aggregate Operations
They perform calculations on a group of records using one of several aggregate (summary) functions available within Access
Used to calculate summary data
Use predefined functions to calculate the summary data. Examples: Sum, Count, Avg, Max, and Min
Sum
Totals the field values for selected records

AutoNumber,Currency, Date/Time, and Number
Avg
Averages the field values for the selected records

AutoNumber,Currency, Date/Time, Memo, Number, OLE Object, Text, and Yes/No
Count
Counts the number of selected records

AutoNumber,Currency, Date/Time, and Number and Text
Max
Returns the highest field value


AutoNumber,Currency, Date/Time, and Number and Text
Minimum
Returns the lowest field value

AutoNumber,Currency, Date/Time, and Number and Text
Total Queries
Total Queries perform calculations on a group of records
Total row – Contains either Group by or aggregate entry
Group By – Records in the dynaset are to be grouped according to the like values
Sum Function – Specifies math to be performed on that field for each group of records
Summarizing data using Aggregate Functions
Create query with fields to be summarized
Click Totals button to Add Total Row to QBE grid
Select Aggregate function for each column
Go to Datasheet view to see results
Action Queries
Enables table updates. Four types:
Update: Changes multiple records
Append: Adds records from one table to another
Delete: deletes records that meet a criteria
Make-table: Creates a new table from existing records
Input Masks
Input masks are used to improve data entry for Text, Date, Number and Currency fields, by providing a pattern of input for the user to follow

Input masks are strings of characters which specify what is displayed to the user, and what is accepted as data input

Input masks can either be created using the Input Mask Wizard (text and data fields) or typed in manually (numeric and currency)