Chapter 5
Database Processing
Jason C. H. Chen, Ph.D.
Professor of MIS
School of Business Administration
Gonzaga University
Spokane, WA 99258
[email protected]
Dr. Chen, Management Information Systems
GearUp Scenarios
Chapter 5: Database Processing
• GearUp does not have the easy access to
data needed to analyze vendor quality and
costs
Dr. Chen, Management Information Systems
2
GearUp: “Can You Put the Data into
Access?”
• GearUp needs operating data to analyze for costcutting decisions
• Need to extract and combine data from multiple
systems
• Will use Access to create report
• In general, what does GearUp need for improving
its decisions?
• Answer - Database processing
Dr. Chen, Management Information Systems
Chapter Preview
•
•
•
•
Businesses of every size organize data records into collections called databases. At one
extreme, small businesses use databases to keep track of customers; at the other extreme, huge
corporations such as Dell and Amazon.com use databases to support complex sales, marketing,
and operations activities. In between, we have businesses like FlexTime that use databases as a
crucial part of their operations, but they don’t have a trained and experienced staff to manage
and support the databases. To obtain answers to the one-of-a-kind queries he needs, Neil needs
to be creative and adaptable in the way that he accesses and uses his database.
This chapter discusses the why, what, and how of database processing. We begin by describing
the purpose of databases and then explain the important components of database systems. We
then overview the process of creating a database system and summarize your role as a future
user of such systems.
Users have a crucial role in the development of database applications. Specifically, the
structure and content of the database depends entirely on how users view their business activity.
To build the database, the developers will create a model of that view using a tool called the
entity-relationship model. You need to understand how to interpret such models, because the
development team might ask you to validate the correctness of such a model when building a
system for your use. Finally, we describe the various database administration tasks.
This chapter focuses on database technology. Here we consider the basic components of a
database and their functions. You will learn about the use of database reporting and data mining
in Chapter 9.
Dr. Chen, Management Information Systems
4
Study Questions
Q1: What is the purpose of a database?
Q2: What is a database?
Q3: What are the components of a database application
system?
Q4: How do database applications make databases more
useful?
Q5: How are data models used for database development?
Q6: How is a data model transformed into a database design?
Q7: What is the users’ role in the development of databases?
Q8: 2022?
Dr. Chen, Management Information Systems
• What is the most important
computer resource on the Internet
Search Engine (backend)?
?
Dr. Chen, Management Information Systems
Answer: Database
• You will learn the following:
– 1) Database Concepts and
– 2) Conceptual Database Design (Data Model)
Dr. Chen, Management Information Systems
DATABASE TRENDS
Linking Internal Databases to the Web
User tier
Dr. Chen, Management Information Systems
Database tier
Server tier
N
8
Web-Based Client/Server Database Architecture
2. Request for
data-based Web page
1. Request for
data-based Web page
Network
8. Data-based
Web page
Web Browser
3. Data query
6. Retrieved data
7. Data-based
Web page
4. Data
query
Web server
5. Retrieved
data
Legend
Communications
Between Web browser
And Web server
Database Server
Dr. Chen, Management Information Systems
Communications
Between Web server
And database server
9
TWO TYPES OF DATA
PROCESSING
• FILE-BASED
• DATA-BASED
Dr. Chen, Management Information Systems
10
Figure: File-based Approach to Data
Processing
Checking
Account
Data
Files
Checking
Account
Programs
Auto
Loan
Data
Files
Auto
Loan
Programs
Savings
Account
Data
Files
Savings
Account
Programs
Dr. Chen, Management Information Systems
11
Three file processing systems at a company
Duplicate
Data
Dr. Chen, Management Information Systems
What is the main
problem in the
company’s processing
systems?
Disadvantages of File Processing
• Program-Data Dependence
– All programs maintain metadata for each file they use
• Data Redundancy (Duplication of data)
– Different systems/programs have separate copies of the same
data
• Limited Data Sharing
– No centralized control of data
• Lengthy Development Times
– Programmers must design their own file formats
• Excessive Program Maintenance
– 80% of of information systems budget
Dr. Chen, Management Information Systems
13
Questions
• Question-1: How to overcome most of the
problems from File-based data processing?
• Answer: Data-based approach
• Question-2: Is MS/Access a database?
• Answer: No (Why?)
• Question-3: Then, what is it?
• You will learn all these concepts in the chapter.
Dr. Chen, Management Information Systems
Database Management System
Application
#1
Application
#2
???
Database
containing
centralized
shared data
Application
#3
Dr. Chen, Management Information Systems
15
Database Management System
Application
#1
Application
#2
Application
#3
Dr. Chen, Management Information Systems
DBMS
Database
containing
centralized
shared data
DBMS manages data
resources like an operating
system manages hardware
resources
16
Q1: What Is the Purpose of a Database?
• As a database user, you play a crucial role in developing
database applications for your business or organization.
• Organize and keep track of things
• Keep track of multiple themes
• General rule:
spreadsheet
 Single theme store in a ____________
