Chapter 2 - Relational Database and Queries Flashcards
Chapter Summary
Objective - Design data.
You should consider the output requirements of a database when creating the table structure. When developing a database, the designer must weigh the need for the data against the cost of collecting the data. You learned that design principles begin with identifying the tables of the database. You learned that storing the results of calculations in a table may assist you when creating queries, forms, and reports later. You also learned that data should be stored in its smallest parts.
Objective - Create tables.
Access employs several ways to create a table. You can create a table by creating the fields in Design view or by entering table data into a new row in Datasheet view, or you can import data from another application such as Excel. You learned that each field needs a unique and descriptive name, and you were introduced to the CamelCase naming convention. Access accommodates many different types of data, including Text, Number, Date/Time, Yes/No, Memo, and others.
Objective - Understand table relationships.
Relationships between tables are set in the Relationships window. In this window, join lines are created, and three options can be set: Enforce Referential Integrity, Cascade Update Related Fields, and Cascade Delete Related Records. When referential integrity is enforced, data cannot be entered into the related table unless it first exists in the primary table. Frequently, the primary key (unique identifier) from one table is entered as a foreign key in a related table. These two fields often become the basis of creating relationships between tables.
Objective - Share data with Excel.
Microsoft Access can easily import and export data to (and from) Excel. You used the Import Wizard to import an Excel worksheet into an Access database table. The settings of the Import Wizard may be saved and reused when the import is cyclical.
Objective - Establish table relationships.
You created relationship links between tables in the database and attempted to enter an invalid branch number in a related table. You discovered that the enforcement of referential integrity prevented you from creating an account in a nonexistent branch. The Cascade Update Related Fields option ensures that if the primary key is modified in one table, Access will automatically update all fields in related tables. Similarly, the Cascade Delete Related Records option ensures that if the primary key is deleted in one table, Access will automatically delete all records in related tables.
Objective - Create a single-table query.
You created a query to display only those records that match certain criteria. You learned to add additional fields to an existing query, to add criteria, and to change the sort order of the query results. The primary sort field needs to be in the left-most position, with additional sort fields determined by their left-to-right positions in the query design grid.
Objective - Specify criteria for different data types.
Different data types require different syntax. Date fields are enclosed in pound signs (#) and text fields in quotations (“). Numeric and currency fields require no delimiters. Additionally, you learned that logical operators, AND, OR, and NOT, help to answer complex questions. The AND logical operator returns only records that meet all criteria. The OR logical operator returns records meeting any of the specified criteria. The NOT logical operator returns all records except the specified criteria.
Objective - Copy and run a query.
After specifying tables, fields, and conditions for one query, you can copy the query, rename it, and then modify the fields and criteria in the second query. Copying queries saves time since you do not have to select tables and fields again for queries that have a similar structure. To run a query during the design process, click Run in the Results group. To run a saved query, double-click the query name in the Navigation Pane.
Objective - Use the Query Wizard.
An alternative way to create a select query is to use the Query Wizard. The wizard enables you to select tables and fields from lists. The last step of the wizard prompts you to save the query.
Objective - Create a multi-table query.
Creating a multi-table query is similar to creating a single-table query; however, choosing the right tables and managing the table relationships requires some additional skills. You should only include related tables in a multi-table query. To add additional tables to a query, open the Navigation Pane, and then drag tables directly into the top section of the Query Design view.
Objective - Modify a multi-table query.
To modify multi-table queries, you use the same techniques you used for single-table queries. Join lines between related tables should appear automatically in a query if the relationships were previously established. If join lines do not appear, the results will not be correct when you run the query. You can add temporary join lines from inside the query or set the Relationships permanently in the Relationships window.
