Transcript ch4

Exploring Microsoft Access 2003
Chapter 4 Proficiency:
Relational Databases, External Data,
Charts, Pivot, and the Switchboard
Objectives (1 of 2)
• Describe one-to-many relationships
• Create a one-to-many relationship
• Use the Get External Data command
to get data from Microsoft Office Excel
• Create and modify a multiple-table select
query.
Objectives (2 of 2)
• Use aggregate functions to create a totals query
• Use Microsoft graph to create a chart based on a
table or query
• Use the Switchboard Manager to create and/or
modify a switchboard
Overview
• Share data between Microsoft Office applications
• Display data from two tables in one query
• Total query aggregates results from groups
of records to create summary information
• Create Chart and Pivot
• Create a user interface
Multiple-Table Queries
• One-to-many relationship
• Primary key (PK)
• Foreign key (FK)
i)
Consider two relation schemas R1 and R2;
ii) The attributes in FK in R1 have the same domain(s) as
the primary key attributes PK in R2; the attributes FK
are said to reference or refer to the relation R2.
• Referential integrity
A value of FK in a tuple (record) t1 of the current state
r(R1) either occurs as a value of PK for some tuple t2 in the
current state r(R2) or is null. In the former case, we have
t1[FK] = t2[PK], and we say that the tuple t1 references or
refers to the tuple t2.
Example:
FK
Employee(SSN, …, Dno)
Dept(Dno, … )
Get External Data
• Get External Data command
• Export command
• Import Spreadsheet Wizard
• Import Text Wizard
• Importing versus linking
Importing & Exporting
Hands-On Exercise 1
• Open the Investment Database
• Import Spreadsheet Wizard
• Create the Relationship
• Print the Relationship
• Add the New Data
• Create & Complete the Multiple-Table Query
• Export the Query and Modified Tables
• View the Excel Workbook
Multiple Table Query
Relationship
between tables
Each field &
table to display
Total Queries
A total query
•
•
•
•
•
Summary functions
Total row
Group By
Count function
Sum function
Total Queries & Charts
Hands-On Exercise 2
•
•
•
•
•
•
•
Copy Assets Under Management Query
Create a Total Query
Check Your Progress
Start the Chart Wizard
Complete the Chart Wizard
Increase the Plot Area
Change the Data
Total Query
Run button
Select Count from
drop-down menu
SELECT Consultants.Lastname, Count(Clients.LastName), Sum(Assets)
FROM Consultants, Clients
WHERE Consultant.ConsultantID = Clients.ConsultantID
GROUP BY Consultants.Lastname
SELECT Consultants.Lastname, Consultants.Status,
Clients.Lastname, Clients.CountType, Clients.Assets
FROM Consultants, Clients
WHERE Consultant.ConsultantID = Clients.ConsultantID
GROUP BY Consultants.Lastname
4
5
1
5
4
The results are grouped according to the last name of the consultants.
The records with the same last name are in the same group.
Referential Integrity
Delete Record button
Click + to display
related records
You cannot delete a Consultant without first deleting related Clients
EMPLOYEE
fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno
DEPARTMENT
Dname, dnumber, mgrssn, mgrstartdate
Dnumber, dlocation
DEPT _LOCATIONS
PROJECT
Pname, pnumber, plocation, dnum
Essn, pno, hours
WORKS_ON
DEPENDENT
Essn, dependentname, sex, bdate, relationship
Updating and constraints
delete
•Delete the WORK_ON tuple with Essn = ‘999887777’
and pno = 10.
•When deleting, the referential constraint will be
checked.
- The following deletion is not acceptable:
Delete the EMPLOYEE tuple with ssn = ‘999887777’
- reject, cascade, modify (cascade update)
Cascade delete – a strategy to enforce referential integrity
Employee
ssn
...
123456789
...
Works-on
Essn
123456789
...
delete
Pno
5
...
delete
Cascade delete – a strategy to enforce referential integrity
Employee
ssn
... ...
supervisor
234589710
123456789
... ...
234589710
null
delete
Employee
ssn
123456789
... ...
supervisor
234589710
... ...
234589710
not reasonable
delete
null
delete
Modify – a strategy to enforce referential integrity
Employee
ssn
...
123456789
...
Works-on
Essn
123456789
...
delete
Pno
5
...
Works-on
Essn
null
...
Pno
5
...
This violates the entity constraint.
Modify – a strategy to enforce referential integrity
Employee
ssn
...
123456789
...
Department
Dno ...
5
...
delete
chairman
123456789
Department
Dno ...
5
...
chairman
null
This does not violate the entity constraint.
Chart Wizard
View button
Modified Y and X
axis now match
the query data
Chart from Wizard
The User Interface
• Switchboard
• Switchboard Manager
• Switchboard Items table
Other Access Utilities
• Convert Database command
• Compact and Repair Database command
Compact the Database
View button
Select the
database
Click compact
The Switchboard Manager
Hands-On Exercise 3
• Start the Switchboard Manager
• Complete the Switchboard
• Test the Switchboard
• Insert the Clip Art
• Complete the Design
• The Completed Switchboard
• Compact the Database
Switchboard Manager
Each button
corresponds
to a command
Chapter 4 Summary (1 of 2)
• One-to-many relationships
• A query can display data from multiple
tables
• Get External Data command
• A total query performs calculations on
• a group of records using summary
functions
Chapter 4 Summary (2 of 2)
• Switchboard Manager creates the user
interface (Switchboard)
• Convert Database command changes an
Access 2000 file to a previous version
• Compact and Repair Database command
Practice with Access
1. The Client Master List
2. The HMO Database
3. Creating a Switchboard
4. The Look Ahead Databas
5. Linking Versus Importing
6. Pivot Tables
7. Pivot Charts
Case Studies
• Your First Consultant’s Job
• The Wellness Center
• The Database Wizard
• Compacting Versus Compressing