View the step-by-step solution to: Confirming Pages CHAPTER 7 Storing

Case Study Assignment 3: The Entity Rela...
This question was answered on Oct 17, 2013. View the Answer
Case Study Assignment 3: The Entity Relationship Diagram
The goal of this exercise is for you to begin to understand how the tables in a database relate. For this assignment, you will need to create an entity relationship diagram (ERD) similar to the one in Figure 7.1 in your textbook.

Consider the data tables you will need in order to bring the Broadway Cafe into the 21st century. You know you have customers and orders and inventory (you will need to think about more things you will need - be creative!). Each of them may require numerous tables (as per the examples given).

Think about the fields needed in each table. For example, your customer database needs:
◦names
◦addresses
◦a customer ID
◦customer status (active, inactive, preferred, etc)
◦what else?

How about your orders table? What else might you need? Your ERD must clearly label primary keys and foreign keys, and show their relationships so that they are easy to follow.

I recommend creating these tables in Word or Excel or PPT, and then uploading the finished product when done by clicking BROWSE, below, and then submitting your work.

You will be graded on the quality and completeness of your submission

please look at the attached file ( Figure 7.1 ) as he said .
baL76841_ch07_086-097.pdf

Confirming Pages

CHAPTER

7

Storing Organizational
InformationDatabases

L EAR N IN G OUTC OMES

7.1.

Define the fundamental concepts of the relational
database model.
Evaluate the advantages of the relational database
model.

7.2.

LO 7.1 Define the fundamental
concepts of the relational database model.

7.3.
7.4.

Define a database management system and its
relationship to a website.
Explain why an organization would want to integrate its databases.

Storing Organizational Information
Organizational information is stored in a database. Applications and programs, such as
supply chain management systems and customer relationship management systems,
access the data in the database so the program can consult it to answer queries. The
records retrieved in answer to questions become information that can be used to make
decisions. The computer program used to manage and query a database is known as a
database management system (DBMS). The properties and design of database systems
are included in the study of information science.
The central concept of a database is that of a collection of records, or pieces of information. Typically, a given database has a structural description of the type of facts
held in that database: This description is known as a schema. The schema describes
the objects that are represented in the database and the relationships among them.
There are a number of different ways of organizing a schema, that is, of modeling the
database structure: These are known as database models (or data models). The most
commonly used model today is the relational model, which represents all information in the form of multiple related tables each consisting of rows and columns. This
model represents relationships by the use of values common to more than one table.
Other models, such as the hierarchical model, and the network model, use a more
explicit representation of relationships.
Many professionals consider a collection of data to constitute a database only if it has
certain properties, for example, if the data are managed to ensure integrity and quality,
if it allows shared access by a community of users, if it has a schema, or if it supports a
query language. However, there is no definition of these properties that is universally
agreed upon.

Relational Database Fundamentals
There are many different models for organizing information in a database, including the
hierarchical database, network database, and the most prevalentthe relational database model. Broadly defined, a database maintains information about various types
of objects (inventory), events (transactions), people (employees), and places (warehouses). In a hierarchical database model, information is organized into a tree-like

86

*

Unit 2 Exploring Business Intelligence

baL76841_ch07_086-097.indd
baL76841_ch07_086-097.indd 86

10/6/11 5:24 PM

Confirming Pages

structure that allows repeating information using parent/child relationships in such
a way that it cannot have too many relationships. Hierarchical structures were widely
used in the first mainframe database management systems. However, owing to their
restrictions, hierarchical structures often cannot be used to relate to structures that
exist in the real world. The network database model is a flexible way of representing
objects and their relationships. Where the hierarchical model structures data as a tree
of records, with each record having one parent record and many children, the network
model allows each record to have multiple parent and child records, forming a lattice
structure. The relational database model is a type of database that stores information
in the form of logically related two-dimensional tables. This text focuses on the relational database model.
Consider how the Coca-Cola Bottling Company of Egypt (TCCBCE) implemented an
inventory-tracking database to improve order accuracy by 27 percent, decrease order
response time by 66 percent, and increase sales by 20 percent. With more than 7,400
employees, TCCBCE owns and operates 11 bottling plants and 29 sales and distribution
centers, making it one of the largest companies in Egypt.
Traditionally, the company sent distribution trucks to each customers premises to
take orders and deliver stock. Many problems were associated with this process including numerous information entry errors, which caused order-fulfillment time to take an
average of three days. To remedy the situation, Coca-Cola decided to create presales
teams equipped with handheld devices to visit customers and take orders electronically.
On returning to the office, the teams synchronized orders with the companys inventorytracking database to ensure automated processing and rapid dispatch of accurate orders
to customers.

