ReLaTiOnAL
DaTaBaSe FuNdAmEnTaLs:-
vInformation is
everywhere in an organization.
vInformation is
stored in databases.
§Database – maintains
information about various types of objects (inventory), events (transactions),
people (employees), and places (warehouses).
vDatabase 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
aNd AttRiBuTeS:-
vEntity – 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.
vAttributes
(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.
KeYs aNd
ReLaTiOnShiPs:-
vPrimary 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.
vPotential
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:-
vDatabase advantages
from a business perspective include:
§Increased
flexibility.
§Increased
scalability and performance.
§Reduced
information redundancy.
§Increased
information integrity (quality).
§Increased
information security.
InCrEaSeD
FLeXiBiLiTy:-
vA 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.
InCreAsEd
ScALaBiLiTy aNd PeRfOrMaNcE:-
vA 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.
ReDuCed
InFoRmAtiOn ReDuNdAnCy:-
vDatabases reduce
information redundancy.
§Redundancy – the
duplication of information or storing the same information in multiple
places.
vInconsistency is
one of the primary problems with redundant information.
InCrEaSe
InFoRmAtiOn InTeGriTy (QuAliTy):-
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.
InCreAsEd
InFoRmAtiOn SeCuRiTy:-
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 SiTeS:-
>A 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.
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:-
vBusiness
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:-
vIntegration – 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.
cision faster.