ITM 102 Study Guide - Quiz Guide: Foreign Key, Unique Key, Database
DepartmentInformation Technology Management
Course CodeITM 102
This preview shows half of the first page. to view the full 1 pages of the document.
Chapter 1- Work with Databases and Create Tables
A database is a structured collection of related information about people, events, and things.
A security warning may display so that you can verify that the database file came from a trusted source. In the Navigation pane, several database objects are listed.
The Navigation Pane can display the objects by type. Database objects---or objects---are the basic parts of a database that you work with.
Database objects work together to provide a database management system (DBMS)---software used to manage and interact with the database. The purpose of each database object
is summarized as follows:
Table-store the data in rows and columns.
Form-enter new records, delete records, or update existing records.
Query-display a subset of the data in response to a specific question.
Report-display table data or query results on the screen or in printed form.
Macro-store a sequence of commands that can be performed as one task.
Databases store information in tables by organizing data into rows and columns.
You can open tables to view the data that it stores and then make changes to that data.
The table---the database object that stores the data---open in Datasheet view. A datasheet displays records in rows and fields in columns similar to a Microsoft Excel spreadsheet. A
record is the collection of related information that displays in a single row of a database table, and a field is a set of common characteristics around which a table is organized.
With Access objects, design changes should be saved.
When you change data, the new data is saved automatically when you click or navigate to a different record.
AutoNumber---a field that automatically enters a unique, numeric value when a record is created. The number is assigned as soon as you begin adding data to a new record. Once an
AutoNumber value has been assigned, it cannot be changed. When your AutoNumber values differ from the ones shown in figures, do not try to change yours to match.
Access forms are created so that you can modify or add to the data stored in tables.
The Form tool creates a form for the table that you selected in the Navigation Pane.
Most forms use the single form layout, a layout that displays one record at a time.
You can cancel entering data into a new record by pressing ESC. This technique is helpful when you are entering data into a record, and you need to start over.
Recall that data is saved automatically as you complete each record. Access had already saved the data that you entered, and it did not ask you to save changes when you closed the
A query displays a subset of the data in response to a specific question.
Queries are modified in Design view---a view in which the structure and behaviour of Access database objects are modified---and they display their results in Datasheet view.
Queries display the subset of data in Datasheet view.
The upper half of the Query tab----the query design workspace----lists the available tables and fields that the query should use.
The lower half of the Query tab----the design grid----lists the fields that will display in the query results.
In Access, criteria are the conditions used to select the records that you are looking for.
When a query criterion is a text value, the text value needs to be in quotation marks.
When you widen datasheet columns or change query criteria, you need to save the changes.
Reports display the results of a query or the data in a table.
Reports are often printed and cannot be used to change data.
When you first create a report using the Report tool, it displays in Layout view---a view used to format a report or a form while being able to view a sample of the data.
The dashed line near the right edge of the report indicates when one printed page will end and another will begin.
Print Preview is a view used to work with a report that will be printed.
Before you create a new database, you assign a name and location for the database file. You can then add objects such as tables, queries, forms, and reports.
When you save design changes to the objects that you add to a database, they become part of the database file that you created.
Primary key---a field that uniquely identifies each record in a table.
Data type specifies the type of information that a file will hold; for example, text, number, date, and currency.
The Text data type stores up to 255 characters of text.
Quick Start data types are a set of fields that can be added with a single click.
Field properties define the characteristics of the data that can be added to a field.
In addition to Data Type and Name, you can change several other field properties.
The Memo data type stores up to 65,535 characters of text data and the formatting assigned to that text.
The Number data type stores numeric values.
The Currency data type stores numbers formatted as a monetary value.
Field Size limits the number of characters that can be typed into a text or number field.
When selected, the Unique field property requires that each record contain a unique value.
The Caption field property is the label that displays in datasheets, forms, and reports. The Description field property is used to document a field's purpose and displays in the lower-
left corner of the datasheet when the field is active.
Database designers often sketch the database they need and then follow that plan to create tables.
In a relational database, you can place the same field in two tables and then join the tables using the related fields. A relationship joins tables using common fields.
An entity relationship diagram, or ERD, is a visual model used to plan a database. An ERD shows the tables and their fields. Each field's data type is also displayed. The lines between
the tables show how each table will be related.
When working with a table in Design view, the Field Name, Data Type, and Description data are entered in rows. Other field properties are entered in the Field Properties pane.
A foreign key is a field that is also in another related table. The field in the other table is usually that table's primary key.
When you join tables, the common fields must share the same data type.
The Data/Time data type stores numbers in the data or time format.
Instructor ID needs to be a number so that it can be joined to the Instructor ID AutoNumber values assigned to records in the Instructors table.
Tables are typically joined in a one-to-many relationship---a relationship where a record in the first table can have many associated records in the second table.
One-to-many relationships enforce referential integrity---the principle that a rule keeps related values synchronized.
With a cascading update, you can edit the primary key values in a table, and all the related records in the other table will update accordingly.
With the cascading delete, you can delete a record on the one side of the relationship, and all the related records on the many side will also be dele ted.
The three tables are joined to create a many-to-many relationship---a relationship where one record in either of the outer tables can have many associated records in the other outer
A many-to-many relationship is created by placing the primary keys from the outer tables into a middle table called a junction table.
A subdatasheet displays related records from the table on the many side of the relationship.
Date Picker---a feature used to enter dates by clicking data on a calendar.
You're Reading a Preview
Unlock to view full version