Transcript Chapter 7

Systems Analysis & Design
(Sixth Edition)
PHASE 3: SYSTEMS DESIGN
Chapter 7
Data Design
1
Chapter Objectives




Explain data design concepts and data structures
Describe file processing systems and various
types of files
Understand database systems and define the
components of a database management system
(DBMS)
Describe Web-based data design
2
Chapter Objectives




Explain data design terminology, including
entities, fields, common fields, records, files,
tables, and key fields
Describe data relationships, draw an entityrelationship diagram, define cardinality, and
use cardinality notation
Explain the concept of normalization
Explain the importance of codes and describe
various coding schemes
3
Chapter Objectives




Describe relational and object-oriented
database models
Explain data warehousing and data mining
Differentiate between logical and physical
storage and records
Explain data control measures
4
Introduction



You will develop a physical plan for data organization, storage,
and retrieval
Begins with a review of data design concepts and terminology,
then discusses file-based systems and database systems,
including Web-based databases
Concludes with a discussion of data storage and access,
including strategic tools such as data warehousing and data
mining, physical design issues, logical and physical records,
data storage formats, and data control
5
Data Design Concepts

Data Structures




A file or table contains data about people, places,
things, or events that interact with the system
File-oriented system
File processing system
Database system
6
Data Design Concepts

Overview of File Processing

Potential problems



Data redundancy
Data integrity
Rigid data structure
7
Data Design Concepts

Overview of File Processing

Uses various types of files






Master file
Table file
Transaction file
Work file – scratch file
Security file
History file
8
Data Design Concepts

Overview of Database Systems




A properly designed database system offers a
solution to the problems of file processing
Provides an overall framework that avoids data
redundancy and supports a real-time, dynamic
environment
Database management system (DBMS)
The main advantage of a DBMS is that it offers
timely, interactive, and flexible data access
9
Data Design Concepts

Overview of Database Systems

Advantages






Scalability
Better support for client/server systems
Economy of scale
Flexible data sharing
Enterprise-wide application – database administrator
(DBA)
Stronger standards
10
Data Design Concepts

Overview of Database Systems

Advantages




Controlled redundancy
Better security
Increased programmer productivity
Data independence
11
Data Design Concepts

Database Tradeoffs



Because DBMSs are powerful, they require more
expensive hardware, software, and data networks
capable of supporting a multi-user environment
More complex than a file processing system
Procedures for security, backup, and recovery are
more complicated and critical
12
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
13
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
14
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
15
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
ODBC – open database connectivity
JDBC – Java database connectivity
16
Web-Based Database Design

Characteristics of Web-Based Design



In a Web-based design, the Internet serves as
the front end, or interface, for the database
management system
Internet technology provides enormous power
and flexibility
Web-based systems are popular because they
offer ease of access, cost-effectiveness, and
worldwide connectivity
17
Web-Based Database Design

Connecting a Database to the Web

Database must be connected to the Internet or
intranet


Middleware
Macromedia’s ColdFusion
18
Web-Based Database Design

Data Security


Web-based data must be totally secure, yet easily
accessible to authorized users
To achieve this goal, well-designed systems
provide security at three levels:
1. The database itself
2. The Web server
3. The telecommunication links that connect the
components of the system
19
Data Design Terminology

Definitions



Entity
Table or file
Field



Attribute
Common field
Record

Tuple
20
Data Design Terminology
Key Fields

Primary key

Composite key


Candidate key



(aka Combination key , Concatenated key, Multi-valued key)
Nonkey field
Foreign key
Secondary key
21
Data Design Terminology

Referential Integrity

Validity checks can help avoid data input errors
22
Entity-Relationship Diagrams



An entity is a person, place, thing, or event for
which data is collected and maintained
Provides an overall view of the system, and a
blueprint for creating the physical data
structures
Entity-relationship diagram
23
Entity-Relationship Diagrams

Drawing an ERD

The first step is to list
the entities that you
identified during the
fact-finding process and
to consider the nature of
the relationships that
link them
24
Entity-Relationship Diagrams

Types of Relationships



One-to-one relationship (1:1)
One-to-many relationship (1:M)
Many-to-many relationship (M:N)

Associative entity
25
Entity-Relationship Diagrams

Cardinality


Cardinality notation
Crow’s foot notation
26
Normalization



Table design
Involves four stages: unnormalized design,
first normal form, second normal form, and
third normal form
Most business-related databases must be
designed in third normal form
27
Normalization

Standard Notation Format

Designing tables is easier if you use a standard
notation format to show a table’s structure, fields,
and primary key
Example: NAME (FIELD 1, FIELD 2, FIELD 3)
28
Normalization

Repeating Groups and Unnormalized Designs

Repeating group


Often occur in manual documents prepared by users
Unnormalized design
29
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 table’s primary key
to include the primary key of the repeating group
Second Normal Form


To understand second normal form (2NF), you must
understand the concept of functional dependence
Functionally dependent
30
Normalization

Second Normal Form

A standard process exists for converting a table
from 1NF to 2NF
•
•
•
Create and name a separate table for each field in
the existing primary key
Create a new table for each possible combination
of the original primary key fields
Study the three tables and place each field with its
appropriate primary key
31
Normalization

Second Normal Form

Four kinds of problems are found with 1NF designs
that do not exist in 2NF




Consider the work necessary to change a particular
product’s description
1NF tables can contain inconsistent data
Adding a new product is a problem
Deleting a product is a problem
32
Normalization

Third Normal Form



3NF design avoids redundancy and data integrity
problems that still can exist in 2NF designs
A table design is in third normal form (3NF) if it is in
2NF and if no nonkey field is dependent on another
nonkey field
To convert the table to 3NF, you must remove all
fields from the 2NF table that depend on another
nonkey field and place them in a new table that uses
the nonkey field as a primary key
33
Normalization
A Normalization Example

To show the
normalization process,
consider the familiar
situation, which
depicts several entities
in a school advising
system: ADVISOR,
COURSE, and
STUDENT
34
Steps in Database Design
•
•
•
•
•
Create the initial ERD
Assign all data elements to entities
Create 3NF designs for all tables, taking care
to identify all primary, secondary, and foreign
keys
Verify all data dictionary entries
After creating your final ERD and normalized
table designs, you can transform them into a
database
35
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
Data design tasks include creating an initial ERD;
assigning data elements to an entity; normalizing all
table designs; and completing the data dictionary
entries for files, records, and data elements
Any questions?
36