database
 Multiple themes require a __________
Dr. Chen, Management Information Systems
Fig 5-1 List of Student Grades, Presented in a Spreadsheet
17
Student Data Form With Multiple Themes
Fig 5-2 Student Data Shown in a Form, from a Database
Dr. Chen, Management Information Systems
Q2: What is a database?
• These are terms you’ll need to
know when working with
databases.
– A database is a self-describing
collection of integrated records
– A database is an organized collection
of logically related data files.
(different from the text)
– Bytes, characters of data, are grouped
into columns which are also called
fields.
– The fields are grouped into rows which
are also called records.
– A table is a group of similar rows or
records which is also called a file.
Dr. Chen, Management Information Systems
Fig 5-3 Student Table (also
called a file)
5-1919
Hierarchy of Data Elements

This figure shows the hierarchy of data elements working from the
smallest at the bottom to the largest at the top.
Dr. Chen, Management Information Systems
Fig 5-4 Hierarchy of Data Elements
20
Components of a Database:
Metadata Describes Structure of Database

A database is more than just a group of tables. It
includes tables or files plus the relationships
among rows in the tables and metadata that
describes the database’s structure.
Fig 5-5 Components of a Database
Dr. Chen, Management Information Systems
21
Metadata



Metadata are data that describe data and makes databases
easy to use. Metadata is always a part of a database.
The Field Name, Data Type, and Description at the top of this
diagram are part of the database’s metadata.
The additional
field properties
at the bottom
are also
considered
metadata.
Fig 5-7 Sample Metadata (in Access)
Dr. Chen, Management Information Systems
5-22
What Are Relationships Among Rows?


This diagram shows
relationships among
rows of different
tables.
The Student Number
value in the first row
of the top table,1325,
relates to the same
Student Number
value in a row in the
second table.
Dr. Chen, Management Information Systems
Fig. 5-6: Example of Relationships Among Rows
23
What Are Relationships Among Rows?



This diagram shows
relationships among
rows of different
tables.
The Student Number
value in the first row
of the top table,1325,
relates to the same
Student Number
value in a row in the
second table.
Both that Student
Number values and
that of 4867 (and
1325) in the second
table relate to those
in the third.
Dr. Chen, Management Information Systems
Fig. 5-6: Example of Relationships Among Rows
24
Relationship Special Terms
• Key
 A column or group of columns that
identifies a unique row in a table.
 Student Number is the key of the Student
table. Given a value of Student Number,
you can determine one and only one row
in Student. Only one student has the
number 1325.
 Every table must have a (primary) key.
 Sometimes more than one column is
needed to form a unique identifier. In a
table called City, for example, the key
would consist of combination of columns
(City, State) – form a composite key.
 Email_Num is the key of Email Table.
 VisitID is the key of Office_Visit Table.
