Transcript Create

Extended Learning
Module J (Office
2010 Version)
Implementing a
Database with
Microsoft Access
McGraw-Hill/Irwin
Copyright © 2013 by The McGraw-Hill Companies, Inc. All rights reserved.
SOLOMON DATABASE

The steps to creating a database are
1.
2.
3.
4.
Define entity classes and primary keys
Define relationships among the entity
classes
Define fields for each relation (file)
Use a data definition language to
create the database, which is the focus
of this Module
Mod J-2
SOLOMON DATABASE

In Figure J.1 (p. 474) on the
following 3 slides, we’ve recreated
the correct database structure from
Extended Learning Module C

Revisit Module C if you need a
refresher
Mod J-3
SOLOMON DATABASE
Mod J-4
SOLOMON DATABASE
Mod J-5
SOLOMON DATABASE
Mod J-6
Creating Solomon’s Database


Data dictionary - contains the
logical structure for the information
To create the Solomon Enterprise
database:
1.
2.
3.
4.
Start Microsoft Access
Click on Blank Database in the upper
left corner of the screen
Enter Solomon Enterprises.accdb as
the database name
Click on Create
Mod J-7
Creating Solomon’s Database
1. Create a new database by
clicking on Blank Database
2. Enter SolomonEnterprises.accdb
Mod J-8
Creating Solomon’s Database

Our recommendation: Create a
relation (table) in Design view:




Must switch from Datasheet View to
Design View
Click on View in the upper left corner
and then Design View
Enter a table name
Use the Design View to enter the
specifications for the table
Mod J-9
Creating Solomon’s Database
This is Design View – our
recommendation for creating
the tables in a database
To switch to Design View,
click on View and then
Design View
Mod J-10
Creating the Raw Material
Relation

Enter the four fields of the Raw
Material relation





Raw Material ID
Raw Material Name
QOH
Supplier ID
Click on the Raw Material ID row and
then the key button to designate
Raw Material ID as the primary key
Mod J-11
Creating the Raw Material
Relation
Primary key identifier and
the Primary key button
Integrity constraints for
Raw Material ID
Mod J-12
Creating the Concrete Type
Relation
The fields for Concrete Type
Primary Key
Integrity Constraints
Mod J-13
Creating the Bill of Material
Relation

We created (in Extended Learning
Module C) the Bill of Material relation
to eliminate the many-to-many
relationship between the Concrete
Type and Raw Material relations
Mod J-14
Creating the Bill of Material
Relation

The Bill of Material relation has a
primary key composed of two fields
(composite primary key):



Concrete Type
Raw Material ID
Composite primary key - consists
of the primary key fields from the two
intersecting relations
Mod J-15
Creating the Bill of Material
Relation
Composite
primary key
Mod J-16
DEFINING RELATIONSHIPS
WITHIN SOLOMON’S
DATABASE



The final structural task is to define
how all the relations relate to each
other
That is, link primary and foreign keys
Foreign key - a primary key of one
file (relation) that appears in another
file (relation)
Mod J-17
Primary and Foreign Key
Logical Ties
Mod J-18
Defining Relationships
between Relations

To create relationships



Click on Database Tools in the menu
area and then click on the
Relationships button
Make each relation appear on the
palette by highlighting each relation
name and clicking on Add
Then click on the Close button
Mod J-19
Defining Relationships
between Relations
Select tables and
click on Add
Relationship
palette
Mod J-20
Defining Relationships
between Relations
Mod J-21
ENTERING INFORMATION
INTO SOLOMON’S DATABASE
Double-click on any table to
begin entering information
Mod J-22
Entering Information
We double-clicked on the Supplier table and
can now begin entering information
We have completed entering
all supplier information
Mod J-23
Referential Integrity



Referential integrity ensures
consistency. For example, that you don’t
put a non-existent Supplier ID into the
Raw Material relation
The relationships we set up for
referential integrity guard against bad
information
Integrity constraints – rules that help
ensure the quality of the information
Mod J-24
Enforcing Referential Integrity
Because we entered a nonexistent Supplier ID (445) that
doesn’t exist, Access will not
allow us to continue
If you try to close the
window, Access will
allow you to change the
information or save the
good information
without the bad
Mod J-25
Change the Structure of a
Relation
You can change the structure of a relation by
clicking on View and then Design View
In Design View, you can
change the structure of a
table
Mod J-26
CREATING A SIMPLE QUERY
USING ONE RELATION


Query-by-example (QBE) tool -
helps you graphically design the
answer to a question
Suppose we wanted to see a list of
raw materials that shows


Raw Material Name
Supplier ID
Mod J-27
Creating a Simple Query
Using the Raw Material
Relation
First, click on Create and
then Query Design
Second, select the table,
click on Add, and then
close the box.
Mod J-28
Creating a Simple Query
Using the Raw Material
Relation
The result of the query
Drag and drop the appropriate
field names and then click on the
exclamation point (Run).
Mod J-29
Creating a Simple Query with
a Condition
Set the condition here
Mod J-30
CREATING AN ADVANCED
QUERY USING MORE THAN
ONE RELATION

Suppose we want a query that shows






