Relational Databases

Chapter 4

Copyright © 2015 Pearson Education, Inc.

4-1
 Learning Objectives
• Explain the importance and advantages of databases, as well as the
difference between database and file-based legacy systems.

• Explain the difference between logical and physical views of a

database.

• Explain fundamental concepts of database systems such as DBMS,

schemas, the data dictionary, and DBMS languages.

• Describe what a relational database is and how it organizes data.

• Create a set of well-structured tables to properly store data in a

relational database.

• Perform simple queries using the Microsoft Access database.

Copyright © 2015 Pearson Education, Inc.

4-2
 S&S
S&S is very successful and operates five stores and a popular website.
Ashton Fleming believes that it is time to upgrade S&S’s accounting
information system (AIS) so that Susan and Scott can easily access the
information they need to run their business. Most new AISs are based on a
relational database. Since Ashton knows that Scott and Susan are likely to
have questions, he prepared a brief report that explains why S&S’s new
AIS should be a relational database system. His report addresses the
following questions:
1. What is a database system, and how does it differ from file-oriented
systems?
2. What is a relational database system?
3. How do you design a well-structured set of tables in a relational
database?
4. How do you query a relational database system?

Copyright © 2015 Pearson Education, Inc.

 What Is a Database?
• Efficiently and centrally coordinates information for a
related group of files
• A file is a related group of records
• A record is a related group of fields
• A field is a specific attribute of
interest for the entity (record)

Copyright © 2015 Pearson Education, Inc.

4-3
 Fact
Databases were developed to address the proliferation of
master files. For many years, companies created new files and
programs each time a need for information arose. Bank of
America once had 36 million customer accounts in 23 separate
systems. This proliferation created problems such as storing
the same data in two or more master files.
This made it difficult to integrate and update data and to
obtain an organizationwide view of data. It also created
problems because the data in the different files were
inconsistent. For example, a customer’s address may have been
correctly updated in the shipping master file but not the billing
master file.
Copyright © 2015 Pearson Education, Inc.
Copyright © 2015 Pearson Education, Inc.
 Solution
• In the database approach, data is an
organizational resource that is used by and
managed for the entire organization, not just the
originating department.
• (DBMS) is the program that manages and
controls the data and the interfaces between the
data and the application programs that use the
data stored in the database
• Database Administrator (DBA) is
responsible for coordinating, controlling, and
managing the database.
Copyright © 2015 Pearson Education, Inc.
 Data Warehouse
• Data warehouse is one or more very large databases
containing both detailed and summarized data for a number
of years that is used for analysis rather than transaction
processing.
• Data warehouses do not replace transaction processing
databases; they complement them by providing support for
strategic decision making. Since data warehouses are not
used for transaction processing, they are usually updated
periodically rather than in real time.
• Analyzing large amounts of data for strategic decision
making is often referred to as business intelligence. There
are two main techniques used in business intelligence: online
analytical processing (OLAP) and data mining.
Copyright © 2015 Pearson Education, Inc.
Copyright © 2015 Pearson Education, Inc.
Copyright © 2015 Pearson Education, Inc.
 Fact !!!
Bank of America created a customer information
database to provide customer service, marketing analysis, and
managerial information. It was the largest in the banking
industry, with over 600 billion characters of data. It contained
all bank data on checking and savings accounts; real estate,
consumer, and commercial loans; ATMs; and bankcards.

Copyright © 2015 Pearson Education, Inc.

 Advantages of Databases
• Data is integrated and easy to share
• Minimize data redundancy
• Data is independent of the programs that use the
data
• Data is easily accessed for reporting and cross-
functional analysis

Copyright © 2015 Pearson Education, Inc.

4-4
 The Importance of Good Data
• A company sent half its mail-order catalogs to incorrect
addresses. A manager finally investigated the large
volume of returns and customer complaints. Correcting
customer address in the database saved the company $12
million a year.
• The Data Warehousing Institute estimates that bad data
cost businesses over $600 billion a year in unnecessary
postage, marketing costs, and lost customer credibility.
It is estimated that over 25% of business data is
inaccurate or incomplete. In a recent survey, 53% of 750
information technology (IT) professionals said their
companies experienced problems due to poor-quality
data.
Copyright © 2015 Pearson Education, Inc.
 Database Users and Designers