E NTITIES AND ATTRIBUTES
Figure 7.1 illustrates the primary concepts of the relational database modelentities,
entity classes, attributes, keys, and relationships. An entity in the relational database
model is a person, place, thing, transaction, or event about which information is stored.
A table in the relational database model is a collection of similar entities. The tables of
interest in Figure 7.1 are CUSTOMER, ORDER, ORDER LINE, PRODUCT, and DISTRIBUTOR. Notice that each entity class (the collection of similar entities) is stored in a different two-dimensional table. Attributes, also called fields or columns, are characteristics
or properties of an entity class. In Figure 7.1 the attributes for CUSTOMER include Customer ID, Customer Name, Contact Name, and Phone. Attributes for PRODUCT include
Product ID, Product Description, and Price. Each specific entity in an entity class (e.g.,
Daves Sub Shop in the CUSTOMER table) occupies one row in its respective table. The
columns in the table contain the attributes.

KEYS AND RELATIONSHIPS
To manage and organize various entity classes within the relational database model,
developers must identify primary keys and foreign keys and use them to create logical
relationships. A primary key is a field (or group of fields) that uniquely identifies a given
entity in a table. In CUSTOMER, the Customer ID uniquely identifies each entity (customer) in the table and is the primary key. Primary keys are important because they provide a way of distinguishing each entity in a table.
A foreign key in the relational database model is a primary key of one table that
appears as an attribute in another table and acts to provide a logical relationship
between the two tables. Consider Hawkins Shipping, one of the distributors appearing in the DISTRIBUTOR table. Its primary key, Distributor ID, is DEN8001. Notice that
Distributor ID also appears as an attribute in the ORDER table. This establishes the fact
that Hawkins Shipping (Distributor ID DEN8001) was responsible for delivering orders
34561 and 34562 to the appropriate customer(s). Therefore, Distributor ID in the ORDER
table creates a logical relationship (who shipped what order) between ORDER and
DISTRIBUTOR.

Chapter 7 Storing Organizational InformationDatabases

baL76841_ch07_086-097.indd
baL76841_ch07_086-097.indd 87

*

87

10/6/11 5:24 PM

Confirming Pages

FIGURE 7.1
Order Number: 34562

Potential Relational
Database for Coca-Cola
Bottling Company of Egypt
(TCCBCE)

Coca-Cola Bottling Company of Egypt
Sample Sales Order
Customer:

Date:

Daves Sub Shop

Quantity
100

8/6/2008

Product
Vanilla Coke

Price
$0.55

Amount
$55

Distributor Fee $12.95
Order Total

$67.95

CUSTOMER
Customer ID

Customer Name

Contact Name

Phone

23

Daves Sub Shop

David Logan

(555)333-4545

43

Pizza Palace

Debbie Fernandez

(555)345-5432

765

Ts Fun Zone

Tom Repicci

(555)565-6655

ORDER
Order ID

Order Date

Customer ID

Distributor ID

Distributor Fee

Total Due

34561

7/4/2008

23

DEN8001

$22.00

$145.75

34562

8/6/2008

23

DEN8001

$12.95

$67.95

34563

6/5/2008

765

NY9001

$29.50

$249.50

ORDER LINE
Order ID

Line Item

Product ID

34561

1

34561

2

34561

DISTRIBUTOR
Quantity

Distributor ID

Distributor Name

12345AA

75

DEN8001

Hawkins Shipping

12346BB

50

CHI3001

ABC Trucking

3

12347CC

100

NY9001

Van Distributors

34562

1

12349EE

100

34563

1

12345AA

100

34563

2

12346BB

100

34563

3

12347CC

50

34563

4

12348DD

50

34563

5

12349EE

100

PRODUCT
Product ID

Product Description

Price

12345AA

Diet Coke

$0.55

12347CC

Sprite

$0.55

12348DD

Diet Sprite

$0.55

12349EE

*

$0.55

12346BB

88

