chapter 12
outline
Databases Overview
and Database What Is a Database?
A Simple Relational Database
Management
Example
Individuals Involved with a Database
Management System
Systems The Evolution of Databases
Advantages and Disadvantages of
the DBMS Approach
After completing this chapter, you will be
able to do the following: Data Concepts and Characteristics
Data Hierarchy
1. Explain what a database is, including com- Entities and Entity Relationships
mon database terminology, and list some Data Definition
of the advantages and disadvantages of The Data Dictionary
using databases. Data Integrity, Security, and Privacy
Data Organization
2. Discuss some basic concepts and character- Database Classifications
istics of data, such as data hierarchy, entity Single-User vs. Multiuser Database
relationships, and data definition. Systems
Client-Server and N-Tier Database
3. Describe the importance of data integrity, Systems
security, and privacy and how they affect Centralized vs. Distributed Database
database design. Systems
Disk-Based vs. In-Memory Database
4. Identify some basic database classifications
Systems
and discuss their differences.
Database Models
5. List the most common database models The Hierarchical and Network
and discuss how they are used today. Database Models
The Relational Database Model
6. Understand how a relational database is The Object-Oriented Database
designed, created, used, and maintained. Model
7. Describe some ways databases are used on Hybrid Database Models
Multidimensional Databases
the Web.
NoSQL Databases
Cloud Databases
Examples of Cloud Databases in Use
How Cloud Databases Work
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
, watcharakun/Shutterstock.com
Ove rvi ew
P eople often need to sort through a large amount of data rapidly to retrieve one
piece of information. To do this quickly and easily, a database is often used.
Databases can be a variety of sizes, from an address book created and used by an
individual, to a company-wide database consisting of customer data used by com-
pany employees, to a product database used in conjunction with an e-commerce site
to enable online shoppers to place real-time orders, to a search engine database
consisting of data about billions of Web pages and accessed by individuals around
the world.
This chapter focuses on databases and the software used to create, maintain, and
use them. The chapter opens with a look at what a database is, the individuals who
use databases, and how databases evolved. We then look at some important data-
base concepts and vocabulary, followed by an explanation of database classifications
and models, with an extended discussion of the most widely used database model:
the relational database. The chapter closes with a discussion of how databases are
used on the Web. ■
What is a Database?
As discussed in Chapter 6, a database is a collection of related data that is stored and
organized in a manner that enables information to be retrieved as needed. Database soft-
ware—more formally called a database management system (DBMS)—is used to create,
maintain, and access a database. A DBMS also controls the organization of the data and
protects the integrity and security of the data so it is entered accurately into the database
SYS
and then protected against both intentional and accidental damage. While batch processing
can be used with databases, most database applications today occur in real time.
A key component of a DBMS is the database engine—the part of the program that
actually stores and retrieves data. In addition to a database engine, most DBMSs come
bundled with a set of tools to perform a variety of necessary tasks, such as creating forms
(used to input data) and reports (used to output data), and interfacing with query languages
and programming languages for complex applications. Programming languages typically
used with databases today include Python, Java, and C#, although some older legacy data-
base systems still use COBOL.
A database typically consists of interrelated tables that contain fields and records.
As discussed in Chapter 6, a field (also called a column) holds a single category of data
(such as customer names or employee phone numbers) that will be stored in a database.
A record (also called a row) is a collection of related fields. The technical difference
between the terms row and record in database terminology is that a row is contained
within a single database table, but a record is a collection of fields, which can be either
>Database. A collection of related data that is stored in a manner enabling information to be retrieved as needed; in a relational database, a collection
of related tables. >Database management system (DBMS). A type of software program used to create, maintain, and access databases. >Table.
In a relational database, a collection of related records. >Field. A single category of data to be stored in a database, such as a person’s last name or
phone number; also called a column. >Record. A collection of related fields in a database; also called a row.
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
,480 systems
a specific row from a single table or a collection of related fields from multiple tables,
such as the records shown in the results of the Order Request screen in Figure 12-1 and
discussed next. However, in this chapter, as in common usage, the two terms are used
interchangeably.
To illustrate these concepts, a simplified example of a possible relational database—
the type of database most widely used at the present time—is shown in Figure 12-1 and
discussed next.
A Simple Relational Database Example
Figure 12-1 illustrates an inventory system for a ski equipment retailer. The tables shown
in this figure contain data related to the retailer’s products: the Product table (for product
descriptions and selling prices), the Inventory table (for current stock levels of products),
and the Inventory on Order table (for future shipments of products that have been ordered
from suppliers). Each table consists of several fields (columns) and records (rows). The
Product table, for example, contains four fields—Product Number, Product Name, Supplier,
and Price—and five records—one each for Skis, Boots, Poles, Bindings, and Wax. Each
record in the Product table contains data for each of the four fields. The Inventory table
contains three fields and five records, and the Inventory on Order table contains four fields
and six records. To keep this example simple, the tables containing data about customers
and their orders (Customer table and Order table, respectively) are not shown and the skis,
boots, and poles are not itemized by size. Real-world databases typically consist of many
more tables than are used in this example, each containing thousands of records.
Figure 12-1 For this example, imagine that you are the sales manager of this company and a customer
<
Using a relational calls on the phone and wants to order 160 pairs of ski boots. First, you need to find out if the
database in an
inventory system.
Fields (columns)
Product Product Supplier Price Product Current On Order?
Number Name Number Stock
A202
A202 Skis Ellis Ski Co. 90.00 A202 15 Yes
A211
A211 Boots Ajax Bros. 60.00 A211 90 Yes
Records A220
A220 Poles Bent Corp. 25.00 A220 30 Yes
(rows)
A240
A240 Bindings Acme Corp. 35.00 A240 25 Yes
A351
A351 Wax Candle Industries 3.00 A351 8 No
PRODUCT TABLE INVENTORY TABLE
RELATING DATA
Data in various tables can be pulled QUERY
together by their common field When the user supplies a
(Product Number). product number and order
size, all other relevant
Order Product Shipment Quantity information needed to
Number Number Date complete an Order Request
1001 A202 1/8 30 screen is gathered and
displayed automatically.
1002 A240 1/8 15
Igor Shikov/Shutterstock.com
1003 A211 1/9 50
1004 A202 1/9 40
1005 A220 1/10 35
1006 A211 1/12 60
INVENTORY ON ORDER TABLE ORDER REQUEST SCREEN
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
, chapter 12 Databases and Database Management Systems 481
order can be filled from stock currently in inventory. If it cannot, you need to know how long
it will be before enough stock is available to fill the order. Using your computer, you execute
the appropriate command to display an Order Request screen and enter the product number
“A211” and the order size “160” as shown in Figure 12-1. The DBMS then displays the name
of the product (Boots, in this example), the current level of stock in inventory (90), and as many
upcoming shipments of this product (50 and 60) as needed to fill the order. It also provides an
estimate as to when the order can be filled: January 12. Within seconds, right in front of you,
you have the information you need to respond to the customer’s request and close the order.
In a relational database, data from several tables is tied together (related) using fields
that the tables have in common so that information can be extracted from multiple tables as
needed. For instance, in Figure 12-1, data from all three tables was pulled together through
a common Product Number field (the green shaded columns) to complete the Order Request
screen shown in that figure. Specifically, the Product table was first accessed to locate and dis- Ti p
play the product name; next, the Inventory table was used to determine and display the num- Primary keys can be a combination
ber of boots currently in stock; and finally, the Inventory on Order table was used to look up of two key fields (called composite
and display the quantities and delivery dates for as many incoming shipments as needed to get primary keys), but single primary
a total of at least 160 boots. The end user is typically not aware that multiple tables are being keys are more typical in simple
used or of the relationships between tables; instead the user knows only that the information is databases; only single primary keys
“somewhere in the database system” and it is retrieved and displayed when requested. are used in the examples in this
The field in a table that is used to relate that table to other tables is called the chapter.
primary key. A primary key must uniquely identify each record in that table, which means
that no two records within a table can have the same value in the primary key
field. To ensure the uniqueness of the primary key, it usually consists of an
identifying number, such as a student ID number, customer number, or product The yellow fields will The blue fields will not
always contain unique always contain unique data
number. For example, in Figure 12-1, the Product Number field is the primary data and so are good and so are not good to use
key field for the Product and Inventory tables and the Order Number field is the to use as primary keys. as primary keys.
primary key field for the Inventory on Order table. When selecting a primary
key field, it is important to pick a field that contains unique data that is not Product Number Product Name Supplier Price
likely to change. Consequently, fields containing names, phone numbers, and
addresses are poor choices for a primary key (see Figure 12-2). Although phone Student ID Number Student Name Address Phone
numbers and complete addresses are unique, they may change and will not nec-
essarily always be associated with the original individual. Customer Number Address Phone Balance
While most complex database systems will have multiple interrelated
tables, you can create a database that does not interrelate tables, or you can cre- Figure 12-2
SYS
<
ate a database that has only a single table, if that’s appropriate for your application. DBMSs Primary key fields.
available for use on personal computers include Microsoft Access, OpenOffice Base, and A primary key field
Corel Paradox (part of the Microsoft Office, Apache OpenOffice, and WordPerfect Office must contain unique
software suites, respectively). Of these, Access is the most widely used. For more compre- data so it can be used
hensive enterprise databases, Oracle Database, IBM DB2, Microsoft SQL Server, MySQL, to identify each record
or another more robust DBMS may be used instead. in the table.
Individuals Involved with a Database Management System
To be effective, data in a DBMS must be initially entered into the database, updated as
necessary to stay current, and then retrieved in the form of information. Consequently, vir-
tually all DBMSs include a user interface for easy data input, modification, and retrieval.
There must also be a way for the database to be backed up and protected against unau-
thorized access. The individuals most often involved with creating, using, managing, and
protecting a DBMS are described next.
>Primary key. A specific field in a database table that uniquely identifies the records in that table.
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
outline
Databases Overview
and Database What Is a Database?
A Simple Relational Database
Management
Example
Individuals Involved with a Database
Management System
Systems The Evolution of Databases
Advantages and Disadvantages of
the DBMS Approach
After completing this chapter, you will be
able to do the following: Data Concepts and Characteristics
Data Hierarchy
1. Explain what a database is, including com- Entities and Entity Relationships
mon database terminology, and list some Data Definition
of the advantages and disadvantages of The Data Dictionary
using databases. Data Integrity, Security, and Privacy
Data Organization
2. Discuss some basic concepts and character- Database Classifications
istics of data, such as data hierarchy, entity Single-User vs. Multiuser Database
relationships, and data definition. Systems
Client-Server and N-Tier Database
3. Describe the importance of data integrity, Systems
security, and privacy and how they affect Centralized vs. Distributed Database
database design. Systems
Disk-Based vs. In-Memory Database
4. Identify some basic database classifications
Systems
and discuss their differences.
Database Models
5. List the most common database models The Hierarchical and Network
and discuss how they are used today. Database Models
The Relational Database Model
6. Understand how a relational database is The Object-Oriented Database
designed, created, used, and maintained. Model
7. Describe some ways databases are used on Hybrid Database Models
Multidimensional Databases
the Web.
NoSQL Databases
Cloud Databases
Examples of Cloud Databases in Use
How Cloud Databases Work
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
, watcharakun/Shutterstock.com
Ove rvi ew
P eople often need to sort through a large amount of data rapidly to retrieve one
piece of information. To do this quickly and easily, a database is often used.
Databases can be a variety of sizes, from an address book created and used by an
individual, to a company-wide database consisting of customer data used by com-
pany employees, to a product database used in conjunction with an e-commerce site
to enable online shoppers to place real-time orders, to a search engine database
consisting of data about billions of Web pages and accessed by individuals around
the world.
This chapter focuses on databases and the software used to create, maintain, and
use them. The chapter opens with a look at what a database is, the individuals who
use databases, and how databases evolved. We then look at some important data-
base concepts and vocabulary, followed by an explanation of database classifications
and models, with an extended discussion of the most widely used database model:
the relational database. The chapter closes with a discussion of how databases are
used on the Web. ■
What is a Database?
As discussed in Chapter 6, a database is a collection of related data that is stored and
organized in a manner that enables information to be retrieved as needed. Database soft-
ware—more formally called a database management system (DBMS)—is used to create,
maintain, and access a database. A DBMS also controls the organization of the data and
protects the integrity and security of the data so it is entered accurately into the database
SYS
and then protected against both intentional and accidental damage. While batch processing
can be used with databases, most database applications today occur in real time.
A key component of a DBMS is the database engine—the part of the program that
actually stores and retrieves data. In addition to a database engine, most DBMSs come
bundled with a set of tools to perform a variety of necessary tasks, such as creating forms
(used to input data) and reports (used to output data), and interfacing with query languages
and programming languages for complex applications. Programming languages typically
used with databases today include Python, Java, and C#, although some older legacy data-
base systems still use COBOL.
A database typically consists of interrelated tables that contain fields and records.
As discussed in Chapter 6, a field (also called a column) holds a single category of data
(such as customer names or employee phone numbers) that will be stored in a database.
A record (also called a row) is a collection of related fields. The technical difference
between the terms row and record in database terminology is that a row is contained
within a single database table, but a record is a collection of fields, which can be either
>Database. A collection of related data that is stored in a manner enabling information to be retrieved as needed; in a relational database, a collection
of related tables. >Database management system (DBMS). A type of software program used to create, maintain, and access databases. >Table.
In a relational database, a collection of related records. >Field. A single category of data to be stored in a database, such as a person’s last name or
phone number; also called a column. >Record. A collection of related fields in a database; also called a row.
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
,480 systems
a specific row from a single table or a collection of related fields from multiple tables,
such as the records shown in the results of the Order Request screen in Figure 12-1 and
discussed next. However, in this chapter, as in common usage, the two terms are used
interchangeably.
To illustrate these concepts, a simplified example of a possible relational database—
the type of database most widely used at the present time—is shown in Figure 12-1 and
discussed next.
A Simple Relational Database Example
Figure 12-1 illustrates an inventory system for a ski equipment retailer. The tables shown
in this figure contain data related to the retailer’s products: the Product table (for product
descriptions and selling prices), the Inventory table (for current stock levels of products),
and the Inventory on Order table (for future shipments of products that have been ordered
from suppliers). Each table consists of several fields (columns) and records (rows). The
Product table, for example, contains four fields—Product Number, Product Name, Supplier,
and Price—and five records—one each for Skis, Boots, Poles, Bindings, and Wax. Each
record in the Product table contains data for each of the four fields. The Inventory table
contains three fields and five records, and the Inventory on Order table contains four fields
and six records. To keep this example simple, the tables containing data about customers
and their orders (Customer table and Order table, respectively) are not shown and the skis,
boots, and poles are not itemized by size. Real-world databases typically consist of many
more tables than are used in this example, each containing thousands of records.
Figure 12-1 For this example, imagine that you are the sales manager of this company and a customer
<
Using a relational calls on the phone and wants to order 160 pairs of ski boots. First, you need to find out if the
database in an
inventory system.
Fields (columns)
Product Product Supplier Price Product Current On Order?
Number Name Number Stock
A202
A202 Skis Ellis Ski Co. 90.00 A202 15 Yes
A211
A211 Boots Ajax Bros. 60.00 A211 90 Yes
Records A220
A220 Poles Bent Corp. 25.00 A220 30 Yes
(rows)
A240
A240 Bindings Acme Corp. 35.00 A240 25 Yes
A351
A351 Wax Candle Industries 3.00 A351 8 No
PRODUCT TABLE INVENTORY TABLE
RELATING DATA
Data in various tables can be pulled QUERY
together by their common field When the user supplies a
(Product Number). product number and order
size, all other relevant
Order Product Shipment Quantity information needed to
Number Number Date complete an Order Request
1001 A202 1/8 30 screen is gathered and
displayed automatically.
1002 A240 1/8 15
Igor Shikov/Shutterstock.com
1003 A211 1/9 50
1004 A202 1/9 40
1005 A220 1/10 35
1006 A211 1/12 60
INVENTORY ON ORDER TABLE ORDER REQUEST SCREEN
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
, chapter 12 Databases and Database Management Systems 481
order can be filled from stock currently in inventory. If it cannot, you need to know how long
it will be before enough stock is available to fill the order. Using your computer, you execute
the appropriate command to display an Order Request screen and enter the product number
“A211” and the order size “160” as shown in Figure 12-1. The DBMS then displays the name
of the product (Boots, in this example), the current level of stock in inventory (90), and as many
upcoming shipments of this product (50 and 60) as needed to fill the order. It also provides an
estimate as to when the order can be filled: January 12. Within seconds, right in front of you,
you have the information you need to respond to the customer’s request and close the order.
In a relational database, data from several tables is tied together (related) using fields
that the tables have in common so that information can be extracted from multiple tables as
needed. For instance, in Figure 12-1, data from all three tables was pulled together through
a common Product Number field (the green shaded columns) to complete the Order Request
screen shown in that figure. Specifically, the Product table was first accessed to locate and dis- Ti p
play the product name; next, the Inventory table was used to determine and display the num- Primary keys can be a combination
ber of boots currently in stock; and finally, the Inventory on Order table was used to look up of two key fields (called composite
and display the quantities and delivery dates for as many incoming shipments as needed to get primary keys), but single primary
a total of at least 160 boots. The end user is typically not aware that multiple tables are being keys are more typical in simple
used or of the relationships between tables; instead the user knows only that the information is databases; only single primary keys
“somewhere in the database system” and it is retrieved and displayed when requested. are used in the examples in this
The field in a table that is used to relate that table to other tables is called the chapter.
primary key. A primary key must uniquely identify each record in that table, which means
that no two records within a table can have the same value in the primary key
field. To ensure the uniqueness of the primary key, it usually consists of an
identifying number, such as a student ID number, customer number, or product The yellow fields will The blue fields will not
always contain unique always contain unique data
number. For example, in Figure 12-1, the Product Number field is the primary data and so are good and so are not good to use
key field for the Product and Inventory tables and the Order Number field is the to use as primary keys. as primary keys.
primary key field for the Inventory on Order table. When selecting a primary
key field, it is important to pick a field that contains unique data that is not Product Number Product Name Supplier Price
likely to change. Consequently, fields containing names, phone numbers, and
addresses are poor choices for a primary key (see Figure 12-2). Although phone Student ID Number Student Name Address Phone
numbers and complete addresses are unique, they may change and will not nec-
essarily always be associated with the original individual. Customer Number Address Phone Balance
While most complex database systems will have multiple interrelated
tables, you can create a database that does not interrelate tables, or you can cre- Figure 12-2
SYS
<
ate a database that has only a single table, if that’s appropriate for your application. DBMSs Primary key fields.
available for use on personal computers include Microsoft Access, OpenOffice Base, and A primary key field
Corel Paradox (part of the Microsoft Office, Apache OpenOffice, and WordPerfect Office must contain unique
software suites, respectively). Of these, Access is the most widely used. For more compre- data so it can be used
hensive enterprise databases, Oracle Database, IBM DB2, Microsoft SQL Server, MySQL, to identify each record
or another more robust DBMS may be used instead. in the table.
Individuals Involved with a Database Management System
To be effective, data in a DBMS must be initially entered into the database, updated as
necessary to stay current, and then retrieved in the form of information. Consequently, vir-
tually all DBMSs include a user interface for easy data input, modification, and retrieval.
There must also be a way for the database to be backed up and protected against unau-
thorized access. The individuals most often involved with creating, using, managing, and
protecting a DBMS are described next.
>Primary key. A specific field in a database table that uniquely identifies the records in that table.
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
