Relational Database Fundamentals
Information is everywhere in an
organization.
Information is stored in databases.
Database –
maintains information about various types of objects (inventory), events
(transactions), people (employees), and places (warehouses).
Database models include:
Hierarchical
database model
– information is organized into a tree-like structure (using parent/child
relationships) in such a way that it cannot have too many relationships.
Network
database model
– a flexible way of representing objects and their relationships.
Relational
database model
– stores information in the form of logically related two-dimensional tables.
Entities & Attributes
Entity – a person, place, thing, transaction,
or event about which information is stored.
The
rows in each table contain the entities.
In
Figure 7.1 CUSTOMER includes Dave’s Sub Shop and Pizza Palace entities.
Attributes
(fields, columns) –
characteristics or properties of an entity class.
The
columns in each table contain the attributes.
In
Figure 7.1 attributes for CUSTOMER include Customer ID, Customer Name, Contact
Name.
Primary keys and foreign keys identify
the various entity classes (tables) in the database.
Primary
key
– a field (or group of fields) that uniquely identifies a given entity in a
table.
Foreign
key
– a primary key of one table that appears an attribute in another table and
acts to provide a logical relationship among the two tables.
Potential relational database for
Coca-Cola:
Walk
your students through the relational database model in Figure 7.1
To
ensure your students are grasping the concepts, ask them to answer the
following:
How
many orders have been placed for T’s Fun Zone?
Answer: 1 Order IT 34563.
How
many orders have been placed for Pizza Palace?
Answer:
None.
How
many items are included in Dave’s Sub Shop’s two orders?
Answer:
Order 34561 has 3 items and order 34562 has one item for a total of 4
items in both orders.
Who
is responsible for distributing Dave’s Sub Shop’s orders?
Answer:
Hawkins Shipping.
Which
products are included in Order 34562?
Answer:
300 Vanilla Coke.
Relational Database Advantages
Database advantages from a business
perspective include:
Increased
flexibility.
Increased
scalability and performance.
Reduced
information redundancy.
Increased
information integrity (quality).
Increased
information security.
lncreased Flexibility
A well-designed database should:
Handle
changes quickly and easily.
Provide
users with different views.
Have
only one physical view.
•Physical
view –
deals with the physical storage of information on a storage device.
Have
multiple logical views.
•Logical
view –
focuses
on how users logically access information.
A database must scale to meet increased
demand, while maintaining acceptable
performance levels.
Scalability –
refers to how well a system can adapt to increased demands.
Performance –
measures how quickly a system performs a certain process or transaction.
Databases reduce information redundancy.
Redundancy –
the duplication of information or storing the same information in multiple
places.
Inconsistency is one of the primary
problems with redundant information.
vInformation
integrity – measures the quality of information.
vIntegrity
constraint – rules that help ensure the quality of
information.
§Relational integrity constraint - rule
that enforces basic and fundamental information-based constraints.
§Business-critical integrity
constraint - rule that enforce business rules vital to an organization’s success and often
require more insight and knowledge than relational integrity constraints.
vInformation is an organizational asset
and must be protected.
vDatabases offer several security features
including:
§Password –
provides authentication of the user.
§Access level –
determines who has access to the different types of information.
§Access control –
determines types of user access, such as read-only access.
Database
Management Systems:-
vDatabase
management systems (DBMS) – software
through which users and application programs interact with a database.
Direct interaction :
>The user interacts directly with the DBMS.
>The DBMS obtains the information from the
database.
Indirect interaction:
>User interacts with an application (i.e.,
payroll application, manufacturing application, sales application).
>The application interacts with the DBMS.
>The DBMS obtains the information from the
database.
data-driven
Web site is an interactive Web Site kept constantly updated and relevant to the needs of its customers through the
use of a database. Data-driven Web sites are especially useful when the site
offers a great deal of information, products, or services. Web site visitors
are frequently angered if they are buried under an avalanche of information
when searching a Web site. A data-driven Web site invites visitors to select
and view what they are interested in by inserting a query, which the Web site
then analyzes and custom builds a Web page in real-time that satisfies the
query. The figure displays a Wikipedia user querying business intelligence and
the database sending back the appropriate Web page that satisfies the user’s
request.
What would happen to a Web site that is not data-driven?
Answer:The users would need to continually
update the Web site data manually as the business data is updated. This would be a redundant effort and most
likely result in errors and the Web site could quickly become out of sync with
the business data.
Driven Web Site Advantages
1. Development:
Allows the Web site owner to make changes
any time—all without having to rely on a developer or knowing HTML programming.
A well-structured, data-driven Web site enables updating with little or no
training.
2. Content
management: A static Web site 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.
3. Future
expandability: Having
a data-driven Web site 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.
4. 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,
data-driven Web site 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.
5.Cutting
production and update costs: A
data-driven Web site 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.
6.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 Web site, while
greatly reducing the chance of ”breaking” some part of the site when adding new
areas.
7. Improved
Stability: Any programmer who has to update a Web
site 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 Web site, there is peace of mind, knowing
the content is never lost—even if your programmer is.
Data-Driven Business Intelligence
Business Intelligence in a data-driven Web site:
>Companies
can gain business intelligence by viewing the data accessed and analyzed from
their Web site. The figure displays how
running queries or using analytical tools, such as a Pivot Table, on the
database that is attached to the Web site can offer insight into the business,
such as items browsed, frequent requests, items bought together, etc.
Integrating Information Among Multiple Databases
Integration – allows separate systems to communicate directly with each other.
Forward integration – takes information entered into a given system and sends it automatically to all downstream systems and processes.
Backward integration – takes information entered into a given system and sends it automatically to all upstream systems and processes.
Forward Integration:
>Basically,
all information flows forward along the business process. Sales
enters the information when it is negotiating the sale (looking for
opportunities). The
information is then passed to the order entry system when the order is actually
placed. The
order fulfillment system picks the products from the warehouse, packs the
products, labels boxes, etc. Once
the order is filled and shipped, the customer is billed.
What
would happen if users could enter order information directly into the billing
system?
The
systems would quickly become out-of-sync.
There might be bills for nonexistent orders, or orders that do not have
any bills (if someone deleted a bill). For
this reason organizations typically place a business-critical integrity
constraint on integrated systems: With a
forward integration the information must be entered in the sales system, you
could not enter information directly into the billing system.
>Integrations
are expensive to build and maintain and difficult to implement. For
these reasons many organizations only build forward integrations and use
business-critical integrity constraints to ensure all information is always
entered only at the start of the integration (one source of record).
Backward Integration:
Basically,
all information flows backward along the business process. Billing
enters information and this information is passed back to the order system. The
order fulfillment system passes the information back to the order entry system. The
order entry system passes the information back to the sales system.
Why
would an organization want to build both forward and backward integrations?
This
allows users to enter information at any point in the business process and the
information is automatically sent upstream and downstream to all other systems. For
example, if order fulfillment determined that they could not fulfill an order
(the product had been discontinued), they could simply enter this information
into the database and it would be sent automatically upstream to the sales
representative who could contact the customer and downstream to billing to
remove the item from the bill.
Building a central repository
specifically for integrated information:-
The
above figure displays an example of customer information integrated using this
method. Users
can create, read, update, and delete in the main customer repository, and it is
automatically sent to all of the other databases. This
method does not follow the business process when building the integrations. Business-critical
integrity constraints still need to be built to ensure information is only ever
entered into the customer repository, otherwise the information will become
out-of-sync.
No comments:
Post a Comment