Coca-Cola

Vanilla Coke

$0.55

Unit 2 Exploring Business Intelligence

baL76841_ch07_086-097.indd 88

10/6/11 5:24 PM

Confirming Pages

Relational Database Advantages
From a business perspective, database information offers many advantages, including:


of the relational database model.

Increased flexibility.



LO 7.2 Evaluate the advantages

Increased scalability and performance.
Reduced information redundancy.
Increased information integrity (quality).
Increased information security.





I NCREASED FLEXIBILITY
Databases tend to mirror business structures, and a good database can handle changes
quickly and easily, just as any good business needs to be able to handle changes quickly
and easily. Equally important, databases provide flexibility in allowing each user to
access the information in whatever way best suits his or her needs. The distinction
between logical and physical views is important in understanding flexible database user
views. The physical view of information deals with the physical storage of information
on a storage device such as a hard disk. The logical view of information focuses on how
users logically access information to meet their particular business needs. This separation of logical and physical views is what allows each user to access database information differently. That is, while a database has only one physical view, it can easily
support multiple logical views. In the previous database illustration, for example, users
could perform a query to determine which distributors delivered shipments to Pizza
Palace last week. At the same time, another person could perform some sort of statistical analysis to determine the frequency at which Sprite and Diet Coke appear on the
same order. These represent two very different logical views, but both views use the
same physical view.
Consider another examplea mail-order business. One user might want a CRM
report presented in alphabetical format, in which case last name should appear before
first name. Another user, working with a catalog mailing system, would want customer
names appearing as first name and then last name. Both are easily achievable, but different logical views of the same physical information.

I NCREASED SCALABILITY AND PERFORMANCE
The official website of The American Family Immigration History Center, www.ellisisland
.org, generated more than 2.5 billion hits in its first year of operation. The site offers easy
access to immigration information about people who entered America through the Port
of New York and Ellis Island between 1892 and 1924. The database contains more than
25 million passenger names correlated to 3.5 million images of ships manifests.
Only a database could scale to handle the massive volumes of information and
the large numbers of users required for the successful launch of the Ellis Island website. Scalability refers to how well a system can adapt to increased demands. Performance measures how quickly a system performs a certain process or transaction. Some
organizations must be able to support hundreds or thousands of online users including
employees, partners, customers, and suppliers, who all want to access and share information. Databases today scale to exceptional levels, allowing all types of users and programs to perform information-processing and information-searching tasks.

R EDUCED INFORMATION REDUNDANCY
Redundancy is the duplication of information, or storing the same information in multiple places. Redundant information occurs because organizations frequently capture
and store the same information in multiple locations. The primary problem with redundant information is that it is often inconsistent, which makes it difficult to determine

Chapter 7 Storing Organizational InformationDatabases

baL76841_ch07_086-097.indd 89

*

89

10/6/11 5:24 PM

Confirming Pages

which values are the most current or most accurate. Not having correct information is
confusing and frustrating for employees and disruptive to an organization. One primary goal of a database is to eliminate information redundancy by recording each
piece of information in only one place in the database. Eliminating information redundancy saves space, makes performing information updates easier, and improves information quality.

I NCREASED INFORMATION INTEGRITY (QUALITY)
Information integrity is a measure of the quality of information. Within a database
environment, integrity constraints are rules that help ensure the quality of information. Integrity constraints can be defined and built into the database design. The database (more appropriately, the database management system, which is discussed later)
ensures that users can never violate these constraints. There are two types of integrity constraints: (1) relational integrity constraints and (2) business-critical integrity
constraints.
Relational integrity constraints are rules that enforce basic and fundamental
information-based constraints. For example, an operational integrity constraint would
not allow someone to create an order for a nonexistent customer, provide a markup
percentage that was negative, or order zero pounds of raw materials from a supplier.
Business-critical integrity constraints enforce business rules vital to an organizations
success and often require more insight and knowledge than relational integrity constraints. Consider a supplier of fresh produce to large grocery chains such as Kroger. The
supplier might implement a business-critical integrity constraint stating that no product
returns are accepted after 15 days past delivery. That would make sense because of the
chance of spoilage of the produce. These types of integrity constraints tend to mirror the
very rules by which an organization achieves success.
The specification and enforcement of integrity constraints produce higher quality information that will provide better support for business decisions. Organizations that establish specific procedures for developing integrity constraints typically
see a decline in information error rates and an increase in the use of organizational
information.

