Transcript Teaching Database Design Concepts With Access 2010 Session

Teaching Database
Design Concepts
With Access 2010
Session Outline
Two Part
Lecture/Discussion/Sharing
Hands-on
Practical
Helpful
Objectives
Challenges
Student background/knowledge/learning
Database Design Concepts
Includes logical and physical
Access 2010
Can implement design using Datasheet
view
Our Approach
Integration and more
Learning Outcomes
Identify and define the information that
is needed to design a database
Create conceptual and logical db
designs
Build a relational database that
provides users with queries, forms, and
reports
Understand core terms, concepts, and
tools
Learning Outcomes
Design and maintain relational db
tables
Create Select and Action queries
Create normalized relationships
between tables, apply validation rules,
and referential integrity principles
Design and modify reports and forms
Challenges
Perceived knowledge versus actual
knowledge
Very little or no previous experience
with Access
No understanding of when to use
No connection to real-world
Looks different from other Office apps
More Challenges
House analogy
Need a blueprint before you can build
GIGO (garbage in, garbage out)
Teaching approach
Skills first
Concepts first
Integrate
Key DB Design Concepts
Entity
Person, place, thing, event (noun)
Attribute
Property of an entity (adjective, adverb)
Relationship
Association between entities
Key DB Design Concepts
Database
Structure that can house information
about multiple types of entities, the
attributes of these entities, and the
relationships among the entities.
Relational Database
Perceived by users to be a collection of
tables; two-dimensional named tables
DBMS (software)
Design structure of database
Create data entry forms
Validate data
Sort and manipulate data
Query the database
Produce reports
Goals of Database Design
Input
set of user requirements
Output
database structure capable of supporting
user requirements
Database Design Step 1
Information-level design
gather user requirements
design a database that meets
requirements as cleanly as possible
independent of DBMS
Database Design Step 2
Concerned with characteristics of
specific DBMS
Must resolve issues such as
column names
data type
number of columns
data length
General Design
Guidelines
Identify the tables (entities)
Determine the primary keys (unique
attribute)
Determine additional fields (attributes)
Determine relationships among tables
General Design
Guidelines (cont)
Determine data types for fields
Identify and remove unwanted
redundancy
Storing a piece of data in more than one
place
Determine a storage location
Determine additional properties for
attributes
Catch 22
Students need some understanding of
concepts before they create database
objects.
Students think concepts are “boring”
and want to get their hands on the
software.
Help is on the way!
Access 2010
Use Datasheet view to implement design
Visually see columns as you create them
Assign data types
Add new fields
Change field size
Add validation rules
Add captions
A Database Example
Camashaly Design Group provides custom marketing
solutions for the service, non-profit, and retail sectors.
The company specializes in designing and maintaining
Web sites and using social networking Web sites for
online marketing. Camashaly uses business analysts to
work collaboratively with clients.
Camashaly would like to organize the data on clients and
business analysts in to a database managed by Access
2010.
Applying the Database
Design Guidelines to
Camashaly Design Group
Client
Number
BA53
BB32
BC76
CJ29
GA74
GF56
HC10
KD21
KG04
ME14
PJ34
SL77
TB17
WE05
WS01
Client Name
Bavant Animal Hospital
Babbage CPA Firm
Buda Community Clinic
Catering by Jenna
Grant Antiques
Granger Foundation
Hendley County Hospital
KAL Design Studio
Kyle Grocery Cooperative
Mike's Electronic Stop
Patricia Jean Florist
Smarter Law Associates
The Bikeshop
Walburg Energy Alternatives
Woody Sporting Goods
Word table
Street
City
134 Main
464 Linnell
867 Ridge
123 Second
78 Catawba
65 Simpson
216 Rivard
116 Pine
421 First
234 Gilham
345 Magee
764 Main
346 Austin
12 Polk
578 Central
Burles
Austin
Buda
Granger
Georgetown
Granger
Austin
Georgetown
Kyle
Georgetown
Kyle
Burles
Buda
Walburg
Walburg
State
NC
SC
NC
NC
NC
NC
SC
NC
SC
NC
SC
NC
NC
NC
NC
Postal Code
28817
28796
27032
27036
28794
27036
28796
28794
28798
28794
28798
28817
27032
28819
28819
Excel workbook
Business
Analyst Number
11
14
27
35
Last Name
Kerry
Martinez
Liu
Scott
First Name
Cordelia
Manuel
Jan
Jeff
Word table
Street
251 Painter
3125 Steel
265 Marble
1925 Pine
City
Georgetown
Kyle
Byron
Georgetown
State
NC
SC
SC
NC
Postal
Code
28794
28797
28795
28794
Excel workbook
Our Approach
Integrate concepts and DBMS
Introduce common database objects
Tables
Forms
Queries
Reports
Use Datasheet view for one table
Use Design view for another table
Our Approach (cont)
Import data from other Office
applications
Less emphasis on typing
More realistic
Use Layout view
Easier to visualize changes
Our Approach (cont)
Show routine database operations
Backing up a database
Renaming objects
Deleting objects
Compacting a database
Advantages
Provides an overview of database tools
Follows the database creation process
from design to implementation
Emphasizes the data independence
feature of a database
Uses real-world situations
Encourages critical thinking
Encourages retention
Your Turn
Try it out
Thank you
Phil and Mary