chapter09-120827115409-phpapp01
TRANSCRIPT
-
8/11/2019 chapter09-120827115409-phpapp01
1/45
Systems Analysis and Design
9thEdition
Chapter 9
Data Design
-
8/11/2019 chapter09-120827115409-phpapp01
2/45
Chapter Objectives
Explain file-oriented systems and how they differ
from database management systems
Explain data design terminology, including entities,
fields, common fields, records, files, tables, and keyfields
Describe data relationships, draw an entity
relationship diagram, define cardinality, and use
cardinality notation
Explain the concept of normalization
2
-
8/11/2019 chapter09-120827115409-phpapp01
3/45
Chapter Objectives
Explain the importance of codes and describe
various coding schemes
Explain data warehousing and data mining
Differentiate between logical and physical
storage and records
Explain data control measures
3
-
8/11/2019 chapter09-120827115409-phpapp01
4/45
Introduction
Begins with a review of data design conceptsand terminology, then discusses file-basedsystems and database systems, including Web-
based databases Concludes with a discussion of data storage
and access, including strategic tools such asdata warehousing and data mining, physicaldesign issues, logical and physical records,data storage formats, and data controls
4
-
8/11/2019 chapter09-120827115409-phpapp01
5/45
Data Design Concepts
Data Structures
Each file or table
contains data about
people, places, things orevents that interact with
the information system
File-oriented system
Database managementsystem (DBMS)
5
-
8/11/2019 chapter09-120827115409-phpapp01
6/45
Data Design Concepts
Overview of File
Processing
File processing can be
efficient and cost-effective in certain
situations
Potential problems
Data redundancy
Data integrity
Rigid data structure
6
-
8/11/2019 chapter09-120827115409-phpapp01
7/45
Data Design Concepts
Overview of File Processing
Various types of files
Master file
Table file
Transaction file
Work file
Security file
History file
7
-
8/11/2019 chapter09-120827115409-phpapp01
8/45
Data Design Concepts
The Evolution from FileSystems to DatabaseSystems A database management
system (DBMS) is acollection of tools,features, and interfacesthat enables users to add,update, manage, access,and analyze the contentsof a database
The main advantage of aDBMS is that it offerstimely, interactive, andflexible data access
8
-
8/11/2019 chapter09-120827115409-phpapp01
9/45
Data Design Concepts
The Evolution from File Systems to Database
Systems
Some Advantages
Scalability
Better support for client/server systems
Economy of scale
Flexible data sharing Enterprise-wide applicationdatabase administrator
(DBA)
Stronger standards
9
-
8/11/2019 chapter09-120827115409-phpapp01
10/45
DBMS Components
Interfaces for Users,
Database Administrators,
and Related Systems
Users
Query language
Query by example (QBE)
SQL (structured query
language)
Database Administrators A DBA is responsible for
DBMS management and
support
10
-
8/11/2019 chapter09-120827115409-phpapp01
11/45
DBMS Components
Interfaces for Users, Database Administrators,
and Related Systems
Related information systems
A DBMS can support several related information
systems that provide input to, and require specific data
from, the DBMS
No human intervention is required for two-way
communication
11
-
8/11/2019 chapter09-120827115409-phpapp01
12/45
DBMS Components
Data Manipulation Language
A data manipulation language (DML) controls
database operations, including storing, retrieving,
updating, and deleting data
Schema
The complete definition of a database, including
descriptions of all fields, tables, and relationships,is called a schema
You also can define one or more subschemas
12
-
8/11/2019 chapter09-120827115409-phpapp01
13/45
DBMS Components
Physical Data Repository
The data dictionary is transformed into a physical
data repository, which also contains the schema
and subschemas
The physical repository might be centralized, or
distributed at several locations
ODBCopen database connectivity JDBCJava database connectivity
13
-
8/11/2019 chapter09-120827115409-phpapp01
14/45
Web-Based Database Design
Characteristics of Web-Based Design
14
-
8/11/2019 chapter09-120827115409-phpapp01
15/45
Web-Based Database Design
Internet Terminology
Web browser
Web page
HTML (Hypertext Markup Language)
Tags
Web server
Web site
15
-
8/11/2019 chapter09-120827115409-phpapp01
16/45
Web-Based Database Design
Internet Terminology
Intranet
Extranet
Protocols
Web-centric
Clients
Servers
16
-
8/11/2019 chapter09-120827115409-phpapp01
17/45
Web-Based Database Design
Connecting a Database to the Web
Database must be connected to the Internet orintranet
Middleware Adobe ColdFusion
Data Security
Well-designed systems provide security at threelevels: the database itself, the Web server, and thetelecommunication links that connect thecomponents of the system
17
-
8/11/2019 chapter09-120827115409-phpapp01
18/45
Data Design Terminology
Definitions
Entity
Table or file
Field
Record
Tuple
18
-
8/11/2019 chapter09-120827115409-phpapp01
19/45
Data Design Terminology
Key Fields
Primary key
Candidate key
Foreign key
Secondary key
19
-
8/11/2019 chapter09-120827115409-phpapp01
20/45
Data Design Terminology
Referential Integrity
Validity checks can helpavoid data input errors
In a relational database,
referential integritymeans that a foreign keyvalue cannot be enteredin one table unless itmatches an existing
primary key in anothertable
Orphan
20
-
8/11/2019 chapter09-120827115409-phpapp01
21/45
Entity-Relationship Diagrams
Drawing an ERD
The first step is to list the
entities that you identified
during the fact-finding
process and to considerthe nature of the
relationships that link them
A popular method is to
represent entities as
rectangles and
relationships as diamond
shapes
21
-
8/11/2019 chapter09-120827115409-phpapp01
22/45
Entity-Relationship Diagrams
Types of Relationships
Three types of
relationships can exist
between entities
One-to-one relationship
(1:1)
One-to-many
relationship (1:M)
Many-to-many
relationship (M:N)
22
-
8/11/2019 chapter09-120827115409-phpapp01
23/45
Entity-Relationship Diagrams
Cardinality Cardinality notation
Crows foot notation
Unified Modeling
Language (UML)
Now that you understand
database elements and
their relationships, you
can start designing tables
23
-
8/11/2019 chapter09-120827115409-phpapp01
24/45
Normalization
Standard Notation Format
Designing tables is easier if you use a standard
notation format to show a tables structure, fields,
and primary key
Example: NAME (FIELD 1, FIELD 2, FIELD 3)
24
-
8/11/2019 chapter09-120827115409-phpapp01
25/45
Normalization
Repeating Groups and Unnormalized Design
Repeating groups
Often occur in manual documents prepared by users
Unnormalized
Enclose the repeating group of fields within a
second set of parentheses
25
-
8/11/2019 chapter09-120827115409-phpapp01
26/45
Normalization
First Normal Form
A table is in first normal form (1NF) if it does not
contain a repeating group
To convert, you must expand the tables primary
key to include the primary key of the repeating
group
26
-
8/11/2019 chapter09-120827115409-phpapp01
27/45
Normalization
Second Normal Form
A table design is in second normal form (2NF) if it is in1NF and if all fields that are not part of the primarykey are functionally dependent on the entire primary
key
A standard process exists for converting a table from1NF to 2NF
The objective is to break the original table into two or
more new tables and reassign the fields so that eachnonkey field will depend on the entire primary key inits table
27
-
8/11/2019 chapter09-120827115409-phpapp01
28/45
-
8/11/2019 chapter09-120827115409-phpapp01
29/45
Normalization
A Normalization Example
29
-
8/11/2019 chapter09-120827115409-phpapp01
30/45
Using Codes During Data Design
Overview of Codes
Because codes often are used to represent data,
you encounter them constantly in your everyday
life They save storage space and costs, reduce data
transmission time, and decrease data entry time
Can reduce data input errors
30
-
8/11/2019 chapter09-120827115409-phpapp01
31/45
-
8/11/2019 chapter09-120827115409-phpapp01
32/45
Using Codes During Data Design
Developing a Code
1. Keep codes concise
2. Allow for expansion
3. Keep codes stable
4. Make codes unique
5. Use sortable codes
32
-
8/11/2019 chapter09-120827115409-phpapp01
33/45
Using Codes During Data Design
Developing a Code
6. Avoid confusing codes
7. Make codes meaningful
8. Use a code for a single purpose
9. Keep codes consistent
33
-
8/11/2019 chapter09-120827115409-phpapp01
34/45
-
8/11/2019 chapter09-120827115409-phpapp01
35/45
Database Models
A Real-World BusinessExample
Imagine a company that
provides on-site service
for electronic
equipment, including
parts and labor
35
-
8/11/2019 chapter09-120827115409-phpapp01
36/45
Database Models
Working with a Relational Database
To understand the power and flexibility of a
relational database, try the following exercise
Suppose you work in IT, and the sales team needsanswers to three specific questions
The data might be stored physically in seven tables
36
-
8/11/2019 chapter09-120827115409-phpapp01
37/45
Data Storage and Access
Data storage and access
involve strategic
business tools
Strategic tools for datastorage and access
Data warehouse
dimensions
Data mart
Data Mining
37
-
8/11/2019 chapter09-120827115409-phpapp01
38/45
Data Storage and Access
Logical and Physical Storage
Logical storage
Characters
Data element or data item
Logical record
Physical storage
Physical record or block
Buffer
Blocking factor
38
-
8/11/2019 chapter09-120827115409-phpapp01
39/45
Data Storage and Access
Data Coding and
Storage
Binary digits
Bit Byte
EBCDIC, ASCII, and
Binary
Unicode
39
-
8/11/2019 chapter09-120827115409-phpapp01
40/45
Data Storage and Access
Data Coding and Storage
Storing dates
Y2K Issue
Most date formats now are based on the modelestablished by the International Organization for
Standardization (ISO)
Absolute date
40
-
8/11/2019 chapter09-120827115409-phpapp01
41/45
Data Control
User ID
Password
Permissions
Encryption
Backup
Recovery procedures
Audit log files
Audit fields
41
-
8/11/2019 chapter09-120827115409-phpapp01
42/45
Chapter Summary
Files and tables contain data about people,
places, things, or events that affect the
information system
DBMS designs are more powerful and flexible
than traditional file-oriented systems
42
-
8/11/2019 chapter09-120827115409-phpapp01
43/45
Chapter Summary
An entity-relationship (ERD) is a graphic
representation of all system entities and the
relationships among them
A code is a set of letters or numbers used to
represent data in a system
The most common database models are
relational and object-oriented
43
-
8/11/2019 chapter09-120827115409-phpapp01
44/45
Chapter Summary
Logical storage is information seen through ausers eyes, regardless of how or where thatinformation actually is organized or stored
Physical storage is hardware-related and involvesreading and writing blocks of binary data tophysical media
File and database control measures include
limiting access to the data, data encryption,backup/recovery procedures, audit-trail files, andinternal audit fields
44
-
8/11/2019 chapter09-120827115409-phpapp01
45/45
Chapter Summary
Chapter 9 complete