• Different users of the database information are
at an external level of the database. These users
have logical views of the data.
• At an internal level of the database is the
physical view of the data which is how the data
is actually physically stored in the system.
• Designers of a database need to understand
user’s needs and the conceptual level of the
entire database as well as the physical view.

Copyright © 2015 Pearson Education, Inc.

4-5
 Database Design
• To design a database, you need to have a
conceptual view of the entire database. The
conceptual view illustrates the different files and
relationships between the files.

• The data dictionary is a “blueprint” of the

structure of the database and includes data
elements, field types, programs that use the data
element, outputs, and so on.

Copyright © 2015 Pearson Education, Inc.

4-6
Copyright © 2015 Pearson Education, Inc.
Copyright © 2015 Pearson Education, Inc.
 DBMS Languages
• Data Definition Language (DDL)
▫ Builds the data dictionary
▫ Creates the database
▫ Describes logical views for each user
▫ Specifies record or field security constraints
• Data Manipulation Language (DML)
▫ Changes the content in the database
 Creates, updates, insertions, and deletions
• Data Query Language (DQL)
▫ Enables users to retrieve, sort, and display specific
data from the database

Copyright © 2015 Pearson Education, Inc.

4-7
 Relational Database

• Represents the conceptual and external schema

as if that “data view” were truly stored in one
table.
• Although the conceptual view appears to the
user that this information is in one big table, it
really is a set of tables that relate to one another.

Copyright © 2015 Pearson Education, Inc.

4-8
 Conceptual View Example

Customer Name Sales Invoice # Invoice Total

D. Ainge 101 $1,447
G. Kite 102 $4,394
D. Ainge 103 $ 898
G. Kite 104 $ 789
F. Roberts 105 $3,994

Copyright © 2015 Pearson Education, Inc.

4-9
 Relational Data Tables

Copyright © 2015 Pearson Education, Inc.

4-10
 Relational Data Tables

Primary Keys

Foreign Key (Customer # is a Foreign

key in the Sales Table because it is a
Primary key that uniquely identifies
Customers in the Customer Table).
Because of this, the Sales Table can relate
to the Customer Table (see red arrow
above).
Copyright © 2015 Pearson Education, Inc.
4-11
 Why Have a Set of Related Tables?
• Data stored in one large table can be redundant
and inefficient causing the following problems:
▫ Update anomaly
▫ Insert anomaly
▫ Delete anomaly

Copyright © 2015 Pearson Education, Inc.

4-12
Relational Database Design Rules

• Every column in a row must be single valued

• Primary key cannot be null (empty) also known as entity integrity
• IF a foreign key is not null, it must have a value that corresponds to
the value of a primary key in another table (referential integrity)
• All other attributes in the table must describe characteristics of the
object identified by the primary key

Following these rules allows databases to be normalized and solves the

update, insert, and delete anomalies.
Copyright © 2015 Pearson Education, Inc.
4-13
 Queries

• Users may want specific information found in a

relational database and not have to sort through
all the files to get that information. So they query
(ask a question) the data.
• An example of a query might be: What are the
invoices of customer D. Ainge and who was the
salesperson for those invoices?

Copyright © 2015 Pearson Education, Inc.

4-14
 4-16

Creating the Query

Copyright © 2015 Pearson Education, Inc.

4-15
 Query Answer

Copyright © 2015 Pearson Education, Inc.

4-16
 Key Terms
• Database • External-level schema
• Database management system • Subschema
(DBMS) • Internal-level schema
• Database system • Data dictionary
• Database administrator (DBA) • Data definition language
• Data warehouse (DDL)
• Business intelligence • Data manipulation language
• Online analytical processing (DML)
(OLAP) • Data query language (DQL)
• Data mining • Report writer
• Record layout • Data model
• Logical view • Relational data model
• Physical view • Tuple
• Schema • Primary key
• Conceptual-level schema • Foreign key
Copyright © 2015 Pearson Education, Inc.
4-17
 Key Terms (continued)
• Update anomaly
• Insert anomaly
• Delete anomaly
• Relational database
• Entity integrity rule
• Referential integrity rule
• Normalization
• Semantic data modeling

Copyright © 2015 Pearson Education, Inc.

4-18