Dr. Chen, Management Information Systems
pk
pk
pk
25
Relationship Special Terms
pk
• Foreign keys
– These are keys of a different
(foreign) table than the table
in which they reside.
– A relational database carries
data in the form of tables and
uses foreign keys to represent
relationships
• Relational databases
 Relationships among tables
are created by using foreign
keys.
• Relation
 Formal name for a table
fk
pk
pk
fk
What is (are) the fk in this relation?
Dr. Chen, Management Information Systems
26
Study Questions
Q1: What is the purpose of a database?
Q2: What is a database?
Q3: What are the components of a database application
system?
Q4: How do database applications make databases more
useful?
Q5: How are data models used for database development?
Q6: How is a data model transformed into a database design?
Q7: What is the users’ role in the development of databases?
Q8: 2022?
Dr. Chen, Management Information Systems
Q3: What Are the Components of a
Database Application System?
• Applications make database data more accessible and useful.
• Users employ a database application that consists of forms,
formatted reports, queries, and application programs.
• Database management system (DBMS) processes database
tables for applications.
Fig 5-8 Components of a Database Application System
Dr. Chen, Management Information Systems
28
What Is a Database Management System (DBMS)?
• Database developers use the DBMS to create or modify tables,
relationships, and other structures in a database.
• A DBMS (database management system) program is used to
create, process, and administer a database.
• Popular DBMS products include:
•
•
•
•
•
Oracle from Oracle Corporation (World #1 Database company)
DB2 from IBM
Access from Microsoft (for personal computers)
SQL Server from Microsoft (for large computer systems)
MySQL, an open-source (it was acquired by Oracle) product that’s
license-free
• Don’t confuse a DBMS, which is a software program, with a
database, which is a collection of tables, relationships and
metadata. But, they are two different concepts.
Dr. Chen, Management Information Systems
29
Creating the Database and Its Structures
• Database developers use the DBMS to create and modify
tables, relationships, and other structures in the database.
• Below, the developer has added a new column called
Response?. This new column has data type Yes/No.
Dr. Chen, Management Information Systems
Fig 5-9 Adding a New Column to a Table (in Access)
30
Processing the Database
• Four DBMS operations
 Read,
insert
 _______,
 modify,
delete data
 _______
• Applications call DBMS in different ways
 From a form, when the user enters new or changed
data, a computer program behind the form calls the
DBMS to make the necessary database changes.
 From an application program, the program calls the
DBMS directly to make the change.
Dr. Chen, Management Information Systems
31
Structured Query Language (SQL)
• SQL—“see-quell”
 International standard language for creating databases and
database structures, and processing databases
• Used by most popular DBMS
• Following SQL statement inserts a new row into the
Student table:
INSERT INTO Student
([Student Number], [Student Name], HW1, HW2, MidTerm)
VALUES
(1000, ’Franklin, Benjamin’, 90, 95, 100);
Dr. Chen, Management Information Systems
32
Administering the Database
• DBMS provides tools to assist in administration of
the database.
• Used to set up a security system involving user
accounts, passwords, permissions, and limits for
processing the database
• Backing up database data, adding structures to
improve performance of database applications,
removing data no longer wanted or needed, and
similar tasks
• Most organizations dedicate one or more employees
to the role of database administration (as database
administrator – DBA)
Dr. Chen, Management Information Systems
33
Major Responsibilities of Database Administration
Fig 5-10 Summary of Database Administration Tasks
Dr. Chen, Management Information Systems
34
Study Questions
Q1: What is the purpose of a database?
Q2: What is a database?
Q3: What are the components of a database application
system?
Q4: How do database applications make databases more
useful?
Q5: How are data models used for database development?
Q6: How is a data model transformed into a database design?
Q7: What is the users’ role in the development of databases?
Q8: 2022?
Dr. Chen, Management Information Systems
Multi-User Processing Problem
• A database application includes forms, reports,
queries, and applications programs available to one or
more users.
• This figure depicts multi-user database processing. The system
must be managed properly to avoid the following problems
(called concurrent problems):
 Potential update loss because
two users update same data
at the same time
 Potential locking problems
 Possible data conflicts
• How to avoid these
problems?
Dr. Chen, Management Information Systems
Fig 5-11 Use of Multiple Database Applications
36
Multi-User Processing Problem
Dr. Chen, Management Information Systems
Multi-User Processing Problem
(another example)
• Lost-update problem
 Process A reads a customer record from a file containing account
information, including the customer’s account balance and phone
number.
 Process B now reads the same record from the same file so it has its
own copy.
 Process A changes the account balance in its copy of the customer
record and writes the record back to the file.
 Process B—which still has the original stale value for the account
balance in its copy of the customer record—updates the customer’s
phone number and writes the customer record back to the file.
 Process B has now written its stale account balance value to the file,
causing the changes made by process A to be lost.
(Source: http://en.wikipedia.org/wiki/File_locking)
Dr. Chen, Management Information Systems
38
What Are Forms, Reports, and Queries?
• Data entry forms are used to read, insert, modify, and
delete data.
Reports
in a data in a structured context
• Reports
areshow
useddata
to show
structured context.
as the example to the right shows.
Fig 5-12 Example of a Student Report
Dr. Chen, Management Information Systems
39
What Are Forms, Reports, and Queries?

ASample
query form helps the
query
form find answers
user
quickly
toused
questions. In this case,
to enter
the
words “barriers to
phrasewere
for found in
entry”
search record.
Baker’s
Sample query results of query operation
Fig 5-13 (a) & (b) Sample Query and Results
Dr. Chen, Management Information Systems
40
Why Are Database Application Programs
Needed?
• Forms, reports, and queries work well for standard
functions. However, most applications have unique
requirements that a simple form, report, or query cannot
meet.
• Application programs process logic that is specific to a
given business need.
• Application programs serve as an intermediary between the
Web server and database.
 Responds to events, such as when a user presses a
submit button; also reads, inserts, modifies, and deletes
database data
Dr. Chen, Management Information Systems
41
Four Database Application Programs Running on a Web
Server Computer
• Database application programs process logic specific to a given
business need. For example, a program could track backordered items
and hold a customer order until the items are ready to ship.
• Application programs enable database processing over the Internet as
the figure below shows. Users access the applications programs via a
Web server, which in turn accesses a single DBMS and database.
Dr. Chen, Management Information Systems
Fig 5-14 Four Application Programs on a Web Server Computer
42
Enterprise DBMS vs. Personal DBMS
• Enterprise DBMS
 Process large organizational and workgroup databases
 Support many, possibly thousands, of users and many different
database applications
 Support 24/7 operations and can manage databases that span
dozens of different magnetic disks with hundreds of gigabytes
or more of data
 IBM’s DB2, Microsoft’s SQL Server, and Oracle’s Oracle are
examples of enterprise DBMS products.
• Personal DBMS
 Designed for smaller, simpler database applications
 Used for personal or small workgroup applications that involve
fewer than 100 users (normally fewer than 15), single user
Dr. Chen, Management Information Systems
43
Access: A DBMS and an Application
Development Product
• Here’s a comparison of an Enterprise DBMS versus a
Personal
BeforeDBMS.
building a database, developers construct a logical

 Personal
Enterprise
DBMSof database data called
representation
a dataDBMS
model to





Smaller,
simpler
applications
Large organizational
and
describe
the data and
relationships to be
stored
in database.
workgroup databases
Hundred to thousands of
users
Many different database
applications
24/7 operations
Oracle, DB2, SQL Server
Dr. Chen, Management Information Systems



Personal or small workgroup
applications
1 - 100 users
Microsoft Access is both a
DBMS and application
development product as this
figure depicts.
Fig 5-15 Personal Database System
44
PART II
Dr. Chen, Management Information Systems
Study Questions
Q1: What is the purpose of a database?
Q2: What is a database?
Q3: What are the components of a database application
system?
Q4: How do database applications make databases more
useful?
Q5: How are data models used for database
development?
Q6: How is a data model transformed into a database design?
Q7: What is the users’ role in the development of databases?
Q8: 2022?
Dr. Chen, Management Information Systems
How to Design a Database?
• Questions:
– What tables to create? (e.g., GU database)
– What attributes should be included in each table?
– How those tables are related to each other?
• Data model: is a conceptual representation of
data classes (data files) and their relationships.
• Relational database: data are organized as
two-dimensional tables called relations, which
are linked together by sharing the same field.
Dr. Chen, Management Information Systems
Q5: How Are Data Models Used for Database Development?
Database Development Process
A data model is similar to blueprints for a house. It’s a
logical representation of database data that describes
data and their relationships.
Fig 5-16 Database Development Process
Dr. Chen, Management Information Systems
48
Steps of Database Development
…
User view-1
User view-2
User view-3
…
User view-N
…
User interview &
Integrated Model
Conceptual Schema (Model)
Logical Model
(ERD or E/ERD)
Implementation
(w/Physical or Internal Model)
Dr. Chen, Management Information Systems
49
How are data models used for database
development?
• Conceptual Design
– The process of preparing an abstract model of the database
from business perspective, which describes the information
needs of end users of the database
– Entity-relationship approach => E-R Diagram (ERD)
– Relational DBMS:
• Transform ERD to tables
• Normalization process (you will learn it later)
• Physical Design
– Describe how data would be physically stored in the data
storage
– Depends on the DBMS
Dr. Chen, Management Information Systems
What Is the Entity-Relationship Data Model?
• Entity-relationship (E-R) data model
Entity is another term for
Table
 File
A tool for constructing data models
Developers use it to describe the content of a data
model by defining entities that will be stored in
database and relationships among those entities
Unified Modeling Language (UML), less
popular, tool for data modeling
Dr. Chen, Management Information Systems
51
Entities (Table or File)
• Some thing that the users want to track
• Examples of entities:
 Order, Customer, Salesperson, and Item. Some entities represent a
physical object, such as Item or Salesperson; others represent a
logical construct or transaction, such as Order or Contract.
 Entity names are always singular.
• Attributes
 Describe characteristics of an entity.
 Examples: order attributes are OrderNumber, OrderDate, SubTotal,
Tax, Total, and so forth.
• Identifier (Key)
 An attribute (or group of attributes) whose value is associated with
one and only one entity instance.
Dr. Chen, Management Information Systems
52
How are data models used for database
development?
• An entity-relationship (ER) data model helps developers define
things (entities) that will be stored in the database as they are
building a data model.
• It also defines relationships among those entities.
• Entities are things users want to track. It can be a physical object
(inventory items) or a logical transaction (sales order). The
names are always singular.
• Each entity has attributes that describe its characteristics. The
entity “Order” has attributes like “OrderNumber” and
“OrderDate”.
• An identifier (pk) is an attribute whose value is associated with
one and only one entity instance. “OrderNumber” is the
identifier for the “Order” entity because there should only be one
number for each order. (see next slide)
Dr. Chen, Management Information Systems
A Data Model on Customer and Order
Segment of an Enterprise Data Model
CUSTOMER
ORDER
M ORDER?
Q1. One CUSTOMER normally places ___
1 CUSTOMER?
Q2. One ORDER normally is placed by __
How about the relationship between ORDER and PRODUCT?
(see next slide)
Dr. Chen, Management Information Systems
ORDER
ORDER_NUMBER (pk)
Customer_ID
Order_Date
Q3. One PRODUCT normally is
contained in M
__ ORDER?
Q4. One ORDER normally contains
__ PRODUCT?
M
Dr. Chen, Management Information Systems
ORDER
ORDER_NUMBER (pk)
Customer_ID
Order_Date
Q3. One PRODUCT normally is
contained in M
__ ORDER?
Q4. One ORDER normally contains
__ PRODUCT?
M
Dr. Chen, Management Information Systems
Comparison of enterprise and project level data models
(a) Segment of an Enterprise Data Model
Q: what are two major differences
between (a) & (b)?
(b) Segment of a Project-Level Data Model
Dr. Chen, Management Information Systems
Student Data Model Entities
Student, Department, Advisor, Email, and Office_Visit are the
entity names in this model.
The entity identifiers are StudentNumber, DeptName, and
AdvisorName. Not all entities require an identifier.


pk
pk
pk?
Email_Number
pk
Dr. Chen, Management Information Systems
pk?
Visit_ID
Fig 5-17 Student Data Model Entities
58
Entities with Relationships
• Relationships join one
entity to another entity
– One-to-one – 1:1
– One-to-many – 1:N
– Many-to-many – N:M
• This diagram shows that
each Department Entity
can have multiple
Adviser Entities in a oneto-many relationship.
Adviser Entities can have
a many-to-many
relationship with Student
Entities.
Dr. Chen, Management Information Systems
M:N or
N:M
1:N
Fig 5-18 Examples of Department Adviser, and
Student Entities and Relationships
59
Basic E-R notation
Entity
symbols
A special
entity that is
also a
relationship
Relationship
degrees
specify number
of entity types
involved
Dr. Chen, Management Information Systems
Attribute
symbols
Relationship
symbols
Relationship
cardinalities specify
how many of each
entity type is
allowed
60
Fig 5-19 Sample Relationship (Version 1)
Crow
’s
Feet
1:N
N:
M
1:N = one-to-many
relationships
N:M = many-to-many
relationships
One department can have
many advisers, but an
adviser has at most one
department.
One adviser can have
many students and one
student can have many
advisers.
Dr. Chen, Management Information Systems
61
Fig 5-20: Sample Relationships (Version 2)
What is the main difference between V1 and V2
Advisers may advise in more than one department,
but a student may have only one adviser,
representing a policy that students may not have
multiple majors.
Dr. Chen, Management Information Systems
62
Crow’s-Foot Diagram Version




Maximum cardinality represents the maximum number of entities
that can be involved in a relationship.
Minimum cardinality represents the minimum number of entities that
can be involved in a relationship.
The vertical bar below indicates that at least one entity is required
between Department and Adviser and between Adviser and Student.
The oval below indicates that an entity is optional and doesn’t
require a relationship.
mandatory
optional
Fig 5-21 Sample Relationships Showing Maximum/Minimum Cardinality
Dr. Chen, Management Information Systems
5-6363
Study Questions
Q1: What is the purpose of a database?
Q2: What is a database?
Q3: What are the components of a database application
system?
Q4: How do database applications make databases more
useful?
Q5: How are data models used for database development?
Q6: How is a data model transformed into a database
design?
Q7: What is the users’ role in the development of databases?
Q8: 2022?
Dr. Chen, Management Information Systems
Database Design
• Database design is the process of converting a data
model into tables, relationships, and data constraints.
• Database design team transforms entities into tables
and expresses relationships by defining foreign keys.
• Two important database design concepts:
normalization and the representation of two kinds
of relationships.
• Normalization is a foundation of database design.
• Representation of relationships will help you
understand important design considerations.
Dr. Chen, Management Information Systems
65
How is a data model transformed into a database design?



Converting a poorly structured table into two or more well-structured
tables is called normalization (Normalization
The table below is poorly designed because it includes the
DeptName as part of the Employee record, making it difficult to
update.
It also creates data integrity
problems because
the DeptName is not
consistent throughout
the table after the
data were updated.
Question: What happen if
“Smith” is laid off and the
record is deleted?
Dr. Chen, Management Information Systems
Fig 5-22: Poorly Designed Employee Table
66
Steps of Database Development
User view-1
User view-2
User view-3
…
…
User view-N
…
Conceptual Schema (Model)
Logical Model
(ERD or E/ERD)
???
Implementation
(w/Physical Model)
Dr. Chen, Management Information Systems
67
Steps of Database Development
User view-1
User view-2
User view-3
…
…
User view-N
…
User interview &
Integrated Model
Conceptual Schema (Model)
Logical Model
(ERD or E/ERD)
NORMALIZATION (up to 3NF)
(more tables
created)
Implementation
(w/Physical Model)
Dr. Chen, Management Information Systems
68
Data Integrity Problems
• In the right figures, some rows show
Dept. 100 is “Accounting and
Finance” and others show Dept. 100
is “Accounting.” Which one is
correct?
• A table with data integrity
problems will produce incorrect
results and inconsistent information.
• Data integrity problems happen
when data are duplicated.
• Users will lose confidence in the
information, and system will
develop a poor reputation.
Information systems with poor
reputations become serious burdens
to the organizations that use them.
Dr. Chen, Management Information Systems
69
Normalizing for Data Integrity
• Normalized tables eliminate data
duplication, but they can be slower to
process.
• General goal of normalization is to
construct tables such that every table has a
single topic or theme so that the database
will be more reliable and stable for a long
period of time without further changes or
redesign.
Dr. Chen, Management Information Systems
70
Normalizing for Data Integrity
• Normalizing the tables by
splitting the Department data
into a separate table, as shown
below, allows each table to
describe a single topic or
theme. The tables have been
transformed into a normal
form.
• By eliminating the duplicate
data, you eliminate data
integrity problems. That’s
called
normalizing for
data integrity.
• Now, the department
name only needs to
be updated in one place
in the normalized tables.
Dr. Chen, Management Information Systems
Fig 5-23: Two
Normalized
Tables
Why it is in a better status?
Question: What happen if “Smith” is laid
off and the record is deleted?
71
Summary of Normalization:
Representing Relationships
• This chart shows the necessary steps to transform a
data model into a relational database design.
will learn more in
bmis441 (for MIS
concentration)
Fig 5-24 Transforming a Data Model into a Database Design
Dr. Chen, Management Information Systems
72
Summary of Normalization
• Database practitioners classify tables into various
normal forms according to the kinds of problems
they have.
• Transforming a table into a normal form to remove
duplicated data and other problems is called
normalizing the table.
• Normalization is just one criterion for evaluating
database designs. Normalized designs can be slower
to process, database designers sometimes choose to
accept non-normalized tables. The best design
depends on the users’ processing requirements.
Dr. Chen, Management Information Systems
73
Steps for Transforming Data Model Into
a Database Design
1. Construct Adviser table and Student
tables with key fields (pk)
2. Represent relationships by adding foreign
keys (fk)
3. Create new table for N:M relationships
(only if the relationship is N:M)
Dr. Chen, Management Information Systems
Q/A
When using the relational model to represent data,
one must ________.
A) add a foreign key to one of the tables
B) have more than ten tables in the model
C) establish only N:M relationships between tables
D) use metadata instead of foreign keys
Answer:
Dr. Chen, Management Information Systems
Transforming Data Model Into a Database Design –
Step 1: Construct Tables with Key fields ‘pk’

