Transcript Session 2: Working with Tables

INFSY540
Information Resources in Management
Lesson 6
Chapter 5
Database Management
Portfolio and Ecommerce HTML
Assignment

Assignment is on course web space at
http://www.courses.psu.edu/infsy/infsy540/rak13/
HTML_Portfolio.html
 Part of the assignment is to do research on
ecommerce.
 The first version of your portfolio.html page and
subsequent sections of your site must be posted
by the October 31st class. The final site must be
online by November 28.
– There are individual and team activities that need to
take place between these times.
Slide
2
Case 2: Cisco Systems Architecture
What’s this case about?
 When did Cisco go public?
 What does Cisco stand for?
 What is Cisco’s mission?

 IT Interaction Model
http://equity.stern.nyu.edu/~msilver/mbacore/itimhdo.htm
Slide
3
Learning Objectives
Know the difference between a
database and a spreadsheet.
 Identify the objects available in a
relational database and their purpose.
 Know the difference between a query
and a filter.
 Identify typical problems that occur with
MS Access and other “personal” vice
“enterprise” databases.

Slide
4
Shared Data
Poorly organized data prevents sharing
that data with other “databases”
 Think of all the “databases” that lists
your name, department, etc.:

•
•
•
•
Messiah College Phone List Database
Students Using College Networked Computers
Students Using Dining Facility
Students Using Nursing Facility
Slide
5
Database Management System

Computer program designed to help a
user store and retrieve data
– Access, Oracle, DB2
Slide
6
DBMS
forms
Outside
Applications
tables
reports
abcd 1234
abcd
defg
121234
abcd
defg
121234
asd
123
defg
12
asd 112
123
kghb
asd 112
123
kghb
kghb 112
DBMS
Engine
import
abcd
defg
asd
kghb
1234
12
123
112
query
abcd 1234 123
abcd123
123412123
asd
asd 112
123 3412
kghb
kghb 112 34
labels
Data
Dictionary
attached
Slide
7
The Traditional versus the Database
Approach to Data Management
Fig 5.3
Slide
9
Problems with the “Traditional”
Approach



Data redundancy
Program-data dependence
Inflexibility
Slide
10
Fig 5.4
Slide
11
Data Modeling & Database Models
Data Design Issues

Content: What data should be collected?
 Access: What data should be given to what
users?
 Logical structure: How will the data be
organized to make sense to a particular user?
 Physical organization: Where will the data
actually be located?
Slide
13
Data Modeling
Logical design
 Physical design
 Planned data redundancy
 Data model

Slide
14
Data Models
Hierarchical models
 Network models
 Relational models

Slide
15
Fig 5.6
Slide
16
Fig 5.7
Slide
17
Fig 5.8
Slide
18
Operations
Select rows
 Project columns
 Join tables

Slide
19
Fig 5.9
Slide
20
Object-Relational Database
Management Systems
Stores objects
 Hypertext
 Hypermedia
 Spatial data technology

Slide
21
Database Management Systems
(DBMSs)
Provide a user view
 Provide tools to create & modify the
database
 Store & retrieve data
 Manipulate data
 Produce reports

Slide
22
Selecting a DBMS
Number of concurrent users
 Database size
 Performance
 Integration
 Features
 Vendor
 Cost

Slide
23
Database Developments
Data warehouses
 Data marts
 Data mining
 Online analytical processing (OLAP)

Slide
24
What Is a Database?
Database: a collection of data
 Table: the primary element for collecting

data, organized into rows and columns
Record: an individual entry in a table (row)
 Field: a piece of data in a record (column)

Slide
26
Access97: A Database Example
Table
Field (column)
Record
(row)
Slide
27
Access Database Objects
Table: Used to store and manipulate data.
 Query: Used to retrieve information.
 Form: Used to view one record of data.
 Report: Used to present, calculate,

summarize, and print table data.

Macro/Module: Used to automate
frequently performed procedures.
Slide
29
The database window
is the command center; it provides the means to create, view, and
edit database objects, such as tables, forms, and reports.
Control
icon
Object
tabs
Processing or
Command Buttons
Slide
30
What is the difference
between a
database and a spreadsheet?
Slide
31

Excel Spreadsheet
Use Microsoft Excel to analyze
financial and statistical data.
Cost Per Month
Susie
Jacob
Total
Videos Music CDs
42
25
38
36
80
61
Total
67
74
141
Slide
32

