Teaching Database Design Concepts

1
Teaching Database Design Concepts

• With Access 2010

2
Session Outline

• Two Part
• Lecture/Discussion/Sharing
• Hands-on
• Practical
• Helpful

3
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

4
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

5
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

6
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

7
More Challenges

• House analogy
• Need a blueprint before you can build
• GIGO (garbage in, garbage out)
• Teaching approach
• Skills first
• Concepts first
• Integrate

8
Key DB Design Concepts

• Entity
• Person, place, thing, event (noun)
• Attribute
• Property of an entity (adjective, adverb)
• Relationship
• Association between entities

9
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

10
DBMS (software)

• Design structure of database
• Create data entry forms
• Validate data
• Sort and manipulate data
• Query the database
• Produce reports

11
Goals of Database Design

• Input
• set of user requirements
• Output
• database structure capable of supporting user
  requirements

12
Database Design Step 1

• Information-level design
• gather user requirements
• design a database that meets requirements as
  cleanly as possible
• independent of DBMS

13
Database Design Step 2

• Concerned with characteristics of specific DBMS
• Must resolve issues such as
• column names
• data type
• number of columns
• data length

14
General Design Guidelines

• Identify the tables (entities)
• Determine the primary keys (unique attribute)
• Determine additional fields (attributes)
• Determine relationships among tables

15
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

16
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.

17
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

18
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.
19
Applying the DatabaseDesign Guidelines to
Camashaly Design Group
20
Client Number Client Name Street City State Postal Code
BA53 Bavant Animal Hospital 134 Main Burles NC 28817
BB32 Babbage CPA Firm 464 Linnell Austin SC 28796
BC76 Buda Community Clinic 867 Ridge Buda NC 27032
CJ29 Catering by Jenna 123 Second Granger NC 27036
GA74 Grant Antiques 78 Catawba Georgetown NC 28794
GF56 Granger Foundation 65 Simpson Granger NC 27036
HC10 Hendley County Hospital 216 Rivard Austin SC 28796
KD21 KAL Design Studio 116 Pine Georgetown NC 28794
KG04 Kyle Grocery Cooperative 421 First Kyle SC 28798
ME14 Mike's Electronic Stop 234 Gilham Georgetown NC 28794
PJ34 Patricia Jean Florist 345 Magee Kyle SC 28798
SL77 Smarter Law Associates 764 Main Burles NC 28817
TB17 The Bikeshop 346 Austin Buda NC 27032
WE05 Walburg Energy Alternatives 12 Polk Walburg NC 28819
WS01 Woody Sporting Goods 578 Central Walburg NC 28819
Word table
21
Excel workbook
22
Business Analyst Number Last Name First Name Street City State Postal Code
11 Kerry Cordelia 251 Painter Georgetown NC 28794
14 Martinez Manuel 3125 Steel Kyle SC 28797
27 Liu Jan 265 Marble Byron SC 28795
35 Scott Jeff 1925 Pine Georgetown NC 28794
Word table
23
Excel workbook
24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
(No Transcript)
30
(No Transcript)
31
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

32
Our Approach (cont)

• Import data from other Office applications
• Less emphasis on typing
• More realistic
• Use Layout view
• Easier to visualize changes

33
Our Approach (cont)

• Show routine database operations
• Backing up a database
• Renaming objects
• Deleting objects
• Compacting a database

34
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

35
Your Turn

• Try it out

Thank you Phil and Mary