The figure on the left represents the relationship (1:N) between
tables. The figure on the right shows a normalized table for
each entity.
pk
pk
1. However, we should know that
AdvserName is not a good pk as it may not
be “unique”.
2. What should be a good one?
Dr. Chen, Management Information Systems
Figure 5-25 (a&b) Representing a 1:N Relationship
Transforming Data Model Into a Database Design Step 2: Adding fk
pk
pk
fk
From Figure 5-25 (c) Representing a 1:N Relationship
Dr. Chen, Management Information Systems
Fig 5-25:
Representing a 1:N
Relationship (in
normalized form)
pk
Step 1.
pk
Transforming a
Data Model into a
Database Design:
1:N Relationship
pk
pk
fk
Step 2.
add
foreign
key (fk)
Dr. Chen, Management Information Systems
78
78
Transforming Data Model Into a Database Design Step 3: Create new table for N:M relationships
The previous
relationship
is 1:N,
therefore, it
does not
need to
create a new
table
Because it is not in
Third Normal
Form (3NF).
How?
Figure 5-26 (a&b) Incorrect Representation of N:M Relationship
Dr. Chen, Management Information Systems
How to solve the M:N problem?
Adviser
Student
AdviserName
Email
StudentNumber
StudentName
MidTerm
Adviser
AdviserName
Email
Adviser_Student
AdviserName
StudentNumber
Student
StudentNumber
StudentName
MidTerm
(intersection entity)
What is the pk in the new Table (Adviser_Student)?
Ans: AdviserName and StudentNumber together (cpk – composite key)
General Rule: One M:N relationship will be broken down into Two 1:M relationships.
Dr. Chen, Management Information Systems
How to solve the M:N problem?
[1]
pk
pk
[2]
pk
pk
[3]
pk
Finally, three tables are produced for the normalized
form.
Therefore, two tables originally in the data model and three
tables are produced after the normalization.
Dr. Chen, Management Information Systems
[1]
How to solve the M:N problem?
The Final Status
pk
pk
cpk (composite key)
[2]
What is the pk?
Dr. Chen, Management Information Systems
[3]
pk
pk
fk
Steps
1&2.
Step 3.
(intersection entity
w/ cpk)
Dr. Chen, Management Information Systems
Fig 5-26: Representing an N:M Relationship
83
Q7: What Is the Users’ Role in the
Development of Databases?
• Users are the final judges of:
 What data the database should contain,
 How tables should be related.