I NCREASED INFORMATION SECURITY
Information is an organizational asset. Like any asset, the organization must protect
its information from unauthorized users or misuse. As systems become increasingly
complex and more available over the Internet, security becomes an even bigger issue.
Databases offer many security features including passwords, access levels, and access
controls. Passwords provide authentication of the user who is gaining access to the
system. Access levels determine who has access to the different types of information,
and access controls determine what type of access they have to the information. For
example, customer service representatives might need read-only access to customer
order information so they can answer customer order inquiries; they might not have
or need the authority to change or delete order information. Managers might require
access to employee files, but they should have access only to their own employees
files, not the employee files for the entire company. Various security features of databases can ensure that individuals have only certain types of access to certain types of
information.
Databases can increase personal security as well as information security. The
Chicago Police Department (CPD) has relied on a crime-fighting system called Citizen
and Law Enforcement Analysis and Reporting (CLEAR). CLEAR electronically streamlines the way detectives enter and access critical information to help them solve crimes,
analyze crime patterns, and ultimately promote security in a proactive manner. The
CPD enters 650,000 new criminal cases and 500,000 new arrests into CLEAR each year.1

90

*

Unit 2 Exploring Business Intelligence

baL76841_ch07_086-097.indd
baL76841_ch07_086-097.indd 90

10/6/11 5:24 PM

Confirming Pages

Database Management Systems

LO 7.3 Define a database

Fords European plant manufactures more than 5,000 vehicles a day and sells them in
over 100 countries worldwide. Every component of every model must conform to complex
European standards, including passenger safety standards and pedestrian and environmental protection standards. These standards govern each stage of Fords manufacturing
process from design to final production. The company needs to obtain many thousands of
different approvals each year to comply with the standards. Overlooking just one means
the company cannot sell the finished vehicle, which brings the production line to a standstill and could potentially cost Ford up to 1 million euros per day. Ford built the Homologation Timing System (HTS), based on a relational database, to help it track and analyze these
standards. The reliability and high performance of the HTS have helped Ford substantially
reduce its compliance risk.2
A database management system is used to access information from a database.
A database management system (DBMS) is software through which users and application programs interact with a database. The user sends requests to the DBMS and the
DBMS performs the actual manipulation of the information in the database. There are
two primary ways that users can interact with a DBMS: (1) directly and (2) indirectly, as
displayed in Figure 7.2. In either case, users access the DBMS and the DBMS accesses
the database.

management system and its
relationship to a website.

DATA-DRIVEN WEBSITES
The pages on a website must change according to what a site visitor is interested in
browsing. Consider for example, a company selling sports cars. A database is created
with information on each of the currently available cars (e.g., make, model, engine
details, year, a photograph, etc.). A visitor to the website clicks on Porsche, for example, enters the price range he or she is interested in, and hits Go. The visitor is presented with information on available cars within the price range and an invitation to
purchase or request more information from the company. Via a secure administration
area on the website, the company has the ability to modify, add, or remove cars to the
database.

Indirect Interaction

FIGURE 7.2

Interacting Directly and
Indirectly with a Database
through a DBMS

Direct Interaction

Accounting
programs
User
Marketing
programs
User

DBMS

Database

DBMS
User

Manufacturing
programs
User

Chapter 7 Storing Organizational InformationDatabases

baL76841_ch07_086-097.indd 91

*

91

10/6/11 5:24 PM

Confirming Pages

1 Search Query

3 Results

2 Database
FIGURE 7.3

WikipediaData-Driven
Website
A data-driven website is an interactive website kept constantly updated and relevant
to the needs of its customers through the use of a database. Data-driven websites are
especially useful when the site offers a great deal of information, products, or services.
Website visitors are frequently angered if they are buried under an avalanche of information when searching a website. A data-driven website invites visitors to select and
view what they are interested in by inserting a query. The website analyzes the query
and then custom builds a web page in real-time that satisfies the query. Figure 7.3 displays a Wikipedia user querying business intelligence and the database sending back the
appropriate web page that satisfies the users request.

Data-Driven Website Business Advantages
When building a website, ask two primary questions to determine if the website needs a
database:

