Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
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) |