Access Database
Use Microsoft Access to create,
maintain, and summarize very
large quantities of data.
Slide
33
Why do “databases” in spreadsheets?
Because we can
 We use 1 or 2 massive tables

– “Lots of tables make the database complex”
– Discomfort with databases and multiple tables

Because we “think it’s simple”
– Skip organizing the data into relational tables
– Go straight to designing forms
NAME
Jones
Smith
Jones
POSITION
Chief
Clerk
Chief
SPOUSE
Betty
Mary
CHILDREN
PHONE
Gloria, Karen 3274
3241
Glorai, Karen 3296
Slide
34
Data Redundancy Problems

Redundancy breeds errors
– Same data defined in multiple places is BAD
– Spelling/typographical error prone
– Lack of data integrity
Inability to perform simple queries
 Inflexibility and inscalability
 Impossible to MAINTAIN!

Slide
35
Relational Database
PROJECTCHIEF
Project
Project Chief
Computing 333-22-1111
Intranet
987-65-4321
Contracting 123-45-6789
Jordan
333-22-1111
EMPLOYEES
LName
FName
Jones
Mike
Smith
Tony
Lee
Bruce
Doodle
Yankee
1
SSN
123-45-6789
987-65-4321
567-89-1234
333-22-1111
Dept
M&B
Math
Engrr
M&B
1
DEPARTMENTS
Dept
Dept. Director
Engr
181-94-5676
Math
987-65-4321
M&B
123-45-6789
Room
B115
123
147
Slide
36
Example
Employee
SSN
L Name
F Name
Rank
Spouse
Children
Office Phone#
Home Phone#
Office Room#
Dept
Dept. Chief
EmpProj
Project Name
Employee SSN
Function
•Must know all constraints on data
–project name is unique
–only one chief per project
–employees can have more than one phone#
–employees can have only one office
–many employees can use the same office
Slide
37
Just a Reminder
A table is a single object within a database
 A database can have other objects such as
queries, forms, reports, macros, and
programming modules
 However, at least one table object must be
created before you can create any other
type of object

Slide
38
Database Design Templates

CHOOSE: File, New Database or
CLICK: New Database button
Design
Templates
Slide
39
How Do I Create a Table?
Using Datasheet View (Usually used for entering data into records)
 Using the Table Wizard (Usually used for creating tables)
 Using Design View (Usually used for creating tables and editing fields)

Sample Table in
Datasheet View
Slide
40
Using the Table Wizard
Pick fields from existing business and personal table structures
Slide
41
Using Design View
Note: Fields are shown in
rows in the design view,
not in columns.
Specify field names, data types, and indexes from scratch
Define fields
and data types
Define field
properties
Slide
42
Field Data Types





Text
Memo
Number
Date/Time
Currency





Autonumber
Yes/No
OLE Object
Hyperlink
Lookup Wizard
Slide
43
Field Properties






Field Size
Format
Decimal Places
Input Mask
Caption
Default Value





Validation Rule
Validation Text
Required
Allow Zero Length
Indexed
Slide
44
Primary Keys (Are extremely important in databases.)

Primary key is a field that uniquely
identifies each record in a table
– Data is automatically displayed in the datasheet
sorted by the primary key.
– There are 3 types of primary keys: AutoNumber,
Single-Field, and MultiField.
– You can use Access AutoNumber as a primary key
(since it is unique) but it is not a good idea.

CHOOSE: Edit, Primary Key or
CLICK: Primary Key button
Slide
45
Primary Keys


A primary key is one or more data fields (columns)
that uniquely identify each record in the table
What would the primary key be below?
– “table of employees, assigned to a department.”
EMPLOYEES
LName
FName
Jones
Mike
Smith
Tony
Lee
Bruce
SSN
123-45-6789
987-65-4321
567-89-1234
Dept
Math
M&B
Science
Slide
46
Indexes
Indexes can slow down data entry.
Access automatically creates indexes for
fields with ‘ID’ in their names.
Like Primary Key, can be used to
determine the order in which data is
displayed
 CLICK: Indexes button

Primary
Key
Index
Definition
Slide
47
Access Uses Automatic Saving
CLICK: a cell using the mouse pointer
 Make changes to the cell’s information.
 SELECT: another record to save the
