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:
◦a customer ID
◦customer status (active, inactive, preferred, etc)
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 .
Confirming Pages CHAPTER 7 Storing Organizational
Information—Databases L EAR N IN G OUTC OMES 7.1. Define the fundamental concepts of the relational
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 prevalent—the 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 customer’s 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 company’s 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 model—entities,
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.,
Dave’s 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 Information—Databases 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: Dave’s Sub Shop Quantity
100 8/6/2008 Product
Vanilla Coke Price
$55 Distributor Fee $12.95
Order Total $67.95 CUSTOMER
Customer ID Customer Name Contact Name Phone 23 Dave’s Sub Shop David Logan (555)333-4545 43 Pizza Palace Debbie Fernandez (555)345-5432 765 T’s 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 example—a 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 Information—Databases 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
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 organization’s
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
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 Ford’s 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 Ford’s 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
User DBMS Database DBMS
User Chapter 7 Storing Organizational Information—Databases 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 Wikipedia—Data-Driven
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 user’s 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” website—one 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
For a website with continually changing information—press 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 time—all 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 lost—even 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 Kingdom’s 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 Army’s 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 Information—Databases baL76841_ch07_086-097.indd 93 * 93 10/6/11 5:24 PM Confirming Pages 2 Database 1 Web Page 3 Pivot Table FIGURE 7.5 BI in a Data-Driven
Website 94 * databases. The integrations not only enable the different departments to share information, bu...
- need help on this these are the feedback from the previous assignment Normalizing your initial EER diagram model to at least 3rd normal form. Completed Step 1:
- What I need help with - Implement this model in Access or other DBMS product. You are also welcome to web-enable your database (but you must find your own
- i) Follow the procedure shown in Figure 4-1 to assess this data. List multivalued dependencies, functional dependencies, candidate keys, primary keys, and
- I need help with the attached homework for Database Management and Applications
- I need help with Entity Relationship Diagrams. The questions and diagrams are located in the word document
- The textbook is: System Analysis & Design by Kendall & Kendall 9th edition There are 5 questions: #1 (For Chapter 3) Alamo Foods of San Antonio wants to