• Users review data model to be sure it accurately
reflects users’ view of the business.
 Mistakes will come back to haunt you.
• Easiest time to change database structure is during
data modeling stage. Changing a relationship from
one-to-many to many-to-many in a data model is
simply a matter of changing the 1:N notation to N:M.
• User review of the data model is crucial.
Dr. Chen, Management Information Systems
84
Q7: What Is the Users’ Role in the
Development of Databases?
• Users are the final judges of:
 What data the database should contain,
 How tables should be related.
• Users review data model to be sure it accurately reflects
users’ view of the business.
 Mistakes will come back to haunt you.
• Easiest time to change database structure is during data
modeling stage. Changing a relationship from one-to-many
to many-to-many in a data model is simply a matter of
changing the 1:N notation to N:M.
• User review of the data model is crucial.
Dr. Chen, Management Information Systems
85
From Data to Knowledge:
How Can Organization Gain Competitive Advantage?
(Survive and Prosper in the Digital Economy)
Data
process
Information
Quality
Information
Available
-As a product
NOT byproduct
Decision
Making
Accessible
context,
experience
D. B.
Organizational
Knowledge
D.B.:
Structured: R-DBMS
Unstructured: Document Mgt. Systems
informate
Dr. Chen, Management Information Systems
Sharable
Collaborative
Reusable
K.B
D.W
automate
Useable
innovate
-As core intellectual capital
NOT merely a few smart employers
External
customers
CRM
Accounting
Finance
Operations
Manufacturing
Q8: 2022?
• Relational databases will look much different
• Use of non-relational data stores, called
NoSQL will be more common
• Major DBMS vendors lose out to open-source
products and shift focus to services supporting
open source software like Bigtable, Dynamo,
Cassandra?
• Database models will be very different
Dr. Chen, Management Information Systems
VIDEO
• Video: Software as a Service overview (2m15s)BMIS235
Dr. Chen, Management Information Systems
88
• End of Chapter 5
Dr. Chen, Management Information Systems
Descargar

Chapter 3 Effects of IT on Strategy and Competition