1. How often will the content change?
2. Who will be making the content changes?
For a general informational website with static information, it is best to build a
static websiteone that a developer can update on an as-needed basis, perhaps
a few times a year. A static website is less expensive to produce and typically meets
business needs.
For a website with continually changing informationpress releases, new product
information, updated pricing, etc.it is best to build a data-driven website. Figure 7.4
displays the many advantages associated with a data-driven website.

92

*

Unit 2 Exploring Business Intelligence

baL76841_ch07_086-097.indd
baL76841_ch07_086-097.indd 92

10/6/11 5:24 PM

Confirming Pages

Data-Driven Website Advantages


Development: Allows the website owner to make changes any timeall without having to rely on a developer or knowing HTML
programming. A well-structured, data-driven website enables updating with little or no training.



Content management: A static website requires a programmer to make updates. This adds an unnecessary layer between the
business and its web content, which can lead to misunderstandings and slow turnarounds for desired changes.



Future expandability: Having a data-driven website enables the site to grow faster than would be possible with a static site.
Changing the layout, displays, and functionality of the site (adding more features and sections) is easier with a data-driven
solution.



Minimizing human error: Even the most competent programmer charged with the task of maintaining many pages will overlook
things and make mistakes. This will lead to bugs and inconsistencies that can be time consuming and expensive to track down and
fix. Unfortunately, users who come across these bugs will likely become irritated and may leave the site. A well-designed, datadriven website will have error trapping mechanisms to ensure that required information is filled out correctly and that content is
entered and displayed in its correct format.



Cutting production and update costs: A data-driven website can be updated and published by any competent data-entry or
administrative person. In addition to being convenient and more affordable, changes and updates will take a fraction of the time
that they would with a static site. While training a competent programmer can take months or even years, training a data-entry
person can be done in 30 to 60 minutes.



More efficient: By their very nature, computers are excellent at keeping volumes of information intact. With a data-driven solution,
the system keeps track of the templates, so users do not have to. Global changes to layout, navigation, or site structure would
need to be programmed only once, in one place, and the site itself will take care of propagating those changes to the appropriate
pages and areas. A data-driven infrastructure will improve the reliability and stability of a website, while greatly reducing the
chance of breaking some part of the site when adding new areas.



Improved stability: Any programmer who has to update a website from static templates must be very organized to keep track of
all the source files. If a programmer leaves unexpectedly, it could involve re-creating existing work if those source files cannot be
found. Plus, if there were any changes to the templates, the new programmer must be careful to use only the latest version. With a
data-driven website, there is peace of mind, knowing the content is never losteven if your programmer is.3

FIGURE 7.4

Data-Driven Business Intelligence
Companies can gain business intelligence by viewing the data accessed and analyzed
from their website. Figure 7.5 displays how running queries or using analytical tools,
such as a Pivot Table, on the database that is attached to the website can offer insight
into the business, such as items browsed, frequent requests, items bought together, etc.

Integrating Information among
Multiple Databases

Data-Driven Website
Advantages

LO 7.4 Explain why an organization would want to integrate its
databases.

Until the 1990s, each department in the United Kingdoms Ministry of Defense (MOD)
and Army headquarters had its own systems, each system had its own database, and
sharing information among the departments was difficult. Manually inputting the same
information multiple times into the different systems was also time consuming and inefficient. In many cases, management could not even compile the information it required
to answer questions and make decisions.
The Army solved the problem by integrating its systems, or building connections
between its many databases. These integrations allow the Armys multiple systems to
automatically communicate by passing information between the databases, eliminating the need for manual information entry into multiple systems because after entering the information once, the integrations send the information immediately to all other

Chapter 7 Storing Organizational InformationDatabases

baL76841_ch07_086-097.indd 93

*

93

10/6/11 5:24 PM

C...

This is the end of the preview. Download to see the full text
Sign up to view the entire interaction

Please find attached doc file, it will open... View the full answer

ER Diagram_8496713.doc

ER Diagram

Note : Used MS access for creating it

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors and customizable flashcards—available anywhere, anytime.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access or to earn money with our Marketplace.

    Browse Documents
  • 890,990,898

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
  • 890,990,898

    Flashcards

    Browse existing sets or create your own using our digital flashcard system. A simple yet effective studying tool to help you earn the grade that you want!

    Browse Flashcards