Transcript here

DAVID M. KROENKE’S
DATABASE CONCEPTS, 2nd Edition
Appendix
Database Processing with
Microsoft Access
Chapter Objectives
• Learn how to use Microsoft Access to:
–
–
–
–
–
–
–
Create tables
Create relationships
Create queries using the query design tool
Create basic forms
Create forms with lookup combo boxes
Create basic reports
Create parameterized reports
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-2
Creating the Database
Design
• Before you begin using Microsoft
Access, you must know what to
create
• This entails creating a data model and
the corresponding data schema
• The DBMS will not help you in this
process
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-3
Create the E-R Diagram
[No help from the DBMS]
SUPPLIER
SuppID
Address
|
1:N
Phone
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
ITEM
ORDER
ItemNumber
OrdNumber
0 Description
Category
|
N:M
0 Date
Comment
Appendix-4
Database Schema
• A database schema is a design of
database tables, relationships, and
constraints
• Schemas are developed by analyzing
user requirements such as forms,
queries, and reports, and then
constructing a data model
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-5
Create the Schema Design
[No help from the DBMS]
SUPPLIER
ITEM
SuppID
Address
ORDER
ItemNumber
|
1:N
OrdNumber
0 Description
Phone
Date
SuppID (FK)
Comment
|
Category
0
1:N
ItemNumber
0
|
ORD_ITEM
N:1
OrdNumber
Quantity
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-6
Create the Data Dictionary
[no help from the DBMS]
ITEM Table:
ITEM
ItemNumber
Autonumber
Description
Text(30)
SuppID
Long Integer Foreign Key
refers to
SUPPLIER
Category
Text(5)
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Not Null
Appendix-7
Create the Data Dictionary
[no help from the DBMS]
ORDER Table:
ORDER
OrdNumber
Autonumber
Date
Date/Time
Comment
Memo
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Not Null
Appendix-8
Create the Data Dictionary
[no help from the DBMS]
SUPPLIER Table:
SUPPLIER
SuppID
Autonumber
Address
Text(30)
Phone
Text(12)
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Not Null
Appendix-9
Create the Data Dictionary
[no help from the DBMS]
ORD_ITEM Table:
ORD_ITEM
ItemNumber
Long Integer Not Null,
Foreign Key
refers to
ITEM
OrdNumber
Long Integer Not Null,
Foreign Key
refers to
ORDER
Quantity
Long Integer
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-10
Enter the DBMS
• Now that the tables and relationships
have been conceptually defined, the
DBMS may be used to actually create
the database objects
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-11
Creating a Table with
Microsoft Access
Double-Click the “Create table in Design view” Option
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-12
Entering Field
Characteristics
• Enter the Field Name, Data Type, and
Description (optional) for each field
• Click and Drag to include all Fields
that are part of the Primary Key
• With all fields making up the Primary
Key highlighted, click the Primary Key
Button
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-13
Microsoft Access
Table Define View
Primary Key Button
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-14
Close the Table Design View
and Save
• To save the table, simply close the
design view window and type a name
for the table
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-15
Microsoft Access
Saving the Table
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-16
Creating the Remaining
Tables
• Follow the above procedure for the
remaining tables
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-17
Defining a
Composite Primary Key
All fields defining the
Primary Key are highlighted
before clicking the Primary Key
Button
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-18
Informing the DBMS of the
Foreign Key Constraints
• The DBMS must be informed about
what Primary Key is mapped to what
Foreign Key.
• This defines the database’s
Referential Integrity
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-19
Microsoft Access:
Relationships View
• Within Microsoft Access the
referential integrity constraints are
defined in the Relationship View
Window by:
– clicking on the Primary Key, then
– dragging it to the Foreign Key, and
– dropping it on the Foreign Key
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-20
Relationships View
Click to enter the
Relationships View
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-21
Select the Tables to be
Included
Add each table to the
Relationships View Window
by highlighting the table name
and clicking the Add button
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-22
Arrange Tables in
Relationships View
Click and Drag to arrange the tables
to avoid crossing relationship lines.
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-23
Creating a Relationship
Table Names
Field Names
Enforce
Referential
Integrity = Checked
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Define Cascading
Rules
Appendix-24
The Resulting Relationship
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-25
Create the Remaining
Relationships
• Drag each Primary Key Field and
drop on the appropriate Foreign Key
field to create the remaining
relationships
• Enforce referential integrity for each
remaining relationship
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-26
The Final Relationships View
Window
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-27
Creating Queries
Click the
Queries Tab
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-28
Creating a Query in
Design View
Double-Click
“Create query in Design view”
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-29
Defining the Query
Click to
Show results
Drag & Drop Fields
to Display
Sorting and Specifying Search Criteria
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-30
Changing the View to see
the SQL Statement
Dropdown Button
Click SQL View
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-31
The SQL View of the Query
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-32
Action Queries
Dropdown Button
Choose Action type
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-33
Forms
A form a graphical display for adding,
updating, viewing, and deleting data
from one or more tables
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-34
Creating a Form
Click to Create
a Form
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-35
Create Form using the Form
Wizard
Double-Click to
Launch the
Form Wizard
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-36
Choosing the Table/Query
and Fields to Include
Select the Table
or Query on which to
base the Form
Select the Field(s)
to include on the Form
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-37
Select the Form Style and
Colors
A finished form
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-38
Creating a Report
Click to Create
a Report
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-39
Create a Report using the
Report Wizard
Click to Create a Report
using the Report Wizard
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-40
Choosing the Table/Query
and Fields to Include
Select the Table or Query
that provides information
for the Report
Select the Fields to
Include in the Report
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-41
Select the Report Style and
Colors
A finished report
DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition
© 2005 Pearson Prentice Hall
Appendix-42
DAVID M. KROENKE’S
DATABASE CONCEPTS, 2nd Edition
End of Presentation on Appendix
Database Processing with
Microsoft Access