Transcript DBLecture2

CSE 2337
Introduction to
Data Management
Access Book – Ch 1
1
DB Design Process: Discovery Phase
2
Discovering and Evaluating Sources of Existing Data
• Identify information that organization
needs to manage and organize
• Might begin to see patterns that
indicate how to organize data
• Database management system
(DBMS)
– Includes:
• Oracle
• Microsoft Access
• MySQL
3
One Goal
Reduce Data Duplication
and Redundancy.
4
Considering Different Types of Data
5
The Text and Memo Data Types
• Text data type
– Letters and numbers
– Not used in calculations or formulas
– Stores maximum of 255 characters
– Default for all fields created in access
database
• Memo data type
– Store long passages of text
– Stores maximum of 65,535 characters
6
The Number Data Type
• Stores both positive and negative
numbers
• Contains up to 15 digits
• Use for values used in calculations
7
AutoNumber
• Unique to Access
• Number automatically generated by
access
• Produces unique values for each record
• Useful to distinguish two records that
share identical information
• Produces values of up to nine digits
8
Selecting the Correct Data Type
• Helps store correct data in correct format
while using least amount of space
• Eases data entry and interactivity with
data
• Choosing certain data types results in
user-friendly interactive features
– Drop-down menus
– Check boxes
– Hyperlinks
• Correctly manipulate data
9
Assigning the Correct Field Size for Text Fields
• Important to consider field size when
assigning data types
– Minimize space reserved for each record
by assigning smallest data type that will
store data
• Be conservative when assigning field
sizes
– But not too conservative
10
Assigning the Correct Field Size for Number Fields
11
Naming Conventions
• Database tables must
– Have unique names
– Follow established naming conventions
• General rules for naming objects
– Object names cannot exceed 64 characters
– Object names cannot include period,
exclamation point, accent grave, or brackets
– Object names should not include spaces
– Most developers capitalize first letter of each
word when table name includes two words
12
Leszynski/Reddick Naming Conventions
I don’t particularly care!!!
13
Understanding Relational Database Objects
• Users can view data in tables by:
– Opening table
– Creating other objects
• Four main objects in database
– Tables
– Queries
– Forms
– Reports
14
Tables
• Data in relational database stored in
one or more tables
• View data in table
– Open it and scroll through records
15
Queries
• Query
– Question asked about data stored in
database
• Query results
– Look similar to table
– Fields displayed in columns
– Records displayed in rows
16
Forms
• Used to view add delete, update and print
records in database
• Based on table or query
• Interface more attractive than table
datasheet
• Customize form’s appearance with
instructions and command buttons
• Switchboard
– Form displayed when database opened
– Provides controlled method for users to open
objects in database
17
18
Reports
• Formatted presentation of data from
table or query
• Created as printout or to be viewed
on screen
• Data displayed by report usually
based on query
• Dynamic
– Reflect latest data from object
• Cannot be used to modify data
19
Creating Table Relationships
• Take advantage of interrelated objects
• Goal in good database design
– Create separate tables for each entity
– Ensure each table has primary key
– Use common field to relate tables
• Relate two (or more) tables
– Query them as though they are one big table
• Join
– Specifies relationship between tables and
properties of relationship
20
One-to-Many Relationships
• Abbreviated as 1:M
• One record in first table matches
zero one or many records in related
table
• Primary table
– One side
• Related table
– Many side
21
22
One – to - one
• Abbreviated as 1:1
• Exists when each record in one table
matches exactly one record in
related table
23
24
Understanding Referential Integrity
• Null value
– Field does not contain any value
• Entity integrity
– Guarantee that there are no duplicate records
in table
– Each record unique
– No primary key field contains null values
• Referential integrity
– If foreign key in one table matches primary
key in second table
– Values in foreign key must match values in
primary key
25
Integrity
• When database does not enforce
referential integrity
– Problems occur that lead to inaccurate
and inconsistent data
• Orphaned
– No longer match between primary key
in primary table and foreign keys in
related table
26
Integrity Errors
27