All order numbers
Date of orders
Where the goods were delivered
The contact person
The truck involved in each delivery
The truck driver in each delivery
Mod J-31
Steps to Create an Advanced
Query
1.
2.
Click on Create in the menu area
and then Query Design
In the Show Table dialog box



Select and Add the relation names
Close the Show Table dialog box
Tables linked appear are joined by lines
with


1 beside the table with the primary key
The infinity sign by the table with the
foreign key
Mod J-32
Steps to Create an Advanced
Query
3.
4.
Drag and drop the fields that you
want from the appropriate relation
into the QBE grid in the order that
you want
Click on the exclamation point
(Run) in the button bar to see the
results of the query
Mod J-33
Steps to Create an Advanced
Query
From the Show Table box,
select the appropriate tables –
Customer, Order, Employee
and Truck
Mod J-34
Steps to Create an Advanced
Query
Drag and drop the appropriate fields
Mod J-35
Steps to Create an Advanced
Query
The completed query
Mod J-36
GENERATING A SIMPLE
REPORT
1.
2.
3.
Click on Create in the menu area
and then the Report Wizard
button
Choose tables and/or queries: Lets
you choose which table/query you
want
Choose fields: Lets you choose the
fields you want
Mod J-37
Grouping, Sorting, Layout of
Report
4.
5.
6.
Grouping: Lets you specify grouping
of information (we chose the
default)
Sorting: Allows you to specify
sorting (we chose the default)
Layout and orientation: Allows you
to select layout and page orientation
(we chose the default)
Mod J-38
Style and Header of Report
7.
8.
Report header: Allows us to enter a
title for the report.
The Report: Shows all customers
and phone numbers.
Mod J-39
Choose Table/Query and Fields
Choose tables and/or queries
Click on Create and then
Report Wizard
Mod J-40
Choose Table/Query and Fields
We don’t need grouping so
click on Next.
Select fields by clicking on
each and clicking on the >
Then click on Next
4. Grouping
Mod J-41
Sorting and Layout
Select a Layout and
Orientation
We don’t need to sort so click
on Next.
Mod J-42
Style and Title
The wizard generated
completed report
Choose a Style then
click on Next.
Enter a report header
and click on Finish
Mod J-43
Changing the Look of the
Report
Click on View an
choose Layout View
Click on Close Print
Preview
Mod J-44
Changing the Look of a Report
Move the report elements to
change the look of the report
Mod J-45
REPORT WITH GROUPING,
SORTING, AND TOTALS

Say we want to create the Supply
Chain Management report from
Extended Learning Module C


First, create a query
Then, put the query into the report
generator
Mod J-46
Steps for Creating a Report
with Grouping, Sorting, and
Totals



Click on Create in the menu area and
click on the Report Wizard button
Choose tables/queries: Query:
Supply Chain Query
Choose fields: Select all fields by
clicking on the double greater-than
sign (>>)
Mod J-47
Specify Table/Query Fields
Select the Supply Chain query
Select all the fields of the
Supply Chain query
Mod J-48
Grouping and Sorting
Information



Top-level grouping: Allows you to
choose ordering. Since we created a
query, Access has defaulted to the
first field
Further grouping: Lets you specify
groups within the top grouping of
Concrete Type, but we don’t want
any further grouping
Sorting: Next we have a chance to
sort our information
Mod J-49
Steps for Grouping Information
Group by Concrete Type and Type Description and
then click Next.
Mod J-50
Sorting Information
Sort on Raw Material ID to sort and change
the order from Ascending to Descending.
Mod J-51
Totaling Information in a
Report

Totaling: The sorting screen also has
a Summary Options button

Within Summary Options you can
choose what type of summary you
want
Mod J-52
Totaling Information in a
Report
Click on Summary Options and
check the Sum box for Unit.
Mod J-53
Formatting the Report


Overall structure of report: Allows
you to choose the layout and
orientation
Report heading: Allows you to enter
the title that will appear on the
report, then click on Finish
Mod J-54
Formatting the Report
Choose the desired report layout and orientation.
Mod J-55
The Report

The Report: Shows the information from
the wizard steps
Mod J-56
Refining the Report

Steps to adjust the report to make it
aesthetically pleasing



Open the report in Design View with
the triangle button
Adjust the boxes to the desired size
Delete unwanted entries
Mod J-57
Refining the Report
Click on Close
Print Preview.
Click on Design View to
see the report in design
format
Mod J-58
Adjusting the Page Header
Click on View and then
Report View to see the
changes
Move the divider to make
more heading room and
change the shape and
size of the header boxes
Mod J-59
Adjusting the Concrete Type
Header
Concrete Type should now
be lined up under its header
Move Concrete
Type to the left
and shrink it.
Mod J-60
Adjusting the Detail Section
The realigned report
Adjust the elements of
the detail line so that they
line up with their headers
Mod J-61
Adjusting the Concrete Type
Footer
The completed report
Delete the Summary line.
Change Sum to Total Units,
and change its color
Mod J-62
CREATING A DATA INPUT
FORM


Select the Order table
Click on Create and then Form
Mod J-63
CREATING A DATA INPUT
FORM
1.
2.
Select the Order table
Click on Create and then Form
Mod J-64
Final Input Form
Mod J-65