Description
pls see attached requirements as well as powerpoint notes for completing the case study
Unformatted Attachment Preview
Chapter 9’s Case Study
New Century Wellness Group offers a holistic approach to healthcare with an emphasis
on preventive medicine as well as traditional medical care. In your role as an IT
consultant, you will help New Century develop a new information system.
Background
After completing the user interface, input, and output design for the new information
system, you will now focus on the data design of the DBMS that will support the system.
Begin by reviewing the DFDs that you prepared in Chapter 5 and the object models that
you created in Chapter 6.
Tasks
1. Create an initial ERD for the new system that contains at least eight entities.
2. Analyze each relationship to determine if it is 1:1, 1:M, or M:N.
3. Normalize your designs for all tables to ensure they are 3NF, and verify that all
primary, secondary, and foreign keys are identified properly. Update your ERD to
reflect any changes.
4. Review the Data Dictionary you created in Chapter 5 and double-check all data
dictionary entries. Make sure that the entries for data stores, records, and data
elements are documented completely and correctly. Determine what codes, if any,
will be used and be sure they are documented in the data dictionary.
Chapter 9
Data Design
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Learning Objectives (1 of 2)
•After this chapter, you will be able to:
• Explain basic data design concepts, including
data structures, DBMSs, and the evolution of
the relational database model
• Explain the main components of a DBMS
• Define the major characteristics of web-based
design
• Define data design terminology
• Draw entity-relationship diagrams
• Apply data normalization
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Learning Objectives (2 of 2)
• Utilize codes to simplify output, input, and data
formats
• Explain data storage tools and techniques,
including logical versus physical storage
• Explain data coding
• Explain data control measures
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Design Concepts (1 of 5)
•Data structures
• Framework for organizing, storing, and
managing data
• Comprises of files or tables that interact in
various ways
• Each file or table contains data about people,
places, things, or events
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Design Concepts (2 of 5)
•Mario and Danica: A Data Design Example
• Mario’s auto shop uses file-oriented systems
• MECHANIC SYSTEM uses the MECHANIC file to
store data about shop employees
• JOB SYSTEM uses the JOB file to store data about
work performed at the shop
• Danica’s auto shop uses a relational model
• SHOP OPERATIONS SYSTEM: tables are linked
by a common field named Mechanic No field
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Design Concepts (3 of 5)
FIGURE 9-3 Mario’s shop uses two separate
systems, so certain data must be entered twice.
This redundancy is inefficient and can produce
data errors.
FIGURE 9-4 Danica’s SHOP OPERATIONS
SYSTEM uses a database design, which
avoids duplication. The data can be viewed
as if it were one large table, regardless of
where the data is stored physically.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Design Concepts (4 of 5)
•Database management system (DBMS)
• Collection of tools, features, and interfaces
that enables users to add, update, manage,
access, and analyze data
• Advantages
• Scalability
• Economy of scale
• Enterprise-wide application
• Stronger standards and better security
• Data independence
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Design Concepts (5 of 5)
Figure 9-5 In this example, a sales database
can support four separate business systems.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
DBMS Components (1 of 2)
•Interfaces for users, database
administrators, and related systems
• Users
• Work with predefined queries and switchboard
commands
• Database administrators
• Responsible for DBMS management and support
• Related information systems
• DBMS provides support to related information
systems
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
DBMS Components (2 of 2)
•Schema
• Descriptions of all fields, tables, and
relationships
• Subschema: database portions that a particular
system or user needs or is allowed to access
•Physical data repository
• Contains the schema and subschemas
• Can be centralized or distributed
• Uses open database connectivity (ODBC)
compliant software
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Web-Based Design (1 of 2)
•Databases are created and managed by
using languages and commands that have
nothing to do with HTML
• Objective: connect the database to the Web
and enable data to be viewed and updated
• Middleware is used integrate different
applications and allow them to exchange data
•Web-based data must be secure, yet
easily accessible to authorized users
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Web-Based Design (2 of 2)
FIGURE 9-9 When a client workstation requests a Web page (1), the Web server uses middleware to generate
a data query to the database server (2). The database server responds (3), and middleware translates the
retrieved data into an HTML page that can be sent by the Web server and displayed by the user’s browser (4).
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Design Terms (1 of 3)
•Definitions
• Entity: person, place, thing, or event for which
data is collected and maintained
• Table or file: contains a set of related records
that store data about a specific entity
• Field (i.e., attribute): single characteristic or
fact about an entity
• Common field: attribute in more than one entity
• Record (i.e., tuple): set of related fields that
describes one instance of an entity
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Design Terms (2 of 3)
•Key fields
• Primary key: field or fields that uniquely and
minimally identifies a member of an entity
• Candidate key: field that could serve as a
primary key
• Foreign key: field in one table that must match
a primary key value in another table for a
relationship between the two tables to exist
• Secondary key: field or combination of fields
that can be used to access or retrieve records
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Design Terms (3 of 3)
•Referential
integrity
• Set of rules that
avoids data
inconsistency
and quality
problems
FIGURE 9-11 Microsoft Access allows a user to specify that
referential integrity rules will be enforced in a relational
database design.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Entity-Relationship Diagrams (1 of
8)
•Drawing an ERD
• List the entities
that were identified
during the systems
analysis phase
• Consider the nature
of the relationships
that link them
FIGURE 9-12 In an entity-relationship diagram,
entities are labeled with singular nouns and
relationships are labeled with verbs. The relationship
is interpreted as a simple English sentence.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Entity-Relationship Diagrams (2 of
8)
•Types of relationships
• One-to-one (1:1)
relationship: exactly
one of the second
entity occurs for each
instance of the first
entity
FIGURE 9-13 Examples of one-to-one
(1:1) relationships.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Entity-Relationship Diagrams (3 of
8)
• One-to-many
relationship (1:M): one
occurrence of the first
entity can relate to
many instances of the
second entity
• Each instance of the
second entity can
associate with only one
instance of the first entity
FIGURE 9-14 Examples of one-tomany (1:M) relationships.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Entity-Relationship Diagrams (4 of
8)
• Many-to-many
relationship (M:N):
one instance of the
first entity can relate
to many instances of
the second entity,
and vice versa
FIGURE 9-15 Examples of many-to-many (M:N)
relationships. Notice that the event or transaction that
links the two entities is an associative entity with its
own set of attributes and characteristics.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Entity-Relationship Diagrams (5 of
8)
FIGURE 9-16 An entity-relationship diagram for SALES REP, CUSTOMER, ORDER, PRODUCT,
and WAREHOUSE. Notice that the ORDER and PRODUCT entities are joined by an associative
entity named ORDER LINE.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Entity-Relationship Diagrams (6 of
8)
•Cardinality
• Describes numeric
relationship between
two entities
• Shows how instances of one
entity relate to instances of
another entity
• Crow’s foot notation
FIGURE 9-17 Crow’s foot notation is a
indicates various
common method of indicating cardinality.
possibilities using circles,
The four examples show how you can use
various symbols to describe the
bars, and symbols
relationships between entities.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Entity-Relationship Diagrams (7 of
8)
FIGURE 9-18 In the first example
of cardinality notation, one and
only one CUSTOMER can place
anywhere from zero to many of
the ORDER entity. In the second
example, one and only one
ORDER can include one ITEM
ORDERED or many. In the third
example, one and only one
EMPLOYEE can have one
SPOUSE or none. In the fourth
example, one EMPLOYEE, or
many employees, or none, can
be assigned to one PROJECT, or
many projects, or none.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Entity-Relationship Diagrams (8 of
8)
FIGURE 9-19 An ERD for a library system drawn with Visible Analyst. Notice that crow’s
foot notation has been used and relationships are described in both directions.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Normalization (1 of 9)
•Normalization: creating table designs by
assigning specific fields or attributes to
each table in the database
• Table design: specifies fields
• Identifies the primary key in a particular table or file
• Normalization stages
• Unnormalized design
• First normal form
• Second normal form
• Third normal form
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Normalization (2 of 9)
•Standard notation format
• Used to show a table’s structure, fields, and
primary key
• The primary key field(s) is underlined
• NAME (FIELD 1, FIELD 2, FIELD 3)
• Recognition of repeating group fields is
important
• Repeating group: set of one or more fields that can
occur any number of times in a single record
• Each occurrence would possess different values
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Normalization (3 of 9)
FIGURE 9-20 In the ORDER table design, two orders have repeating groups that contain several
products. ORDER is the primary key for the ORDER table, and PRODUCT NUMBER serves as a primary
key for the repeating group. Because it contains repeating groups, the ORDER table is unnormalized.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Normalization (4 of 9)
•First Normal Form (1NF)
• Does not contain a repeating group
• Converting an unnormalized design to 1NF
requires expansion of the table’s primary key
to include the primary key of the repeating
group
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Normalization (5 of 9)
FIGURE 9-21 The ORDER table as it appears in 1NF. The repeating groups have been eliminated.
Notice that the repeating group for order 86223 has become three separate records, and the
repeating group for order 86390 has become two separate records. The 1NF primary key is a
combination of ORDER and PRODUCT NUMBER, which uniquely identifies each record.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Normalization (6 of 9)
•Second Normal Form (2NF)
• Examples of functional dependence
• Field A is functionally dependent on Field B if the
value of Field A depends on Field B
• A table design is in 2NF if it is in 1NF and all fields
not part of the primary key are functionally
dependent on the entire primary key
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Normalization (7 of 9)
FIGURE 9-22 ORDER, PRODUCT, and ORDER LINE tables in 2NF. All
fields are functionally dependent on the primary key.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Normalization (8 of 9)
•Third Normal Form (3NF)
• Design is in 3NF if every nonkey field depends
on the key, the whole key, and nothing but the
key
• Avoids redundancy and data integrity
problems that still can exist in 2NF designs
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Normalization (9 of 9)
FIGURE 9-23 When the PRODUCT
table is transformed from 2NF to 3F,
the result is two separate tables:
PRODUCT and SUPPLIER. Note that
in 3NF, all fields depend on the key, the
whole key, and nothing but the key!
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Two Real-World Examples
•A good way to learn about normalization is
to apply the rules to a representative
situation
• Refer to the text for two scenarios
• School
• Technical service company
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Codes (1 of 4)
•Overview of codes
• Shorter than the data they represent
• Save storage space and costs
• Decrease data entry time and transmission
time
• Reveal or conceal information
• Reduce data input errors
• Easier to remember
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Codes (2 of 4)
•Types of codes
• Sequence codes: numbers or letters assigned
in a specific order
• Block sequence codes: use blocks of numbers
for different classifications
• Alphabetic codes: use letters to distinguish
one item from another
• Category codes: identify a group of related items
• Abbreviation codes: alphabetic abbreviations
• Mnemonic codes: specific combination of letters
that are easy to remember
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Codes (3 of 4)
• Significant digit codes: distinguish items by
using a series of subgroups of digits
• Derivation codes: combine data from different
item attributes, or characteristics
• Cipher codes: use a keyword to encode a
number
• Action codes: indicate what action is to be
taken with an associated item
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Codes (4 of 4)
•Designing codes
• Keep codes concise and consistent
• Allow for expansion
• Keep codes stable and make them unique
• Use sortable codes and a simple structure
• Avoid confusion and make codes meaningful
• Use a code for a single purpose
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Storage and Access (1 of 7)
•Tools and techniques
• Data warehouse
• An integrated collection of data that can include
seemingly unrelated information, no matter where it
is stored in the company
• Data mart
• Designed to serve needs of a specific department
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Storage and Access (2 of 7)
FIGURE 9-37 A data warehouse stores data from several systems. By selecting data dimensions, a
user can retrieve specific information without having to know how or where the data is stored.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Storage and Access (3 of 7)
•Data mining
• Looks for meaningful data patterns and
relationships
• Suggested goals for data mining
• Increase the number of pages viewed per session
and referred customers
• Reduce clicks to close
• Increase checkouts per visit and average profit per
checkout
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Storage and Access (4 of 7)
•Logical versus physical storage
• Logical storage: data that a user can view,
understand, and access, regardless of how or
where that information actually is organized or
stored
• Physical storage: strictly hardware related
• Involves the process of reading and writing binary
data to physical media
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Storage and Access (5 of 7)
•Data coding
• EBCDIC (Extended Binary Coded Decimal
Interchange Code)
• Mainframe computers and high-capacity servers
• ASCII (American Standard Code for
Information Interchange)
• Used on most personal computers
• Binary storage format
• Represents numbers as actual binary values
• Unicode: uses two bytes per character
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Storage and Access (6 of 7)
FIGURE 9-38 Unicode is an international coding format that represents characters as integers,
using 16 bits per character. The Unicode Consortium maintains standards and support for
Unicode.
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Storage and Access (7 of 7)
•Storing dates
• International Organization for Standardization
(ISO) requires a format of four digits for the
year, two for the month, and two for the day
(YYYYMMDD)
• Absolute date: total number of days from
some specific base date
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Data Control
•Well-designed DBMS
• Must provide built-in control and security
•Forms of data protection
• Limited access to files and databases
• User ID and passwords
• Permissions and encryption
•Backup copies of databases must be
retained for a specified period of time
• Recovery procedures can be used to restore
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Summary (1 of 3)
•A database consists of linked tables that
form an overall data structure
• DBMS enable users to add, update, manage,
access, and analyze data in a database
•DBMS designs are more powerful and
flexible than traditional file-oriented
systems
• Components include interfaces for users,
database administrators, and related systems
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Summary (2 of 3)
•Key fields include primary keys, candidate
keys, foreign keys, and secondary keys
•An entity-relationship diagram (ERD) is a
graphic representation of all system
entities and the relationships among them
•Normalization is a process for avoiding
problems in data design
•A code is a set of letters or numbers used
to represent data in a system
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Summary (3 of 3)
•Logical storage is information seen
through a user’s eyes, regardless of how
or where that information actually is
organized or stored
•File and database control measures
include limiting access to the data, data
encryption, backup/recovery procedures,
audit-trail files, and internal audit fields
Systems Analysis Design, 12th Edition. ©2020 Cengage. May not be copied, scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
Purchase answer to see full
attachment