changes
 CHOOSE: Edit, Undo from the menu
if you want to reverse the most recent
changes

Note: This is a very important difference between
Access and other Office 2000 products..
Slide
48
Customizing Datasheet View
Remember: formatting changes are not
saved automatically. Only structural
and data changes are saved
automatically.
Slide
49
Adjusting Row Heights

DRAG: the border between rows to
change all row heights in the datasheet
Row
Borders
Slide
51
Reordering Fields
SELECT: the entire field column
 DRAG: field name to move the column


To save the Datasheet view:
CLICK: Save button
Slide
52
Sorting Records
Allows you to better organize and
present data; makes information out of
raw data (alphabetical phone listing)
 SELECT: column or field to sort
CLICK: Sort Ascending button
CLICK: Sort Descending button

Slide
53
Filters
Lets you limit the display of records in a
table using a simple matching criterion
 Methods for filtering data:
 Filter For Input
 Filter By Selection
 Filter Excluding Selection
 Filter By Form
 Advanced Filter/Sort

Slide
54
What Is a Query?

A query is a question you ask of your
database
– How many customers live in Chicago?
– What is the average age of our employees?

There are two types of queries
– Select queries let you retrieve information
– Action queries let you modify information
Slide
55
Queries versus Filters

Although similar to filters, queries
provide additional functionality:
– you can display data from multiple tables
– you can control which fields display
– you can perform calculations on field values

While filters are temporary, queries are
saved as independent database objects
Slide
56
Query Design Window
A graphical query-by-example tool.
Displays tables
on which you’ve
chosen to base
your query
Displays the grid
that you use to
specify your criteria
and sorting options
Slide
57
Query Grid Area
Field: used to add a field to the grid
 Table: used to clarify the field’s table
 Sort: used to sort the resulting table
 Show: used to hide or show the field
 Criteria: used to set the query criteria
 Or: used to specify a second criterion

Slide
58
Specifying a Search Criteria
Enter an example of value you are
searching for in the Criteria row
 Use the question mark (?) wildcard to
represent a single unknown character
 Use the asterisk (*) wildcard to
represent more than one character
 Use operators (<, >, =) to limit records
between a range of values or dates

Slide
59
Examples of Search Criteria

Like Sm?th
returns records with Smyth and Smith

Like Ch*ng
returns records with Chang and Chickering

Between 21 And 65
returns records with values from 21 to 65

<=98000
returns values of less than or equal to 98000
Slide
60
How Do I Create a Form?
Using the AutoForm Wizard
 Using Design View

A form created using
the AutoForm Wizard
Form Navigation
Controls
Slide
61
What Is a Report?
An on-screen or printed
display of organized data
from a table
 Examples include: sales
summaries, invoices,
mailing labels, personal
address books, and
inventory listings

Report Title
Name Address Phone
Slide
62
AutoReport: Columnar
Created using the AutoReport Wizard.
Field
Names
Table
Data
Slide
63
AutoReport: Tabular
Created using the AutoReport Wizard.
Field
Names
Table
Data
Slide
64
Questions to consider when
designing a DataBase
Who will use it?
 What type of tasks?
 What are the data
sources?
 What output is required?

Slide
65
Data Modeling
Determine Data Requirements
•Entity Class
•something that can be identified in environment
•each entity class is a separate table
•each entity becomes a separate row in a table
•Attributes
•property or characteristic of entity
•each characteristic of an entity class become a column
•each characteristic of an entry become an entry in table
•Keys
•one or more attributes that uniquely identified an entity
•Constraints
Slide 66
•values or rules the DBMS must enforce
Designing a Database
 Determine
your output requirements
 Design your database on paper first
 Divide information into separate fields
 Divide information into separate tables
 Identify each record with a unique code
 Place important fields at top of structure
 Test your database
Slide
67
Access Lab and Assignment

If not familiar with Access, then go to lab and on
course web space do either Access 97 or
Access 2000 tutorials.
– access_tutorial_2000.html
– access_tutorial_97.html

Try doing exercises 1-5 and 20-25 on page 205
and 206 using c05Ex15.mdb and Rolling
Thunder Database from student CD (or from
G:\Courses\Infsy540).
 Database Assignment will be on course web
space next week
– dbms_assignment.html
– Due by beginning of class on Oct 17th
Slide
68
Questions?
Slide
69