Relational Databases and Queries
Exploring Microsoft
Access 2010 by Robert Grauer, Keith
Mast, Mary Anne Poatsy
Chapter 2
Relational Databases and
Queries
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
1
Objectives
•
•
•
•
•
•
•
Design data
Create tables
Understand table relationships
Share data with Excel
Establish table relationships
Create a single-table query
Specify criteria for different data types
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
2
Objectives (continued)
•
•
•
•
Copy and run a query
Use the Query Wizard
Create a multi-table query
Modify a multi-table query
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
3
Table Design: Designing Data
• Table Definition – Revised
• Input vs. Output in Design
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
4
Designing Fields Guidelines
1.
2.
3.
4.
5.
6.
Include the necessary data
Design for now and the future
Store data in its smallest parts
Add calculated fields to a table
Design to accommodate date arithmetic
Link tables using common fields
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
5
Include Necessary Data
• Determine what data is necessary
• Create a rough draft of reports that may be needed • Create tables based on fields necessary for reports Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
6
Design for Now and the Future
• Organizations evolve over time
• Databases should evolve with the organization
– Anticipate future needs of the organization
– Build flexibility into system to satisfy future needs
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
7
Store Data in
Smallest Possible Pieces
• Creating a name field with the entire name in it violates good database design and reduces the usefulness of the data
• Divide data into the smallest pieces that
Access 2010 by Robert Grauer, Keith
Mast, Mary Anne Poatsy
Chapter 2
Relational Databases and
Queries
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
1
Objectives
•
•
•
•
•
•
•
Design data
Create tables
Understand table relationships
Share data with Excel
Establish table relationships
Create a single-table query
Specify criteria for different data types
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
2
Objectives (continued)
•
•
•
•
Copy and run a query
Use the Query Wizard
Create a multi-table query
Modify a multi-table query
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
3
Table Design: Designing Data
• Table Definition – Revised
• Input vs. Output in Design
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
4
Designing Fields Guidelines
1.
2.
3.
4.
5.
6.
Include the necessary data
Design for now and the future
Store data in its smallest parts
Add calculated fields to a table
Design to accommodate date arithmetic
Link tables using common fields
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
5
Include Necessary Data
• Determine what data is necessary
• Create a rough draft of reports that may be needed • Create tables based on fields necessary for reports Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
6
Design for Now and the Future
• Organizations evolve over time
• Databases should evolve with the organization
– Anticipate future needs of the organization
– Build flexibility into system to satisfy future needs
Copyright © 2013 Pearson Education, Inc. Publishing as Prentice Hall.
7
Store Data in
Smallest Possible Pieces
• Creating a name field with the entire name in it violates good database design and reduces the usefulness of the data
• Divide data into the smallest pieces that